Explicit cursor declaration

NORMAL CURSOR DECLARATION

/*USE OF OPEN,FETCH,%TYPE*/
DECLARE
CURSOR c1 IS SELECT ENAME FROM emp;
name1 emp.ENAME%TYPE;
name2 emp.ENAME%TYPE;
name3 emp.ENAME%TYPE;
BEGIN
OPEN c1;
FETCH c1 INTO name1; -- this fetches first row
DBMS_OUTPUT.PUT_LINE('ENAME:>'||NAME1);
FETCH c1 INTO name2; -- this fetches SECOND row
DBMS_OUTPUT.PUT_LINE('ENAME:>'||NAME2);
CLOSE c1;
END;
------------------------------------------------------------

/*FETCHING DATA FROM CURSOR USING for loop*/
DECLARE
name1 emp.ENAME%TYPE;
name2 emp.SAL%TYPE;
name3 emp.HIREDATE%TYPE;
CURSOR c1 IS SELECT ENAME,SAL,HIREDATE FROM emp;
BEGIN
OPEN c1;
FOR I IN 1..10
LOOP
FETCH c1 INTO NAME1,NAME2,NAME3;
DBMS_OUTPUT.PUT_LINE(' '||NAME1||' '||NAME2||' '||NAME3);
END LOOP;
CLOSE c1;
END;

------------------------------------------------------------

/*Example of %ISOPEN, %NOTFOUND,%ROWCOUNT */
DECLARE
name1 emp.ENAME%TYPE;
name2 emp.SAL%TYPE;
name3 emp.HIREDATE%TYPE;
CURSOR c1 IS SELECT ENAME,SAL,HIREDATE FROM emp;
BEGIN
IF NOT C1%ISOPEN THEN OPEN C1;
END IF;
LOOP
FETCH c1 INTO NAME1,NAME2,NAME3;
EXIT WHEN C1%ROWCOUNT>10 OR C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' '||NAME1||' '||NAME2||' '||NAME3);
END LOOP;
close c1;
END;
------------------------------------------------------------

/*CURSOR USING %ROWTYPE*/

DECLARE
CURSOR c1 IS SELECT * FROM emp;
name1 emp%ROWTYPE;
name2 emp%ROWTYPE;
name3 emp%ROWTYPE;
BEGIN
OPEN c1;
FETCH c1 INTO name1; -- this fetches first row
DBMS_OUTPUT.PUT_LINE('ENAME:>'||NAME1.ENAME||'SAL:>'||NAME1.SAL);
FETCH c1 INTO name2; -- this fetches second row
DBMS_OUTPUT.PUT_LINE('ENAME:>'||NAME2.ENAME||'SAL:>'||NAME2.SAL);
FETCH c1 INTO name3; -- this fetches third row
CLOSE c1;

END;

------------------------------------------------------------
/*CURSOR AND RECORD*/

DECLARE
CURSOR c1 IS SELECT ENAME,SAL,HIREDATE FROM emp WHERE COMM IS NULL;
C1_REC C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH c1 INTO C1_REC;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' '||C1_REC.ENAME||''||C1_REC.SAL||' '||C1_REC.HIREDATE);
END LOOP;
END;
------------------------------------------------------------

/*CURSOR FOR LOOP*/

DECLARE
CURSOR C1 IS
SELECT Ename,EMPNO, HIREDATE
FROM EMP
WHERE DEPTNO = 20;
BEGIN
FOR C1_DAT IN C1 LOOP
INSERT INTO STUD (NAME,ROLL,DOB)
VALUES (C1_DAT.ENAME, C1_DAT.EMPNO,C1_DAT.HIREDATE);
END LOOP;
COMMIT;
END;
/
------------------------------------------------------------

/*CURSOR FOR LOOP(A FAULTY CONDITION)*/

DECLARE
CURSOR c1 IS SELECT ENAME,SAL,HIREDATE FROM emp WHERE COMM IS NULL;
name1 emp.ENAME%TYPE;
name2 emp.SAL%TYPE;
name3 emp.HIREDATE%TYPE;
BEGIN
for C1_REC IN C1 LOOP
FETCH c1 INTO NAME1,NAME2,NAME3;
DBMS_OUTPUT.PUT_LINE(' '||NAME1||' '||NAME2||' '||NAME3);
END LOOP;
END;