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 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.
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 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.
Below are shown the three different possible sectional combinations of a PL/SQL program.
BEGIN ... ; ... ; ... ; END;
DECLARE ... ; ... ; BEGIN ... ; ... ; ... ; END;
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;
Locally scoped variables will temporarily override variables declared in calling blocks.
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.
CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure name [ parameters, ... ] AS | IS [ DECLARE Variables, Constants, Cursors, Exceptions, Pragma ] BEGIN ... [ EXCEPTION exception handling ] END name;
variable_name variable_characteristic variable_type (role_name IN VARCHAR2, newpass IN VARCHAR2)
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;
PRAGMA EXCEPTION_INIT(table_not_found, -904);
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;
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.
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.
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.
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;
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 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 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;
Explicit cursors are predefined multi-row returning SQL statements. Explicit cursors are processed using the OPEN, FETCH and CLOSE commands.
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;
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.
Cursors have built-in attributes allowing status tracking. Cursor attributes can also be used with the most recent SQL statement or implict cursor.
Flow control comprises loops and logic control.
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;
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;
A WHILE loop tests at the start of the loop.
WHILE [NOT] condition LOOP ... END LOOP;
There is no STEP clause for the FOR loop.
FOR index IN [REVERSE] <lower_bound>..<higher_bound> LOOP ... END LOOP;
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 is an executable command which does nothing.
There are four DML statements used in PL/SQL, INSERT, UPDATE, DELETE and SELECT.
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.
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.
BEGIN ... OPEN getName; FETCH getName INTO <variable_name>; WHILE getName%FOUND LOOP ... FETCH getName INTO <variable_name>; END LOOP; CLOSE getName; ... END;
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.
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.
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 only option for the ALTER command in PL/SQL is the procedure and function COMPILE option.
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.
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;
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.
The Procedure Builder is a GUI tool with a number of components.
The Interpreter can be manually invoked or invoked from within a wrapping application.
The Object Navigator provides icon based access to a current session's objects.
The Program Unit Editor allows for creation and editing of PL/SQL program units.
Procedure Builder debug actions allow for the creation of PL/SQL unit breakpoints and debug triggers.
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.