|PL/SQL User's Guide and Reference
10g Release 1 (10.1)
Part Number B10807-01
The limits of my language mean the limits of my world. —Ludwig Wittgenstein
This chapter introduces the main features of the PL/SQL language. It shows how PL/SQL deals with the challenges of database programming, and how you can reuse techniques that you know from other programming languages.
This chapter contains these topics:
See Also:Access additional information and code samples for PL/SQL on the Oracle Technology Network, at
Support for SQL
Support for object-oriented programming
Tight integration with Oracle
The PL/SQL language is tightly integrated with SQL. You do not have to translate between SQL and PL/SQL datatypes: a
VARCHAR2 column in the database is stored in a
VARCHAR2 variable in PL/SQL. This integration saves you both learning time and processing time. Special PL/SQL language features let you work with table columns and rows without specifying the datatypes, saving on maintenance work when the table definitions change.
Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages.
Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation.
Many database features, such as triggers and object types, make use of PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL.
SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like commands such as
DELETE make it easy to manipulate the data stored in a relational database.
PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and pseudocolumns. This extensive SQL support lets you manipulate Oracle data flexibly and safely. Also, PL/SQL fully supports SQL datatypes, reducing the need to convert data passed between your applications and the database.
PL/SQL also supports dynamic SQL, a programming technique that makes your applications more flexible and versatile. Your programs can build and process SQL data definition, data control, and session control statements at run time, without knowing details such as table names and
WHERE clauses in advance.
Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead.
With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application. As Figure 1-1 shows, you can use PL/SQL blocks and subprograms to group SQL statements before sending them to the database for execution. PL/SQL even has language features to further speed up SQL statements that are issued inside a loop.
PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient. Because stored procedures execute in the database server, a single call over the network can start a large job. This division of work reduces network traffic and improves response times. Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.
Figure 1-1 PL/SQL Boosts Performance
PL/SQL extends tools such as Oracle Forms and Oracle Reports. With PL/SQL in these tools, you can use familiar language constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits.
PL/SQL is the same in all environments. Once you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.
Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs. With PL/SQL, you can write portable program libraries and reuse them in different environments.
PL/SQL stored procedures move application code from the client to the server, where you can protect it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself or to the text of the
Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.
Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides allowing you to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.
By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods. Also, object types hide implementation details, so that you can change the details without affecting client programs.
In addition, object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. This direct mapping helps your programs better reflect the world they are trying to simulate.
You can control program flow with statements like
LOOP. As with other procedural programming languages, you can declare variables, define procedures and functions, and trap runtime errors.
PL/SQL lets you break complex problems down into easily understandable procedural code, and reuse this code across multiple applications. When a problem can be solved through plain SQL, you can issue SQL commands directly inside your PL/SQL programs, without learning new APIs. PL/SQL's data types correspond with SQL's column types, making it easy to interchange PL/SQL variables with data inside a table.
A block groups related declarations and statements. You can place declarations close to where they are used, even inside a large subprogram. The declarations are local to the block and cease to exist when the block completes, helping to avoid cluttered namespaces for variables and procedures.
As Figure 1-2 shows, a PL/SQL block has three parts: a declarative part, an executable part, and an exception-handling part that deals with error conditions. Only the executable part is required.
First comes the declarative part, where you define types, variables, and similar items. These items are manipulated in the executable part. Exceptions raised during execution can be dealt with in the exception-handling part.
Figure 1-2 Block Structure
You can nest blocks in the executable and exception-handling parts of a PL/SQL block or subprogram but not in the declarative part. You can define local subprograms in the declarative part of any block. You can call local subprograms only from the block in which they are defined.
PL/SQL lets you declare constants and variables, then use them in SQL and procedural statements anywhere an expression can be used. You must declare a constant or variable before referencing it in any other statements.
Variables can have any SQL datatype, such as
NUMBER, or a PL/SQL-only datatype, such as
PLS_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
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
RECORD composite datatypes.
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 salary * 0.10 INTO bonus FROM employees WHERE employee_id = emp_id;
The third way to assign values to a variable is by passing it as an
OUT parameter to a subprogram, and doing the assignment inside the subprogram. The following example passes a variable to a subprogram, and the subprogram updates the variable:
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
Processing a SQL query with PL/SQL is like processing files with other languages. For example, a Perl program opens a file, reads the file contents, processes each line, then closes the file. In the same way, a PL/SQL program issues a query and processes the rows from the result set:
FOR someone IN (SELECT * FROM employees) LOOP DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name); DBMS_OUTPUT.PUT_LINE('Last name = ' || someone.last_name); END LOOP;
You can use a simple loop like the one shown here, or you can control the process precisely by using individual statements to perform the query, retrieve data, and finish processing.
As part of the declaration for each PL/SQL variable, you declare its datatype. Usually, this datatype is one of the types shared between PL/SQL and SQL, such as
VARCHAR2(length). For easier maintenance of code that interacts with the database, you can also use the special qualifiers
%ROWTYPE to declare variables that hold table columns or table rows.
%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:
%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.
In PL/SQL, records are used to group data. A record consists of a number of related fields in which data values can be stored. The
%ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable.
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
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 fetched from 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.
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
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.
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
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;
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;
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;
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
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;
PL/SQL lets you break an application down into manageable, well-defined modules. PL/SQL meets this need with program units, which include blocks, subprograms, and packages. You can reuse program units by loading them into the database as triggers, stored procedures, and stored functions.
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.
PL/SQL lets you bundle logically related types, variables, cursors, and subprograms into a package, a database object that is a step above regular stored procedures. The packages defines a simple, clear, interface to a set of related procedures and types.
Packages usually have two parts: a specification and a body. The specification defines the application programming interface; it declares the types, constants, variables, exceptions, cursors, and subprograms. The body fills in the SQL queries for cursors and the code for subprograms.
The following example packages 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;
Applications that call these procedures only need to know the names and parameters from the package spec. You can change the implementation details inside the package body without affecting the calling applications.
Packages are stored in the database, where they can be shared by many applications. Calling a packaged subprogram for the first time loads the whole package and caches it in memory, saving on disk I/O for subsequent calls. Thus, packages enhance reuse and improve performance in a multi-user, multi-application environment.
Data abstraction lets you work with the essential properties of data without being too involved with details. Once you design a data structure, you can focus on designing algorithms that manipulate the data structure.
PL/SQL collection types let you declare high-level datatypes similar to arrays, sets, and hash tables found in other languages. In PL/SQL, array types are known as varrays (short for variable-size arrays), set types are known as nested tables, and hash table types are known as associative arrays. Each kind of collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.
To reference an element, use subscript notation with parentheses. For example, the following call references the fifth element in the nested table (of type
Staff) returned by function
DECLARE TYPE Staff IS TABLE OF Employee; staffer Employee; FUNCTION new_hires (hiredate DATE) RETURN Staff IS BEGIN ... END; BEGIN staffer := new_hires('10-NOV-98')(5); END;
Collections can be passed as parameters, so that subprograms can process arbitrary numbers of elements.You can use collections to move data into and out of database tables using high-performance language features known as bulk SQL.
Records are composite data structures whose fields can have different datatypes. You can use records to hold related items and pass them to subprograms with a single parameter.
You can use the
%ROWTYPE attribute to declare a record that represents a row in a table or a row from a query result set, without specifying the names and types for the fields.
Consider the following example:
DECLARE TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT); TYPE MeetingTyp IS RECORD ( date_held DATE, duration TimeRec, -- nested record location VARCHAR2(20), purpose VARCHAR2(50));
PL/SQL supports object-oriented programming through object types. An object type encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are known as attributes. The functions and procedures that manipulate the attributes are known as methods.
Object types reduce complexity by breaking down a large system into logical entities. This lets you create software components that are modular, maintainable, and reusable.
Object-type definitions, and the code for the methods, are stored in the database. Instances of these object types can be stored in tables or used as variables inside PL/SQL code.
CREATE TYPE Bank_Account AS OBJECT ( acct_number INTEGER(5), balance REAL, status VARCHAR2(10), MEMBER PROCEDURE open (amount IN REAL), MEMBER PROCEDURE verify_acct (num IN INTEGER), MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL), MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL), MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL), MEMBER FUNCTION curr_bal (num IN INTEGER) RETURN REAL );
PL/SQL makes it easy to detect and process error conditions known as exceptions. When an error occurs, an exception is raised: normal execution stops and control transfers to special exception-handling code, which comes at the end of any PL/SQL block. Each different exception is processed by a particular exception handler.
Predefined exceptions are raised automatically for certain common error conditions involving variables or database operations. For example, if you try to divide a number by zero, PL/SQL raises the predefined exception
You can declare exceptions of your own, for conditions that you decide are errors, or to correspond to database errors that normally result in ORA- error messages. When you detect a user-defined error condition, you execute a
RAISE statement. The following example computes the bonus earned by a salesperson. The bonus is based on salary and commission. If the commission is null, you raise the exception
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
The PL/SQL compilation and run-time system is 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.
In either environment, the PL/SQL engine accepts as input any valid PL/SQL block or subprogram. Figure 1-3 shows the PL/SQL engine processing an anonymous block. The PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine in the Oracle database.
Figure 1-3 PL/SQL Engine
Typically, the Oracle database server processes PL/SQL blocks and subprograms.
Anonymous PL/SQL blocks can be submitted to interactive tools such as SQL*Plus and Enterprise Manager, or embedded in an Oracle Precompiler or OCI program. At run time, the program sends these blocks to the Oracle database, where they are compiled and executed.
Subprograms can be compiled and stored in an Oracle database, ready to be executed. Once compiled, it is a schema object known as a stored procedure or stored function, which can be referenced by any number of applications connected to that database.
Stored subprograms are the key to modular, reusable PL/SQL code. Wherever you might use a JAR file in Java, a module in Perl, a shared library in C++, or a DLL in Visual Basic, you should use PL/SQL stored procedures, stored functions, and packages.
You can call stored subprograms from a database trigger, another stored subprogram, an Oracle Precompiler or OCI application, or interactively from SQL*Plus or Enterprise Manager. You can also configure a web server so that the HTML for a web page is generated by a stored subprogram, making it simple to provide a web interface for data entry and report generation.
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 a compact compiled form. When called, they are loaded and processed immediately. Subprograms take advantage of shared memory, so that only one copy of a subprogram is loaded into memory for execution by multiple users.
A database trigger is a stored subprogram associated with a database table, view, or event. The trigger can be called once, when some event occurs, or many times, once for each row affected by an
DELETE statement. The trigger can be called after the event, to record it or take some followup action. Or, the trigger can be called before the event to prevent erroneous operations or fix new data so that it conforms to business rules. 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 Oracle Database Application Developer's Guide - Fundamentals.
An application development tool that contains the PL/SQL engine can process PL/SQL blocks and subprograms. The tool passes the blocks to its local PL/SQL engine. The engine executes all procedural statements inside the application and sends only SQL statements to the database. Most of the work is done inside the application, not on the database server. If the block contains no SQL statements, the application executes the entire block. This is useful if your application can benefit from conditional and iterative control.
Frequently, Oracle Forms applications use SQL statements to test the value of field entries or to do simple computations. By using PL/SQL instead, you can avoid calls to the database. You can also use PL/SQL functions to manipulate field entries.