Native Dynamic SQL

11.3.1. Beberapa Contoh Dynamic SQLPL/SQL block berikut ini mengandung beberapa contoh dari dynamic SQL:

DECLARE
  sql_stmt VARCHAR2(200);
  plsql_block VARCHAR2(500);
  emp_id NUMBER(4) := 7566;
  salary NUMBER(7,2);
  dept_id NUMBER(2) := 50;
  dept_name VARCHAR2(14) := 'PERSONNEL';
  location VARCHAR2(13) := 'DALLAS';
  emp_rec emp%ROWTYPE;
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
  sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
  EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
  sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
  EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
  plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
  EXECUTE IMMEDIATE plsql_block USING 7788, 500;
  sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
  RETURNING sal INTO :2';
  EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
  EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
  USING dept_id;
  EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;

Dalam contoh tersebut, sebuah standalone procedure menerima nama dari sebuah database tabpe (misalnya ‘emp’) dan sebuah kondisi klausa WHERE opsional (misalnya ’sal > 2000′).

Jika kita mengabaikan kondisi ini, procedure menghapus seluruh baris-baris data dari table. Selain daripada itu, procedure menghapus hanya baris-baris data yang sesuai dengan kondisi tersebut.

CREATE PROCEDURE delete_rows (
table_name IN VARCHAR2,
condition IN VARCHAR2 DEFAULT NULL) AS
where_clause VARCHAR2(100) := ' WHERE ' || condition;
BEGIN
  IF condition IS NULL THEN where_clause := NULL; END IF;
  EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || where_clause;
  EXCEPTION
    ...
END;

11.3.2. Kompatibilitas Kebelakang dari Klausa USING

Ketika sebuah perintah-perintah dynamic INSERT, UPDATE, atau DELETE memiliki sebuah klausa RETURNING, output bind arguments dapat menuju ke dalam klausa RETURNING INTO atau USING tersebut. Dalam aplikasi-aplikasi baru, gunakan klausa RETURNING INTO. Dalam aplikasi-aplikasi lama, kita dapat terus menggunakan klausa USING. Sebagai contoh, kedua perintah-perintah EXECUTE IMMEDIATE berikut ini diperbolehkan:

DECLARE
  sql_stmt VARCHAR2(200);
  my_empno NUMBER(4) := 7902;
  my_ename VARCHAR2(10);
  my_job VARCHAR2(9);
  my_sal NUMBER(7,2) := 3250.00;
BEGIN
  sql_stmt := 'UPDATE emp SET sal = :1 WHERE empno = :2
  RETURNING ename, job INTO :3, :4';
  /* Bind returned values through USING clause. */
  EXECUTE IMMEDIATE sql_stmt
  USING my_sal, my_empno, OUT my_ename, OUT my_job;
  /* Bind returned values through RETURNING INTO clause. */
  EXECUTE IMMEDIATE sql_stmt
  USING my_sal, my_empno RETURNING INTO my_ename, my_job;
  ...
END;

11.3.3. Menentukan Parameter Mode

Dengan klausa USING, kita tidak perlu menentukan sebuah parameter mode untuk input bind arguments karena mode diset default ke IN. Dengan klausa RETURNING INTO, kita tidak dapat menentukan sebuah parameter mode untuk output bind arguments karena, secara definisi, modenya adalah OUT. Berikut ini adalah contohnya:

DECLARE
  sql_stmt VARCHAR2(200);
  dept_id NUMBER(2) := 30;
  old_loc VARCHAR2(13);
BEGIN
  sql_stmt :=
  'DELETE FROM dept WHERE deptno = :1 RETURNING loc INTO :2';
  EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO old_loc;
  ...
END;

Ketika sesuai, kita harus menentukan IN atau IN OUT mode untuk bind arguments yang dilewatkan sebagai parameters. Sebagai contoh, misalkan kita ingin memanggil standalone procedure berikut:

CREATE PROCEDURE create_dept (
deptno IN OUT NUMBER,
dname IN VARCHAR2,
loc IN VARCHAR2) AS
BEGIN
  SELECT deptno_seq.NEXTVAL INTO deptno FROM dual;
  INSERT INTO dept VALUES (deptno, dname, loc);
END;

Untuk memanggil procedure tersebut dari sebuah dynamic PL/SQL block, kita harus menentukan IN OUT mode untuk bind arguments yang terkait dengan formal parameter deptno, seperti berikut:

DECLARE
  plsql_block VARCHAR2(500);
  new_deptno NUMBER(2);
  new_dname VARCHAR2(14) := 'ADVERTISING';
  new_loc VARCHAR2(13) := 'NEW YORK';
BEGIN
  plsql_block := 'BEGIN create_dept(:a, :b, :c); END;';
  EXECUTE IMMEDIATE plsql_block
  USING IN OUT new_deptno, new_dname, new_loc;
  IF new_deptno > 90 THEN ...
END;