REF CURSORS:
************
-->Ref Cursors are user defined types which is used to process multiple records.
-->Generally the static cursors we are using only one select statement at a time for single active set area.
-->The query associated with normal cursor has to be defined as part of the cursor declaration itself.
-->In refcursors we are executing no.of select statements dynamically for a single active set area.That's why these type of cursors are also called as dynamic cursors.
-->The query is not associated at the declaration,rather the query is associsted at run time.
-->Refcursors are user defined type so we are creating in two step process.
-->First we are creating type and then only we are creating a variable of that type.That's why these type of cursors are also called as cursor variables.
-->There are two types of refcursors supported by oracle.
1)Strong Refcursor
2)Weak Refcursor
-->Strong Refcursor is a refcursor which having a return type.
-->Weak Refcursor is a refcursor which doesn't have a return type.
Syntax:
*******
Type Typename is ref cursor return recordtypedatatype; --Strong Ref cursor
VARIABLENAME TYPENAME;
Type Typename is ref cursor; --Weak ref cursor
VARIABLENAME TYPENAME;
Note:
*****
-->In ref cursors we are executing select statements using open...for statements.
-->These atatements are executed in executable section of the pl/sql block.
--normal cursor
DECLARE
CURSOR EMP_NAME IS SELECT ENAME FROM EMP;
CURSOR DEPT_NAME IS SELECT DNAME FROM DEPT;
LV_NAME VARCHAR2(50);
BEGIN
DBMS_OUTPUT.PUT_LINE('*********EMP DETAILS**********');
OPEN EMP_NAME;
LOOP
FETCH EMP_NAME INTO LV_NAME;
EXIT WHEN EMP_NAME%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_NAME);
END LOOP;
CLOSE EMP_NAME;
DBMS_OUTPUT.PUT_LINE('*********DEPT DETAILS**********');
OPEN DEPT_NAME;
LOOP
FETCH DEPT_NAME INTO LV_NAME;
EXIT WHEN DEPT_NAME%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_NAME);
END LOOP;
CLOSE DEPT_NAME;
END;
/
EX:
***
--WEAK REF CURSOR
DECLARE
TYPE T1 IS REF CURSOR;
V_T T1;
I EMP%ROWTYPE;
BEGIN
OPEN V_T FOR SELECT * FROM EMP;
LOOP
FETCH V_T INTO I;
EXIT WHEN V_T%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.ENAME||','||I.SAL);
END LOOP;
CLOSE V_T;
END;
/
--WEAK REF CURSOR FOR SPECIFIC COLUMN
DECLARE
TYPE XX_REF_CUR_TYPE IS REF CURSOR;
C_REF_CUR XX_REF_CUR_TYPE;
LV_VAR1_TYPE VARCHAR2(30);
LV_VAR2_TYPE VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE('***************EMPLOYEE DETAILS*************');
OPEN C_REF_CUR FOR SELECT ENAME,SAL FROM EMP;
LOOP
FETCH C_REF_CUR INTO LV_VAR1_TYPE,LV_VAR2_TYPE;
EXIT WHEN C_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(C_REF_CUR%ROWCOUNT||'-'||LV_VAR1_TYPE||'|Salary is :'|| LV_VAR2_TYPE);
END LOOP;
CLOSE C_REF_CUR;
DBMS_OUTPUT.PUT_LINE('***************DEPARTMENT DETAILS*************');
OPEN C_REF_CUR FOR SELECT DNAME,DEPTNO FROM DEPT;
LOOP
FETCH C_REF_CUR INTO LV_VAR1_TYPE,LV_VAR2_TYPE;
EXIT WHEN C_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(C_REF_CUR%ROWCOUNT||'-'||LV_VAR1_TYPE||'|Deptno is :'|| LV_VAR2_TYPE);
END LOOP;
CLOSE C_REF_CUR;
DBMS_OUTPUT.PUT_LINE('***************PO_HEADERS_ALL DETAILS*************');
OPEN C_REF_CUR FOR SELECT PO_HEADER_ID,SEGMENT1 FROM PO_HEADERS_ALL WHERE ROWNUM<=10;
LOOP
FETCH C_REF_CUR INTO LV_VAR1_TYPE,LV_VAR2_TYPE;
EXIT WHEN C_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(C_REF_CUR%ROWCOUNT||'-'||LV_VAR1_TYPE||'|Deptno is :'|| LV_VAR2_TYPE);
END LOOP;
CLOSE C_REF_CUR;
END;
/
--WEAK REFCURSOR FOR ROWTYPE
DECLARE
TYPE XX_REF_CUR_TYPE IS REF CURSOR;
C_REF_CUR XX_REF_CUR_TYPE;
LV_EMP_TYPE EMP%ROWTYPE;
LV_DEPT_TYPE DEPT%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('*****************CURSOR1 DETAILS*****************');
OPEN C_REF_CUR FOR SELECT * FROM EMP;
LOOP
FETCH C_REF_CUR INTO LV_EMP_TYPE;
EXIT WHEN C_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_EMP_TYPE.EMPNO||'-'||LV_EMP_TYPE.ENAME||'-'||LV_EMP_TYPE.JOB);
END LOOP;
CLOSE C_REF_CUR;
DBMS_OUTPUT.PUT_LINE('*****************CURSOR2 DETAILS*****************');
OPEN C_REF_CUR FOR SELECT * FROM DEPT;
LOOP
FETCH C_REF_CUR INTO LV_DEPT_TYPE;
EXIT WHEN C_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_DEPT_TYPE.DEPTNO||'-'||LV_DEPT_TYPE.DNAME||'-'||LV_DEPT_TYPE.LOC);
END LOOP;
CLOSE C_REF_CUR;
END;
/
DECLARE
TYPE XX_REF_CUR_TYPE IS REF CURSOR;
C_REF_CUR XX_REF_CUR_TYPE;
LV_EMP_TYPE EMP%ROWTYPE;
LV_DEPT_TYPE DEPT%ROWTYPE;
LV_HR_UNITS HR_OPERATING_UNITS%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('*****************CURSOR1 DETAILS*****************');
OPEN C_REF_CUR FOR SELECT * FROM EMP;
LOOP
FETCH C_REF_CUR INTO LV_EMP_TYPE;
EXIT WHEN C_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_EMP_TYPE.EMPNO||'-'||LV_EMP_TYPE.ENAME||'-'||LV_EMP_TYPE.JOB);
END LOOP;
CLOSE C_REF_CUR;
DBMS_OUTPUT.PUT_LINE('*****************CURSOR2 DETAILS*****************');
OPEN C_REF_CUR FOR SELECT * FROM DEPT;
LOOP
FETCH C_REF_CUR INTO LV_DEPT_TYPE;
EXIT WHEN C_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_DEPT_TYPE.DEPTNO||'-'||LV_DEPT_TYPE.DNAME||'-'||LV_DEPT_TYPE.LOC);
END LOOP;
CLOSE C_REF_CUR;
DBMS_OUTPUT.PUT_LINE('*****************CURSOR3 DETAILS*****************');
OPEN C_REF_CUR FOR SELECT * FROM HR_OPERATING_UNITS;
LOOP
FETCH C_REF_CUR INTO LV_HR_UNITS;
EXIT WHEN C_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_HR_UNITS.NAME);
END LOOP;
CLOSE C_REF_CUR;
END;
/
drop table emp_insert;
/
drop table emp_dept;
/
CREATE TABLE EMP_INSERT AS SELECT * FROM EMP WHERE 1=2;
/
CREATE TABLE EMP_DEPT AS SELECT * FROM DEPT WHERE 1=2;
/
DECLARE
TYPE XX_REF_CUR_TYPE IS REF CURSOR;
C_REF_CUR XX_REF_CUR_TYPE;
LV_EMP_TYPE EMP%ROWTYPE;
LV_DEPT_TYPE DEPT%ROWTYPE;
LV_HR_UNITS HR_OPERATING_UNITS%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('*****************CURSOR1 DETAILS*****************');
OPEN C_REF_CUR FOR SELECT * FROM EMP;
LOOP
FETCH C_REF_CUR INTO LV_EMP_TYPE;
EXIT WHEN C_REF_CUR%NOTFOUND;
INSERT INTO EMP_INSERT VALUES(LV_EMP_TYPE.EMPNO,LV_EMP_TYPE.ENAME,LV_EMP_TYPE.JOB,LV_EMP_TYPE.MGR,LV_EMP_TYPE.HIREDATE,LV_EMP_TYPE.SAL,LV_EMP_TYPE.COMM,LV_EMP_TYPE.DEPTNO);
DBMS_OUTPUT.PUT_LINE(LV_EMP_TYPE.EMPNO||'-'||LV_EMP_TYPE.ENAME||'-'||LV_EMP_TYPE.JOB);
END LOOP;
CLOSE C_REF_CUR;
DBMS_OUTPUT.PUT_LINE('*****************CURSOR2 DETAILS*****************');
OPEN C_REF_CUR FOR SELECT * FROM DEPT;
LOOP
FETCH C_REF_CUR INTO LV_DEPT_TYPE;
EXIT WHEN C_REF_CUR%NOTFOUND;
INSERT INTO EMP_DEPT VALUES(LV_DEPT_TYPE.DEPTNO,LV_DEPT_TYPE.DNAME,LV_DEPT_TYPE.LOC);
DBMS_OUTPUT.PUT_LINE(LV_DEPT_TYPE.DEPTNO||'-'||LV_DEPT_TYPE.DNAME||'-'||LV_DEPT_TYPE.LOC);
END LOOP;
CLOSE C_REF_CUR;
DBMS_OUTPUT.PUT_LINE('*****************CURSOR3 DETAILS*****************');
OPEN C_REF_CUR FOR SELECT * FROM HR_OPERATING_UNITS;
LOOP
FETCH C_REF_CUR INTO LV_HR_UNITS;
EXIT WHEN C_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_HR_UNITS.NAME);
END LOOP;
CLOSE C_REF_CUR;
END;
/
SELECT * FROM EMP_INSERT;
/
SELECT * FROM EMP_DEPT;
/
---STRONG REF CURSOR
DECLARE
TYPE XX_REF_CUR_TYPE IS REF CURSOR RETURN EMP%ROWTYPE;
C_REF_CUR XX_REF_CUR_TYPE;
LV_EMP_TYPE EMP%ROWTYPE;
LV_DEPT_TYPE DEPT%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('*****************CURSOR1 DETAILS*****************');
OPEN C_REF_CUR FOR SELECT * FROM EMP;
LOOP
FETCH C_REF_CUR INTO LV_EMP_TYPE;
EXIT WHEN C_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_EMP_TYPE.EMPNO||'-'||LV_EMP_TYPE.ENAME||'-'||LV_EMP_TYPE.JOB);
END LOOP;
CLOSE C_REF_CUR;
END;
/
--STRONG REF CURSOR USING DIFFERENT TABLE TYPE(It will throw the error)
DECLARE
TYPE XX_REF_CUR_TYPE IS REF CURSOR RETURN EMP%ROWTYPE;
C_REF_CUR XX_REF_CUR_TYPE;
LV_EMP_TYPE EMP%ROWTYPE;
LV_DEPT_TYPE DEPT%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('*****************CURSOR1 DETAILS*****************');
OPEN C_REF_CUR FOR SELECT * FROM DEPT;
LOOP
FETCH C_REF_CUR INTO LV_DEPT_TYPE;
EXIT WHEN C_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_DEPT_TYPE.DEPTNO||'-'||LV_DEPT_TYPE.DNAME||'-'||LV_DEPT_TYPE.LOC);
END LOOP;
CLOSE C_REF_CUR;
END;
/
--SYS REFCURSOR --IS ALWAYS WEAKLY TYPE REF CURSOR
DECLARE
--TYPE XX_REF_CUR_TYPE IS REF CURSOR; -- RETURN EMP%ROWTYPE;
C_REF_CUR SYS_REFCURSOR;
LV_EMP_TYPE EMP%ROWTYPE;
LV_DEPT_TYPE DEPT%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('*****************CURSOR1 DETAILS*****************');
OPEN C_REF_CUR FOR SELECT * FROM EMP;
LOOP
FETCH C_REF_CUR INTO LV_EMP_TYPE;
EXIT WHEN C_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_EMP_TYPE.EMPNO||'-'||LV_EMP_TYPE.ENAME||'-'||LV_EMP_TYPE.JOB);
END LOOP;
CLOSE C_REF_CUR;
END;
/
CREATE OR REPLACE PROCEDURE P_REF_CUR(P_DEPTNO NUMBER,P_NAME_LIST OUT SYS_REFCURSOR)
IS
BEGIN
OPEN P_NAME_LIST FOR SELECT ENAME FROM EMP WHERE DEPTNO=P_DEPTNO;
END;
/
DECLARE
LV_NAME_LIST SYS_REFCURSOR;
LV_NAME VARCHAR2(20);
BEGIN
P_REF_CUR(10,LV_NAME_LIST);
LOOP
FETCH LV_NAME_LIST INTO LV_NAME;
EXIT WHEN LV_NAME_LIST%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_NAME);
END LOOP;
END;
/
--Is it possible to return multiple values in function.
CREATE OR REPLACE FUNCTION F1(P_DEPTNO IN NUMBER)
RETURN SYS_REFCURSOR
IS
LV_NAME_LIST SYS_REFCURSOR;
BEGIN
OPEN LV_NAME_LIST FOR SELECT ENAME,SAL
FROM EMP
WHERE DEPTNO=P_DEPTNO;
RETURN LV_NAME_LIST;
END;
/
DECLARE
LV_NAME_LIST SYS_REFCURSOR;
LV_NAME VARCHAR2(20);
LV_SAL NUMBER;
BEGIN
LV_NAME_LIST :=F1(10);
LOOP
FETCH LV_NAME_LIST INTO LV_NAME,LV_SAL;
EXIT WHEN LV_NAME_LIST%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LV_NAME||' '||LV_SAL );
END LOOP;
END;
No comments:
Post a Comment