PL/SQL

PL/SQL = Procedural Language extensions to SQL
An Oracle-specific language combining features of:

• modern, block-structured programming language
• database interaction via SQL

Designed to overcome declarative SQL's inability to specify control aspects of DB interaction.

Used to add procedural capabilities to Oracle tools.

PL/SQL is implemented via a PL/SQL engine (cf. JVM)

• which can be embedded in clients (e.g. Forms, SQL*Plus)
• which is also usually available in the Oracle server

Why PL/SQL?

Consider trying to implement the following in SQL (SQL*Plus):

If a user attempts to withdraw more funds than they have from their account, then indicate "Insufficient Funds", otherwise update the account

A possible implementation:

ACCEPT person PROMPT 'Name of account holder: '
ACCEPT amount PROMPT 'How much to withdraw: '

UPDATE Accounts
SET balance = balance - &amount
WHERE holder = '&person' AND balance > &amount;
SELECT 'Insufficient Funds'
FROM Accounts
WHERE holder = '&person' AND balance < = &amount;

Two problems:

• doesn't express the "business logic" nicely
• performs both actions when (balance-amount < amount)

We could fix the second problem by reversing the order (SELECT then UPDATE).

But in SQL there's no way to avoid executing both the SELECT and the UPDATE

PL/SQL allows us to specify the control more naturally:
-- A sample PL/SQL procedure

PROCEDURE withdrawal(person IN varchar(20), amount IN REAL ) IS
     current REAL;
BEGIN
     SELECT balance INTO current
     FROM Accounts
     WHERE holder = person;
     IF (amount > current)
          dbms_output.put_line('Insufficient Funds');
     ELSE
          UPDATE Accounts
          SET balance = balance - amount
          WHERE holder = person AND balance > amount;
          COMMIT;
     END IF;
END;
And package it up into a useful function, which could be used as:
SQL> EXECUTE withdrawal('John Shepherd', 100.00);

PL/SQL Syntax

PL/SQL is block-structured, where a block consists of:

DECLARE
     declarations for
          constants, variables and local procedures
BEGIN
     procedural and SQL statements
EXCEPTION
     exception handlers
END;

Data Types

PL/SQL constants and variables can be defined using:

• standard SQL data types (CHAR, DATE, NUMBER, ...)
• built-in PL/SQL types (BOOLEAN, BINARY_INTEGER)
• PL/SQL structured types (RECORD, TABLE)

Users can also define new data types in terms of these.
There is also a CURSOR type for interacting with SQL.

Record Types

Corresponding to Modula RECORDs or Constructs, and also closely related to SQL table row type.

New record types can be defined via:
     TYPE TypeName IS RECORD
          (Field1 Type1, Field2 Type2, ...);
Example:
     TYPE Student IS RECORD (
          id# NUMBER(6),
          name VARCHAR(20),
          course NUMBER(4)
     );
Record components are accessed via Var.Field notation.
     fred Student;
     ...
     fred.id# := 123456;
     fred.name := 'Fred';
     fred.course := 3978;
Record types can be nested.
     TYPE Day IS RECORD
          (day NUMBER(2), month NUMBER(2), year NUMBER(4));

     TYPE Person IS RECORD
          (name VARCHAR(20), phone VARCHAR(10), birthday Day);

Constants and Variables

Variables and constants are declared by specifying:
     Name [ CONSTANT ] Type [ := Expr ] ;

Examples:
     amount INTEGER;
     part_number NUMBER(4);
     in_stock BOOLEAN;
     owner_name VARCHAR(20);
     max_credit CONSTANT REAL := 5000.00;
     my_credit REAL := 2000.00;

Variables can also be defined in terms of:

• the type of an existing variable or table column
• the type of an existing table row (implict RECORD type)

Examples:

     employee Employees%ROWTYPE;
     name Employees.name%TYPE;

Assigning Values to Variables

A standard assignment operator is available:
    tax := price * tax_rate;
    amount := TO_NUMBER(SUBSTR('750 dollars',1,3));

Values can also be assigned via SELECT...INTO:
     SELECT price +10 INTO cost
    FROM StockList
    WHERE item = 'Cricket Bat';
    total := total + cost;

SELECT...INTO can assign a whole row at once:
     DECLARE
          emp Employees%ROWTYPE;
          my_name VARCHAR(20);
          pay NUMBER(8,2);
     BEGIN
          SELECT * INTO emp
          FROM Employees
          WHERE id# = 966543;
          my_name := emp.name;
...
          SELECT name,salary INTO my_name,pay
          FROM Employees
          WHERE id# = 966543;
     END;

Control Structures

PL/SQL has conventional set of control structures:

• for sequence (note:- ; is a terminator)
• IF for selection
• FOR, WHILE, LOOP for repetition

Along with exceptions to interrupt normal control flow.
And a NULL; statement to do nothing.

Selection

Selection is expressed via:

1. IF Cond1 THEN Statements1;
2. ELSIF Cond2 THEN Statements2;
3. ELSIF Cond3 THEN Statements3;

Example:

If A > B Then     Dbms_output.put_line (‘A is big’); End if;	If A > B Then     Dbms_output.put_line (‘A is big’); ELSIF     Dbms_output.put_line (‘B is big’); End if;
If A > B Then     Dbms_output.put_line (‘A is big’); ELSE     Dbms_output.put_line (‘B is big’); End if;