Use of Oracle Note nine, PL/SQL cursors

--Demonstration of implicit cursors, automatic system declaration, auto-open, auto-use and auto-close

Begin

     Update emp Set sal = 1000;

     Dbms_output.put_line (' Number of rows affected: ' | | sql%rowcount);

End

Rollback

/* How to use cursors:

First step: Declaring a cursor

Step Two: Open cursors

Step three: Use cursors for loop operations

Fourth step: Close the cursor */

--Normal cursor, the cursor itself is a variable

Declare     

     --The following line of code declares a cursor

     Cursor Mycur is a SELECT * from emp where deptno = 20;

     Emprow Emp%rowtype;

Begin

     Open mycur; --Open cursor

     Loop

         Fetch mycur into Emprow; --Place the record indicated by the cursor in the variable

         Exit  When (mycur%notfound)--Exits the loop when the cursor does not point to a row

         Dbms_output.put_line (' Name: ' | | emprow.ename | | ' Salary: ' | | Emprow.sal);

     End Loop;

     Close mycur;  --Close cursor

End

--Simple cursors, column operations

Declare

       EmpName Emp.ename%type;

       Empsal Emp.sal%type;

       Cursor Mycur is a select ename,sal from emp where deptno = 30;

Begin

     Open mycur;

     Loop

         Fetch mycur into empname,empsal;

         Exit when Mycur%notfound;

         Dbms_output.put_line (' Name: ' | | empname | | ' Wages ' | | Empsal);

     End Loop;

End

--Simple cursors, column operations

Declare

  Is

  SELECT * FROM dept;

  Vdept_row_record C%rowtype;

Begin

  Open C;

  Fetch C into Vdept_row_record;

  Dbms_output.put_line (Vdept_row_record.dname);

  Close C;

End

--when Loop Cursors

Declare

  Is

  SELECT * FROM dept;

  Vdept_row_record C%rowtype;

Begin

  Open C;

  Loop

       Fetch C into Vdept_row_record;

       Exit when (C%notfound);

       Dbms_output.put_line (Vdept_row_record.dname);

  End Loop;

  Close C;

End

--while Loop Cursors

Declare

  Cursor C

  Is

  SELECT * FROM dept;

  Vdept_row_record C%rowtype;

Begin

  Open C;

  Fetch C into Vdept_row_record;

  while (C%found) loop

    Dbms_output.put_line (Vdept_row_record.dname);

    Fetch C into Vdept_row_record;

  End Loop;

  Close C;

End

--for Loop Cursors

Declare

  Cursor C

  Is

  SELECT * FROM dept;

  Vdept_row_record C%rowtype;

Begin

  For Vdept_row_record in C loop

    Dbms_output.put_line (Vdept_row_record.dname);

  End Loop;

End

--With parameter cursors

Declare

  Cursor C (ssal emp.sal%type, Sempno emp.empno%type)

  Is

  SELECT * from emp where Sal >= ssal and empno > sempno;

Begin

  For Record_data in C (2500, 6666) loop

      Dbms_output.put_line (Record_data.ename);

  End Loop;

End

--update Cursors

Declare

  Cursor C (ssal emp2.sal%type)

  Is

  SELECT * from EMP2 where Sal >= ssal for update;

Begin

  For Record_data in C (2500) loop

    if (Record_data.sal <) Then

      Update EMP2 Set sal = sal + 3 where current of C;

      Dbms_output.put_line (Record_data.ename);

    elsif (record_data.sal =) Then

      Update EMP2 Set sal = sal-3 where CURRENT of C;

      Dbms_output.put_line (Record_data.ename);

    End If;

  End Loop;

End

--Reference cursors cannot use the syntax of a circular cursor

--Reference cursors cannot be deleted and modified

--A reference cursor is a data type that must be declared with a variable

--A weakly typed reference cursor that does not specify the type of data rows that the cursor will fetch

Declare

       Type my_cur_type is REF CURSOR;

       Mycur my_cur_type;--declaring variables

       which varchar2 (10);

       Deptrow Dept%rowtype;

       Emprow Emp%rowtype;

Begin

     Which: = ' & Please select dept or EMP ';

     if (which = ' dept ') then

        Open Mycur for SELECT * FROM dept;

        Loop

            Fetch mycur into Deptrow;

            Exit when (Mycur%notfound);

            Dbms_output.put_line (Deptrow.deptno | | "  | | | deptrow.dname);

        End Loop;

     elsif (which = ' emp ') then

        Open Mycur for SELECT * from EMP;

        Loop

            Fetch mycur into Emprow;

            Exit when (Mycur%notfound);

            Dbms_output.put_line (Emprow.empno | | "  | | | emprow.ename);

        End Loop;

     End If;

     Close mycur;

End

--A strongly typed reference cursor, which specifies the type of data row that the cursor will fetch, only the record or%rowtype type

-For example: Return number is wrong, return emp.ename%type is also wrong

Declare

       Type Mycurtype is REF CURSOR return emp%rowtype;

       Mycur mycurtype;--declaring variables

       Emprow Emp%rowtype;

Begin

        Open Mycur for SELECT * from EMP;

        Loop

            Fetch mycur into Emprow;

            Exit when Mycur%notfound;

            Dbms_output.put_line (Emprow.empno | | "  | | | emprow.ename);

        End Loop;

        Close mycur;

End

Use of Oracle Note nine, PL/SQL cursors