Cursor Variables

最新推荐文章于 2024-09-15 08:03:51 发布

DB架构

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量299

点赞数

分类专栏： PL/SQL 文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/u011868279/article/details/129863888

版权

PL/SQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

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. The queries can include variables.

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.

1 Creating Cursor Variables

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

The basic syntax of a REF CURSOR type definition is:

TYPE type_name IS REF CURSOR [ RETURN return_type ]

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

Example 6-24 Cursor Variable Declarations

This example defines strong and weak REF CURSOR types, variables of those types, and a variable of the predefined type SYS_REFCURSOR.

SQL> show user
USER is "SYS"
SQL> alter session set container=PDB1;

Session altered.

SQL> conn hr/hr@PDB1;
Connected.
SQL> 
SQL> set pagesize 200 linesize 200
SQL> 
SQL> DECLARE
  2    TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  -- strong type
  3    TYPE genericcurtyp IS REF CURSOR;                       -- weak type
  4  
  5    cursor1  empcurtyp;       -- strong cursor variable
  6    cursor2  genericcurtyp;   -- weak cursor variable
  7    my_cursor SYS_REFCURSOR;  -- weak cursor variable
  8  
  9    TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;  -- strong type
 10    dept_cv deptcurtyp;  -- strong cursor variable
 11  BEGIN
 12    NULL;
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /

PL/SQL procedure successfully completed.

SQL>

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

In this example, EmpRecTyp is a user-defined RECORD type.

SQL> 
SQL> DECLARE
  2    TYPE EmpRecTyp IS RECORD (
  3      employee_id NUMBER,
  4      last_name VARCHAR2(25),
  5      salary   NUMBER(8,2));
  6  
  7    TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
  8    emp_cv EmpCurTyp;
  9  BEGIN
 10    NULL;
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL>

2 Opening and Closing Cursor Variables

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

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 USING clause of the OPEN FOR statement.

Allocates database resources to process the query

Processes the query; that is:

Identifies the result set

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

If the query has a FOR UPDATE clause, locks the rows of the result set

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

Positions the cursor before the first row of the result set

3 Fetching Data with Cursor Variables

After opening a cursor variable, you can fetch the rows of the query result set with the FETCH statement.

The return type of the cursor variable must be compatible with the into_clause of the FETCH 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 ROWTYPE_MISMATCH before the first fetch.

Example 6-26 Fetching Data with Cursor Variables

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

SQL> DECLARE
  2    cv SYS_REFCURSOR;  -- cursor variable
  3   
  4    v_lastname  employees.last_name%TYPE;  -- variable for last_name
  5    v_jobid     employees.job_id%TYPE;     -- variable for job_id
  6   
  7    query_2 VARCHAR2(200) :=
  8      'SELECT * FROM employees
  9      WHERE REGEXP_LIKE (job_id, ''[ACADFIMKSA]_M[ANGR]'')
 10      ORDER BY job_id';
 11   
 12    v_employees employees%ROWTYPE;  -- record variable row of table
 13   
 14  BEGIN
 15    OPEN cv FOR
 16      SELECT last_name, job_id FROM employees
 17      WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
 18      ORDER BY last_name;
 19   
 20    LOOP  -- Fetches 2 columns into variables
 21      FETCH cv INTO v_lastname, v_jobid;
 22      EXIT WHEN cv%NOTFOUND;
 23      DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
 24    END LOOP;
 25   
 26    DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
 27   
 28    OPEN cv FOR query_2;
 29   
 30    LOOP  -- Fetches entire row into the v_employees record
 31      FETCH cv INTO v_employees;
 32      EXIT WHEN cv%NOTFOUND;
 33      DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||
 34                                 v_employees.job_id );
 35    END LOOP;
 36   
 37    CLOSE cv;
 38  END;
 39  /
Atkinson                 ST_CLERK
Bell                     SH_CLERK
Bissot                   ST_CLERK
Bull                     SH_CLERK
Cabrio                   SH_CLERK
Chung                    SH_CLERK
Davies                   ST_CLERK
Dellinger                SH_CLERK
Dilly                    SH_CLERK
Everett                  SH_CLERK
Feeney                   SH_CLERK
Fleaur                   SH_CLERK
Gates                    SH_CLERK
Gee                      ST_CLERK
Geoni                    SH_CLERK
Grant                    SH_CLERK
Jones                    SH_CLERK
Ladwig                   ST_CLERK
Landry                   ST_CLERK
Mallin                   ST_CLERK
Markle                   ST_CLERK
Marlow                   ST_CLERK
Matos                    ST_CLERK
McCain                   SH_CLERK
Mikkilineni              ST_CLERK
Nayer                    ST_CLERK
OConnell                 SH_CLERK
Olson                    ST_CLERK
Patel                    ST_CLERK
Perkins                  SH_CLERK
Philtanker               ST_CLERK
Rajs                     ST_CLERK
Rogers                   ST_CLERK
Sarchand                 SH_CLERK
Seo                      ST_CLERK
Stiles                   ST_CLERK
Sullivan                 SH_CLERK
Taylor                   SH_CLERK
Vargas                   ST_CLERK
Walsh                    SH_CLERK
-------------------------------------
Higgins                  AC_MGR
Greenberg                FI_MGR
Hartstein                MK_MAN
Russell                  SA_MAN
Partners                 SA_MAN
Errazuriz                SA_MAN
Cambrault                SA_MAN
Zlotkey                  SA_MAN

PL/SQL procedure successfully completed.

SQL>

Example 6-27 Fetching from Cursor Variable into Collections

This example fetches from a cursor variable into two collections (nested tables), using the BULK COLLECT clause of the FETCH statement.

SQL> DECLARE
  2    TYPE empcurtyp IS REF CURSOR;
  3    TYPE namelist IS TABLE OF employees.last_name%TYPE;
  4    TYPE sallist IS TABLE OF employees.salary%TYPE;
  5    emp_cv  empcurtyp;
  6    names   namelist;
  7    sals    sallist;
  8  BEGIN
  9    OPEN emp_cv FOR
 10      SELECT last_name, salary FROM employees
 11      WHERE job_id = 'SA_REP'
 12      ORDER BY salary DESC;
 13  
 14    FETCH emp_cv BULK COLLECT INTO names, sals;
 15    CLOSE emp_cv;
 16    -- loop through the names and sals collections
 17    FOR i IN names.FIRST .. names.LAST
 18    LOOP
 19      DBMS_OUTPUT.PUT_LINE
 20        ('Name = ' || names(i) || ', salary = ' || sals(i));
 21    END LOOP;
 22  END;
 23  /
Name = Ozer, salary = 11500
Name = Abel, salary = 11000
Name = Vishney, salary = 10500
Name = Tucker, salary = 10000
Name = Bloom, salary = 10000
Name = King, salary = 10000
Name = Fox, salary = 9600
Name = Sully, salary = 9500
Name = Bernstein, salary = 9500
Name = Greene, salary = 9500
Name = Hall, salary = 9000
Name = McEwen, salary = 9000
Name = Hutton, salary = 8800
Name = Taylor, salary = 8600
Name = Livingston, salary = 8400
Name = Olsen, salary = 8000
Name = Smith, salary = 8000
Name = Doran, salary = 7500
Name = Cambrault, salary = 7500
Name = Smith, salary = 7400
Name = Bates, salary = 7300
Name = Marvins, salary = 7200
Name = Sewall, salary = 7000
Name = Tuvault, salary = 7000
Name = Grant, salary = 7000
Name = Lee, salary = 6800
Name = Ande, salary = 6400
Name = Johnson, salary = 6200
Name = Banda, salary = 6200
Name = Kumar, salary = 6100

PL/SQL procedure successfully completed.

SQL>

4 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.

target_cursor_variable := source_cursor_variable;

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

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

5 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 OPEN FOR 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 Variable in Cursor Variable Query—No Result Set Change

This example opens a cursor variable for a query that references the variable factor, which has the value 2. Therefore, sal_multiple is always 2 times sal, despite that factor is incremented after every fetch.

SQL> 
SQL> DECLARE
  2    sal           employees.salary%TYPE;
  3    sal_multiple  employees.salary%TYPE;
  4    factor        INTEGER := 2;
  5   
  6    cv SYS_REFCURSOR;
  7   
  8  BEGIN
  9    OPEN cv FOR
 10      SELECT salary, salary*factor
 11      FROM employees
 12      WHERE job_id LIKE 'AD_%';   -- PL/SQL evaluates factor
 13   
 14    LOOP
 15      FETCH cv INTO sal, sal_multiple;
 16      EXIT WHEN cv%NOTFOUND;
 17      DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
 18      DBMS_OUTPUT.PUT_LINE('sal          = ' || sal);
 19      DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
 20      factor := factor + 1;  -- Does not affect sal_multiple
 21    END LOOP;
 22   
 23    CLOSE cv;
 24  END;
 25  /
factor = 2
sal          = 4400
sal_multiple = 8800
factor = 3
sal          = 24000
sal_multiple = 48000
factor = 4
sal          = 17000
sal_multiple = 34000
factor = 5
sal          = 17000
sal_multiple = 34000

PL/SQL procedure successfully completed.

SQL>

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

SQL> 
SQL> DECLARE
  2    sal           employees.salary%TYPE;
  3    sal_multiple  employees.salary%TYPE;
  4    factor        INTEGER := 2;
  5   
  6    cv SYS_REFCURSOR;
  7   
  8  BEGIN
  9    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
 10   
 11    OPEN cv FOR
 12      SELECT salary, salary*factor
 13      FROM employees
 14      WHERE job_id LIKE 'AD_%';   -- PL/SQL evaluates factor
 15   
 16    LOOP
 17      FETCH cv INTO sal, sal_multiple;
 18      EXIT WHEN cv%NOTFOUND;
 19      DBMS_OUTPUT.PUT_LINE('sal          = ' || sal);
 20      DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
 21    END LOOP;
 22   
 23    factor := factor + 1;
 24   
 25    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
 26   
 27    OPEN cv FOR
 28      SELECT salary, salary*factor
 29      FROM employees
 30      WHERE job_id LIKE 'AD_%';   -- PL/SQL evaluates factor
 31   
 32    LOOP
 33      FETCH cv INTO sal, sal_multiple;
 34      EXIT WHEN cv%NOTFOUND;
 35      DBMS_OUTPUT.PUT_LINE('sal          = ' || sal);
 36      DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
 37    END LOOP;
 38   
 39    CLOSE cv;
 40  END;
 41  /
factor = 2
sal          = 4400
sal_multiple = 8800
sal          = 24000
sal_multiple = 48000
sal          = 17000
sal_multiple = 34000
sal          = 17000
sal_multiple = 34000
factor = 3
sal          = 4400
sal_multiple = 13200
sal          = 24000
sal_multiple = 72000
sal          = 17000
sal_multiple = 51000
sal          = 17000
sal_multiple = 51000

PL/SQL procedure successfully completed.

SQL>

6 Querying a Collection

You can query a collection if all of the following are true:

The data type of the collection was either created at schema level or declared in a package specification.

The data type of the collection element is either a scalar data type, a user-defined type, or a record type.

Example 6-30 Querying a Collection with Static SQL

SQL> CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
  2    TYPE rec IS RECORD(f1 NUMBER, f2 VARCHAR2(30));
  3    TYPE mytab IS TABLE OF rec INDEX BY pls_integer;
  4  END;
  5  /

Package created.

SQL> DECLARE
  2    v1 pkg.mytab;  -- collection of records
  3    v2 pkg.rec;
  4    c1 SYS_REFCURSOR;
  5  BEGIN
  6    v1(1).f1 := 1;
  7    v1(1).f2 := 'one';
  8    OPEN c1 FOR SELECT * FROM TABLE(v1);
  9    FETCH c1 INTO v2;
 10    CLOSE c1;
 11    DBMS_OUTPUT.PUT_LINE('Values in record are ' || v2.f1 || ' and ' || v2.f2);
 12  END;
 13  /
Values in record are 1 and one

PL/SQL procedure successfully completed.

SQL>

7 Cursor Variable Attributes

A cursor variable has the same attributes as an explicit cursor .The syntax for the value of a cursor variable attribute is cursor_variable_name immediately followed by attribute .If a cursor variable is not open, referencing any attribute except %ISOPEN raises the predefined exception INVALID_CURSOR.

8 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.

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 IN OUT.

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

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

SQL> 
SQL> CREATE OR REPLACE PACKAGE emp_data AUTHID DEFINER AS
  2    TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
  3    PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);
  4  END emp_data;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY emp_data AS
  2    PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
  3    BEGIN
  4      OPEN emp_cv FOR SELECT * FROM employees;
  5    END open_emp_cv;
  6  END emp_data;
  7  /

Package body created.

SQL>

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

In this example ,the stored procedure opens its cursor variable parameter for a chosen query. The queries have the same return type.

SQL> 
SQL> CREATE OR REPLACE PACKAGE emp_data AUTHID DEFINER AS
  2    TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
  3    PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT);
  4  END emp_data;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY emp_data AS
  2    PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT) IS
  3    BEGIN
  4      IF choice = 1 THEN
  5        OPEN emp_cv FOR SELECT *
  6        FROM employees
  7        WHERE commission_pct IS NOT NULL;
  8      ELSIF choice = 2 THEN
  9        OPEN emp_cv FOR SELECT *
 10        FROM employees
 11        WHERE salary > 2500;
 12      ELSIF choice = 3 THEN
 13        OPEN emp_cv FOR SELECT *
 14        FROM employees
 15        WHERE department_id = 100;
 16      END IF;
 17    END;
 18  END emp_data;
 19  /

Package body created.

SQL>

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

In this example,the stored procedure opens its cursor variable parameter for a chosen query. The queries have the different return types.

SQL> 
SQL> CREATE OR REPLACE PACKAGE admin_data AUTHID DEFINER AS
  2    TYPE gencurtyp IS REF CURSOR;
  3    PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT);
  4  END admin_data;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY admin_data AS
  2    PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT) IS
  3    BEGIN
  4      IF choice = 1 THEN
  5        OPEN generic_cv FOR SELECT * FROM employees;
  6      ELSIF choice = 2 THEN
  7        OPEN generic_cv FOR SELECT * FROM departments;
  8      ELSIF choice = 3 THEN
  9        OPEN generic_cv FOR SELECT * FROM jobs;
 10      END IF;
 11    END;
 12  END admin_data;
 13  /

Package body created.

SQL>

9 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).

/* PL/SQL anonymous block in host environment */
BEGIN
  OPEN :emp_cv FOR SELECT * FROM employees;
  OPEN :dept_cv FOR SELECT * FROM departments;
  OPEN :loc_cv FOR SELECT * FROM locations;
END;
/

/* PL/SQL anonymous block in host environment */
BEGIN
  CLOSE :emp_cv;
  CLOSE :dept_cv;
  CLOSE :loc_cv;
END;
/

/* PL/SQL anonymous block in host environment */
BEGIN
  OPEN :c1 FOR SELECT 1 FROM DUAL;
  OPEN :c2 FOR SELECT 1 FROM DUAL;
  OPEN :c3 FOR SELECT 1 FROM DUAL;
END;
/

Example 6-34 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;