Dynamic Variable Binding in Dynamic SQL

Untuk membuat query dinamis dengan kondisi yang dinamis juga sesuai dengan pilihan user cukup sering ditemui pada query untuk menampilkan report. Sebagai contoh saya ingin membuat laporan untuk data transaksi yang bisa dilakukan filter tanggal transaksi , petugas yang melakukan transaksi, jenis transaksi, lokasi transaksi dan filter lainnya yang mungkin untuk dilakukan. Untuk membuat query dinamis bisa menggunakan EXECUTE IMMEDIATE

Cara Pertama

Cara ini yang paling mudah dengan tanpa menggunakan binding.

   Procedure ViewReport (
     pIn_startDate IN VARCHAR2,
     pIn_endDate   IN VARCHAR2,
     pIn_locationId IN NUMBER,
     pIn_userId      IN NUMBER,
     pOut_data      OUT TCursor -- Oracle Ref Cursor
   ) AS
    v_sql   VARCHAR2(2048);
    v_where VARCHAR2 (512);

  BEGIN
        IF pIn_startDate IS NOT NULL
        THEN
            v_where := v_where || '  AND TGL_TRANS >= TO_DATE('||pIn_startDate||',''DD/MM/YYYY'')';
        END IF;

        IF pIn_endDate IS NOT NULL
        THEN
            v_where := v_where || '  AND TGL_TRANS <= TO_DATE('||pIn_endDate||',''DD/MM/YYYY'')';
        END IF;

       IF pIn_locationId > 0
       THEN
          v_where := v_where || ' AND LOC_ID = '|| pIn_locationId;
        END IF;

      IF pIn_userId > 0
      THEN
          v_where := v_where || ' AND USER_ID = '|| pIn_userId;
      END IF; 

        v_sql := 'SELECT * FROM DATA_TRANSAKSI WHERE 1 = 1 '|| v_where;

        OPEN pOut_data FOR v_sql;

  END ViewReport;

Cara pertama ini memiliki kekurangan karena oracle harus melakukan hard parser terhadap setiap query yang dikirimkan ke server. Setiap kombinasi filter yang berbeda akan dianggap sql yang berbeda oleh oracle.

Cara Kedua
Cara kedua ini menggunakan binding variable seperti contoh dibawah ini

  Procedure ViewReport (
     pIn_startDate IN VARCHAR2,
     pIn_endDate   IN VARCHAR2,
     pIn_locationId IN NUMBER,
     pIn_userId      IN NUMBER,
     pOut_data      OUT TCursor -- Oracle Ref Cursor
   ) AS
    v_sql   VARCHAR2(2048);
    v_where VARCHAR2 (512);

  BEGIN
        IF pIn_startDate IS NOT NULL
        THEN
            v_where := v_where || '  AND TGL_TRANS >= TO_DATE(:pIn_startDate,''DD/MM/YYYY'')';
        END IF;

        IF pIn_endDate IS NOT NULL
        THEN
            v_where := v_where || '  AND TGL_TRANS <= TO_DATE(:pIn_endDate,''DD/MM/YYYY'')';
        END IF;

       IF pIn_locationId > 0
       THEN
          v_where := v_where || ' AND LOC_ID = :pIn_locationId';
        END IF;

      IF pIn_userId > 0
      THEN
          v_where := v_where || ' AND USER_ID = :pIn_userId';
      END IF; 

        v_sql := 'SELECT * FROM DATA_TRANSAKSI WHERE 1 = 1 '|| v_where;

        OPEN pOut_data FOR v_sql using pIn_startDate, pIn_endDate, pIn_locationId, pIn_userId;

  END ViewReport;

Procedure diatas akan error jika salah satu filter tidak dipilih, solusinya adalah kita harus memodifikasi procedure sehingga binding yang menjadi tepat. Solusi pertama adalah membuat kondisi pada eksekusi sql sehingga jumlah variable yang di binding dengan nilai penggantinya bersesuaian.

   Procedure ViewReport (
     pIn_startDate IN VARCHAR2,
     pIn_endDate   IN VARCHAR2,
     pIn_locationId IN NUMBER,
     pIn_userId      IN NUMBER,
     pOut_data      OUT TCursor -- Oracle Ref Cursor
   ) AS
    v_sql   VARCHAR2(2048);
    v_where VARCHAR2 (512);
    v_startDate  VARCHAR2(30) := pIn_startDate;
    v_endDate    VARCHAR2(30) := pIn_endDate;
    v_locationId  NUMBER(9) := pIn_locationId;
    v_userId      NUMBER(9) := pIn_userId;
  BEGIN
        IF pIn_startDate IS NOT NULL
        THEN
            v_where := v_where || '  AND TGL_TRANS >= TO_DATE(:pIn_startDate,''DD/MM/YYYY'')';
        ELSE
             v_where := v_where || '  AND ''1'' = :pIn_startDate  ';
             v_startDate := '1';
        END IF;

        IF pIn_endDate IS NOT NULL
        THEN
            v_where := v_where || '  AND TGL_TRANS <= TO_DATE(:pIn_endDate,''DD/MM/YYYY'')';
       ELSE
             v_where := v_where || '  AND ''1'' = :pIn_endDate ';
             v_endDate := '1';
       END IF;

       IF pIn_locationId > 0
       THEN
          v_where := v_where || ' AND LOC_ID = :pIn_locationId';
       ELSE
           v_where := v_where || ' AND 1 = :pIn_locationId';
           v_locationId := 1;
       END IF;

      IF pIn_userId > 0
      THEN
          v_where := v_where || ' AND USER_ID = :pIn_userId';
       ELSE
          v_where := v_where || ' AND 1 = :pIn_userId';
          v_userId := 1;
      END IF; 

        v_sql := 'SELECT * FROM DATA_TRANSAKSI WHERE 1 = 1 '|| v_where;

        OPEN pOut_data FOR v_sql using v_startDate, v_endDate, v_locationId, v_userId;

  END ViewReport;

Dengan procedure diatas kita bisa membuat dinamik sql dan dinamik varibale binding untuk kebutuhan pelaporan data.