13 Sub Programs

pl/sql subprograms part i *objectives : *describe what is a pl/sql module *list the types of pl/sql modules *define a subprogram *list advantages of subprograms *list types of subprograms *state & explain syntax for creating a procedure *construct a procedure *identify the need for parameters *list parameter types & parameter modes

modular programming *modularity involves decomposing a program into small, independent chunks of code that do a specific task *each independent program chunk is called as a module

features of modular programs *easy to read and understand *easy to modify *fast to develop *you can make pl/sql programs modular by using named modules

pl/sql modules *pl / sql has two types of modules *subprograms *packages

subprogram *a pl / sql subprogram : *is a named pl/sql module *is stored in a database in compiled form *can take parameters, return values to calling environment *can be invoked in another subprogram when required

advantages of subprograms *complied form *subprograms are stored in complied form. this avoids reparsing when multiple users invoke it

*security & integrity *provide security and integrity control by allowing access on the subprogram and not on database objects

*extensible

*provide extensibility by writing subprograms tailored to suit the needs of the application

*modular *provide extensibility by dividing a program into manageable well-defined logic modules

*reusability and maintainability

*a validated / tested subprogram can be used in any number of applications. *modifications easy and at one place, so maintainable

*abstraction *hiding actual implementation

types of subprograms *there are two types of subprograms *procedures *functions

*generally procedures are used to perform an action and a function is used to compute a value

procedures *a procedure is a subprogram that performs a specific action *result of the execution of a procedure is in the form of side effect *a procedure has two parts *the procedure specification *the procedure body

procedures *the procedure specification : *definition of the procedure and its parameters *it is not necessary to create a procedure which accepts parameters

*the procedure body :

*the procedure body contains *declaration section without declare keyword *an executable section *and exception handling sections

creating a new procedure *syntax create [ or replace ] procedure <procedure_name> [ (<argument_1> [mode] , <argument_2> [mode] , : )] -- procedure specification is | as -- declarartions begin --- procedure body end [< procedure_name>];

*or replace : *used to modify an existing procedure

*mode : [ in | out | in out ] *decides how parameters ( arguments ) are passed

creating a procedure

*example 1 : write a procedure to add a new employee to the emp table. *solution : create or replace procedure addnewemp ( p_empno emp.empno%type, p_ename emp.ename%type, p_deptno emp.deptno%type) as begin insert into emp ( empno, ename, deptno ) values ( p_empno, p_ename, p_deptno ); commit; exception when dup_val_on_index then insert into errors values ( p_empno, ‘duplicate employee number’ ); end addnewemp;

creating a procedure *example 2 : write a procedure to update the quantity on hand in itemmast table with quantity issued. *solution : create procedure issue_qty (p_itno itemmast.itno%type, p_qty number) is begin update itemmast set qoh = qoh – p_qty where itno = p_itno; commit; end issue_qty;

points to remember *a procedure can be called from another pl/sql block

*a procedure call is a pl/sql statement itself and not a part of an expression *when a procedure is called, parameters can be passed

parameters and modes *parameters *formal parameters *actual parameters

*parameter modes

formal parameters *procedure can be declared which accepts parameters *parameters in the procedure declaration are called as formal parameters *the formal parameters are the place holders for actual parameters *inside the procedures formal parameters are referred

actual parameters *a procedure is called by passing parameters to it *the parameters passed are called as actual parameters *actual parameters contain the values that are passed to the procedures and they receive results from the procedure when it returns

parameter modes *formal parameters can have three modes in, out and in out mode description in used to pass values to the called subprogram. this is the default mode. inside the subprogram, the in parameter acts like a constant i.e. it is considered read - only. cannot be modified out used to return values to the called subprogram. inside the subprogram, the out parameter acts like an uninitialised variable. in out combination of in and out. lets user pass initial value. the in out parameter can be read or written to inside the called subprogram.

constraints on formal parameters *it is illegal to constraint the parameters in the procedure declaration *constraining char, varchar with a length and number with a value or precision and/or scale is illegal e.g. the following declaration is illegal – create or replace procedure someproc ( p_name in out varchar(25), p_balance in number (5) ) as : *the formal parameters are constrained by the declaration of actual parameters *when a procedure is called, along with values of actual parameters constraints are also passed

constraints on formal parameters

e.g. declare v_name varchar2 (25); v_balance number (5); begin : someproc (v_name, v_balance); end; the only way to put constraints on a formal parameter in the declaration is using %type in the declaration of procedure. e.g. create or replace procedure addnewemp ( p_empno emp.empno%type, p_ename emp.empname%type, p_deptno emp.deptno%type) as begin : end;

default values for parameters *formal parameters for subprograms can have default values *if a parameter has a default value, it does not have to be passed *it if is passed, the value of the actual parameter will be used e.g. create procedure someproc (p_ename emp.ename%type, p_empno emp.empno%type, p_deptno emp.deptno%type := 20 ) as :

pl/sql subprograms part ii

*objectives : *state & explain creating a function *construct a function *state how to execute a subprogram *state syntax for dropping a subprogram *list subprogram categories *explain what is a package *list package components *state & explain syntax for creating a package *demonstrate package creation *state the syntax for dropping a package *describe overloading subprograms in a package

functions *a function is a subprogram that returns a value *functions and procedures have similar structure except that the functions have a * return clause and * use return statement

creating functions syntax : create [or replace] function [( <argument_1> [mode] , <argument_2> [mode] )] return -- function specification is | as -- function body *return clause *return clause is used in function specification to specify return datatype

*return statement : *return statement is used to return control to the calling environment with the value *syntax : *return expression ;

*there can be more than one return statements inside the function but only one of them will be executed

creating function *example 1:

write a subprogram to retrieve the quantity on hand from itemmast table for the given item number, using a function. solution : create function balance_qoh ( p_itno itemmast.itno%type ) return number is v_balqoh itemmast.qoh%type ; -- local variable begin select qoh into v_balqoh from itemmast where itno = p_itno; return (v_balqoh); end;

example 2 : write a function that accepts employee number and returns the salary class as `low’, `medium’, `high’ based on his salary. salary class null not applicable less than 3000 low between 3000 and 5000 medium greater than 5000 high *solution : create or replace function sal_class ( p_empno emp.empno%type ) return varchar2 is v_sal emp.sal%type; begin select sal into v_sal from emp where empno = p_empno; if v_sal is null then return `not applicable’; elsif v_sal < 3000 then return `low’; elsif sal <=5000 then return `medium’; else return `high’; end if; end;

executing subprograms *once created a subprogram can be *executed interactively

*called from the other subprograms

executing subprograms interactively

*a subprogram can be executed interactively by oracle tools. *to execute a subprogram the execute statement is used. *syntax : execute subprogram_name(parameter(s));

*example : sql> execute issue_qty (1950, 40); you need to include the name of the subprogram owner in the execution call to execute the subprogram owned by some other user

*syntax :

sql> execute owner_name.subprogram_name (parameters);

*example : sql> execute scott.issue_qty (1950, 40);

calling subprograms from other programs *once a subprogram is created, it can be invoked by other programs. *example : --calling calc_int procedure : begin : calc_int (x, y); : end;

-- calling calc_int function : begin : variable1 := calc_int (x, y); -- or if calc_int (x, y) = …….. then : end;

example 2 : declare v_empno emp.empno%type; v_class varchar2 (10); begin : v_empno := 1234; v_class := sal_class (v_empno); : end;

dropping a subprogram *if a subprogram is not required it can be deleted using drop command *syntax: drop procedure | function <procedure_name | ;

stored subprograms *stored subprogram is created using create command. *stored subprogram is stored as a separate database object

recompiling subprograms *if the subprogram refers to some non-existing objects or has some errors, it will be created with compilation errors. *after fixing the problems you have to recompile the subprogram.

*syntax : alter procedure | function <procedure_name> | compile;

packages *a package is a database object that groups logically related pl/sql objects *pl/sql objects that can be grouped in a package are *procedure, functions *variables, constants, etc. *a package encapsulates related procedures, functions, associated cursors and variables together as a logical unit in the database. *the entire package is loaded into memory when a procedure, within the package, is called for the first time. *this reduces unnecessary disk i/o and network traffic. *there is a considerable performance improvement because of this encapsulation.

packages components *packages are made of two components *package specification and *package body

*the package specification is the interface to the package and has declarative statements. *the package body contains implementation of procedures and functions.

creating a package *a package comprises of the package specifications and the package body. *the package specification and body are created and stored separately in the data dictionary. *you can declare program objects such as procedures, functions, variables, constants, cursors etc. in the package specifications. *oracle declares these objects as public objects. *these objects can be referenced from outside the package

as well as by objects inside the package. *package specification contains information about the contents of the package. *syntax : create [or replace] package <package_name> as /* declaration of global variables, constants (if any); cursors; procedures & functions; */ end [<package_name>];

creating a package body

*you can create a package body only after you have created the package specifications. *the package body is used to define the code for the procedures, functions specified in the package specification. *you can declare objects in the package body also. *these objects are known as private objects and can be referenced only inside the package. *the package body is optional.

*syntax : create [or replace] package body <package_name> as /* private type and object declarations; subprogram bodies ; */ begin …….. code ; end;

*example : create a package employee which can be used to manipulate the employee’s records in the emp table. *functions : ins_employee

*procedures : del_employee and raise_salary *exception : salary_missing

create package employee as /* global declarations */ salary_missing exception; /* global functions declarations */ function ins_employee ( p_ename p_job p_mgr p_sal p_comm p_deptno return number;

char, char, number, number, number, number )

/* global procedures declarations */ procedure del_employee (p_empno number); procedure raise_salary (p_empno number, incr number); end employee; --package specification ends here

create package body employee as /* define the local variable */ v_number_joined number := 0; /* define the function ins_employee */ function ins_employee ( p_ename char, p_job char, p_mgr number, p_sal number, p_comm number, p_deptno number ) return number is new_empno number; /* local variable */ begin /* it is assumed that the sequence empno_seq is already existing */ select empno_seq.nextval into new_empno from dual; insert into emp values (new_empno, p_ename, p_job, p_mgr, sysdate, p_sal, p_comm, p_deptno ); v_number_joined := v_number_joined + 1; return new_empno; end ins_ employee; procedure del_employee ( p_empno number ) is begin delete from emp where empno = p_empno; end del_employee; procedure raise_salary ( p_empno number, p_incr number) is v_current_sal number; begin select sal into v_current_sal from emp where empno = p_empno; if v_current_sal is null then raise salary_missing; else update emp set sal=sal + p_incr where empno = p_empno; end if;

exception when salary_missing then raise_application_error (-20230, `salary is null’); end raise_salary; end employee; /* end of the package body employee */

referencing the contents of a package *a package is a library of related procedures, functions etc. *the contents of a package can be referred from *a subprogram *an application program *an oracle tool *the objects declared in a package need to be qualified with package name using dot notation. *syntax : package_name.package_object_name;

referencing a packaged object from a subprogram

*example : referencing procedure deposit_proc in package acct_transfer from a procedure account. procedure account (acct_no number) as begin : acct_transfer.deposit_proc(acct_no, trans_amt); : end;

referencing a packaged object from an oracle tool *example : referencing procedure deposit_proc from sql*plus prompt. *sql>execute acct_transfer.deposit_proc(acct_no, trans_amt);

replacing a package *you can’t modify the specification of the body of a package. *you need to redefine the package along with changes

incorporated. *use replace option with create statement. *syntax : create or replace [package | package body] <package_name> as [package_specification> | <package_body>];

dropping a package *you can drop a package from the database when the package is no longer required. *use drop statement. *syntax : drop [package | package body] <package_name>;

overloading subprograms *inside a package, procedures and functions can be overloaded. *this means there can be more than one procedures or functions with same name, but with different parameters. create or replace package emp_package is procedure addnewemp ( p_empno emp.empno% type, p_ename emp.ename%type, p_deptno emp.deptno%type ); procedure addnewemp ( p_empno emp.empno%type, p_ename emp.ename%type; p_dname dept.dname%type ); end emp_package; create or replace package body emp_package is --procedure to add a new employee with department number procedure addnewemp ( p_empno emp.empno%type;

begin

end;

p_ename p_deptno

emp.ename%type; emp.deptno%type ) is

insert into emp (empno, ename, deptno) values (p_empno, p_ename, p_deptno); commit;

--procedure to add a new employee with department name procedure addnewemp ( p_empno emp.empno%type; p_ename emp.ename%type; p_dname dept.dname%type ) is v_deptno emp.deptno%type; begin select deptno into v_deptno from dept where dname = p_dname; insert into emp (empno, ename, deptno) values (p_empno, p_ename, v_deptno); commit; end; end emp_package;