Friday, August 16, 2013

Strong and Weak REF CURSOR Structure - PLSQL

Strong REF CURSOR: Structure of REF cursor is well known during compile time.
Weak REF CURSOR: Not known during compile time.

Following 3 samples will give different ways to use REF CURSOR in PLSQL. These examples created using default HR schema available with XE database.

Weak REF CURSOR Example:
CREATE OR REPLACE PROCEDURE PROC_WEAK_REF_CUR(
                                                in_dept_id  IN  DEPARTMENTS.DEPARTMENT_ID%TYPE,
                                               out_ref_cur OUT SYS_REFCURSOR)
IS
BEGIN
       OPEN    out_ref_cur FOR
       SELECT EMP.*
       FROM   EMPLOYEES EMP,
                     DEPARTMENTS DEPT,
                     LOCATIONS LOC
       WHERE  EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID
       AND       DEPT.LOCATION_ID = LOC.LOCATION_ID
       AND       DEPT.DEPARTMENT_ID = in_dept_id;
END;


DECLARE
        out_ref_cur SYS_REFCURSOR;
        employee EMPLOYEES%ROWTYPE;
BEGIN
        PROC_WEAK_REF_CUR(20,out_ref_cur);
        LOOP
                FETCH out_ref_cur INTO employee;
                EXIT WHEN out_ref_cur%NOTFOUND;
                dbms_output.put_line(employee.EMPLOYEE_ID);
                dbms_output.put_line(employee.FIRST_NAME);
                dbms_output.put_line(employee.LAST_NAME);
                dbms_output.put_line(employee.EMAIL);
                dbms_output.put_line(employee.HIRE_DATE);
                dbms_output.put_line(employee.JOB_ID);
                dbms_output.put_line(employee.SALARY);
                dbms_output.put_line(employee.COMMISSION_PCT);
                dbms_output.put_line(employee.MANAGER_ID);
                dbms_output.put_line(employee.DEPARTMENT_ID);
         END LOOP;
        CLOSE out_ref_cur;
END;


Strong REF CURSOR Example:
CREATE OR REPLACE PACKAGE PKG_STRONG_REF_CUR AS
   TYPE ref_cur_type IS RECORD (
                                EMPLOYEE_ID             EMPLOYEES.EMPLOYEE_ID%TYPE,
                        FIRST_NAME                EMPLOYEES.FIRST_NAME%TYPE,
                                DEPARTMENT_ID        DEPARTMENTS.DEPARTMENT_ID%TYPE,
                                DEPARTMENT_NAME DEPARTMENTS.DEPARTMENT_NAME%TYPE,
                                LOCATION_ID              LOCATIONS.LOCATION_ID%TYPE,
                                CITY                                LOCATIONS.CITY%TYPE );
   TYPE ref_cur IS REF CURSOR RETURN ref_cur_type;
   PROCEDURE PROC_STRONG_REF_CUR(
                                  in_dept_id   IN     DEPARTMENTS.DEPARTMENT_ID%TYPE,
                                  out_ref_cur  OUT ref_cur);
END PKG_STRONG_REF_CUR;


CREATE OR REPLACE PACKAGE BODY PKG_STRONG_REF_CUR AS
   PROCEDURE PROC_STRONG_REF_CUR(
                                                        in_dept_id   IN     DEPARTMENTS.DEPARTMENT_ID%TYPE,
out_ref_cur  OUT ref_cur)
   IS
   BEGIN
      OPEN   out_ref_cur FOR
      SELECT EMP.EMPLOYEE_ID,
                     EMP.FIRST_NAME, 
                     DEPT.DEPARTMENT_ID,
                     DEPT.DEPARTMENT_NAME, 
                     LOC.LOCATION_ID, 
                     LOC.CITY
      FROM   EMPLOYEES EMP,
                    DEPARTMENTS DEPT,
                    LOCATIONS LOC
      WHERE  EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID
      AND       DEPT.LOCATION_ID = LOC.LOCATION_ID
      AND       DEPT.DEPARTMENT_ID = in_dept_id;  
   END;
END PKG_STRONG_REF_CUR;


DECLARE
      v_ref_cur      PKG_STRONG_REF_CUR.ref_cur;
      v_ref_cur_type PKG_STRONG_REF_CUR.ref_cur_type;
BEGIN
     PKG_STRONG_REF_CUR.PROC_STRONG_REF_CUR(20,v_ref_cur);
     LOOP
         FETCH v_ref_cur INTO v_ref_cur_type;
    EXIT WHEN v_ref_cur%notfound;
         dbms_output.put_line(v_ref_cur_type.EMPLOYEE_ID);
         dbms_output.put_line(v_ref_cur_type.FIRST_NAME);
         dbms_output.put_line(v_ref_cur_type.DEPARTMENT_ID);
         dbms_output.put_line(v_ref_cur_type.DEPARTMENT_NAME);
         dbms_output.put_line(v_ref_cur_type.LOCATION_ID);
         dbms_output.put_line(v_ref_cur_type.CITY);
  END LOOP;
END;


Another Example:
CREATE OR REPLACE PROCEDURE PROC_STRONG_REF_CUR(
                                                 in_dept_id  IN     DEPARTMENTS.DEPARTMENT_ID%TYPE,
                                                 out_ref_cur OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN   out_ref_cur FOR
  SELECT EMP.EMPLOYEE_ID,
                 EMP.FIRST_NAME, 
                 DEPT.DEPARTMENT_ID,
                 DEPT.DEPARTMENT_NAME, 
                 LOC.LOCATION_ID, 
                 LOC.CITY
  FROM   EMPLOYEES EMP,
                DEPARTMENTS DEPT,
                LOCATIONS LOC
  WHERE  EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID
  AND       DEPT.LOCATION_ID = LOC.LOCATION_ID
  AND       DEPT.DEPARTMENT_ID = in_dept_id;
END;


DECLARE
TYPE ref_cur_type IS  RECORD(
                                EMPLOYEE_ID             EMPLOYEES.EMPLOYEE_ID%TYPE,
                        FIRST_NAME                EMPLOYEES.FIRST_NAME%TYPE,
                                DEPARTMENT_ID        DEPARTMENTS.DEPARTMENT_ID%TYPE,
                                DEPARTMENT_NAME DEPARTMENTS.DEPARTMENT_NAME%TYPE,
                                LOCATION_ID              LOCATIONS.LOCATION_ID%TYPE,
                                CITY                                LOCATIONS.CITY%TYPE );
TYPE ref_cur IS  REF CURSOR RETURN ref_cur_type;
      v_ref_cur ref_cur;
      v_ref_cur_type ref_cur_type;
  BEGIN
       PROC_WEAK_REF_CUR(20,v_ref_cur);
       LOOP
            FETCH v_ref_cur INTO v_ref_cur_type;
       EXIT WHEN v_ref_cur%NOTFOUND;
         dbms_output.put_line(v_ref_cur_type.EMPLOYEE_ID);
         dbms_output.put_line(v_ref_cur_type.FIRST_NAME);
         dbms_output.put_line(v_ref_cur_type.DEPARTMENT_ID);
         dbms_output.put_line(v_ref_cur_type.DEPARTMENT_NAME);
         dbms_output.put_line(v_ref_cur_type.LOCATION_ID);
         dbms_output.put_line(v_ref_cur_type.CITY);
  END LOOP;
  CLOSE v_ref_cur;
END;

No comments:

Post a Comment

Provide your thoughts !