PL/SQL
- A block-structured language
- Oracle's procedural extension to SQL
Main Features
• Block-structure
[DECLARE -- declarations of variables, constants, cursors]
BEGIN
-- statements
[EXCEPTION -- handlers]
END;
• Supports the use of variables, constants and cursors
DECLARE
CURSOR c1 IS
SELECT ename, job FROM emp WHERE deptno=20;
wages NUMBER(6);
*PL/SQL initializes variables to NULL
*Cursor FOR Loop implicitly declares its loop index as a record having the structure of the database table.
DECLARE
CURSOR c1 IS
SELECT ename, job FROM emp WHERE deptno=20;
salary_total NUMBER(6);
BEGIN
salary := 0; -- variable must be initialized
FOR emp_rec IN c1 LOOP
salary_total:=salary_total + emp_rec.sal;
END LOOP;
END;
*Cursor Variable
• Control Structure
*LOOP
*FOR LOOP
*WHILE LOOP
*EXIT WHEN
*GOTO
• PL/SQL Table -- unbound array
DECLARE
TYPE DepTabType IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_tab DeptTabType;
• User Defined Record
DECLARE
TYPE TimeTyp IS RECORD
(minute SMALLINT, hour SMALLINT);
TYPE MeetingTyp IS RECORD(
day DATE,
time TimeTyp, -- nested record
place VARCHAR2(20),
purpose VARCHAR2(50));
• Modularity
*Procedures
*Functions -- return value
*Packages -- logical grouping of types, procedures & functions
• Error Handling -- RAISE and handle exceptions
Architecture
• PL/SQL Engine can reside in
*Oracle Server
Anonymous Blocks (sending to server for execution because of the fact that the PL/SQL engine comes from the server)
Stored Subprograms (Named PL/SQL blocks compiled and stored in the database)
Packaged
Non-Packaged
Database Triggers (a stored subprogram associated with a table and get executed according to a triggering operation like INSERT)
*Oracle Tools
If the Pl/SQL block contains no SQL statement, then the whole execution resides in the tool's environment
If the PL/SQL block contains some SQL statements, then only these statements are sent to be executed at the server. (Oracle Forms application can use PL/SQL to manipulate field entries, and hence totally gets executed locally)
Transaction
A block can include many transactions, a transaction can include many blocks.
• The way PL/SQL supports rollback depends on the work environment
*Stored Subprogram
If the stored subprogram exits with an unhandled exception. PL/SQL does not roll back database work done by the subprogram
*Between blocks in SQL*Plus
If after the block's execution, a DDL, DCL or COMMIT, or EXIT, DISCONNECT, QUIT. Oracle commits the transaction.
*Oracle Precompiler environment
If the program does not terminated normally with either COMMIT or ROLLBACK (RELEASE option), the transaction will be rolled back automatically.
• Default locking mode can be overruled by SET TRANSACTION
*READ ONLY transaction (though the existing transaction will always see the same snapshot, other concurrent transactions can update the underlying data)
*SELECT FOR UPDATE clause
*LOCK TABLE
"Row Share" prevents other transactions put exclusive locks
Table lock never keeps other users from querying a table.
Query a table never acquires a table lock.
Error Handling
• Internal exception (eg. zero_devide)
For a list of predefined exception, see Pl/SQL User's Guide and Reference, Ch.6 - Error Handling, § Predefined Exceptions
• User defined exception (eg. raise insufficient_fund)
• Exception handler
When an exception either raised by Oracle internally or explicitly by a raise command, the control then returns to the exception handler. After the exception handler has been run, the control returns to the next statement of the enclosing block. If there is no enclosing block, the control returns to the host environment.
*Exceptions raised in the declaration of a sub-block propagates immediately to the enclosing block.
Scoping rule dictates that the enclosing block cannot reference exceptions declared in a sub-block. Use the handler OTHERS to catch exception from the enclosing block.
*2 ways to raise exception from a sub-block to its enclosing block
BEGIN
...
DECLARE -- sub-block begins
past_due EXCEPTION;
BEGIN
...
IF ... THEN
RAISE past_due; -- exception raised from the sub-block
END IF;
END; -- sub-block ends
EXCEPTION -- exception handler at enclosing block
...
-- must use OTHERS to catch the past_due exception
WHEN OTHERS THEN
ROLLBACK;
END;
If the sub-block has an exception handler. The same exception can be raised again at the enclosing block by using RAISE statement at the sub-block's exception handler.
Declare
out_of_balance EXCEPTION;
BEGIN
...
BEGIN -- sub-block begins
...
IF ... THEN
RAISE out_of_balance; -- exception raised
END IF;
EXCEPTION
WHEN out_of_balance THEN
-- handle the error
RAISE;
...
END; -- sub-block ends
EXCEPTION -- exception handler at enclosing block
WHEN out_of_balance THEN
-- handle the error differently
END;
Procedure
• Syntax
PROCEDURE name [(parameter[, parameter, ...])] IS
[local declarations]
BEGIN
exception statements
[EXCEPTION
exception handlers]
END [name];
parameter_name
[ IN | OUT| IN OUT ] datatype [ {:= | DEFAULT} expr]
cannot impose the NOT NULL constraints on a parameter
cannot specify a constraint on the datatype, for example, NUMBER(4) will be illegal.
• RETURN statement cannot contain an expression.
Function
• Syntax
FUNCTION name [(parameter[, parameter, ...])] RETURN datatype IS
[local declarations]
BEGIN
exception statements
[EXCEPTION
exception handlers]
END [name];
parameter_name
[ IN | OUT| IN OUT ] datatype [ {:= | DEFAULT} expr]
cannot impose the NOT NULL constraints on a parameter
cannot specify a constraint on the datatype, for example, NUMBER(4) will be illegal.
• Must contain RETURN statement with expression returning type defined at the specification.
Forward Declaration
- PL/SQL requires that every identifier must be declared before use. There are occasions where such declaration is not possible. For instance, 2 mutually recursive procedures will need to refer to each other before anyone such procedure can be declared.
- The solution is by Forward Declaration in which function or procedure's specifications are declared at the declaration. Another way to do forward declaration is to set up a package which can be separated into specification part and body part.
Packages
• holds logically related PL/SQL types (specification), objects (variables, constants, cursors...), and subprograms (functions, procedures).
CREATE PACKAGE name AS -- specification (visible part)
-- public type and object declarations
-- subprogram specifications
END [name];
CREATE PACKAGE BODY name AS -- body (hidden part)
-- private type and object declarations
-- subprogram bodies
[BEGIN
-- initialization statements ]
END [name];
• Package Body and Specification must be literally matched. That is, the same literal needs to appear at both the specification and body for cursor and subprogram declaration
• Referencing Package Contents
*Dot notation
package_name.type_name
package_name.object_name
package_name.subprogram_name
*Package can be referenced from a database trigger, a stored subprogram, an Oracle Precompiler application, an OCI application, an Oracle tool like SQL*Plus
• Product-specific packages
Oracle has provided some packages that come with different tools to help build Pl/SQL applications.
*DBMS_STANDARD (extension to STANDARD package)
provides language facilities that help to interact with Oracle like
raise_application_error which when called, ends the subprogram, rolls back any database changes it made, and returns a user-defined error number (-20000 .. -20999) and error message to the application
Procedure raise_salary(emp_id number, increase number) is
...
begin
select ...
if current_salary is null then
raise_application_error(-20101, 'Salary is missing');
end if;
end raise_salary;
exec SQL execute
declare
...
null_salary exception;
pragma exception_init(null_salary, -20101);
begin
...
exception
when null_salary then insert into emp_audit values...
end;
end-exec;
*DBMS_SQL
allows PL/SQL to execute dynamic SQL (DDL & DML)
*DBMS_ALERT
enables database triggers to alert an application when specific database values change.
*DBMS_OUTPUT
enables the display of output from PL/SQL blocks and subprograms. The put_line procedure outputs information to a buffer in the SGA which can then be displayed by calling the procedure get_line or SET SERVEROUTPUT ON in SQL
*DBMS_PIPE
allow different sessions to communicate over named pipes. (eg pack_message / send message, receive_message / unpack_message)
*UTL_FILE
allows PL/SQL programs to read and write operating system text files.
Execution Environments
• SQL*Plus
*Input, Store and Run a PL/SQL block
- begins with either DECLARE or BEGIN
- unlabeled PL/SQL block only
- ending the block by a dot < . > or slash < / >
- script file can be saved ( SQL> SAVE <filename> )
*Create, load, and run a script containing Pl/SQL blocks, subprograms, and/or packages
- script can contain SQL*Plus statements, PL/SQL blocks, subprograms, and/or packages. These components will get run sequentially.
- Pl/SQL block can make use of SQL*Plus substitution variable feature. ( &variable )
- bind variable can be used to set up variables which can both be referred by SQL*Plus and PL/SQL. In PL/SQL, the bind variable needs to be referred by prefixing with colon < : >, to print the bind variable in SQL*Plus, use PRINT statement.
- bind variable of REFCURSOR can be used to set up cursor variables which can be used to return query results. The variable can either be displayed using PRINT statement, or by SET AUTOPRINT ON
CREATE PACKAGE emp_data AS
TYPE EmpRecTyp IS RECORD (
emp_id NUMBER(4),
emp_name CHAR(10),
job_title CHAR(9),
dept_name CHAR(14),
dept_loc CHAR(13));
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp
-- EmpCurTyp is a strong Cursor variable
-- if no return type is specified, weal Cursor variable
PROCEDURE get_staff -- parameter must be IN OUT mode
(dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp);
END;
/
CREATE PACKAGE BODY emp_data AS
PROCEDURE get_staff -- parameter must be IN OUT mode
(dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR
SELECT empno, ename, job, dname, loc FROM emp, dept
WHERE emp.deptno = deptno AND
emp.deptno = dept.deptno
ORDER BY empno;
END;
END;
/
COLUMN EMPNO HEADING Number
COLUMN ENAME HEADING Name
COLUMN JOB HEADING JobTitle
COLUMN DNAME HEADING Department
COLUMN LOC HEADING Location
SET AUTOPRINT ON
VARIABLE cv REFCURSOR
EXECUTE emp_data.get_staff(10, :cv);
• Other environments
*Oracle Precompiler Evnvironment
*OCI Environment
Privileges Required to Execute a Procedure
A stored subprogram or package executes in the security domain of the owner of the procedure. So the granted (execute) user does not have to be granted the privileges to the objects referenced by the procedure.
• To execute a standalone or packaged procedure owned by another user, you need the following
*EXECUTE privilege for the standalone procedure or package
*Include the owner's name in the call, as in EXECUTE jward.fire_emp(1043)
Calling stored functions from SQL expressions
user-written PL/SQL functions can be included in SQL expressions (PL/SQL 2.1 or greater) Note that procedures are called as statements. Therefore, PL/SQL procedures are not directly callable from SQL statements. Functions referenced in a SQL expression can call a PL/SQL procedure
• Possible usages
- ADT implementation; manipulate character strings to represent special datatypes (ex. temperature, latitude, longitude)
- Increase query efficiency by letting the function to do filtering of data. Otherwise, superset of data need to be retrieved and processed by the application; this saves query cost as well as network cost
- Extend SQL expressiveness; the SELECT LIST can include user-functions which can be used to do complex calculation
- Parallel query execution is supported as SQL statements in PL/SQL function may be executed in parallel.
• Where can be used?
- SELECT LIST
- WHERE & HAVING clause's condition
- CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses
- VALUES clause in INSERT
- SET clause of the UPDATE
• Requirements to be callable from SQL
- must be a stored function
- must be a row function; that is, it cannnot take an entire column of data as its argument
- all its formal parameters must be IN parameters
formal parameters datatype must be Oracle Server internal types
return type must be an Oracle Server internal type (this feature is less powerful than a OO database as Oracle's internal types are non-structured)
• Controlling side-effect
- should be free of side-effect (changing variable or data that are not local to the function).
- standalone functions can be enforced by checking the function body. For packaged functions which the body is hidden, you need to use the prgama (compiler directive) RESTRICT_REFERENCES in the package specification to enfore the rules; the pragma tells the compiler to deny the packaged function read/write access to database tables, packaged variables, or both.
- An issue arises when the package contains initialization code that read/write the database state. Then the package itself needs to specify its purity from side effect by having PRAGMA RESTRICT_REFERENCES to the package name. There are occasions where package's purity is less than the function which causing the function not callable from SQL. Moving the initialization into a procedure of the package is a possibility
- PRAGMA RESTRICT_REFERENCES (function_name, WNDS [, WNPS] [, RNDS] [, RNPS]);
WNDS (writes no database state)
WNPS (writes no package state)
RNDS (reads no databse state)
RNPS (read no package state)
- the function cannot modify database tables,
- functions that read or write the values of packaged variables cannot be executed remotely or in parallel
- only functions called from a SELECT, VALUES, or SET clause can write the values of packaged variables
- function cannot call another subprogram that breaks rules above
Listing information about procedures and packages
- ALL_ERRORS, USER_ERRORS, DBA_ERRORS
- ALL_SOURCE, USER_SOURCE, DBA_SOURCE
- USER_OBJECT_SIZE, DBA_OBJECT_SIZE
- Examples
SELECT name, type, line, position, text FROM user_errors;
SELECT line, text FROM user_source WHERE name='HIRE_EMP';
SELECT name, source_size + parsed_size + code_size + error_size
"Total Size"
FROM user_object_size WHERE name='HIRE_EMP';
Input / Ouput Packages
• DBMS_PIPE (PL/SQL Input/Ouput, Oracle7 Server Application Developer's Guide)
- Information can be exchanged across 2 or more sessions in the same instance via an Oracle PIPE (buffered in the SGA area)
- PUBLIC pipe can be created by user when calling CREATE_PIPE, PRIVATE pipe can only be accessed by SYSDBA
• DBMS_OUTPUT
- use to send messages from stored procedures, packages, and triggers to a buffer which can then be read by another trigger, procedure, or package.
- Server Manager or SQL*Plus can also display messages when SET SERVEROUTPUT ON