Cursor __oracle in Oracle

There are two types of cursors in Oracle: an explicit cursor, an implicit cursor. A display cursor is a cursor defined with the cursor...is command that handles multiple records returned by a query statement (SELECT), while an implicit cursor is a query that performs inserts (insert), deletes (delete), modifies (update), and returns a single record ( Select) statement is automatically defined by Pl/sql.


An explicit cursor
When an explicit cursor is declared, you can control the operation of an explicit cursor with the following three commands: Open the cursor, push the cursor, and close the cursor.


Declaring an explicit cursor


? No-parameter cursors


Cursor C_auths is select * from Auths


? A parameter cursor


Cursor c_auths (p_code auths.author_code%type) is select * from Auths where Author_code=p_code


? Cursors for binding variable arguments


V_code Auths.author_code%type;


Cursor C_auths is select * from Auths where author_code=v_code;


Open an explicit cursor


It is also legal to open an open cursor. When the cursor is opened for the second time, Pl/sql automatically closes the cursor before opening it. Opening more than one cursor at a time is also allowed by Pl/sql. Assign a value to a bound variable before opening the cursor.


v_code:= ' A00001 ';


Open c_auths;


Open c_auths (' A00001 '); --passing parameters in when the cursor is opened


Advancing an explicit cursor


When an explicit cursor is opened, you can use the FETCH statement to push the cursor and return a row in the query result set. After each FETCH statement is executed, the explicit cursor automatically points to the next row of the query result set.


Close an explicit cursor


When the entire result set is retrieved, the cursor should be closed. The close cursor is used to inform the pl/sql that the cursor operation has ended and to release the resources occupied by the cursor (the resource space used by the result set).


An implicit cursor
Implicitly declaring a cursor in pl/sql for all SQL data manipulation statements, including a select that returns a row, is called an implicit cursor. The main reason is that users cannot directly name and control such cursors. When a user uses data manipulation statements (DML) in Pl/sql, Oracle defines an implicit cursor named SQL, and obtains information about the most recently executed SQL statement by examining the properties of an implicit cursor.


By adding a separate into clause to a standard SELECT statement in Pl/sql, you can assign a variable or row variable to a query record from a table or view. What you need to be aware of is select. INTO statement the result must have and only one row. If the query does not return rows, Pl/sql throws a No_data_found exception. If the query returns multiple rows, a Too_many_rows exception is thrown. If an exception is thrown, execution is stopped and control is transferred to the exception-handling section (without exception handling, the program breaks). When an exception is thrown, the attribute%found,%notfound,%rowcount is not used to find out whether the DML statement has affected the number of rows.


Begin


Update auths set entry_date_time=sysdate where author_code= ' A00017 ';


--If the modified row in the UPDATE statement does not exist (the SQL%notfound return value is true), a row is inserted into the Auths table.


If SQL%nofound Then


INSERT into auths values (' A000017 ', ' Qiuys ', 1, ' 30-apr-40 ', 88.5, sysdate);


End If;


End


--If the modified row in the UPDATE statement does not exist (SQL%rowcount=0)


Declare


V_birthdate date;


Begin


Select Birthdate into V_birthdate from auths where name= ' Qiuys ';


--If you query to a record, delete the record.


If SQL%found Then


Delete from auths where name= ' Qiuys ';


End If;


exception


When No_data_found Then


Dbms_output.put_line (' The record does not exist ');


When Too_many_rows Then


Dbms_output_line (' The existence of a writer of the same name ');


End


Cursor Properties
? %found%found property returns True only if the DML statement affects one or more rows


? %notfound is exactly the opposite of the%found attribute. If the DML statement does not affect any number of rows, the%notfound property returns True.


? %rowcount returns the number of rows affected by a DML statement. If the DML statement does not affect any number of rows, the%rowcount property returns 0.


? %isopen to determine if the SQL cursor is already open. After the SQL statement is executed, Oracle automatically closes the SQL cursor, so the%isopen property of the implicit cursor is always false.


Cursor Loops
Fetch loop


Delcare


--Declares a variable used to receive the result set returned by the cursor.


V_salary Auths.salary%type;


V_code Auths.author_code%type;


/* Declares a cursor that the query result set is the value of the writer's code "A00001" to "A00006". */


Cursor C_salary is select Salary,author_code from auths where author_code<= ' A00006 ';


Begin


--Open the cursor and initialize the result set


Open c_salary;


Loop


--Push the cursor and save a row in the query result set in the variable v_salary.


Fetch c_salary into V_salary,v_code;


--Exits the loop when there are no rows in the result set.


Exit when C_salary%notfound;


-If the author's salary is less than or equal to 200, the author's salary will be increased.


If V_salary<= Then


Update auths set salary=salary+50 where Author_code=v_code;


End If;


End Loop;


--Closes the cursor and frees the cursor to occupy resources.


Close c_salary;


--Submit the changes you have made.


Commit


End


For loop


Delcare


Cursor C_salary is


Select Salary form auths where author_code<= ' A00006 ';


Begin


--Start the cursor for loop and implicitly open the C_salary cursor.


For v_salary in C_salary loop


-An implied FETCH statement is executed here.


If V_salary.salary<= Then


Update auths set salary=salary+50 where salary=v_salary.salary;


End If;


--An implied c_auths%notfound is detected before the loop continues.


End Loop;


--Now that the loop has ended, an implied close operation of the c_auths cursor is executed.


Commit


End


Use the current OF cursor clause as a condition
Declare


Cursor Cur_emp is


Elect empno, ename, job from emp where empno = 7369 for update of ename;


Begin


For Return_cur in Cur_emp


Loop


Update emp Set ename = ' LHG ' where current of cur_emp;


End Loop;


End;


The for update of ename means that the rows in the table are locked, and after testing, the following ename can be written into any field in the table because the lowest level of lock in Oracle is a row lock and no word Chega exists.


The following are the effects of row locks:


1. The row lock begins with a cursor open, ending with a commit commit or rollback, rather than ending with a cursor end (close).


2. When the row of a table is locked in a cursor, it waits for the first cursor to be submitted after the first cursor is submitted, if another cursor is used to manipulate the row in this session. The operation of a row in the second cursor begins execution.


3. When the first cursor is unable to submit due to an operation error, the second cursor will wait until the deadlock is formed. The way to prevent this happens is to specify the NOWAIT option after the for update of ename, so that when the second cursor does not wait all the time, it appears ORA-00054 [resource busy and acquire with Nowa IT specified] message.


4. Since the field behind the for update is a random field, can you not write it? If you do not need to specify the NOWAIT option, then the field will not be written, and if you must specify the NOWAIT option, you must specify at least one field.


5. There is another way to replace the where current of Your_cursor_name statement with ROWID.


The following code:


Declare
Cursor Cur_emp is select a. Deptno, A. Dname, A. rowID, B. rowID rowid_1


From dept A, emp b where empno = 7369 and A. Deptno = b. Deptno for update nowait;


V_deptno Dept. Deptno% Type;
V_dname Dept. Dname% Type;
V_rowid rowID;
V_rowid_1 rowID;
Begin
Open cur_emp;
Loop
Fetch cur_emp into V_deptno, V_dname, V_rowid, v_rowid_1;
Exit when cur_emp% NotFound;
Update Dept Set dname = ' abc ' WHERE ROWID = V_rowid;
Update emp Set ename = ' Frank ' where rowid = v_rowid_1;
End Loop;
Close cur_emp;
commit;
exception
When others then
Rollback;
raise;
End;


From this, the recommended for update habits are:


? NoWait certainly with for UPDATE after.


? Replace the where current of the Your_cursor_name statement directly with ROWID, especially in the program of relative propagation.


? A COMMIT must exist at the end of the program. In case of deadlock forming.


? The rollback in EXCEPTION is the most basic need.


Cursor variable
The examples of all previous explicit cursors so far are static cursors-that is, the cursor is associated with an SQL statement, and the SQL statement was determined at compile time. A cursor variable is a variable of a reference type (REF).


Declaration of a cursor variable


--Define a cursor variable type using%rowtype.


Type T_AUTHSREF is REF CURSOR return auths%rowtype;


V_AUTHCV T_authsref;


pl/sql2.8 in the above version, you can specify multiple different types of queries by using a cursor variable that does not specify a result set type.


Type t_authsref is REF CURSOR;


V_ AUTHSCV t_authsref;--declares a variable of that type.


Open cursor Variable


In order to associate a cursor change with a specific SELECT statement, a SELECT statement is added to the open syntax.


Open V_AUTHSCV for select * from Auths;


Turn off cursor operation


The close cursor operation is used to free the resources occupied by the query. However, the storage space occupied by the cursor variable is not freed. When a variable goes out of scope, the space it occupies is released. The following block defines a cursor variable that does not specify a result set, so that we can use this cursor variable to point to a different query and to return different record types:


Set serveroutput on size 100000--Sets the storage buffer size.


Declare


/* Defines a cursor change type T_CURREF, the cursor variable type does not specify a result set type, so a variable of that cursor variable type can return a different pl/sql record type. */


Type t_curref is REF CURSOR;


--Declaring a variable of a cursor variable type


C_cursorref T_curref;


--Defines the Pl/sql record type T_authorrec, which is used to receive the return value of the cursor variable.


Type T_authorrec is record (


Authorcode Auths.author_code%type,


Name Auths.name%type);


--Defines the Pl/sql record type T_articlerec, which is also used to receive the return value of the cursor variable.


Type T_articlerec is record (


Authorcode Article.author_code%type,


Title Artitle.title%type);


--declares two record type variables.


V_author T_authorrec;


V_article T_articlerec;


Begin


--Opens the cursor variable c_cursorref and returns a record of the T_authorrec type.


Open C_cursorref for


Select Author_code,name from Auths where Author_code in (' A00001 ', ' A00002 ', ' A00003 ', ' A00004 ', ' A00005 ');


--Propulsion cursor variable


Fetch c_cursorref into V_author;


--the propulsion loop of the cursor variable.


While C_cursorref%found loop


--Output The writer's code and the corresponding writer's name to the screen.


Dbms_output.put (v_author.authorcode| | | v_author.name| | ' ' );


Fetch c_cursorref into V_author;


End Loop;


dbms_output.new_line;--to the screen to output a back to the dealership.


--Closes the cursor variable, releasing only the resource specified by the cursor variable, and the cursor variable itself is not freed from storage space.


Close C_cursorref;


--Open the cursor variable again, returning a record of type T_articlerec.


Open C_cursorref for


Select Author_code,title from article


where Author_code in (' A00001 ', ' A00002 ', ' A00003 ', ' A00004 ', ' A00005 ');


Fetch c_cursorref into v_article;


While C_cursorref%found loop


...


End Loop;


Close C_cursorref;


End


Note that in the above example, the first time you close a cursor variable is omitted, because the first query is lost when you open the cursor variable for the second time. Also, cursor variables have cursor properties, which are typically used when you push cursor variables, such as the%found property used in the previous example.