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