Oracle PL/SQL Quick Reference
PL/SQL Block Structure
DECLARE --Optional
--Variables, Cursors, User-defined exceptions
BEGIN --Mandatory
--SQL statements
--PL/SQL statements
EXCEPTION --Optional
--Actions to perform when errors occur
END; --Mandatory
PL/SQL Block Type
Anonymous Procedure Function
[DECLARE] PROCEDURE name FUNCTION name
IS RETURN datatype IS
BEGIN BEGIN BEGIN
--statements --statements --statements
RETURN value;
[EXCEPTION] [EXCEPTION] [EXCEPTION]
END ; END ; END ;
Declaring PL/SQL Variables
identifier [CONSTANT] datatype [NOT NULL]
[:=|DEFAULT expr];
Assigning Values to Variables
identifier := expr;
Types of Variables
PL/SQL variables:
- Scalar
- Composite
- Reference
- LOB(Large OBjects)
Non-PL/SQL variables: Bind or host variables
- Used to pass run time values out of the PL/SQL block back to the Host environment
Base Scalar Datatypes
VARCHAR2(maximum_length) BOOLEAN CHAR[(maximum_length)]
DATE LONG LONG RAW
BINARY_INTEGER PLS_INTEGER NUMBER(precision,scale)
TIMESTAMP
The %TYPE Attribute
Identifier Table_name.column_name%TYPE ;
Identifier Variable_name%TYPE ;
Composite Datatypes
INDEX BY TABLE RECORD NESTED TABLE VARRAY
LOB Datatypes
CLOB BLOB BFILE NCLOB
Creating Bind Variables
VARIABLE variable_name dataype
Displaying Bind Variables
PRINT [variable_name]
NOTE: To reference a bind variable in PL/SQL, you must prefix it's name with colon (:)
Commenting Code
--prefix single-line comments with two dashes
/* Place muti-line comment between the symbols */
SELECT Statements in PL/SQL
SELECT {column_list|*}
INTO {variable_name[,variable_name]...
|record_name}
FROM table
[WHERE condition];
NOTE: The INTO clause is must and queries must return one and only one row. A CURSOR can be used to retrieve more than one row.
Implicit Cursor Attributes for DML statements
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN
Control Structures
IF Statement Basic Loop
IF condition THEN LOOP
statements; statements;
[ELSIF condition THEN ...
statements;] EXIT [WHEN condition];
[ELSE END LOOP;
statements;]
END IF;
FOR Loop WHILE Loop
FOR counter IN [REVERSE] WHILE condition LOOP
Lower_bound..upper_bound LOOP statement1;
statement1; statement2;
statement2; ...
... END LOOP;
END LOOP;
CASE
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
[ELSE resultN+1]
END;
Creating a PL/SQL Record
TYPE type_name IS RECORD
(field_declaration[,field_declaration]...) ;
identifier type_name ;
Where field_declaration is:
field_name {field_type|variable%TYPE|
table.column%TYPE|table%ROWTYPE}
[[NOT NULL] {:=|DEFAULT} expr]
Referencing Fields in the Record
record_name.field_name
Declaring Records with the %ROWTYPE Attribute
DECLARE
record_name reference%ROWTYPE
Creating a PL/SQL Table (INDEX BY Table)
TYPE type_name IS TABLE OF
{column_type|variable%TYPE|table.column%TYPE
|variable%ROWTYPE} [NOT NULL]
[INDEX BY BINARY_INTEGER];
identifier type_name ;
Referencing a PL/SQL table
pl_sql_table_name(primary_key_value)
Using PL/SQL Table Method
table_name.method_name[(parameters)]
PL/SQL Table Methods
EXITS(n) COUNT FIRST LAST PRIOR(n)
NEXT(n) EXTEND(n,i) TRIM DELETE
PL/SQL Table of Records
TYPE table_name_type IS TABLE OF table_name%ROWTYPE
INDEX BY BINARY_INTEGER ;
table_name table_name_type ;
Referencing a Table of Records
table_name(index).field
Cursor
Declaring the Cursor in Declaration Section
CURSOR cursor_name IS select_statement ;
record_name cursor_name%ROWTYPE ;
Opening and Closing the Cursor
OPEN cursor_name ;
CLOSE cursor_name ;
Fetching Data from the Cursor
FETCH cursor_name
INTO [variable1(,variable2,...)
|record_name] ;
Explicit Cusor Attributes
cursor_name%ISOPEN
cursor_name%NOTFOUND
cursor_name%FOUND
cursor_name%ROWCOUNT
Cursor FOR Loops
FOR record_name IN cursor_name LOOP
statement1;
statement2;
...
END LOOP;
Cursor FOR Loops Using Subqueries
FOR record_name IN (subqueries) LOOP
statement1
...
END LOOP ;
Cursors with Parameters
CURSOR cursor_name [(cursor_parameter_name datatype
[,...])]
IS select_statement
[FOR UPDATE [OF column_reference][NOWAIT]];
Parameter Name
cursor_parameter_name [IN] datatype [{:=|DEFAULT}expr]
Openning with Parameters
OPEN cursor_name(cursor_parameter_name[,...]);
Cursor FOR Loops with parameters
FOR record_name IN cursor_name(cursor_parameter_name
[,...]) LOOP
statement1;
statement2;
...
END LOOP;
WHERE CURRENT OF clause
UPDATE|DELETE ... WHERE CURRENT OF cursor_name ;
Predefined Exceptions
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
ZERO_DIVIDE
DUP_VAL_ON_INDEX
Exception
Trapping Exceptions
EXCEPTION
WHEN exception1 [OR exception2 ...] THEN
statement1 ;
statement2 ;
...
[WHEN exception3 [OR exception4 ...] THEN
statement1 ;
statement2 ;
...]
[WHEN OTHERS THEN
statement1 ;
statement2 ;
...]
Declaring Non-Predefined Oracle Sever Exception
DECLARE
exception EXCEPTION ;
PRAGMA EXCEPTION_INIT(exception, error_number) ;
Referencing the declared Non-predefined execption
BEGIN
...
EXCEPTION
WHEN exception THEN
statement1 ;
...
END ;
Trapping User-Defined Exceptions
DECLARE
exception EXCEPTION ;
BEGIN
...
IF SQL%NOTFOUND THEN
RAISE exception ;
END IF ;
...
EXCEPTION
WHEN exception THEN
statement1 ;
...
END ;
Functions for Trapping Exceptions
SQLCODE return error code
SQLERRM return error message
RAISE_APPLICATION_ERROR procedure(Executable/Exception
Section)
RAISE_APPLICATION_ERROR ( error_number,
message [, {TRUE|FALSE}]) ;
error_number between -20000 to -20999
message string up to 2,048 bytes long
TRUE placed on the stack of previous errors.
FALSE replaces all previous errors
Really it is a fantastic summary for all present in the oracle. Thanks a lot ……………