The cursor is performed as follows:
Parse resolution
Bind bind
Open opens
Execute Execute
Fetch callback
Close off
1. Write your own first cursor pl/sql
Declare
Cursor c_s is select * from User_tables;
Begin
Open c_s; --Open cursor
Close c_s;--Closing Cursors
End
4 properties of a cursor%found,%notfound,%rowcount,%isopen
Returns true if the 1.%found cursor has records or false
Declare
Cursor c_s is select * from User_tables;
CC C_s%rowtype;
Begin
Open c_s; --Open cursor
Fetch c_s into CC;
While C_s%found loop
Fetch c_s into CC;
End Loop;
Close c_s;--Closing Cursors
End
Returns true if the 2.%notfound cursor is not logged or false (personal feeling a bit superfluous)
Declare
Cursor c_s is select * from User_tables;
CC c_s%rowtype;//CURSOR variable
Begin
Open c_s; --Open cursor
Fetch c_s into CC;
While C_s%found loop
Exit when C_s%notfound;
End Loop;
Close c_s;--Closing Cursors
End
3.%rowcount returns the number of records retrieved by the cursor
Declare
Cursor c_s is select * from User_tables;
CC C_s%rowtype;
Begin
Open c_s; --Open cursor
Fetch c_s into CC;
While C_s%found loop
Dbms_output.put_line (C_s%rowcount);
End Loop;
Close c_s;--Closing Cursors
End
4.%isopen returns True if the cursor is open or false
Declare
Cursor c_s is select * from User_tables;
Begin
If C_s%isopen Then
Dbms_output.put_line (' cursor is open ');
Else
Open c_s; --Open cursor
End If;
Close c_s;--Closing Cursors
End
Cursor parameters
Declare
Cursor c_s (cs_id number) is a select * from admin id=cs_id;
Begin
Open c_s (10); --Open a cursor with parameters
Close c_s;--Closing Cursors
End
For update in Cursors
Declare
Cursor c_s is select ID from admin
Lock ID column for update of ID//query
Begin
Open c_s;
commit;//commit a release lock or you can use rollback
Close c_s;
End
where cursor in the cursor
UPDATE table_name SET set_clause WHERE CURSOR of cursor_name; Update the record that the cursor points to
DELETE from table_name WHERE CURSOR of cursor_name; Deletes the record that the cursor points to
REF CURSOR type in a cursor
TYPE c_s is REF CURSOR retrun table%rowtype; This form is strongly typed and is defined as a row type at the time of declaration
Type c_s is REF cursor;//weak type is not associated with a record's data type
Example:
Declare
TYPE c_s is REF CURSOR retrun table%rowtype;
TYPE C_s2 is REF CURSOR;
Var_cs c_s;//declared as a type of a cursor variable
Begin
OPEN c_s for SELECT * from admin;
Close c_s;
End