PL/SQL: Usage Information

The topic discusses the development with PL/SQL. It includes the following topics:


See Also:

  • Oracle Database PL/SQL Language Reference for detailed information about PL/SQL

  • Oracle Database PL/SQL Packages and Types Reference for information on packages supplied by Oracle

  • Oracle Database Development Guide for information on using PL/SQL to develop Web applications


Overview of PL/SQL

PL/SQL is Oracle's procedural language extension to SQL. It provides a server-side, stored procedural language that is easy-to-use, seamless with SQL, robust, portable, and secure.

The PL/SQL compiler and interpreter are embedded in Oracle Database, providing developers with a consistent and leveraged development model on both the client and the server side. In addition, PL/SQL stored subprograms can be called from Oracle clients.

PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can create and run PL/SQL program units such as procedures, functions, and packages. PL/SQL program units generally are categorized as anonymous blocks, stored subprograms, and packages.

The basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program can be nested inside one another.

You can place declarations close to where they are used, such as 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.

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.

Entering and Executing PL/SQL Code

PL/SQL code can be entered and executed from the SQL Workshop or the SQL*Plus command line.

In the SQL Workshop, you can save your SQL statements as a script file that can be run as a SQL script with SQL*Plus.

If you use SQL*Plus, simply type in each line of code at the SQL prompt. For information on using SQL*Plus, see SQL*Plus: Usage Information.

You can create a text file of the PL/SQL code and run that as a SQL script. Using a script makes correcting mistakes much easier because you only need to make the necessary updates to correct the problem rather than retyping all the PL/SQL code. For information on running SQL scripts from SQL*Plus, see Running Scripts From SQL*Plus.

Utilizing the Main Features of PL/SQL

PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages. You can control program flow with statements like IF and LOOP. As with other procedural programming languages, you can declare variables, define procedures and functions, and trap runtime errors.

PL/SQL enables you to 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 data types correspond with SQL column types, making it easy to interchange PL/SQL variables with data inside a table.

Using PL/SQL Block Structure

As Example: Simple PL/SQL Block shows, a PL/SQL block has three basic parts: a declarative part (DECLARE), an executable part (BEGIN .. END), and an exception-handling (EXCEPTION) part that handles error conditions. For a discussion of exception handling, see Handling PL/SQL Errors.

Only the executable part is required. The optional declarative part is written first, 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.

Note the comments that are added to the PL/SQL code. See Using Comments. Also, note the use of DBMS_OUTPUT.PUT_LINE to display output. See Inputting and Outputting Data with PL/SQL.

Simple PL/SQL Block

-- the following is an optional declarative part
DECLARE
  monthly_salary         NUMBER(6);
  number_of_days_worked  NUMBER(2);
  pay_per_day            NUMBER(6,2);

-- the following is the executable part, from BEGIN to END
BEGIN
  monthly_salary := 2290;
  number_of_days_worked := 21;
  pay_per_day := monthly_salary/number_of_days_worked;

-- the following displays output from the PL/SQL block
  DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day));

-- the following is an optional exception part that handles errors
EXCEPTION
  WHEN ZERO_DIVIDE THEN
      pay_per_day := 0;

END;
/

For another example of PL/SQL block structure, see Example: Assigning Values to Variables by SELECTing INTO.

Using Comments

The PL/SQL compiler ignores comments, but you should not. Adding comments to your program promotes readability and help others understand your code. Generally, you use comments to describe the purpose and use of each code segment. PL/SQL supports single-line and multiline comment styles.

Single-line comments begin with a double hyphen (--) anywhere on a line and extend to the end of the line. Multiline comments begin with a slash-asterisk (/*), end with an asterisk-slash (*/), and can span multiple lines. See Example: Using Comments.

Using Comments

DECLARE  -- Declare variables here.
  monthly_salary         NUMBER(6);  -- This is the monthly salary.
  number_of_days_worked  NUMBER(2);  -- This is the days in one month.
  pay_per_day            NUMBER(6,2); -- Calculate this value.
BEGIN
-- First assign values to the variables.
  monthly_salary := 2290;
  number_of_days_worked := 21;

-- Now calculate the value on the following line.
  pay_per_day := monthly_salary/number_of_days_worked;

-- the following displays output from the PL/SQL block
  DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day));

EXCEPTION
/* This is a simple example of an exeception handler to trap division by zero. 
   In actual practice, it would be best to check whether a variable is
   zero before using it as a divisor. */
  WHEN ZERO_DIVIDE THEN
      pay_per_day := 0; -- set to 0 if divisor equals 0
END;
/

While testing or debugging a program, you might want to disable a line of code. The following example shows how you can disable a single line by making it a comment:

-- pay_per_day := monthly_salary/number_of_days_worked;

You can use multiline comment delimiters to comment-out large sections of code.

Declaring Variables and Constants

Variables can have any SQL data type, such as VARCHAR2, DATE, or NUMBER, or a PL/SQL-only data type, such as BOOLEAN or PLS_INTEGER. You can also declare nested tables, variable-size arrays (varrays for short), and records using the TABLE, VARRAY, and RECORD composite data types. See Working With PL/SQL Data Structures.

Declaring a constant is like declaring a variable except that you must add the keyword CONSTANT and immediately assign a value to the constant. No further assignments to the constant are allowed. For an example, see avg_days_worked_month in Example: Declaring Variables in PL/SQL.

For example, assume that you want to declare variables for employee data, such as employee_id to hold 6-digit numbers and active_employee to hold the Boolean value TRUE or FALSE. You declare these and related employee variables and constants, as shown in Example: Declaring Variables in PL/SQL.

Note that there is a semi-colon (;) at the end of each line in the declaration section. Also, note the use of the NULL statement which enables you to execute and test the PL/SQL block.

You can choose any naming convention for variables that is appropriate for your application. For example, you could begin each variable name with the v_ prefix to emphasize that these are variable names.

Declaring Variables in PL/SQL

DECLARE -- declare the variables in this section
  last_name              VARCHAR2(30);
  first_name             VARCHAR2(25);
  employee_id            NUMBER(6);
  active_employee        BOOLEAN;
  monthly_salary         NUMBER(6);
  number_of_days_worked  NUMBER(2);
  pay_per_day            NUMBER(6,2);
  avg_days_worked_month  CONSTANT NUMBER(2) := 21; -- a constant variable
BEGIN
  NULL; -- NULL statement does nothing, allows this block to executed and tested
END;
/

See Also:

Oracle Database PL/SQL Language Reference for information on data types used with PL/SQL, including the PL/SQL BOOLEAN and PLS_INTEGER data types

Using Identifiers in PL/SQL

You use identifiers to name PL/SQL program items and units, such as constants, variables, exceptions, and subprograms. An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs.

The declaration section in Example: Valid Identifiers for Variables illustrates some valid identifiers. You can see additional examples of valid identifiers for variable names in Example: Using Comments and Example: Declaring Variables in PL/SQL.

Valid Identifiers for Variables

DECLARE  -- all declarations use valid identifiers
  x                  NUMBER;
  t2                 NUMBER;
  phone#             VARCHAR2(12);
  credit_limit       NUMBER;
  oracle$number      NUMBER;
  money$$$tree       NUMBER;
  SN##               VARCHAR2(9);
  try_again          BOOLEAN;
BEGIN
  NULL;
END;
/

Characters such as hyphens, slashes, and spaces are not allowed. For example the following identifiers are not allowed:


mine&yours is not allowed because of the ampersand
debit-amount is not allowed because of the hyphen
on/off is not allowed because of the slash
user id is not allowed because of the space

You can use upper, lower, or mixed case to write identifiers. PL/SQL is not case-sensitive except within string and character literals. Every character, including dollar signs, underscores, and number signs, is significant. If the only difference between identifiers is the case of corresponding letters, PL/SQL considers them the same, as in the following:


lastname is same as LASTNAME and LastName
LastName is the same as lastname and LASTNAME
LASTNAME is same as lastname and LastName

The size of an identifier cannot exceed 30 characters. Identifiers should be descriptive. When possible, avoid obscure names such as cpm. Instead, use meaningful names such as cost_per_thousand.

Some identifiers, called reserved words or keywords, have a special syntactic meaning to PL/SQL. For example, the words BEGIN and END are reserved. Often, reserved words and keywords are written in upper case for readability. Neither reserved words or keywords should be used as identifiers and the use can cause compilation errors.


See Also:

Oracle Database PL/SQL Language Reference for information on PL/SQL reserved words and keywords

Assigning Values to a Variable With the Assignment Operator

You can assign values to a variable in several ways. One way uses the assignment operator (:=), a colon followed by an equal sign, as shown in Example: Assigning Values to Variables With the Assignment Operator. You place the variable to the left of the operator and an expression, including function calls, to the right. Note that you can assign a value to a variable when it is declared.

Assigning Values to Variables With the Assignment Operator

DECLARE  -- declare and assiging variables
   wages          NUMBER(6,2);
   hours_worked   NUMBER := 40;
   hourly_salary  NUMBER := 22.50;
   bonus          NUMBER := 150;
   country        VARCHAR2(128);
   counter        NUMBER := 0;
   done           BOOLEAN := FALSE;
   valid_id       BOOLEAN;
BEGIN
   wages := (hours_worked * hourly_salary) + bonus;  -- compute wages
   country := 'France'; -- assign a string literal
   country := UPPER('Canada'); -- assign an uppercase string literal
   done := (counter > 100); -- assign a BOOLEAN, in this case FALSE
   valid_id := TRUE; -- assign a BOOLEAN
END;
/

Using Literals

A literal is an explicit numeric, character, string, or BOOLEAN value not represented by an identifier. For example, 147 is a numeric literal and FALSE is a BOOLEAN literal.

Numeric Literals

Two kinds of numeric literals can be used in arithmetic expressions: integers and reals. An integer literal is an optionally signed whole number without a decimal point, such as +6. A real literal is an optionally signed whole or fractional number with a decimal point, such as -3.14159. PL/SQL considers a number such as 25. to be real even though it has an integral value.

Numeric literals cannot contain dollar signs or commas, but can be written using scientific notation. Simply suffix the number with an E (or e) followed by an optionally signed integer, such as -9.5e-3. E (or e) stands for times ten to the power of.

Character Literals

A character literal is an individual character enclosed by single quotes (apostrophes), such as '(' or '7'. Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols.

PL/SQL is case-sensitive within character literals. For example, PL/SQL considers the character literals 'Z' and 'z' to be different. Also, the character literals '0'..'9' are not equivalent to integer literals but can be used in arithmetic expressions because they are implicitly convertible to integers.

String Literals

A character value can be represented by an identifier or explicitly written as a string literal, which is a sequence of zero or more characters enclosed by single quotes, such as 'Hello, world!' and '$1,000,000'. All string literals except the null string ('') have data type CHAR.

PL/SQL is case-sensitive within string literals. For example, PL/SQL considers the following string literals 'baker' and 'Baker' to be different:

To represent an apostrophe within a string, you can write two single quotes (''), which is not the same as writing a double quote ("). Doubling the quotation marks within a complicated literal, particularly one that represents a SQL statement, can be tricky. You can also define your own delimiter characters for the literal. You choose a character that is not present in the string, and then do not need to escape other single quotation marks inside the literal, such as the following string.

q'!I'm using the exclamation point for a delimiter here.!'

BOOLEAN Literals

BOOLEAN literals are the predefined values TRUE, FALSE, and NULL. NULL stands for a missing, unknown, or inapplicable value. Remember, BOOLEAN literals are values, not strings. For example, TRUE is no less a value than the number 25.

Datetime Literals

Datetime literals have various formats depending on the datetime data type, such as '14-SEP-05' or '14-SEP-05 09:24:04 AM'.

Example: Using Literals shows some examples of the use of literals.

Using Literals

DECLARE  -- declare and assign variables
  number1 PLS_INTEGER := 32000;  -- numeric literal
  number2 NUMBER(8,3);
  char1   VARCHAR2(1) := 'x'; -- character literal
  char2   VARCHAR2(1000);
  boolean BOOLEAN := TRUE; -- BOOLEAN literal
  date1   DATE := '11-AUG-2005'; -- DATE literal
  time1     TIMESTAMP;
  time2     TIMESTAMP WITH TIME ZONE;
BEGIN
  number2 := 3.125346e3;  -- numeric literal
  number2 := -8300.00;  -- numeric literal
  number2 := -14;  -- numeric literal
  char2 := q'!I'm writing an example string.!';  -- string literal
  char2 := 'I''m writing an example string.'; -- need two single quotes here
  time1 := '11-AUG-2005 11:01:01 PM'; -- TIMESTAMP literal
  time2 := '11-AUG-2005 09:26:56.66 PM +02:00';
END;
/

See Also:

  • Oracle Database SQL Language Reference for information on the syntax for literals and the date and time types

  • Oracle Database Development Guide for examples of performing date and time arithmetic

  • Oracle Database PL/SQL Language Reference for information on using literals with PL/SQL.


Declaring and Assigning Variables With DEFAULT or NOT NULL

You can use the keyword DEFAULT instead of the assignment operator to initialize variables. Use DEFAULT for variables that have a typical value. Use the assignment operator for variables (such as counters and accumulators) that have no typical value. You can also use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.

Besides assigning an initial value, declarations can impose the NOT NULL constraint so that assigning a NULL raises an error. The NOT NULL constraint must be followed by an initialization clause.

In Example: Using DEFAULT and NOT NULL the declaration for avg_days_worked_month uses the DEFAULT to assign a value of 21 and the declarations for active_employee and monthly_salary use the NOT NULL constraint.

Using DEFAULT and NOT NULL

DECLARE  -- declare and assign variables
  last_name              VARCHAR2(30);
  first_name             VARCHAR2(25);
  employee_id            NUMBER(6);
  active_employee        BOOLEAN NOT NULL := TRUE;  -- value cannot be NULL
  monthly_salary         NUMBER(6) NOT NULL := 2000; -- value cannot be NULL
  number_of_days_worked  NUMBER(2);
  pay_per_day            NUMBER(6,2);
  employee_count         NUMBER(6) := 0;
  avg_days_worked_month  NUMBER(2) DEFAULT 21;  -- assign a default value
BEGIN
  NULL; -- NULL statement does nothing, allows this block to executed and tested
END;
/

Assigning Values to a Variable With SELECT INTO

Another way to assign values to a variable is by selecting (or fetching) database values into it. In Example: Assigning Values to Variables by SELECTing INTO, 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.

In the example, DBMS_OUTPUT.PUT_LINE is used to display output from the PL/SQL program. For more information, see Inputting and Outputting Data with PL/SQL.

Assigning Values to Variables by SELECTing INTO

DECLARE -- declare and assign values
  bonus  NUMBER(8,2);
  emp_id NUMBER(6) := 100;  -- declare variable and assign a test value
BEGIN
-- retreive a value from the employees table and assign to the bonus variable
  SELECT salary * 0.10 INTO bonus FROM employees
    WHERE employee_id = emp_id;
    DBMS_OUTPUT.PUT_LINE ( 'Employee: ' || TO_CHAR(emp_id) 
      || ' Bonus: ' || TO_CHAR(bonus) ); -- display data
END;
/

Inputting and Outputting Data with PL/SQL

Most PL/SQL input and output is through SQL statements, to store data in database tables or query those tables. All other PL/SQL I/O is done through APIs that interact with other programs. For example, the DBMS_OUTPUT package has procedures such as PUT_LINE. To see the result outside of PL/SQL requires another program, such as SQL*Plus, to read and display the data passed to DBMS_OUTPUT. SQL*Plus does not display DBMS_OUTPUT data unless you first issue the SQL*Plus command SET SERVEROUTPUT ON. For information on SQL*Plus SET command, see SQL*Plus SET Commands.

Example: Using DBMS_OUTPUT to Display Output show the use of DBMS_OUTPUT.PUTLINE. Note the use of SET SERVEROUTPUT ON to enable output.

Using DBMS_OUTPUT to Display Output

-- enable SERVEROUTPUT in SQL*Plus to display with DBMS_OUTPUT.PUT_LINE
-- this enables SERVEROUTPUT for this SQL*Plus session only
SET SERVEROUTPUT ON

DECLARE
  answer  VARCHAR2(20); -- declare a variable
BEGIN
-- assign a value to a variable
  answer := 'Maybe';
-- use PUT_LINE to display data from the PL/SQL block
  DBMS_OUTPUT.PUT_LINE( 'The answer is: ' || answer );
END;
/

The DBMS_OUTPUT package is a predefined Oracle package. For information about Oracle supplied packages, see Oracle Product-Specific Packages.


See Also:

  • SQL*Plus User's Guide and Reference for information SQL*Plus commands

  • Oracle Database PL/SQL Packages and Types Reference.for information about Oracle-supplied packages


Using %ROWTYPE and %TYPE Attributes to Declare Data Types

As part of the declaration for each PL/SQL variable, you declare its data type. Usually, this data type is one of the types shared between PL/SQL and SQL, such as NUMBER or VARCHAR2. For easier maintenance of code that interacts with the database, you can also use the special qualifiers %ROWTYPE and %TYPE to declare variables that hold table columns or table rows.

Using the %ROWTYPE Attribute to Declare Variables

For easier maintenance of code that interacts with the database, you can use the %ROWTYPE attribute to declare a variable that represents a row in a table. A PL/SQL record is the data type that stores the same information as a row in a table.

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 record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. For information on records, see Using Records.

Columns in a row and corresponding fields in a record have the same names and data types. In Example: Using %ROWTYPE with a Record, you declare a record named emp_rec. Its fields have the same names and data types as the columns in the employees table. You use dot notation to reference fields, such as emp_rec.last_name.

In Example: Using %ROWTYPE with a Record, SELECT is used to store row information from the employees table into the emp_rec record. When you execute the SELECT INTO statement, the value in the first_name column of the employees table is assigned to the first_name field of emp_rec, the value in the last_name column is assigned to the last_name field of emp_rec, and so on.

Using %ROWTYPE with a Record

DECLARE -- declare variables-- declare record variable that represents a row fetched from the employees table   emp_rec employees%ROWTYPE; -- declare variable with %ROWTYPE attributeBEGIN  SELECT * INTO emp_rec FROM EMPLOYEES WHERE employee_id = 120; -- retrieve record  DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_rec.first_name || ' ' 
                       || emp_rec.last_name); -- displayEND;/

Declaring variables with %ROWTYPE has several advantages. First, you do not need to know the exact data type of the table columns. Second, if you change the database definition of any of the table columns, the data types associated with the %ROWTYPE declaration change accordingly at runtime.


See Also:

Oracle Database PL/SQL Language Reference for information on %ROWTYPE

Using the %TYPE Attribute to Declare Variables

The %TYPE attribute provides the data type of a variable or table column. This is particularly useful when declaring variables that will hold values of a table column. For example, suppose you want to declare variables as the same data type as columns employee_id and last_name in table employees. To declare variables named empid and emplname that have the same data type as the table columns, use dot notation and the %TYPE attribute. See Example: Using %TYPE With Table Columns.

Using %TYPE With Table Columns

DECLARE -- declare variables using %TYPE attribute
   empid    employees.employee_id%TYPE;  -- employee_id data type is NUMBER(6)
   emplname employees.last_name%TYPE;  -- last_name data type is VARCHAR2(25)
BEGIN
   empid    := 100301;  -- this is OK because it fits in NUMBER(6)
--   empid  := 3018907;  -- this is too large and will cause an overflow
   emplname := 'Patel'; --  this is OK because it fits in VARCHAR2(25)
   DBMS_OUTPUT.PUT_LINE('Employee Id: ' || empid);  -- display data
   DBMS_OUTPUT.PUT_LINE('Employee name: ' || emplname); -- display data
END;
/

Declaring variables with %TYPE has two advantages. First, you need not know the exact data type of the table columns. Second, if you change the database definition of columns, such as employee_id or last_name, the data types of empid and emplname in Example: Using %TYPE With Table Columns change accordingly at runtime.


See Also:

Oracle Database PL/SQL Language Reference for information on %TYPE

Using PL/SQL Control Structures

Control structures are the most important PL/SQL extension to SQL. Not only can you use PL/SQL to manipulate Oracle data, you can 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.

Conditional Control With IF-THEN

Often, it is necessary to take alternative actions depending on circumstances. The IF-THEN statement enables you to execute a sequence of statements conditionally. The forms of the statement can be IF-THEN, IF-THEN-ELSE, or IF-THEN-ELSIF-ELSE. 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: Using a Simple IF-THEN Statement shows a simple use of the IF-THEN statement.

Using a Simple IF-THEN Statement

DECLARE
  sales  NUMBER(8,2) := 10100;
  quota  NUMBER(8,2) := 10000;
  bonus  NUMBER(6,2);
  emp_id NUMBER(6) := 120; -- use employee 120 for testing
BEGIN
  IF sales > (quota + 200) THEN
     bonus := (sales - quota)/4;
     UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;
  END IF;
END;
/

Example: Using the IF-THEN-ELSIF Statement shows the use of IF-THEN-ELSIF-ELSE to determine the salary raise an employee receives based on the hire date of the employee.

Using the IF-THEN-ELSIF Statement

DECLARE
  bonus  NUMBER(6,2);
  emp_id NUMBER(6) := 120;
  hire_date DATE;
BEGIN
  SELECT hire_date INTO hire_date FROM employees WHERE employee_id = 120;
  IF hire_date > TO_DATE('01-JAN-98') THEN
      bonus := 500;
   ELSIF hire_date > TO_DATE('01-JAN-96') THEN
      bonus := 1000;
   ELSE
      bonus := 1500;
   END IF;
   UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;
END;
/

Conditional Control With the CASE Statement

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, such as an entire PL/SQL block, for each case. When possible, rewrite lengthy IF-THEN-ELSIF statements as CASE statements because the CASE statement is more readable and more efficient.

Example: Using the CASE-WHEN Statement shows a simple CASE statement.

Using the CASE-WHEN Statement

DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';
  CASE grade
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;
/

Example: Using the IF-THEN_ELSE and CASE Statement determines the salary raise an employee receives based on the current salary of the employee and the job Id. This complex example combines the CASE expression with IF-THEN-ELSE statements.

Using the IF-THEN_ELSE and CASE Statement

DECLARE -- declare variables
   jobid      employees.job_id%TYPE;
   empid      employees.employee_id%TYPE := 115;
   sal        employees.salary%TYPE;
   sal_raise  NUMBER(3,2);
BEGIN
-- retrieve data from employees and assign to variables jobid and sal
  SELECT job_id, salary INTO jobid, sal from employees WHERE employee_id = empid;
  CASE  -- check for conditions
    WHEN jobid = 'PU_CLERK' THEN
        IF sal < 3000 THEN sal_raise := .08;
          ELSE sal_raise := .07;
        END IF;
    WHEN jobid = 'SH_CLERK' THEN
        IF sal < 4000 THEN sal_raise := .06;
          ELSE sal_raise := .05;
        END IF;
    WHEN jobid = 'ST_CLERK' THEN
        IF sal < 3500 THEN sal_raise := .04;
          ELSE sal_raise := .03;
        END IF;
    ELSE
     BEGIN
-- if no conditions met, then the following
       DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid); 
     END;
  END CASE;
  UPDATE employees SET salary = salary + salary * sal_raise 
    WHERE employee_id = empid; -- update a record in the employees table
  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.

Iterative Control With LOOPs

LOOP statements enable you to 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 FOR-LOOP statement enables you to specify a range of integers, then execute a sequence of statements once for each integer in the range. In Example: Using the FOR-LOOP the loop displays the number and the square of the number for numbers 1 to 10. inserts 100 numbers, square roots, squares, and the sum of squares into a database table:

Using the FOR-LOOP

BEGIN
-- use a FOR loop to process a series of numbers
  FOR i in 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(i) || ' Square: ' || TO_CHAR(i*i));
  END LOOP;
END;
/

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: Using WHILE-LOOP for Control, you find the first employee who has a salary over $15000 and is higher in the chain of command than employee 120:

Using WHILE-LOOP for Control

-- create a temporary table for this example
CREATE TABLE temp (tempid NUMBER(6), tempsal NUMBER(8,2), tempname VARCHAR2(25));
DECLARE  -- declare variables
   sal            employees.salary%TYPE := 0;
   mgr_id         employees.manager_id%TYPE;
   lname          employees.last_name%TYPE;
   starting_empid employees.employee_id%TYPE := 120;
BEGIN
   SELECT manager_id INTO mgr_id FROM employees 
      WHERE employee_id = starting_empid;  -- retrieve data from employees
-- use WHILE LOOP to process data
   WHILE sal <= 15000 LOOP -- loop until sal > 15000
      SELECT salary, manager_id, last_name INTO sal, mgr_id, lname
         FROM employees WHERE employee_id = mgr_id;
   END LOOP;
   INSERT INTO temp VALUES (NULL, sal, lname); -- insert NULL for tempid in table
   COMMIT;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      INSERT INTO temp VALUES (NULL, NULL, 'Not found'); -- insert NULLs
      COMMIT;
END;
/
-- display rows in table temp
SELECT * FROM temp;
-- drop temporary table
DROP TABLE temp;

The EXIT-WHEN statement enables you to 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: Using the EXIT-WHEN Statement, the loop completes when the value of total exceeds 25,000:

Using the EXIT-WHEN Statement

DECLARE -- declare and assign values to variables
  total   NUMBER(9) := 0;
  counter NUMBER(6) := 0;
BEGIN
  LOOP
    counter := counter + 1; -- increment counter variable
    total := total + counter * counter;  -- compute total
    -- exit loop when condition is true
    EXIT WHEN total > 25000; -- LOOP until condition is met 
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter) || ' Total: ' || TO_CHAR(total));  -- display data
END;
/

Sequential Control With GOTO

The GOTO statement enables you to 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 shown in Example: Using the GOTO Statement.

Using the GOTO Statement

DECLARE  -- declare variables
  p        VARCHAR2(30);
  n        PLS_INTEGER := 37; -- test any integer > 2 for prime, here 37
BEGIN
-- loop through divisors to determine if a prime number
  FOR j in 2..ROUND(SQRT(n)) 
  LOOP
    IF n MOD j = 0 THEN -- test for prime
      p := ' is NOT a prime number'; -- not a prime number
      GOTO print_now;
    END IF;
  END LOOP;
  p := ' is a prime number';
<<print_now>>
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p);  -- display data
END;
/

Using Local PL/SQL Subprograms in PL/SQL Blocks

Subprograms are named PL/SQL blocks that can be called with a set of parameters from inside a PL/SQL block. PL/SQL has two types of subprograms: procedures and functions.

Example: Declaring a Procedure With IN OUT Parameters is an example of a declaration of a PL/SQL procedure in a PL/SQL block. Note that the v1 and v2 variables are declared as IN OUT parameters to a subprogram. An IN OUT parameter passes an initial value that is read inside a subprogram and then returns a value that has been updated in the subprogram.

Declaring a Procedure With IN OUT Parameters

DECLARE -- declare variables and subprograms
  fname    VARCHAR2(20) := 'randall';
  lname    VARCHAR2(25) := 'dexter';
  PROCEDURE upper_name ( v1 IN OUT VARCHAR2, v2 IN OUT VARCHAR2) AS
    BEGIN
      v1 := UPPER(v1); -- change the string to uppercase
      v2 := UPPER(v2); -- change the string to uppercase
    END;
BEGIN
  DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); -- display initial values
  upper_name (fname, lname); -- call the procedure with parameters
  DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); -- display new values
END;
/

Example: Declaring a Function With IN Parameters is an example of a declaration of a PL/SQL function in a PL/SQL block. Note that the value returned by the function is used directly in the DBMS_OUTPUT.PUT_LINE statement. Note that the v1 and v2 variables are declared as IN parameters to a subprogram. An IN parameter passes an initial value that is read inside a subprogram. Any update to the value of the parameter inside the subprogram is not accessible outside the subprogram.

Declaring a Function With IN Parameters

DECLARE -- declare variables and subprograms
  fname    VARCHAR2(20) := 'randall';
  lname    VARCHAR2(25) := 'dexter';
  FUNCTION upper_name ( v1 IN VARCHAR2, v2 IN VARCHAR2)
    RETURN VARCHAR2 AS
    v3     VARCHAR2(45);  -- this variable is local to the function
    BEGIN
-- build a string that will be returned as the function value 
      v3 := v1 || ' + ' || v2 || ' = ' || UPPER(v1) || ' ' || UPPER(v2);
      RETURN v3;  -- return the value of v3
    END;
BEGIN
-- call the function and display results
  DBMS_OUTPUT.PUT_LINE(upper_name (fname, lname)); 
END;
/

In Example: Declaring a Complex Procedure in a PL/SQL Block, both a variable and a numeric literal are passed as a parameter to a more complex procedure.

Declaring a Complex Procedure in a PL/SQL Block

DECLARE  -- declare variables and subprograms
  empid NUMBER;
  PROCEDURE avg_min_max_sal (empid IN NUMBER) IS
    jobid     VARCHAR2(10);
    avg_sal   NUMBER;
    min_sal   NUMBER;
    max_sal   NUMBER;
  BEGIN
-- determine the job Id for the employee
    SELECT job_id INTO jobid FROM employees WHERE employee_id = empid;
-- calculate the average, minimum, and maximum salaries for that job Id
    SELECT AVG(salary), MIN(salary), MAX(salary) INTO avg_sal, min_sal, max_sal
      FROM employees WHERE job_id = jobid;
-- display data
    DBMS_OUTPUT.PUT_LINE ('Employee Id: ' || empid || ' Job Id: ' || jobid);
    DBMS_OUTPUT.PUT_LINE ('The average salary for job Id: ' || jobid 
                            || ' is ' || TO_CHAR(avg_sal));
    DBMS_OUTPUT.PUT_LINE ('The minimum salary for job Id: ' || jobid 
                            || ' is ' || TO_CHAR(min_sal));
    DBMS_OUTPUT.PUT_LINE ('The maximum salary for job Id: ' || jobid 
                            || ' is ' || TO_CHAR(max_sal));
  END avg_min_max_sal;
BEGIN
-- call the procedure with several employee Ids
  empid := 125;
  avg_min_max_sal(empid);
  avg_min_max_sal(112);
END;
/

Subprograms can also be declared in packages. You can create subprograms that are stored in the database. These subprograms can be called from other subprograms, packages, and SQL statements. See Subprograms and Packages: Usage Information.

Working With PL/SQL Data Structures

Data structure are composite data types that enable you to 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.

Using 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. You can explicitly declare a cursor for one row, as shown in Example: Using %ROWTYPE with a Record declares an explicit cursor.

For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. See Example: Fetching With a Cursor.

Fetching With a Cursor

DECLARE  -- declare variables and cursors
  jobid     employees.job_id%TYPE;     -- variable for job_id
  lastname  employees.last_name%TYPE;  -- variable for last_name
  CURSOR c1 IS SELECT last_name, job_id FROM employees 
                 WHERE job_id LIKE '%CLERK';
  employees employees%ROWTYPE;         -- record variable for row
  CURSOR c2 is SELECT * FROM employees 
                 WHERE job_id LIKE '%MAN' OR job_id LIKE '%MGR';
BEGIN
  OPEN c1; -- open the cursor before fetching
  LOOP
    FETCH c1 INTO lastname, jobid; -- fetches 2 columns into variables
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( RPAD(lastname, 25, ' ') || jobid );
  END LOOP;
  CLOSE c1;
  DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
  OPEN c2;
  LOOP
    FETCH c2 INTO employees; -- fetches entire row into the employees record
    EXIT WHEN c2%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( RPAD(employees.last_name, 25, ' ') ||
                               employees.job_id );
  END LOOP;
  CLOSE c2;
END;
/

In Example: Fetching With a Cursor, LIKE is used to specify the records to return with the query. For information on LIKE, see Restricting Data Using the WHERE Clause.


See Also:

Oracle Database PL/SQL Language Reference for information on managing cursors with PL/SQL

Using Collections

PL/SQL collection types enable you to 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.

To reference an element, use subscript notation with parentheses, as shown in Example: Using a PL/SQL Collection Type.

Using a PL/SQL Collection Type

DECLARE -- declare variables
  TYPE jobids_array IS VARRAY(12) OF VARCHAR2(10);  -- declare VARRAY
  jobids  jobids_array; -- declare a variable of type jobids_array
  howmany NUMBER;  -- declare a variable to hold employee count
BEGIN
  -- initialize the arrary with some job Id values
  jobids := jobids_array('AC_ACCOUNT', 'AC_MGR', 'AD_ASST', 'AD_PRES', 'AD_VP',
                         'FI_ACCOUNT', 'FI_MGR', 'HR_REP', 'IT_PROG', 'SH_CLERK',
                         'ST_CLERK', 'ST_MAN');
  FOR i IN jobids.FIRST..jobids.LAST LOOP -- loop through all the varray values
  -- determine the number of employees for each job Id in the array
    SELECT COUNT(*) INTO howmany FROM employees WHERE job_id = jobids(i);
    DBMS_OUTPUT.PUT_LINE ( 'Job Id: ' || jobids(i) || 
                           ' Number of employees: ' || TO_CHAR(howmany));
  END LOOP;
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.


See Also:

Oracle Database PL/SQL Language Reference for information on PL/SQL collections

Using 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 the TYPE definition.

Example: Declaring a Record Type shows how are records are declared.

Declaring a Record Type

DECLARE  -- declare RECORD type variables
   TYPE timerec IS RECORD (hours SMALLINT, minutes SMALLINT);
   TYPE meetin_typ IS RECORD (
      date_held DATE,
      duration  timerec,  -- nested record
      location  VARCHAR2(20),
      purpose   VARCHAR2(50));
BEGIN
-- NULL does nothing but allows unit to be compiled and tested
  NULL;
END;
/

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. When using %ROWTYPE, the record type definition is implied and the TYPE keyword is not necessary, as shown in Example: Using %ROWTYPE with a Cursor.

Using %ROWTYPE with a Cursor

DECLARE -- declare variables
  CURSOR c1 IS
    SELECT * FROM employees 
       WHERE employee_id = 120; -- declare cursor
-- declare record variable that represents a row fetched from the employees table
   employee_rec c1%ROWTYPE; -- declare variable with %ROWTYPE attribute
BEGIN
-- open the explicit cursor c1 and use it to fetch data into employee_rec
  OPEN c1;
  FETCH c1 INTO employee_rec; -- retrieve record
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name); -- display
END;
/

See Also:

Oracle Database PL/SQL Language Reference for information on PL/SQL records

Processing Queries with PL/SQL

Processing a SQL query with PL/SQL is like processing files with other languages. This process includes opening a file, reading the file contents, processing each line, then closing 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: Processing Query Results in a LOOP.

Processing Query Results in a LOOP

BEGIN
-- use values from SELECT for FOR LOOP processing
  FOR someone IN (SELECT * FROM employees WHERE employee_id < 120 )
  LOOP
    DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name ||
                         ', Last name = ' || someone.last_name);
  END LOOP;
END;
/

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.

Using Dynamic SQL in PL/SQL

PL/SQL supports both dynamic and static SQL. Dynamic SQL enables you to build SQL statements dynamically at runtime while static SQL statements are known in advance. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation. For additional information about dynamic SQL, see Oracle Database PL/SQL Language Reference.

To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multirow query (SELECT statement), you use the OPEN-FOR, FETCH, and CLOSE statements.

Example: Examples of Dynamic SQL illustrates several uses of dynamic SQL.

Examples of Dynamic SQL

-- create a standalone procedure
CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value NUMBER, 
                             emp_column VARCHAR2, amount NUMBER) IS
   column VARCHAR2(30);
   sql_stmt  VARCHAR2(200);
BEGIN
-- determine if a valid column name has been given as input
  SELECT COLUMN_NAME INTO column FROM USER_TAB_COLS 
    WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;
  sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' 
               || column || ' = :2';
  EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Salaries have been updated for: ' || emp_column 
                        || ' = ' || column_value);
  END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/

DECLARE
   plsql_block       VARCHAR2(500);
BEGIN
-- note the semi-colons (;) inside the quotes '...'
  plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';
  EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10;
  EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'
      USING 112, 'EMPLOYEE_ID', 10;
END;
/

DECLARE
   sql_stmt          VARCHAR2(200);
   column          VARCHAR2(30) := 'DEPARTMENT_ID';
   dept_id           NUMBER(4) := 46;
   dept_name         VARCHAR2(30) := 'Special Projects';
   mgr_id            NUMBER(6) := 200;
   loc_id            NUMBER(4) := 1700;
BEGIN
-- note that there is no semi-colon (;) inside the quotes '...'
  EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
  sql_stmt := 'INSERT INTO departments VALUES (:1, :2, :3, :4)';
  EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id;
  EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || column || ' = :num'
      USING dept_id;
  EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
  EXECUTE IMMEDIATE 'DROP TABLE bonus';
END;
/
-- rollback the changes
ROLLBACK;
-- delete the procedure
DROP PROCEDURE raise_emp_salary;

Using Bind Variables

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

You do need to specify bind variables (also called bind arguments) with dynamic SQL, in clauses like WHERE and VALUES where you normally use variables. Instead of concatenating literals and variable values into a single string, replace the variables with the names of bind variables (prefixed by a colon) and specify the corresponding PL/SQL variables with the USING clause. Using the USING clause, instead of concatenating the variables into the string, reduces parsing overhead and lets Oracle reuse the SQL statements.

In Example: Examples of Dynamic SQL, :1 and :2 are bind variables for amount and column_value. In the same example, there are additional bind variables, such as :cvalue, :cname, and :amt.

Handling PL/SQL Errors

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.

PL/SQL exception handling is different from the manual checking you might be used to from 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. See Summary of Predefined PL/SQL Exceptions.

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. See Declaring PL/SQL Exceptions.

This topic includes the following topics:

Summary of Predefined PL/SQL Exceptions

An internal exception is raised automatically if your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.

To handle unexpected Oracle errors, you can use the OTHERS handler. Within this handler, you can call the functions SQLCODE and SQLERRM to return the Oracle error code and message text.

PL/SQL declares predefined exceptions globally in package STANDARD. You need not declare them yourself. You can write handlers for predefined exceptions using the names in Table: Predefined PL/SQL Exceptions.

Predefined PL/SQL Exceptions

Exception Description

ACCESS_INTO_NULL

A program attempts to assign values to the attributes of an uninitialized object

CASE_NOT_FOUND

None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.

COLLECTION_IS_NULL

A program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.

CURSOR_ALREADY_OPEN

A program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers, so your program cannot open that cursor inside the loop.

DUP_VAL_ON_INDEX

A program attempts to store duplicate values in a column that is constrained by a unique index.

INVALID_CURSOR

A program attempts a cursor operation that is not allowed, such as closing an unopened cursor.

INVALID_NUMBER

n a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.

LOGIN_DENIED

A program attempts to log on to Oracle with an invalid username or password.

NO_DATA_FOUND

A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table.

Because this exception is used internally by some SQL functions to signal completion, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query.

NOT_LOGGED_ON

A program issues a database call without being connected to Oracle.

PROGRAM_ERROR

PL/SQL has an internal problem.

ROWTYPE_MISMATCH

The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. When an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.

SELF_IS_NULL

A program attempts to call a MEMBER method, but the instance of the object type has not been initialized. The built-in parameter SELF points to the object, and is always the first parameter passed to a MEMBER method.

STORAGE_ERROR

PL/SQL runs out of memory or memory has been corrupted.

SUBSCRIPT_BEYOND_COUNT

A program references a nested table or varray element using an index number larger than the number of elements in the collection.

SUBSCRIPT_OUTSIDE_LIMIT

A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.

SYS_INVALID_ROWID

The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.

TIMEOUT_ON_RESOURCE

A time out occurs while Oracle is waiting for a resource.

TOO_MANY_ROWS

A SELECT INTO statement returns more than one row.

VALUE_ERROR

An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)

ZERO_DIVIDE

A program attempts to divide a number by zero.


Using the Exception Handler

Using exceptions for error handling has several advantages. With exceptions, you can reliably handle potential errors from many statements with a single exception handler:

Managing Multiple Errors With a Single Exception Handler

DECLARE  -- declare variables
   emp_column       VARCHAR2(30) := 'last_name';
   table_name       VARCHAR2(30) := 'emp';  -- set value to raise error
   temp_var         VARCHAR2(30);
BEGIN
  temp_var := emp_column;
  SELECT COLUMN_NAME INTO temp_var FROM USER_TAB_COLS 
    WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = UPPER(emp_column);
-- processing here
  temp_var := table_name;
  SELECT OBJECT_NAME INTO temp_var FROM USER_OBJECTS
    WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
-- processing here
EXCEPTION
   WHEN NO_DATA_FOUND THEN  -- catches all 'no data found' errors
     DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp_var);
END;
/

Declaring PL/SQL Exceptions

Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION. In Example: Scope of PL/SQL Exceptions, you declare an exception named past_due that is raised when the due_date is less than the today's date.

Exception and variable declarations are similar. But remember, an exception is an error condition, not a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions.

Scope Rules for PL/SQL Exceptions

You cannot declare an exception twice in the same block. You can, however, declare the same exception in two different blocks.

Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block.

If you redeclare a global exception in a sub-block, the local declaration prevails. The sub-block cannot reference the global exception, unless the exception is declared in a labeled block and you qualify its name with the block label:

block_label.exception_name

Example: Scope of PL/SQL Exceptions illustrates the scope rules:

Scope of PL/SQL Exceptions

DECLARE
   past_due EXCEPTION;
   acct_num NUMBER;
BEGIN
   DECLARE  ---------- sub-block begins
     past_due EXCEPTION;  -- this declaration prevails
     acct_num NUMBER;
     due_date DATE := SYSDATE - 1; -- set on purpose to raise exception
     todays_date DATE := SYSDATE;
   BEGIN
      IF due_date < todays_date THEN
         RAISE past_due;  -- this is not handled
      END IF;
   END;  ------------- sub-block ends
EXCEPTION
  WHEN past_due THEN  -- does not handle raised exception
    DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
/

The enclosing block does not handle the raised exception because the declaration of past_due in the sub-block prevails. Though they share the same name, the two past_due exceptions are different, just as the two acct_num variables share the same name but are different variables. Thus, the RAISE statement and the WHEN clause refer to different exceptions. To have the enclosing block handle the raised exception, you must remove its declaration from the sub-block or define an OTHERS handler.

Continuing After an Exception is Raised

By default, 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.

You can still handle an exception for a statement, then continue with the next statement. Place the statement in its own sub-block with its own exception handlers. If an error occurs in the sub-block, a local handler can catch the exception. When the sub-block ends, the enclosing block continues to execute at the point where the sub-block ends, as shown in Example: Continuing After an Exception.

Continuing After an Exception

-- create a temporary table for this example
CREATE TABLE employees_temp AS 
  SELECT employee_id, salary, commission_pct FROM employees;

DECLARE
  sal_calc NUMBER(8,2);
BEGIN
  INSERT INTO employees_temp VALUES (303, 2500, 0);
  BEGIN -- sub-block begins
    SELECT salary / commission_pct INTO sal_calc FROM employees_temp
      WHERE employee_id = 303;
    EXCEPTION
      WHEN ZERO_DIVIDE THEN
        sal_calc := 2500;
  END; -- sub-block ends
  INSERT INTO employees_temp VALUES (304, sal_calc/100, .1);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    NULL;
END;
/
-- view the results
SELECT * FROM employees_temp WHERE employee_id = 303 OR employee_id = 304;
-- drop the temporary table
DROP TABLE employees_temp;

In this example, if the SELECT INTO statement raises a ZERO_DIVIDE exception, the local handler catches it and sets sal_calc to 2500. Execution of the handler is complete, so the sub-block terminates, and execution continues with the INSERT statement.