ORACLE cursor concepts

1. What is a cursor?
① Retrieve the result set from the table and point to a record for interaction at a time.

② Operations in relational databases are performed on the complete set of rows.
The row set returned by the SELECT statement includes all rows that meet the conditions listed in the WHERE clause of the statement. The complete set of rows returned by this statement is called the result set.
It is not always effective for applications, especially interactive and online applications, to process the complete result set as a unit.
These applications require a mechanism to process one or more rows at a time. The cursor is an extension of the result set that provides this mechanism.
 
The cursor is implemented through the cursor library. A cursor library is a software that is often implemented as part of a database system or data access API,
Used to manage the attributes (result set) of the data returned from the data source ). These attributes include concurrency management, location in the result set, and number of returned rows,
And whether it can be moved forward and/or backward (rolling) in the result set ).
 
The cursor tracks the position of the result set and allows multiple operations on the result set row by row. In this process, the operation may be returned to the original table or the operation may not be returned to the original table.
In other words, a cursor is a database-based table that returns a result set.
Because it indicates the current position in the result set, just as the cursor on the computer screen indicates the current position, the cursor is named.
 
2. What is the role of a cursor?
① Specify the location of a specific row in the result set.
② Retrieve one or more consecutive rows based on the current result set location.
③ Modify the data in the row at the current position of the result set.
④ Data changes made to other users define different sensitivity levels.
⑤ You can access the database programmatically.

3. Why avoid using a cursor?
① When creating a cursor, the most important thing to consider is: "Is there a way to avoid using a cursor ?"
Because the cursor efficiency is poor, if the cursor operation has more than 10 thousand rows of data, it should be rewritten;
If a cursor is used, try to avoid table join operations in the cursor loop.
 
4. What is the Oracle cursor type?
① Static cursor: The cursor with a result set (static definition. Includes implicit and display cursors.
(1) Implicit cursor: All DML statements are implicit cursors. You can use the implicit cursor attribute to obtain SQL statement information.
(2) display cursor: the user displays the declared cursor, that is, the specified result set. An explicit cursor is required when more than one row of query results are returned.
② REF cursor: temporary object of the result set dynamically associated.
 
5. What are the statuses of Oracle cursors? How do I use the cursors?
① The cursor status is represented by attributes.
% Found: the execution status of the Fetch Statement (Obtain Record) is True or False.
% NotFound: whether to extract True or False from the last record.
% ISOpen: whether to enable the cursor is True or False.
% RowCount: number of rows currently extracted by the cursor.
② Use the attributes of the cursor.
Example:/* conn scott/tiger */
Begin
Update emp Set SAL = SAL + 0.1 Where JOB = 'cler ';
If SQL % Found Then
DBMS_OUTPUT.PUT_LINE ('updated! ');
Else
DBMS_OUTPUT.PUT_LINE ('Update failed! ');
End If;
End;
 
6. How to use a display cursor ,? How do I traverse the cyclic cursor?
① Use a display cursor
(1) declared cursor: divides the storage area. Note that the Select statement is not executed at this time.
CURSOR name (parameter list) [Return Value Type] IS Select statement;
(2) Open the cursor: Execute the Select statement to obtain the result set and store it in the cursor. The cursor points to the result set header instead of the first record.
Open cursor name (parameter list );
(3) retrieve record: move the cursor to retrieve a record.
Fetch cursor name InTo temporary record or attribute type variable;
(4) Close the cursor: Put the cursor into the buffer pool and the resource is not completely released. You can open it again.
Close cursor name;
② Traverse the cyclic cursor
(1) For Loop cursor
Loop cursors open the cursors implicitly, automatically scroll to get a record, and automatically create a temporary record type variable storage record. The cursor is automatically closed after processing.
For variable name In cursor name
Loop
Data processing statement;
End Loop;
(2) Loop cursor
...
Loop
Fatch cursor name InTo temporary record or attribute type variable;
Exit When cursor name % NotFound;
End Loop;
...
Example 1:
/* Conn scott/tiger */
Declare
Cursor myCur is select empno, ename, sal from emp;
Vna varchar2 (10 );
Vno number (4 );
Vsal number (7,2 );
Begin
Open myCur;
Fetch myCur into vno, vna, vsal;
Dbms_output.put_line (vno | ''| vna |'' | vsal );
Close myCur;
End;
/
 
Example 2: Use loop to traverse the cursor.
/* Conn scott/tiger */
Declare
Cursor myCur is select ename, job, sal, empno from emp;
VarE myCur % rowType;
Begin
If myCur % isopen = false then
Open myCur;
Dbms_output.put_line ('opening ...');
End if;
Loop
Fetch myCur into varE;
Exit when myCur % notfound;
Dbms_output.put_line (myCur % rowCount | ''| vare. empno |'' | vare. ename | ''| vare. sal );
End loop;
If myCur % isopen then
Close myCur;
Dbms_output.put_line ('closing ...');
End if;
End;
/
 
Example 3: Use the For loop to traverse the cursor,
/* Conn scott/tiger */
Declare
Cursor myCur is select * from emp;
Begin
For varA in myCur
Loop
Dbms_output.put_line (myCur % rowCount | ''| varA. empno |'' | varA. ename | ''| varA. sal );
End loop;
End;
/
 
7. How to update and delete records in the display cursor?
① The where current of substring in the UPDATE or DELETE statement is used to process the most recent data retrieved from the table for the UPDATE or DELETE operation.
To use this method, you must use the for update substring when declaring the cursor. When you use the for update substring to open a cursor,
All data rows in the returned dataset are locked exclusively at the ROW-LEVEL. Other objects can only query these data rows,
You cannot perform UPDATE, DELETE, or SELECT... for update operations.
In multi-table queries, the "OF" clause is used to lock a specific table. If the "OF" clause is ignored, all selected data rows in the table are locked.
If these data rows have been locked by other sessions, ORACLE will normally wait until the data row is unlocked.
② Use update or delete:
(1) Declare update or delete display cursor:
Cursor name is select statement For Update [Of updating column name];
Cursor name is select statement For Delete [Of updating column name];
(2) Use the current display cursor record to update or delete:
Update table name SET Update statement Where Current Of cursor name;
Delete From table name Where Current Of cursor name;
 
Example 1: update the display cursor record
/* Conn scott/tiger */
Declare
Cursor myCur is select job from emp for update;
Vjob empa. job % type;
Rsal empa. sal % type;
Begin
Open myCur;
Loop
Fetch myCur into vjob;
Exit when myCur % notFound;
Case (vjob)
When 'analyst' then rsal: = 0.1;
When 'cler' then rsal: = 0.2;
When'manager' then rsal: = 0.3;
Else
Rsal: = 0.5;
End case;
Update emp set sal = sal + rsal where current of myCur;
End loop;
End;
/
Example 2: delete a display cursor record
/* Conn scott/tiger
Crate table empa Select * from scott. emp;
*/
Declare
Cursor MyCursor Select JOB From empa For Update;
VSal emp. Sal % TYPE;
Begin
Loop
Fetch MyCursor InTo vSal;
Exit When MyCursor % NotFound;
If vSal & lt; 800 Then & gt;
Delete From empa Where Cursor Of MyCursor;
End If;
End Loop;
End ;/
8. What is a display cursor with parameters?
① Similar to procedures and functions, parameters can be passed to the cursor and used in the query.
The parameter defines only the data type and no size (all Oracle parameters only define the data type, not the size ).
Different from the procedure, a cursor can only accept transmitted values, but cannot return values.
You can set a default value for the parameter. When no parameter value is passed to the cursor, the default value is used.
The parameter defined in the cursor is just a placeholder. It is not necessarily reliable to reference it elsewhere.
② Use a display cursor with Parameters
(1) declare a display cursor with parameters:
CURSOR name [(parameter [, parameter],...)] IS Select statement ;;

Parameter format: 1. Parameter Name Data Type
2. DEFAULT Value of Parameter Name Data Type

Example:
/* Conn scott/tiger
Crate table empa Select * from scott. emp;
*/
Declare
Cursor MyCursor (pSal Number Default 800) Select JOB From empa Where SAL> pSal;
VarA MyCursor % ROWTYPE;
Begin
Loop
Fetch MyCursor InTo varA;
Exit When MyCursor % NotFound;
DBMS_OUTPUT.PUT_LINE (MyCursor % RowCount | ''| varA. empno |'' | varA. ename | ''| varA. sal );
End Loop;
End ;/
 
 
 
REF CURSOR
 
1. What is a REF cursor?
A temporary object that dynamically associates with the result set. That is, the query is dynamically determined during running.
 
2. What is the function of the REF cursor?
You can use ref cursor to transfer result sets between programs to improve SQL Performance.
 
3. What is the difference between a static cursor and a REF cursor?
① A static cursor is a static definition, and a REF cursor is a dynamic Association;
② REF cursor requires REF cursor variable.
③ REF cursors can be passed as parameters, but static cursors are impossible.
 
4. What is a REF cursor variable?
A ref cursor variable is a variable that references the REF cursor type and points to a dynamically correlated result set.
 
5. How to Use the REF cursor?
1. Declare the REF cursor type and determine the REF cursor type;
(1) strong type REF cursor: Specify the retrun type. The type of the REF cursor variable must be the same as the return type.
Syntax: Type REF Cursor name IS Ref Cursor Return result set Return record Type;
(2) weak type ref cursor: return type is not specified and can match any type of CURSOR variable to obtain any result set.
Syntax: Type REF Cursor name IS Ref Cursor;
 
② Declare the Ref cursor type variable;
Syntax: The variable name has declared the Ref cursor type;
 
③ Open the REF cursor and associate the result set;
Syntax: return result set of Open Ref cursor type variable For query statement;
 
④ Retrieve records and operation records;
Syntax: Fatch REF cursor name InTo temporary record type variable or attribute Type Variable list;
 
⑤ Close the cursor and completely release the resource;
Syntax: Close REF cursor name;
 
Example: strong type REF cursor
/* Conn scott/tiger */
Declare
Type MyRefCurA is ref cursor return emp % RowType;
Type MyRefCurB is ref cursor return emp. ename % Type;
VRefCurA MyRefCurA;
VRefCurB MyRefCurB;
VTempA vRefCurA % RowType;
VTempB vRefCurB. ename % Type;
 
Begin
Open vRefCurA For Select * from emp Where SAL> 2000;
Loop
Fatch vRefCurA InTo vTempA;
Exit When vRefCurA % NotFound;
DBMS_OUTPUT.PUT_LINE (vRefCurA % RowCount | ''| vTempA. eno |'' | vTempA. ename | ''| vTempA. sal)
End Loop;
Close vRefCurA;
 
DBMS_OUTPUT.PUT_LINE ('hangzhou ('-------------------------------------------------------------------------------------------------------');
 
Open vRefCurB For Select ename from emp Where SAL> 2000;
Loop
Fatch vRefCurB InTo vTempB;
Exit When vRefCurB % NotFound;
DBMS_OUTPUT.PUT_LINE (vRefCurB % RowCount | ''| vTempB)
End Loop;
Close vRefCurB;
 
DBMS_OUTPUT.PUT_LINE ('hangzhou ('-------------------------------------------------------------------------------------------------------');
 
Open vRefCurA For Select * from emp Where JOB = 'cler ';
Loop
Fatch vRefCurA InTo vTempA;
Exit When vRefCurA % NotFound;
DBMS_OUTPUT.PUT_LINE (vRefCurA % RowCount | ''| vTempA. eno |'' | vTempA. ename | ''| vTempA. sal)
End Loop;
Close vRefCurA;
End;
 
Example: weak type REF cursor
/* Conn scott/tiger */
Declare
Type MyRefCur IS Ref Cursor;
VRefCur MyRefCur;
Vtemp vRefCur % RowType;
Begin
Case (& n)
When 1 Then Open vRefCur For Select * from emp;
When 2 Then Open vRefCur For Select * from dept;
Else
Open vRefCur For Select eno, ename from emp Where JOB = 'cler ';
End Case;
Close vRefCur;
End;