Procedural Programming in Oracle

Declarative languages can be very powerful and reliable, but sometimes it is easier to think about things procedurally. One way to do this is by using a procedural language in the database client. Like using JDBC APIs to talk to the Oracle DB in Java language or the Pro*C package which comes with Oracle for writing the client applications in C/C++ language or writing stored procedures, functions and triggers in the PL/SQL language which is an extension for procedural programming to SQL provided by Oracle.

There are no clean ways in standard SQL to say "do this just for the first N rows" or "do something special for a particular row if its data match a certain pattern". Suppose that you have a million rows in your news table, you want five, but you can only figure out which five with a bit of procedural logic. Does it really make sense to drag those million rows of data all the way across the network from the database server to your client application and then throw out 999,995 rows?

Or suppose that you're querying a million-row table and want the results back in a strange order. Does it make sense to build a million-row data structure in your client application, sort them in the client program, then return the sorted rows to the user?

Eg: Visit and search for "benzene". Note that there are 328 chemicals whose names contain the string "benzene":

select count(*)
from chemical
where upper(edf_chem_name) like upper('%benzene%');


The way we want to display them is

  • exact matches on top
  • line break
  • chemicals that start with the query string
  • line break
  • chemicals that contain the query string

Within each category of chemicals, we want to sort alphabetically. However, if there are numbers or special characters in front of a chemical name, we want to ignore those for the purposes of sorting.
Can you do all of that with one query? And have them come back from the database in the desired order?

You could if you could make a procedure that would run inside the database. For each row, the procedure would compute a score reflecting goodness of match. To get the order correct, you need only ORDER BY this score. To get the line breaks right, you need only have your application program watch for changes in score. For the fine tuning of sorting equally scored matches alphabetically, just write another procedure that will return a chemical name stripped of leading special characters, then sort by the result. Here's how it looks:

-- Your SQL query in your client application
select edf_chem_name,
          as match_score
from chemical
where upper(edf_chem_name) like upper('%benzene%');
order by score_chem_name_match_score(upper(edf_chem_name),upper('benzene')),

We specify the procedure score_chem_name_match_score to take two arguments: one the chemical name from the current row, and one the query string from the user. It returns 0 for an exact match, 1 for a chemical whose name begins with the query string, and 2 in all other cases (remember that this is only used in queries where a LIKE clause ensures that every chemical name at least contains the query string. Once we defined this procedure, we'd be able to call it from a SQL query, the same way that we can call built-in SQL functions such as upper.

-- Server side stored procedure 1.
create or replace function score_chem_name_match_score
 (chem_name IN varchar, query_string IN varchar)
return integer
  IF chem_name = query_string THEN
     return 0;
  ELSIF instr(chem_name,query_string) = 1 THEN
     return 1;
     return 2;
END score_chem_name_match_score;

Notice that PL/SQL is a strongly typed language. We say what arguments we expect, whether they are IN or OUT, and what types they must be. We say that score_chem_name_match_score will return an integer. We can say that a PL/SQL variable should be of the same type as a column in a table:

-- Server side stored procedure 2.
create or replace function score_chem_name_for_sorting (chem_name IN varchar)
return varchar
  stripped_chem_name chem_hazid_ref.edf_chem_name%TYPE;
  stripped_chem_name := ltrim(chem_name,'1234567890-+()[],'' #');
  return stripped_chem_name;
END score_chem_name_for_sorting;

The local variable stripped_chem_name is going to be the same type as the edf_chem_name column in the chem_hazid_ref table.

IMO it's a good idea to do transaction control (BEGIN TRANSACTION/ROLLBACK/COMMIT) solely in stored procedures, and not in a client program. The situation you want to avoid at all costs is a client opening a transaction, and then crashing -- with your database holding a transaction open (and pages locked) until (some time later) the operating system figures out that the TCP/IP connection to the client has closed, and tells the database about it (which then rolls back the transaction). This is more of an issue with PC clients (and users hitting Ctrl-Alt-Del) as opposed to a webserver program.
Also, it should be mentioned that putting SQL into stored procedures makes it run faster, as it can be "precompiled" (some of the interpretation steps done ahead of time, such as parsing and generating a query plan). This is true of Sybase and (I would think) of Oracle etc. as well.

PL/SQL is a block-structured language. Each block builds a (named) program unit, and blocks can be nested. Blocks that build a procedure, a function, or a package must be named. A PL/SQL block has an optional declare section, a part containing PL/SQL statements, and an optional exception-handling part. Thus the structure of a PL/SQL looks as follows (brackets[ ] enclose optional parts):

[<Block header>]
<User defined exceptions>]
<PL/SQL statements>
<Exception handling>]

The block header speci es whether the PL/SQL block is a procedure, a function, or a package. If no header is speci ed, the block is said to be an anonymous PL/SQL block. Each PL/SQL block again builds a PL/SQL statement. Thus blocks can be nested like blocks in conventional programming languages. The scope of declared variables (i.e., the part of the program in which one can refer to the variable) is analogous to the scope of variables in programming languages such as C or Pascal.

Constants, variables, cursors, and exceptions used in a PL/SQL block must be declared in the declare section of that block. Variables and constants can be declared as follows:

<variable name> [constant] <data type> [not null] [:= <expression>];

Valid data types are SQL data types (see Section 1.1) and the data type boolean. Boolean data may only be true, false, or null. The not null clause requires that the declared variable must always have a value di erent from null. <expression> is used to initialize a variable. If no expression is speci ed, the value null is assigned to the variable. The clause constant states that once a value has been assigned to the variable, the value cannot be changed (thus the variable becomes a constant). Example:

hire date date; /* implicit initialization with null */
job title varchar2(80) := 'Salesman';
emp found boolean; /* implicit initialization with null */
salary incr constant number(3,2) := 1.5; /* constant */
: : :
begin : : : end;

Instead of specifying a data type, one can also refer to the data type of a table column (so-called anchored declaration). For example, EMP.Empno%TYPE refers to the data type of the column Empno in the relation EMP. Instead of a single variable, a record can be declared that can store a complete tuple from a given table (or query result). For example, the data type DEPT%ROWTYPE speci es a record suitable to store all attribute values of a complete row from the table DEPT.Such records are typically used in combination with a cursor. A eld in a record can be accessed using <record name>.<column name>, for example, DEPT.Deptno.

A cursor declaration speci es a set of tuples (as a query result) such that the tuples can be processed in a tuple-oriented way (i.e., one tuple at a time) using the fetch statement. A cursor declaration has the form

cursor <cursor name> [(<list of parameters>)] is <select statement>;

The cursor name is an undeclared identi er, not the name of any PL/SQL variable. A parameter has the form <parameter name> <parameter type>. Possible parameter types are char, varchar2, number, date and boolean as well as corresponding subtypes such as integer.Parameters are used to assign values to the variables that are given in the select statement.
Example: We want to retrieve the following attribute values from the table EMP in a tuple-oriented way: the job title and name of those employees who have been hired after a given date, and who have a manager working in a given department.

cursor employee cur (start date date, dno number) is
select JOB, ENAME from EMP E where HIREDATE > start date
and exists (select

 from EMP
where E.MGR = EMPNO and DEPTNO = dno);

If (some) tuples selected by the cursor will be modi ed in the PL/SQL block, the clause for update[(<column(s)>)] has to be added at the end of the cursor declaration. In this case selected tuples are locked and cannot be accessed by other users until a commit has been issued. Before a declared cursor can be used in PL/SQL statements, the cursor must be opened, and after processing the selected tuples the cursor must be closed.

Exceptions are used to process errors and warnings that occur during the execution of PL/SQL statements in a controlled manner. Some exceptions are internally de ned, such as ZERO DIVIDE. Other exceptions can be speci ed by the user at the end of a PL/SQL block. User de ned ex-ceptions need to be declared using <name of exception> exception.

PL/SQL uses a modi ed select statement that requires each selected tuple to be assigned to a record (or a list of variables).There are several alternatives in PL/SQL to a assign a value to a variable. The most simple way to assign a value to a variable is

counter integer := 0;
: : :
counter := counter + 1;

Values to assign to a variable can also be retrieved from the database using a select statement

select <column(s)> into <matching list of variables>
from <table(s)> where <condition>;

It is important to ensure that the select statement retrieves at most one tuple ! Otherwise it is not possible to assign the attribute values to the speci ed list of variables and a run-time error occurs. If the select statement retrieves more than one tuple, a cursor must be used instead. Furthermore, the data types of the speci ed variables must match those of the retrieved attribute values. For most data types, PL/SQL performs an automatic type conversion (e.g., from integer to real). Instead of a list of single variables, a record can be given after the keyword into. Also in this case, the select statement must retrieve at most one tuple !

employee_rec EMP%ROWTYPE;
max sal EMP.SAL%TYPE;
into employee_rec
from EMP where EMPNO = 5698;
select max(SAL) into max sal from EMP;
: : :

PL/SQL provides while-loops, two types of for-loops, and continuous loops. Latter ones are used in combination with cursors. All types of loops are used to execute a sequence of statements multiple times. The speci cation of loops occurs in the same way as known from imperative programming languages such as C or Pascal.

A while-loop has the pattern
[<< <label name> >>]
while <condition> loop
<sequence of statements>;
end loop [<label name>] ;

A loop can be named. Naming a loop is useful whenever loops are nested and inner loops are completed unconditionally using the exit <label name>; statement. Whereas the number of iterations through a while loop is unknown until the loop completes, the number of iterations through the for loop can be speci ed using two integers.

[<< <label name> >>]
for <index> in [reverse] <lower bound>..<upper bound> loop
<sequence of statements>
end loop [<label name>] ;

The loop counter <index> is declared implicitly. The scope of the loop counter is only the for loop. It overrides the scope of any variable having the same name outside the loop. Inside the for loop, <index> can be referenced like a constant. <index> may appear in expressions, but one cannot assign a value to <index>. Using the keyword reverse causes the iteration to proceed downwards from the higher bound to the lower bound.

Processing Cursors: Before a cursor can be used, it must be opened using the open statement
open <cursor name> [(<list of parameters>)] ;
The associated select statement then is processed and the cursor references the rst selected tuple. Selected tuples then can be processed one tuple at a time using the fetch command
fetch <cursor name> into <list of variables>;
The fetch command assigns the selected attribute values of the current tuple to the list of variables. After the fetch command, the cursor advances to the next tuple in the result set. Note that the variables in the list must have the same data types as the selected values. After all tuples have been processed, the close command is used to disable the cursor.
close <cursor name>;
The example below illustrates how a cursor is used together with a continuous loop:

cursor emp cur is select * from EMP;
emp rec EMP%ROWTYPE;
emp sal EMP.SAL%TYPE;
open emp cur;
fetch emp cur into emp rec;
exit when emp cur%NOTFOUND;
emp sal := emp rec.sal;
<sequence of statements>
end loop;
close emp cur;
: : :

Each loop can be completed unconditionally using the exit clause:
exit [<block label>] [when <condition>]
Using exit without a block label causes the completion of the loop that contains the exit statement. A condition can be a simple comparison of values. In most cases, however, the condition refers to a cursor. In the example above, %NOTFOUND is a predicate that evaluates to false if the most recent fetch command has read a tuple. The value of <cursor name>%NOTFOUND is null before the rst tuple is fetched. The predicate evaluates to true if the most recent fetch failed to return a tuple, and false otherwise. %FOUND is the logical opposite of %NOTFOUND.
Cursor for loops can be used to simplify the usage of a cursor:
[<< <label name> >>]
for <record name> in <cursor name>[(<list of parameters>)] loop
<sequence of statements>
end loop [<label name>];
A record suitable to store a tuple fetched by the cursor is implicitly declared. Furthermore, this loop implicitly performs a fetch at each iteration as well as an open before the loop is entered and a close after the loop is left. If at an iteration no tuple has been fetched, the loop is automatically terminated without an exit.
It is even possible to specify a query instead of <cursor name> in a for loop:
for <record name> in (<select statement>) loop
<sequence of statements>
end loop;
That is, a cursor needs not be speci ed before the loop is entered, but is de ned in the select statement.
for sal rec in (select SAL + COMM total from EMP) loop
: : : ;
end loop;
total is an alias for the expression computed in the select statement. Thus, at each iteration only one tuple is fetched. The record sal rec, which is implicitly de ned, then contains only one entry which can be accessed using sal Aliases, of course, are not necessary if only attributes are selected, that is, if the select statement contains no arithmetic operators or aggregate functions.
For conditional control, PL/SQL o ers if-then-else constructs of the pattern
if <condition> then <sequence of statements>
[elsif ] <condition> then <sequence of statements>
: : :
[else] <sequence of statements> end if ;

Starting with the rst condition, if a condition yields true, its corresponding sequence of statements is executed, otherwise control is passed to the next condition. Thus the behavior of this type of PL/SQL statement is analogous to if-then-else statements in imperative programming languages.
Except data de nition language commands such ascreate table, all types of SQL statements can be used in PL/SQL blocks, in particular delete, insert, update, and commit. Note that in PL/SQL only select statements of the type select <column(s)> into are allowed, i.e.,selected attribute values can only be assigned to variables (unless the select statement is used in a subquery). The usage of select statements as in SQL leads to a syntax error. If update ordelete statements are used in combination with a cursor, these commands can be restricted tocurrently fetched tuple. In these cases the clause where current of<cursor name> is addedas shown in the following example.
Example: The following PL/SQL block performs the following modi cations: All employees having 'KING' as their manager get a 5% salary increase.

manager EMP.MGR%TYPE;
cursor emp cur (mgr no number) is
select SAL from EMP
where MGR = mgr no
for update of SAL;
select EMPNO into manager from EMP
where ENAME = 'KING';
for emp rec in emp cur(manager) loop
update EMP set SAL = emp rec.sal * 1.05 -- implicit fetch
where current of emp cur;
end loop;

Remark: Note that the record emp rec is implicitly de ned.

A PL/SQL block may contain statements that specify exception handling routines. Each error or warning during the execution of a PL/SQL block raises an exception. One can distinguish between two types of exceptions:
 system defined exceptions
 user defined exceptions (which must be declared by the user in the declaration part of a block where the exception is used/implemented)

System de ned exceptions are always automatically raised whenever corresponding errors or warnings occur. User de ned exceptions, in contrast, must be raised explicitly in a sequence of statements using raise <exception name>. After the keyword exception at the end of a block, user de ned exception handling routines are implemented. An implementation has the pattern

when <exception name> then <sequence of statements>;

The most common errors that can occur during the execution of PL/SQL programs are handled by system de ned exceptions. The table below lists some of these exceptions with their names and a short description.

Exception name                                     Number                                 Remark
CURSOR ALREADY OPEN             ORA-06511                         You have tried to open a cursor which is already open
INVALID CURSOR                          ORA-01001                          Invalid cursor operation such as fetching from a closed cursor
NO DATA FOUND                           ORA-01403                         A select : : : into or fetch statement returned no tuple
TOO MANY ROWS                         ORA-01422                         A select : : : into statement returned more than one tuple
ZERO DIVIDE                                   ORA-01476                         You have tried to divide a number by 0


emp sal EMP.SAL%TYPE;
too high sal exception;
select EMPNO, SAL into emp no, emp sal
from EMP where ENAME = 'KING';
if emp sal * 1.05 > 4000 then raise too high sal
else update EMP set SQL : : :
end if ;
when NO DATA FOUND { {no tuple selected
then rollback;
when too high sal then insert into high sal emps values(emp no);

After the keyword when a list of exception names connected with or can be speci ed. The last when clause in the exception part may contain the exception name others. This introduces the default exception handling routine, for example, a rollback.

If a PL/SQL program is executed from the SQL*Plus shell, exception handling routines may contain statements that display error or warning messages on the screen. For this, the procedure raise application error can be used. This procedure has two parameters <error number> and <message text>. <error number> is a negative integer de ned by the user and must range between -20000 and -20999. <error message> is a string with a length up to 2048 characters.The concatenation operator "||" can be used to concatenate single strings to one string. In order to display numeric variables, these variables must be converted to strings using the function to char. If the procedure raise application error is called from a PL/SQL block, processing the PL/SQL block terminates and all database modi cations are undone, that is, an implicit rollback is performed in addition to displaying the error message.
if emp sal * 1.05 > 4000
then raise application error(-20010, 'Salary increase for employee with Id '|| to char(Emp no) || ' is too high');

Procedures and Functions
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 de nition is

create [or replace] procedure <procedure name> [(<list of parameters>)] is
<sequence of statements>
<exception handling routines>]
end [<procedure name>];

A function can be speci ed in an analogous way

create [or replace] function <function name> [(<list of parameters>)]
return <data type> is
: : :

The optional clause or replace re-creates the procedure/function. A procedure can be deleted using the command drop procedure <procedure name> (drop function <function name>). In contrast to anonymous PL/SQL blocks, the clause declare may not be used in procedure/function de nitions. Valid parameters include all data types. However, for char, varchar2, and number no length and scale, respectively, can be speci ed. 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 speci ed as follows:
<parameter name> [IN | OUT | IN OUT] <data type> [{ := | DEFAULT} <expression>]

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 su
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.

set echo on

create or replace procedure raise_salary (DNUM in number, PERCENT in number) as
cursor EMP_CUR is
  select EMPNO, SAL from EMP
  where DEPTNO = DNUM
  for update of SAL;
ENUM number(4);
ESAL number;
  open EMP_CUR;
        fetch EMP_CUR into ENUM, ESAL;
        exit when EMP_CUR%NOTFOUND;
        update EMP set sal = (ESAL * (1+(PERCENT / 100)))
        where current of EMP_CUR;
  end loop;
  close EMP_CUR;
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 speci ed in the list of parameters in the procedure de nition. If a procedure is called from a PL/SQL block, the keyword execute is omitted. Functions have the same structure as procedures. The only di erence is that a function returns a value whose data type (unconstrained) must be speci ed.

create function get dept salary(dno number) return number is
all sal number;
all sal := 0;
for emp sal in (select SAL from EMP where DEPTNO = dno and SAL is not null)
    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 rst de ne a variable to which the return value can be assigned. In SQL*Plus a variable can be de ned using the command variable <variable name> <data type>;, 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 thevariable. Further information about procedures and functions can be obtained using the help command in the SQL*Plus shell, for example, help [create] function, help subprograms, help stored subprograms.

Packages:PL/SQL supports the concept of modularization by which modules and other constructs can be organized into packages. A package consists of a package speci cation and a package body. The package speci cation de nes the interface that is visible for application programmers, and the package body implements the package speci cation (similar to header- and source les in the programming language C).

create package manage_employee as -- package specification
   function hire_emp (name varchar2, job varchar2, mgr number, hiredate date,
                      sal number, comm number default 0, deptno number)
            return number;
   procedure fire_emp (emp_id number);
   procedure raise_sal (emp_id number, sal_incr number);
end manage_employee;

create package body manage_employee as
   function hire_emp (name varchar2, job varchar2, mgr number, hiredate date,
                      sal number, comm number default 0, deptno number)
            return number is
   -- Insert a new employee with a new employee Id
   new_empno number(10);
     select emp_sequence.nextval into new_empno from dual;
     insert into emp values(new_empno, name, job, mgr, hiredate,
                            sal, comm, deptno);
     return new_empno;
   end hire_emp;

   procedure fire_emp(emp_id number) is
   -- deletes an employee from the table EMP
     delete from emp where empno = emp_id;
     if SQL%NOTFOUND then -- delete statement referred to invalid emp_id
     raise_application_error(-20011, 'Employee with Id ' ||
                             to_char(emp_id) || ' does not exist.');
     end if;
   end fire_emp;

   procedure raise_sal(emp_id number, sal_incr number) is
   -- modify the salary of a given employee
     update emp set sal = sal + sal_incr
     where empno = emp_id;
     if SQL%NOTFOUND then
     raise_application_error(-20012, 'Employee with Id ' ||
                             to_char(emp_id) || ' does not exist');
     end if;
   end raise_sal;
end manage_employee;