PL/SQL - Stored Procedures

PL/SQL SUBPROGRAMS

A subprogram is a names PL/SQL block that can accept parameters and be invoked from a calling environment.

Is of two ways

A Procedure that performs an action

A Function that computes a value.

--> Provides modularity, reusability, extensibility, and maintainability.

--> It can be compiled and stored in the database.

--> Modularization is the process of breaking up large blocks of code into smaller groups of code called modules.

Structure of PL/SQL subprograms

IS | AS

Declaration section

BEGIN

Executable section

EXCEPTION

Exception section

End;

The header determines

--> The PL/SQL subprogram type that is either a procedure or a function.

--> The name of the subprogram.

-->The parameter list, if one exists.

--> The RETURN clause, which applies only to functions.

Procedure

A procedure is a type of subprogram that performs an action.

A procedure can be stored in the database as a schema object, for repeated execution.

Syntax :

CREATE [ OR REPLACE ] PROCEDURE procedure_name

[ (parameter1 [mode1] datatype1,

[parameter2] [mode2] datatyep2,

…..) ]

IS | AS

PL/SQL Block;

Parameter is name of a pl/sql variable whose value is passed to or populated by the calling environment.

Mode: type of argument: IN(default), OUT, IN OUT

Formal parameters :

Variables declared in the parameter list of a subprogram specification.

Actual parameters:

Variables or expressions referenced in the parameter list of subprogram call.

Eg: raise_sal( emno, 2000);

Procedural parameter modes:

IN : Passes a constant value from the calling environment into the procedure.

OUT : Passes a value from the procedure to calling environment.

IN OUT : Passes a value from the calling environment into the procedure and a possibly different value from the procedure back to the calling environment using the same parameter.

Eg: (IN)

CREATE OR REPLACE PROCEDURE raise_sal

(p_id IN emp.empno%TYPE)

IS

BEGIN

UPDATE emp

SET sal = sal*1.10

WHERE empno = p_id;

END;

Executing a procedure :

EXECUTE raise_sal(7900);

Eg: (OUT)

CREATE OR REPLACE PROCEDURE query_emp

(p_id IN emp.empno%TYPE,

P_name OUT emp.ename%TYPE,

P_sal OUT emp.sal%TYPE,

P_comm OUT emp.comm%TYPE)

IS

BEGIN

SELECT ename, sal, comm

INTO p_name, p_sal, p_comm

FROM emp

WHERE empno = p_id;

END;

Viewing OUT parameters:

Declare host variables

Variable g_name varchar2(25)

Variable g_sal number

Variable g_comm number

EXECUTE query_emp (7900, :g_name, g_sal, :g_comm);

Print g_name g_sal g_comm

Eg: (IN OUT)

CREATE OR REPLACE PROCEDURE format_phone

(p_phone_no IN OUT varchar2)

IS

BEGIN

P_phone_no := ‘{‘ || substr(p_phone_no, 1, 3) ||

‘)’ || substr(p_phone_no, 4,3) ||

‘-‘ || substr(p_phone_no, 7);

END;

Variable g_phone_no varchar2(15)

BEGIN

:g_phone_no := ‘8006330575’;

END;

/

Exec format_phone(:g_phone_no)

Print g_phone_no

Methods for passing parameters:

Positional : List actual parameters in the same order as formal parameters.

Named : List actual parameters in arbitrary order by associating each with its corresponding

formal parameter.

Combination : List some as positional and some as named.

Eg: Default option for parameters.

Create or replace procedure add_dept

(p_name In dept.deptname%type default ‘unknown’,

P_loc in dept.loc%type default 1700)

Is

Begin

Insert into dept(dpetno, deptname, loc)

Values(dept_seq.nextval, p_name, p_loc);

End;

We can assign default values only to parameters of the IN mode.

OUT and IN OUT parameters are not permitted to have default values.

Examples of passing parameters

Begin

Add_dept;

Add_dept(‘TRAINING’, 2500);

Add_dept(p_loc => 2400, p_name => ‘EDUCATION’);

Add_dept(p_loc => 1200);

End;

/

Select deptno, deptname, loc from dept;

All the positional parameters should precede the named parameters in a subprogram call.

Declaring subprograms :

Create or replace procedure leave_emp

(p_id in emp.empno%type)

Is

Procedure log_exect

Is

Begin

Insert into log_table( user_id, log_date)

Values(user, sysdate)

End log_exect;

Begin

Delete from emp where empno = p_id;

Log_exect;

End leave_emp;

Invoking a procedure from anonymous pl/sql block :

Declare

V_id number := 163;

Begin

Raise_sal(v_id);

Commit;

----

End;

Invoking a procedure from another procedure :

Create or replace procedure process_emps

Is

Cursor emp_cursor is

Select empno from emp;

Begin

For emp_rec in emp_cursor

Loop

Raise_sal(emp_rec.empno);

End loop;

Commit;

End;

Handling exceptions :

Create procedure ins_dept( p_locid number) is

V_did number(4);

Begin

Dbms_output.put_line(‘Procedure started’);

Insert into dept values(5, ‘dept 5’, 145, p_locid);

Select deptno into v_did from emp where empno=7900;

End;

Create procedure ins_loc( p_lid number, p_city varchar2)

Is

V_city varchar2(30);

V_dname varchar2(30);

Begin

Dbms_output.put_line (‘main procedure pro_loc’);

Insert into locations (locid, city) values(p_lid, p_city);

Select city into v_city from locations where locid = p_lid;

Dbms_output.put_line( ‘inserted city’ || v_city);

Dbms_output.put_line(‘Invoking the procedure pro_ins’);

Ins_dept(p_lid);

Exception

When no_data_found then

Dbms_output.put_line(‘No such dept/loc for any employee’);

End;

Removing procedures:

Drop procedure procedure_name;

Drop procedure raise_sal;

Local subprograms are programs that are defined within the declaration section of another program.