Posts tagged dynamic sql

EXECUTE IMMEDIATE

0
607 days
by in Oracle

Execute immediate merupakan pengganti dari DBMS_SQL, jika kita sebelumnya menggunakan DBMS_SQL cukup merepotkan sekarang anda tidak perlu kawatir lagi. Yang perlu diingat perintah execute immediate tidak otomatis commit untuk perintah-perintah DML, jadi kita harus melakukan commit atau rollback manual. Sql yang dieksekusi tidak perlu diakhiri dengan tanda titik koma (;).

Berikut beberapa contoh penggunaan execute immediate.

Menggunakan perintah DDL

begin
execute immediate 'create table belajar(t1 number(3))';
end;

SQL> select * from belajar;

T1
---

Menggunakan Variable dalam dinamik SQL

Contoh berikut adalah insert data ketable belajar.

declare
v_t1 number(3); -- deklarasi variable v_t1
begin

v_t1 := 123; -- assignment varibale v_t1 dengna nilai 123

execute immediate 'insert into belajar(t1) values (:t1) ' using v_t1;
commit;

select * from belajar;

end;

SQL> select * from belajar;

T1
----
123

“:t1″ adalah variable yang akan digantikan oleh v_t1, binding yang digunakan adalah “:”

Mengembalikan Nilai

Untuk mendapatkan nilai dari execute immediate kita menggunakan keyword INTO

declare
v_t1 number(3); -- deklarasi variable
begin

execute immediate 'select t1 + 5 from belajar'  into v_t1;

dbms_output.put_line ('Hasil : '||v_t1);

Hasil : 128

end;

jika select membutuhkan kondisi, misal dengan tambahan filter t1=x query menjadi execute ‘select t1 + 5 from belajar where t1=:x’ into v_t1 using x ;

Menjalankan Package/Store Procedure / Function

Contoh procedure yang digunakan adalah

declare
  v_str varchar2(20);
  v_serviceName varchar2(40) : 'MyPackage.printHello';
begin
  v_str := 'Selamat Datang di Nailuvar Education Center';
  execute immediate 'BEGIN '|| v_serviceName || ' (:a,:b); END;' using in v_str , out v_status;

 dbms_output.put_line ('Status = '||v_status);
end;

Menyimpan hasil dalam bentuk Record PL/SQL
contoh ini akan menampilkan data employee pada schema HR, data yang akan ditampilkan adalah employee_id dan nama.

declare
-- deklarasi type record  dengan nama RData
TYPE RData IS RECORD (
 emp_id number(9),
 emp_name varchar2(40)
);

--deklarasi variable v_data dengan type RData.

begin
  EXECUTE IMMEDIATE 'select employee_id,first_name || last_name from employee where employee_id = 1234 ' into v_data;
end;

demikian beberapa contoh penggunaan execute immediate yang membuat pemrosesan query dinamis menjadi lebih simple jika dibandingkan menggunakan DBMS_SQL. Yang harus diperhatikan adalah penanganan error, setiap kemungkinan error harus ditangani.

Selamat Mencoba.

Dynamic Variable Binding in Dynamic SQL

0
667 days
by in Oracle

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.

Go to Top