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