Oracle Cursor __oracle

1, what is a cursor.
① retrieves a result set from a table from which the mechanism for interacting with each record is pointed.

Operations in the ② relational database are performed on a complete rowset.
The rowset returned by the SELECT statement includes all rows that meet the criteria listed in the WHERE clause of the statement. Returning the complete rowset by this statement is called the result set.
Applications, especially interactive and online applications, do not always work with a complete set of results as a unit.
These applications require a mechanism to process one row or successive lines at a time. Cursors are an extension of the result set that provides this mechanism.

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

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

2, what is the role of the cursor.
① Specifies the location of a specific 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 sensitivity levels for data changes made by other users.
⑤ can access the database programmatically.

3, why do you avoid using cursors?
① when creating a cursor, the most thing to consider is, "is there a way to avoid using cursors?" ”
Because the cursor is inefficient, if the cursor operation has more than 10,000 rows of data, it should be overwritten;
If you use a cursor, you should try to avoid the operation of table joins in the cursor loop.

the type of the 4,oracle cursor.
① static cursor: A cursor that has a true (statically defined) result set. is divided into implicit and display cursors.
⑴ implicit cursor: All DML statements are implicit cursors, and SQL statement information can be obtained through implicit cursor properties.
⑵ Display cursor: The user displays a declared cursor, that is, the result set is specified. When a query returns more than one row, an explicit cursor is required.
②REF cursor: A temporary object that dynamically associates a result set.

5,oracle The state of the cursor and how to use the cursor properties.
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 is open 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 to traverse a loop cursor.
① using Display Cursors
⑴ declaration cursor: Dividing the storage area, note that the SELECT statement is not executed at this time.
CURSOR cursor name (argument list) [return value type] is Select statement;
⑵ Open cursor: Executes the SELECT statement, obtains the result set to be stored in the cursor, at which point the cursor points to the result set header instead of the first record.
Open cursor name (argument list);
⑶ FETCH 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 and does not fully release the resource. can be reopened.
The close cursor name;
② traversal loop cursor
⑴for Loop cursor
Loop Leng open cursors, automatically scroll to get a record, and automatically create temporary record type variables to store records. Automatically closes the cursor when the process is finished.
For variable name in cursor name
Loop
Data processing statements;
End Loop;
⑵loop Loop cursor
。。。
Loop
Fatch cursor into a temporary record or an 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 the 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: 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 to update and delete the records in the display cursor.
The WHERE current of substring in a ①update or DELETE statement deals specifically with the most recent data that is fetched from the table to perform an update or delete operation.
To use this method, you must use a for update substring when declaring a cursor, and when the dialog uses a for update substring to open a cursor,
All data rows in the returned set will be in row-level (row-level) exclusive locks, and other objects can only query the rows of data.
Cannot make update, delete, or select ... For update operation.
In a multiple-table query, use the OF clause to lock a particular table, and if the of clause is omitted, the rows of data selected in all tables are locked.
If these rows of data are already locked by another session, Oracle will normally wait until the data row unlocks.
② use updates or deletes:
⑴ declare update or delete a display cursor:
Cursor cursor name is SELECT statement for UPDATE [of Update column column name];
Cursor cursor name is SELECT statement for Delete [of Update column column name];
⑵ to update or delete using the current record of the display cursor:
Update table name SET UPDATE statement Where current of cursor name;
Delete from table name Where current of cursor name;

Example 1: Update 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 ' clerk ' 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 < Then
Delete from Empa Where Cursor of MyCursor;
End If;
End Loop;
end;/
8, what is a display cursor with parameters.
① are similar to procedures and functions, you can pass parameters to cursors and use them in queries.
The parameter defines only the data type, not the size (all the parameters in Oracle define only the data type, not the size).
Unlike a procedure, a cursor can only accept values that are passed, not values.
You can set a default value for the parameter and use the default value when no parameter values are passed to the cursor.
The parameter defined in the cursor is only a placeholder, and it is not necessarily reliable to reference the parameter elsewhere.
② 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 value

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 a result set. That is, dynamically deciding to execute the query at run time.

What is the effect of 2,ref cursors.
Implements the ability to pass result sets between programs, using REF CURSOR can also implement bulk SQL to improve SQL performance.

3, what is the difference between a static cursor and a REF CURSOR?
① static cursors are statically defined, and REF cursors are dynamic associations;
② using a REF CURSOR requires a REF CURSOR variable.
③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 refers to a REF cursor type, pointing to a dynamically associated result set.

5, how to use a REF CURSOR.
① declares the REF cursor type and determines the REF cursor type;
⑴ strongly type 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 type REF CURSOR: Does not specify return type, can match any type of cursor variable to get any result set.
Syntax: Type REF CURSOR name is ref Cursor;

② declares a REF cursor type variable;
Syntax: variable name declared REF CURSOR type;

③ opens the REF cursor, associating the result set;
Syntax: Open REF cursor type variable for query statement returns result set;

④ obtain records, Operation Records;
Syntax: Fatch REF CURSOR into temporary record type variable or attribute type variable list;

⑤ closes the cursor, releasing the resource completely;
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 CURSOR
/*conn scott/tiger*/
Declare
Type Myrefcur is Ref Cursor;
Vrefcur myrefcur;
Vtemp Vrefcur%rowtype;
Begin
Case (&n)
When 1 Then the Open vrefcur for the Select * from EMP;
When 2 Then the Open vrefcur for Select * from dept;
Else
Open vrefcur for Select Eno, ename from emp Where JOB = ' clerk ';
End case;
Close vrefcur;
End;