-- 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. Moreover, you can 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.
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 objects can be declared. Once declared, objects 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
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.
part_no NUMBER(4); in_stock BOOLEAN;
You can also declare records and PL/SQL tables using the RECORD and TABLE composite datatypes.
tax := price * tax_rate; bonus := current_salary * 0.10; amount := TO_NUMBER(SUBSTR('750 dollars', 1, 3)); valid := FALSE;
The second way to assign values to a variable is to select or fetch database values into it. In the following example, you have Oracle compute a 10% bonus when you select the salary of an employee:
SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;
Then, you can use the variable bonus in another computation or insert its value into a database table.
credit_limit CONSTANT REAL := 5000.00;
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
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.
A cursor FOR loop implicitly declares its loop index as a record that represents a row in a database table, 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; END;
You use dot notation to reference individual fields in the record.
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 packaged procedure opens the cursor variable generic_cv for the chosen query:
CREATE PACKAGE BODY emp_data AS PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp, choice IN 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 open_cv; END emp_data;
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.
Columns in a row and corresponding fields in a record have the same names and datatypes. In the example below, you declare a record named dept_rec. Its fields have the same names and datatypes as the columns in the dept table.
DECLARE dept_rec dept%ROWTYPE; -- declare record variable
You use dot notation to reference fields, as the following example shows:
my_deptno := dept_rec.deptno;
If you declare a cursor that retrieves the last name, salary, hire date, and job title of an employee, you can use %ROWTYPE to declare a record that stores the same information, as follows:
DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, job FROM emp; emp_rec c1%ROWTYPE; -- declare record variable that -- represents a row in the emp table
When you execute the statement
FETCH c1 INTO emp_rec;
the value in the ename column of the emp table is assigned to the ename field of emp_rec, the value in the sal column is assigned to the sal field, and so on. Figure 1 - 3 shows how the result might appear.
Figure 1 - 3. %ROWTYPE Record
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;
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. This can improve performance and simplify the integrity checks built into Oracle Forms applications.
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, suppose that you are a manufacturer of custom-made cars and that each car has a serial number. To keep track of which customer buys each car, you might use the following FOR loop:
FOR i IN 1..order_qty LOOP UPDATE sales SET custno = customer_id WHERE serial_num = serial_num_seq.NEXTVAL; 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 yields TRUE, the sequence of statements is executed, then control resumes at the top of the loop. If the condition yields 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 $4000 and is higher in the chain of command than employee 7902:
-- available online in file EXAMP3 DECLARE salary emp.sal%TYPE; mgr_num emp.mgr%TYPE; last_name emp.ename%TYPE; starting_empno CONSTANT NUMBER(4) := 7902; BEGIN SELECT sal, mgr INTO salary, mgr_num FROM emp WHERE empno = starting_empno; WHILE salary < 4000 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; 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 yields 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
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;
PL/SQL tables help you move bulk data. They can store columns or rows of Oracle data, and they can be passed as parameters. So, PL/SQL tables make it easy to move collections of data into and out of database tables or between client-side applications and stored subprograms.
You can use a cursor FOR loop to fetch an entire column or table of Oracle data into a PL/SQL table. In the following example, you fetch a table of data into the PL/SQL table dept_tab:
DECLARE TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER; dept_tab DeptTabTyp; n BINARY_INTEGER := 0; BEGIN FOR dept_rec IN (SELECT * FROM dept) LOOP n := n + 1; dept_tab(n) := dept_rec; END LOOP; ... END;
Records contain uniquely named fields, which can have different datatypes. Suppose you have various data about an employee such as name, salary, and hire date. These items are dissimilar in type but logically related. A record containing a field for each item lets you treat the data as a logical unit. Consider the following example:
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));
Notice that you can nest records. That is, a record can be the component of another record.
PROCEDURE award_bonus (emp_id NUMBER) IS bonus REAL; comm_missing EXCEPTION; BEGIN 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 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.
Packages usually have two parts: a specification and a body. The specification is the interface to your applications; it declares the types, constants, variables, exceptions, cursors, and subprograms available for use. The body defines cursors and subprograms and so implements the specification.
In the following example, you package two employment procedures:
CREATE PACKAGE emp_actions AS -- package specification PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions; CREATE PACKAGE BODY emp_actions AS -- package body PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS BEGIN INSERT INTO emp VALUES (empno, ename, ...); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; END emp_actions;
Only the declarations in the package specification are visible and accessible to applications. Implementation details in the package body are hidden and inaccessible.
Packages can be compiled and stored in an Oracle database, where their contents can be shared by many applications. When you call a packaged subprogram for the first time, the whole package is loaded into memory. So, subsequent calls to related subprograms in the package require no disk I/O. Thus, packages can enhance productivity and improve performance.
With PL/SQL packages, you can specify whether types, program objects, and subprograms are public or private. Thus, packages enforce data encapsulation by letting you put type declarations in a black box. A private type definition is hidden and inaccessible. Only the package, not your application, is affected if the definition changes. This simplifies maintenance and enhancement.
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 salary NUMBER(7,2); commission NUMBER(7,2); comm_missing EXCEPTION; -- declare exception BEGIN SELECT sal, comm INTO salary, commission FROM emp WHERE empno = :emp_id; IF commission IS NULL THEN RAISE comm_missing; -- raise exception ELSE :bonus := (salary * 0.05) + (commission * 0.15); END IF; EXCEPTION -- begin exception handlers WHEN comm_missing THEN -- process error END;
The variables emp_id and bonus are declared and assigned values in a host environment. For more information about host variables, see "Oracle Precompiler Environment" .