Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)

B28370-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

1 Overview of PL/SQL

PL/SQL, the Oracle procedural extension of SQL, is a completely portable, high-performance transaction-processing language. This chapter explains its advantages and briefly describes its main features and its architecture.

Topics:

Advantages of PL/SQL

PL/SQL has these advantages:

Tight Integration with SQL

SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like statements such as SELECT, INSERT, UPDATE, and DELETE make it easy to manipulate the data stored in a relational database.

PL/SQL is tightly integrated with SQL. With PL/SQL, you can use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators, and pseudocolumns.

PL/SQL fully supports SQL data types. You need not convert between PL/SQL and SQL data types. For example, if your PL/SQL program retrieves a value from a database column of the SQL type VARCHAR2, it can store that value in a PL/SQL variable of the type VARCHAR2. Special PL/SQL language features let you work with table columns and rows without specifying the data types, 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, use PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL.

PL/SQL supports both static and dynamic SQL. Static SQL is SQL whose full text is known at compilation time. Dynamic SQL is SQL whose full text is not known until run time. Dynamic SQL enables you to make your applications more flexible and versatile. For information about using static SQL with PL/SQL, see Chapter 6, "Using Static SQL." For information about using dynamic SQL, see Chapter 7, "Using Dynamic SQL."

High Performance

With PL/SQL, an entire block of statements can be sent to the database 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 (procedures and functions) to group SQL statements before sending them to the database for execution. PL/SQL also has language features to further speed up SQL statements that are issued inside a loop.

PL/SQL stored subprograms are compiled once and stored in executable form, so subprogram calls are efficient. Because stored subprograms 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 subprograms are cached and shared among users, which lowers memory requirements and call overhead.

Figure 1-1 PL/SQL Boosts Performance

PL/SQL Boosts Performance
Description of "Figure 1-1 PL/SQL Boosts Performance"

High Productivity

PL/SQL lets you write very compact code for manipulating data. In the same way that scripting languages such as PERL can read, transform, and write data from files, PL/SQL can query, transform, and update data in a database. PL/SQL saves time on design and debugging by offering a full range of software-engineering features, such as exception handling, encapsulation, data hiding, and object-oriented data types.

PL/SQL extends tools such as Oracle Forms. 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. After you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.

Full Portability

Applications written in PL/SQL can run on any operating system and platform where the database runs. With PL/SQL, you can write portable program libraries and reuse them in different environments.

Tight Security

PL/SQL stored subprograms 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 subprogram that updates a table, but not grant them access to the table itself or to the text of the UPDATE statement. Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.

For information about wrapping, or hiding, the source of a PL/SQL unit, see Appendix A, "Wrapping PL/SQL Source Code".

Access to Predefined Packages

Oracle provides product-specific packages that define APIs you can invoke from PL/SQL to perform many useful tasks. These packages include DBMS_ALERT for using triggers, DBMS_FILE for reading and writing operating system text files, UTL_HTTP for making hypertext transfer protocol (HTTP) callouts, DBMS_OUTPUT for display output from PL/SQL blocks and subprograms, and DBMS_PIPE for communicating over named pipes. For more information about these packages, see Overview of Product-Specific PL/SQL Packages.

For complete information about the packages supplied by Oracle, see Oracle Database PL/SQL Packages and Types Reference.

Support for Object-Oriented Programming

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 enabling 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. For information about object types, see Oracle Database Object-Relational Developer's Guide.

Support for Developing Web Applications and Server Pages

You can use PL/SQL to develop Web applications and Server Pages (PSPs). For more information, see Using PL/SQL to Create Web Applications and Using PL/SQL to Create Server Pages.

Main Features of PL/SQL

PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages.

When a problem can be solved using SQL, you can issue SQL statements from your PL/SQL programs, without learning new APIs.

Like other procedural programming languages, PL/SQL lets you declare constants and variables, control program flow, define subprograms, and trap run-time errors.

You can break complex problems into easily understandable subprograms, which you can reuse in multiple applications.

Topics:

PL/SQL Blocks

The basic unit of a PL/SQL source program is the block, which groups related declarations and statements.

A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords partition the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required.

Declarations are local to the block and cease to exist when the block completes execution, helping to avoid cluttered namespaces for variables and subprograms.

Blocks can be nested: Because a block is an executable statement, it can appear in another block wherever an executable statement is allowed.

Example 1-1 shows the basic structure of a PL/SQL block. For the formal syntax description, see Block.

Example 1-1 PL/SQL Block Structure

DECLARE    -- Declarative part (optional)
  -- Declarations of local types, variables, & subprograms

BEGIN      -- Executable part (required)
  -- Statements (which can use items declared in declarative part)

[EXCEPTION -- Exception-handling part (optional)
  -- Exception handlers for exceptions raised in executable part]
END;

A PL/SQL block can be submitted to an interactive tool (such as SQL*Plus or Enterprise Manager) or embedded in an Oracle Precompiler or OCI program. The interactive tool or program executes the block only once. The block is not stored in the database.

A named PL/SQL block—a subprogram—can be invoked repeatedly (see PL/SQL Subprograms).

Note:

A block that is not stored in the database is called an anonymous block, even if it has a label.

PL/SQL Error Handling

PL/SQL makes it easy to detect and process error conditions, which are called 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.

PL/SQL exception handling differs from the manual checking that you do in C programming, where you insert a check to make sure that every operation succeeded. Instead, the checks and calls to error routines are performed automatically, similar to the exception mechanism in Java programming.

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 ZERO_DIVIDE automatically.

You can define exceptions of your own, for conditions that you decide are errors, or to correspond to database errors that normally result in ORA-n error messages. When you detect a user-defined error condition, you raise an exception with either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR. See the exception comm_missing in Example 1-16. In the example, if the commission is null, the exception comm_missing is raised.

Typically, you put an exception handler at the end of a subprogram to handle exceptions that are raised anywhere inside the subprogram. To continue executing from the spot where an exception happens, enclose the code that might raise an exception inside another BEGIN-END block with its own exception handler. For example, you might put separate BEGIN-END blocks around groups of SQL statements that might raise NO_DATA_FOUND, or around arithmetic operations that might raise DIVIDE_BY_ZERO. By putting a BEGIN-END block with an exception handler inside a loop, you can continue executing the loop even if some loop iterations raise exceptions. See Example 5-38.

For information about PL/SQL errors, see Overview of PL/SQL Run-Time Error Handling. For information about PL/SQL warnings, see Overview of PL/SQL Compile-Time Warnings.

PL/SQL Input and Output

Most PL/SQL input and output (I/O) is through SQL statements that store data in database tables or query those tables. All other PL/SQL I/O is done through APIs, such as the PL/SQL package DBMS_OUTPUT.

To display output passed to DBMS_OUTPUT, you need another program, such as SQL*Plus. To see DBMS_OUTPUT output with SQL*Plus, you must first issue the SQL*Plus command SET SERVEROUTPUT ON. For information about SET SERVEROUTPUT ON, see SQL*Plus User's Guide and Reference.

Other PL/SQL APIs for processing I/O are provided by packages such as:

Package(s) PL/SQL uses package ...
HTF and HTP to display output on a web page
DBMS_PIPE to pass information between PL/SQL and operating-system commands
UTL_FILE to reads and write operating system files
UTL_HTTP to communicate with web servers
UTL_SMTP to communicate with mail servers

Although some of the preceding APIs can accept input as well as display output, they have cannot accept data directly from the keyboard. For that, use the SQL*Plus commands PROMPT and ACCEPT.

See Also:

PL/SQL Variables and Constants

PL/SQL lets you declare variables and constants, and then use them in SQL and procedural statements anywhere an expression can be used. You must declare a variable or constant before referencing it in any other statements. For more information, see Declarations.

Topics:

Declaring PL/SQL Variables

A PL/SQL variable can have any SQL data type (such as CHAR, DATE, or NUMBER) or a PL/SQL-only data type (such as BOOLEAN or PLS_INTEGER).

Example 1-2 declares several PL/SQL variables. One has a PL/SQL-only data type; the others have SQL data types.

Example 1-2 PL/SQL Variable Declarations

SQL> DECLARE
  2    part_number       NUMBER(6);     -- SQL data type
  3    part_name         VARCHAR2(20);  -- SQL data type
  4    in_stock          BOOLEAN;       -- PL/SQL-only data type
  5    part_price        NUMBER(6,2);   -- SQL data type
  6    part_description  VARCHAR2(50);  -- SQL data type
  7  BEGIN
  8    NULL;
  9  END;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL> 

For more information about PL/SQL data types, see Chapter 3, "PL/SQL Data Types."

PL/SQL also lets you declare composite data types, such as nested tables, variable-size arrays, and records. For more informations, see Chapter 5, "Using PL/SQL Collections and Records."

Assigning Values to Variables

You can assign a value to a variable in the following ways:

  • With the assignment operator (:=), as in Example 1-3.

  • By selecting (or fetching) database values into it, as in Example 1-4.

  • By passing it as an OUT or IN OUT parameter to a subprogram, and then assigning the value inside the subprogram, as in Example 1-5

Example 1-3 Assigning Values to Variables with the Assignment Operator

SQL> DECLARE  -- You can assign values here
  2    wages          NUMBER;
  3    hours_worked   NUMBER := 40;
  4    hourly_salary  NUMBER := 22.50;
  5    bonus          NUMBER := 150;
  6    country        VARCHAR2(128);
  7    counter        NUMBER := 0;
  8    done           BOOLEAN;
  9    valid_id       BOOLEAN;
 10    emp_rec1       employees%ROWTYPE;
 11    emp_rec2       employees%ROWTYPE;
 12    TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
 13    comm_tab       commissions;
 14  
 15  BEGIN  -- You can assign values here too
 16     wages := (hours_worked * hourly_salary) + bonus;
 17     country := 'France';
 18     country := UPPER('Canada');
 19     done := (counter > 100);
 20     valid_id := TRUE;
 21     emp_rec1.first_name := 'Antonio';
 22     emp_rec1.last_name := 'Ortiz';
 23     emp_rec1 := emp_rec2;
 24     comm_tab(5) := 20000 * 0.15;
 25  END;
 26  /
 
PL/SQL procedure successfully completed.
 
SQL> 

In Example 1-4, 10% of an employee's salary is selected into the bonus variable. Now you can use the bonus variable in another computation or insert its value into a database table.

Example 1-4 Using SELECT INTO to Assign Values to Variables

SQL> DECLARE
  2    bonus   NUMBER(8,2);
  3    emp_id  NUMBER(6) := 100;
  4  BEGIN
  5    SELECT salary * 0.10 INTO bonus
  6      FROM employees
  7        WHERE employee_id = emp_id;
  8  END;
  9  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Example 1-5 passes the new_sal variable to a subprogram, and the subprogram updates the variable.

Example 1-5 Assigning Values to Variables as Parameters of a Subprogram

SQL> DECLARE
  2    new_sal  NUMBER(8,2);
  3    emp_id   NUMBER(6) := 126;
  4  
  5    PROCEDURE adjust_salary (
  6      emp_id      NUMBER,
  7      sal IN  OUT NUMBER
  8    ) IS
  9      emp_job  VARCHAR2(10);
 10      avg_sal  NUMBER(8,2);
 11    BEGIN
 12      SELECT job_id INTO emp_job
 13        FROM employees
 14          WHERE employee_id = emp_id;
 15  
 16      SELECT AVG(salary) INTO avg_sal
 17        FROM employees
 18          WHERE job_id = emp_job;
 19  
 20      DBMS_OUTPUT.PUT_LINE ('The average salary for '
 21                            || emp_job
 22                            || ' employees: '
 23                            || TO_CHAR(avg_sal)
 24                           );
 25  
 26      sal := (sal + avg_sal)/2;
 27    END;
 28  
 29  BEGIN
 30    SELECT AVG(salary) INTO new_sal
 31      FROM employees;
 32  
 33    DBMS_OUTPUT.PUT_LINE ('The average salary for all employees: '
 34                          || TO_CHAR(new_sal)
 35                         );
 36  
 37    adjust_salary(emp_id, new_sal);
 38  END;
 39  /
The average salary for all employees: 6461.68
The average salary for ST_CLERK employees: 2785
 
PL/SQL procedure successfully completed.
 
SQL> 

Declaring PL/SQL Constants

Declaring a PL/SQL constant is like declaring a PL/SQL variable except that you must add the keyword CONSTANT and immediately assign a value to the constant. For example:

credit_limit CONSTANT NUMBER := 5000.00;

No further assignments to the constant are allowed.

Bind Variables

Bind variables improve performance by allowing the database to reuse SQL statements.

When you embed a SQL INSERT, UPDATE, DELETE, or SELECT statement directly in your PL/SQL code, PL/SQL turns the variables in the WHERE and VALUES clauses into bind variables automatically. The database can reuse these SQL statements each time the same code is executed. To run similar statements with different variable values, you can save parsing overhead by invoking a stored subprogram that accepts parameters and then issues the statements with the parameters substituted in the appropriate places.

PL/SQL does not create bind variables automatically when you use dynamic SQL, but you can use them with dynamic SQL by specifying them explicitly.

PL/SQL Data Abstraction

Data abstraction lets you work with the essential properties of data without being too involved with details. After you design a data structure, you can focus on designing algorithms that manipulate the data structure.

Topics:

Cursors

A cursor is a name for a specific private SQL area in which information for processing the specific statement is kept. PL/SQL uses both implicit and explicit cursors. PL/SQL implicitly declares a cursor for all SQL data manipulation statements on a set of rows, 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. For example, Example 1-6 declares an explicit cursor.

For information about cursors, see Managing Cursors in PL/SQL.

%TYPE Attribute

The %TYPE attribute provides the data type 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 last_name in a table named employees. To declare a variable named v_last_name that has the same data type as column last_name, use dot notation and the %TYPE attribute, as follows:

v_last_name employees.last_name%TYPE;

Declaring v_last_name with %TYPE has two advantages. First, you need not know the exact data type of last_name. Second, if you change the database definition of last_name, perhaps to make it a longer character string, the data type of v_last_name changes accordingly at run time.

For more information about %TYPE, see Using the %TYPE Attribute and %TYPE Attribute.

%ROWTYPE Attribute

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. See Cursors.

Columns in a row and corresponding fields in a record have the same names and data types. In the following example, you declare a record named dept_rec, whose fields have the same names and data types as the columns in the departments table:

dept_rec departments%ROWTYPE; -- declare record variable

You use dot notation to reference fields, as follows:

v_deptid := dept_rec.department_id;

If you declare a cursor that retrieves the last name, salary, hire date, and job class of an employee, you can use %ROWTYPE to declare a record that stores the same information.

The FETCH statement in Example 1-6 assigns the value in the last_name column of the employees table to the last_name field of employee_rec, the value in the salary column is to the salary field, and so on.

Example 1-6 Using %ROWTYPE with an Explicit Cursor

SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT last_name, salary, hire_date, job_id
  4        FROM employees
  5          WHERE employee_id = 120;
  6  
  7     employee_rec c1%ROWTYPE;
  8  
  9  BEGIN
 10    OPEN c1;
 11    FETCH c1 INTO employee_rec;
 12    DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
 13  END;
 14  /
Employee name: Weiss
 
PL/SQL procedure successfully completed.
 
SQL> 

For more information about %ROWTYPE, see Using the %ROWTYPE Attribute and %ROWTYPE Attribute.

Collections

PL/SQL collection types let you declare high-level data types 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. When declaring collections, you use a TYPE definition. See Defining Collection Types.

To reference an element, use subscript notation with parentheses, as shown in Example 1-7.

Example 1-7 Using a PL/SQL Collection Type

SQL> DECLARE
  2    TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
  3    staff  staff_list;
  4    lname  employees.last_name%TYPE;
  5    fname  employees.first_name%TYPE;
  6  BEGIN
  7    staff := staff_list(100, 114, 115, 120, 122);
  8  
  9    FOR i IN staff.FIRST..staff.LAST LOOP
 10      SELECT last_name, first_name INTO lname, fname
 11        FROM employees
 12          WHERE employees.employee_id = staff(i);
 13  
 14       DBMS_OUTPUT.PUT_LINE (TO_CHAR(staff(i))
 15                             || ': '
 16                             || lname
 17                             || ', '
 18                             || fname
 19                            );
 20    END LOOP;
 21  END;
 22  /
100: King, Steven
114: Raphaely, Den
115: Khoo, Alexander
120: Weiss, Matthew
122: Kaufling, Payam
 
PL/SQL procedure successfully completed.
 
SQL> 

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.

For information about collections, see Chapter 5, "Using PL/SQL Collections and Records."

Records

Records are composite data structures whose fields can have different data types. You can use records to hold related items and pass them to subprograms with a single parameter. When declaring records, you use a TYPE definition, as in Example 1-8. See Defining and Declaring Records.

Example 1-8 Declaring a Record Type

SQL> DECLARE
  2    TYPE timerec IS RECORD (
  3      hours   SMALLINT,
  4      minutes SMALLINT
  5    );
  6  
  7    TYPE meeting_type IS RECORD (
  8      date_held  DATE,
  9      duration   timerec,  -- nested record
 10      location   VARCHAR2(20),
 11      purpose    VARCHAR2(50)
 12    );
 13  
 14  BEGIN
 15    NULL;
 16  END;
 17  /
 
PL/SQL procedure successfully completed.
 
SQL> 

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.

For information about records, see Chapter 5, "Using PL/SQL Collections and Records."

Object Types

PL/SQL supports object-oriented programming through object types. An object type encapsulates a data structure along with the subprograms needed to manipulate the data. The variables that form the data structure are known as attributes. The subprograms 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. Example 1-9 shows an object type definition for a bank account.

Example 1-9 Defining an Object Type

SQL> CREATE TYPE bank_account AS OBJECT (
  2    acct_number NUMBER(5),
  3    balance     NUMBER,
  4    status      VARCHAR2(10),
  5  
  6    MEMBER PROCEDURE open
  7      (SELF IN OUT NOCOPY bank_account,
  8       amount IN NUMBER),
  9  
 10    MEMBER PROCEDURE close
 11      (SELF IN OUT NOCOPY bank_account,
 12       num IN NUMBER,
 13       amount OUT NUMBER),
 14  
 15    MEMBER PROCEDURE deposit
 16      (SELF IN OUT NOCOPY bank_account,
 17       num IN NUMBER,
 18       amount IN NUMBER),
 19  
 20    MEMBER PROCEDURE withdraw
 21      (SELF IN OUT NOCOPY bank_account,
 22       num IN NUMBER,
 23       amount IN NUMBER),
 24  
 25    MEMBER FUNCTION curr_bal (num IN NUMBER) RETURN NUMBER
 26  );
 27  /
 
Type created.
 
SQL> 

For information about object types, see Oracle Database Object-Relational Developer's Guide.

PL/SQL Control Structures

Control structures are the most important PL/SQL extension to SQL. Not only does PL/SQL let you manipulate database data, it lets you process the data using flow-of-control statements.

Topics:

For more information, see Chapter 4, "Using PL/SQL Control Structures."

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 and the ELSE clause defines what to do if the condition is false or null. Example 1-10 shows the use of IF-THEN-ELSE to determine the salary raise an employee receives based on the current salary of the employee.

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 for each case, as in Example 1-10.

Example 1-10 Using the IF-THEN-ELSE and CASE Statement for Conditional Control

SQL> DECLARE
  2     jobid      employees.job_id%TYPE;
  3     empid      employees.employee_id%TYPE := 115;
  4     sal        employees.salary%TYPE;
  5     sal_raise  NUMBER(3,2);
  6  BEGIN
  7    SELECT job_id, salary INTO jobid, sal
  8      FROM employees
  9        WHERE employee_id = empid;
 10  
 11    CASE
 12      WHEN jobid = 'PU_CLERK' THEN
 13        IF sal < 3000 THEN
 14          sal_raise := .12;
 15        ELSE
 16          sal_raise := .09;
 17        END IF;
 18  
 19      WHEN jobid = 'SH_CLERK' THEN
 20        IF sal < 4000 THEN
 21          sal_raise := .11;
 22        ELSE
 23          sal_raise := .08;
 24        END IF;
 25  
 26      WHEN jobid = 'ST_CLERK' THEN
 27        IF sal < 3500 THEN
 28          sal_raise := .10;
 29        ELSE
 30          sal_raise := .07;
 31        END IF;
 32  
 33      ELSE
 34        BEGIN
 35          DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid);
 36        END;
 37     END CASE;
 38  
 39     UPDATE employees
 40       SET salary = salary + salary * sal_raise
 41         WHERE employee_id = empid;
 42  END;
 43  /
 
PL/SQL procedure successfully completed.
 
SQL>

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. In Example 1-11 the loop inserts 100 numbers, square roots, squares, and the sum of squares into a database table.

Example 1-11 Using the FOR-LOOP

SQL> CREATE TABLE sqr_root_sum (
  2    num NUMBER,
  3    sq_root NUMBER(6,2),
  4    sqr NUMBER,
  5    sum_sqrs NUMBER
  6  );
 
Table created.
 
SQL> 
SQL> DECLARE
  2     s  PLS_INTEGER;
  3  BEGIN
  4    FOR i in 1..100 LOOP
  5      s := (i * (i + 1) * (2*i +1)) / 6;  -- sum of squares
  6  
  7      INSERT INTO sqr_root_sum
  8        VALUES (i, SQRT(i), i*i, s );
  9    END LOOP;
 10  END;
 11  /
 
PL/SQL procedure successfully completed.
 
SQL> 

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 Example 1-12, you find the first employee who has a salary over $15000 and is higher in the chain of command than employee 120.

Example 1-12 Using WHILE-LOOP for Control

SQL> CREATE TABLE temp (
  2    tempid   NUMBER(6),
  3    tempsal  NUMBER(8,2),
  4    tempname VARCHAR2(25)
  5  );
 
Table created.
 
SQL> 
SQL> DECLARE
  2    sal             employees.salary%TYPE := 0;
  3    mgr_id          employees.manager_id%TYPE;
  4    lname           employees.last_name%TYPE;
  5    starting_empid  employees.employee_id%TYPE := 120;
  6  
  7  BEGIN
  8     SELECT manager_id INTO mgr_id
  9       FROM employees
 10         WHERE employee_id = starting_empid;
 11  
 12     WHILE sal <= 15000 LOOP
 13       SELECT salary, manager_id, last_name INTO sal, mgr_id, lname
 14         FROM employees
 15           WHERE employee_id = mgr_id;
 16     END LOOP;
 17  
 18     INSERT INTO temp
 19        VALUES (NULL, sal, lname);
 20  
 21  EXCEPTION
 22    WHEN NO_DATA_FOUND THEN
 23      INSERT INTO temp VALUES (NULL, NULL, 'Not found');
 24  END;
 25  /
 
PL/SQL procedure successfully completed.
 
SQL>

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 Example 1-13, the loop completes when the value of total exceeds 25,000:

Similarly, the CONTINUE-WHEN statement immediately transfers control to the next iteration of the loop when there is no need to continue working on this iteration.

Example 1-13 Using the EXIT-WHEN Statement

SQL> CREATE TABLE temp (
  2    tempid   NUMBER(6),
  3    tempsal  NUMBER(8,2),
  4    tempname VARCHAR2(25)
  5  );
 
Table created.
 
SQL> 
SQL> DECLARE
  2    total    NUMBER(9) := 0;
  3    counter  NUMBER(6) := 0;
  4  BEGIN
  5    LOOP
  6      counter := counter + 1;
  7      total   := total + counter * counter;
  8      EXIT WHEN total > 25000;
  9    END LOOP;
 10  
 11    DBMS_OUTPUT.PUT_LINE ('Counter: '
 12                          || TO_CHAR(counter)
 13                          || ' Total: '
 14                          || TO_CHAR(total)
 15                         );
 16  END;
 17  /
Counter: 42 Total: 25585
 
PL/SQL procedure successfully completed.
 
SQL>

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 in Example 1-14.

Example 1-14 Using the GOTO Statement

SQL> DECLARE
  2    total    NUMBER(9) := 0;
  3    counter  NUMBER(6) := 0;
  4  BEGIN
  5    <<calc_total>>
  6    counter := counter + 1;
  7    total := total + counter * counter;
  8  
  9    IF total > 25000 THEN
 10      GOTO print_total;
 11    ELSE
 12      GOTO calc_total;
 13    END IF;
 14  
 15    <<print_total>>
 16    DBMS_OUTPUT.PUT_LINE
 17      ('Counter: ' || TO_CHAR(counter) || ' Total: ' || TO_CHAR(total));
 18  END;
 19  /
Counter: 42 Total: 25585
 
PL/SQL procedure successfully completed.
 
SQL> 

PL/SQL Subprograms

A PL/SQL subprogram is a named PL/SQL block that can be invoked with a set of parameters, like double in Example 1-15. PL/SQL has two types of subprograms, procedures and functions. A function returns a result.

Example 1-15 PL/SQL Procedure

SQL> DECLARE
  2    in_string   VARCHAR2(100) := 'Test string';
  3    out_string  VARCHAR2(200);
  4  
  5    PROCEDURE double (
  6      original    IN  VARCHAR2,
  7      new_string  OUT VARCHAR2
  8    ) AS
  9    BEGIN
 10      new_string := original || original;
 11    END;
 12  
 13  BEGIN
 14    DBMS_OUTPUT.PUT_LINE ('in_string: ' || in_string);
 15    double (in_string, out_string);
 16    DBMS_OUTPUT.PUT_LINE ('out_string: ' || out_string);
 17  END;
 18  /
in_string: Test string
out_string: Test stringTest string
 
PL/SQL procedure successfully completed.
 
SQL> 

Topics:

For more information about PL/SQL subprograms, see Chapter 8, "Using PL/SQL Subprograms."

Standalone PL/SQL Subprograms

You create standalone subprograms at schema level with the SQL statements CREATE PROCEDURE and CREATE FUNCTION. They are compiled and stored in the database, where they can be used by any number of applications connected to the database. When invoked, they are loaded and processed immediately. Subprograms use shared memory, so that only one copy of a subprogram is loaded into memory for execution by multiple users.

Example 1-16 creates a standalone procedure that accepts an employee ID and a bonus amount, uses the ID to select the employee's commission percentage from a database table and to convert the commission percentage to a decimal amount, and then checks the commission amount. If the commission is null, the procedure raises an exception; otherwise, it updates the employee's salary.

Example 1-16 Creating a Standalone PL/SQL Procedure

SQL> CREATE OR REPLACE PROCEDURE award_bonus (
  2    emp_id NUMBER, bonus NUMBER) AS
  3    commission    REAL;
  4    comm_missing  EXCEPTION;
  5  BEGIN
  6    SELECT commission_pct / 100 INTO commission
  7      FROM employees
  8        WHERE employee_id = emp_id;
  9  
 10    IF commission IS NULL THEN
 11      RAISE comm_missing;
 12    ELSE
 13      UPDATE employees
 14        SET salary = salary + bonus*commission
 15          WHERE employee_id = emp_id;
 16    END IF;
 17  EXCEPTION
 18    WHEN comm_missing THEN
 19      DBMS_OUTPUT.PUT_LINE
 20        ('This employee does not receive a commission.');
 21      commission := 0;
 22    WHEN OTHERS THEN
 23      NULL;
 24  END award_bonus;
 25  /
 
Procedure created.
 
SQL> 

A PL/SQL subprogram can be invoked from an interactive tool (such as SQL*Plus or Enterprise Manager), from an Oracle Precompiler or OCI program, from another PL/SQL subprogram, or from a trigger.

For information, about the CREATE PROCEDURE statment, see CREATE PROCEDURE Statement.

For more information about the SQL CREATE FUNCTION, see CREATE FUNCTION Statement.

Example 1-17 invokes the stored subprogram in Example 1-16 with the CALL statement and then from inside a block.

Example 1-17 Invoking a Standalone Procedure from SQL*Plus

SQL> -- Invoke standalone procedure with CALL statement
SQL> 
SQL> CALL award_bonus(179, 1000);
Call completed.
 
SQL> 
SQL> -- Invoke standalone procedure from within block
SQL> 
SQL> BEGIN
  2    award_bonus(179, 10000);
  3  END;
  4  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Using the BEGIN-END block is recommended in several situations. For example, using the CALL statement can suppress an ORA-n error that was not handled in the PL/SQL subprogram.

For additional examples of invoking PL/SQL subprograms, see Example 8-8. For information about the CALL statement, see Oracle Database SQL Language Reference

Triggers

A trigger is a stored subprogram associated with a table, view, or event. The trigger can be invoked once, when some event occurs, or many times, once for each row affected by an INSERT, UPDATE, or DELETE statement. The trigger can be invoked before or after the event.

The trigger in Example 1-18 is invoked whenever salaries in the employees table are updated. For each update, the trigger writes a record to the emp_audit table. (Example 1-10 would invoke this trigger.)

Example 1-18 Creating a Trigger

SQL> CREATE TABLE emp_audit (
  2    emp_audit_id  NUMBER(6),
  3    up_date       DATE,
  4    new_sal       NUMBER(8,2),
  5    old_sal       NUMBER(8,2)
  6  );
 
Table created.
 
SQL> 
SQL> CREATE OR REPLACE TRIGGER audit_sal
  2    AFTER UPDATE OF salary
  3      ON employees
  4        FOR EACH ROW
  5  BEGIN
  6    INSERT INTO emp_audit
  7      VALUES(:old.employee_id, SYSDATE, :new.salary, :old.salary);
  8  END;
  9  /
 
Trigger created.
 
SQL> 

For more information about triggers, see Chapter 9, "Using Triggers."

PL/SQL Packages (APIs Written in PL/SQL)

A PL/SQL package bundles logically related types, variables, cursors, and subprograms into a database object called a package. The package defines a simple, clear, interface to a set of related subprograms and types that can be accessed by SQL statements.

PL/SQL lets you access many predefined packages (see Access to Predefined Packages) and to create your own packages.

A package usually has two parts: a specification and a body.

The specification defines the application programming interface (API); it declares the types, constants, variables, exceptions, cursors, and subprograms. To create a package specification, use the CREATE PACKAGE Statement.

The body contains the SQL queries for cursors and the code for subprograms.To create a package body, use the CREATE PACKAGE BODY Statement.

In Example 1-19, the emp_actions package contains two procedures that update the employees table and one function that provides information.

Example 1-19 Creating a Package and Package Body

SQL> -- Package specification:
SQL> 
SQL> CREATE OR REPLACE PACKAGE emp_actions AS
  2  
  3    PROCEDURE hire_employee (
  4      employee_id     NUMBER,
  5      last_name       VARCHAR2,
  6      first_name      VARCHAR2,
  7      email           VARCHAR2,
  8      phone_number    VARCHAR2,
  9      hire_date       DATE,
 10      job_id          VARCHAR2,
 11      salary          NUMBER,
 12      commission_pct  NUMBER,
 13      manager_id      NUMBER,
 14      department_id   NUMBER
 15    );
 16  
 17    PROCEDURE fire_employee (emp_id NUMBER);
 18  
 19    FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER;
 20  END emp_actions;
 21  /
 
Package created.
 
SQL> -- Package body:
SQL> 
SQL> CREATE OR REPLACE PACKAGE BODY emp_actions AS
  2  
  3    -- Code for procedure hire_employee:
  4  
  5    PROCEDURE hire_employee (
  6      employee_id     NUMBER,
  7      last_name       VARCHAR2,
  8      first_name      VARCHAR2,
  9      email           VARCHAR2,
 10      phone_number    VARCHAR2,
 11      hire_date       DATE,
 12      job_id          VARCHAR2,
 13      salary          NUMBER,
 14      commission_pct  NUMBER,
 15      manager_id      NUMBER,
 16      department_id   NUMBER
 17    ) IS
 18    BEGIN
 19      INSERT INTO employees
 20        VALUES (employee_id,
 21                last_name,
 22                first_name,
 23                email,
 24                phone_number,
 25                hire_date,
 26                job_id,
 27                salary,
 28                commission_pct,
 29                manager_id,
 30                department_id);
 31    END hire_employee;
 32  
 33    -- Code for procedure fire_employee:
 34  
 35    PROCEDURE fire_employee (emp_id NUMBER) IS
 36    BEGIN
 37      DELETE FROM employees
 38        WHERE employee_id = emp_id;
 39    END fire_employee;
 40  
 41    -- Code for function num_above_salary:
 42  
 43    FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS
 44      emp_sal NUMBER(8,2);
 45      num_count NUMBER;
 46    BEGIN
 47      SELECT salary INTO emp_sal
 48        FROM employees
 49          WHERE employee_id = emp_id;
 50  
 51      SELECT COUNT(*) INTO num_count
 52        FROM employees
 53          WHERE salary > emp_sal;
 54  
 55      RETURN num_count;
 56    END num_above_salary;
 57  END emp_actions;
 58  /
 
Package body created.
 
SQL>

To invoke a packaged subprogram, you must know only name of the package and the name and parameters of the subprogram (therefore, you can change the implementation details inside the package body without affecting the invoking applications).

Example 1-20 invokes the emp_actions package procedures hire_employee and fire_employee.

Example 1-20 Invoking a Procedure in a Package

SQL> CALL emp_actions.hire_employee (300, 'Belden', 'Enrique',
  2    'EBELDEN', '555.111.2222',
  3    '31-AUG-04', 'AC_MGR', 9000,
  4    .1, 101, 110);
 
Call completed.
 
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE
  3      ('Number of employees with higher salary: ' ||
  4        TO_CHAR(emp_actions.num_above_salary(120)));
  5  
  6    emp_actions.fire_employee(300);
  7  END;
  8  /
Number of employees with higher salary: 34
 
PL/SQL procedure successfully completed.
 
SQL> 

Packages are stored in the database, where they can be shared by many applications. Invoking a packaged subprogram for the first time loads the whole package and caches it in memory, saving on disk I/O for subsequent invocations. Thus, packages enhance reuse and improve performance in a multiuser, multi-application environment.

For more information about packages, see Chapter 10, "Using PL/SQL Packages."

Conditional Compilation

PL/SQL provides conditional compilation, which lets you customize the functionality in a PL/SQL application without having to remove any source code. For example, you can:

  • Use the latest functionality with the latest database release and disable the new features to run the application against an older release of the database.

  • Activate debugging or tracing functionality in the development environment and hide that functionality in the application while it runs at a production site.

For more information, see Conditional Compilation.

Embedded SQL Statements

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 as shown in Example 1-21.

Example 1-21 Processing Query Results in a LOOP

SQL> BEGIN
  2    FOR someone IN (SELECT * FROM employees WHERE employee_id < 120)
  3    LOOP
  4      DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name ||
  5                           ', Last name = ' || someone.last_name);
  6    END LOOP;
  7  END;
  8  /
First name = Steven, Last name = King
First name = Neena, Last name = Kochhar
First name = Lex, Last name = De Haan
First name = Alexander, Last name = Hunold
First name = Bruce, Last name = Ernst
First name = David, Last name = Austin
First name = Valli, Last name = Pataballa
First name = Diana, Last name = Lorentz
First name = Nancy, Last name = Greenberg
First name = Daniel, Last name = Faviet
First name = John, Last name = Chen
First name = Ismael, Last name = Sciarra
First name = Jose Manuel, Last name = Urman
First name = Luis, Last name = Popp
First name = Den, Last name = Raphaely
First name = Alexander, Last name = Khoo
First name = Shelli, Last name = Baida
First name = Sigal, Last name = Tobias
First name = Guy, Last name = Himuro
First name = Karen, Last name = Colmenares
 
PL/SQL procedure successfully completed.
 
SQL> 

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.

Architecture of PL/SQL

Topics:

PL/SQL Engine

The PL/SQL compilation and run-time system is an engine that compiles and executes PL/SQL units. The engine can be installed in the database or in an application development tool, such as Oracle Forms.

In either environment, the PL/SQL engine accepts as input any valid PL/SQL unit. The engine executes procedural statements, but sends SQL statements to the SQL engine in the database, as shown in Figure 1-2.

Typically, the database processes PL/SQL units.

When an application development tool processes PL/SQL units, it passes them to its local PL/SQL engine. If a PL/SQL unit contains no SQL statements, the local engine processes the entire PL/SQL unit. This is useful if the application development tool can benefit from conditional and iterative control.

For example, Oracle Forms applications frequently use SQL statements to test the values of field entries and do simple computations. By using PL/SQL instead of SQL, these applications can avoid calls to the database.

PL/SQL Units and Compilation Parameters

A PL/SQL unit is any one of the following:

  • PL/SQL block

  • FUNCTION

  • PACKAGE

  • PACKAGE BODY

  • PROCEDURE

  • TRIGGER

  • TYPE

  • TYPE BODY

PL/SQL units are affected by PL/SQL compilation parameters (a category of database initialization parameters). Different PL/SQL units—for example, a package specification and its body—can have different compilation parameter settings.

Table 1-1 lists and briefly describes the PL/SQL compilation parameters. For more information about these parameters, see Oracle Database Reference.

To display the values of these parameters, use the static data dictionary view ALL_PLSQL_OBJECT_SETTINGS. For more information about this view, see Oracle Database Reference.

Table 1-1 PL/SQL Compilation Parameters

Parameter Description

PLSCOPE_SETTINGSFoot 1 

Controls the compile-time collection, cross reference, and storage of PL/SQL source code identifier data. Used by the PL/Scope tool, which is described in Oracle Database Advanced Application Developer's Guide.

PLSQL_CCFLAGS 1

Enables you to control conditional compilation of each PL/SQL unit independently.

PLSQL_CODE_TYPE 1

Specifies the compilation mode for PL/SQL units—INTERPRETED (the default) or NATIVE.

If the optimization level (set by PLSQL_OPTIMIZE_LEVEL) is less than 2:

  • The compiler generates interpreted code, regardless of PLSQL_CODE_TYPE.

  • If you specify NATIVE, the compiler warns you that NATIVE was ignored.

PLSQL_DEBUG 1

Specifies whether or not PL/SQL units will be compiled for debugging. See note following table.

PLSQL_NATIVE_LIBRARY_DIR

Has no effect. See note following table.

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT

Has no effect. See note following table.

PLSQL_OPTIMIZE_LEVEL 1

Specifies the optimization level at which to compile PL/SQL units (the higher the level, the more optimizations the compiler tries to make).

If PLSQL_OPTIMIZE_LEVEL=1, PL/SQL units will be compiled for debugging.

PLSQL_WARNINGS 1

Enables or disables the reporting of warning messages by the PL/SQL compiler, and specifies which warning messages to show as errors.

NLS_LENGTH_SEMANTICS 1

Enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics.


Footnote 1 The compile-time value of this parameter is stored with the metadata of the PL/SQL unit.

Note:

The following compilation parameters are deprecated and might be unavailable in future Oracle Database releases:
  • PLSQL_DEBUG

    For Release 11.1, it has the same effect as it had for Release 10.2—described in Table 1-1—but the compiler warns you that it is deprecated.

    Instead of PLSQL_DEBUG, Oracle recommends using PLSQL_OPTIMIZE_LEVEL=1.

  • PLSQL_NATIVE_LIBRARY_DIR

    For Release 11.1, it has no effect. The compiler does not warn you that it is deprecated.

  • PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT

    For Release 11.1, it has no effect. The compiler does not warn you that it is deprecated.

The compile-time values of most of the parameters in Table 1-1 are stored with the metadata of the PL/SQL unit, which means you can reuse those values when you explicitly recompile the program unit by doing the following:

  1. Use one of the following statements to recompile the program unit:

    • ALTER FUNCTION COMPILE

    • ALTER PACKAGE COMPILE

    • ALTER PROCEDURE COMPILE

  2. Include the REUSE SETTINGS clause in the statement.

    This clause preserves the existing settings and uses them for the recompilation of any parameters for which values are not specified elsewhere in the statement.

If you use the SQL statement CREATE OR REPLACE to explicitly compile a PL/SQL subprogram, or if you do not include the REUSE SETTINGS clause in the ALTER COMPILE statement, then the value of the compilation parameter is its value for the session.