CURSOR is also called a CURSOR. It is often used in relational databases. In PLSQL programs, you can use CURSOR and SELECT to query and read data in tables or views row by row.
CURSOR is also called a CURSOR. It is often used in relational databases. In PL/SQL programs, you can use CURSOR and SELECT to query and read data in tables or views row by row.
Oracle cursors include display cursors and implicit cursors.
Explicit Cursor: The Cursor defined in the PL/SQL program for query is called a display Cursor.
Implicit Cursor: A Cursor automatically allocated by the Oracle system when UPDATE/DELETE statements are not defined in PL/SQL programs and used in PL/SQL.
1. Display cursor
1. Procedure
(1) define (2) Open (3) Use (4) Close
2. Demo
First, create the test table STUDENT. The script is as follows:
The Code is as follows:
Create table "STUDENT "(
"STUNAME" VARCHAR2 (10 BYTE ),
"STUNO" VARCHAR2 (4 BYTE ),
"AGE" NUMBER,
"GENDER" VARCHAR2 (2 CHAR)
)
(1). Use the WHILE loop to process the cursor
Create or replace PROCEDURE PROC_STU1
BEGIN
-- Display cursor usage, while loop
Declare
-- 1. Define the cursor named cur_stu
Cursor cur_stu is
Select stuno, stuname from student order by stuno;
-- Define variables to store the data retrieved by the cursor
V_stuno varchar (4 );
V_stuname varchar (20 );
Begin
-- 2. Open cur_stu
Open cur_stu;
-- 3. Extract the current row of the cursor and store it in the variable.
Fetch cur_stu into v_stuno, v_stuname;
While cur_stu % found -- the cursor indicates that there are data rows, and the loop continues.
Loop
-- Print the result
Dbms_output.PUT_LINE (v_stuno | '->' | v_stuname );
-- Continue to extract the current row pointed by the cursor into the variable
Fetch cur_stu into v_stuno, v_stuname;
End loop;
Close cur_stu; -- 4. close the cursor
End;
END PROC_STU1;
(2). Use IF... ELSE instead of the WHILE loop to process the cursor
Create or replace PROCEDURE PROC_STU2
BEGIN
-- Display cursor usage, use if to judge
Declare
-- 1. Define the cursor named cur_stu
Cursor cur_stu is
Select stuno, stuname from student order by stuno;
-- Define variables to store the data retrieved by the cursor
V_stuno varchar (4 );
V_stuname varchar (20 );
Begin
-- 2. Open cur_stu
Open cur_stu;
-- 3. Extract the current row of the cursor and store it in the variable.
Fetch cur_stu into v_stuno, v_stuname;
Loop
If cur_stu % found then -- if the cursor cur_stu indicates that there are data rows
-- Print the result
Dbms_output.PUT_LINE (v_stuno | '->' | v_stuname );
-- Continue to extract the current row pointed by the cursor into the variable
Fetch cur_stu into v_stuno, v_stuname;
Else
Exit;
End if;
End loop;
Close cur_stu; -- 4. close the cursor
End;
END PROC_STU2;
(3). Use the FOR loop to process the cursor
Create or replace PROCEDURE PROC_STU3
BEGIN
-- Display cursor usage, use for Loop
Declare
-- Defines the 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 );
-- Perform implicit check in a loop % notfound
End loop;
-- Automatically close the cursor
End;
END PROC_STU3;
(4). Usually use exit when to process the cursor
Create or replace
PROCEDURE PROC_STU1_1
BEGIN
-- Display cursor usage, use exit when Loop
Declare
-- 1. Define the cursor named cur_stu
Cursor cur_stu is
Select stuno, stuname from student order by stuno;
-- Define variables to store the data retrieved by the cursor
V_stuno varchar (4 );
V_stuname varchar (20 );
Begin
-- 2. Open cur_stu
Open cur_stu;
Loop
-- 3. Extract the current row of the cursor and store it in the variable.
Fetch cur_stu into v_stuno, v_stuname;
Exit when cur_stu % notfound; -- the cursor indicates that there are data rows, and the loop continues.
-- Print the result
Dbms_output.PUT_LINE (v_stuno | '->' | v_stuname );
End loop;
Close cur_stu; -- 4. close the cursor
End;
END PROC_STU1_1;
Ii. Implicit cursor
1. Demo
Create or replace PROCEDURE PROC_STU4
BEGIN
-- Implicit cursor usage
Update student set stuname = 'zhang yanguang 'where stuno = '000000 ';
-- Insert a new record if the update does not match
If SQL % NOTFOUND then
Insert into student (STUNO, STUNAME, AGE, GENDER)
Values ('000000', 'zhang Yangguang ', 18, 'male ');
End if;
END PROC_STU4;
2. Description
All SQL statements are executable within the context zone because a cursor points to the context zone.
The SQL cursor, unlike the actual cursor, does not need to be opened or closed in PL/SQL, but is executing UPDATE,
DELETE is enabled and disabled automatically.
In the preceding example, the SQL % NOTFOUND cursor attribute is used to determine whether to insert a new record based on the execution result of the UPDATE statement.
