Introduction to Oracle cursor usage

Oracle cursor usage

The following example describes the complete declaration, opening, and extracting of the closure operation and the passing of the cursor parameters.

 

Declare
Deptrec Dept % Rowtype;
Dept_name Dept. dname @ Type ;
Dept_loc Dept. Loc % Type;
Cursor C1 Is

Select Dname, Loc From Dept
Where Deptno <= 30 ;
Cursor C2 (dept_no Number   Default   10 ) Is  
Select Dname, Loc
From Dept
Where Deptno <= Dept_no;
Cursor C3 (dept_no Number   Default   10 )
Return Dept % Rowtype
Is
Select   *   From Dept
Where Deptno <= Dept_no;
Begin
Open C1;
Open C2 ( 30 );
Loop
-- Extract data from C2 cursor cyclically
Fetch C2 Into Dept_name, dept_loc;
Exit   When C2 % Notfound
Dbms_output.put_line (dept_name | '' | Dept_loc );
End Loop;
Open C3 (dept_no > = 20 );
Loop
-- Extract and process data in C3 cursor
Fetch C3 Into Deptrec;
  Exit   When C3 % Notfound;
Dbms_output.put_line (deptrec. deptno | ' : ' | Deptrec. dname );
End Loop;
Close C1;
Close C2;
Close C3;
End ;

Cursor For Loop

 

Declare
Cursor C1 (dept_no Number   Default   10 ) Is
Select Dname, Loc From Dept
Where Deptno <= Dept_no;
Begin

Dbms_output.put_line ( ' The dept_no value is 30. ' );
For C1_rec In C1 ( 30 ) Loop
Dbms_output.put_line (c1_rec.dname | ' : '   | C1_rec.loc );
End Loop;

Dbms_output.put_line (CHR ( 10 ) | ' : ' | The default value is 10: ' );


For c1_rec in C1 Loop
Dbms_output.put_line (c1_rec.dname | ' : ' | C1_rec.loc );
End Loop

End;

In addition, PL/SQL allows subqueries IN THE for loop to implement the cursor function.
Begin
For c1_rec in (select dname, LOC from Dept) loop
Dbms_output.put_line (c1_rec.dname | ' : ' | C1_rec.loc );
End loop;
End;

Cursor property

% Isopen % found % notfound % rowcount

Declare cursor variable
Type ref_type_name is ref cursor [return return_type]

For example:

Declare
Type deptrecord Is Record
(
Deptno Dept. deptno % Type,
Dname Dept. dname % Type,
Loc Dept. Loc % Type
);

Type deptcurtype Is Ref Cursor   Return Dept % Rowtype;
Type deptcurtyp1 Is Ref Cursor   Return Deptrecord;
Type curtype Is Ref Cursor ;

Dept_c1 deptcurtype;
Dept_c2 deptcurtyp1;
CV curtype;

--------------------

Define dynamic variables and PL/SQL records

Define the PL/SQL variable syntax of the column type
Variable_name table_name.column_name % type;

Define a record with the same field as the row in the table
Record_name table_name % rowtype

Define a record with the same fields as the columns in the cursor
Record_name cursor_name % rowtype

PL/SQL records
Type record_type_name is
(Field_name1 field_1_type,
Field_name2 field_2_tupe,
...
);