An Oracle cursor is divided into a display cursor and an implicit cursor.
Display cursor (Explicit Cursor): A cursor defined in a PL/SQL program, used for querying, is called a display cursor.
Implicit cursors (implicit Cursor): A cursor that is automatically allocated by an Oracle system when a UPDATE/DELETE statement is defined in a non-Pl/sql program and is used in Pl/sql.
one. Display cursors
1. Use steps
(1) Definition (2) open (3) use (4) to close
2. Use Demo
First, create the test table student, as follows:
Copy Code code as follows:
CREATE TABLE "STUDENT" (
"Stuname" VARCHAR2 (BYTE),
"Stuno" VARCHAR2 (4 BYTE),
"Age" number,
"GENDER" VARCHAR2 (2 CHAR)
)
(1). Using a while loop to process a cursor
Create or replace PROCEDURE PROC_STU1 as
BEGIN
--Display cursor usage, using while loop
Declare
--1. Defines a cursor, named Cur_stu
Cursor Cur_stu is
Select Stuno,stuname from student order by Stuno;
--Define variables to hold the data that the cursor takes out
V_stuno varchar (4);
V_stuname varchar (20);
Begin
--2. Open Cursor Cur_stu
Open cur_stu;
--3. Store the current row of the cursor in a variable
Fetch cur_stu into v_stuno,v_stuname;
While Cur_stu%found--the cursor refers to a data row, the loop continues
Loop
--Print results
Dbms_output. Put_Line (v_stuno| | ' -> ' | | V_stuname);
--Continue to remove the current row that the cursor refers to and place it in the variable
Fetch cur_stu into v_stuno,v_stuname;
End Loop;
Close Cur_stu; --4. Close cursor
End
End PROC_STU1;
(2). Use IF. else instead of while loop processing cursors
Create or replace PROCEDURE PROC_STU2 as
BEGIN
--Display cursor usage, using if judgment
Declare
--1. Defines a cursor, named Cur_stu
Cursor Cur_stu is
Select Stuno,stuname from student order by Stuno;
--Define variables to hold the data that the cursor takes out
V_stuno varchar (4);
V_stuname varchar (20);
Begin
--2. Open Cursor Cur_stu
Open cur_stu;
--3. Store the current row of the cursor in a variable
Fetch cur_stu into v_stuno,v_stuname;
Loop
If Cur_stu%found then--if the cursor cur_stu refers to a row of data
--Print results
Dbms_output. Put_Line (v_stuno| | ' -> ' | | V_stuname);
--Continue to remove the current row that the cursor refers to and place it in the variable
Fetch cur_stu into v_stuno,v_stuname;
Else
Exit
End If;
End Loop;
Close Cur_stu; --4. Close cursor
End
End PROC_STU2;
(3). Using a For loop to process a cursor
Create or replace PROCEDURE PROC_STU3 as
BEGIN
--Display cursor usage, using for loop
Declare
--Defines a cursor, named Cur_stu
Cursor Cur_stu is
Select Stuno,stuname from student order by Stuno;
Begin
For Stu in Cur_stu
Loop
Dbms_output. Put_Line (stu.stuno| | ' -> ' | | Stu.stuname);
--loop do hidden check%notfound
End Loop;
--Auto Close cursor
End
End PROC_STU3;
(4). Common use of exit when processing cursors
Create or replace
PROCEDURE Proc_stu1_1 as
BEGIN
--Display cursor usage, using exit when loop
Declare
--1. Defines a cursor, named Cur_stu
Cursor Cur_stu is
Select Stuno,stuname from student order by Stuno;
--Define variables to hold the data that the cursor takes out
V_stuno varchar (4);
V_stuname varchar (20);
Begin
--2. Open Cursor Cur_stu
Open cur_stu;
Loop
--3. Store the current row of the cursor in a variable
Fetch cur_stu into v_stuno,v_stuname;
Exit when Cur_stu%notfound; --the cursor refers to a data row, then the loop continues
--Print results
Dbms_output. Put_Line (v_stuno| | ' -> ' | | V_stuname);
End Loop;
Close Cur_stu; --4. Close cursor
End
End Proc_stu1_1;
Two. Implicit cursors
1. Use Demo
Create or replace PROCEDURE Proc_stu4 as
BEGIN
--An implicit cursor uses
Update student set stuname= ' Zhang Yanquang ' where stuno= ' 1104 ';
--Inserts a new record if the update does not match
If Sql%notfound Then
INSERT into student (Stuno,stuname,age,gender)
VALUES (' 1104 ', ' Zhang Yanquang ', 18, ' Male ');
End If;
End Proc_stu4;
2. Notes
All SQL statements are executable within the context area because there is a cursor pointing to the context area, and this cursor is
SQL cursors, unlike reality cursors, where SQL cursors do not need to be turned on and off in Pl/sql, but rather in the execution of update,
Delete is automatically turned on and off.
In the example above, the Sql%notfound cursor property is used to determine the execution of the UPDATE statement to determine whether a new record needs to be inserted.
