Introduction to PL/SQL

PL/SQL (Procedural Language extensions to SQL) is used to access an Oracle database from various environments (e.g., Forms, Reports to create triggers, procedures, functions, etc.). PL/SQL is integrated with the database server. It does not exist as a standalone language.

Language type

• PL/SQL is a block structured language like Pascal. In this type of language, the basic unit of a program is a block and the block can be nested.

• PL/SQL is a typed language (all variables must be defined before uses and types should be matched like C/C++).

Block Structure

DECLARE
         Declarative section -- variables and types, cursors, and local subprograms here (optional)
BEGIN
         Executable section -- statements here -- this is the main section that is required
EXCEPTION -- this is optional
         Exception handling section -- error handling statements here (optional)
END;

An example PL/SQL block

DECLARE
          Comp_id NUMBER;
          Comp_name CHAR(20) := 'ACME SHOWERS';
BEGIN
           SELECT company_id_seq.NEXTVAL INTO comp_id
           FROM dual;

           INSERT INTO company (company_id, company_name)
           VALUES (comp_id, comp_name);
END;

Several kinds of blocks
• Anonymous blocks (generally constructed dynamically and executed only once)
• Named blocks (anonymous blocks with a label)
• Subprograms (procedures, functions, packages)
• Triggers (named blocks that are also stored in the database)

->  Subprograms and triggers can be treated as named blocks

Identifiers

Valid names in PL/SQL (e.g., variables, cursors, subprograms) are similar to SQL's (30 characters starting with alphabets, and numbers, #, $, _, quoted identifiers), many reserved words.

Delimiters
+, –, *, /, ** (exponentiation), = (equality), := (assignment), -- (single line comment), /* */ (multi-line comment), .. (range), << >> (label), ; (statement separator), and so on.

Literals (constant)
Number (400 or NULL), String ('houston' or NULL), Boolean literals(TRUE, FALSE, NULL)

Variable declarations
Syntax: Variable_name [CONSTANT] data_type [NOT NULL] [:= (or DEFAULT) value];

All declarations must be made in the declaration section of the block.
e.g., num NUMBER(2)
e.g., next_tax_filing_date CONSTANT DATE := '15-APR-00';
e.g., company_name VARCHAR2(60) NOT NULL DEFAULT 'PCS R US';

The basic PL/SQL types
•    Scalar type (defined in package STANDARD)
     NUMBER, CHAR, VARCHAR2, BOOLEAN, BINARY_INTEGER, LONG
•    Composite types (user-defined types): TABLE, RECORD

Converting between data types
•     Implicit conversion -- may be confusing or erroneous (hard to debug) sometimes
      PL/SQL can convert between characters and numbers, characters and dates
•     Explicit conversion -- this is a better idea
       TO_CHAR(), TO_DATE(), TO_NUMBER(), …

Variable Scopes

Follows the typical scoping rules in Pascal.

Boolean expression

e.g., 'scott' LIKE 'sc%t' returns TRUE, 115 BETWEEN 110 AND 120 returns TRUE, 'scott' IN ('mike', 'scott') returns TRUE, etc.

Boolean expression with NULL

TRUE/FALSE test with NULL (is it true or false?)
a) mystring := ‘ ‘;        IF mystring = NULL THEN ….?
                                  IF mystring != NULL THEN …?
b) mynum := 0;           IF mynum = NULL THEN …?
c) Mystring := ‘have fun’;
    Yourstring := NULL;      IF mystring != yourstring THEN …?
d) IF NULL = NULL THEN …?
e) IF NULL != NULL THEN …?

Statement

Every PL/SQL statement is separated by a semicolon ; and put in BEGIN and END section.

PL/SQL Control Structures

IF THEN ELSE statement

IF boolean_exp1 THEN
     Statement1;                                             -- executed when TRUE
[ELSIF boolean_exp2 THEN                      -- Note that ELSIF not ELSEIF
     Statement2;]
…
[ELSE statementN;]                                     -- executed when NOT TRUE
END IF;

NULL as a statement
IF report.selection = 'DETAIL' THEN
NULL;                                                            -- Do nothing
ELSE
Exec_detail_report;
END IF;

LOOPS statements

LOOP                                                        -- simple loop
            [EXIT [WHEN condition]];
END LOOP;

DECLARE
   cnt number(2) := 0;
BEGIN
   LOOP
       cnt := cnt + 1;
       EXIT WHEN cnt > 1;
   END LOOP;
END;

WHILE condition                                   -- while loop
    LOOP
      Statements;                                      -- [EXIT [WHEN condition]] can still be used for premature exit
    END LOOP;

There are two kinds of FOR LOOPS: the numeric FOR LOOP and the cursor FOR LOOP. Here is the general syntax of the numeric FOR LOOP (the cursor FOR LOOP will be discussed later).

FOR loop_index IN [REVERSE] low_bound .. high_bound
     LOOP
         statements;
     END LOOP;

• Use the numeric FOR LOOP when you want to execute a body of codes a fixed number of times.
• The numeric FOR LOOP terminates when the number of times specified in its range scheme has been satisfied. You can also    terminate the loop with an EXIT statement (although not recommended).
• After each execution of the loop body, PL/SQL checks the value of the loop index. When it exceeds the difference between    the upper and lower bounds of the range scheme, the loop terminates.
• If the lower bound is greater than the upper bound of the range scheme, the loop never executes its body.

  Rules for Numeric FOR LOOPs

• Do not declare the loop index variable. PL/SQL automatically and implicitly declares it as a local variable with data type    INTEGER. The scope of this index is the loop itself; you can't reference the loop index outside the loop.
• Expressions used in the range scheme are evaluated once, when the loop starts. If you make changes within the loop to the    variables, those changes will have no effect.
• The loop index is always incremented or decremented by one. There is no STEP.
• Never change the values of either the loop index or the range boundary from within the loop.

FOR cnt IN 1..10                                    -- this loop executes 10 times counting cnt from 1 to 10
LOOP
            … statements …
END LOOP;

FOR cnt IN REVERSE 1..10                  -- this loop executes 10 times counting cnt from 10 to 1
LOOP
            … statements …
END LOOP;

FOR cnt IN 10..1                                    -- this loop DOES NO executes at all since 10 > 1
LOOP
           … statements …
END LOOP;

FOR cnt IN start_period..end_period       -- this loop executes the period times specified
LOOP                                                      -- in the expression.
           … statements …
END LOOP;