Oracle Cursor Usage Summary _oracle

Source: Internet
Author: User
Tags oracle cursor
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.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.