Overview of PL/SQL

A subset of Oracle 9i docs of for CS430/630.

Understanding the Main Features of PL/SQL

A good way to get acquainted with PL/SQL is to look at a sample program. The program below processes an order for a tennis racket. First, it declares a variable of type NUMBER to store the quantity of tennis rackets on hand. Then, it retrieves the quantity on hand from a database table named inventory. If the quantity is greater than zero, the program updates the table and inserts a purchase record into another table named purchase_record. Otherwise, the program inserts an out-of-stock record into the purchase_record table.

-- available online in file 'examp1'
DECLARE
 qty_on_hand NUMBER(5);
BEGIN
 SELECT quantity INTO qty_on_hand FROM inventory
 WHERE product = 'TENNIS RACKET'
 FOR UPDATE OF quantity;
 IF qty_on_hand > 0 THEN -- check quantity
 UPDATE inventory SET quantity = quantity - 1
 WHERE product = 'TENNIS RACKET';
 INSERT INTO purchase_record
 VALUES ('Tennis racket purchased', SYSDATE);
 ELSE
 INSERT INTO purchase_record
 VALUES ('Out of tennis rackets', SYSDATE);
 END IF;
 COMMIT;
END;
 

With PL/SQL, you can use SQL statements to manipulate Oracle data and flow-of-control statements to process the data. You can also declare constants and variables, define procedures and functions, and trap runtime errors. Thus, PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages.

Block Structure

PL/SQL is a block-structured language. That is, the basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested sub-blocks. Typically, each logical block corresponds to a problem or subproblem to be solved. Thus, PL/SQL supports the divide-and-conquer approach to problem solving called stepwise refinement.

A block (or sub-block) lets you group logically related declarations and statements. That way, you can place declarations close to where they are used. The declarations are local to the block and cease to exist when the block completes.

As Figure 1-1 shows, a PL/SQL block has three parts: a declarative part, an executable part, and an exception-handling part. (In PL/SQL, a warning or error condition is called an exception.) Only the executable part is required.

The order of the parts is logical. First comes the declarative part, in which items can be declared. Once declared, items can be manipulated in the executable part. Exceptions raised during execution can be dealt with in the exception-handling part.

Figure 1-1 Block Structure

Text description of the illustration pls81001_block_structure.gif

You can nest sub-blocks in the executable and exception-handling parts of a PL/SQL block or subprogram but not in the declarative part. Also, you can define local subprograms in the declarative part of any block. However, you can call local subprograms only from the block in which they are defined.

Variables and Constants

PL/SQL lets you declare constants and variables, then use them in SQL and procedural statements anywhere an expression can be used. However, forward references are not allowed. So, you must declare a constant or variable before referencing it in other statements, including other declarative statements.

Declaring Variables

Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or any PL/SQL datatype, such as BOOLEAN or BINARY_INTEGER. For example, assume that you want to declare a variable named part_no to hold 4-digit numbers and a variable named in_stock to hold the Boolean value TRUE or FALSE. You declare these variables as follows:

part_no NUMBER(4);
in_stock BOOLEAN;
 

You can also declare nested tables, variable-size arrays (varrays for short), and records using the TABLE, VARRAY, and RECORD composite datatypes.

Assigning Values to a Variable

You can assign values to a variable in three ways. The first way uses the assignment operator (:=), a colon followed by an equal sign. You place the variable to the left of the operator and an expression (which can include function calls) to the right. A few examples follow:

tax := price * tax_rate;
valid_id := FALSE;
bonus := current_salary * 0.10;
wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;
 

The second way to assign values to a variable is by selecting (or fetching) database values into it. In the example below, you have Oracle compute a 10% bonus when you select the salary of an employee. Now, you can use the variable bonus in another computation or insert its value into a database table.

SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;
 

The third way to assign values to a variable is by passing it as an OUT or IN OUT parameter to a subprogram. As the following example shows, an IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller:

DECLARE
 my_sal REAL(7,2);
 PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ...
BEGIN
 SELECT AVG(sal) INTO my_sal FROM emp;
 adjust_salary(7788, my_sal); -- assigns a new value to my_sal

Declaring Constants

Declaring a constant is like declaring a variable except that you must add the keyword CONSTANT and immediately assign a value to the constant. Thereafter, no more assignments to the constant are allowed. In the following example, you declare a constant named credit_limit:

credit_limit CONSTANT REAL := 5000.00;

Cursors

Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows:

DECLARE
 CURSOR c1 IS
 SELECT empno, ename, job FROM emp WHERE deptno = 20;
 

The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria. As Figure 1-2 shows, an explicit cursor "points" to the current row in the result set. This allows your program to process the rows one at a time.

Figure 1-2 Query Processing

Text description of the illustration pls81003_query_processing.gif

Multi-row query processing is somewhat like file processing. For example, a COBOL program opens a file, processes records, then closes the file. Likewise, a PL/SQL program opens a cursor, processes rows returned by a query, then closes the cursor. Just as a file pointer marks the current position in an open file, a cursor marks the current position in a result set.

You use the OPEN, FETCH, and CLOSE statements to control a cursor. The OPEN statement executes the query associated with the cursor, identifies the result set, and positions the cursor before the first row. The FETCH statement retrieves the current row and advances the cursor to the next row. When the last row has been processed, the CLOSE statement disables the cursor.

Cursor FOR Loops

In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a record that represents a row fetched from the database. Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, then closes the cursor when all rows have been processed. In the following example, the cursor FOR loop implicitly declares emp_rec as a record:

DECLARE
 CURSOR c1 IS
 SELECT ename, sal, hiredate, deptno FROM emp;
 ...
BEGIN
 FOR emp_rec IN c1 LOOP
 ...
 salary_total := salary_total + emp_rec.sal;
 END LOOP;
 

To reference individual fields in the record, you use dot notation, in which a dot (.) serves as the component selector.

Cursor Variables

Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, unlike a cursor, a cursor variable can be opened for any type-compatible query. It is not tied to a specific query. Cursor variables are true PL/SQL variables, to which you can assign new values and which you can pass to subprograms stored in an Oracle database. This gives you more flexibility and a convenient way to centralize data retrieval.

Typically, you open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters. The following procedure opens the cursor variable generic_cv for the chosen query:

PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS
BEGIN
 IF choice = 1 THEN
 OPEN generic_cv FOR SELECT * FROM emp;
 ELSIF choice = 2 THEN
 OPEN generic_cv FOR SELECT * FROM dept;
 ELSIF choice = 3 THEN
 OPEN generic_cv FOR SELECT * FROM salgrade;
 END IF;
 ...
END;

Attributes

PL/SQL variables and cursors have attributes, which are properties that let you reference the datatype and structure of an item without repeating its definition. Database columns and tables have similar attributes, which you can use to ease maintenance. A percent sign (%) serves as the attribute indicator.

%TYPE

The %TYPE attribute provides the datatype of a variable or database column. This is particularly useful when declaring variables that will hold database values. For example, assume there is a column named title in a table named books. To declare a variable named my_title that has the same datatype as column title, use dot notation and the %TYPE attribute, as follows:

my_title books.title%TYPE;
 

Declaring my_title with %TYPE has two advantages. First, you need not know the exact datatype of title. Second, if you change the database definition of title (make it a longer character string for example), the datatype of my_title changes accordingly at run time.

Control Structures

Control structures are the most important PL/SQL extension to SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data using conditional, iterative, and sequential flow-of-control statements such as IF-THEN-ELSE, CASE, FOR-LOOP, WHILE-LOOP, EXIT-WHEN, and GOTO. Collectively, these statements can handle any situation.

Conditional Control

Often, it is necessary to take alternative actions depending on circumstances. The IF-THEN-ELSE statement lets you execute a sequence of statements conditionally. The IF clause checks a condition; the THEN clause defines what to do if the condition is true; the ELSE clause defines what to do if the condition is false or null.

Consider the program below, which processes a bank transaction. Before allowing you to withdraw $500 from account 3, it makes sure the account has sufficient funds to cover the withdrawal. If the funds are available, the program debits the account. Otherwise, the program inserts a record into an audit table.

-- available online in file 'examp2'
DECLARE
 acct_balance NUMBER(11,2);
 acct CONSTANT NUMBER(4) := 3;
 debit_amt CONSTANT NUMBER(5,2) := 500.00;
BEGIN
 SELECT bal INTO acct_balance FROM accounts
 WHERE account_id = acct
 FOR UPDATE OF bal;
 IF acct_balance >= debit_amt THEN
 UPDATE accounts SET bal = bal - debit_amt
 WHERE account_id = acct;
 ELSE
 INSERT INTO temp VALUES
 (acct, acct_balance, 'Insufficient funds');
 -- insert account, current balance, and message
 END IF;
 COMMIT;
END;
 

To choose among several values or courses of action, you can use CASE constructs. The CASE expression evaluates a condition and returns a value for each case. The case statement evaluates a condition and performs an action (which might be an entire PL/SQL block) for each case.

-- This CASE statement performs different actions based
-- on a set of conditional tests.
CASE
 WHEN shape = 'square' THEN area := side * side;
 WHEN shape = 'circle' THEN 
 BEGIN
 area := pi * (radius * radius);
 DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is 
irrational.');
 END;
 WHEN shape = 'rectangle' THEN area := length * width;
 ELSE
 BEGIN
 DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' || 
shape);
 RAISE PROGRAM_ERROR;
  END;
END CASE;
 

A sequence of statements that uses query results to select alternative actions is common in database applications. Another common sequence inserts or deletes a row only if an associated entry is found in another table. You can bundle these common sequences into a PL/SQL block using conditional logic.

Iterative Control

LOOP statements let you execute a sequence of statements multiple times. You place the keyword LOOP before the first statement in the sequence and the keywords END LOOP after the last statement in the sequence. The following example shows the simplest kind of loop, which repeats a sequence of statements continually:

LOOP
 -- sequence of statements
END LOOP;
 

The FOR-LOOP statement lets you specify a range of integers, then execute a sequence of statements once for each integer in the range. For example, the following loop inserts 500 numbers and their square roots into a database table:

FOR num IN 1..500 LOOP
 INSERT INTO roots VALUES (num, SQRT(num));
END LOOP;
 

The WHILE-LOOP statement associates a condition with a sequence of statements. Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.

In the following example, you find the first employee who has a salary over $2500 and is higher in the chain of command than employee 7499:

-- available online in file 'examp3'
DECLARE
 salary emp.sal%TYPE := 0;
 mgr_num emp.mgr%TYPE;
 last_name emp.ename%TYPE;
 starting_empno emp.empno%TYPE := 7499;
BEGIN
 SELECT mgr INTO mgr_num FROM emp 
 WHERE empno = starting_empno;
 WHILE salary <= 2500 LOOP
 SELECT sal, mgr, ename INTO salary, mgr_num, last_name
 FROM emp WHERE empno = mgr_num;
 END LOOP;
 INSERT INTO temp VALUES (NULL, salary, last_name);
 COMMIT;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 INSERT INTO temp VALUES (NULL, NULL, 'Not found');
 COMMIT;
END;
 

The EXIT-WHEN statement lets you complete a loop if further processing is impossible or undesirable. When the EXIT statement is encountered, the condition in the WHEN clause is evaluated. If the condition is true, the loop completes and control passes to the next statement. In the following example, the loop completes when the value of total exceeds 25,000:

LOOP
 ...
 total := total + salary;
 EXIT WHEN total > 25000; -- exit loop if condition is true
END LOOP;
-- control resumes here

Sequential Control

The GOTO statement lets you branch to a label unconditionally. The label, an undeclared identifier enclosed by double angle brackets, must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block, as the following example shows:

IF rating > 90 THEN
 GOTO calc_raise; -- branch to label
END IF;
...
<<calc_raise>>
IF job_title = 'SALESMAN' THEN -- control resumes here
 amount := commission * 0.25;
ELSE
 amount := salary * 0.10;
END IF;

Modularity

Modularity lets you break an application down into manageable, well-defined modules. Through successive refinement, you can reduce a complex problem to a set of simple problems that have easy-to-implement solutions. PL/SQL meets this need with program units, which include blocks, subprograms, and packages.

Subprograms

PL/SQL has two types of subprograms called procedures and functions, which can take parameters and be invoked (called). As the following example shows, a subprogram is like a miniature program, beginning with a header followed by an optional declarative part, an executable part, and an optional exception-handling part:

PROCEDURE award_bonus (emp_id NUMBER) IS
 bonus REAL;
 comm_missing EXCEPTION;
BEGIN -- executable part starts here
 SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;
 IF bonus IS NULL THEN
 RAISE comm_missing;
 ELSE
 UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
 END IF;
EXCEPTION -- exception-handling part starts here
 WHEN comm_missing THEN
 ...
END award_bonus;
 

When called, this procedure accepts an employee number. It uses the number to select the employee's commission from a database table and, at the same time, compute a 15% bonus. Then, it checks the bonus amount. If the bonus is null, an exception is raised; otherwise, the employee's payroll record is updated.

Error Handling

PL/SQL makes it easy to detect and process predefined and user-defined error conditions called exceptions. When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. To handle raised exceptions, you write separate routines called exception handlers.

Predefined exceptions are raised implicitly by the runtime system. For example, if you try to divide a number by zero, PL/SQL raises the predefined exception ZERO_DIVIDE automatically. You must raise user-defined exceptions explicitly with the RAISE statement.

You can define exceptions of your own in the declarative part of any PL/SQL block or subprogram. In the executable part, you check for the condition that needs special attention. If you find that the condition exists, you execute a RAISE statement. In the example below, you compute the bonus earned by a salesperson. The bonus is based on salary and commission. So, if the commission is null, you raise the exception comm_missing.

DECLARE
 ...
 comm_missing EXCEPTION; -- declare exception
BEGIN
 ...
 IF commission IS NULL THEN
 RAISE comm_missing; -- raise exception
 END IF;
 bonus := (salary * 0.10) + (commission * 0.15);
EXCEPTION
 WHEN comm_missing THEN ... -- process the exception

PL/SQL Architecture

The PL/SQL compilation and run-time system is a technology, not an independent product. Think of this technology as an engine that compiles and executes PL/SQL blocks and subprograms. The engine can be installed in an Oracle server or in an application development tool such as Oracle Forms or Oracle Reports. So, PL/SQL can reside in two environments:

• The Oracle database server
• Oracle tools

These two environments are independent. PL/SQL is bundled with the Oracle server but might be unavailable in some tools. In either environment, the PL/SQL engine accepts as input any valid PL/SQL block or subprogram. Figure 1-4 shows the PL/SQL engine processing an anonymous block. The engine executes procedural statements but sends SQL statements to the SQL Statement Executor in the Oracle server.

Figure 1-4 PL/SQL Engine

Text description of the illustration pls81004_plsql_engine.gif

In the Oracle Database Server

Application development tools that lack a local PL/SQL engine must rely on Oracle to process PL/SQL blocks and subprograms. When it contains the PL/SQL engine, an Oracle server can process PL/SQL blocks and subprograms as well as single SQL statements. The Oracle server passes the blocks and subprograms to its local PL/SQL engine.

Anonymous Blocks

Anonymous PL/SQL blocks can be embedded in an Oracle Precompiler or OCI program. At run time, the program, lacking a local PL/SQL engine, sends these blocks to the Oracle server, where they are compiled and executed. Likewise, interactive tools such as SQL*Plus and Enterprise Manager, lacking a local PL/SQL engine, must send anonymous blocks to Oracle.

Stored Subprograms

Subprograms can be compiled separately and stored permanently in an Oracle database, ready to be executed. A subprogram explicitly CREATEd using an Oracle tool is called a stored subprogram. Once compiled and stored in the data dictionary, it is a schema object, which can be referenced by any number of applications connected to that database.

Stored subprograms defined within a package are called packaged subprograms. Those defined independently are called standalone subprograms. Those defined within another subprogram or within a PL/SQL block are called local subprograms, which cannot be referenced by other applications and exist only for the convenience of the enclosing block.

Stored subprograms offer higher productivity, better performance, memory savings, application integrity, and tighter security. For example, by designing applications around a library of stored procedures and functions, you can avoid redundant coding and increase your productivity.

You can call stored subprograms from a database trigger, another stored subprogram, an Oracle Precompiler application, an OCI application, or interactively from SQL*Plus or Enterprise Manager. For example, you might call the standalone procedure create_dept from SQL*Plus as follows:

SQL> CALL create_dept('FINANCE', 'NEW YORK');
 

Subprograms are stored in parsed, compiled form. So, when called, they are loaded and passed to the PL/SQL engine immediately. Also, they take advantage of shared memory. So, only one copy of a subprogram need be loaded into memory for execution by multiple users.

Database Triggers

A database trigger is a stored subprogram associated with a database table, view, or event. For instance, you can have Oracle fire a trigger automatically before or after an INSERT, UPDATE, or DELETE statement affects a table. One of the many uses for database triggers is to audit data modifications. For example, the following table-level trigger fires whenever salaries in the emp table are updated:

CREATE TRIGGER audit_sal
 AFTER UPDATE OF sal ON emp
 FOR EACH ROW
BEGIN
 INSERT INTO emp_audit VALUES ...
END;
 

The executable part of a trigger can contain procedural statements as well as SQL data manipulation statements. Besides table-level triggers, there are instead-of triggers for views and system-event triggers for schemas. For more information, see Oracle9i Application Developer's Guide - Fundamentals.