2.3 PL/SQL
PL/SQL is Oracle's proprietary procedural language that was developed to add programming constructs around SQL. PL/SQL contains standard programming constructs such as the following:
PL/SQL is primarily used for adding procedures and functions to a database, and in Chapter 3 you will see how to call PL/SQL procedures and functions from your SQLJ programs.
2.3.1 PL/SQL Program Structure
Like most programs written in third-generation programming languages, PL/SQL programs are divided up into blocks, with each block encapsulating a set of PL/SQL program statements. A PL/SQL block has the following structure:
[DECLARE declaration_statements ] BEGIN executable_statements [EXCEPTION exception_handling_statements ] END;
The syntax elements are as follows:
- declaration_statements
-
The declarations for the variables used by the block.
- executable_statements
-
The executable statements for the block. These statements implement the business logic.
- exception_handling_statements
-
The statements that handle errors generated by the executable statements.
A block must be terminated using the END keyword. The following is a simple example of a PL/SQL block, which declares an integer variable named x and initializes x to 10 in the executable section:
DECLARE x INTEGER; BEGIN x := 10; END; /
An EXCEPTION block allows you to trap errors. In the following example, an attempt to perform a division by is made, which causes control to pass to the EXCEPTION block that handles the error:
DECLARE x INTEGER; BEGIN x := 1/0; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Division by zero'); END; / The DBMS_OUTPUT.PUT_LINE( ) procedure is used to send output to a buffer, which is displayed in SQL*Plus if the command SET SERVEROUTPUT ON has been issued prior to running the example.
 | The forward slash (/) that you see in the last line of the previous two PL/SQL blocks is not, strictly speaking, part of PL/SQL syntax. The forward slash is necessary only when you are typing a PL/SQL block into SQL*Plus; it tells SQL*Plus that you are done entering a PL/SQL block or stored program unit. |
|
2.3.2 Declarations
Variables declared within the DECLARE section of a PL/SQL block are local to that block. Variable declarations in PL/SQL have the following syntax:
variable_name variable_type;
The syntax elements are as follows:
- variable_name
-
The name you wish to assign to the variable.
- variable_type
-
The PL/SQL type of the variable. The PL/SQL types are basically the same as the column types shown earlier in this chapter.
The following example illustrates a few variable declarations:
DECLARE state CHAR(2); first_name VARCHAR2(10); id INTEGER; length NUMBER; dob DATE; BEGIN ...
Notice the use of the INTEGER type to declare the id variable; INTEGER is a sub-type of the NUMBER type. In addition to using the built-in data types, variables may also be declared using the %TYPE keyword, which specifies that the type used for the variable is the same as that used for a specified column in a table. The following examples illustrates the use of %TYPE to declare a variable of the same type as the name column of the products table:
DECLARE product_name products.name%TYPE; BEGIN ...
2.3.3 Conditional Logic
PL/SQL uses the IF, THEN, ELSE, ELSIF, and END IF keywords for performing conditional logic. The following syntax illustrates the use of conditional logic:
IF condition1 THEN statements1 ELSIF condition2 THEN statements2 ELSE statements3 END IF;
The syntax elements are as follows:
- condition1 and condition2
-
Boolean expressions that evaluate to true or false.
- statements1, statements2, and statements3
-
Groups of PL/SQL statements.
If condition1 is true, then statements1 are executed; if condition1 is false but condition2 is true, then statements2 are executed; if neither condition1 nor condition2 is true, then statements3 are executed. The following example illustrates the use of conditional logic; notice the use of a nested IF statement:
IF x < 0 THEN message := 'x is negative'; ELSIF x = 0 THEN message := 'x is zero'; IF y = 0 THEN message := 'x and y are zero'; END IF ELSE message := 'x is positive'; END IF;
2.3.4 Loops
Loops may be used to execute a statement, or a group of statements, many times over. Loops can execute indefinitely, or you can have them terminate when a specified condition is met. There are three types of loops in PL/SQL:
-
Simple loops
-
FOR loops
-
WHILE loops
2.3.4.1 Simple loops
A simple loop runs until you execute a statement to explicitly end the loop. The termination condition is not built into the loop syntax. The general form of a simple loop is as follows:
LOOP statements END LOOP;
You can use the EXIT or EXIT WHEN statement to terminate a loop. The EXIT statement ends a loop immediately. The EXIT WHEN statement specifies a condition that must be satisfied before the loop is terminated.
The following example illustrates a simple loop. The variable y is initialized to 10 before the loop is entered, and is then decremented by 1 with each iteration through the loop. The EXIT WHEN statement causes control to leave the loop when the variable y equals 0.
y := 10; LOOP x := y + 1; y := y - 1; EXIT WHEN y = 0; END LOOP;
The EXIT WHEN statement doesn't have to be the last statement executed: it can appear anywhere in the code.
2.3.4.2 FOR loops
A FOR loop performs a predetermined number of iterations. You set the number of iterations by specifying lower and upper bounds for a loop variable. This loop variable is then incremented or decremented with each iteration through the loop. The general form of a FOR loop is:
FOR loop_variable IN [REVERSE] lower_bound..upper_bound LOOP statements END LOOP;
The syntax elements are as follows:
- loop_variable
-
The name of the loop variable. The loop variable is implicitly created by the loop as an integer and is local to the loop. The value stored in the loop variable is increased (or decreased if the REVERSE keyword is used) by 1 at the end of each iteration through the loop.
- lower_bound
-
The lower bound for the loop. If REVERSE is not used, the loop variable is initialized to this lower bound.
- upper_bound
-
The upper bound for the loop. If REVERSE is used, the loop variable is initialized to this upper bound.
- REVERSE
-
A keyword indicating a loop that counts backwards. The loop variable is initialized to the upper bound and decremented by 1 until it reaches the lower bound. The lower and upper bounds are still specified in the same order as for a forward-counting loop.
The following example illustrates the use of a FOR loop. Notice that the variable x is referenced within the loop:
FOR x IN 1..10 LOOP message := 'x = ' || x; END LOOP;
The loop variable may be set in the reverse order from the upper bound to the lower bound using the REVERSE keyword. If you used the REVERSE keyword in this example, the loop variable x would start at 10 and end at 1.
2.3.4.3 WHILE loops
A WHILE loop executes until a specified condition is met. The general form of a WHILE loop is:
WHILE condition LOOP statements END LOOP;
The syntax elements are as follows:
- condition
-
Before each iteration of the loop, the condition is checked. If the condition is true, the statements in the loop are executed; if the condition is false, the loop terminates.
- statements
-
A list of statements to be executed.
This example illustrates the use of a WHILE loop:
x := 10; WHILE x > 0 LOOP x := x - 1; END LOOP;
The loop runs until the variable x reaches 0.
2.3.5 Cursors
If you have a query that returns many rows and you want to access those rows using PL/SQL, then you must use a cursor to read those rows. A cursor is an object that allows you store one or more rows retrieved from the database. There are five steps you must perform when using a cursor:
-
Declare variables to store the column values.
-
Declare the cursor.
-
Open the cursor.
-
Fetch the rows from the cursor.
-
Close the cursor.
I will now describe the details of these five steps.
2.3.5.1 Step 1: Declare the variables used to store the column values
You must first declare variables that will be used in Step 4 to store the column values retrieved from the rows stored in the cursor. These variables must be compatible with the column types; to ensure this, you should declare the variables using %TYPE. The following example declares five variables to store the id, first_name, last_name, dob, and phone columns from the customers table:
DECLARE v_id customers.id%TYPE; v_first_name customers.first_name%TYPE; v_last_name customers.last_name%TYPE; v_dob customers.dob%TYPE; v_phone customers.phone%TYPE;
Notice that all the variables were declared using the corresponding column name from the customers table followed by %TYPE. This causes the column types to be automatically picked up and used as the variable types.
2.3.5.2 Step 2: Declare the cursor
A cursor definition consists of a cursor name and a SELECT statement that is executed when the cursor is opened. The following declaration is for a cursor named customer_cursor:
CURSOR customer_cursor IS SELECT id, first_name, last_name, dob, phone FROM customers ORDER BY id;
Cursor declarations must be placed in the declaration section of a PL/SQL block.
2.3.5.3 Step 3: Open the cursor
Once a cursor has been declared, you may open the cursor in the executable section of the PL/SQL block. For example:
OPEN customer_cursor;
When you open a cursor, the underlying SQL query is executed. The rows in the cursor are then available for you to fetch and process.
2.3.5.4 Step 4: Fetch the rows from the cursor
You can retrieve each row from the cursor using the FETCH statement; this retrieves the column values from the rows in the cursor into the variables previously declared in Step 1. The following FETCH statement retrieves one row from customer_cursor:
FETCH customer_cursor INTO v_id, v_first_name, v_last_name, v_dob, v_phone;
Because the cursor may contain many rows, a loop is required to read each row in turn:
LOOP FETCH customer_cursor INTO v_id, v_first_name, v_last_name, v_dob, v_phone; exit the loop when there are no more rows, as indicated by -- the boolean customer_cursor%NOTFOUND EXIT WHEN customer_cursor%NOTFOUND; -- use DBMS_OUTPUT.PUT_LINE( ) to print the variables DBMS_OUTPUT.PUT_LINE( 'v_id = ' || v_id || ', v_first_name = ' || v_first_name || ', v_last_name = ' || v_last_name || ', v_dob = ' || v_dob || ', v_phone = ' || v_phone ); END LOOP;
(Notice that the comments in this PL/SQL example are delineated using --.) The loop is terminated when the Boolean value customer_cursor%NOTFOUND is true; this will happen when the FETCH reaches the end of the rows in the cursor. The PL/SQL package DBMS_OUTPUT contains the procedure PUT_LINE( ), which is used to print a line on the screen; the call to the PUT_LINE( ) procedure displays the variables that were populated by the FETCH statement.
2.3.5.5 Step 5: Close the cursor
After you retrieve all the rows, you should close the cursor, using the CLOSE statement, to free system resources. The following statement closes customer_cursor:
CLOSE customer_cursor;
The next section contains a complete example script that demonstrates all five steps.
2.3.6 Example Script: cursor.sql
The SQL*Plus script cursor.sql (Example 2-1) implements the five steps for using cursors. This script is available for download from the web site for this book.
Example 2-1. cursor.sql
-- The SQL*Plus script cursor.sql displays the rows in the -- customers table using a cursor. DECLARE -- step 1: declare the variables v_id customers.id%TYPE; v_first_name customers.first_name%TYPE; v_last_name customers.last_name%TYPE; v_dob customers.dob%TYPE; v_phone customers.phone%TYPE; -- step 2: declare the cursor CURSOR customer_cursor IS SELECT id, first_name, last_name, dob, phone FROM customers ORDER BY id; BEGIN -- step 3: open the cursor OPEN customer_cursor; LOOP -- step 4: fetch the rows from the cursor FETCH customer_cursor INTO v_id, v_first_name, v_last_name, v_dob, v_phone; -- exit the loop when there are no more rows, as indicated by -- the boolean customer_cursor%NOTFOUND EXIT WHEN customer_cursor%NOTFOUND; -- use DBMS_OUTPUT.PUT_LINE( ) to print the variables DBMS_OUTPUT.PUT_LINE( 'v_id = ' || v_id || ', v_first_name = ' || v_first_name || ', v_last_name = ' || v_last_name || ', v_dob = ' || v_dob || ', v_phone = ' || v_phone ); END LOOP; -- step 5: close the cursor CLOSE customer_cursor; END; /
To run this example, follow these steps:
-
Start SQL*Plus.
-
Log in as the user named fundamental_user.
-
Enter the command SET SERVEROUTPUT ON. This causes the output from the call to DBMS_OUTPUT.PUT_LINE( ) to appear on the screen.
-
Run the cursor.sql script, using the command @d:\sql\cursor.sql. Substitute the directory where you saved the cursor.sql file for the d:\sql directory.
The output from cursor.sql is as follows:
v_id = 1, v_first_name = John, v_last_name = Smith, v_dob = 01-JAN-65, v_phone = 650-555-1212 v_id = 2, v_first_name = Cynthia, v_last_name = Stevens, v_dob = 05-MAR-68, v_phone = 650-555-1213 v_id = 3, v_first_name = Steve, v_last_name = Seymour, v_dob = 16-JUN-71, v_phone = 650-555-1214 v_id = 4, v_first_name = Gail, v_last_name = Williams, v_dob = 01-DEC-75, v_phone = 650-555-1215 v_id = 5, v_first_name = Doreen, v_last_name = Heyson, v_dob = 20-AUG-70, v_phone = 650-555-1216
2.3.7 Procedures
A procedure may be created in PL/SQL to perform a specific task, and can be invoked any number of times. This saves you from repeating the same code over and over. Procedures are created using the CREATE PROCEDURE statement. The syntax for the CREATE PROCEDURE statement is:
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN|OUT|IN OUT] data_type [, ...])] {IS|AS} declarations BEGIN body END; The syntax elements are as follows:
- procedure_name
-
The name of the procedure.
- parameter_name
-
The name of a parameter passed to the procedure. A procedure may have any number of parameters.
- IN|OUT|IN OUT
-
Indicates the mode of the parameter. The modes are as follows:
- IN
-
Used for parameters that may already contain a value when the procedure is invoked; the value may not be modified in the procedure body. INis the default parameter mode.
- OUT
-
Used for parameters whose values are initialized by the procedure body. These are output-only parameters.
- IN OUT
-
Used for parameters that may already contain a value when the procedure is invoked, but whose values may be changed in the procedure body.
- data_type
-
The data type of the parameter.
- declarations
-
The declarations for the variables used by the procedure.
- body
-
The PL/SQL statements that perform the task you created the procedure to do.
The following example, contained in the file fundamental_user_schema.sql, creates a procedure named update_product_price( ). This procedure attempts to multiply the price of a product by a factor. Both the product ID and the factor are specified as parameters to the procedure.
CREATE PROCEDURE update_product_price( p_product_id IN products.id%TYPE, p_factor IN NUMBER ) AS product_count INTEGER; BEGIN -- ensure that the product exists SELECT COUNT(*) INTO product_count FROM products WHERE id = p_product_id; -- if the product doesn't exist then return IF product_count = 0 THEN RETURN; END IF; -- if the product does exist then perform the update UPDATE products SET price = price * p_factor; -- commit the transaction COMMIT; END update_product_price; /
The procedure update_product_price( ) accepts two parameters, named p_product_id and p_factor. The IN keyword indicates that the values of the parameters cannot be modified inside the procedure. The declaration section contains a single integer variable, named product_count:
product_count INTEGER;
The main body of the procedure follows the declaration section and starts with the BEGIN keyword. The first thing the procedure does is check that the product specified by p_product_id actually exists in the products table. This is done by using the SQL function COUNT(*) to count the number of rows in the products table that have the specified ID number:
-- ensure that the product exists SELECT COUNT(*) INTO product_count FROM products WHERE id = p_product_id;
The number of rows returned by the query is stored in the product_count variable. If the value in product_count is 0, then the product was not found in the products table, and the procedure returns control to the calling program:
-- if the product doesn't exist then return IF product_count = 0 THEN RETURN; END IF;
If the procedure does not return at this point, then the product was found, and it is safe to update the row:
-- if the product does exist then perform the update UPDATE products SET price = price * p_factor;
Next, a COMMIT is used to permanently save the update in the database:
-- commit the transaction COMMIT;
Finally, the END keyword is used to mark the end of the procedure definition:
END update_product_price;
The label after the END keyword is optional, but it is good programming style to include it.
2.3.8 Functions
A function is similar to a procedure, except that a function must return a value. Functions may be created using the CREATE FUNCTION statement. The syntax for the CREATE FUNCTION statement is:
CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN|OUT|IN OUT] data_type [, ...])] RETURN data_type {IS|AS} declarations BEGIN body END; The syntax elements are as follows:
- function_name
-
The name of the function.
- parameter_name
-
The name of a parameter to the function. A function may have any number of parameters.
- IN|OUT|IN OUT
-
Indicates the mode of the parameter. The modes are as follows:
- IN
-
Used for parameters that may already contain a value when the function is invoked; the value may not be modified in the function body. INis the default parameter mode.
- OUT
-
Used for parameters whose values are initialized by the function body. These are output-only parameters.
- IN OUT
-
Used for parameters that may already contain a value when the function is invoked, but whose values may be changed in the function body.
- data_type
-
The data type of the parameter.
- declarations
-
The declarations for the variables used by the function.
- body
-
The PL/SQL statements that perform the task you created the function to do. The body must return a value of the data type specified in the RETURN clause.
The following CREATE FUNCTION statement creates a function named update_product_price_func( ). The function does the same thing as the procedure update_product_price( ) shown in the previous section, except that the function returns the value 1 if the product is not found, and 0 if the product is found.
CREATE FUNCTION update_product_price_func( p_product_id IN products.id%TYPE, p_factor IN NUMBER ) RETURN INTEGER AS product_count INTEGER; BEGIN -- ensure that the product exists SELECT COUNT(*) INTO product_count FROM products WHERE id = p_product_id; -- if the product doesn't exist then return 1 IF product_count = 0 THEN -- return 1, indicating that the function was unsuccessful RETURN 1; END IF; -- if the product does exist then perform the update UPDATE products SET price = price * p_factor; -- commit the transaction COMMIT; -- return 0, indication that the function was successful RETURN 0; END update_product_price_func; /
If the value in product_count is 0, then the product was not found and the function returns the value 1. The following IF statement implements this logic:
-- if the product doesn't exist then return IF product_count = 0 THEN -- return 1, indicating that the function was unsuccessful RETURN 1; END IF;
If the function does not return, then the product was found and it is safe to update the row:
-- if the product does exist then perform the update UPDATE Products SET price = price * p_factor;
Finally, the function returns 0, indicating that it was successful:
-- return 0, indication that the function was successful RETURN 0;
The function definition is terminated using the END keyword:
END update_product_price_func;
The label after the END keyword is optional, but it is good programming style to include it.
2.3.9 Packages
Procedures and functions may be grouped together into packages, allowing you to encapsulate related procedures, functions, and types into one self-contained unit. You, or someone else, can then potentially reuse your packages.
Packages consist of a specification and a body. A package specification lists all the procedures, functions, and variables available to the users of the package. A package body contains the actual variables, cursors, and PL/SQL code that implement the procedures and functions defined in the package specification.
Any procedures and functions contained in the body but not listed in the specification are private to the body. By hiding the complex details of your code in private procedures and functions within the body, you make it easier to reuse your package.
2.3.9.1 Creating a package specification
You create a package specification using the CREATE PACKAGE statement. The syntax for CREATE PACKAGE is:
CREATE [OR REPLACE] PACKAGE package_name {IS|AS} package_specification END; The syntax elements are as follows:
- package_name
-
The name of the package.
- package_specification
-
The list of variables, cursors, procedures, and functions that are available to the users of the package.
The following example creates a package specification for a package named ref_cursor_package:
CREATE PACKAGE ref_cursor_package AS TYPE t_ref_cursor IS REF CURSOR; FUNCTION get_customers_ref_cursor RETURN t_ref_cursor; END ref_cursor_package; /
This package defines a type named t_ref_cursor, which uses the PL/SQL REF CURSOR type. A REF CURSOR variable in PL/SQL is conceptually the same as a pointer in the C language. One use for a REF CURSOR variable is to point to the result set returned by a SELECT statement that you execute through a cursor. You'll see how that works in the next section. The package ref_cursor_package also declares a function named get_customers_ref_cursor( ) that returns a variable of type t_ref_cursor.
2.3.9.2 Creating a package body
You create a package body using the CREATE PACKAGE BODY statement. The syntax for CREATE PACKAGE BODY is:
CREATE [OR REPLACE] PACKAGE BODY package_name {IS|AS} package_body END; The syntax elements are as follows:
- package_name
-
The name of the package. This must match the package name in the package specification.
- package_body
-
The variables, cursors, procedures, and functions that implement the package.
The following example creates a package body for ref_cursor_package:
CREATE PACKAGE BODY ref_cursor_package AS -- function get_customers_ref_cursor returns a REF CURSOR -- which points to the customers result set FUNCTION get_customers_ref_cursor RETURN t_ref_cursor IS customers_ref_cursor t_ref_cursor; BEGIN -- get the REF CURSOR OPEN customers_ref_cursor FOR SELECT id, first_name, last_name, dob, phone FROM customers; -- return the REF CURSOR RETURN customers_ref_cursor; END get_customers_ref_cursor; END ref_cursor_package; /
The function get_customers_ref_cursor( ) opens a cursor and selects the id, first_name, last_name, dob, and phone columns from the customers table. The reference to the cursor is then returned by the function. You will see how to use this package in the next chapter.
