Home > Oracle Database Usage Infor... > PL/SQL: Usage Information
The topic discusses the development with PL/SQL. It includes the following topics:
See Also:
|
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.
Anonymous block
An anonymous block is a PL/SQL block that appears in your application and is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear. A PL/SQL block groups related declarations and statements.
Stored or standalone subprogram
A stored or standalone subprogram is a PL/SQL block that Oracle stores in the database and can be called by name from an application. Subprograms can be procedures or functions; the difference is that functions return a value when executed. When you create a stored subprogram, Oracle parses the subprogram and stores its parsed representation in the database. See Subprograms and Packages: Usage Information.
Package
A package is a group of subprograms and variable definitions that Oracle stores in the database. Subprograms and variables in packages can be called from other packages or subprograms. See Subprograms and Packages: Usage Information.
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.
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.
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.
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.
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/SQLBOOLEAN and PLS_INTEGER data types |
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 ampersanddebit-amount
is not allowed because of the hyphenon/off
is not allowed because of the slashuser id
is not allowed because of the spaceYou 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 |
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; /
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 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:
|
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;
/
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; /
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:
|
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.
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 |
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 |
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
.
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; /
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.
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; /
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; /
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.
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.
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 |
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 |
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 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.
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;
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
.
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:
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 |
---|---|
A program attempts to assign values to the attributes of an uninitialized object |
|
None of the choices in the |
|
A program attempts to apply collection methods other than |
|
A program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor |
|
A program attempts to store duplicate values in a column that is constrained by a unique index. |
|
A program attempts a cursor operation that is not allowed, such as closing an unopened cursor. |
|
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, |
|
A program attempts to log on to Oracle with an invalid username or password. |
|
A 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. |
|
A program issues a database call without being connected to Oracle. |
|
PL/SQL has an internal problem. |
|
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. |
|
A program attempts to call a |
|
PL/SQL runs out of memory or memory has been corrupted. |
|
A program references a nested table or varray element using an index number larger than the number of elements in the collection. |
|
A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range. |
|
The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid. |
|
A time out occurs while Oracle is waiting for a resource. |
|
A |
|
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 |
|
A program attempts to divide a number by zero. |
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; /
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.
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.
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.