An Oracle database Cursor usage Detail

An Oracle database Tutorial cursor usage detailed

1. What is a cursor

The cursor is a kind of pl/sql control structure, can display control to the SQL statement, it is easy to deal with the data of the table.

2. Cursor classification

Display cursors: Declared and named by the programmer

Implicit cursors: Declared for all DML and pl/sql SELECT statements

3, the properties of the cursor:

%found:evaluates to TRUE if the most recent SQL statement affects one or more rows

%notfound: Contrary to the%found

%isopen: Is a Boolean value, true if the cursor is open, or false if the cursor is closed. Sql%isopen is always false for an implicit cursor, because an implicit cursor is opened at the time the DML statement is executed, and closes immediately at the end.

%rowcount:number of records affected by the most recent SQL statement

Note: Dbms_output.put_line (); This is not possible to print a Boolean, workaround

If B Then

Dbms_output.put_line (' b=true ');

End If;

Or:

Declare

b boolean;

Begin

B: = true;

Dbms_output.put_line (case when B then ' true ' else ' false '));

End

For NULL, class first uses NVL () or decode () to process

Display cursors: Requires manual open and close

For example:

DECLARE

CURSOR MyCursor is

SELECT * FROM Dept;

Myrecord Dept%rowtype;

BEGIN

OPEN MyCursor;

FETCH mycursor into Myrecord;

While Mycursor%found loop

Dbms_output. Put_Line (myrecord.deptno| | ' '||   myrecord.dname| | ' '|| MYRECORD.LOC);

FETCH mycursor into Myrecord;

End LOOP;

Close MyCursor;

End;

Note: Before you can perform a while loop, you must first have a fetch ... into operation, no person%found always returns false

Cursors with parameters:

DECLARE

CURSOR mycursor (num varchar2) is

SELECT * from DEPT WHERE deptno=num;

Myrecord Dept%rowtype;

BEGIN

OPEN MyCursor (10);

LOOP

FETCH mycursor into Myrecord;

EXIT when Mycursor%notfound;

Dbms_output. Put_Line (' deptnum= ' | | myrecord.deptno| | ' deptname= ' | | Myrecord.dname);

End LOOP;

Close MyCursor;

End;

For loop action cursor

When you use a For loop to read a cursor, you do not need to display the declaration variable to receive the result, nor do you need to manually open and close the cursor, for example:

DECLARE

CURSOR mycursor (num varchar2) is

SELECT * from DEPT WHERE deptno=num;

BEGIN

For cur in mycursor (a) loop

Dbms_output. Put_Line (' deptnum= ' | | cur.deptno| | ' deptname= ' | | Cur.dname);

End LOOP;

End;

Note: The Pl/sql parameter only needs to give the type, does not need to give the length or the precision.

When you read a cursor value directly to a variable, the number of variables should be the same as the number of columns in the result set to which the cursor points. For example, there are two columns in the result set, so use the fetch .... into the corresponding number of variables should also have two.

DECLARE

D_no number;

D_name VARCHAR2 (10);

CURSOR mycursor (num varchar2) is

SELECT deptno,dname from DEPT WHERE deptno=num;

BEGIN

OPEN MyCursor (10);

FETCH mycursor into D_no,d_name;

LOOP

Dbms_output. Put_Line (d_no| | ' '|| D_name);

FETCH mycursor into D_no,d_name;

EXIT when Mycursor%notfound;

End LOOP;

Close MyCursor;

End;

/

%rowcount initial value is NULL, whenever you use the fetch ... into when a data is fetched from a cursor, the ROWCOUNT value plus 1 does not identify the number of rows in the result set.

For example:

DECLARE

D_name VARCHAR2 (10);

CURSOR MyCursor is

SELECT dname from DEPT;

BEGIN

OPEN MyCursor;

LOOP

FETCH mycursor into D_name;

EXIT when Mycursor%notfound;

Dbms_output. Put_Line (Mycursor%rowcount);

End LOOP;

Close MyCursor;

End;

The result set has 4 rows and the output is: 1 2 3 4

Cursors that can update data

To modify data while using a cursor, you need to add the FOR UPDATE keyword when declaring the cursor.

For example:

DECLARE

D_name VARCHAR2 (20);

CURSOR MyCursor is

SELECT dname from Dept for UPDATE;

BEGIN

OPEN MyCursor;

LOOP

FETCH mycursor into D_name;

EXIT when Mycursor%notfound;

UPDATE Dept SET Dname=rtrim (dname, ' _t ') WHERE current of MyCursor;

End LOOP;

Close MyCursor;

End;

Current of+ cursor name: Gets the row currently pointed to by the cursor

RTRIM (dname, ' _t '): LTRIM, RTRIM implement string filtering (not only remove spaces)

Implicit cursors: A declared cursor is not displayed using declare.

For example:

BEGIN

For cur in (SELECT dname from dept) loop

Dbms_output. Put_Line (Cur.dname);

End LOOP;

End;

1, the cursor containing parameters

Declare
Cursor Cur_my (MV number) is select * FROM person where no<mv;
Begin
For TEM in Cur_my (4) loop
Dbms_output.put_line (' Name: ' | | Tem.name);
End Loop;
End

2. Set reference cursors
Declare
Temp_row Person%rowtype;
Type my_type is REF CURSOR;
Cur_my My_type;
Begin
Open cur_my for ' select * from person ';
Loop
Fetch cur_my into Temp_row;
Exit when Cur_my%notfound;
Dbms_output.put_line (' Name: ' | | Temp_row.name);
End Loop;
Close cur_my;
End

3. For Loop loop cursor

DECLARE
V_ID Integer;
V_name VARCHAR2 (50);
V_age Integer;
Cursor Cur_mycursor is select Id,name,age from Users;
BEGIN
For temp in Cur_mycursor loop
V_ID: =temp.id;
V_name: =temp.name;
V_age: =temp.age;
Dbms_output.put_line (' ID: ' | | v_id| | ' Name: ' | | v_name| | ' Age: ' | | V_age);
End Loop;
/**dbms_output.put_line (' All records: ' | | cur_mycursor%rowcount| | ' Article ');*/
End;

4. Standardize Loop cycle cursors

DECLARE
    v_id Integer
    v_name varchar2 (x)
    v_ Age Integer;
    cursor cur_mycursors is select Id,name,age from Users;
BEGIN
     OPEN cur_mycursors
       dbms_output.put _line (' All records: ' | | cur_mycursors%rowcount| | ' Article ');
     LOOP
       FETCH cur_mycursors into v_id,v_name,v_ Age
       dbms_output.put_line (' ID: ' | | v_id| | ' Name: ' | | v_name| | ' Age: ' | | V_age);
       if  cur_mycursors%notfound THEN
             EXIT;
       End IF;
     End LOOP;
     dbms_output.put_line (' All records: ' | | cur_mycursors%rowcount| | ' Article ');
     Close cur_mycursors;
End;