Introduction of Procedures and Functions in PL/SQL

PL/SQL provides sophisticated language constructs to program procedures and functions as stand-alone PL/SQL blocks. They can be called from other PL/SQL blocks, other procedures
and functions. The syntax for a procedure definition is

create [or replace] procedure [()] is

begin

[exception
]
end [];

A function can be specified in an analogous way

create [or replace] function [()]
return is
. . .
The optional clause or replace re-creates the procedure/function. A procedure can be deleted using the command drop procedure (drop function ). In contrast to anonymous PL/SQL blocks, the clause declare may not be used in procedure/ function definitions.

Valid parameters include all data types. However, for char, varchar2, and number no length and scale, respectively, can be specified. For example, the parameter number(6) results in a compile error and must be replaced by number. Instead of explicit data types, implicit types of the form %TYPE and %ROWTYPE can be used even if constrained declarations are referenced.

A parameter is specified as follows:
[IN | OUT | IN OUT] [{ := | DEFAULT} ]

The optional clauses IN, OUT, and IN OUT specify the way in which the parameter is used. The default mode for a parameter is IN. IN means that the parameter can be referenced inside the procedure body, but it cannot be changed. OUT means that a value can be assigned to the parameter in the body, but the parameter’s value cannot be referenced. IN OUT allows both assigning values to the parameter and referencing the parameter. Typically, it is sufficient to use the default mode for parameters.

Example: The subsequent procedure is used to increase the salary of all employees who work
in the department given by the procedure’s parameter. The percentage of the salary increase
is given by a parameter, too.

create procedure raise salary(dno number, percentage number DEFAULT 0.5) is
cursor emp cur (dept no number) is
select SAL from EMP where DEPTNO = dept no
for update of SAL;
empsal number(8);
begin
open emp cur(dno); – - Here dno is assigned to dept no
loop
fetch emp cur into empsal;
exit when emp cur%NOTFOUND;
update EMP set SAL = empsal ((100 + percentage)/100)
where current of emp cur;
end loop;
close emp cur;
commit;
end raise salary;

This procedure can be called from the SQL*Plus shell using the command

execute raise salary(10, 3);

If the procedure is called only with the parameter 10, the default value 0.5 is assumed as specified in the list of parameters in the procedure definition. If a procedure is called from a PL/SQL block, the keyword execute is omitted. Functions have the same structure as procedures. The only difference is that a function returns a value whose data type (unconstrained) must be specified.

Example:
create function get dept salary(dno number) return number is
all sal number;
begin
all sal := 0;
for emp sal in (select SAL from EMP where DEPTNO = dno
and SAL is not null) loop

all sal := all sal + emp sal.sal;
end loop;
return all sal;
end get dept salary;

In order to call a function from the SQL*Plus shell, it is necessary to first define a variable to which the return value can be assigned. In SQL*Plus a variable can be defined using the command variable ;, for example, variable salary
number. The above function then can be called using the command execute :salary :=
get dept salary(20); Note that the colon “:” must be put in front of the variable.