Oracle Database cursor

Cursor Concept

A cursor is a memory Workspace of SQL, which is defined by the system or user as a variable.

A cursor is used to temporarily store data blocks extracted from the database.

Why use a cursor? The data in the database is stored in the disk. The cursor transfers the data from the disk to the computer memory for processing. The processing result is displayed or written back to the database, this can improve data processing efficiency because frequent disk data exchange will reduce the efficiency.

Two types of cursors are available: Implicit cursors and display cursors.

Implicit cursor: for SELECT... INTO... statements (only one row of records can be extracted from the database at a time) and DML (data manipulation language, that is, INSERT statements, UPDATE statements, and DELETE statements), the system uses an implicit cursor.

Display cursor: for SELECT statements with more than one record in the result set, the programmer needs to define a display cursor.

Implicit cursor

Four attributes of implicit cursor

% FOUNT	The value true indicates that a single row query statement or DML statement is successfully operated.
% NOTFOUNT	Opposite to % Found
% ISOPEN	True in DML execution and false after execution
% ROWCOUNT	Indicates the number of rows of Data successfully executed by the DML statement.

An example of using a hidden cursor:

1. SETSERVEROUTPUTON
2. BEGIN
3. UPDATEEmpSETSal = sal + 100WHEREEmpno = 1234;
4. If SQL % FOUNDTHEN
5. DBMS_OUTPUT.PUT_LINE ('Employee salary modified successfully! ');
6. COMMIT;
7. ELSE
8. DBMS_OUTPUT.PUT_LINE ('Employee salary modification failed! ');
9. ENDIF;
10. END;

Display cursor

Display four attributes of a cursor

% FOUNT	True if the latest FETCH statement returns a record; otherwise, false.
% NOTFOUNT	Opposite to % Found
% ISOPEN	True when the cursor is opened; otherwise, false.
% ROWCOUNT	Obtain the number of rows returned by the FETCH statement.

The procedure for displaying a cursor is as follows:

Declared cursor

Open cursor

Extract data

Close cursor

Declared cursor:

CURSOR name [(parameter 1 Data Type [, parameter 2 data type...])] is select statement;

Parameter is optional

Open cursor:

OPEN cursor name [(actual parameter 1 [, actual parameter 2...])];

The parameter is an optional part. When a cursor is opened, the result set is sent to the cursor workspace.

Extract data:

FETCH cursor name INTO variable name 1 [, variable name 2...];

Or

FETCH cursor name INTO record variable;

After a cursor is opened, a pointer points to the data zone. The FETCH statement returns the data of a row pointed to by the pointer at a time. To return multiple rows, repeated execution is required. You can use a loop statement. The control loop can be performed by judging the attributes of the cursor.

The first FETCH format: the variable name is used to receive data from the cursor, which needs to be defined in advance. The number and type of variables must be the same as the number and type of field variables in the SELECT statement.

The second FETCH format: To extract a row of data to the record variable at a time, you need to use % ROWTYPE to define the record variable in advance. This method is convenient to use and does not have to define or use multiple variables separately. Method for defining record variables: variable name table name | cursor name % ROWTYPE; the table must exist and the cursor name must be defined first.

1 2 Next Page