Stored
Procedures
What
is a Stored Procedure?
A stored
procedure or in simple a proc is a named
PL/SQL block which performs one or more specific task. This is similar to a
procedure in other programming languages. A procedure has a header and a body.
The header consists of the name of the procedure and the parameters or
variables passed to the procedure. The body consists or declaration section,
execution section and exception section similar to a general PL/SQL Block. A
procedure is similar to an anonymous PL/SQL Block but it is named for repeated
usage.
We
can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters
2) OUT-parameters
3) IN OUT-parameters
A
procedure may or may not return any value.
General
Syntax to create a procedure is:
CREATE [OR REPLACE]
PROCEDURE proc_name [list of parameters]
IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
IS - marks the
beginning of the body of the procedure and is similar to DECLARE in anonymous
PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.
The syntax within the
brackets [ ] indicate they are optional. By using CREATE OR REPLACE together
the procedure is created if no other procedure with the same name exists or the
existing procedure is replaced with the current code.
The
below example creates a procedure ‘employer_details’ which gives the details of
the employee.
CREATE OR REPLACE PROCEDURE employer_details
IS
CURSOR
emp_cur IS
SELECT
first_name, last_name, salary FROM emp_tbl;
emp_rec emp_cur%rowtype;
BEGIN
FOR
emp_rec in sales_cur
LOOP
dbms_output.put_line(emp_cur.first_name
|| ' ' ||emp_cur.last_name
|| ' ' ||emp_cur.salary);
END
LOOP;
END;
/
Parameters
in Procedure and Functions
How to pass parameters to Procedures and Functions in PL/SQL ?
In
PL/SQL, we can pass parameters to procedures and functions in three ways.
1) IN type parameter: These types of parameters are used to send values to
stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.
NOTE: If
a parameter is not explicitly defined a parameter type, then by default it is
an IN type parameter.
1) IN
parameter:
This is similar to
passing parameters in programming languages. We can pass values to the stored
procedure through these parameters or variables. This type of parameter is a
read only parameter. We can assign the value of IN type parameter to a variable
or use it in a query, but we cannot change its value inside the procedure.
The
General syntax to pass a IN parameter is
CREATE [OR REPLACE]
PROCEDURE procedure_name (
param_name1 IN datatype, param_name12 IN
datatype ... )
- param_name1, param_name2...
are unique parameter names.
- datatype - defines the datatype
of the variable.
- IN - is optional, by default it
is a IN type parameter.
2) OUT Parameter:
The OUT parameters are
used to send the OUTPUT from a procedure or a function. This is a write-only
parameter i.e, we cannot pass values to OUT paramters while executing the
stored procedure, but we can assign values to OUT parameter inside the stored
procedure and the calling program can recieve this output value.
The General syntax to
create an OUT parameter is
CREATE [OR REPLACE]
PROCEDURE proc2 (param_name OUT datatype)
The
parameter should be explicity declared as OUT parameter.
3) IN OUT Parameter:
3) IN OUT Parameter:
The IN OUT parameter
allows us to pass values into a procedure and get output values from the
procedure. This parameter is used if the value of the IN parameter can be
changed in the calling program.
By using IN OUT
parameter we can pass values into a parameter and return a value to the calling
program using the same parameter. But this is possible only if the value passed
to the procedure and output value have a same datatype. This parameter is used
if the value of the parameter will be changed in the procedure.
The
General syntax to create an IN OUT parameter is
CREATE [OR REPLACE]
PROCEDURE proc3 (param_name IN OUT datatype)
The
below examples show how to create stored procedures using the above three types
of parameters.
Example1:
Using IN and OUT parameter:
Let’s
create a procedure which gets the name of the employee when the employee id is
passed.
CREATE OR REPLACE PROCEDURE emp_name (id IN
NUMBER, emp_name OUT NUMBER)
IS
BEGIN
SELECT first_name INTO emp_name
FROM emp_tbl WHERE empID = id;
END;
/
We
can call the procedure ‘emp_name’ in this way from a PL/SQL Block.
DECLARE
empName varchar(20);
CURSOR id_cur SELECT id FROM emp_ids;
BEGIN
FOR emp_rec in id_cur
LOOP
emp_name(emp_rec.id, empName);
dbms_output.putline('The employee ' ||
empName || ' has id ' || emp-rec.id);
END LOOP;
END;
/
In
the above PL/SQL Block
In line no 3; we are creating a cursor ‘id_cur’ which contains the employee id.
In line no 7; we are calling the procedure ‘emp_name’, we are passing the ‘id’ as IN parameter and ‘empName’ as OUT parameter.
In line no 8; we are displaying the id and the employee name which we got from the procedure ‘emp_name’.
In line no 3; we are creating a cursor ‘id_cur’ which contains the employee id.
In line no 7; we are calling the procedure ‘emp_name’, we are passing the ‘id’ as IN parameter and ‘empName’ as OUT parameter.
In line no 8; we are displaying the id and the employee name which we got from the procedure ‘emp_name’.
Example
2:
Using IN OUT parameter in procedures:
CREATE OR REPLACE PROCEDURE
emp_salary_increase
(emp_id IN emptbl.empID%type, salary_inc IN
OUT emptbl.salary%type)
IS
tmp_sal number;
BEGIN
SELECT salary
INTO tmp_sal
FROM emp_tbl
WHERE empID = emp_id;
IF
tmp_sal between 10000 and 20000 THEN
salary_inout := tmp_sal * 1.2;
ELSIF tmp_sal between 20000 and 30000 THEN
salary_inout := tmp_sal * 1.3;
ELSIF tmp_sal > 30000 THEN
salary_inout := tmp_sal * 1.4;
END IF;
END;
/
The
below PL/SQL block shows how to execute the above 'emp_salary_increase'
procedure.
DECLARE
CURSOR updated_sal is
SELECT empID,salary
FROM emp_tbl;
pre_sal number;
BEGIN
FOR emp_rec IN updated_sal LOOP
pre_sal := emp_rec.salary;
emp_salary_increase(emp_rec.empID,
emp_rec.salary);
dbms_output.put_line('The salary of ' ||
emp_rec.empID ||
' increased from '|| pre_sal ||
' to '||emp_rec.salary);
END LOOP;
END;
/
How to execute a
Stored Procedure?
There
are two ways to execute a procedure.
1)
From the SQL prompt.
EXECUTE [or EXEC] procedure_name;
2)
Within another procedure – simply use the procedure name.
procedure_name;
NOTE: In the examples given above, we are
using backward slash ‘/’ at the end of the program. This indicates the oracle
engine that the PL/SQL program has ended and it can begin processing the
statements.
Parameter Data types
The
datatype of a formal parameter consists of one of the following:
·
An unconstrained type name, such as
NUMBER or VARCHAR2.
·
A
type that is constrained using the
%TYPE or %ROWTYPE attributes.
Note:
Numerically constrained types such as
NUMBER(2) or VARCHAR2(20) are not allowed in a
parameter list.
%TYPE
and %ROWTYPE Attributes
Use the type
attributes
%TYPE and %ROWTYPE to constrain the parameter. For
example, the Get_emp_names procedure specification in"Parameters for
Procedures and Functions" could
be written as the following:PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)
This has the
Dept_num parameter take the same datatype as
the Deptno column in the Emp_tab table. The column and table must be
available when a declaration using %TYPE (or %ROWTYPE)
is elaborated.
Using
%TYPE is recommended, because if the type of
the column in the table changes, then it is not necessary to change the
application code.
If the
Get_emp_names procedure is part of a package, then
you can use previously-declared public (package) variables to constrain a
parameter datatype. For example:Dept_number number(2);
...
PROCEDURE Get_emp_names(Dept_num IN Dept_number%TYPE);
Use the
%ROWTYPE attribute to create a record that
contains all the columns of the specified table. The following example defines
theGet_emp_rec procedure, which returns all the
columns of the Emp_tab table in a PL/SQL record for the
given empno:
Caution:
To execute the following, use
CREATE OR REPLACE PROCEDURE...PROCEDURE Get_emp_rec (Emp_number IN Emp_tab.Empno%TYPE,
Emp_ret OUT Emp_tab%ROWTYPE) IS
BEGIN
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
INTO Emp_ret
FROM Emp_tab
WHERE Empno = Emp_number;
END;
You
could call this procedure from a PL/SQL block as follows:
DECLARE
Emp_row Emp_tab%ROWTYPE; -- declare a record matching a
-- row in the Emp_tab table
BEGIN
Get_emp_rec(7499, Emp_row); -- call for Emp_tab# 7499
DBMS_OUTPUT.PUT(Emp_row.Ename || ' ' || Emp_row.Empno);
DBMS_OUTPUT.PUT(' ' || Emp_row.Job || ' ' || Emp_row.Mgr);
DBMS_OUTPUT.PUT(' ' || Emp_row.Hiredate || ' ' || Emp_row.Sal);
DBMS_OUTPUT.PUT(' ' || Emp_row.Comm || ' '|| Emp_row.Deptno);
DBMS_OUTPUT.NEW_LINE;
END;
FUNCTION Get_emp_rec (Dept_num IN Emp_tab.Deptno%TYPE)
RETURN Emp_tab%ROWTYPE IS ...
Tables and Records
You
can pass PL/SQL tables as parameters to stored procedures and functions. You
can also pass tables of records as parameters.
Note:
When passing a user defined type, such as a PL/SQL table
or record to a remote procedure, to make PL/SQL use the same definition so that
the type checker can verify the source, you must create a redundant loop back
DBLINK so that when the PL/SQL compiles, both sources pull from the same
location.
Default Parameter Values
Parameters can
take default values. Use the
DEFAULT keyword or the assignment operator to
give a parameter a default value. For example, the specification for the Get_emp_names procedure could be written as the
following:PROCEDURE Get_emp_names (Dept_num IN NUMBER DEFAULT 20) IS ...
or
PROCEDURE Get_emp_names (Dept_num IN NUMBER := 20) IS ...
When a
parameter takes a default value, it can be omitted from the actual parameter
list when you call the procedure. When you do specify the parameter value on
the call, it overrides the default value.
Note:
Unlike in an anonymous PL/SQL block, you do not use the
keyword
DECLARE before the declarations of variables,
cursors, and exceptions in a stored procedure. In fact, it is an error to use
it.
Creating Stored Procedures
and Functions
Use
a text editor to write the procedure or function. At the beginning of the
procedure, place the following statement:
CREATE PROCEDURE Procedure_name AS ...
For
example, to use the example in "%TYPE and %ROWTYPE
Attributes ", create a text (source) file called
get_emp.sql containing the following code:CREATE PROCEDURE Get_emp_rec (Emp_number IN Emp_tab.Empno%TYPE,
Emp_ret OUT Emp_tab%ROWTYPE) AS
BEGIN
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
INTO Emp_ret
FROM Emp_tab
WHERE Empno = Emp_number;
END;
/
Then,
using an interactive tool such as SQL*Plus,
load the text file containing the procedure by entering the following
statement:
SQL> @get_emp
This loads the
procedure into the current schema from the
get_emp.sql file (.sql is the default file extension). Note
the slash (/) at the end of the code. This is not part of the code; it just activates
the loading of the procedure.
Use
the
CREATE [OR REPLACE] FUNCTION...
statement to store functions.
Caution:
When developing a new procedure, it is usually much more
convenient to use the
CREATE OR REPLACE PROCEDURE statement. This replaces any previous
version of that procedure in the same schema with the newer version, but note
that this is done without warning.
You
can use either the keyword
IS or AS after the procedure parameter list.Altering Stored Procedures and Functions
To
alter a stored procedure or function, you must first drop it using the
DROP PROCEDURE or DROP FUNCTION statement, then re-create it using the CREATE PROCEDURE or CREATE FUNCTION statement. Alternatively, use the CREATE OR REPLACE PROCEDURE or CREATE ORREPLACE FUNCTION statement, which first drops the
procedure or function if it exists, then re-creates it as specified.
Caution:
The procedure or function is dropped without any warning.
Dropping Procedures and
Functions
A
standalone procedure, a standalone function, a package body, or an entire
package can be dropped using the SQL statements
DROPPROCEDURE, DROP FUNCTION, DROP PACKAGE BODY,
and DROP PACKAGE,
respectively. DROP PACKAGE statement drops both the specification
and body of a package.
The
following statement drops the
Old_sal_raise procedure in your schema:DROP PROCEDURE Old_sal_raise;
