What is PL / SQL ?

PL/SQL is the procedural extension to SQL. With PL/SQL you can use SQL datatypes and commands. The big advantage of using PL/SQL is that you can send an entire block of statements to the database at one time. PL/SQL stored subprograms are compiled once and stored in excutable form in the database for future use. PL/SQL offers exception handling and support for object-oriented programming. Applications written in PL/SQL are platform independent. With PL/SQL you have access to several packages of software provided by Oracle.

PL/SQL Block

The fundamental unit of code in PL/SQL programming is a block. A block maybe completely nested in another block but blocks may not overlap. This is the basic structure of a block:

[DECLARE]

BEGIN

[EXCEPTION]

END;

Comments

Single line comments begin with -- and can be any where in the program. Everything after the -- to the end of the line is taken as a comment. Multi-line comments start with /* and ends with */

Identifiers

PL/SQL is case in-sensitive. It recognizes most of the ASCII characters. The maximum length of an identifier is 30 characters. An identifier must start with a letter and can be followed by at most 29 letters, digits, underscore, dollar sign, and number sign (#). An identifier may not be a reserved or key word.

A numeric literal can be an integer or floating point number.

2  3.14  -9.81E03

'John Doe'
'I''m on my way'

d_birth_day  DATE  := to_date ('19810101', 'YYYYMMDD');

To declare a constant the keyword CONSTANT is used. The value of the constant must be assigned when it is declared and cannot be changed within the program.

n_speed_of_light  CONSTANT NUMBER := 300000;

The %TYPE declaration allows you to assign a variable the same type as that for a certain column in a table. For example, let us say we have the following table: employees (emp_id, first_name, last_name). Then you can assign the type for a variable n_id to have the same type as emp_id like so:

n_id  employees.emp_id%TYPE;

The attribute %ROWTYPE allows you to assign a variable the datatype for an entire row in a table.

DECLARE
  emp_rec employees%ROWTYPE;

BEGIN
  emp_rec.emp_id := 101;
  emp_rec.first_name := 'John';
  emp_rec.last_name := 'Doe';
END;

BEGIN
  select * into emp_rec
  from employees
  where emp_id = 101;
END;

A variable must be declared before it can be accessed. A variable declared in the DECLARE section of the block is accessible within that block and any nested block. If you have a local variable that has the same name as a global variable then you have access only to the local variable.

Operators

Conditionals

There are two forms for the conditional IF and CASE. There are several variations of both.

IF (condition) THEN
  ...
END IF;             -- make sure that there are two words END and IF

IF (condition) THEN
  ...
ELSE
  ...
END IF;

IF (condition1) THEN
  ...
ELSIF (condition2) THEN  -- contraction of ELSE and IF
  ...
ELSE
  ...
END IF;  

-- Simple Case
CASE v_digit
  WHEN '0' THEN n_number := 0;
  WHEN '1' THEN n_number := 1; 
  ELSE DBMS_OUTPUT.PUT_LINE ('Not Valid Binary Digit');
END CASE;

-- Searched Case
CASE 
  WHEN v_digit = '0' THEN n_number := 0;
  WHEN v_digit = '1' THEN n_number := 1; 
  ELSE DBMS_OUTPUT.PUT_LINE ('Not Valid Binary Digit');
END CASE;

Labeled Statement

Any statement or block can be labeled by placing an identifier before the block or statement. The identifier must be enclosed in two pairs of angle brackets and the usual rules for forming the identifier applies.

<<id_123>>
Statement / Block

Loops

There are thre main forms of the loop - Basic, While, and For. The Basic Loop behaves like do-until in Java. To exit a loop PL/SQL provides two constructs - EXIT and EXIT-WHEN. To exit the current iteration of the loop and start the next one we have - CONTINUE and CONITNUE-WHEN.

idx  NUMBER := 0;
LOOP
  ...
  idx := idx + 1;
  ...
  IF (idx < 3) THEN
    CONTINUE;
  END IF;
  ... 
  IF (idx = 10) THEN
    EXIT;
  END IF;
END LOOP;

LOOP
  ...
  idx := idx + 1;
  ...
  CONTINUE WHEN idx < 3;
  ...
  EXIT WHEN idx = 10;
END LOOP;

WHILE (condition) LOOP
  ...
END LOOP;

FOR i IN 1..10 LOOP
  ...
END LOOP;

-- Loop counter i is not defined outside the loop, 
-- nor can it be changed inside the loop.
-- The lower and upper bounds are numeric values.

-- To go in reverse order do
FOR i IN REVERSE 1..10 LOOP
  ...
END LOOP;

-- The loop counter is incremented and decremented by 1.
-- You cannot change it.

<<outer_loop>>
  FOR i IN 1..10 LOOP
    <<inner_loop>>
      FOR j IN 1..10 LOOP
        ...
        EXIT outer_loop WHEN (condition);
        ...
      END LOOP inner_loop;
    ...
  END LOOP outer_loop;
    

Collections

A collection is a group of elements of the same datatype. A collection is similar to arrays in other languages. To create a collection, you must first create a collection type and then declare a variable of that type. In PL/SQL all collections are one dimensional. But you can always define collections whose elements are themselves collections. There are three different types of collections:

• Associative array - is a set of key-value pairs, where each key is unique. The keys could be numbers or strings. If the keys are numbers, then these numbers need not be contiguous.
• Nested table - is like a one-dimensional array with an arbitrary number of elements. The indices are consecutive, starting at 1.
• Variable-size array (varray) - has a maximum size that you specify in its type definition. A varry can contain a varying number of elements from zero when empty to the maximum size.

The correspondence with other languages is as follows:

• Arrays in other languages become varrays in PL/SQL
• Sets in other languages become nested tables in PL/SQL
• Hash tables or maps in other languages become associative arrays in PL/SQL

Here are examples of declaring and creating collections:

-- Associative array (does not need a constructor)
TYPE cost_type IS TABLE OF NUMBER
  INDEX BY VARCHAR2(30);
item_cost  cost_type;

item_cost ('ObjectA') := 1.23;
item_cost ('ObjectB') := 4.56;

-- Nested Tables
TYPE nested_table IS TABLE of VARCHAR2(30);
cs327 nested_table;

cs327 := nested_table ('Alan Turing', 'William Gates');

-- Varray
TYPE number_type IS VARRAY(10) of NUMBER;
squares  number_type := number_type();

idx number := 0;
LOOP
  idx := idx + 1;
  number_type ( idx ) := idx * idx;
  EXIT WHEN idx = 10;
END LOOP;

You can check if an element exists in a collection using the EXISTS method. EXISTS ( n ) will return TRUE if the nth element in either a nested table or varray exists and FALSE otherwise. The EXISTS method does not work for associative arrays.

TYPE numlist IS TABLE of NUMBER;
n numlist := numlist (9, 1, 7, 3);

IF n.EXISTS ( 3 ) THEN  -- checks if 7 exists since it is at location 3
  ...
END IF;

The COUNT method returns the number of elements in the collection.

DBMS_OUTPUT.PUT_LINE ('The collection n has ' || n.COUNT || ' elements.');

The LIMIT method returns the maximum number of elements that a varray can have. If the collection has no maximum size then it returns null.

TYPE num_type IS VARRAY(10) of NUMBER;
squares num_type := num_type (1, 4, 9, 16, 25);

DBMS_OUTPUT.PUT_LINE 
  ('You still have ' || squares.LIMIT - squares.COUNT || ' spaces.');

To iterate through the elements of a collection, you can use the PRIOR and the NEXT methods.

TYPE numlist IS TABLE OF NUMBER;
n numlist := numlist (9, 2, 7, 4, 6);

counter NUMBER := n.FIRST;
WHILE counter IS NOT NULL LOOP
  DBMS_OUTPUT.PUT_LINE ( n ( counter ) );
  counter := n.NEXT ( counter );
END LOOP;

-- To go in reverse order
counter := n.LAST;
WHILE counter IS NOT NULL LOOP
  DBMS_OUTPUT.PUT_LINE ( n ( counter ) );
  counter := n.PRIOR ( counter );
END LOOP;

Records

The components of a record can be of different datatypes unlike that of a collection. These components are called fields. A record can hold a row in a table or a subset of the columns from a table. You first define a record type and then declare a variable of that type. You access each field in the record using the dot operator - <record_name>.<field_name> A big advantage of using records is with functions. A function may return only one value and that value could be a record where multiple values are bundled together.

DECLARE
TYPE StudentType IS RECORD
(
  s_id   students.eid%TYPE,
  first  students.first_name%TYPE,
  last   students.last_name%TYPE
);
student_rec  StudentType;
BEGIN
  SELECT eid, first_name, last_name INTO student_rec
  FROM students
  WHERE eid = 'jdoe123';
END;
/

Subprograms

A subprogram is either a function or a procedure. A subprogram can be invoked with a set of parameters. A procedure performs an action and a function returns a value. A procedure call is like a statement. A function call is like an expression or operator inside a statement. You can create and store a standalone subprogram or define one within a block. Related subprograms are often grouped into packages.

Functions

The general syntax for a standalone or stored function is as follows:

CREATE [OR REPLACE] FUNCTION <function_name>
[(
   <parameter_name_1>  [IN] [OUT] <parameter_data_type_1>,
   ...
   <parameter_name_n>  [IN] [OUT] <parameter_data_type_n>,
)]
RETURN  <return_data_type> IS

-- declaration section

BEGIN

  -- executable code

  return <return_data_type>;
EXCEPTION

  -- exception handling code

END;
/

CREATE OR REPLACE FUNCTION compute_bonus 
(
  emp_id  NUMBER,
  bonus  NUMBER
)
RETURN NUMBER IS
  emp_sal NUMBER;
BEGIN
  SELECT salary INTO emp_sal
  FROM employees
  WHERE employee_id = emp_id;
  RETURN emp_sal + bonus;
EXCEPTION
  WHEN  NO_DATA_FOUND  THEN
    RETURN  -1;
END compute_bonus;
/

n_new_salary := compute_bonus (101, 500);

Procedures

The general syntax for a standalone or stored procedure is as follows:

CREATE [OR REPLACE] PROCEDURE <procedure_name>
[(
   <parameter_name_1>  [IN] [OUT] <parameter_data_type_1>,
   ...
   <parameter_name_n>  [IN] [OUT] <parameter_data_type_n>,
)] IS

-- declaration section

BEGIN

  -- executable code

EXCEPTION

  -- exception handling code

END;
/

CREATE OR REPLACE PROCEDURE pl
(
  aiv_text  IN VARCHAR2 
) IS
BEGIN
  SYS.DBMS_OUTPUT.PUT_LINE ( aiv_text );
END pl;
/

  pl ('Hello World!');

Parameter Modes

There are 3 types of modes for parametes - IN, OUT, and IN OUT. An IN parameter lets you pass a value to a subprogram but you may not change it inside the subprogram. The IN parameter can be initialzed to a default value in case it is omitted in the call. An OUT parameter can be assigned a value and it changes the value of the actual parameter that binds to it. Before exiting a subprogram be sure to assign values to all the OUT parameters otherwise they will have the default NULL value. The IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller.

Exception Handling

In PL/SQL code there is an optional Exception block that you can handle exceptions. Some exceptions are generated by Oracle and others could be user-defined. Here are some pre-defined errors:

• case_not_found
• collection_is_null
• cursor_already_open
• invalid_cursor
• invalid_number
• no_data_found
• subscript_outside_limit
• too_many_rows
• zero_divide

DECLARE
  v_id    students.ut_eid%TYPE := 'jdoe123';
  v_first     students.first_name%TYPE;
  v_last      students.last_name%TYPE;
BEGIN
  select first_name, last_name
  into v_first, v_last
  from students
  where students.ut_eid = v_id;
  DBMS_OUTPUT.PUT_LINE (v_last || ', ' || v_first);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Does not exist in the database');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE ('Duplicate data exist in the database');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE ('An error occurred');
END;
/

In its simplest form user defined exceptions are declared in the declaration portion of the code, they are raised when an error condition arises and handled in the exception block. Let us create an exception called e_invalid_id when a negative id is encountered.

DECLARE
  v_id    students.ut_eid%TYPE;
  e_invalid_id  EXCEPTION;
BEGIN
  ...
  if ( v_id < 0 ) then
    raise e_invalid_id;
  end if;
  ...
EXCEPTION
  WHEN e_invalid_id THEN
    DBMS_OUTPUT.PUT_LINE ('ID cannot be negative');
END;
/

There are times when you may just wish to raise an exception but not handle it. Oracle provides a built-in procedure called RAISE_APPLICATION_ERROR that allows you to do that. This procedure has two input parameters and the syntax for the procedure call is as follows:

RAISE_APPLICATION_ERROR ( error_number, error_message );

DECLARE
  v_id    students.ut_eid%TYPE;
BEGIN
  ...
  if ( v_id < 0 ) then
    RAISE_APPLICATION_ERROR (-20000, 'ID cannot be negative');
  end if;
  ...
END;
/

Cursors

You can use a SELECT statement within you PL/SQL code if it returns a single row. However, to handle queries that return multiple rows PL/SQL provides a CURSOR construct that allows to process the multiple rows returned, one row at a time. A cursor must be declared in the declaration section, then opened in the main body of the code. When all the rows of data have been processed it must be closed. Here is an example of the use of a cursor to determine all the properties managed by a given staff member.

DECLARE
  v_prop_num		PropertyForRent.propertyNo%TYPE;
  v_street		PropertyForRent.street%TPE;
  v_city		PropertyForRent.city%TYPE;
  v_zip			PropertyForRent.zip%TYPE;
  CURSOR propertyCursor (v_staff_id varchar2) IS
    SELECT propertyNo, street, city, zip
    FROM PropertyForRent
    WHERE staffNo = v_staff_id
    ORDER BY propertyNo;
BEGIN
  -- This get all the properties managed by 'jdoe123' 
  OPEN propertyCursor ('jdoe123'); 

  -- Process all the rows returned one row at a time
  LOOP
    FETCH propertyCursor
      INTO v_prop_num, v_street, v_city, v_zip
    EXIT WHEN propertyCursor%NOTFOUND;

    -- process data

  END LOOP;
  CLOSE propertyCursor;
EXCEPTION
  WHEN OTHERS THEN
    IF propertyCursor%ISOPEN THEN
      CLOSE propertyCursor;
    FIND IF;
END;
/

Triggers

A trigger is a PL/SQL block of code that is excuted when an event occurs. A trigger is used to audit changes in the database or ensure data integrity. The trigerring event could be

• A DML statement like INSERT, UPDATE, or DELETE.
• A DDL statement such as CREATE or ALTER.
• User logon or logoff event.

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER}
INSERT | DELETE | UPDATE [OF column_list]
ON table_name
[FOR EACH {ROW | STATEMENT}]
[WHEN condition]
DECLARE
...
BEGIN
...
EXCEPTION
...
END;

In the realtor company a staff member should not handle more than 100 properties. A BEFORE trigger is created that will prevent an assignment being made to a staff member who already has 100 properties.

CREATE OR REPLACE TRIGGER StaffHandlingTooMuch
BEFORE INSERT ON PropertyForRent
FOR EACH ROW
DECLARE
  n_pCount	NUMBER;
BEGIN
  SELECT COUNT(*) INTO n_pCount
  FROM PropertyForRent
  WHERE staffNo = :new.staffNo;
  IF n_pCount = 100 THEN
    raise_application_error (-20000, 'Staff already managing 100 properties');
  END IF;
END;

Here is a trigger that is activated after an update or delete operation. It basically keeps track of changes made to a table called instructor. The table statistics ( table_name, transaction_name, transaction_user, transaction_date )

CREATE OR REPLACE TRIGGER instructor_audit
AFTER UPDATE OR DELETE ON INSTRUCTOR
DECLARE
  v_type VARCHAR2(10);
BEGIN
  IF UPDATING THEN
    v_type := 'UPDATE';
  ELSIF DELETING THEN
    v_type := 'DELETE';
  END IF;

  UPDATE statistics
  SET  transaction_user = USER,
       transaction_date = SYSDATE
  WHERE  table_name = 'INSTRUCTOR'
  AND  transaction_name = v_type;

  IF SQL%NOTFOUND THEN
    INSERT INTO statistics
    VALUES ('INSTRUCTOR', v_type, USER, SYSDATE);
  END IF;
END;

A row trigger is fired for each row that is modified. If there are 10 rows that are modified, then a row trigger is activated 10 times. A statement trigger is fired once , regardless of the number of rows affected by the triggering statement. To create a statement trigger you just eliminate the FOR EACH ROW clause.

Supposing you want people to access the instructor table only during work days and not weekends, then this trigger will raise an exception

CREATE OR REPLACE TRIGGER instructor_workdays
BEFORE INSERT OR UPDATE OR DELETE ON INSTRUCTOR
DECLARE
  v_day VARCHAR2(10);
BEGIN
  v_day := RTRIM ( TO_CHAR ( SYSDATE, 'DAY' ) );

  IF v_day LIKE ( 'S%' ) THEN
    RAISE_APPLICATION_ERROR
      (-20000, 'This table cannot modified during weekends.');
  END IF;
END;