Oracle Middle-scale use instance __oracle

I. Introduction TO Cursors:

A cursor is a control structure in a pl&sql. Can be divided into explicit and implicit cursors. Pl&sql creates an implicit cursor for each SELECT statement. But when we need to process more than one piece of data, we need to create an explicit cursor. Note: Cursors are not schema objects.

Second, several common properties of cursors:

1,%found

--To determine if there is any data in the cursor, if so, return true, or false.

2,%notfound

--Contrary to the%found

3,%isopen

--Determine if the cursor is open

4,%rowcount

--Records the number of records that have been fetched from the cursor

III. Application Examples of cursors:

1, the use of%found properties

DECLARE
CURSOR Mycur is
SELECT * from student;
Myrecord Student%rowtype;
BEGIN
OPEN mycur;
FETCH mycur into Myrecord;
While Mycur%found loop
Dbms_output. Put_Line (myrecord.stuno| | ', ' | | Myrecord.stuname);
FETCH mycur into Myrecord;
End LOOP;
Close mycur;
End;

2, the use of%notfound properties:

DECLARE
CURSOR Cur_para (id varchar2) is
SELECT stuname from student WHERE stuno=111;
T_name Student.stuname%type;
BEGIN
OPEN Cur_para (111);
LOOP
FETCH Cur_para into T_name;
EXIT when Cur_para%notfound;
Dbms_output. Put_Line (T_name);
End LOOP;
Close Cur_para;
End;

3, for the special use of the middle-loop:

Sql> DECLARE
2 CURSOR Cur_para (ID varchar2) is
3 SELECT stuname from student WHERE Stuno=id;
4 BEGIN
5 Dbms_output. Put_Line (' * * * *);
6 for cur in Cur_para (' ") LOOP
7 Dbms_output. Put_Line (Cur.stuname);
8 End LOOP;
9 End;

4, the use of%isopen properties:

Sql> DECLARE
2 T_name Student.stuname%type;
3 CURSOR cur (id varchar2) is
4 SELECT stuname from student WHERE Stuno=id;
5 BEGIN
6 IF Cur%isopen THEN
7 Dbms_output. Put_Line (' The cur has been opened ');
8 ELSE
9 OPEN cur (' 111 ');
Ten end IF;
One FETCH cur into t_name;
Close cur;
Dbms_output. Put_Line (T_name);
End;

5, the use of%rowcount properties:

Sql> DECLARE
2 T_name VARCHAR2 (10);
3 CURSOR Mycur is
4 SELECT stuname from student;
5 BEGIN
6 OPEN Mycur;
7 LOOP
8 EXIT when Mycur%notfound OR Mycur%notfound is NULL;
9 Dbms_output. Put_Line (' *****rowcount**** ' | | Mycur%rowcount);
Ten end LOOP;
One close mycur;
End;