Hastinapura

Oracle Database Resources

Native Dynamic SQL

11.6. Tips dan Traps untuk Dynamic SQL

Bagian ini menunjukkan kepada kita bagaimana mengambil keuntungan penuh dari dynamic SQL dan bagaimana menghindari beberapa perangkap.

Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world’s largest enterprise software company.

For more information about Oracle, please visit their Web site at http://www.oracle.com. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Get Free Oracle Magazine

11.6.1. Meningkatkan Performa

Dalam contoh di bawah ini, Oracle membuka sebuah cursor berbeda untuk setiap nilai berbeda dari emp_id. Hal ini dapat menyebabkan perebutan sumberdaya dan menurunkan performa.

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
  EXECUTE IMMEDIATE
  'DELETE FROM emp WHERE empno = ' || TO_CHAR(emp_id);
END;

Kita dapat meningkatkan performa dengan menggunakan sebuah bind variable, seperti ditunjukkan di bawah ini. Hal ini mengijinkan Oracle untuk menggunakan cursor yang sama untuk nilai-nilai berbeda dari emp_id.

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
  EXECUTE IMMEDIATE
  'DELETE FROM emp WHERE empno = :num' USING emp_id;
END;

11.6.2. Membuat Procedures Bekerja pada Named Schema Objects yang Berubah-ubah

Misalkan kita membutuhkan sebuah procedure yang dapat menerima nama dari suatu database table, kemudian menghapus table tersebut dari schema kita. Menggunakan dynamic SQL, kita dapat menulis standalone procedure berikut ini:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name;
END;

Namun, saat run time, procedure ini gagal dengan sebuah invalid table name error. Hal ini karena kita tidak menggunakan bind arguments untuk melewatkan nama-nama dari schema objects ke sebuah perintah SQL dynamic. Daripada itu, kita harus menyertakan parameter-parameter di dalam dynamic string, kemudian melewatkan nama-nama dari schema objects ke parameter-parameter tersebut.

Untuk men-debug conto terakhir tersebut, kita harus merevisi perintah EXECUTE IMMEDIATE. Dibandingkan menggunakan sebuah placeholder dan bind argument, kita menyertakan parameter table_name di dalam dynamic string, seperti berikut:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;

Sekarang, kita dapat melewatkan nama dari suatu database table ke perintah SQL dynamic tersebut.

11.6.3. Menggunakan Duplicate Placeholders

Placeholders di dalam sebuah perintah SQL dynamic terkait dengan bind arguments di dalam klausa USING berdasarkan posisi, tidak berdasarkan nama. Jadi, jika placeholder yang sama muncul dua kali atau lebih di dalam perintah SQL tersebut, setiap kemunculan harus sesuai dengan sebuah bind argument di dalam klausa USING. Sebagai contoh, dynamic string berikut

sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';

kita dapat meng-kode-kan klausa USING yang sesuai sebagai berikut:

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

Namun, hanya unique placeholders di dalam sebuah dynamic PL/SQL block yang terkait dengan bind arguments di dalam klausa USING berdasarkan posisi. Jadi, jika placeholder yang sama muncul dua kali atau lebih di dalam sebuah PL/SQL block, seluruh kemunculannya sesuai dengan satu bind argument di dalam klausa USING. Dalam contoh di bawah ini, unique placeholder pertama (x) terhubung dengan bind argument pertama (a). Demikian juga, unique placeholder kedua (y) terhubung dengan bind argument kedua (b).

DECLARE
a NUMBER := 4;
b NUMBER := 7;
BEGIN
  plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;'
  EXECUTE IMMEDIATE plsql_block USING a, b;
  ...
END;

11.6.4. Menggunakan Cursor Attributes

Setiap explicit cursors memiliki empat atribut: %FOUND, %ISOPEN, %NOTFOUND, dan %ROWCOUNT. Ketika ditambahkan kepada nama cursor, mereka menghasilkan  informasi yang berguna tentang eksekusi dari perintah-perintah SQL static dan dynamic.

Untuk memproses perintah-perintah manipulasi data SQL, Oracle membuka sebuah implicit cursor dengan nama SQL. Atribut-atribut-nya menghasilkan informasi tentang perintah-perintah INSERT, UPDATE, DELETE, atau SELECT baris-tunggal yang terakhir dieksekusi. Sebagai contoh, standalone function berikut ini menggunakan %ROWCOUNT untuk menghasilkan jumlah baris-baris data yang dihapus dari sebuah database table:

CREATE FUNCTION rows_deleted (
table_name IN VARCHAR2,
condition IN VARCHAR2) RETURN INTEGER AS
BEGIN
  EXECUTE IMMEDIATE
  'DELETE FROM ' || table_name || ' WHERE ' || condition;
  RETURN SQL%ROWCOUNT; -- menghasilkan jumlah baris data yang dihapus
END;

Demikian juga, ketika ditambahkan ke sebuah nama variable cursor, atribut-atribut cursor menghasilkan informasi tentang eksekusi dari sebuah multi-row query.

11.6.5. Melewatkan Nulls

Misalkan kita ingin melewatkan nilai-nilai null ke sebuah perintah SQL dynamic. Sebagai contoh, kita dapat menulis perintah EXECUTE IMMEDIATE berikut ini:

EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING NULL;

Namun, perintah ini gagal dengan sebuah error bad expression karena literal NULL tidak diperbolehkan di dalam klausa USING. Untuk dapat bekerja diantara batasan ini, secara sederhana kita dapat mengganti keyword NULL dengan sebuah variable tak terinisialisasi, seperti berikut ini:

DECLARE
a_null CHAR(1); -- set ke NULL secara otomatis saat run time
BEGIN
  EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null;
END;

11.6.6. Melakukan Operasi-operasi dari Jauh

Seperti ditunjukkan oleh contoh berikut ini, PL/SQL subprograms dapat mengeksekusi perintah-perintah SQL dynamic yang mengacu ke objek-objek pada sebuah remote database:

PROCEDURE delete_dept (db_link VARCHAR2, dept_id INTEGER) IS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM dept@' || db_link ||
  ' WHERE deptno = :num' USING dept_id;
END;

Juga, target-target dari remote procedure calls (RPC) dapat mengandung perintah-perintah SQL dynamic. Sebagai contoh, mari kita perhatikan standalone function berikut ini, yang menghasilkan jumlah baris data di dalam sebuah table, yang berada pada Chicago database:

CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN INTEGER AS
rows INTEGER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;
  RETURN rows;
END;

Dari sebuah anonymous block, kita dapat memanggil function tersebut dari jauh, seperti berikut:

DECLARE
emp_count INTEGER;
BEGIN
  emp_count := row_count@chicago(’emp’);

11.6.7. Menggunakan Invoker Rights

Secara default, sebuah stored procedure dijalankan dengan privilege dari definer-nya (yang mendefinisikan), bukan invoker-nya (yang memanggil). Procedures seperti ini dibatasi oleh schema dimana mereka berada. Sebagai contoh, asumsikan bahwa standalone procedure berikut ini, yang dapat menghapus suatu database object, berada di dalam schema scott:

CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;

Mari kita asumsikan juta bahwa user jones telah diberikan privilete EXECUTE pada procedure ini. Ketika user jones memanggil drop_it, seperti berikut ini, perintah dynamic DROP dieksekusi dengan privilege dari user scott:

SQL> CALL drop_it('TABLE', 'dept');

Juga, referensi tak terkualifikasi kepada table dept ditetapkan di dalam schema scott. Sehingga, procedure menghapus table tersebut dari schema scott, tidak dari schema jones.

Namun, klausa AUTHID memungkinkan sebuah stored procedure untuk dijalankan dengan privileges dari invoker-nya (user terkini). Procedures seperti ini tidak dibatasi terhadap sebuah schema tertentu. Sebagai contoh, versi dari drop_id dijalankan dengan privileges dari invoker-nya (pemanggilnya):

CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2)
AUTHID CURRENT_USER AS
BEGIN
  EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;

Referensi tak terkualifikasi ke database object juga ditetapkan di dalam schema dari si pemanggil.

11.6.8. Menggunakan Pragma RESTRICT_REFERENCES

Sebuah function yang dipanggil dari perintah-perintah SQL harus mematuhi aturan-aturan yang dimaksudkan untuk mengontrol efek-efek samping. (Baca kembali Controlling Side Effects dari PL/SQL Subprograms pada pembahasan sebelumnya). Untuk memeriksa pelanggaran terhadap aturan-aturan tersebut, kita dapat menggunakan pragma RESTRICT_REFERENCES. Pragma tersebut menegaskan bahwa sebuah function tidak membaca dan / atau menulis database tables dan / atau package variables.

Namun, jika function body-nya mengandung sebuah perintah dynamic INSERT, UPDATE, atau DELETE, function tersebut selalu menyalahi aturan “write no database state” (WNDS) dan “read no database state” (RNDS). Hal ini dikarenakan perintah-perintah SQL dynamic diperiksa ketika run time, tidak pada saat compile time. Di dalam sebuah perintah EXECUTE IMMEDIATE, hanya klausa INTO yang dapat diperiksa pada saat compile time pelanggaran pelanggaran RNDS.

11.6.9. Menghindari Deadlocks

Dalam beberapa situasi, mengeksekusi sebuah perintah data definition SQL dapat menyebabkan sebuah deadlock. Sebagai contoh, procedure di bawah ini menyebabkan sebuah deadlock karena ia berusaha menghapus dirinya sendiri. Untuk menghindari deadlocks, jangan pernah berusaha untuk melakukan ALTER atau DROP terhadap sebuah subprogram atau package ketika kita masih menggunakannya.

CREATE PROCEDURE calc_bonus (emp_id NUMBER) AS
BEGIN
  ...
  EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus';

Silahkan melanjutkan membaca pembahasan Tuning PL/SQL Applications

 

 

Buku ini membahas berbagai macam tip dan trik yang sangat berguna bagi Anda pembaca pengguna database Oracle. Pemula, mahasiswa, programmer, maupun database administrator yang mengharapkan solusi cepat dalam menangani permasalahan-permasalahan dalam penggunaan database Oracle dapat membaca buku ini. Anda tidak harus membaca buku ini secara urut karena buku ini di susun berdasarkan topik permasalahan, dan Anda dapat membaca topik-topik yang sesuai dengan permasalahan yang Anda hadapi. Dapat di beli di toko buku Gramedia, Gunung Agung, Uranus, Karisma dsb. Harga Rp. 24.800,- (157 halaman)

 

• PL/SQL Object Types
• PL/SQL Packages
• PL/SQL Subprograms
• Menangani PL/SQL Errors
• Interaksi PL/SQL dengan Oracle

 

Copyright © 2012 Hastinapura. Beranda | Berbagi Cerita | Hastinapura | Kelasik | Langgam | Nunung | Obsesi | Propolis | Serasi | Spektakuler

Switch to our mobile site