PL/SQL

A PL/SQL code snippet.

PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and datatypes are similar to that of Ada. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.

History and support status

PL/SQL (version 1.0) was first introduced with SQL*Forms v3.0. PL/SQL version 1.1 was later included with the Developer/2000 Release 1 tool set.

In 1991 PL/SQL was for the first time included with the Oracle database as part of the Transaction Processing Option for Oracle 6 and later Oracle 7's Procedural Option.

PL/SQL version 2.0 shipped with Oracle 7.0, PL/SQL version 2.1 with Oracle 7.1, PL/SQL version 2.2 with Oracle 7.2 and PL/SQL version 2.3 with Oracle 7.3. Starting with Oracle 8, PL/SQL's version numbers were syncronized with the database version numbers.

Today PL/SQL is a standard feature (not a cost option anymore) of the database and one of Oracle's premier development languages.

Interestingly enough, PL/SQL was not Oracle's first attempt at a procedural language for its database. In the early to mid-80's there was a product called SPUFI (Sql Procedural User Friendly Interface). It was 95% functional but had serious bugs and given other priorities, Oracle decided to shelve the effort, so SPUFI never made it out of beta.

Operators

Arithmetic Operators

• Addition: +
• Subtraction: -
• Multiplication: *
• Division: /
• Power: **

Examples:

Give an employee a 5% raise:

v_salary := v_salary + v_salary * .05;

Determines an employee's after tax wage:

v_sal_after_tax := v_wage - v_tax;

Order Precedence of Arithmetic Operators

1. +,- (as UNARY operators) - Identity, Negation
2. *,/ "Multiplication, Division"
3. +,- (as BINARY operators) "Addition, Subtraction"

Examples:

SELECT 8+9*4 AS example1
  FROM dual;

EXAMPLE1
--------
44

The incorrect answer would be 68 if you applied your arithmetic from left to right ignoring precedence.

SELECT -7+3*5 AS example1
  FROM dual;

EXAMPLE1
--------
8

SELECT -2*6+1 AS example1
  FROM dual;

EXAMPLE1
--------
-11

Comparison Operators

• Greater Than: >
• Greater Than or Equal To: >=
• Less Than: <
• Less Than or Equal to: <=
• Equivalence: =
• Inequality: != ^= <> ¬= (depends on platform)

Example:

IF v_salary > 40000 THEN
  ...
END IF;

String Operators

• Concatenate: ||

v_newstr := v_str1 || ', ' || v_str2;

Date Operators

• Addition: +
• Subtraction: -

v_tomorrow := SYSDATE + 1;

Types

Basic PL/SQL types

Scalar type (as defined in package STANDARD): Number, Char, Varchar2, Boolean, BINARY_INTEGER, PLS_INTEGER, LONG and LONG RAW, Date, Timestamp (including intervals).

LOB (Large OBject) types to store large amounts of unstructured data: CLOB, NCLOB, BLOB and BFILE.

Composite types (user-defined): TABLE, RECORD, NESTED TABLE and VARRAY.

%TYPE

Declaring a variable with the same type as a database column. This provides data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

The syntax for anchored type declarations is:

<var-name> <obj>%type [not null][:= <init-val>];

for example

DECLARE
  v_title Books.title%type;   /*  name is defined as the same type as column 'title' of table  Books */

and

DECLARE
  v_commission number(5,2) := 12.5;
  v_var1 v_commission%type;   /*  x is defined as the same type as variable 'v_commission' */

Notes:

• Anchored variables allow for the automatic synchronization of the type of anchored variable with the type of <obj> when there is a change to the <obj> type.
• Anchored types are evaluated at compile time, so recompile the program to reflect the change of <obj> type in the anchored variable.

%ROWTYPE

Declaring a record with the same columns and types as a database table. This provides data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

for example

DECLARE
  v_books_rec Books%rowtype;

Collections

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar data types. Each element has a unique subscript that determines its position in the collection.

--Define a PL/SQL record type representing a book:
TYPE book_rec IS RECORD 
   (title                   book.title%TYPE,
    author                  book.author_last_name%TYPE,
    year_published          book.published_date%TYPE));

--define a PL/SQL table containing entries of type book_rec:
Type book_rec_tab IS TABLE OF book_rec%TYPE
     INDEX BY BINARY_INTEGER;

v_book_rec  book_rec%TYPE;
v_book_rec_tab book_rec_tab%TYPE;
...
v_book_rec := v_book_rec_tab(5);
find_authors_books(v_book_rec.author);
...

Some of the reasons why one should use collections:

• Dramatically faster execution speed, thanks to transparent performance boosts including a new optimizing compiler, better integrated native compilation, and new data types that help for number-crunching applications.

• The FORALL statement, made even more flexible and useful. For example, FORALL now supports nonconsecutive indexes.

• Regular expressions are available in PL/SQL in the form of three new functions (REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR) and the REGEXP_LIKE operator for comparisons.

• Collections, improved to include such things as collection comparison for equality and support for set operations on nested tables.

Control Structures

Conditional Control

IF-THEN-ELSE

Example

IF v_salary > 40000 THEN
  ...
ELSIF v_salary < 1000 THEN
  ...
ELSE
  ...
END IF;

CASE statement

From Oracle 9i PL/SQL supports the CASE statement directly. Example:

CASE v_grade
  WHEN 'A' THEN dbms_output.put_line('Excellent');
  WHEN 'B' THEN dbms_output.put_line('Very Good');
  WHEN 'C' THEN dbms_output.put_line('Good');
  WHEN 'D' THEN dbms_output.put_line('Fair');
  WHEN 'F' THEN dbms_output.put_line('Poor');
  ELSE dbms_output.put_line('Error: No such grade');
END CASE

Iterative Control

Simple LOOP

Example:

i := 1;
LOOP
  i := i + 1;
  dbms_output.put_line('i = '|| i);
  EXIT WHEN i > 10;
END LOOP;

FOR-LOOP

Example:

FOR i IN 1..10 LOOP
  CONTINUE WHEN (i = 5);
  dbms_output.put_line('i = '|| i);
END LOOP;

WHILE-LOOP

Example:

WHILE i < 10 LOOP
  i := 1 + 1;
  dbms_output.put_line('i = '|| i);
END LOOP;

Notes:

• To exit a loop use the EXIT WHEN statement;
• To skip to the next iteration of the loop, use the CONTINUE WHEN statement (Oracle 11g and above).

Sequential Control

The GOTO statement. Not recommended to use.

goto the_end;

<<the_end>>
dbms_output.put_line ('The End.');

Cursors

Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors:

Implicit Cursors

PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. An implicit cursor is of the variety:

SELECT employee_fname
  INTO :memployee_fname
  FROM employee
 WHERE employee_no = 100

Explicit Cursors

For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows:

DECLARE
 CURSOR c1 IS
 SELECT employee_no, employee_fname 
   FROM employee
  WHERE deptno = 20;
...

When to use

A SELECT INTO clause will make two accesses to the database when it queries in a value, to determine whether or not it should raise the TOO_MANY_ROWS exception. With an explicit cursor, one fetch can be explicitly stated to be performed, assuming somewhat correctly that there are no duplicate primary keys returned. Thus on average, an implicit cursor will make two accesses to the database on a single row query while an explicit cursor will make only one. This could be a significant performance advantage, especially in client-server environments.

Anonymous PL/SQL blocks

An anonymous PL/SQL block is a block of code that is not stored in the database. When submitted to the database, the code will be executed immediately. An anonymous PL/SQL block always starts with DECLARE or BEGIN

Example:

BEGIN
  dbms_output.put_line('Hi Ma, I can write PL/SQL');
END;
/

Stored PL/SQL blocks

Functions

A function returns a value to the caller.

The syntax for a function is

CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
    return [return value]

    [EXCEPTION
        exception_section]
END [procedure_name];

for example:

CREATE OR REPLACE FUNCTION to_date_check_null(dateString IN VARCHAR2, dateFormat IN VARCHAR2) 
RETURN DATE IS
BEGIN 
  IF dateString IS NULL THEN
     return NULL;
  ELSE
     return to_date(dateString, dateFormat);
  END IF;
END to_date_check_null;
/

Procedures

A procedure differs from a function in that it does not return a value to the caller.

The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [IN|OUT|IN OUT] [DEFAULT value] [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
    [EXCEPTION
        exception_section]
END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

• IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
• OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
• IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

The following is a simple example of a procedure:

CREATE OR REPLACE PROCEDURE GetNumberOfStudents
   ( courseId IN number, numberOfStudents OUT number )
IS
  /* There are better ways to compute the number of students, 
     but this is a good opportunity to show a cursor in action          
  */
  cursor student_cur is
  select studentId, studentName
    from course
   where course.courseId = courseId;
  student_rec    student_cur%ROWTYPE;
BEGIN
  LOOP
      FETCH student_cur INTO student_rec;
      EXIT WHEN student_cur%NOTFOUND;
      numberOfStudents := numberOfStudents + 1;
  END LOOP;
  CLOSE student_cur;
EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20001,'Error encountered - '||SQLCODE||' - '||SQLERRM);
END GetNumberOfStudents;
/

Packages

Stored functions and procedures can be packaged into a larger unit, essentially a library of procedures and functions, called a package. Public declarations are put in the PACKAGE SPECIFICATON, or header, while the procedure or function code, or private code, is put in a PACKAGE BODY with the same name.

Example:

CREATE OR REPLACE PACKAGE student_pkg AS
  FUNCTION to_date_check_null(dateString IN VARCHAR2, dateFormat IN VARCHAR2)
    RETURN DATE;
  PROCEDURE GetNumberOfStudents(
    courseId IN number, numberOfStudents OUT number);
END;
/

CREATE OR REPLACE PACKAGE BODY student_pkg AS

  FUNCTION to_date_check_null(dateString IN VARCHAR2, dateFormat IN VARCHAR2)
  RETURN DATE IS
  BEGIN
    ...
  END;

  PROCEDURE GetNumberOfStudents(
     courseId IN number, numberOfStudents OUT number)
  IS
  BEGIN
    ...
  END;
 
END;
/

In addition to performing the function of being a common library of procedures and functions there are three aspects of Oracle packages that are unique. They are:

1. The initialization section. A package's initialization section is executed once the first time any portion of a package is used within a session. This can be valuable for setting session variables.
2. Packages, by default, retain the values of variables within a session after execution of a procedure or function, within the package, is completed. This behavior can be modified to be identical to that of procedures and functions by using PRAGMA SERIALLY_REUSABLE.
3. Packages can be overloaded. A package can contain multiple procedures and functions with the same name provided the parameters passed in and returned are different.

Exceptions

Oracle provides some predefined exceptions that can be used, including: NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX, VALUE_ERROR, ZERO_DIVIDE, INVALID_CURSOR, NOT_LOGGED_ON, etc.

Here is an example of how to define a custom expection:

DECLARE
  view_doesnot_exist EXCEPTION;
  PRAGMA             EXCEPTION_INIT(view_doesnot_exist, -942);
BEGIN
  ...
EXCEPTION
  WHEN view_doesnot_exist THEN null;
END;

Also see

• PL/SQL FAQ
• PL/SQL code examples

External links

• Oracle's PL/SQL Homepage
• PLNet.org - An open source repository for PL/SQL developers
• The DMOZ PL/SQL Directory