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 multi-line comment styles.
Single-line comments begin with a double hyphen (--) anywhere on a line and extend to the end of the line. Multi-line 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 multi-line 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 LastNameLastName is the same as lastname and LASTNAMELASTNAME is same as lastname and LastNameThe 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 run time.
|
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 run time.
|
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 multi-row 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.