PL/SQL

Introduction

 * PL/SQL is procedural language extending and integrated with SQL.
 * It is a proprietary to Oracle Corporation and there is no standard procedural language extension to SQL, but ANSI is working on it.
 * It is based on Ada. PL/SQL incorporates many modern programming language concepts.
 * Initially, it provided batch-processing logic for SQL scripts and procedural logic for SQL*Forms.
 * It has matured into a robust programming environment.

Language Overview

 * Logical statement are terminated by semi-colon.
 * Statements can flow over more than one line.
 * No continuation char required.
 * Not case-sensitive, except in the case of string literals.

Literals

 * A literal is an explicit value
 * Use the symbol || to concatenate strings
 * Use single quote to concatenate strings. Example : 'Testing... ' || 'concatenation'
 * Use double minus (--) to comment something in the source code
 * The command is very used to convert number to strings
 * Booleans are TRUE, FALSE, NULL without any quotes
 * Avoid hard-coding literals in your programs
 * Rules for single quote use:
 * To embed a single quote in a literal, use two single quotes together
 * To concatenate a single quote at the end of string, use three single quotes

Identifiers -- names of things

 * up to 30 characters
 * Must start with letter
 * Use underscore(_), dollar sign($), pound sign(#) as separators.
 * Can qualify identifier with name of program, loop, or package.

Identifiers Examples


 * The following identifiers are all the same:
 * TOTAL_sales
 * TOTAL_SALES
 * total_sales


 * Illegal Identifiers
 * 1st_year_total
 * company-name
 * match_found?
 * the_long_name_of_the_corporation


 * Valid Identifiers
 * row#
 * total_sales_$
 * First_year_total

Comments

 * Single line comments start with a double hyphen (--) and extend to end of physical line.
 * Multiple line comments start with a slash-asterisk (/*) and end with an asterisk-slash (*/).

Example IF status_cd = ‘C’ -- Closed THEN /*   || Reject changes. I have lots to  || say here so I use a multiple || comment block. */  action_status := rejected; END IF;

Datatypes

 * PL/SQL supports all datatypes of Oracle server, plus some of its own.
 * BINARY_INTEGER
 * BOOLEAN
 * Record: multiple fields
 * PL/SQL Table: an in-memory table or array.
 * Four families of scalar datatypes:
 * Character
 * Number
 * Date-time
 * Boolean

Scalar Datatypes

 * BINARY_INTEGER
 * DEC
 * DECIMAL
 * DOUBLE PRECISION
 * FLOAT
 * INT
 * INTEGER
 * NATURAL
 * NUMBER
 * NUMERIC
 * POSITIVE
 * REAL
 * SMALLINT
 * CHAR
 * CHARACTER
 * LONG
 * LONG RAW
 * ROWID
 * STRING
 * VARCHAR
 * VARCHAR2
 * BOOLEAN
 * DATE

Composite Datatypes

 * Records
 * PL/SQL Tables

Dates

 * PL/SQL dates parallel those in the Oracle RDBMS. Composed of:
 * Century, Year, Month, Day
 * Hour, Minute, Second
 * You cannot store subsecond intervals in a date variable.
 * Valid dates range from 1/1/4712 BC to 12/31/4712 AD.
 * By default, the time portion is midnight.
 * PL/SQL provides functions which allow you to perform date arithmetic and manipulation.

Booleans

 * A variable declared as BOOLEAN is TRUE, FALSE or NULL.
 * These values are NOT strings.
 * Remember: the Oracle database does not support BOOLEAN columns.
 * A very natural datatype for programming.
 * Improves readability of code.
 * Must convert to Y/N or 1/0 when you store Booleans in the database.

Declarations
Syntax DECLARE  
 * You must declare all variables and constants before you can use them.
 * Declare one variable per statement.
 * Can provide default value at declaration time.
 * Can apply NOT NULL constraint.
 * Must provide default value at same time.
 * Use %TYPE and %ROWTYPE attributes to anchor your variable to an existing variable or database element.

IF Statements
Syntax IF THEN ELSIF THEN ELSIF THEN ELSE END IF;

Example set serveroutput on DECLARE x NUMBER(3) := 47; BEGIN IF x < 10 THEN dbms_output.put_line('X is less than 10'); ELSIF x = 10 THEN dbms_output.put_line('X is equal to 10'); ELSIF x < 100 THEN dbms_output.put_line('X is between 11 and 99'); ELSE dbms_output.put_line('X is greater than 99'); END IF; END; /

Oracle Loops
There are 5 kinds of Oracle loops:
 * Basic Loop
 * While Loop
 * For Loop
 * Cursor Loop
 * Cursor FOR Loop

Basic Loop
The BASIC loop just uses LOOP and END LOOP and depends on the programmer to create an explicit exit from the loop.

Syntax LOOP END LOOP;

Example CREATE TABLE loop_test (test_col VARCHAR2(5));

DECLARE i PLS_INTEGER := 1; BEGIN LOOP i := i + 1; INSERT INTO loop_test VALUES (i); IF i > 99 THEN EXIT; END IF; END LOOP; COMMIT; END; / SELECT * FROM loop_test;

While Loop
The WHILE loop loops until a condition, explicitly stated at the beginning of the loop is met.

Syntax WHILE  LOOP END LOOP;

Example DECLARE i PLS_INTEGER := 999; BEGIN WHILE i < 1100 LOOP i := i + 1; INSERT INTO loop_test VALUES (i); END LOOP; COMMIT; END; /

For Loop
The FOR loop loops for a specific number of times defined by FOR statement. The variable used as the counter for the FOR loop does not need to be declared in the declaration section of the code.

Syntax -- incrementing FOR IN  ..  LOOP END LOOP;

-- decrementing FOR IN REVERSE  ..  LOOP END LOOP;

Example - incrementing BEGIN FOR i IN 2000 .. 2100  LOOP INSERT INTO loop_test VALUES (i); END LOOP; COMMIT; END; /

Example - decrementing BEGIN FOR i IN REVERSE 3000 .. 3100  LOOP INSERT INTO loop_test VALUES (i); END LOOP; COMMIT; END; /

Cursor Loop
A BASIC loop running while a cursor can fetch records. A record must be explicitly declared.

Syntax LOOP FETCH  INTO  EXIT WHEN %NOTFOUND; END LOOP;

Example DECLARE CURSOR ao_cur IS SELECT SUBSTR(object_name,1,5) FIRSTFIVE FROM all_objects WHERE SUBSTR(object_name,1,5) BETWEEN 'A' AND 'M'; ao_rec ao_cur%ROWTYPE; BEGIN OPEN ao_cur; LOOP FETCH ao_cur INTO ao_rec; EXIT WHEN ao_cur%NOTFOUND; INSERT INTO loop_test VALUES (ao_rec.firstfive); END LOOP; COMMIT; CLOSE ao_cur; END; /

Cursor FOR Loop
A FOR loop with an extrinsic or an intrinsic cursor. A record is implicitly declared by the variable in the FOR statement.

Syntax FOR  IN  LOOP END LOOP;

Example - Using extrinsic cursor DECLARE CURSOR ao_cur IS   SELECT SUBSTR(object_name,1,5) FIRSTFIVE FROM all_objects WHERE SUBSTR(object_name,1,5) BETWEEN 'N' AND 'Z'; BEGIN FOR ao_rec IN ao_cur LOOP INSERT INTO loop_test VALUES (ao_rec.firstfive); END LOOP; COMMIT; END; /

Example - Using intrinsic cursor DECLARE

BEGIN FOR ao_rec IN (    SELECT SUBSTR(object_name,1,5) FIRSTFIVE     FROM all_objects     WHERE SUBSTR(object_name,1,5) BETWEEN 'N' AND 'Z') LOOP INSERT INTO loop_test VALUES (ao_rec.firstfive); END LOOP; COMMIT; END; /