Oracle 9i Cursors

A cursor is a data extracted from a datasheet, stored as a temporary table in memory, with a data pointer in the cursor, a first record in the initial state, a fetch statement to move the pointer, a variety of operations on the data in the cursor, and then write the action results back into the datasheet.

Defining cursors

cursors, as a data type, must first be defined with the following syntax.
Cursor cursor name is SELECT statement;
Cursor is the keyword that defines the cursor, and the Select is the Data Table Query command that establishes the cursor.
Connect the database with the Scott user and execute the following Pl/sql program in "Sqlplus Worksheet". This program defines tempsal as a variable that is the same as the Sal field type in the Scott.emps datasheet, mycursor a cursor that is made from the SCOTT.EMP data table that Sal is larger than the tempsal.
The results of the execution are shown in Figure 9.35.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
Tempsal Scott.emp.sal%type;
Cursor MyCursor is
SELECT * FROM Scott.emp
where sal>tempsal;
Begin
tempsal:=800;
Open mycursor;
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Cursordefine.sql.

Open cursor

To use the created cursor, the next step is to open the cursor with the following syntax structure:
The open cursor name;
The process of opening a cursor has the following two steps:
(1) to feed the records of the qualifying record into memory.
(2) Point the pointer to the first record.

Extract cursor Data

To extract data from a cursor, use the FETCH command, which is the following syntax.
Fetch cursor name into variable name 1, variable name 2,......;
Or
Fetch cursor name into record variable name;
Execute the following pl/sql program in "Sqlplus Worksheet", which defines the Cursorrecord variable as the row variable of the cursor MyCursor, and finds the first record of the Sal field greater than 800 in the result of the cursor mycursor. Displays the contents of the Deptno field.
The results of the execution are shown in Figure 9.36.

―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
Tempsal Scott.emp.sal%type;
Cursor MyCursor is
SELECT * FROM Scott.emp
where sal>tempsal;
Cursorrecord Mycursor%rowtype;
Begin
tempsal:=800;
Open mycursor;
Fetch mycursor into Cursorrecord;
Dbms_output.put_line (To_char (Cursorrecord.deptno));
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Cursorfetch.sql.

Close cursor

After you use the cursor, to close the cursor, use the closing command, which is the following syntax:
The close cursor name;

Properties of Cursors

Cursors provide properties that help you write Pl/sql programs, which are used by cursor names [properties], such as Mycursor%isopen, and the primary cursor properties are as follows.
1.%isopen Properties
The property function is to test whether the cursor is open, and to use the FETCH statement if the cursor is not open will prompt for an error.
In sqlplus worksheet, execute the following Pl/sql program, which uses the%isopen property to determine whether the cursor is open. The results of the execution are shown in Figure 9.37.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
Tempsal Scott.emp.sal%type;
Cursor MyCursor is
SELECT * FROM Scott.emp
where sal>tempsal;
Cursorrecord Mycursor%rowtype;
Begin
tempsal:=800;
If Mycursor%isopen Then
Fetch mycursor into Cursorrecord;
Dbms_output.put_line (To_char (Cursorrecord.deptno));
Else
Dbms_output.put_line (' Cursor not open! ');
End If;
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Isopenattribute.sql.

2.%found Properties
The property function is to test whether a previous FETCH statement has a value, a value that returns True, or false.
Perform the following pl/sql programs in the Sqlplus worksheet. The program uses the%found property to determine whether the cursor has data.
The results of the execution are shown in Figure 9.38.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
Tempsal Scott.emp.sal%type;
Cursor MyCursor is
SELECT * FROM Scott.emp
where sal>tempsal;
Cursorrecord Mycursor%rowtype;
Begin
tempsal:=800;
Open mycursor;
Fetch mycursor into Cursorrecord;
If Mycursor%found Then
Dbms_output.put_line (To_char (Cursorrecord.deptno));
Else
Dbms_output.put_line (' No data! ');
End If;
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Foundattribute.sql.

3.%notfound Properties
This property is the inverse of the%found property and is often used to exit the loop.
Perform the following pl/sql programs in the Sqlplus worksheet. The program uses the%notfound property to determine whether the cursor has no data.
The results of the execution are shown in Figure 9.39.

"Matching program Location": 9th Chapter \ Notfoundattribute.sql.

4.%rowcount Properties
This property is used to return the number of data rows for the cursor.
In the code editing area of Sqlplus worksheet, execute the following Pl/sql program that uses the%rowcount property to determine the number of cursor data rows.
The results of the execution are shown in Figure 9.40.

"Matching program Location": 9th Chapter \ Rowcountattribute.sql.
A return value of 0 indicates that the cursor is already open, but the data is not extracted.