PL/SQL cursor variable

Cursor variables are similar to cursors. They have similarities and differences. In general, both of them point to the current row in the result set of multi-row queries. Must go through the declaration, open, search and
The process of disabling. The difference is that the cursor and the cursor variable are similar to constants and variables. The cursor is static, while the cursor variable is dynamic, because the cursor variable is not bound to a specific query.
Therefore, the cursor variable can open any type of compatible queries. Second, you can pass cursor variables as parameters to local and storage subroutines. This document describes how to use cursor variables.

 

1. What is a cursor variable?
The display cursor is used to name a work area. It stores information about multi-row queries and always points to the content of the work area. Cursor variables are similar to those in C or Pascal.
It points to a memory address, rather than the content in the address itself. Therefore, declaring a cursor variable can create a pointer instead of the specific content.
In PL/SQL, to create a cursor variable, you must first declare a ref cursor type and then declare a variable of this type.
To perform multi-row queries, Oracle opens an unnamed workspace to store processing information. We can use an explicit cursor to name the workspace and then access the relevant information; or
A cursor variable pointing to the workspace. No matter where a cursor is used, it always points to the same query workspace, And the cursor variable can point to different workspaces.
Therefore, the cursor and the cursor variable cannot be used for interaction. That is to say, we cannot use the cursor variable in the place where the cursor is used or where the cursor variable is used.

 

Ii. Usage of cursor Variables
PL/SQL stores subprograms and various clients can use cursor variables to pass query results, which is the most important role of cursor variables. PL/SQL and other client programs do not have
Result set, which only shares a pointer pointing to the workspace of the result set. For example, an OCI client, an Oracle Forms application, and an Oracle server can reference
The same workspace. As long as there is a cursor variable pointing to the query workspace, We can reference it. Therefore, we can freely pass the value of the cursor variable from one scope to another.
For example, if we pass the primary cursor variable to the PL/SQL block nested in the Pro * C program, the workspace to which the cursor variable points can be accessed.
If the client contains the PL/SQL engine, there is no restriction on calling the server from the client. If we declare the cursor variable on the client, open the variable on the server and obtain the number
And then return the obtained result to the client. These operations are completed on the server side, which reduces network traffic.

 

Iii. Key Steps for Using cursor Variables
1. Define and declare cursor Variables
Type ref_type_name is ref cursor [return return_type]; -- the ref cursor type must be defined first.
Cursor_variable ref_type_name; -- next, define the cursor variable

Ref_type_name: Specifies the custom type name.
Return: Specifies the Data Type of the result returned by REF cursor.
Cursor_variable: defines the name of the cursor variable.
Note: If the return clause is specified, its data type must be record type. In addition, the cursor variable cannot be defined in the package specification.
If the return clause is specified, it is of the strong cursor type. Otherwise, it is of the weak cursor type.
A strongly-compatible query can be associated with any query. Therefore, the error probability of a strong type cursor variable is low, while the weak type is more flexible.

2. Open the cursor variable
When the cursor variable is enabled, the cursor variable is associated with a specific SELECT statement to run the query and identify the result set. You can use open for to open the same game for Different queries.
Variable. Before you open it again, you do not need to close the cursor variable, but all the previous queries will be lost.
Open cursor_variable for select_statement;

3. Retrieve data rows from the result set
Each time you retrieve from the result set. Note that the data type returned by a strong type must be compatible with the variable type used by into in the fetch statement.
Second, the number of queried column values must be equal to the number of variables. If the number does not match, a strong type will cause an error during compilation, while a weak type will cause an error during runtime.
Fetch cursor_variable into variable1,... variable2; -- extract a single row of data, which must be used with a loop statement.
Fetch cursor_variable bulk collect into collect1, collect2,... [limit rows]; -- extract multiple rows of data, and collect is the set variable

4. Close the cursor variable
Close cursor_vairable;

 

Iv. Example of defining ref cursor and declaring cursor Variables

-- Declare the cursor variable declare type emp_cur_type is ref cursor return EMP % rowtype In the PL/SQL block; --> define a cursor type with a return type, which is a strong type dept_cur_type is ref cursor; --> define the cursor type of the non-return type, which is weak emp_cv emp_cur_type; --> next, declare the two cursor variables dept_cv dept_cur_type; begin NULL; end; -- Use % type to define the return type of the cursor variable declare emp_type EMP % rowtype; --> defines an implicit record type: emp_cur_type is ref cursor return emp_type % type; --> define the cursor type and use % type to return the Data Type emp_cv emp_cur_type; --> declare the cursor variable begin NULL; end; -- declare type emp_rec_type is record --> defines a custom record type (empno number (4 ), ename varchar2 (10), hiredate EMP. hiredate % type); Type emp_cur_type is ref cursor return emp_rec_type; -- defines the cursor type with the return type, and the return type is the custom record type emp_cv emp_cur_type; -- declare the cursor variable begin NULL; end; -- the cursor variable is used as the declare type emp_cur_type is ref cursor parameter of the function or process --> to define a cursor type, return EMP % rowtype; emp_cur emp_cur_type; --> declare the cursor variable -- the following local process is used to process the result set of the cursor variable -- note, procedure process_emp_cv (emp_cv in emp_cur_type) is --> the parameter emp_cv uses the emp_cur_type cursor type person EMP % rowtype; begin dbms_output.put_line ('-----'); dbms_output.put_line ('here are the names from the result set :'); loop fetch emp_cv into person; exit when emp_cv % notfound; dbms_output.put_line ('name = '| person. ename | ''| person. hiredate); End loop; end; begin open emp_cur for select * from EMP where deptno = 10; --> use the cursor variable to open the cursor process_emp_cv (emp_cur ); --> call the local process to process the opened cursor variable close emp_cur; --> display and close the cursor variable open emp_cur for select * from EMP where job like 'cler '; --> open the cursor variable again and return process_emp_cv (emp_cur) of different result sets; --> call the local process to process the opened cursor variable close emp_cur; --> display the closed cursor variable end;

V. Use cursor Variables

1. Use the cursor variable create package emp_data as type empcurtyp is ref cursor return EMP % 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 each_emp EMP % rowtype; begin open emp_cv for select * from EMP where ename like 'a % '; end open_emp_cv; end emp_data; 2. Bind the cursor variable to a different return type (weak type) Create package get_data as type ref_cur_type is ref cursor; Procedure open_cv (ref_cv in out ref_cur_type, choice INT ); end get_data; Create package body get_data as procedure open_cv (ref_cv in out ref_cur_type, choice INT) is begin if choice = 1 then open ref_cv for select * from EMP; elsif choice = 2 then open ref_cv for select * from Dept; elsif choice = 3 then open ref_cv for select * from bonus; end if; end get_data; 3. Declare type emp_rec_type is record --> define a record type (empno EMP. empno % type, ename EMP. ename % type, hiredate EMP. hiredate % type); Type ref_cur_type is ref cursor --> defines the record type of a cursor variable whose return type is emp_rec_type. Return emp_rec_type; emp_cv ref_cur_type; --> declares the cursor variable emp_rec emp_rec_type; --> declare the record type variable begin open emp_cv for select empno, ename, hiredate from EMP where ename like 'a % '; loop fetch emp_cv into emp_rec; --> Save the result of the cursor variable to the record variable exit when emp_cv % notfound; dbms_output.put_line ('name = '| emp_rec.ename | '; '| 'hire date =' | emp_rec.hiredate); End loop; close emp_cv; end; 4. Bind the cursor variable result to the set declare type emp_rec_type is record --> define a record type (empno EMP. empno % type, ename EMP. ename % type, hiredate EMP. hiredate % type); Type emp_nst_type is table of emp_rec_type --> define the Union array index by pls_integer Based on the record type; type ref_cur_type is ref cursor --> defines the cursor variable and returns the record type return emp_rec_type; --> If emp_nst_type is used, the error emp_cv ref_cur_type is returned. --> declares the cursor variable emp_collect emp_nst_type; --> declare the composite data type variable begin open emp_cv for select empno, ename, hiredate from EMP where ename like 'a % '; fetch emp_cv bulk collect into emp_collect; --> use Bulk collect into batch extract cursor records to composite variables close emp_cv; for I in emp_collect.first .. emp_collect.last --> output the result loop dbms_output.put_line ('name = '| emp_collect (I) in the composite variable ). ename | ', hiredate =' | emp_collect (I ). hiredate); End loop; end; 5. Operate cursor variables in SQL * Plus -- The following is based on the previously defined package get_data, we call the cursor variable in the package in SQL * Plus and return data Scott @ cnmmbo> variable lv_ref_cv refcursor; Scott @ cnmmbo> variable lv_choice number; Scott @ cnmmbo> Exec: lv_choice: = 2; PL/SQL procedure successfully completed. scott @ cnmmbo> exec get_data.open_cv (: lv_ref_cv,: lv_choice); PL/SQL procedure successfully completed. scott @ cnmmbo> Print lv_ref_cv deptno dname loc ---------- --------------------- 10 Accounting New York 20 research Dallas 30 sales Chicago 40 operations boston6, PL/SQL call the cursor variable declare v_ref_cv variable in the package; --> declare a packet-based weak type cursor variable v_ref_cv_rec dept % rowtype; --> because of the weak type, therefore, we use the record type of the corresponding table as the return type to declare the record variable --> If the variable defined in the package is a strong type cursor variable, then the preceding statement can be written as return_type % rowtype v_choice pls_integer: = 2; begin regression (v_ref_cv, v_choice); loop fetch v_ref_cv into v_ref_cv_rec; exit when v_ref_cv % notfound; dbms_output.put_line ('current REC is '| v_ref_cv_rec.dname |', '| v_ref_cv_rec.loc); End loop; end; --> author: Robinson Cheng --> blog: http://blog.csdn.net/robinson_0612current REC is accounting, new yorkcurrent REC is research, dallascurrent REC is sales, chicagcurrent REC is operations, bostonpl/SQL procedure successfully completed.7. Exceptions caused by poorly defined return types: declare type weak_ref_cur_type is ref cursor; weak_ref_cur failed; weak_ref_rec failed % rowtype; --> Generate a PL/SQL 320 error -- weak_ref_rec dept % rowtype; --> correct, use the custom return type -- weak_ref_rec EMP % rowtype; --> if a type is defined incompatible with the return type, an exception occurs at runtime --> ORA-06504: PL/SQL: return types of result set variables or query do not matchbegin open weak_ref_cur for select * from Dept; fetch weak_ref_cur into weak_ref_rec; values ('current REC is '| required | ', '| weak_ref_rec.loc); close weak_ref_cur; end; error at line 5: ORA-06550: line 5, column 24: PLS-00320: the Declaration of the type of this expression is incomplete or malformedORA-06550: line 5, column 24: PL/SQL: item ignoredORA-06550: Line 10, column 28: PLS-00320: the Declaration of the type of this expression is incomplete or malformed8, an exception occurs during strong-type Compilation --> the returned type in the strongly-type cursor variables defined below is incompatible, the declare type strong_ref_cur_type is ref cursor return EMP % rowtype; strong_ref_cur partition; strong_ref_rec EMP % rowtype; begin open strong_ref_cur for select * from Dept; --> the defined return type is EMP % rowtype, and the query at this time is dept table Type fetch strong_ref_cur into strong_ref_rec; close strong_ref_cur; end; error at line 8: ORA-06550: line 8, column 28: PLS-00382: expression is of wrong typeORA-06550: line 8, column 4: PL/SQL: SQL statement ignored

Vi. Considerations for Using cursor Variables
1. The cursor variable cannot be defined in the package specification.
2. You cannot use cursor variables in remote subroutines of other servers. You cannot pass cursor variables to the called process through database connections.
3. When processing cursor variables, do not use for update and open for together.
4. Comparison operators cannot be used to test the equivalence, non-equivalence, or non-null of cursor variables.
5. The cursor variable cannot be assigned a null value.
6. The ref cursor type cannot be used in create table or view statements, because there is no equivalent data type for database columns.
7. The stored procedure using the cursor variable can only be used as a query block data source. It cannot be used as a DML block data source. Ref cursor is suitable for changes that only depend on SQL statements (not PL/SQL statements ).
Quantity Query
8. cursor variables cannot be stored in the combined array, nested table, or variable-length array.
9. If a host cursor variable is passed to PL/SQL, it cannot be retrieved on the server unless it is opened in the same server call.

VII. More references

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan