Use cursor in Oracle

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 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 cursor name is SELECT statement for update [of updating column name];
Cursor 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 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 ;/