Oracle Cursor Concepts Explained

1, what is a cursor?
① retrieves the result set from the table, and the mechanism from which each point points to a record for interaction.
The operations in the ② relational database are performed on the complete rowset.
The rowset returned by the SELECT statement includes all rows that satisfy the conditions listed in the WHERE clause of the statement. The complete rowset returned by the statement is called the result set.
Applications, especially interactive and online applications, treat a complete set of results as a unit that is not always valid.
These applications require a mechanism to process a row or rows at a time. The cursor is an extension of the result set that provides this mechanism.

Cursors are implemented through a cursor library. Cursor Library is a software that is often implemented as part of a database system or data access API,
A property (result set) used to manage the data returned from the data source. These properties include concurrency management, the location in the result set, the number of rows returned,
And whether you can move forward and/or backward in the result set (scrollable).

The cursor tracks the position in the result set and allows multiple actions to be performed on the result set line by row, which may be returned to the original table or not back to the original table.
In other words, a cursor conceptually returns a result set from a database-based table.
Because it indicates the current position in the result set, the cursor is named as if the cursor on the computer screen indicates the current position.

2, what is the function of the cursor?
① Specifies the location of a particular row in the result set.
② retrieves a row or successive rows based on the current result set location.
③ modifies the data in the row at the current position of the result set.
④ defines different levels of sensitivity for data changes made by other users.
⑤ can access the database programmatically.
3, why avoid using cursors?
① the most thing to consider when creating cursors is, "is there a way to avoid using cursors?" ”
Because cursors are inefficient, they should be rewritten if the cursor operates on more than 10,000 rows of data.
If a cursor is used, try to avoid the operation of table joins in the cursor loop.

What is the type of 4,oracle cursor?
① static cursor: A cursor that has a true (statically defined) result set. is divided into implicit and display cursors.
⑴ implicit cursors: All DML statements are implicit cursors, and SQL statement information can be obtained through implicit cursor properties.
⑵ Display cursors: The user displays the declared cursor, which specifies the result set. An explicit cursor is required when the query returns more than one row.
②REF cursor: A temporary object that dynamically associates the result set.
What is the state of the 5,oracle cursor and how is the cursor property used?
The state of the ① cursor is represented by a property.
%found:fetch statement (get record) execution True or False.
%notfound: Whether the last record extracts True or False.
%isopen: Whether the cursor opens true or False.
%rowcount: The number of rows currently fetched by the cursor.
② uses the properties of the cursor.
Example:/* Conn Scott/tiger */
Begin
Update emp Set sal = sal + 0.1 Where JOB = ' clerk ';
If Sql%found Then
Dbms_output. Put_Line (' has been updated! ‘);
Else
Dbms_output. Put_Line (' Update failed! ‘);
End If;
End;
6, how to use the display cursor,? How do I traverse a circular cursor?
① using Display Cursors
⑴ declares a cursor: divides the storage area, noting that the SELECT statement is not executed at this time.
cursor cursor name (parameter list) [return value type] is Select statement;
⑵ Open cursor: Executes a SELECT statement to obtain the result set stored in the cursor, at which point the cursor points to the result set header instead of the first record.
Open cursor name (parameter list);
⑶ Get record: Move cursor fetch a record
Fetch cursor name into temporary record or attribute type variable;
⑷ closes the cursor: puts the cursor in the buffer pool without fully releasing the resource. can be reopened.
Close cursor name;
② traversing a loop cursor
⑴for Loop Cursors
Loop Leng Open the cursor, automatically scroll to get a record, and automatically create a temporary record type variable to store the record. The cursor is automatically closed when finished processing.
For variable name in cursor name
Loop
Data processing statements;
End Loop;
⑵loop Loop Cursors
。。。
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 a 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 a cursor.
/* Conn Scott/tiger */
Declare
Cursor Mycur is a 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: Using a For loop to traverse a cursor,
/* Conn Scott/tiger */
Declare
Cursor Mycur is a 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 do I update and delete the records in the display cursor?
The WHERE current of substring in the ①update or DELETE statement specifically handles the most recent data that is taken out of the table to perform the update or delete operation.
To use this method, you must use the FOR update substring when declaring the cursor, and when the dialog uses the for update substring to open a cursor,
All data rows in the returned set are locked at row level (Row-level) exclusive, and other objects can only query those rows.
Cannot make update, delete, or select ... For update operation.
In a multi-table query, use the OF clause to lock a particular table, and if the of clause is omitted, all rows of data selected in the table will be locked.
If these data rows are already locked by another session, Oracle will normally wait until the data row is unlocked.
② using UPDATE or delete:
⑴ declares that the display cursor is updated or deleted:
Cursor cursor name is the SELECT statement for update [of Update column column name];
Cursor cursor name is a SELECT statement for Delete [of Update column column name];
⑵ is updated or deleted using the current record of the display cursor:
Update table name SET Updated statement Where current of cursor name;
Delete from table name Where current of cursor name;
Example 1: Updating display cursor records
/*conn scott/tiger*/
Declare
Cursor Mycur is the select job from the 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 ' clerk ' then rsal: = 0.2;
When the ' 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 the 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 < Then
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, you can pass parameters to cursors and use them in queries.
The parameter defines only the data type, no size (all parameters in Oracle define only the data type and do not specify the size).
Unlike a procedure, a cursor can only accept values that are passed and cannot return a value.
A default value can be set for the parameter, and the default value is used when no parameter value is passed to the cursor.
The parameter defined in the cursor is just a placeholder, and referencing it elsewhere is not necessarily reliable.
② using a display cursor with parameters
⑴ declares a display cursor with parameters:
cursor cursor name [(Parameter[,parameter],...)] is select statement;;
Parameter form: 1, Parameter name data type
2, Parameter name data type default defaults
Example:
/*conn Scott/tiger
Crate table Empa Select * from Scott.emp;
*/
Declare
Cursor MyCursor (psal number Default) 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 the result set. This is the dynamic decision to execute the query at run time.
What is the role of 2,REF cursors?
Implement the ability to pass result sets between programs, and use REF CURSOR to implement bulk SQL, which improves SQL performance.

3, what is the difference between a static cursor and a REF CURSOR?
① static cursors are statically defined and REF cursors are dynamically correlated;
② the REF CURSOR variable is required to use a REF CURSOR.
③REF cursors can be passed as parameters, while static cursors are not possible.
4, what is a REF CURSOR variable?
A REF CURSOR variable is a variable that references a REF cursor type, pointing to a dynamically associated result set.

5, how do I use a REF CURSOR?
① declares a REF cursor type and determines the REF cursor type;
⑴ strongly typed REF cursor: Specifies that the type of the Retrun TYPE,REF cursor variable must be the same as the return type.
Syntax: type REF CURSOR name is REF CURSOR return result set returns record type;
⑵ weakly typed REF CURSOR: Does not specify return type, can match any type of cursor variable, and is used to get any result set.
Syntax: Type REF CURSOR name is REF CURSOR;

② declaring a REF CURSOR type variable;
Syntax: The variable name has declared a REF cursor type;
③ opens a REF cursor, associating the result set;
Syntax: The Open REF cursor type variable for query statement returns a result set;
④ get record, operation record;
Syntax: Fatch REF CURSOR name into temporary record type variable or attribute type variable list;
⑤ close the cursor and completely release the resources;
Syntax: Close REF CURSOR name;
Example: strongly-typed ref cursors
/*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 ('---------------------------------------------------------------------------------------------------- ---‘);
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 ('---------------------------------------------------------------------------------------------------- ---‘);
Open Vrefcura for Select * from emp Where JOB = ' clerk ';
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: weakly-typed ref cursors
/*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 = ' clerk ';
End case;
Close vrefcur;
End;

Reprint Please specify source: http://blog.csdn.net/zcywell/article/details/7329670

Oracle Cursor Concepts explained