Oracle Cursor details and examples

Abstract: describes in detail the definition and use of cursors in Oracle databases. You can use instance operations to gain an in-depth understanding of the usage and usage of cursor.

I. Related Concepts 1. concept

When Oracle Database executes aSQL statement, it stores the result set and processing information in anunnamed private SQL area. A pointer to this unnamed area, called a cursor, let you retrieve the rows of the result set one at a time. cursor attributesreturn information about the state of the cursor.

2. concept:

A cursor is a memory Workspace of SQL, which is defined by the system or user as a variable. The cursor is used to temporarily store data blocks extracted from the database. In some cases, you need to transfer the data from the table stored in the disk to the computer memory for processing, and finally display the processing results or write them back to the database. In this way, the data processing speed will increase; otherwise, frequent disk data exchanges will reduce the efficiency. Cursor type: static Cursor-divided into explicit Cursor and implicit (implicit) Cursor, REF Cursor-dynamic Cursor, is a reference type, similar to a pointer.

 

II. Specific Types and use 1. implicit cursor

1) explain: Everytime you run either a SQL DML statement or a PL/SQLSELECTINTO statement, PL/SQLopens an implicit cursor. you can get information about this cursor from itsattributes, but you cannot control it. after the statement runs, the databasecloses the cursor; however, its attribute values remain available until anotherDML orSELECTINTO statement runs.

2) implicit cursor (implicit cursor) the system automatically opens and closes a SQL dml instance. When we execute an SQL DML instance, the system automatically opens a cursor instance. After the execution is complete, the system closes the cursor instance. We cannot directly control the cursor instance, but we can you can use the attributes of implicit Cursor to understand the operation status and results and control the process. The attributes of Cursor include:

I. SQL % ROWCOUNT integer -- indicates the number of rows successfully executed by the DML statement

Ii. SQL % FOUND Boolean -- when the value is true, the table is inserted, deleted, updated, or queried successfully.

Iii. SQL % NOTFOUND Boolean -- opposite to above

V, SQL % ISOPEN Boolean -- the DML execution process is true, otherwise it is false

3) Example:

Begin
Update student set sname = 'chy' WHERE sno = '1 ';
If SQL % isopen then
Dbms_output.put_line ('cursor is opening! ');
Else
Dbms_output.put_line ('cursor is closed! ');
End if;
If SQL % found then
Dbms_output.put_line ('dml is successed! ');
Else
Dbms_output.put_line ('dml is failed! ');
End if;
If SQL % notfound then
Dbms_output.put_line ('dml is failed! ');
Else
Dbms_output.put_line ('dml is successed! ');
End if;
Dbms_output.put_line (SQL % rowcount | 'is the number of result! ');
Exception
When no_data_found then
Dbms_output.put_line ('Sorry No data ');
When too_many_rows then
Dbms_output.put_line ('too writable rows ');
End;

2. explicit cursor

 

1) explanation: PL/SQLalso lets you declare explicit cursors. an explicit cursor has a name and isassociated with a query (SQLSELECT statement)-usually one that returns multiplerows. after declaring an explicit cursor, you must open it (with the OPENstatement), fetch rows one at a time from the result set (with the FETCHstatement ), and close the cursor (with the CLOSE statement ). after closing thecursor, you can neither fetch records from the result set nor see the cursorattribute values.

The usage and usage of the display cursor are straightforward.

2) attributes of the explicit cursor include:

The type of the attribute return value of the cursor.

% ROWCOUNT integer to obtain the number of data rows returned by the FETCH statement

The latest FETCH statement of the % FOUND Boolean Type Returns a row of data, which is true. Otherwise, the return value is false.

The return value of % NOTFOUND is the opposite of that of % FOUND.

% ISOPEN: the value of the Boolean cursor that has been opened is true. Otherwise, the value is false.

3) the use of explicit Cursor involves four steps:

A defines the Cursor -- Cursor [Cursor Name [param_name, param_type] IS select xxx from xxxwhere xxx;

B Open the Cursor -- Open [Cursor Name [varialbe_value];

C operation Cursor -- Fetch [Cursor Name];

D Close the Cursor -- Close [Cursor Name];

 

4) use the display cursor to traverse the cyclic cursor:

A) Use a display cursor

I. Declared cursor: divides the storage area. Note that the Select statement is not executed at this time. CURSOR name (parameter list) [Return Value Type] IS Select statement;

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

Iii. Retrieve record �� move the cursor to retrieve a record fetch cursor name into temporary record or attribute type variable;

V. 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;

B) traverse the cyclic cursor

I. 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;

Ii. loop cursor

......

Loop

Fetch cursor name into temporary record or attribute type variable;

Exit when cursor name % notfound;

End loop;

Iii. while Loop

......

Open cursor name

Fetch cursor name into temporary record or attribute type variable;

While cursor name % foundloop

-- Do something

 

Fetch cursor name into temporary record or attribute type variable;

End loop;

......

Close cursor name

 

5) Common explicit Cursor usage:

I. Use a for loop to use cursor:

Declare
Cursor cur is select * from t_user where age = 22;
Userinfo t_user % rowtype;
Begin
For userinfo in cur loop
Exit when cur % notfound;
Dbms_output.put_line ('user id: '| userinfo. id |'-'| 'user name:' | userinfo. username );
End loop;
Exception
When others then
Dbms_output.put_line (sqlerrm );
End;

Ii. Use fetch to use cursor: exp2

Declare
Cursor cur is select * from t_user where age = 22;
Userinfo t_user % rowtype;
Begin
Open cur;
Loop
Exit when cur % notfound;
Fetch cur into userinfo;
Dbms_output.put_line ('user id: '| userinfo. id |'-'| 'user name:' | userinfo. username );
End loop;
Exception
When others then
Dbms_output.put_line (sqlerrm );
Close cur;
End;

1 2 Next Page