Cursors in Oracle

There are two types of cursors in Oracle: Explicit cursors, implicit cursors. The display cursor is a cursor defined with the cursor...is command, which can process multiple records returned by a query statement (SELECT), whereas an implicit cursor is a query that performs an insert (insert), delete, modify (update), and return a single record ( Select) statement is automatically defined by PL/SQL.

An explicit cursor

When an explicit cursor is declared, the operation of an explicit cursor can be controlled by the following three commands: Open the cursor, push the cursor, and close the cursor.

Declaring an explicit cursor

Ø Non-parametric cursors

Cursor C_auths is select * from Auths

Ø a parametric cursor

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

Ø cursors that bind variable parameters

V_code Auths.author_code%type;

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

Open an explicit cursor

It is also legal to open a cursor that is already open. When the cursor is opened for the second time, PL/SQL automatically closes the cursor before opening it. Opening multiple cursors at once is also allowed by PL/SQL. assigns a value to the bound variable before opening the cursor .

v_code:= ' A00001 ';

Open c_auths;

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

Advancing an explicit cursor

When an explicit cursor is opened, you can use a FETCH statement to push the cursor back to return a row in the query result set. After each execution of a fetch statement, an explicit cursor automatically points to the next row in 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 notify the PL/SQL cursor that the operation has ended and to release the resource occupied by the cursor (the resource space used by the result set).

An implicit cursor

In PL/SQL, the implicit declaration of a cursor is known as an implicit cursor for all data manipulation statements, including a select that returns a row. The main reason is that users cannot directly name and control such cursors. When the user uses data manipulation statements (DML) and select INTO in PL/SQL, Oracle Pre-defines an implicit cursor called SQL, which obtains information about the most recently executed SQL statement by examining the properties of the implicit cursor.

Adding a separate into clause to a standard SELECT statement in PL/SQL allows you to assign a variable or row variable to a query record from a table or view. It is important to note that select: The into statement must have a result and only one row. If the query does not return rows, PL/SQL throws a No_data_found exception. If the query returns more than a row, the Too_many_rows exception is thrown. If an exception is thrown, execution is stopped and control is transferred to the Exception handling section (the program is interrupted without exception handling). 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 a record is queried, the record is deleted.

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 author of the same name exists ');

End

Cursor Properties

Ø%found the%found property returns true only if the DML statement affects one or more rows

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

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

Ø%isopen determines whether the SQL cursor is 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 loops

Delcare

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

V_salary Auths.salary%type;

V_code Auths.author_code%type;

/* Declares a cursor, which is the salary value of the writer code "A00001" to "A00006". */

Cursor C_salary is a 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 to save a row from the query result set in the v_salary to the variable.

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 is increased.

If v_salary<=

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

--Starts the cursor for loop, implicitly opening the c_salary cursor.

For v_salary in C_salary loop

--An implied FETCH statement is executed here.

If v_salary.salary<=

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 is over, 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 to lock the rows in the table and, after testing, ename can be written to any field in the table because the lowest level of lock in Oracle is row lock and does not exist for word Chega.

Here is the function of the row lock:

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

2. When the row of a table is locked in a cursor, it waits for the first cursor to complete the commit when the row record is manipulated by another cursor in the session, until the first cursor commit is completed. A change to a row in the second cursor starts execution.

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

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

5. Another way is 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;

As a result, the recommended custom for update is:

Ønowait with for UPDATE.

Ø directly replaces the where current of Your_cursor_name statement with ROWID, especially in the relatively complex program.

Øcommit must be at the end of the program. Prevent the deadlock from forming.

The rollback in Øexception is the most basic need.

Cursor variables

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

Declaration of a cursor variable

--use%rowtype to define a cursor variable type.

Type T_AUTHSREF is REF CURSOR return auths%rowtype;

V_AUTHCV T_authsref;

pl/sql2.8 above, you can specify several 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, the open syntax adds a SELECT statement.

Open V_AUTHSCV for select * from Auths;

To close a cursor operation

The close cursor action is used to free the resources that the query occupies. However, the storage space occupied by the cursor variable is not released. When a variable goes out of scope, the space it occupies is freed. 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 different queries and to return different record types:

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

Declare

/* Defines the cursor change type T_CURREF, which does not specify a result set type, so variables of the cursor variable type can return different PL/SQL record types. */

Type t_curref is REF CURSOR;

--Declare a variable of type of cursor variable

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);

--declaration of two record type variables.

V_author T_authorrec;

V_article T_articlerec;

Begin

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

Open C_cursorref for

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

--Push cursor variable

Fetch c_cursorref into V_author;

--The push cycle of the cursor variable.

While C_cursorref%found loop

--Output The writer code and the corresponding writer 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;--output a return car to the screen.

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

Close C_cursorref;

--Opens 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 the cursor variable is closed is omitted, because the first query is lost when the cursor variable is opened the second time. Also, cursor variables have cursor properties, which are typically used when pushing cursor variables, such as the%found property used in the previous example.

(Transferred from http://blog.csdn.net/bupt_zoucq/article/details/6771585)

Cursors in Oracle (RPM)