Procedure- A procedure is a logical group of SQL & PL-SQL statement that performs a specific action
Types of PL-SQL Procedures-
- Local or anonymous procedures
- Stored procedures
1. Local Procedure- This type of procedure has no name such procedure is a part of an anonymous block which gets executed when a user submit its source code to the database server. Also these procedure are not stored as a database object in an oracle database.
2. Stored procedures- These types of procedures are named sub-programs that are created with a name , are stored in compiled format as an object in an oracle database, can accept input paraments or values and pass values to output parameters and can be invoked from various environment that have access to the database. their differences can be summarised as
Local Procedure
|
Stored Procedure
| |
1
|
these are the part of an anonymous block which is not stored as object in the oracle database
|
these are stored as an object in the oracle database
|
2
|
We can call these from executable section of that block, it cannot be called from inside of any other procedure or function
|
these can be called from various environments or other procedures or functions
|
3
|
These can be compiled each time, the containing block is submitted to the oracle server
|
are compiled at the time of their completion or modification
|
4
|
Can not accept parameters or values
|
can accept input parameters, pass value to output parameters
|
Advantages of procedures:-
- Procedures can be written to meet and serve some specific requirement
- Through procedures a bigger program can be broken down into smaller, manageable unit
- Stored procedures enhance performance as they are compiled at a time only when they are created or modified. when they are invoked or executed, no recompilation is performed, which not only saves time and the network traffic but also enhances performance
- stored procedures enhance reusability as they became part of the database, they can be used again and again.
- with stored procedures a better database security is practised because they become database objects and database administrator can restrict database access by granting or revoking the privileges to the user for these procedures.
- procedures take advantages of shared memory resources.
Creating stored procedure or creating procedure
Stored procedures are created as per following syntax-
Create [or replace] procedure procedure_name(parameter1 datatype[, parameter2 datatype …]
as
declaration
begin
statements
end;
/
e.g.
Create or replace procedure find_emp as
e_name varchar2(15);
e_job varchar2(15);
begin
select ename, job into e_name, e_job from emp;
dbms_output.put_line(‘Employee ’ || e_name);
dbms_output.put_line(‘Job ’ || e_job);
end;
/
Compiling stored procedures
A stored procedures is not actually stored as a part of database until it is compiled. we can compile stored procedures by using following command
SQL>@d:/addrow.sql
Passing parameters
Parameters can be actual or formal
Actual parameters-the variable or expression referenced in the parameter list of a procedure call statement are known as actual parameter
Formal parameters- the variables declared in a procedure specification and referenced in the procedure body are known as formal parameters. so we can say that the parameter appearing in the procedure call statement are actual parameters and the parameters appearing in the procedure header are called formal parameters.
parameters modes- these define the behaviour of formal parameters. These parameter modes are
- Read only and default mode- in parameter lets us pass value to the procedure being called inside the procedure, in parameter acts like a constraint that mean it can not be assigned a value
- Out mode- These lets us return a value to the caller of the procedure i.e. to the sub program, which invokes the procedure inside the procedure an out parameter acts like an uninitialised variable. that mean its value can not be assigned to another variables or re assigned to itself. in short an out parameter can’t appear in the right hand side of an assignment operator(:=)
- In out mode-
Finding, altering and removing stored procedure- to find out procedure and functions we can use following sql query
Select object type, object name
from user_objects
where objecttype=’procedure’ or objecttype=’function’
Dropping/Removing Stored Procedure-to drop a stored procedure or function we can make use of Drop procedure or Drop Function as per following syntax:-
Drop Procedure <Procedure_Name>;
Drop Function <Function_Name>;
Altering Stored Procedure-to Alter/Modify a stored procedure or function we need to drop it first using Drop procedure or Drop Function then we can recreate it using the Create Procedure or Create Function statement using following syntax:-
Create Procedure <Procedure_Name>;
Create Function <Function_Name>;
Alternatively we can use Create or Replace Procedure or Create or Replace Function statement which 1st drop the procedure or function then create it as specified.
Create or Replace Procedure <Procedure_Name>;
Create or Replace Function <Function_Name>;