--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