PL/SQL uses cursors for all database information access statements. The language supports the use of both implicit and explicit cursors. Implicit cursors are those established for which explicit cursors are not declared. You must use explicit cursors or cursor FOR loops in all queries that return multiple rows.
Declaring Cursors
You define cursors in the variable definition area of PL/SQL subprograms using the CURSOR name IS statement, as shown in the following example:
CURSOR c_line_item IS
(sql statement)
The cursor SQL statement can be any valid query statement. Subsequent to cursor initialization, you are able to control cursor actions with the OPEN, FETCH, and CLOSE statements.
Cursor Control
To use a cursor for manipulating data, you must use the statement OPEN name to execute the query and identify all rows that meet the select criteria. Subsequent retrieval of rows is accomplished with the FETCH statement. Once all information is processed, the CLOSE statement terminates all activity associated with the opened cursor. The following is an example of cursor control:
OPEN c_line_item;
...
FETCH c_line_item
INTO li_info;
...
(retrieved row processing)
...
CLOSE c_line_item;
The code opens the cursor c_line_item and processes the fetched rows. After it retrieves and processes all the information, the cursor closes. Retrieved row processing is typically controlled by iterative loops as discussed later in the chapter.
Explicit Cursor Attributes
There are four attributes associated with PL/SQL cursors.
* %NOTFOUND
* %FOUND
* %ROWCOUNT
* %ISOPEN
All cursor attributes evaluate to TRUE, FALSE, or NULL, depending on the situation. The attribute %NOTFOUND evaluates to FALSE when a row is fetched, TRUE if the last FETCH did not return a row, and NULL if the cursor SELECT returned no data. Attribute %FOUND is the logical opposite of %NOTFOUND with respect to TRUE and FALSE but still evaluates to NULL if the cursor FETCH returns no data.
You can use %ROWCOUNT to determine how many rows have been selected at any point in the FETCH. This attribute increments upon successful selection of a row. In addition, %ROWCOUNT is at zero when the cursor first opens.
The final attribute, %ISOPEN, is either TRUE or FALSE, depending on whether the associated cursor is open. Before the cursor opens and after the cursor closes, %ISOPEN is FALSE. Otherwise, it evaluates to TRUE.
Cursor Parameters
You can specify parameters for cursors in the same way you do for subprograms. The following example illustrates the syntax for declaring parameter cursors:
CURSOR c_line_item (order_num IN NUMBER) IS
SELECT merch_gross, recipient_num
FROM line_item
WHERE order_num = g_order_num;
The parameter mode is always IN for cursor parameters, but the data type can be any valid data type. You can reference a cursor parameter, whose value is set when the cursor opens, only during the cursor's declared SQL query.
Flexibility within cursor parameters enables the developer to pass different numbers of parameters to a cursor by using the parameter default mechanism. This is illustrated in the following example:
CURSOR c_line_item
(order_num INTEGER DEFAULT 100,
line_num INTEGER DEFAULT 1) IS ...
By using the INTEGER DEFAULT declaration, you can pass all, one, or none of the parameters to this cursor depending on the logic flow of your code.
Creating Cursor Packages
A cursor package is similar to a procedure package in that you specify the cursor and its return attribute, %TYPE or %ROWTYPE, in the package specification area. You then specify the cursor "body" in the package body specification area. Packaging a cursor in this manner gives you the flexibility of changing the cursor body without having to recompile applications that reference the packaged procedure. The following is a cursor package example:
CREATE OR REPLACE PACKAGE order_total
AS
CURSOR c_line_item RETURN line_item.merch_gross%TYPE;
...
END order_total;
CREATE OR REPLACE PACKAGE BODY order_total
AS
CURSOR c_line_item RETURN line_item.merch_gross%TYPE
SELECT merch_gross
FROM line_item
WHERE order_num = g_order_num;
...
END order_total;
In this example, the RETURN variable is the same as the line_item.item_merch_gross column. You can use the %ROWTYPE attribute to specify a RETURN record that mirrors a row in a database table.
Procedure Variables
The most important feature of any language is how to define variables. Once you've defined the variables, PL/SQL enables you to use them in SQL statements as well as language statements. Definition of constants within PL/SQL follow the same rules. Also, you can define variables and constants as local to one subprogram or global to the entire package you are creating.
Declaring Cursors
You define cursors in the variable definition area of PL/SQL subprograms using the CURSOR name IS statement, as shown in the following example:
CURSOR c_line_item IS
(sql statement)
The cursor SQL statement can be any valid query statement. Subsequent to cursor initialization, you are able to control cursor actions with the OPEN, FETCH, and CLOSE statements.
Cursor Control
To use a cursor for manipulating data, you must use the statement OPEN name to execute the query and identify all rows that meet the select criteria. Subsequent retrieval of rows is accomplished with the FETCH statement. Once all information is processed, the CLOSE statement terminates all activity associated with the opened cursor. The following is an example of cursor control:
OPEN c_line_item;
...
FETCH c_line_item
INTO li_info;
...
(retrieved row processing)
...
CLOSE c_line_item;
The code opens the cursor c_line_item and processes the fetched rows. After it retrieves and processes all the information, the cursor closes. Retrieved row processing is typically controlled by iterative loops as discussed later in the chapter.
Explicit Cursor Attributes
There are four attributes associated with PL/SQL cursors.
* %NOTFOUND
* %FOUND
* %ROWCOUNT
* %ISOPEN
All cursor attributes evaluate to TRUE, FALSE, or NULL, depending on the situation. The attribute %NOTFOUND evaluates to FALSE when a row is fetched, TRUE if the last FETCH did not return a row, and NULL if the cursor SELECT returned no data. Attribute %FOUND is the logical opposite of %NOTFOUND with respect to TRUE and FALSE but still evaluates to NULL if the cursor FETCH returns no data.
You can use %ROWCOUNT to determine how many rows have been selected at any point in the FETCH. This attribute increments upon successful selection of a row. In addition, %ROWCOUNT is at zero when the cursor first opens.
The final attribute, %ISOPEN, is either TRUE or FALSE, depending on whether the associated cursor is open. Before the cursor opens and after the cursor closes, %ISOPEN is FALSE. Otherwise, it evaluates to TRUE.
Cursor Parameters
You can specify parameters for cursors in the same way you do for subprograms. The following example illustrates the syntax for declaring parameter cursors:
CURSOR c_line_item (order_num IN NUMBER) IS
SELECT merch_gross, recipient_num
FROM line_item
WHERE order_num = g_order_num;
The parameter mode is always IN for cursor parameters, but the data type can be any valid data type. You can reference a cursor parameter, whose value is set when the cursor opens, only during the cursor's declared SQL query.
Flexibility within cursor parameters enables the developer to pass different numbers of parameters to a cursor by using the parameter default mechanism. This is illustrated in the following example:
CURSOR c_line_item
(order_num INTEGER DEFAULT 100,
line_num INTEGER DEFAULT 1) IS ...
By using the INTEGER DEFAULT declaration, you can pass all, one, or none of the parameters to this cursor depending on the logic flow of your code.
Creating Cursor Packages
A cursor package is similar to a procedure package in that you specify the cursor and its return attribute, %TYPE or %ROWTYPE, in the package specification area. You then specify the cursor "body" in the package body specification area. Packaging a cursor in this manner gives you the flexibility of changing the cursor body without having to recompile applications that reference the packaged procedure. The following is a cursor package example:
CREATE OR REPLACE PACKAGE order_total
AS
CURSOR c_line_item RETURN line_item.merch_gross%TYPE;
...
END order_total;
CREATE OR REPLACE PACKAGE BODY order_total
AS
CURSOR c_line_item RETURN line_item.merch_gross%TYPE
SELECT merch_gross
FROM line_item
WHERE order_num = g_order_num;
...
END order_total;
In this example, the RETURN variable is the same as the line_item.item_merch_gross column. You can use the %ROWTYPE attribute to specify a RETURN record that mirrors a row in a database table.
Procedure Variables
The most important feature of any language is how to define variables. Once you've defined the variables, PL/SQL enables you to use them in SQL statements as well as language statements. Definition of constants within PL/SQL follow the same rules. Also, you can define variables and constants as local to one subprogram or global to the entire package you are creating.
