PLSQL Procedure

PLSQL Procedure is a PLSQL block with a name and declared with PROCEDURE keyword. It may accept some input arguments, and perform some activities. A procedure does not return a value. If you want the procedure to return some value, it can be done by defining the output parameter in its parameter list.

Syntax of PLSQL Procedure

CREATE  OR REPLACE PROCEDURE proc_name([param1,[param2,],…..])
AS/IS
[ local variable declaration]
 BEGIN 
Procedure statements 
[EXCEPTION 
exception handler list ] 
END [proc_name];

A PLSQL procedure is created by CREATE statement with PROCEDURE keyword. Including OR REPLACE keywords  in procedure header will replace the existing procedure definition. In absence of OR REPLACE if the procedure already exists, an error message will be displayed indicating failure to create due to name being already in use.  In case you omit OR REPLACE keywords, you need to remove the existing Procedure definition from schema using  DROP statement.

Procedure Parameters, Local variable declaration and exception section of the procedure are optional. Procedure name is compulsory since it has to be called.

A procedure can be declared with no parameters or one or more parameters. A procedure, like a PLSQL Block, must have these two sections.

  • Header section– It precedes AS/ IS keyword.  It must have name of the procedure and names, types and data types of the parameters.  Type of parameters are IN,OUT and IN OUT. Data types can be those allowed in  Oracle.
  • Body section – It starts from AS keyword. The local variables used in the PLSQL Procedure are declared as the first thing and are optional. After the BEGIN keyword logic of the actions to be implemented for the procedure is given in the PLSQL statements. It may include the Exception Section starting with keyword EXCEPTION.

Calling the Procedure

A declared and successfully compiled PLSQL Procedure can be called in another PLSQL block like anonymous block, procedure or function. It can be directly called by using EXEC statement followed by name of procedure and actual arguments

EXEC max_sal(10)

Use of PLSQL Procedure

Procedures can be created to make the middle layer of an application which uses the database tables. You can call these procedures in your front end programming interface without the need of executing SQL statements directly from the application.

Once a procedure is compiled it is saved in the schema as a database object and called any number of times from different applications. They can be called simultaneously from different user interfaces.

PLSQL procedure implements reusability and structuring of database applications. Once Compiled-Used Many times reduces programming efforts thus reducing application development  duration.

Type of Parameters

Type of parameter indicates whether the parameter is treated as input, output or both input and output by the procedure. Syntax of defining parameters in a procedure is

Parameter_name [IN/OUT/IN OUT] data-type 

IN Parameter

IN defines a parameter to be used as an input value. It is read-only and cannot be changed within the procedure.  IN parameter can be a constant, literal or an expression that evaluates to some value. You can define multiple IN parameters in a procedure to perform some calculation or filter data from tables.

OUT  Parameter

OUT defines a parameter to be used as an output argument to be returned after execution of procedure. It is write-only and cannot be used in any reading operations in the procedure.  OUT parameter cannot be a constant, literal or an expression that evaluates to some value. It can only be a variable which gets its value when the procedure is executed successfully.

You can use multiple OUT parameters in a procedure. It means that unlike a function, a procedure will not be restricted to return single value to the calling program. OUT parameters always return values where they a procedure is called. So, when calling a procedure with OUT parameters you must pass on the declared variables as actual parameters corresponding to all the OUT parameters defined in procedure header.

CREATE OR REPLACE PROCEDURE p_emp_data(deptid IN emp.deptno%type, empnam OUT emp.ename%type, empsal  OUT emp.sal%type)

Calling procedure in an anonymous block-

DECLARE
en emp.ename%type;
es emp.sal%type;
BEGIN
p_emp_data(10,en,es);
DBMS_OUTPUT.PUT_LINE(en||' '||es);
END;

IN OUT Parameter

This defines that a parameter can be used as read-write argument and must always be a variable. The passed value of an IN OUT parameter will be overwritten by value modified  in the procedure.

Examples

Write a PLSQL procedure that prints maximum salary in emp table.

CREATE OR REPLACE PROCEDURE Max_Sal
AS
ms emp.sal%type;
BEGIN
 select max(sal) into ms from emp;
 DBMS_OUTPUT.PUT_LINE('Maximum salary ' ||ms);
END Max_Sal ; 

Write a PLSQL procedure that accepts first name and last name as IN parameters and displays the concatenated name.

CREATE OR REPLACE PROCEDURE proc_join (fname varchar, lname varchar)
AS
BEGIN
 DBMS_OUTPUT.PUT_LINE(fname||' '||lname);
END proc_join; 

Write a PLSQL procedure that accepts a department number as IN parameter and displays the maximum salary in the department

CREATE OR REPLACE PROCEDURE max_sal(did emp.deptno%type)
AS
ms emp.sal%type;
BEGIN
 select max(sal) into ms from emp where deptno=did;
 DBMS_OUTPUT.PUT_LINE('Maximum salary ' ||ms);
END max_sal ; 

Write a procedure that accepts a department number as IN parameter and returns maximum hire date in the department as output parameter

CREATE OR REPLACE PROCEDURE max_hdate(did IN emp.deptno%type,edt OUT emp.hiredate%type  )
AS
BEGIN
 select max(hiredate) into edt from emp where deptno=did;
END max_hdate ; 


--calling the procedure in an anoymous block
DECLARE
dt emp.hiredate%type ;
BEGIN
max_hdate(10,dt);
DBMS_OUTPUT.PUT_LINE(dt);
END;

Write a procedure that displays concatenated name and job of employee after accepting employee id.

CREATE OR REPLACE PROCEDURE empjob(emj OUT varchar2  )
AS
eid emp.deptno%type;
BEGIN
 eid:=&empid;
 select ename||' '||job  into emj from emp where emp_id=eid;
END empjob ; 


--calling the procedure in an anoymous block
DECLARE
edata varchar2(50) ;
BEGIN
empjob(edata);
DBMS_OUTPUT.PUT_LINE(edata);
END;

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *