Archive for January, 2010

Sering kita harus membuat procedure dengan ouput ref cursor. Ref Cursor sendiri sebenarnya bukanlah Cursor itu sendiri melainkan pointer yang mengarah ke varibale cursor di RAM (seperti pointer di bahasa C).Dalam pendefinisian ref cursor kita bisa mendefinisikan weak cursor atau strong cursor. Weak Cursor kita mendefinisikan cursor tanpa menyertakan tipe data yang akan dihasilkan sedangkan untuk strong cursor pendefinisian cursor diikuti dengan pendefinisian tipe data yang akan dihasilkan.

Sintak mendeklarasikan weak cursor :

  TYPE TDept_Cursor IS REF CURSOR;

Sintak mendeklarasikan Strong Cursor :

  TYPE TDept_Cursor IS REF CURSOR
           RETURN DEPARTMENT%ROWTYPE;

Pada tulisan ini saya coba menulis langkah-langkah membuat procedure dengan ouptut ref cursor. Sample schema yang digunakan adalah schema HR.

Permasalahan : Saya ingin membuat procedure dengan inputan departmentId dan menghasilkan seluruh

Membuat package misal dengan nama PKG_EMPLOYEE

  CREATE OR REPLACE PACKAGE PKG_EMPLOYEE
  IS
      TYPE REC_EMP_DATA IS RECORD (
          empId EMPLOYEES.EMP_ID%TYPE,
          empName EMPLOYEES.FIRST_NAME%TYPE
      );

      TYPE CUR_EMP IS REF CURSOR
              RETURN REC_EMP_DATA;

     PROCEDURE getEmployeeData (pIn_deptId IN NUMBER, pOut_EmpData OUT CUR_EMP);

  END PKG_EMPLOYEE;

Kemudian kita buat package body nya seperti berikut :

   CREATE OR REPLACE PACKAGE BODY PKG_EMPLOYEE
   IS
      PROCEDURE getEmployeeData (pIn_deptId IN NUMBER, pOut_EmpData OUT CUR_EMP)
      AS
      BEGIN
          OPEN pOut_EmpData FOR
          SELECT EMP_ID,FIRST_NAME
          FROM EMPLOYEES
          WHERE DEPERTMENT_ID = pIn_deptId;
      END getEmployeeData;
   END  PKG_EMPLOYEE;

selesai sudah deh package dengn procedure return ref cursor. Untuk mengujinya bisa menggunakan sccript berikut

    DECLARE
       v_cursor  PKG_EMPLOYEE.CUR_EMP;
       v_deptId NUMBER(4);
       v_empId NUMBER(9);
       v_name VARCHAR2(40);
    BEGIN
          v_deptId := 20;

          PKG_EMPLOYEE.getEmployeeData (
                                   pIn_deptId      => v_depId,
                                   pOut_EmpData => v_cursor
                                   );

          LOOP
          FETCH v_cursor INTO v_empId,v_name
          EXIT WHEN v_cursor%NOTFOUND
                 DBMS_OUTPUT.PUT_LINE (' ID : '|| v_empId || ' NAMA : '||v_name);
          END LOOP;
    END;

Semoga bermanfaat.