Oracle PL/SQL

Introduction

• Anonymous PL/SQL Block - an anonymous PL/SQL block is procedural PL/SQL code which is not stored in the database. An anonymous PL/SQL block is not a stored procedure or function by definition.
• Procedure - in Oracle7 a procedure can return values by reference. In Oracle8 a procedure can return a set of rows by reference using what is called a BYREF CURSOR.
• Function - returns a single value.
• Package Header - the definition for a package. A package contains a group of related procedures and functions.
• Package Body - the body section contains the executable procedural code.
• Implicit Cursor - an implicict cursor loops through a set of rows automatically. The programmer has no control over each row individually except through built-in functions.
• Explicit Cursor - an explicit cursor is explicitly looped through row by row and under programmer control.
• Flow Control - flow control requires the use of procedural conditional and looping statements such as IF statements plus FOR and WHILE looping constructs.

Speed Increase with PL/SQL

Use of PL/SQL in stored procedures executing in the database server increases speed of data access by reducing network traffic as shown below. Network traffic is reduced since transactions are sent over the network to the database and back as PL/SQL blocks as opposed to single SQL statements.

The PL/SQL Engine

The PL/SQL Engine processes PL/SQL blocks submitted from the Server manager. PL/SQL blocks are parsed into procedural and SQL statements. Procedural statements are passed to the Procedural Statement Executor inside the local application engine. This engine passes the SQL statements to the server SQL Statement Executor. PL/SQL blocks are executed in the PL/SQL engine. The PL/SQL Engine resides on both the client and in the server depending on the tool being used. SQL^*Plus for instance is a client based tool.

Memory Use and PL/SQL

A PL/SQL package, function, procedure or trigger will remain in memory upon use until no longer frequently accessed and is known as a stored or persistent object; hence Stored Procedure. By the wrapping of many SQL statements into a single PL/SQL program object client-server applications reduce network traffic since a single transaction is sent to and from the database. A network transfer is infinitesimally slower than a memory or disk I/O task.

PL/SQL Basics

PL/SQL groups SQL statements into blocks or sequentially executable groups of SQL statements. A block is denoted as shown below.

BEGIN
	... ;
	... ;
	... ;
END;

A PL/SQL program consists of the following sections.

• Declaration Section - define variables, cursors, constants and user-defined exceptions.
• Executable Section - program logic, ie. SQL commands.
• Exception Handling Section - errors are handled.

Below are shown the three different possible sectional combinations of a PL/SQL program.

BEGIN
	... ;
	... ;
	... ;
END;

OR

DECLARE
	... ;
	... ;
BEGIN
	... ;
	... ;
	... ;
END;

OR

DECLARE
	... ;
	... ;
BEGIN
	... ;
	... ;
	... ;
EXCEPTION
	... ;
	... ;
	WHEN OTHERS NULL;
END;

The most simplistic PL/SQL block would look as below. This block does nothing. NULL is an executable statement. Some exception situations could require this for instance. Assuming that an unhandled exception is passed to the calling block then if it is required that the exception should be handled by the calling block then a WHEN OTHERS NULL; exception could be utilised.

BEGIN
	NULL;
END;

A useful NULL-only statement procedure would be that of a procedure contained within a package which instantiates the package into frequent use memory without actually executing any procedures or functions within the package. This and other procedures like it could be executed on database startup using a script. Obviously when procedures are executed directly from CACHE the first time they are executed they will be very much faster. Also these initial CACHE-loading scripts could be executed at any time when performance affecting removal of packaged PL/SQL code has occurred. Below is some example packaged PL/SQL code with just such a memory initialising procedure.

AS
	PROCEDURE init IS
	BEGIN
		NULL;
	END init;

	PROCEDURE doThis IS
	BEGIN
		... do this ...
	END doThis;

	PROCEDURE doThat IS
	BEGIN
		... do that ...
	END doThat;
END;

Declaring Variables

Locally scoped variables will temporarily override variables declared in calling blocks.

PL/SQL Procedures

Only a single row can be returned from each procedure call. However, using a REF cursor as a BYREF IN OUT variable in Oracle7 and a BYREF OUT variable in Oracle8+ will allow the returning of multiple rows. Parameter variables are of three different types. Procedures allow the reduction of complex processing into smaller parts by burying complexity into the database. Also stored procedures allow for database access code to be executed on the database server itself and not a browser or client machine. Procedures enforce uniform techniques and database controlled processing. Another obvious advantage of stored procedures and functions, inside or outside of packages, is network traffic reduction.

1. IN - passed in by value.
2. IN OUT - passed in by value and out by reference.
3. OUT - passed out by reference.

CREATE PROCEDURE

CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure name [ parameters, ... ]  AS | IS 
[ DECLARE
	Variables, Constants, Cursors, Exceptions, Pragma ]
BEGIN
	...
[ EXCEPTION exception handling ]
END name;

• CREATE [OR REPLACE] - REPLACE will overwrite if the procedure exists. An error will result if REPLACE is not used and the procedure already exists.
• schema - owner.
• procedure_name.
• variables - variable declarations in a comma delimited list. No size information is specified. IN is the default and the variable type must be provided.

  variable_name variable_characteristic variable_type
  (role_name IN VARCHAR2, newpass IN VARCHAR2)
  

• IS or AS - one of these keywords must be given. Atleast one is required.
• variable_declaration - variable_name datatype;.
• constant_declaration - constant_name CONSTANT datatype := value;.
• cursor declaration - a cursor stores multiple rows and allows programming control over each row retrieved.

  CURSOR cursor_name (input_variables) IS
  executable_sql_statement;
  

  CURSOR getRows(variable_name IN VARCHAR2) IS
  	SELECT * FROM table_name WHERE column_name = variable_name;

• exception declaration - an exception declaration is an error handling routine. User-defined exceptions must be explicitly called using the RAISE command. Implicit exceptions are automatically invoked when they occur.

  exception_name EXCEPTION;

• pragma declaration - A PRAGMA EXCEPTION_INIT declaration is related to exception declaration. A pragma is a PL/SQL interpreter directive which remaps standard Oracle errors to user-defined errors.

  PRAGMA EXCEPTION_INIT(table_not_found, -904);

• procedural logic - the executable section of a PL/SQL procedure or function placed between BEGIN ... END; block markers. Some systems will have size limits on stored procedure sizes, possibly 32, 64 or 128K.
• exception processing logic - exception handling processing code.

  EXCEPTION
  	WHEN exception_name THEN
  	Error_handling_code
  

  EXCEPTION
  	WHEN table_not_found THEN
  	INSERT INTO error_log VALUES (SYSDATE, 'Table '||table_name||' not found');
  END;
  

PL/SQL Functions

CREATE [ OR REPLACE ] FUNCTION [ schema. ] function name [ parameters, ... ]  IS 
[ DECLARE
	Variables, Constants, Cursors, Exceptions, Pragma ]
RETURN expression IS | AS
BEGIN
	...
[ EXCEPTION exception handling ]
END name;

PL/SQL functions allow multiple inputs and must return a single value. If a function is used outside of a package it must be defined through the use of a PRAGMA_RESTRICT_REFERENCES call in the package header after the function's declaration. Functions can be used to manipulate data and return a single value. Therefore functions can not write data or change the system state or database. A function must always contain a RETURN statement.

• schema - owner.
• parameter_list - specified as param_name [IN|OUT|IN OUT] datatype [{:=|DEFAULT} expr].
• local declarations - locally defined variables, constants, exceptions or cursor.
• executable section - function processing code.
• exception_handler - exception handling code.
• name - function name.
• expr - expression (single value) return to the calling user.

PL/SQL Triggers

A trigger is actually an anonymous PL/SQL block. A trigger is a procedure which is associated with a table and is executed or automatically fired as a result of an action such as INSERT, UPDATE or DELETE against a table.

Packages

Related PL/SQL procedures and functions can be combined into stored structures called packages. A package object contains a header and a body section. The header section contains publicly available items, accessible from outside the package, and the body section contains the executable procedures and functions. Below is an example package. The procedures contained within this package are for a single database table object. This code is actually a small part of a very large package which had each type of procedure for every table in the database. Under some circumstances dynamic SQL would have been coded faster. However, all these procedures contain explicit code, which with the prolific number of procedures, may make the database seem somewhat cluttered. So what ! Explicitly coded SQL code is the most efficient SQL code; certainly much more efficient than dynamic SQL which is generated and parsed at run-time rather than compile time. Also note that all these procedures were generated from the database table, index and column views using a Perl script. Thus the unimaginable scenario of a programmer sitting and typing in all these procedures did not occur. All these procedures were generated automatically through the above mentioned Perl script. The Perl script took about four hours to build. An additional benefit in this case was that this project was continually under development for an extended period and database object structures were continually undergoing change. Thus the Perl script could be utilised to alter table object matching stored procedures in a matter of five minutes. A real time-saver.

The Package Declaration or Header Section

AS

	TYPE jCOUNT IS RECORD (RECORDS NUMBER);
	TYPE rCOUNT IS REF CURSOR RETURN jCOUNT;

	TYPE jDAT_NAV IS RECORD
	(
		 ROWID VARCHAR2(32)
		,FUND_NO DAT_NAV.FUND_NO%TYPE
		,NAV_DATE DAT_NAV.NAV_DATE%TYPE
		,NAV DAT_NAV.NAV%TYPE
		,UPDATE_DATE DAT_NAV.UPDATE_DATE%TYPE
		,BATCH_NO DAT_NAV.BATCH_NO%TYPE
	);
	TYPE rDAT_NAV IS REF CURSOR RETURN jDAT_NAV;

	PROCEDURE COUNT_DAT_NAV (cCOUNT OUT rCOUNT);

	PROCEDURE GET_DAT_NAV (cDAT_NAV OUT rDAT_NAV);

	PROCEDURE PUT_DAT_NAV
	(
		 pTYPE IN CHAR DEFAULT ''
		,pROWID IN VARCHAR2 DEFAULT ''
		,pFUND_NO IN VARCHAR2 DEFAULT ''
		,pNAV_DATE IN DATE DEFAULT ''
		,pNAV IN NUMBER DEFAULT 0
		,pUPDATE_DATE IN DATE DEFAULT ''
		,pBATCH_NO IN NUMBER DEFAULT 0
	);

	PROCEDURE DEL_DAT_NAV (pROWID IN VARCHAR2 DEFAULT '');
	
END;

The Package Body or Executable Section

AS	

	PROCEDURE COUNT_DAT_NAV (cCOUNT OUT rCOUNT) IS
	BEGIN
		OPEN cCOUNT FOR SELECT COUNT(ROWID) FROM DAT_NAV;
		EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
	END;
	
	PROCEDURE GET_DAT_NAV (cDAT_NAV OUT rDAT_NAV) IS
	BEGIN
		OPEN cDAT_NAV FOR SELECT
			 DAT_NAV.ROWID
			,DAT_NAV.FUND_NO
			,DAT_NAV.NAV_DATE
			,DAT_NAV.NAV
			,DAT_NAV.UPDATE_DATE
			,DAT_NAV.BATCH_NO
		FROM DAT_NAV;
		EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
	END;

	PROCEDURE PUT_DAT_NAV
	(
		 pTYPE IN CHAR DEFAULT ''
		,pROWID IN VARCHAR2 DEFAULT ''
		,pFUND_NO IN VARCHAR2 DEFAULT ''
		,pNAV_DATE IN DATE DEFAULT ''
		,pNAV IN NUMBER DEFAULT 0
		,pUPDATE_DATE IN DATE DEFAULT ''
		,pBATCH_NO IN NUMBER DEFAULT 0
	) IS
	BEGIN
		IF pTYPE = 'I' THEN
			INSERT INTO DAT_NAV
			(
				 FUND_NO
				,NAV_DATE
				,NAV
				,UPDATE_DATE
				,BATCH_NO
			)
			VALUES
			(
				 pFUND_NO
				,pNAV_DATE
				,pNAV
				,pUPDATE_DATE
				,pBATCH_NO
			);	
		ELSIF pTYPE = 'U' THEN
			IF NOT pROWID IS NULL THEN
				UPDATE DAT_NAV SET
					 FUND_NO = pFUND_NO
					,NAV_DATE = pNAV_DATE
					,NAV = pNAV
					,UPDATE_DATE = pUPDATE_DATE
					,BATCH_NO = pBATCH_NO
				WHERE ROWID = pROWID;
			END IF;
		END IF;
		COMMIT;
		EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
	END;

	PROCEDURE DEL_DAT_NAV (pROWID IN VARCHAR2 DEFAULT '') IS
	BEGIN
		IF NOT pROWID IS NULL THEN
			DELETE FROM DAT_NAV WHERE ROWID = pROWID;
			COMMIT;
		END IF;
		EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
	END;

END;

If a package is shared by many users using an application it can be pinned into the memory using a procedure like the NULL containing procedure shown previously. The package can also be pinned into memory using the DBMS_SHARED_POOL package.

Anonymous PL/SQL Blocks

Anonymous PL/SQL blocks are used inside SQL routines, triggers or from operating system files. Anonymous PL/SQL procedures can do anything that stored procedures can with the exception that they are not stored in the database. For instance anonymous PL/SQL blocks can be typed directly into a tool such as SQL^*Plus or executed as a text file load into a program such as the console server manager utility.

DECLARE
	Declarations
BEGIN
	Executable section
EXCEPTION
	Exception handlers
END;

Exception Handling

Exception processing can eliminate application hanging errors. A user-defined exception must be implicitly invoked use the RAISE command. The OTHERS exception should be part of any exceptions set. The OTHERS exception will detect an exception when all other exceptions fail to define an occurring exception. The WHEN OTHERS clause is allowed in a PL/SQL block only.

BEGIN
	...
	IF <an error occurs>
		RAISE <this is an error>
	END IF;
	...
END;

Using Cursors

Explicit Cursors

Explicit cursors are predefined multi-row returning SQL statements. Explicit cursors are processed using the OPEN, FETCH and CLOSE commands.

• OPEN - parses cursor code and calls query rows.
• FETCH - selects a single row from the set of rows and inserts the column values into a record or set of variables using the INTO clause.
• CLOSE - closes the cursor releasing memory.

Explicit Cursor Loops

No OPEN, FETCH and CLOSE commands are required in the cursor FOR loop. The <record> object shown below must be a defined record structure or properly defined type variable which returns the value row of the cursor.

FOR <record> IN <cursor> LOOP
	...
END LOOP;

Implicit Cursors

An implicit cursor is automatically defined whenever an SQL statement is executed. Implicit cursors cause two fetches from the database, the first is row count (if > 1 then implicit exception is raised) and the second is the actual row. Implicit cursors can only be used to process a single row at once.

Cursor Attributes

• cursor_name%FOUND - latest successful fetch sets %FOUND to TRUE and before first fetch on opened cursor %FOUND is NULL.
• cursor_name%ISOPEN - TRUE when cursor opened.
• cursor_name%NOTFOUND - opposite of %FOUND.
• cursor_name%ROWCOUNT - contains last valid count of records fetched until cursor closed.

Cursors have built-in attributes allowing status tracking. Cursor attributes can also be used with the most recent SQL statement or implict cursor.

• SQL%FOUND.
• SQL%ISOPEN.
• SQL%NOTFOUND.
• SQL%ROWCOUNT.

Controlling Flow in PL/SQL

Flow control comprises loops and logic control.

IF...THEN...ELSE Structures

Note the syntax of the ELSIF and END IF parts of the IF statement.

IF condition-a THEN
	...
ELSIF condition-b THEN
	...
ELSE
	... default statement sequence ...
END IF;

LOOP...END LOOP Construct

Below are the different formats for the simple LOOP...END LOOP construct. Note that the label_name is not required.

<<label_name>>
LOOP
	...
END LOOP label_name;

<<label_name>>
LOOP
	...
	IF condition THEN EXIT label_name;
END LOOP label_name;

<<label_name>>
LOOP
	...
	EXIT label_name WHEN condition;
END LOOP label_name;

<<label_name>>
LOOP
	...
	WHEN condition EXIT;
END LOOP label_name;

The WHILE Loop

A WHILE loop tests at the start of the loop.

WHILE [NOT] condition LOOP
	...
END LOOP;

The FOR Loop

There is no STEP clause for the FOR loop.

FOR index IN [REVERSE] <lower_bound>..<higher_bound> LOOP
	...
END LOOP;

The GOTO Statement

GOTO can not be used in an IF, a LOOP or a sub-block and can be directed to a label.

BEGIN
	<<start>>
	... statements ...
	GOTO start;
	...statements ...
END;

The NULL Statement

The NULL statement is an executable command which does nothing.

DML and PL/SQL

There are four DML statements used in PL/SQL, INSERT, UPDATE, DELETE and SELECT.

INSERT, UPDATE and DELETE in PL/SQL

INSERT is used as the SQL INSERT command. For both UPDATE and DELETE there is an additional item. In addition to SQL for UPDATE and DELETE is the WHERE CURRENT OF clause. The WHERE CURRENT OF clause is used to specify the name of a cursor whose SELECT statement includes the FOR UPDATE qualifier.

SELECT in PL/SQL

In PL/SQL the SELECT statement can only return a single row unless the result row set is placed into a cursor. In addition a single row returning SELECT statement must use the INTO clause and return column list variables into a list of variables placed after the INTO clause.

The equivalent of the SQL statement ...

SELECT name FROM people WHERE <where_clause>

... in PL/SQL would be ...

SELECT name INTO <variable_name> WHERE <where_clause>

A multi-row returning SELECT statement returning its resulting row set must be returned into a cursor such as ...

CURSOR getName IS SELECT name FROM people;

In PL/SQL the selected cursor rows can be accessed using the OPEN, FETCH and CLOSE commands or a cursor loop.

Using the OPEN, FETCH and CLOSE commands

BEGIN
	...
	OPEN getName;
	FETCH getName INTO <variable_name>;
	WHILE getName%FOUND LOOP
		...
		FETCH getName INTO <variable_name>;
	END LOOP;
	CLOSE getName;
	...
END;

Using a Cursor Loop

BEGIN
	...
	FOR <variable_name> IN getName LOOP
		...
	END LOOP;
	...

For every item in the select list there must be a corresponding item in the INTO list. Record and type specifications using %ROWTYPE and %TYPE definition mappings respectively can be used. Cursor attributes SQL%FOUND, SQL%NOTFOUND and SQL%ROWCOUNT can be used to assess current cursor state.

The FOR UPDATE clause can be added to the SELECT command to lock a row or rows selected in order for update or deletion to be performed.

Accessing a Database Using PL/SQL from an Application

A recordset is passed out to the application as a byref (pointer) parameter value. The RECORDS package contains a type definition of a cursor called rCursor.

PROCEDURE (cCursor IN OUT RECORDS.rCursor) IS
BEGIN
	OPEN cCursor FOR SELECT * FROM <table_name>;
	EXCEPTION WHEN OTHERS THEN NULL;
END;

The following example shows the type definition.

PACKAGE RECORDS
AS
	TYPE rCursor IS REF CURSOR RETURN <table_name>%ROWTYPE;

	--OR ...

	TYPE jCursor IS RECORD
	(
		field1	<table_name 1>.field1%TYPE,
		field2	<table_name 2>.field1%TYPE,
		field3	<table_name 3>.field2%TYPE
	);
	TYPE rCursor IS REF CURSOR RETURN jCursor;
END;

See Database Driven Security in Oracle for an example of BYREF cursors.

COMMIT and ROLLBACK in PL/SQL

Like SQL, PL/SQL is transaction based. This means that SQL commands can be grouped and only permanently stored to the database when commited using the COMMIT command. In PL/SQL transactions are contained within a PL/SQL block. A transaction comprises all SQL commands between COMMIT statements. A transaction can also be rolled back to a SAVEPOINT to preserve SQL statements issued back to a savepoint.

The ALTER Command in PL/SQL

The only option for the ALTER command in PL/SQL is the procedure and function COMPILE option.

The DROP Command in PL/SQL

A procedure or function within a package can not be dropped. Only objects can be dropped, ie. entire packages and non package contained procedures or functions.

Dynamic SQL in PL/SQL

Below is an example of dynamic DDL code using the DBMS_SQL package. Note that dynamic SQL can be both DDL and DML.

PROCEDURE REBUILD_A_TABLE
AS
	vCursor NUMBER;
BEGIN
	vCursor := DBMS_SQL.open_cursor;
	DBMS_SQL.parse(vCursor, 'DROP TABLE test', DBMS_SQL.NATIVE);
	DBMS_SQL.parse(vCursor, 'CREATE TABLE test (id NUMBER NOT NULL, name VARCHAR2(32) NOT NULL)', DBMS_SQL.NATIVE);
END;

Procedure Builder

This tool allows for creation, debugging and testing of PL/SQL code. This tool is similar in nature and function to that of SQL-Programmer. SQL-Programmer is probably more sophisticated.

Files Generated by Procedure Builder

• PLS - unit PL/SQL source code.
• PLD - interpretable script.
• PLL - compiled PL/SQL library.
• LOG - interpreter log file.

Components of Procedure Builder

The Procedure Builder is a GUI tool with a number of components.

The Interpreter

The Interpreter can be manually invoked or invoked from within a wrapping application.

The Object Navigator

The Object Navigator provides icon based access to a current session's objects.

The Program Unit Editor

The Program Unit Editor allows for creation and editing of PL/SQL program units.

Procedure Builder Debug Actions

Procedure Builder debug actions allow for the creation of PL/SQL unit breakpoints and debug triggers.

System Privileges in Packages and Roles

Roles granted system privileges are ignored in packages and stored procedures. When executing stored procedures, the privileges granted through roles are ignored. The reason why roles are disabled in stored procedures is not due to the roles themselves, but has to do with the dependency model. If roles where enabled in stored procedures, Oracle would need to administer what roles are being used in each individual procedure. When a roles changes or is deleted Oracle would need to invalidate the procedure.