CURSORS IN PLSQL WITH SIMPLE EXAMPLES
-----------------------------CURSORS--------------------------
CURSOR :
-->> WHENEVER A SQL AUERY WHICH RETRIVES MORE THAN ONE ROW , TO PROCESS THOSE RECORDS/ROWS WE USE CURSORS.
>>CONTEXT AREA OF MEMORY WHERE QUERY RECORDS ARE SAVED.
-----------
DECLARE
V_EMPNO EMPL.EMPNO%TYPE;
V_SAL EMPL.SAL%TYPE;
V_HIREDATE EMPL.HIREDATE%TYPE;
BEGIN
SELECT EMPNO,SAL,HIREDATE
INTO V_EMPNO,V_SAL,V_HIREDATE --ONLY ONE ROW
FROM EMPL
WHERE ROWNUM<10;
DBMS_OUTPUT.PUT_LINE('ID OF EMPLOYE IS :'||V_EMPNO);
DBMS_OUTPUT.PUT_LINE('SALARY OF EMPLOYE IS :'||V_SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE OF EMPLOYE IS :'||V_HIREDATE);
END;
-----------------------
--TYPES OF CUSRSORS
1. IMPLICIT CURSORS: >> SYSTEM DEFINED
2. EXPLICIT CURSORS >> USER DEFINED
-------------------
DECLARE
CURSOR C IS SELECT * FROM EMPL;
BEGIN
FOR I IN c
LOOP
dbms_output.put_line('EMPLOYEE NAME IS : '||i.ename || ' ::EMPLOYEE ID IS :'|| I.EMPNO ||' ::SALARY IS : '||I.SAL);
END LOOP;
END;
/
-------------------------------
DECLARE
CURSOR EMP_CUR IS SELECT ENAME, EMPNO FROM EMPL; --DECLARE
V_CUR EMP_CUR%ROWTYPE;
BEGIN
OPEN EMP_CUR; --2. OPEN THE CURSORS
FETCH EMP_CUR INTO V_CUR;
LOOP
EXIT WHEN EMP_CUR%NOTFOUND
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS : ' || V_CUR.ENAME || ':: EMP ID IS : '||V_CUR.EMPNO );
END LOOP;
END;
/
DECLARE
CURSOR c IS SELECT ename FROM empl; --DECLARE
vname c%rowtype;
BEGIN
OPEN c; --2. OPEN THE CURSORS
loop
fetch c INTO vname; -- FETCH CURSOR
exit WHEN c%notfound;
dbms_output.put_line(vname.ename);
END loop;
CLOSE C; --4. CLOSE CURSOR
END;
/
--CURSOR FOR LOOPS:
--OPEN , FETCH AND CLOSE THE CURSR INTERNALLY...
--WE NEED NOT INITIALIZE VARIABLE AS WELL.
--WAY001
DECLARE
CURSOR c
IS
SELECT ename FROM empl;
Vname c%rowtype;
BEGIN
OPEN c;
LOOP
FETCH c INTO vname;
EXIT
WHEN c%notfound;
Dbms_output.put_line(vname.ename);
END LOOP;
END;
/
--WAY _002
DECLARE
CURSOR c IS SELECT ename FROM empl; --DECLARE
BEGIN
FOR I IN C LOOP
DBMS_OUTPUT.PUT_LINE('NAME OF EMPLOYEE IS :'||I.ENAME);
END LOOP;
END;
--WAY _003
BEGIN
FOR I IN ( SELECT ename FROM empl) LOOP
DBMS_OUTPUT.PUT_LINE('NAME OF EMPLOYEE IS :'||I.ENAME);
END LOOP;
END;
/
--WAY001
DECLARE
CURSOR c
IS
SELECT ename FROM empl;
Vname c%rowtype;
BEGIN
OPEN c;
LOOP
FETCH c INTO vname;
EXIT
WHEN c%notfound;
Dbms_output.put_line(vname.ename);
END LOOP;
END;
/
----------------------------------------------PARAMETERISED CURSORS:-------------------------------
----------------------------------------------PARAMETERISED CURSORS:-------------------------------
A CURSOR WHICH CAN ACCEPT THE PARAMETER IS CALLED AS PARAMETERISED CURSOR.
E.G
DECLARE
CURSOR c (X NUMBER,V_ENAME VARCHAR2) IS SELECT HIREDATE,SAL FROM empl WHERE EMPNO = X AND ENAME = V_ENAME; --DECLARE
VAR_1 NUMBER;
VAR_2 VARCHAR2(100);
BEGIN
VAR_1 := &a;
VAR_2 := &B;
FOR I IN C (VAR_1 , VAR_2) LOOP
DBMS_OUTPUT.PUT_LINE('HIREDATE OF EMPLOYEE IS :'||I.HIREDATE);
DBMS_OUTPUT.PUT_LINE('SALARY OF EMPLOYEE IS :'||I.SAL);
END LOOP;
END;
/
