8,127
Cursor
===========
Implicit Cursor--sql Returns Single Line. Created By Oracle Server.
Explicit Cursor--sql Retuns Multiple Line Of Record. Created By User.
----------
Cursor Life Cycle
----------
Declare-->open-->fetch-->check Last Record-->close
Basic Syntax
---------------------------
Expand|Select|Wrap|Line Numbers
- Declare
- Cursor Cursorname [(param1,param2,....)]
- Is Select Statment [for Update [of Col1,col2,..];
- Begin
- Open Cursorname[(input Arguments)];
- Loop
- Fetch Cursorname Into V1,v2,.....;
- End Loop;
- Close Cursorname;
- End;
%FOUND---returns--BOOLEAN
%NOTFOUND--returns---BOOLEAN
%ISOPEN--returns---BOOLEAN
%ROWCOUNT---returns---INTEGER
%BULKROWCOUNT----returns-----INTEGER
Sample test code for cursor.
=======================
Expand|Select|Wrap|Line Numbers
- DECLARE
- I EMP.EMPNO%TYPE;
- J EMP.ENAME%TYPE;
- CURSOR C IS SELECT EMPNO,ENAME FROM EMP;
- BEGIN
- OPEN C;
- LOOP
- FETCH C INTO I,J;
- DBMS_OUTPUT.PUT_LINE(I||' '||J);
- EXIT WHEN C%NOTFOUND;
- END LOOP;
- CLOSE C;
- END;
Please try on SCOTT Schema
Also check PL/SQL-CURSOR - 2
