|
|
; text-indent: 12pt; font-family: "Times New Roman"; font-size: 10.5pt; -ms-text-autospace:; -ms-text-justify: inter-ideograph;'>v_counter := v_counter + 1;
END LOOP;
FOR Loops
A FOR loop runs a predetermined number of times; you determine the number of times the loop runs by specifying the lower and upper bounds for a loop variable. The loop variable is then incremented (or decremented) each time around the loop. The syntax for a FOR loop is as follows:
FOR loop_variable IN [REVERSE] lower_bound..upper_bound LOOP
statements
END LOOP;
where
l loop_variable is the loop variable. You can use a variable that already exists as the loop variable, or you can just have the loop create a new variable for you (this occurs if the variable you specify doesn’t exist). The loop variable value is increased (or decreased if you use the REVERSE keyword) by 1 each time through the loop.
l REVERSE means that the loop variable value is to be decremented each time through the loop. The loop variable is initialized to the upper boundary, and is decremented by 1 until the loop variable reaches the lower boundary. You must specify the lower boundary before the upper boundary.
l lower_bound is the loop’s lower boundary. The loop variable is initialized to this lower boundary provided REVERSE is not used.
l upper_bound is the loop’s upper boundary. If REVERSE is used, the loop variable is initialized to this upper boundary.
The following example shows a FOR loop. Notice that the variable v_counter2 isn’t explicitly declared—so the FOR loop automatically creates a new INTEGER variable named v_counter2:
FOR v_counter2 IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(v_counter2);
END LOOP;
The following example uses REVERSE:
FOR v_counter2 IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(v_counter2);
END LOOP;
In this example, v_counter2 starts at 5, is decremented by 1 each time through the loop, and ends at 1.
Cursors
You use a cursor to fetch rows returned by a query. You retrieve the rows into the cursor using a query and then fetch the rows one at a time from the cursor. You typically use the following five steps when using a cursor:
1. Declare variables to store the column values for a row.
2. Declare the cursor, which contains a query.
3. Open the cursor.
4. Fetch the rows from the cursor one at a time and store the column values in the variables declared in Step 1. You would then do something with those variables, such as display them on the screen, use them in a calculation, and so on.
5. Close the cursor.
You’ll learn the details of these five steps in the following sections, and you’ll see a simple example that gets the product_id, name, and price columns from the products table.
Step 1: Declare the Variables to Store the Column Values
The first step is to declare the variables that will be used to store the column values. These variables must be compatible with the column types.
TIP
Earlier you saw that %TYPE may be used to get the type of a column.If you use %TYPE when declaring your variables, your variables will automatically be of the correct type.
The following example declares three variables to store the product_id, name, and price columns from the products table; notice that %TYPE is used to automatically set the type of the variables to the same type as the columns:
DECLARE
v_product_id products.product_id%TYPE;
v_name products.name%TYPE;
v_price products.price%TYPE;
Step 2: Declare the Cursor
Step 2 is to declare the cursor. A cursor declaration consists of a name that you assign to the cursor and the query you want to execute. The cursor declaration, like all other declarations, is placed in the declaration section. The syntax for declaring a cursor is as follows:
CURSOR cursor_name IS
SELECT_statement;
where
l cursor_name is the name of the cursor.
l SELECT_statement is the query.
The following example declares a cursor named v_product_cursor whose query retrieves the product_id, name, and price columns from the products table:
CURSOR v_product_cursor IS
SELECT product_id, name, price
FROM products
ORDER BY product_id;
The query isn’t executed until you open the cursor.
Step 3: Open the Cursor
Step 3 is to open the cursor. You open a cursor using the OPEN statement, which must be placed in the executable section of the block.
The following example opens v_product_cursor, which executes the query:
OPEN v_product_cursor;
Step 4: Fetch the Rows from the Cursor
Step 4 is to fetch the rows from the cursor, which you do using the FETCH statement. The FETCH statement reads the column values into the variables declared in Step 1. FETCH uses the following syntax:
FETCH cursor_name
INTO variable[, variable ...];
Where
l cursor_name is the name of the cursor.
l variable is the variable into which a column value from the cursor is stored. You need to provide matching variables for each column value.
The following FETCH example retrieves a row from v_product_cursor and stores the column values in the v_product_id, v_name, and v_price variables created earlier in Step 1:
FETCH v_product_cursor
INTO v_product_id, v_name, v_price;
Because a cursor may contain many rows, you need a loop to read them. To figure out when to end the loop, you can use the Boolean variable v_product_cursor%NOTFOUND. This variable is true when there are no more rows to read in v_product_cursor. The following example shows a loop:
LOOP
-- fetch the rows from the cursor
FETCH v_product_cursor
INTO v_product_id, v_name, v_price;
-- exit the loop when there are no more rows, as indicated by
-- the Boolean variable v_product_cursor%NOTFOUND (= true when
-- there are no more rows)
EXIT WHEN v_product_cursor%NOTFOUND;
-- use DBMS_OUTPUT.PUT_LINE() to display the variables
DBMS_OUTPUT.PUT_LINE(
'v_product_id = ' || v_product_id || ', v_name = ' || v_name ||
', v_price = ' || v_price
);
END LOOP;
Notice that I’ve used DBMS_OUTPUT.PUT_LINE() to display the v_product_id, v_name, and v_price variables that were read for each row. In a real application, you might use v_price in a complex calculation.
Step 5: Close the Cursor
Step 5 is to close the cursor using the CLOSE statement. Closing a cursor frees up system resources. The following example closes v_product_cursor:
CLOSE v_product_cursor;
,From <Oracle Database 11g-SQL>
by Jason Price
Chapter 11: Introducing PL/SQL Programming
Introducing PL/SQL Programming
Oracle added a procedural programming language known as PL/SQL (Procedural Language/SQL) to Oracle Database 6. PL/SQL enables you to write programs that contain SQL statements. In this chapter, you’ll learn about the following PL/SQL
l topics:
l Block structure
l Variables and types
l Conditional logic
l Loops
l Cursors, which allow PL/SQL to read the results returned by a query
l Procedures
l Functions
l Packages, which are used to group procedures and functions together in one unit
l Triggers, which are blocks of code that are run when a certain event occurs in the
database
l Oracle Database 11g enhancements to PL/SQL
You can use PL/SQL to add business logic to a database application. This centralized business logic can be run by any program that can access the database, including SQL*Plus, Java programs,C# programs, and more.
NOTE
For full details on how to access a database through Java, see my book Oracle9i JDBC Programming (Oracle Press, 2002). For details on how to access a database through C#, see my book Mastering C# Database Programming (Sybex, 2003).
Block Structure
PL/SQL programs are divided up into structures known as blocks, with each block containing PL/SQL and SQL statements. A PL/SQL block has the following structure:
[DECLARE
declaration_statements
]
BEGIN
executable_statements
[EXCEPTION
exception_handling_statements
]
END;
/
where
l declaration_statements declare the variables used in the rest of the PL/SQL block.
DECLARE blocks are optional.
l executable_statements are the actual executable statements, which may include
loops, conditional logic, and so on.
l exception_handling_statements are statements that handle any execution errors
that might occur when the block is run. EXCEPTION blocks are optional.
Every statement is terminated by a semicolon (;), and a PL/SQL block is terminated using the forward slash (/) character. Before I get into the details of PL/SQL, you’ll see a simple example to get a feel for the language. The following example (contained in the area_example.sql script in the SQL directory) calculates the width of a rectangle given its area and height:
SET SERVEROUTPUT ON
DECLARE
v_width INTEGER;
v_height INTEGER := 2;
v_area INTEGER := 6;
BEGIN
-- set the width equal to the area divided by the height
v_width := v_area / v_height;
DBMS_OUTPUT.PUT_LINE('v_width = ' || v_width);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero');
END;
/
The SET SERVEROUTPUT ON command turns the server output on so you can see the lines produced by DBMS_OUTPUT.PUT_LINE() on the screen when you run the script in SQL*Plus. After this initial command comes the PL/SQL block itself, which is divided into the DECLARE,BEGIN, and EXCEPTION blocks.
The DECLARE block contains declarations for three INTEGER variables named v_width, v_height, and v_area (I always put v_ at the start of variable names). The v_height and v_area variables are initialized to 2 and 6 respectively.
Next comes the BEGIN block, which contains three lines. The first line is a comment thatcontains the text “set the width equal to the area divided by the height.” The second line sets v_width to v_area divided by v_height; this means v_width is set to 3 (= 6 / 2). The third line calls DBMS_OUTPUT.PUT_LINE() to display the value of v_width on the screen. DBMS_OUTPUT is a built-in package of code that comes with the Oracle database; among other items, DBMS_OUTPUT contains procedures that allow you to output values to the screen.
Next, the EXCEPTION block handles any attempts to divide a number by zero. It does this by “catching” the ZERO_DIVIDE exception; in the example, no attempt is actually made to divide by zero, but if you change v_height to 0 and run the script you’ll see the exception.
At the very end of the script, the forward slash character (/) marks the end of the PL/SQL block.
The following listing shows the execution of the area_example.sql script in SQL*Plus:
SQL> @ C:\SQL\area_example.sql
v_width = 3
NOTE
If your area_example.sql script is in a directory other than C:\SQL, use your own directory in the previous command.
Variables and Types
Variables are declared within a DECLARE block. As you saw in the previous example, a variable declaration has both a name and a type. For example, the v_width variable was declared as
v_width INTEGER;
NOTE
The PL/SQL types are similar to the database column types. You can see all the types in the appendix.
The following example shows more variable declarations (these variables could be used to store the column values from the products table):
v_product_id INTEGER;
v_product_type_id INTEGER;
v_name VARCHAR2(30);
v_description VARCHAR2(50);
v_price NUMBER(5, 2);
You may also specify a variable’s type using the %TYPE keyword, which tells PL/SQL to use the same type as a specified column in a table. The following example uses %TYPE to declare a variable of the same type as the price column of the products table, which is NUMBER(5, 2):
v_product_price products.price%TYPE;
Conditional Logic
You use the IF, THEN, ELSE, ELSIF, and END IF keywords to perform conditional logic:
IF condition1 THEN
statements1
ELSIF condition2 THEN
statements2
ELSE
statements3
END IF;
where
l condition1 and condition2 are Boolean expressions that evaluate to true or false.
l statements1, statements2, and statements3 are PL/SQL statements.
The conditional logic flows as follows:
l If condition1 is true, then statements1 are executed.
l If condition1 is false but condition2 is true, then statements2 are executed.
l If neither condition1 nor condition2 is true, then statements3 are executed.
You can also embed an IF statement within another IF statement, as shown in the following example:
IF v_count > 0 THEN
v_message := 'v_count is positive';
IF v_area > 0 THEN
v_message := 'v_count and v_area are positive';
END IF
ELSIF v_count = 0 THEN
v_message := 'v_count is zero';
ELSE
v_message := 'v_count is negative';
END IF;
In this example, if v_count is greater than 0, then v_message is set to 'v_count is positive'. If v_count and v_area are greater than 0, then v_message is set to 'v_count and v_area are positive'. The rest of the logic is straightforward.
Loops
You use a loop to run statements zero or more times. There are three types of loops in PL/SQL:
l Simple loops run until you explicitly end the loop.
l WHILE loops run until a specified condition occurs.
l FOR loops run a predetermined number of times.
You’ll learn about these loops in the following sections.
Simple Loops
A simple loop runs until you explicitly end the loop. The syntax for a simple loop is as follows:
LOOP
statements
END LOOP;
To end the loop, you use either an EXIT or an EXIT WHEN statement. The EXIT statement ends a loop immediately; the EXIT WHEN statement ends a loop when a specified condition occurs.
The following example shows a simple loop. A variable named v_counter is initialized to 0 prior to the beginning of the loop. The loop adds 1 to v_counter and exits when v_counter is equal to 5 using an EXIT WHEN statement.
v_counter := 0;
LOOP
v_counter := v_counter + 1;
EXIT WHEN v_counter = 5;
END LOOP;
NOTE
The EXIT WHEN statement can appear anywhere in the loop code.
In Oracle Database 11g you can also end the current iteration of a loop using the CONTINUE or CONTINUE WHEN statement. The CONTINUE statement ends the current iteration of the loop unconditionally and continues with the next iteration; the CONTINUE WHEN statement ends the current iteration of the loop when a specified condition occurs and then continues with the next iteration. The following example shows the use of the CONTINUE statement:
v_counter := 0;
LOOP
-- after the CONTINUE statement is executed, control returns here
v_counter := v_counter + 1;
IF v_counter = 3 THEN
CONTINUE; -- end current iteration unconditionally
END IF;
EXIT WHEN v_counter = 5;
END LOOP;
The next example shows the use of the CONTINUE WHEN statement:
v_counter := 0;
LOOP
-- after the CONTINUE WHEN statement is executed, control returns here
v_counter := v_counter + 1;
CONTINUE WHEN v_counter = 3; -- end current iteration when v_counter = 3
EXIT WHEN v_counter = 5;
END LOOP;
NOTE
A CONTINUE or CONTINUE WHEN statement cannot cross a procedure, function, or method boundary.
WHILE Loops
A WHILE loop runs until a specified condition occurs. The syntax for a WHILE loop is as follows:
WHILE condition LOOP
statements
END LOOP;
The following example shows a WHILE loop that executes while the v_counter variable is less than 6:
v_counter := 0;
WHILE v_counter < 6 LOOP
