PL SQL Tutorial
In PL/SQL Cursors are Name Assigned to Memory Area. This area contains SQL statement and data returned by SQL Statement. There can be either static cursors or dynamic cursors, Static cursors where SQL statement is determined at compiled time, Dynamic Cursors where SQL statement is determined at runtime.
Implicit Cursors, Opening a cursor,Fetching records, and Closing a cursor, these operations done by PL/SQL Engine. User has no knowledge how it is done, internally PL/SQL performs These operations on behalf of User. Anyway Programmer can check cursor status using following Cursor Attributes.
Implicit Cursor Attributes
for loop is used to loop through implicit cursors. it internally, opens the cursor, fetches the data, and close the cursor. This process done, automatically.
Here SQL statement specified in the for itself. IDX is a record,it contains all fields specified in the SQL Statement.
idx.empno gives employee no, idx.ename gives employee name etc., in above SQL statement '*' is used means fetch all fields. for simplicity used only 2 fields ,empno,ename
BEGIN FOR IDX IN (SELECT * FROM EMP) LOOP DBMS_OUTPUT.PUT_LINE(IDX.EMPNO|| ' ' || IDX.ENAME); END LOOP; END; SQL>/ 7839 KING 7698 BLAKE 7782 CLARK .....
In this example
cursor is declared in the declare section,
Cursor variable is used in the For loop, as shown below.
EMP_REC is a Record type,using which all data can be fetched. for loop fetches one-record at a time,
loop terminates when SQL%NOTFOUND is returns a value.
set serveroutput on; DECLARE CURSOR C IS SELECT EMPNO,ENAME,SAL FROM EMP; BEGIN FOR EMP_REC IN C LOOP DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPNO||' '||EMP_REC.ENAME||' '||EMP_REC.SAL); END LOOP; END; SQL>/ 7839 KING 5000 7698 BLAKE 2850 7782 CLARK 2450 7566 JONES 2975 7788 SCOTT 3000 .......
Steps required to use Explicite Cursor
Explicit Cursor Attributes
returns TRUE when cursor is opened.
returns TRUE when record fetched successfully
returns TRUE when record was not fetched successfully
returns number of records fetched from the cursor.
Cursor C points to SQL Query(fetch Depart name,ID, Employee Name, JOB from emp and dept table)
RECORD REC is of type Cursor C
OPEN -- open cursor C
FETCH REC from Cursor C
CLOSE Cursor C
DECLARE CURSOR C IS SELECT E.DEPTNO,DNAME,ENAME,JOB FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO; REC C%ROWTYPE; BEGIN OPEN C; LOOP FETCH C INTO REC; IF c%notFOUND THEN EXIT;END IF; DBMS_OUTPUT.PUT_LINE(REC.DEPTNO||' '||REC.DNAME||' '||REC.ENAME||' '||REC.JOB); END LOOP; CLOSE C; END; SQL>/ 10 ACCOUNTING KING PRESIDENT 30 SALES BLAKE MANAGER 10 ACCOUNTING CLARK MANAGER 20 RESEARCH JONES MANAGER 20 RESEARCH SCOTT ANALYST . . .
Parameters can be specified to cursor at the time of declaration. Like procedures or functions, parameters can be passed,
Syntax:cursor is cursor_variable(param1,param2....) is SQL Statement;Example:
Get dept 10 employees
DECLARE CURSOR C(DNO NUMBER) IS SELECT * FROM EMP WHERE DEPTNO=DNO;In the Execution Section
BEGIN FOR I IN C(10) LOOP DBMS_OUTPUT.PUT_LINE(I.EMPNO||' '||I.ENAME); END LOOP; END; SQL>/ 7839 KING 7782 CLARK 7934 MILLER
TYPE ref_cursor_type_name is REF CURSOR [RETURN record_type]; cursor_variable ref_cursor_type_name;
The cursor doesnot have RETURN statement is called weakly typed ref cursor, For weakly typed REF cursors you can use built-in SYS_REFCURSOR
The cursor which have RETURN statement
DECLARE TYPE emp_ref_type is REF CURSOR RETURN emp%ROWTYPE; emp_rec emp_ref_type;Declaring Weakly Typed REF CURSOR
TYPE emp_ref_type is REF CURSOR; emp_rec emp_ref_type; or emp_ref SYS_REFCURSOR;
REF CURSORS in Functions Stored Functions/Functions
REF CURSORS in Stored Procedures PL/SQL Stored Procedures
REF CURSORS in Packages PL/SQL Packages
ADS