How Oracle database cursors are used

Cursors are divided into implicit cursors and display cursors.

One, implicit cursors
There is an implicit cursor attached to the SELECT * into and DML operations (update, delete, insert).

Attributes of an implicit cursor

Sql%rowcount integer represents the number of rows of data that a DML statement successfully executes
The Sql%found Boolean value of True represents the success of the INSERT, delete, UPDATE, or single-line query operation
The Sql%notfound Boolean is the opposite of the return value of the Sql%found property
Sql%isopen Boolean DML is true during execution, false after end

When a system uses an implicit cursor, it is possible to understand the state and result of the operation through the properties of the implicit cursor, and then the process of the control program. Implicit cursors can be accessed using the first name SQL, but note that the SQL cursor name always accesses only the cursor properties of the previous DML operation or the single row select operation. Therefore, you typically use the SQL cursor name to access the property immediately after the operation has just been done.

Example:

--/
DECLARE
V_emp Emp%rowtype;
BEGIN
Delete from emp where empno = 9999;
IF Sql%notfound THEN
Dbms_output.put_line (' Implicit cursor property is NotFound ');
End IF;
Dbms_output.put_line (' Delete no record no exception ');
End;
/

Run Result:

An implicit cursor property is NotFound
Delete no record no exception

Two, explicit cursors
1, the use of cursors have 4 steps:
1) Declaring cursors
CURSOR cursor Name (parameter 1, Parameter 2, ..., parameter n) is SELECT statement;

2) Open cursor
The OPEN cursor name;

3) Extracting data
FETCH cursor name into variable 1, variable 2 ...
Or
FETCH cursor name into record variable;

4) Close cursor
The close cursor name;

Example:

--/
DECLARE
CURSOR C_emp is a select * from EMP; --Declaring a cursor
V_emp C_emp%rowtype;
--v_emp Emp%rowtype;
BEGIN
OPEN c_emp; --Open cursor
LOOP
FETCH c_emp into v_emp; --Extracting data
EXIT when C_emp%notfound;
Dbms_output.put_line (V_emp.empno);
End LOOP;
Close c_emp; --Close cursor
End;
/

Description
1. On the judgment of withdrawal cycle
If you write in the following way:

EXIT when C_emp%notfound;
FETCH c_emp into v_emp; --Extracting data

The values in the v_emp are printed more than once.

C_emp to the last record, and then loop again, at this point to determine whether c_emp is empty, c_emp is not empty, and then fetch the data, at this time Guang is empty, v_emp record unchanged, so will print more than one line.

2 about the definition of the record variable
V_emp C_emp%rowtype;
And
V_emp Emp%rowtype;
Either way.

2. Cursor loop
1 The first way is to do all the 4 steps of the cursor

2) The second way, using the For loop traversal

--/
DECLARE
CURSOR C_emp is a select * from EMP;
BEGIN
For v_emp in C_emp
LOOP
Dbms_output.put_line (V_emp.ename);
End LOOP;
End;
/

In this applet, only the declaration of the cursor, the for loop omitted the opening of the cursor, data extraction, shutdown. The declaration of a record variable is also omitted.

3 The third way, simpler for loop traversal

--/
BEGIN
For re in SELECT * from emp
LOOP
Dbms_output.put_line (Re.ename);
End LOOP;
End;
/

In this applet, the declaration, opening, data extraction, and shutdown of the cursor are omitted. The declaration of a record variable is also omitted.

3. Explicit Cursor Properties
Although you can use the previous form to obtain cursor data, it is a more flexible method to use some of its properties for structural control after the cursor definition. The properties of an explicit cursor are shown below.

How to use:
Cursor Name% property

Properties of an explicit cursor

%rowcount integer Gets the number of data rows returned by the FETCH statement
%found Boolean most recent FETCH statement returns a row of data is true, otherwise false
The%notfound Boolean is the opposite of the return value of the%found property
%isopen Boolean cursor is open value is true or False

Summary:
Cursors allow you to declare the cursor first, and then use a simple for loop to traverse the data.