Homework Week 4 Pl/Sql Cursor

The statement executes a sequence of statements depending on the value of a condition. There are three forms of statements: , , and . For a description of the syntax of the statement, see "IF Statement".

The statement is a compact way to evaluate a single condition and choose between many alternative actions. It makes sense to use when there are three or more alternatives to choose from. For a description of the syntax of the statement, see "CASE Statement".

Using the IF-THEN Statement

The simplest form of statement associates a condition with a sequence of statements enclosed by the keywords and (not ) as illustrated in Example 4-1.

The sequence of statements is executed only if the condition is . If the condition is or , the statement does nothing. In either case, control passes to the next statement.

Example 4-1 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; BEGIN IF sales > (quota + 200) THEN bonus := (sales - quota)/4; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END IF; END; /

Using the IF-THEN-ELSE Statement

The second form of statement adds the keyword followed by an alternative sequence of statements, as shown in Example 4-2.

The statements in the clause are executed only if the condition is or . The statement ensures that one or the other sequence of statements is executed. In the Example 4-2, the first statement is executed when the condition is , and the second statement is executed when the condition is or .

Example 4-2 Using a Simple IF-THEN-ELSE Statement

DECLARE sales NUMBER(8,2) := 12100; quota NUMBER(8,2) := 10000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120; BEGIN IF sales > (quota + 200) THEN bonus := (sales - quota)/4; ELSE bonus := 50; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END; /

statements can be nested as shown in Example 4-3.

Example 4-3 Nested IF Statements

DECLARE sales NUMBER(8,2) := 12100; quota NUMBER(8,2) := 10000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120; BEGIN IF sales > (quota + 200) THEN bonus := (sales - quota)/4; ELSE IF sales > quota THEN bonus := 50; ELSE bonus := 0; END IF; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END; /

Using the IF-THEN-ELSIF Statement

Sometimes you want to choose between several alternatives. You can use the keyword (not or ) to introduce additional conditions, as shown in Example 4-4.

If the first condition is or , the clause tests another condition. An statement can have any number of clauses; the final clause is optional. Conditions are evaluated one by one from top to bottom. If any condition is , its associated sequence of statements is executed and control passes to the next statement. If all conditions are false or , the sequence in the clause is executed, as shown in Example 4-4.

Example 4-4 Using the IF-THEN-ELSEIF Statement

DECLARE sales NUMBER(8,2) := 20000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120; BEGIN IF sales > 50000 THEN bonus := 1500; ELSIF sales > 35000 THEN bonus := 500; ELSE bonus := 100; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END; /

If the value of is larger than 50000, the first and second conditions are . Nevertheless, is assigned the proper value of 1500 because the second condition is never tested. When the first condition is , its associated statement is executed and control passes to the statement.

Another example of an -- statement is Example 4-5.

Example 4-5 Extended IF-THEN Statement

DECLARE grade CHAR(1); BEGIN grade := 'B'; IF grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); ELSIF grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); ELSIF grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); ELSIF grade = 'D' THEN DBMS_OUTPUT. PUT_LINE('Fair'); ELSIF grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END IF; ENd; /

Using CASE Statements

Like the statement, the statement selects one sequence of statements to execute. However, to select the sequence, the statement uses a selector rather than multiple Boolean expressions. A selector is an expression whose value is used to select one of several alternatives.

To compare the and statements, consider the code in Example 4-5 that outputs descriptions of school grades. Note the five Boolean expressions. In each instance, we test whether the same variable, , is equal to one of five values: , , , , or . You can rewrite the code inExample 4-5 using the statement, as shown in Example 4-6.

Example 4-6 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; /

The statement is more readable and more efficient. When possible, rewrite lengthy statements as statements.

The statement begins with the keyword . The keyword is followed by a selector, which is the variable in the last example. The selector expression can be arbitrarily complex. For example, it can contain function calls. Usually, however, it consists of a single variable. The selector expression is evaluated only once. The value it yields can have any PL/SQL datatype other than , , an object type, a PL/SQL record, an index-by-table, a varray, or a nested table.

The selector is followed by one or more clauses, which are checked sequentially. The value of the selector determines which clause is executed. If the value of the selector equals the value of a -clause expression, that clause is executed. For instance, in the last example, if equals , the program outputs . Execution never falls through; if any clause is executed, control passes to the next statement.

The clause works similarly to the clause in an statement. In the last example, if the grade is not one of the choices covered by a clause, the clause is selected, and the phrase is output. The clause is optional. However, if you omit the clause, PL/SQL adds the following implicit clause:

There is always a default action, even when you omit the clause. If the statement does not match any of the clauses and you omit the clause, PL/SQL raises the predefined exception .

The keywords terminate the statement. These two keywords must be separated by a space. The statement has the following form:

Like PL/SQL blocks, statements can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the statement. Optionally, the label name can also appear at the end of the statement.

Exceptions raised during the execution of a statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.

An alternative to the statement is the expression, where each clause is an expression. For details, see "CASE Expressions".

Searched CASE Statement

PL/SQL also provides a searched statement, similar to the simple statement, which has the form shown in Example 4-7.

The searched statement has no selector. Also, its clauses contain search conditions that yield a Boolean value, not expressions that can yield a value of any type. as shown in Example 4-7.

Example 4-7 Using the Searched CASE Statement

DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END CASE; END; -- rather than using the ELSE in the CASE, could use the following -- EXCEPTION -- WHEN CASE_NOT_FOUND THEN -- DBMS_OUTPUT.PUT_LINE('No such grade'); /

The search conditions are evaluated sequentially. The Boolean value of each search condition determines which clause is executed. If a search condition yields , its clause is executed. If any clause is executed, control passes to the next statement, so subsequent search conditions are not evaluated.

If none of the search conditions yields , the clause is executed. The clause is optional. However, if you omit the clause, PL/SQL adds the following implicit clause:

Exceptions raised during the execution of a searched statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.

Guidelines for PL/SQL Conditional Statements

Avoid clumsy statements like those in the following example:











The value of a Boolean expression can be assigned directly to a Boolean variable. You can replace the first statement with a simple assignment:

A Boolean variable is itself either true or false. You can simplify the condition in the second statement:

When possible, use the clause instead of nested statements. Your code will be easier to read and understand. Compare the following statements:












These statements are logically equivalent, but the second statement makes the logic clearer.

To compare a single expression to multiple values, you can simplify the logic by using a single statement instead of an with several clauses.

Static SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement. This chapter describes static SQL and explains how to use it.

Description of Static SQL

Static SQL has the same syntax as SQL, except as noted.

Topics

Statements

These are the PL/SQL static SQL statements, which have the same syntax as the corresponding SQL statements, except as noted:

  • (this statement is also called a query)

    For the PL/SQL syntax, see "SELECT INTO Statement".

  • Data manipulation language (DML) statements:

  • Transaction control language (TCL) statements:

  • (for syntax, see Oracle Database SQL Language Reference)

A PL/SQL static SQL statement can have a PL/SQL identifier wherever its SQL counterpart can have a placeholder for a bind variable. The PL/SQL identifier must identify either a variable or a formal parameter.

In Example 6-1, a PL/SQL anonymous block declares three PL/SQL variables and uses them in the static SQL statements , , . The block also uses the static SQL statement .

Example 6-1 Static SQL Statements

DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name FROM employees; DECLARE emp_id employees_temp.employee_id%TYPE := 299; emp_first_name employees_temp.first_name%TYPE := 'Bob'; emp_last_name employees_temp.last_name%TYPE := 'Henry'; BEGIN INSERT INTO employees_temp (employee_id, first_name, last_name) VALUES (emp_id, emp_first_name, emp_last_name); UPDATE employees_temp SET first_name = 'Robert' WHERE employee_id = emp_id; DELETE FROM employees_temp WHERE employee_id = emp_id RETURNING first_name, last_name INTO emp_first_name, emp_last_name; COMMIT; DBMS_OUTPUT.PUT_LINE (emp_first_name || ' ' || emp_last_name); END; /

Result:

Robert Henry

To use PL/SQL identifiers for table names, column names, and so on, use the statement, explained in "Native Dynamic SQL".

Note:

After PL/SQL code runs a DML statement, the values of some variables are undefined. For example:
  • After a or statement raises an exception, the values of the define variables after that statement are undefined.

  • After a DML statement that affects zero rows, the values of the bind variables are undefined, unless the DML statement is a or multiple-row operation.

Pseudocolumns

A pseudocolumn behaves like a table column, but it is not stored in the table. For general information about pseudocolumns, including restrictions, see Oracle Database SQL Language Reference.

Static SQL includes these SQL pseudocolumns:

CURRVAL and NEXTVAL in PL/SQL

After a sequence is created, you can access its values in SQL statements with the pseudocolumn, which returns the current value of the sequence, or the pseudocolumn, which increments the sequence and returns the new value. (For general information about sequences, see Oracle Database SQL Language Reference.)

To reference these pseudocolumns, use dot notation—for example, .. For complete syntax, see Oracle Database SQL Language Reference.

Note:

Each time you reference ., the sequence is incremented immediately and permanently, whether you commit or roll back the transaction.

As of Oracle Database 11g Release 1, you can use . and . in a PL/SQL expression wherever you can use a expression. However:

  • Using . or . to provide a default value for an ADT method parameter causes a compilation error.

  • PL/SQL evaluates every occurrence of . and . (unlike SQL, which evaluates a sequence expression for every row in which it appears).

Example 6-2 generates a sequence number for the sequence and refers to that number in multiple statements.

Example 6-2 CURRVAL and NEXTVAL Pseudocolumns

DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name FROM employees; DROP TABLE employees_temp2; CREATE TABLE employees_temp2 AS SELECT employee_id, first_name, last_name FROM employees; DECLARE seq_value NUMBER; BEGIN -- Generate initial sequence number seq_value := employees_seq.NEXTVAL; -- Print initial sequence number: DBMS_OUTPUT.PUT_LINE ( 'Initial sequence value: ' || TO_CHAR(seq_value) ); -- Use NEXTVAL to create unique number when inserting data: INSERT INTO employees_temp (employee_id, first_name, last_name) VALUES (employees_seq.NEXTVAL, 'Lynette', 'Smith'); -- Use CURRVAL to store same value somewhere else: INSERT INTO employees_temp2 VALUES (employees_seq.CURRVAL, 'Morgan', 'Smith'); /* Because NEXTVAL values might be referenced by different users and applications, and some NEXTVAL values might not be stored in database, there might be gaps in sequence. */ -- Use CURRVAL to specify record to delete: seq_value := employees_seq.CURRVAL; DELETE FROM employees_temp2 WHERE employee_id = seq_value; -- Update employee_id with NEXTVAL for specified record: UPDATE employees_temp SET employee_id = employees_seq.NEXTVAL WHERE first_name = 'Lynette' AND last_name = 'Smith'; -- Display final value of CURRVAL: seq_value := employees_seq.CURRVAL; DBMS_OUTPUT.PUT_LINE ( 'Ending sequence value: ' || TO_CHAR(seq_value) ); END; /

Cursors

A cursor is a pointer to a private SQL area that stores information about processing a specific or DML statement.

The cursors that this chapter explains are session cursors. A session cursor lives in session memory until the session ends, when it ceases to exist.

A session cursor that is constructed and managed by PL/SQL is an implicit cursor. A session cursor that you construct and manage is an explicit cursor.

You can get information about any session cursor from its attributes (which you can reference in procedural statements, but not in SQL statements).

To list the session cursors that each user session currently has opened and parsed, query the dynamic performance view , explained in Oracle Database Reference.

Note:

Generally, PL/SQL parses an explicit cursor only the first time the session opens it and parses a SQL statement (creating an implicit cursor) only the first time the statement runs.

All parsed SQL statements are cached. A SQL statement is reparsed only if it is aged out of the cache by a new SQL statement. Although you must close an explicit cursor before you can reopen it, PL/SQL need not reparse the associated query. If you close and immediately reopen an explicit cursor, PL/SQL does not reparse the associated query.

Topics

Implicit Cursors

An implicit cursor is a session cursor that is constructed and managed by PL/SQL. PL/SQL opens an implicit cursor every time you run a or DML statement. You cannot control an implicit cursor, but you can get information from its attributes.

The syntax of an implicit cursor attribute value is (therefore, an implicit cursor is also called a SQL cursor). always refers to the most recently run or DML statement. If no such statement has run, the value of is .

An implicit cursor closes after its associated statement runs; however, its attribute values remain available until another or DML statement runs.

The most recently run or DML statement might be in a different scope. To save an attribute value for later use, assign it to a local variable immediately. Otherwise, other operations, such as subprogram invocations, might change the value of the attribute before you can test it.

The implicit cursor attributes are:

SQL%ISOPEN Attribute: Is the Cursor Open?

always returns , because an implicit cursor always closes after its associated statement runs.

SQL%FOUND Attribute: Were Any Rows Affected?

returns:

  • if no or DML statement has run

  • if a statement returned one or more rows or a DML statement affected one or more rows

  • otherwise

Example 6-3 uses to determine if a statement affected any rows.

Example 6-3 SQL%FOUND Implicit Cursor Attribute

DROP TABLE dept_temp; CREATE TABLE dept_temp AS SELECT * FROM departments; CREATE OR REPLACE PROCEDURE p ( dept_no NUMBER ) AUTHID DEFINER AS BEGIN DELETE FROM dept_temp WHERE department_id = dept_no; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE ( 'Delete succeeded for department number ' || dept_no ); ELSE DBMS_OUTPUT.PUT_LINE ('No department number ' || dept_no); END IF; END; / BEGIN p(270); p(400); END; /

Result:

Delete succeeded for department number 270 No department number 400

SQL%NOTFOUND Attribute: Were No Rows Affected?

(the logical opposite of ) returns:

  • if no or DML statement has run

  • if a statement returned one or more rows or a DML statement affected one or more rows

  • otherwise

The attribute is not useful with the PL/SQL statement, because:

  • If the statement returns no rows, PL/SQL raises the predefined exception immediately, before you can check .

  • A statement that invokes a SQL aggregate function always returns a value (possibly ). After such a statement, the attribute is always , so checking it is unnecessary.

SQL%ROWCOUNT Attribute: How Many Rows Were Affected?

returns:

  • if no or DML statement has run

  • Otherwise, the number of rows returned by a statement or affected by a DML statement (a )

Example 6-4 uses to determine the number of rows that were deleted.

Example 6-4 SQL%ROWCOUNT Implicit Cursor Attribute

DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT * FROM employees; DECLARE mgr_no NUMBER(6) := 122; BEGIN DELETE FROM employees_temp WHERE manager_id = mgr_no; DBMS_OUTPUT.PUT_LINE ('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT)); END; /

Result:

Number of employees deleted: 8

If a statement without a clause returns multiple rows, PL/SQL raises the predefined exception and returns 1, not the actual number of rows that satisfy the query.

The value of attribute is unrelated to the state of a transaction. Therefore:

  • When a transaction rolls back to a savepoint, the value of is not restored to the value it had before the savepoint.

  • When an autonomous transaction ends, is not restored to the original value in the parent transaction.

Explicit Cursors

An explicit cursor is a session cursor that you construct and manage. You must declare and define an explicit cursor, giving it a name and associating it with a query (typically, the query returns multiple rows). Then you can process the query result set in either of these ways:

You cannot assign a value to an explicit cursor, use it in an expression, or use it as a formal subprogram parameter or host variable. You can do those things with a cursor variable (see "Cursor Variables").

Unlike an implicit cursor, you can reference an explicit cursor or cursor variable by its name. Therefore, an explicit cursor or cursor variable is called a named cursor.

Topics

Declaring and Defining Explicit Cursors

You can either declare an explicit cursor first and then define it later in the same block, subprogram, or package, or declare and define it at the same time.

An explicit cursor declaration, which only declares a cursor, has this syntax:

CURSOR cursor_name [ parameter_list ] RETURN return_type;

An explicit cursor definition has this syntax:

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ] IS select_statement;

If you declared the cursor earlier, then the explicit cursor definition defines it; otherwise, it both declares and defines it.

Example 6-5 declares and defines three explicit cursors.

Example 6-5 Explicit Cursor Declaration and Definition

DECLARE CURSOR c1 RETURN departments%ROWTYPE; -- Declare c1 CURSOR c2 IS -- Declare and define c2 SELECT employee_id, job_id, salary FROM employees WHERE salary > 2000; CURSOR c1 RETURN departments%ROWTYPE IS -- Define c1, SELECT * FROM departments -- repeating return type WHERE department_id = 110; CURSOR c3 RETURN locations%ROWTYPE; -- Declare c3 CURSOR c3 IS -- Define c3, SELECT * FROM locations -- omitting return type WHERE country_id = 'JP'; BEGIN NULL; END; /

Opening and Closing Explicit Cursors

After declaring and defining an explicit cursor, you can open it with the statement, which does the following:

  1. Allocates database resources to process the query

  2. Processes the query; that is:

    1. Identifies the result set

      If the query references variables or cursor parameters, their values affect the result set. For details, see "Variables in Explicit Cursor Queries" and "Explicit Cursors that Accept Parameters".

    2. If the query has a clause, locks the rows of the result set

      For details, see "SELECT FOR UPDATE and FOR UPDATE Cursors".

  3. Positions the cursor before the first row of the result set

You close an open explicit cursor with the statement, thereby allowing its resources to be reused. After closing a cursor, you cannot fetch records from its result set or reference its attributes. If you try, PL/SQL raises the predefined exception .

You can reopen a closed cursor. You must close an explicit cursor before you try to reopen it. Otherwise, PL/SQL raises the predefined exception .

Fetching Data with Explicit Cursors

After opening an explicit cursor, you can fetch the rows of the query result set with the statement. The basic syntax of a statement that returns one row is:

FETCH cursor_name INTO into_clause

The is either a list of variables or a single record variable. For each column that the query returns, the variable list or record must have a corresponding type-compatible variable or field. The and attributes are useful for declaring variables and records for use in statements.

The statement retrieves the current row of the result set, stores the column values of that row into the variables or record, and advances the cursor to the next row.

Typically, you use the statement inside a statement, which you exit when the statement runs out of rows. To detect this exit condition, use the cursor attribute (described in "%NOTFOUND Attribute: Has No Row Been Fetched?"). PL/SQL does not raise an exception when a statement returns no rows.

Example 6-6 fetches the result sets of two explicit cursors one row at a time, using and inside statements. The first statement retrieves column values into variables. The second statement retrieves column values into a record. The variables and record are declared with and , respectively.

Example 6-6 FETCH Statements Inside LOOP Statements

DECLARE CURSOR c1 IS SELECT last_name, job_id FROM employees WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK') ORDER BY last_name; v_lastname employees.last_name%TYPE; -- variable for last_name v_jobid employees.job_id%TYPE; -- variable for job_id CURSOR c2 IS SELECT * FROM employees WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]') ORDER BY job_id; v_employees employees%ROWTYPE; -- record variable for row of table BEGIN OPEN c1; LOOP -- Fetches 2 columns into variables FETCH c1 INTO v_lastname, v_jobid;EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid ); END LOOP; CLOSE c1; DBMS_OUTPUT.PUT_LINE( '-------------------------------------' ); OPEN c2; LOOP -- Fetches entire row into the v_employees record FETCH c2 INTO v_employees;EXIT WHEN c2%NOTFOUND; DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') || v_employees.job_id ); END LOOP; CLOSE c2; END; /

Result:

Atkinson ST_CLERK Bell SH_CLERK Bissot ST_CLERK ... Walsh SH_CLERK ------------------------------------- Higgins AC_MGR Greenberg FI_MGR Hartstein MK_MAN ... Zlotkey SA_MAN

Example 6-7 fetches the first five rows of a result set into five records, using five statements, each of which fetches into a different record variable. The record variables are declared with .

Example 6-7 Fetching Same Explicit Cursor into Different Variables

DECLARE CURSOR c IS SELECT e.job_id, j.job_title FROM employees e, jobs j WHERE e.job_id = j.job_id AND e.manager_id = 100 ORDER BY last_name; -- Record variables for rows of cursor result set: job1 c%ROWTYPE; job2 c%ROWTYPE; job3 c%ROWTYPE; job4 c%ROWTYPE; job5 c%ROWTYPE; BEGIN OPEN c; FETCH c INTO job1; -- fetches first row FETCH c INTO job2; -- fetches second row FETCH c INTO job3; -- fetches third row FETCH c INTO job4; -- fetches fourth row FETCH c INTO job5; -- fetches fifth row CLOSE c; DBMS_OUTPUT.PUT_LINE(job1.job_title || ' (' || job1.job_id || ')'); DBMS_OUTPUT.PUT_LINE(job2.job_title || ' (' || job2.job_id || ')'); DBMS_OUTPUT.PUT_LINE(job3.job_title || ' (' || job3.job_id || ')'); DBMS_OUTPUT.PUT_LINE(job4.job_title || ' (' || job4.job_id || ')'); DBMS_OUTPUT.PUT_LINE(job5.job_title || ' (' || job5.job_id || ')'); END; /

Result:

Sales Manager (SA_MAN) Administration Vice President (AD_VP) Sales Manager (SA_MAN) Stock Manager (ST_MAN) Marketing Manager (MK_MAN) PL/SQL procedure successfully completed.

Variables in Explicit Cursor Queries

An explicit cursor query can reference any variable in its scope. When you open an explicit cursor, PL/SQL evaluates any variables in the query and uses those values when identifying the result set. Changing the values of the variables later does not change the result set.

In Example 6-8, the explicit cursor query references the variable . When the cursor opens, has the value 2. Therefore, is always 2 times , despite that is incremented after every fetch.

Example 6-8 Variable in Explicit Cursor Query—No Result Set Change

DECLARE sal employees.salary%TYPE; sal_multiple employees.salary%TYPE; factor INTEGER := 2; CURSOR c1 IS SELECT salary, salary*factor FROM employees WHERE job_id LIKE 'AD_%'; BEGIN OPEN c1; -- PL/SQL evaluates factor LOOP FETCH c1 INTO sal, sal_multiple; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE('factor = ' || factor); DBMS_OUTPUT.PUT_LINE('sal = ' || sal); DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple); factor := factor + 1; -- Does not affect sal_multiple END LOOP; CLOSE c1; END; /

Result:

factor = 2 sal = 4451 sal_multiple = 8902 factor = 3 sal = 26460 sal_multiple = 52920 factor = 4 sal = 18742.5 sal_multiple = 37485 factor = 5 sal = 18742.5 sal_multiple = 37485

To change the result set, you must close the cursor, change the value of the variable, and then open the cursor again, as in Example 6-9.

Example 6-9 Variable in Explicit Cursor Query—Result Set Change

DECLARE sal employees.salary%TYPE; sal_multiple employees.salary%TYPE; factor INTEGER := 2; CURSOR c1 IS SELECT salary, salary*factor FROM employees WHERE job_id LIKE 'AD_%'; BEGIN DBMS_OUTPUT.PUT_LINE('factor = ' || factor); OPEN c1; -- PL/SQL evaluates factor LOOP FETCH c1 INTO sal, sal_multiple; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE('sal = ' || sal); DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple); END LOOP; CLOSE c1;factor := factor + 1; DBMS_OUTPUT.PUT_LINE('factor = ' || factor); OPEN c1; -- PL/SQL evaluates factor LOOP FETCH c1 INTO sal, sal_multiple; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE('sal = ' || sal); DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple); END LOOP; CLOSE c1; END; /

Result:

factor = 2 sal = 4451 sal_multiple = 8902 sal = 26460 sal_multiple = 52920 sal = 18742.5 sal_multiple = 37485 sal = 18742.5 sal_multiple = 37485 factor = 3 sal = 4451 sal_multiple = 13353 sal = 26460 sal_multiple = 79380 sal = 18742.5 sal_multiple = 56227.5 sal = 18742.5 sal_multiple = 56227.5

When Explicit Cursor Queries Need Column Aliases

When an explicit cursor query includes a virtual column (an expression), that column must have an alias if either of the following is true:

  • You use the cursor to fetch into a record that was declared with .

  • You want to reference the virtual column in your program.

In Example 6-10, the virtual column in the explicit cursor needs an alias for both of the preceding reasons.

Example 6-10 Explicit Cursor with Virtual Column that Needs Alias

DECLARE CURSOR c1 IS SELECT employee_id, (salary * .05) raise FROM employees WHERE job_id LIKE '%_MAN' ORDER BY employee_id; emp_rec c1%ROWTYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE ( 'Raise for employee #' || emp_rec.employee_id || ' is $' || emp_rec.raise ); END LOOP; CLOSE c1; END; /

Result:

Raise for employee #114 is $550 Raise for employee #120 is $533.61 Raise for employee #121 is $520.905 Raise for employee #122 is $501.8475 Raise for employee #123 is $412.9125 Raise for employee #124 is $368.445 Raise for employee #145 is $700 Raise for employee #146 is $675 Raise for employee #147 is $600 Raise for employee #148 is $550 Raise for employee #149 is $525 Raise for employee #201 is $650

Explicit Cursors that Accept Parameters

You can create an explicit cursor that has formal parameters, and then pass different actual parameters to the cursor each time you open it. In the cursor query, you can use a formal cursor parameter anywhere that you can use a constant. Outside the cursor query, you cannot reference formal cursor parameters.

Tip:

To avoid confusion, use different names for formal and actual cursor parameters.

Example 6-11 creates an explicit cursor whose two formal parameters represent a job and its maximum salary. When opened with a specified job and maximum salary, the cursor query selects the employees with that job who are overpaid (for each such employee, the query selects the first and last name and amount overpaid). Next, the example creates a procedure that prints the cursor query result set (for information about procedures, see Chapter 8, "PL/SQL Subprograms"). Finally, the example opens the cursor with one set of actual parameters, prints the result set, closes the cursor, opens the cursor with different actual parameters, prints the result set, and closes the cursor.

Example 6-11 Explicit Cursor that Accepts Parameters

DECLARE CURSOR c (job VARCHAR2, max_sal NUMBER) IS SELECT last_name, first_name, (salary - max_sal) overpayment FROM employees WHERE job_id = job AND salary > max_sal ORDER BY salary; PROCEDURE print_overpaid IS last_name_ employees.last_name%TYPE; first_name_ employees.first_name%TYPE; overpayment_ employees.salary%TYPE; BEGIN LOOP FETCH c INTO last_name_, first_name_, overpayment_; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ || ' (by ' || overpayment_ || ')'); END LOOP; END print_overpaid; BEGIN DBMS_OUTPUT.PUT_LINE('----------------------'); DBMS_OUTPUT.PUT_LINE('Overpaid Stock Clerks:'); DBMS_OUTPUT.PUT_LINE('----------------------'); OPEN c('ST_CLERK', 5000); print_overpaid; CLOSE c; DBMS_OUTPUT.PUT_LINE('-------------------------------'); DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:'); DBMS_OUTPUT.PUT_LINE('-------------------------------'); OPEN c('SA_REP', 10000); print_overpaid; CLOSE c; END; /

Result:

---------------------- Overpaid Stock Clerks: ---------------------- Davies, Curtis (by 15.3) Nayer, Julia (by 177.08) Stiles, Stephen (by 177.08) Bissot, Laura (by 338.87) Mallin, Jason (by 338.87) Rajs, Trenna (by 662.43) Ladwig, Renske (by 824.21) ------------------------------- Overpaid Sales Representatives: ------------------------------- Fox, Tayler (by 80) Tucker, Peter (by 500) King, Janette (by 500) Bloom, Harrison (by 500) Vishney, Clara (by 1025) Abel, Ellen (by 1550) Ozer, Lisa (by 2075) PL/SQL procedure successfully completed.

Topics

Formal Cursor Parameters with Default Values

When you create an explicit cursor with formal parameters, you can specify default values for them. When a formal parameter has a default value, its corresponding actual parameter is optional. If you open the cursor without specifying the actual parameter, then the formal parameter has its default value.

Example 6-12 creates an explicit cursor whose formal parameter represents a location ID. The default value of the parameter is the location ID of company headquarters.

Example 6-12 Cursor Parameters with Default Values

DECLARE CURSOR c (location NUMBER DEFAULT 1700) IS SELECT d.department_name, e.last_name manager, l.city FROM departments d, employees e, locations l WHERE l.location_id = location AND l.location_id = d.location_id AND d.department_id = e.department_id ORDER BY d.department_id; PROCEDURE print_depts IS dept_name departments.department_name%TYPE; mgr_name employees.last_name%TYPE; city_name locations.city%TYPE; BEGIN LOOP FETCH c INTO dept_name, mgr_name, city_name; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE(dept_name || ' (Manager: ' || mgr_name || ')'); END LOOP; END print_depts; BEGIN DBMS_OUTPUT.PUT_LINE('DEPARTMENTS AT HEADQUARTERS:'); DBMS_OUTPUT.PUT_LINE('--------------------------------'); OPEN c; print_depts; DBMS_OUTPUT.PUT_LINE('--------------------------------'); CLOSE c; DBMS_OUTPUT.PUT_LINE('DEPARTMENTS IN CANADA:'); DBMS_OUTPUT.PUT_LINE('--------------------------------'); OPEN c(1800); -- Toronto print_depts; CLOSE c; OPEN c(1900); -- Whitehorse print_depts; CLOSE c; END; /

Result:

DEPARTMENTS AT HEADQUARTERS: -------------------------------- Administration (Manager: Whalen) Purchasing (Manager: Colmenares) Purchasing (Manager: Baida) Purchasing (Manager: Himuro) Purchasing (Manager: Raphaely) Purchasing (Manager: Khoo) Purchasing (Manager: Tobias) Executive (Manager: Kochhar) Executive (Manager: De Haan) Executive (Manager: King) Finance (Manager: Popp) Finance (Manager: Greenberg) Finance (Manager: Faviet) Finance (Manager: Chen) Finance (Manager: Urman) Finance (Manager: Sciarra) Accounting (Manager: Gietz) Accounting (Manager: Higgins) -------------------------------- DEPARTMENTS IN CANADA: -------------------------------- Marketing (Manager: Hartstein) Marketing (Manager: Fay) PL/SQL procedure successfully completed.
Adding Formal Cursor Parameters with Default Values

If you add formal parameters to a cursor, and you specify default values for the added parameters, then you need not change existing references to the cursor. Compare Example 6-13 to Example 6-11.

Example 6-13 Adding Formal Parameter to Existing Cursor

DECLARE CURSOR c (job VARCHAR2, max_sal NUMBER, hired DATE DEFAULT '31-DEC-99') IS SELECT last_name, first_name, (salary - max_sal) overpayment FROM employees WHERE job_id = job AND salary > max_sal AND hire_date > hired ORDER BY salary; PROCEDURE print_overpaid IS last_name_ employees.last_name%TYPE; first_name_ employees.first_name%TYPE; overpayment_ employees.salary%TYPE; BEGIN LOOP FETCH c INTO last_name_, first_name_, overpayment_; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ || ' (by ' || overpayment_ || ')'); END LOOP; END print_overpaid; BEGIN DBMS_OUTPUT.PUT_LINE('-------------------------------'); DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:'); DBMS_OUTPUT.PUT_LINE('-------------------------------'); OPEN c('SA_REP', 10000); -- existing reference print_overpaid; CLOSE c; DBMS_OUTPUT.PUT_LINE('------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives Hired After 2004:'); DBMS_OUTPUT.PUT_LINE('------------------------------------------------'); OPEN c('SA_REP', 10000, '31-DEC-04'); -- new reference print_overpaid; CLOSE c; END; /

Result:

------------------------------- Overpaid Sales Representatives: ------------------------------- Fox, Tayler (by 80) Tucker, Peter (by 500) King, Janette (by 500) Bloom, Harrison (by 500) Vishney, Clara (by 1025) Abel, Ellen (by 1550) Ozer, Lisa (by 2075) ------------------------------------------------ Overpaid Sales Representatives Hired After 2004: ------------------------------------------------ Fox, Tayler (by 80) Tucker, Peter (by 500) Bloom, Harrison (by 500) Vishney, Clara (by 1025) Ozer, Lisa (by 2075) PL/SQL procedure successfully completed.

Explicit Cursor Attributes

The syntax for the value of an explicit cursor attribute is immediately followed by (for example, ).

Note:

Explicit cursors and cursor variables (named cursors) have the same attributes. This topic applies to all named cursors except where noted.

The explicit cursor attributes are:

If an explicit cursor is not open, referencing any attribute except raises the predefined exception .

See Also:

"Named Cursor Attribute" for complete syntax and semantics of named cursor (explicit cursor and cursor variable) attributes
%ISOPEN Attribute: Is the Cursor Open?

returns if its explicit cursor is open; otherwise.

is useful for:

  • Checking that an explicit cursor is not already open before you try to open it.

    If you try to open an explicit cursor that is already open, PL/SQL raises the predefined exception . You must close an explicit cursor before you can reopen it.

    Note:

    The preceding paragraph does not apply to cursor variables.
  • Checking that an explicit cursor is open before you try to close it.

Example 6-14 opens the explicit cursor only if it is not open and closes it only if it is open.

Example 6-14 %ISOPEN Explicit Cursor Attribute

DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; the_name employees.last_name%TYPE; the_salary employees.salary%TYPE; BEGIN IF NOT c1%ISOPEN THEN OPEN c1; END IF; FETCH c1 INTO the_name, the_salary; IF c1%ISOPEN THEN CLOSE c1; END IF; END; /
%FOUND Attribute: Has a Row Been Fetched?

returns:

  • after the explicit cursor is opened but before the first fetch

  • if the most recent fetch from the explicit cursor returned a row

  • otherwise

is useful for determining whether there is a fetched row to process.

Example 6-15 loops through a result set, printing each fetched row and exiting when there are no more rows to fetch.

Example 6-15 %FOUND Explicit Cursor Attribute

DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11 ORDER BY last_name; my_ename employees.last_name%TYPE; my_salary employees.salary%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_salary; IF c1%FOUND THEN -- fetch succeeded DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary); ELSE -- fetch failed EXIT; END IF; END LOOP; END; /

Result:

Name = Abel, salary = 11000 Name = Ande, salary = 6400 Name = Atkinson, salary = 3557.4 Name = Austin, salary = 4800 Name = Baer, salary = 10000 Name = Baida, salary = 2900 Name = Banda, salary = 6200 Name = Bates, salary = 7300 Name = Bell, salary = 5082 Name = Bernstein, salary = 9500
%NOTFOUND Attribute: Has No Row Been Fetched?

(the logical opposite of ) returns:

  • after the explicit cursor is opened but before the first fetch

  • if the most recent fetch from the explicit cursor returned a row

  • otherwise

is useful for exiting a loop when fails to return a row, as in Example 6-16.

Example 6-16 %NOTFOUND Explicit Cursor Attribute

DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11 ORDER BY last_name; my_ename employees.last_name%TYPE; my_salary employees.salary%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_salary; IF c1%NOTFOUND THEN -- fetch failed EXIT; ELSE -- fetch succeeded DBMS_OUTPUT.PUT_LINE ('Name = ' || my_ename || ', salary = ' || my_salary); END IF; END LOOP; END; /

Result:

Name = Abel, salary = 11000 Name = Ande, salary = 6400 Name = Atkinson, salary = 3557.4 Name = Austin, salary = 4800 Name = Baer, salary = 10000 Name = Baida, salary = 2900 Name = Banda, salary = 6200 Name = Bates, salary = 7300 Name = Bell, salary = 5082 Name = Bernstein, salary = 9500
%ROWCOUNT Attribute: How Many Rows Were Fetched?

returns:

  • Zero after the explicit cursor is opened but before the first fetch

  • Otherwise, the number of rows fetched (a )

Example 6-17 numbers and prints the rows that it fetches and prints a message after fetching the fifth row.

Example 6-17 %ROWCOUNT Explicit Cursor Attribute

DECLARE CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11 ORDER BY last_name; name employees.last_name%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO name; EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL; DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name); IF c1%ROWCOUNT = 5 THEN DBMS_OUTPUT.PUT_LINE('--- Fetched 5th row ---'); END IF; END LOOP; CLOSE c1; END; /

Result:

1. Abel 2. Ande 3. Atkinson 4. Austin 5. Baer --- Fetched 5th row --- 6. Baida 7. Banda 8. Bates 9. Bell 10. Bernstein

Query Result Set Processing

In PL/SQL, as in traditional database programming, you use cursors to process query result sets. However, in PL/SQL, you can use either implicit or explicit cursors. The former need less code, but the latter are more flexible. For example, explicit cursors can accept parameters (see "Explicit Cursors that Accept Parameters").

The following PL/SQL statements use implicit cursors that PL/SQL defines and manages for you:

  • Implicit cursor

The following PL/SQL statements use explicit cursors:

  • Explicit cursor

    You define the explicit cursor, but PL/SQL manages it while the statement runs.

  • , , and

    You define and manage the explicit cursor.

Topics

Note:

If a query returns no rows, PL/SQL raises the exception . For information about handling exceptions, see "Exception Handler".

Query Result Set Processing With SELECT INTO Statements

Using an implicit cursor, the statement retrieves values from one or more database tables (as the SQL statement does) and stores them in variables (which the SQL statement does not do).

Topics

Large Multiple-Row Result Sets

If you must assign a large quantity of table data to variables, Oracle recommends using the statement with the clause. This statement retrieves an entire result set into one or more collection variables. For more information, see "SELECT INTO Statement with BULK COLLECT Clause".

Query Result Set Processing With Cursor FOR LOOP Statements

The cursor statement lets you run a statement and then immediately loop through the rows of the result set. This statement can use either an implicit or explicit cursor.

If you use the statement only in the cursor statement, then specify the statement inside the cursor statement, as in Example 6-18. This form of the cursor statement uses an implicit cursor, and is called an implicit cursorstatement. Because the implicit cursor is internal to the statement, you cannot reference it with the name .

If you use the statement multiple times in the same PL/SQL unit, then define an explicit cursor for it and specify that cursor in the cursor statement, as in Example 6-19. This form of the cursor statement is called an explicit cursorstatement. You can use the same explicit cursor elsewhere in the same PL/SQL unit.

The cursor statement implicitly declares its loop index as a record variable of the type that its cursor returns. This record is local to the loop and exists only during loop execution. Statements inside the loop can reference the record and its fields. They can reference virtual columns only by aliases, as in Example 6-21.

After declaring the loop index record variable, the statement opens the specified cursor. With each iteration of the loop, the statement fetches a row from the result set and stores it in the record. When there are no more rows to fetch, the cursor statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or if PL/SQL raises an exception.

In Example 6-18, an implicit cursor statement prints the last name and job ID of every clerk whose manager has an ID greater than 120.

Example 6-18 Implicit Cursor FOR LOOP Statement

BEGIN FOR item IN ( SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%' AND manager_id > 120 ORDER BY last_name ) LOOP DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id); END LOOP; END; /

Result:

Name = Atkinson, Job = ST_CLERK Name = Bell, Job = SH_CLERK Name = Bissot, Job = ST_CLERK ... Name = Walsh, Job = SH_CLERK

Example 6-19 is like Example 6-18, except that it uses an explicit cursor statement.

Example 6-19 Explicit Cursor FOR LOOP Statement

DECLARE CURSOR c1 IS SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%' AND manager_id > 120 ORDER BY last_name; BEGIN FOR item IN c1 LOOP DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id); END LOOP; END; /

Result:

Name = Atkinson, Job = ST_CLERK Name = Bell, Job = SH_CLERK Name = Bissot, Job = ST_CLERK ... Name = Walsh, Job = SH_CLERK

Example 6-20 declares and defines an explicit cursor that accepts two parameters, and then uses it in an explicit cursor statement to display the wages paid to employees who earn more than a specified wage in a specified department.

Example 6-20 Passing Parameters to Explicit Cursor FOR LOOP Statement

DECLARE CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS SELECT * FROM employees WHERE job_id = job AND salary > max_wage; BEGIN FOR person IN c1('ST_CLERK', 3000) LOOP -- process data record DBMS_OUTPUT.PUT_LINE ( 'Name = ' || person.last_name || ', salary = ' || person.salary || ', Job Id = ' || person.job_id ); END LOOP; END; /

Result:

Name = Nayer, salary = 4065.6, Job Id = ST_CLERK Name = Mikkilineni, salary = 3430.35, Job Id = ST_CLERK Name = Landry, salary = 3049.2, Job Id = ST_CLERK ... Name = Vargas, salary = 3176.25, Job Id = ST_CLERK

In Example 6-21, the implicit cursor references virtual columns by their aliases, and .

Example 6-21 Cursor FOR Loop References Virtual Columns

BEGIN FOR item IN ( SELECT first_name || ' ' || last_name AS full_name, salary * 10 AS dream_salary FROM employees WHERE ROWNUM <= 5 ORDER BY dream_salary DESC, last_name ASC ) LOOP DBMS_OUTPUT.PUT_LINE (item.full_name || ' dreams of making ' || item.dream_salary); END LOOP; END; /

Result:

Michael Hartstein dreams of making 143325 Pat Fay dreams of making 66150 Jennifer Whalen dreams of making 48510 Douglas Grant dreams of making 31531.5 Donald OConnell dreams of making 31531.5

Note:

When an exception is raised inside a cursor statement, the cursor closes before the exception handler runs. Therefore, the values of explicit cursor attributes are not available in the handler.

Query Result Set Processing With Explicit Cursors, OPEN, FETCH, and CLOSE

For full control over query result set processing, declare explicit cursors and manage them with the statements , , and . (For instructions and examples, see "Explicit Cursors".)

This result set processing technique is more complicated than the others, but it is also more flexible. For example, you can:

  • Process multiple result sets in parallel, using multiple cursors.

  • Process multiple rows in a single loop iteration, skip rows, or split the processing into multiple loops.

  • Specify the query in one PL/SQL unit but retrieve the rows in another.

Query Result Set Processing with Subqueries

If you process a query result set by looping through it and running another query for each row, then you can improve performance by removing the second query from inside the loop and making it a subquery of the first query. For more information about subqueries, see Oracle Database SQL Language Reference.

Example 6-22 defines explicit cursor with a query whose clause contains a subquery.

Example 6-22 Subquery in FROM Clause of Parent Query

DECLARE CURSOR c1 IS SELECT t1.department_id, department_name, staff FROM departments t1, ( SELECT department_id, COUNT(*) AS staffFROM employeesGROUP BY department_id) t2 WHERE (t1.department_id = t2.department_id) AND staff >= 5 ORDER BY staff; BEGIN FOR dept IN c1 LOOP DBMS_OUTPUT.PUT_LINE ('Department = ' || dept.department_name || ', staff = ' || dept.staff); END LOOP; END; /

Result:

Department = IT, staff = 5 Department = Purchasing, staff = 6 Department = Finance, staff = 6 Department = Sales, staff = 34 Department = Shipping, staff = 45

While an ordinary subquery is evaluated for each table, a correlated subquery is evaluated for each row. Example 6-23 returns the name and salary of each employee whose salary exceeds the departmental average. For each row in the table, the correlated subquery computes the average salary for the corresponding department.

Example 6-23 Correlated Subquery

DECLARE CURSOR c1 IS SELECT department_id, last_name, salary FROM employees t WHERE salary > ( SELECT AVG(salary)FROM employeesWHERE t.department_id = department_id ) ORDER BY department_id, last_name; BEGIN FOR person IN c1 LOOP DBMS_OUTPUT.PUT_LINE('Making above-average salary = ' || person.last_name); END LOOP; END; /

Result:

Making above-average salary = Hartstein Making above-average salary = Raphaely Making above-average salary = Bell ... Making above-average salary = Higgins

Cursor Variables

A cursor variable is like an explicit cursor, except that:

  • It is not limited to one query.

    You can open a cursor variable for a query, process the result set, and then use the cursor variable for another query.

  • You can assign a value to it.

  • You can use it in an expression.

  • It can be a subprogram parameter.

    You can use cursor variables to pass query result sets between subprograms.

  • It can be a host variable.

    You can use cursor variables to pass query result sets between PL/SQL stored subprograms and their clients.

  • It cannot accept parameters.

    You cannot pass parameters to a cursor variable, but you can pass whole queries to it.

A cursor variable has this flexibility because it is a pointer; that is, its value is the address of an item, not the item itself.

Before you can reference a cursor variable, you must make it point to a SQL work area, either by opening it or by assigning it the value of an open PL/SQL cursor variable or open host cursor variable.

Note:

Cursor variables and explicit cursors are not interchangeable—you cannot use one where the other is expected. For example, you cannot reference a cursor variable in a cursor statement.

Topics

Creating Cursor Variables

To create a cursor variable, either declare a variable of the predefined type or define a type and then declare a variable of that type.

Note:

Informally, a cursor variable is sometimes called a ).

The basic syntax of a type definition is:

TYPE type_name IS REF CURSOR [ RETURN return_type ]

(For the complete syntax and semantics, see "Cursor Variable Declaration".)

If you specify , then the type and cursor variables of that type are strong; if not, they are weak. and cursor variables of that type are weak.

With a strong cursor variable, you can associate only queries that return the specified type. With a weak cursor variable, you can associate any query.

Weak cursor variables are more error-prone than strong ones, but they are also more flexible. Weak types are interchangeable with each other and with the predefined type . You can assign the value of a weak cursor variable to any other weak cursor variable.

You can assign the value of a strong cursor variable to another strong cursor variable only if both cursor variables have the same type (not merely the same return type).

Example 6-24 defines strong and weak types, variables of those types, and a variable of the predefined type .

Example 6-24 Cursor Variable Declarations

DECLARE TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- strong type TYPE genericcurtyp IS REF CURSOR; -- weak type cursor1 empcurtyp; -- strong cursor variable cursor2 genericcurtyp; -- weak cursor variable my_cursor SYS_REFCURSOR; -- weak cursor variable TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE; -- strong type dept_cv deptcurtyp; -- strong cursor variable BEGIN NULL; END; /

In Example 6-25, is a user-defined type.

Example 6-25 Cursor Variable with User-Defined Return Type

DECLARE TYPE EmpRecTyp IS RECORD ( employee_id NUMBER, last_name VARCHAR2(25), salary NUMBER(8,2)); TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp; emp_cv EmpCurTyp; BEGIN NULL; END; /

Opening and Closing Cursor Variables

After declaring a cursor variable, you can open it with the statement, which does the following:

  1. Associates the cursor variable with a query (typically, the query returns multiple rows)

    The query can include placeholders for bind variables, whose values you specify in the clause of the statement.

  2. Allocates database resources to process the query

  3. Processes the query; that is:

    1. Identifies the result set

      If the query references variables, their values affect the result set. For details, see "Variables in Cursor Variable Queries".

    2. If the query has a clause, locks the rows of the result set

      For details, see "SELECT FOR UPDATE and FOR UPDATE Cursors".

  4. Positions the cursor before the first row of the result set

You need not close a cursor variable before reopening it (that is, using it in another statement). After you reopen a cursor variable, the query previously associated with it is lost.

When you no longer need a cursor variable, close it with the statement, thereby allowing its resources to be reused. After closing a cursor variable, you cannot fetch records from its result set or reference its attributes. If you try, PL/SQL raises the predefined exception .

You can reopen a closed cursor variable.

Fetching Data with Cursor Variables

After opening a cursor variable, you can fetch the rows of the query result set with the statement, which works as described in "Fetching Data with Explicit Cursors".

The return type of the cursor variable must be compatible with the of the statement. If the cursor variable is strong, PL/SQL catches incompatibility at compile time. If the cursor variable is weak, PL/SQL catches incompatibility at run time, raising the predefined exception before the first fetch.

Example 6-26 uses one cursor variable to do what Example 6-6 does with two explicit cursors. The first statement includes the query itself. The second statement references a variable whose value is a query.

Example 6-26 Fetching Data with Cursor Variables

DECLARE cv SYS_REFCURSOR; -- cursor variable v_lastname employees.last_name%TYPE; -- variable for last_name v_jobid employees.job_id%TYPE; -- variable for job_id query_2 VARCHAR2(200) :='SELECT * FROM employeesWHERE REGEXP_LIKE (job_id, ''[ACADFIMKSA]_M[ANGR]'')ORDER BY job_id'; v_employees employees%ROWTYPE; -- record variable row of table BEGIN OPEN cv FORSELECT last_name, job_id FROM employeesWHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')ORDER BY last_name; LOOP -- Fetches 2 columns into variables FETCH cv INTO v_lastname, v_jobid; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid ); END LOOP; DBMS_OUTPUT.PUT_LINE( '-------------------------------------' ); OPEN cv FOR query_2; LOOP -- Fetches entire row into the v_employees record FETCH cv INTO v_employees; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') || v_employees.job_id ); END LOOP; CLOSE cv; END; /

Result:

Atkinson ST_CLERK Bell SH_CLERK Bissot ST_CLERK ... Walsh SH_CLERK ------------------------------------- Higgins AC_MGR Greenberg FI_MGR Hartstein MK_MAN ... Zlotkey SA_MAN

Example 6-27 fetches from a cursor variable into two collections (nested tables), using the clause of the statement.

Example 6-27 Fetching from Cursor Variable into Collections

DECLARE TYPE empcurtyp IS REF CURSOR; TYPE namelist IS TABLE OF employees.last_name%TYPE; TYPE sallist IS TABLE OF employees.salary%TYPE; emp_cv empcurtyp; names namelist; sals sallist; BEGIN OPEN emp_cv FOR SELECT last_name, salary FROM employees WHERE job_id = 'SA_REP' ORDER BY salary DESC; FETCH emp_cv BULK COLLECT INTO names, sals; CLOSE emp_cv; -- loop through the names and sals collections FOR i IN names.FIRST .. names.LAST LOOP DBMS_OUTPUT.PUT_LINE ('Name = ' || names(i) || ', salary = ' || sals(i)); END LOOP; END; /

Result:

Name = Ozer, salary = 12075 Name = Abel, salary = 11550 Name = Vishney, salary = 11025 ... Name = Kumar, salary = 6405

Assigning Values to Cursor Variables

You can assign to a PL/SQL cursor variable the value of another PL/SQL cursor variable or host cursor variable. The syntax is:

target_cursor_variable := source_cursor_variable;

If is open, then after the assignment, is also open. The two cursor variables point to the same SQL work area.

If is not open, opening after the assignment does not open .

Variables in Cursor Variable Queries

The query associated with a cursor variable can reference any variable in its scope. When you open a cursor variable with the statement, PL/SQL evaluates any variables in the query and uses those values when identifying the result set. Changing the values of the variables later does not change the result set.

Example 6-28 opens a cursor variable for a query that references the variable , which has the value 2. Therefore, is always 2 times , despite that is incremented after every fetch.

Example 6-28 Variable in Cursor Variable Query—No Result Set Change

DECLARE sal employees.salary%TYPE; sal_multiple employees.salary%TYPE; factor INTEGER := 2; cv SYS_REFCURSOR; BEGIN OPEN cv FORSELECT salary, salary*factorFROM employeesWHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor LOOP FETCH cv INTO sal, sal_multiple; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE('factor = ' || factor); DBMS_OUTPUT.PUT_LINE('sal = ' || sal); DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple); factor := factor + 1; -- Does not affect sal_multiple END LOOP; CLOSE cv; END; /

Result:

factor = 2 sal = 4451 sal_multiple = 8902 factor = 3 sal = 26460 sal_multiple = 52920 factor = 4 sal = 18742.5 sal_multiple = 37485 factor = 5 sal = 18742.5 sal_multiple = 37485

To change the result set, you must change the value of the variable and then open the cursor variable again for the same query, as in Example 6-29.

Example 6-29 Variable in Cursor Variable Query—Result Set Change

DECLARE sal employees.salary%TYPE; sal_multiple employees.salary%TYPE; factor INTEGER := 2; cv SYS_REFCURSOR; BEGIN DBMS_OUTPUT.PUT_LINE('factor = ' || factor); OPEN cv FORSELECT salary, salary*factorFROM employeesWHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor LOOP FETCH cv INTO sal, sal_multiple; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE('sal = ' || sal); DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple); END LOOP; factor := factor + 1; DBMS_OUTPUT.PUT_LINE('factor = ' || factor); OPEN cv FORSELECT salary, salary*factorFROM employeesWHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor LOOP FETCH cv INTO sal, sal_multiple; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE('sal = ' || sal); DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple); END LOOP; CLOSE cv; END; /

Result:

factor = 2 sal = 4451 sal_multiple = 8902 sal = 26460 sal_multiple = 52920 sal = 18742.5 sal_multiple = 37485 sal = 18742.5 sal_multiple = 37485 factor = 3 sal = 4451 sal_multiple = 13353 sal = 26460 sal_multiple = 79380 sal = 18742.5 sal_multiple = 56227.5 sal = 18742.5 sal_multiple = 56227.5

Cursor Variable Attributes

A cursor variable has the same attributes as an explicit cursor (see "Explicit Cursor Attributes"). The syntax for the value of a cursor variable attribute is immediately followed by (for example, ). If a cursor variable is not open, referencing any attribute except raises the predefined exception .

Cursor Variables as Subprogram Parameters

You can use a cursor variable as a subprogram parameter, which makes it useful for passing query results between subprograms. For example:

  • You can open a cursor variable in one subprogram and process it in a different subprogram.

  • In a multilanguage application, a PL/SQL subprogram can use a cursor variable to return a result set to a subprogram written in a different language.

Note:

The invoking and invoked subprograms must be in the same database instance. You cannot pass or return cursor variables to subprograms invoked through database links.

When declaring a cursor variable as the formal parameter of a subprogram:

  • If the subprogram opens or assigns a value to the cursor variable, then the parameter mode must be .

  • If the subprogram only fetches from, or closes, the cursor variable, then the parameter mode can be either or .

Corresponding formal and actual cursor variable parameters must have compatible return types. Otherwise, PL/SQL raises the predefined exception .

To pass a cursor variable parameter between subprograms in different PL/SQL units, define the type of the parameter in a package. When the type is in a package, multiple subprograms can use it. One subprogram can declare a formal parameter of that type, and other subprograms can declare variables of that type and pass them to the first subprogram.

Example 6-30 defines, in a package, a type and a procedure that opens a cursor variable parameter of that type.

Example 6-30 Procedure to Open Cursor Variable for One Query

CREATE OR REPLACE PACKAGE emp_data AS TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp); END emp_data; / CREATE OR REPLACE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS BEGIN OPEN emp_cv FOR SELECT * FROM employees; END open_emp_cv; END emp_data; /

In Example 6-31,the stored procedure opens its cursor variable parameter for a chosen query. The queries have the same return type.

Example 6-31 Opening Cursor Variable for Chosen Query (Same Return Type)

CREATE OR REPLACE PACKAGE emp_data AS TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT); END emp_data; / CREATE OR REPLACE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT) IS BEGIN IF choice = 1 THEN OPEN emp_cv FOR SELECT * FROM employees WHERE commission_pct IS NOT NULL; ELSIF choice = 2 THEN OPEN emp_cv FOR SELECT * FROM employees WHERE salary > 2500; ELSIF choice = 3 THEN OPEN emp_cv FOR SELECT * FROM employees WHERE department_id = 100; END IF; END; END emp_data; /

In Example 6-32,the stored procedure opens its cursor variable parameter for a chosen query. The queries have the different return types.

Example 6-32 Opening Cursor Variable for Chosen Query (Different Return Types)

CREATE OR REPLACE PACKAGE admin_data AS TYPE gencurtyp IS REF CURSOR; PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT); END admin_data; / CREATE OR REPLACE PACKAGE BODY admin_data AS PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT) IS BEGIN IF choice = 1 THEN OPEN generic_cv FOR SELECT * FROM employees; ELSIF choice = 2 THEN OPEN generic_cv FOR SELECT * FROM departments; ELSIF choice = 3 THEN OPEN generic_cv FOR SELECT * FROM jobs; END IF; END; END admin_data; /

Cursor Variables as Host Variables

You can use a cursor variable as a host variable, which makes it useful for passing query results between PL/SQL stored subprograms and their clients. When a cursor variable is a host variable, PL/SQL and the client (the host environment) share a pointer to the SQL work area that stores the result set.

To use a cursor variable as a host variable, declare the cursor variable in the host environment and then pass it as an input host variable (bind variable) to PL/SQL. Host cursor variables are compatible with any query return type (like weak PL/SQL cursor variables).

In Example 6-33, a Pro*C client program declares a cursor variable and a selector and passes them as host variables to a PL/SQL anonymous block, which opens the cursor variable for the selected query.

Example 6-33 Cursor Variable as Host Variable in Pro*C Client Program

EXEC SQL BEGIN DECLARE SECTION; SQL_CURSOR generic_cv; -- Declare host cursor variable. int choice; -- Declare selector. EXEC SQL END DECLARE SECTION; EXEC SQL ALLOCATE :generic_cv; -- Initialize host cursor variable. -- Pass host cursor variable and selector to PL/SQL block. / EXEC SQL EXECUTE BEGIN IF :choice = 1 THEN OPEN :generic_cv FOR SELECT * FROM employees; ELSIF :choice = 2 THEN OPEN :generic_cv FOR SELECT * FROM departments; ELSIF :choice = 3 THEN OPEN :generic_cv FOR SELECT * FROM jobs; END IF; END; END-EXEC;

A SQL work area remains accessible while any cursor variable points to it, even if you pass the value of a cursor variable from one scope to another. For example, in Example 6-33, the Pro*C program passes a host cursor variable to an embedded PL/SQL anonymous block. After the block runs, the cursor variable still points to the SQL work area.

0 Thoughts to “Homework Week 4 Pl/Sql Cursor

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *