Define Cursor with Example

                                  Cursor With Example

Cursor:- Cursor is basically an area allocate by oracle for executing SQL statement.

Type of Cursor                                         

1. Implicit Cursor:- Implicit cursor created by Oracle.

2. Explicit Cursor:- Explicit cursor created by developer.

Attribute:-

Implicit Cursor:- SQL%ROWCOUNT, SQL%NOTFOUND, SQL%FOUND

Explicit Cursor:- cursor_name%ROWCOUNT, cursor_name%ISOPEN, cursor_name%FOUND, cursor_name%NOTFOUND

Example of Cursor

1.

DECLARE

CURSOR EMP_CUR IS

SELECT EMPLOYEE_ID,LAST_NAME,HIRE_DATE

FROM EMPLOYEES;

EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;

L_NAME EMPLOYEES.LAST_NAME%TYPE;

H_DATE EMPLOYEES.HIRE_DATE%TYPE;

BEGIN

OPEN EMP_CUR;

LOOP

FETCH EMP_CUR INTO EMP_ID,L_NAME,H_DATE;

EXIT WHEN EMP_CUR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(EMP_ID||CHR(9)||L_NAME||CHR(9)||H_DATE);

END LOOP;

END;

2. 

DECLARE

CURSOR EMP_CUR IS

SELECT *

FROM EMPLOYEES;

EMP_REC EMPLOYEES%ROWTYPE;

BEGIN

OPEN EMP_CUR;

LOOP

FETCH EMP_CUR INTO EMP_REC;

EXIT WHEN EMP_CUR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPLOYEE_ID||CHR(9)||EMP_REC.LAST_NAME||CHR(9)||EMP_REC.HIRE_DATE);

END LOOP;

END;

3. Example of Cursor FOR LOOP

DECLARE

CURSOR EMP_CUR IS

SELECT * 

FROM EMPLOYEES;

BEGIN

FOR I IN EMP_CUR

LOOP

DBMS_OUTPUT.PUT_LINE(I.EMPLOYEE_ID);

END LOOP;

END;

4. Without declare cursor

DECLARE 

BEGIN

FOR I IN (SELECT * FROM EMPLOYEES)

LOOP

DBMS_OUTPUT.PUT_LINE(I.EMPLOYEE_ID);

END LOOP;

END;

5. Parametrised Cursor

DECLARE

CURSOR C1(DEPT_NUM NUMBER) IS

SELECT * 

FROM EMPLOYEES

WHERE DEPARTMENT_ID = DEPT_NUM;

EMP_REC EMPLOYEES%ROWTYPE;

BEGIN

OPEN C1(50);

LOOP

FETCH C1 INTO EMP_REC;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPLOYEE_ID||CHR(9)||EMP_REC.LAST_NAME||CHR(9)||EMP_REC.HIRE_DATE);

END LOOP;

END;