Understanding and using Oracle-based cursors

For cursors, you first need to know the definition of the cursor.

A cursor, an area in memory that holds the result set of a Select

Cursors are used to process multiple rows of records retrieved from the database (using the SELECT statement). Cursors allow the program to process and iterate through the entire set of records returned by an index, one at a time.

In the database, there are two kinds of cursors:

First, display cursors ( need to be clearly defined )

Display cursors are used to process SELECT statements that return multiple rows of data, with cursor names passing through the cursor ... The IS statement displays the assignment to the SELECT statement.

The four steps for displaying cursors are handled in PL/sql:

1) declaring cursor: Cursor is SELECT statement

2) Open the cursor for the query: Open cursor name

3) Get the result into the PL/SQL variable:

FETCH cursor name into list_of_variables;

FETCH cursor name into Pl/sql_record;

4) Close cursor: Close cursor name

Note: When declaring a cursor, the SELECT statement cannot contain an into clause. When you use the display cursor, the INTO clause is part of the FETCH statement.

Second, implicit cursors
All implicit cursors are assumed to return only one record.
When using implicit cursors, users do not need to declare, open, and close. PL/SQL implicitly opens, processes, and then turns off the cursor.
For example:
.......
SELECT Studentno,studentname

Into Curstudentno,curstudentname

From Studentrecord

WHERE name= ' GG ';

The above cursor automatically opens, assigns the relevant value to the corresponding variable, and then closes. After execution, the value is already in the PL/SQL variable curstudentno,curstudentname.
2. Implicit cursors
Result sets produced by a single SQL statement
To represent an implicit cursor with the keyword SQL
4 attribute%rowcount affects the number of rows of the record integer
%found affects the record true
%notfound does not affect the record true
%isopen whether the Boolean value is turned on is always false
Multiple SQL statements implicit cursor SQL always refers to the result of the last SQL statement
Primarily used on UPDATE and DELETE statements


practical operations and examples:

(1) For loop cursor (a common cursor)

--<1> Defining cursors
--<2> Defining a cursor variable
--<3> Use this cursor with a for loop

--forward cursors can only be moved in one Direction
-High efficiency
Declare
--type definition
Cursor CC is select Empno,ename,job,sal
From emp where job = ' MANAGER ';
--Define a cursor variable
Ccrec Cc%rowtype;
Begin
--for Cycle
For Ccrec in CC loop
Dbms_output.put_line (ccrec.empno| | ' -' | | ccrec.ename| | ' -' | | ccrec.job| | ' -' | | Ccrec.sal);
End Loop;
End
(2) Fetch cursors
--Must be explicitly opened and closed when used
Declare
--type definition
Cursor CC is select Empno,ename,job,sal
From emp where job = ' MANAGER ';
--Define a cursor variable
Ccrec Cc%rowtype;
Begin
--Open cursor
Open cc;
--loop Cycle
Loop
--Fetch a row of data into the Ccrec
Fetch CC into CCREC;
--Determine if the value is extracted and exit without taking a value
--Take the value Cc%notfound is False
--No value Cc%notfound is true
Exit when Cc%notfound;
Dbms_output.put_line (ccrec.empno| | ' -' | | ccrec.ename| | ' -' | | ccrec.job| | ' -' | | Ccrec.sal);
End Loop;
--Close cursor
Close cc;
End
4 Properties of the cursor
%notfound fetch whether reference data no true refers to false
%found fetch whether reference data has true not mentioned false
%rowcount number of records that have been fetched
%isopen whether the Boolean cursor is open
(3) Parametric cursors
Export department manager's name in order of department number
Declare
--Department
Cursor C1 is a select Deptno from dept;
--Parameter cursor c2, when defining parameters
--can only specify type, cannot specify length
--The parameter can only appear to the right of the SELECT statement = Sign
Cursor C2 (no number,pjob varchar2) is a select emp.* from emp
where Deptno = no and job=pjob;
C1rec C1%rowtype;
C2rec C2%rowtype;
--Specify the length when defining the variable
V_job varchar2 (20);
Begin
--Department
For C1rec in C1 loop
--parameters are used in cursors
For C2rec in C2 (C1rec.deptno, ' MANAGER ') loop
Dbms_output.put_line (c1rec.deptno| | ' -' | | C2rec.ename);
End Loop;
End Loop;
End
(4) referencing cursors/dynamic cursors
--The SELECT statement is dynamic
Declare
--Define a type (ref CURSOR) Weak type
Type cur is REF CURSOR;
--Strongly typed (the returned result set is required)
Type CUR1 is REF CURSOR return emp%rowtype;
--Define a variable of type REF CURSOR
Cura cur;
C1rec Emp%rowtype;
C2rec Dept%rowtype;
Begin
Dbms_output.put_line (' output employees ');
Open cura for SELECT * from EMP;
Loop
Fetch cura into C1rec;
Exit when Cura%notfound;
Dbms_output.put_line (C1rec.ename);
End Loop;
Dbms_output.put_line (' output department ');
Open cura for SELECT * from dept;
Loop
Fetch cura into C2rec;
Exit when Cura%notfound;
Dbms_output.put_line (C2rec.dname);
end Loop;
Close Cura;
End;

Understanding and using Oracle-based cursors