DBMS_SQL - PolarDB

Paket DBMS_SQL mendukung kueri SQL dinamis di PolarDB for PostgreSQL (Kompatibel dengan Oracle). Anda dapat membangun kueri selama waktu proses aplikasi.

PolarDB for PostgreSQL (Kompatibel dengan Oracle) menawarkan dukungan asli untuk pernyataan SQL dinamis dan menyediakan metode yang kompatibel dengan basis data Oracle.

Tabel 1. Fungsi dan Prosedur Tersimpan DBMS_SQL

Fungsi atau prosedur tersimpan	Tipe	Tipe nilai kembali	Deskripsi
BIND_VARIABLE(c, nama, nilai [, out_value_size ])	Prosedur tersimpan	Tidak ada	Mengikat nilai ke variabel.
BIND_VARIABLE_CHAR(c, nama, nilai [, out_value_size ])	Prosedur tersimpan	Tidak ada	Mengikat nilai CHAR ke variabel.
BIND_VARIABLE_RAW(c, nama, nilai [, out_value_size ])	Prosedur tersimpan	Tidak ada	Mengikat nilai RAW ke variabel.
CLOSE_CURSOR(c IN OUT)	Prosedur tersimpan	Tidak ada	Menonaktifkan kursor.
COLUMN_VALUE(c, posisi, nilai OUT [, column_error OUT [, actual_length OUT ]])	Prosedur tersimpan	Tidak ada	Mengembalikan nilai kolom ke dalam variabel.
COLUMN_VALUE_CHAR(c, posisi, nilai OUT [, column_error OUT [, actual_length OUT ]])	Prosedur tersimpan	Tidak ada	Mengembalikan nilai kolom CHAR ke dalam variabel.
COLUMN_VALUE_RAW(c, posisi, nilai OUT [, column_error OUT [, actual_length OUT ]])	Prosedur tersimpan	Tidak ada	Mengembalikan nilai kolom RAW ke dalam variabel.
DEFINE_COLUMN(c, posisi, kolom [, column_size ])	Prosedur tersimpan	Tidak ada	Mendefinisikan kolom dalam daftar SELECT.
DEFINE_COLUMN_CHAR(c, posisi, kolom, column_size)	Prosedur tersimpan	Tidak ada	Mendefinisikan kolom CHAR dalam daftar SELECT.
DEFINE_COLUMN_RAW(c, posisi, kolom, column_size)	Prosedur tersimpan	Tidak ada	Mendefinisikan kolom RAW dalam daftar SELECT.
DEFINE_ARRAY(c,posisi,table_variable,cnt, lower_bnd)	Prosedur tersimpan	Tidak ada	Mendefinisikan kolom-kolom ke dalam array tempat Anda ingin mengambil baris.
DESCRIBE_COLUMNS	Prosedur tersimpan	Tidak ada	Mendefinisikan kolom untuk menampung set hasil kursor.
EXECUTE(c)	Fungsi	INTEGER	Menjalankan kursor.
EXECUTE_AND_FETCH(c [, exact ])	Fungsi	INTEGER	Menjalankan kursor dan mengambil satu baris.
FETCH_ROWS(c)	Fungsi	INTEGER	Mengambil baris dari kursor.
IS_OPEN(c)	Fungsi	BOOLEAN	Memeriksa apakah kursor diaktifkan.
LAST_ROW_COUNT	Fungsi	INTEGER	Mengembalikan jumlah total baris yang diambil.
OPEN_CURSOR	Fungsi	INTEGER	Mengaktifkan kursor
PARSE(c, statement, language_flag)	Prosedur tersimpan	Tidak ada	Mengurai pernyataan.

Paket DBMS_SQL di PolarDB sebagian diimplementasikan dibandingkan dengan paket DBMS_SQL Oracle. PolarDB hanya mendukung fungsi dan prosedur tersimpan yang terdaftar dalam tabel sebelumnya.

Tabel berikut mencantumkan variabel publik yang tersedia dalam paket DBMS_SQL.

Tabel 2. Variabel Publik DBMS_SQL

Variabel publik	Tipe data	Nilai	Deskripsi
native	INTEGER	1	Digunakan untuk kompatibilitas dengan sintaks Oracle. Untuk informasi lebih lanjut, lihat DBMS_SQL.PARSE.
V6	INTEGER	2	Digunakan untuk kompatibilitas dengan sintaks Oracle. Untuk informasi lebih lanjut, lihat DBMS_SQL.PARSE.
V7	INTEGER	3	Digunakan untuk kompatibilitas dengan sintaks Oracle. Untuk informasi lebih lanjut, lihat DBMS_SQL.PARSE.

BIND_VARIABLE

Prosedur tersimpan BIND_VARIABLE digunakan untuk mengikat nilai ke variabel bind IN atau IN OUT dalam pernyataan SQL.

BIND_VARIABLE(c INTEGER, nama VARCHAR2,
  nilai { BLOB | CLOB | DATE | FLOAT | INTEGER | NUMBER |
          TIMESTAMP | VARCHAR2 }
  [, out_value_size INTEGER ])

Parameters

Parameter	Deskripsi
c	ID kursor untuk pernyataan SQL yang berisi variabel bind.
nama	Nama variabel bind dalam pernyataan SQL.
nilai	Nilai yang akan ditetapkan.
out_value_size	Jika `nama` adalah variabel IN OUT, parameter ini mendefinisikan panjang maksimum nilai keluaran. Jika parameter ini tidak ditentukan, panjang nilai saat ini menjadi panjang maksimum secara default.

Examples

Blok anonim berikut menggunakan variabel bind untuk menyisipkan baris ke dalam tabel emp.

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(150) := 'INSERT INTO emp VALUES ' ||
                        '(:p_empno, :p_ename, :p_job, :p_mgr, ' ||
                        ':p_hiredate, :p_sal, :p_comm, :p_deptno)';
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
    v_mgr           emp.mgr%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_comm          emp.comm%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    v_empno    := 9001;
    v_ename    := 'JONES';
    v_job      := 'SALESMAN';
    v_mgr      := 7369;
    v_hiredate := TO_DATE('13-DEC-07','DD-MON-YY');
    v_sal      := 8500.00;
    v_comm     := 1500.00;
    v_deptno   := 40;
    DBMS_SQL.BIND_VARIABLE(curid,':p_empno',v_empno);
    DBMS_SQL.BIND_VARIABLE(curid,':p_ename',v_ename);
    DBMS_SQL.BIND_VARIABLE(curid,':p_job',v_job);
    DBMS_SQL.BIND_VARIABLE(curid,':p_mgr',v_mgr);
    DBMS_SQL.BIND_VARIABLE(curid,':p_hiredate',v_hiredate);
    DBMS_SQL.BIND_VARIABLE(curid,':p_sal',v_sal);
    DBMS_SQL.BIND_VARIABLE(curid,':p_comm',v_comm);
    DBMS_SQL.BIND_VARIABLE(curid,':p_deptno',v_deptno);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Jumlah baris diproses: ' || v_status);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

Output serupa ditampilkan:

Jumlah baris diproses: 1

BIND_VARIABLE_CHAR

Prosedur tersimpan BIND_VARIABLE_CHAR digunakan untuk mengikat nilai CHAR ke variabel bind IN atau IN OUT dalam pernyataan SQL.

BIND_VARIABLE_CHAR(c INTEGER, nama VARCHAR2, nilai CHAR
  [, out_value_size INTEGER ])

Parameters

Parameter	Deskripsi
c	ID kursor untuk pernyataan SQL yang berisi variabel bind.
nama	Nama variabel bind dalam pernyataan SQL.
nilai	Nilai tipe CHAR yang akan ditetapkan.
out_value_size	Jika nama adalah variabel IN OUT, parameter ini mendefinisikan panjang maksimum nilai keluaran. Jika parameter ini tidak ditentukan, panjang nilai saat ini menjadi panjang maksimum secara default.

BIND_VARIABLE_RAW

Prosedur tersimpan BIND_VARIABLE_RAW digunakan untuk mengikat nilai RAW ke variabel bind IN atau IN OUT dalam pernyataan SQL.

BIND_VARIABLE_RAW(c INTEGER, nama VARCHAR2, nilai RAW
  [, out_value_size INTEGER ])

Parameters

Parameter	Deskripsi
c	ID kursor untuk pernyataan SQL yang berisi variabel bind.
nama	Nama variabel bind dalam pernyataan SQL.
nilai	Nilai tipe CHAR yang akan ditetapkan.
out_value_size	Jika `nama` adalah variabel IN OUT, parameter ini mendefinisikan panjang maksimum nilai keluaran. Jika parameter ini tidak ditentukan, panjang nilai saat ini menjadi panjang maksimum secara default.

CLOSE_CURSOR

Prosedur tersimpan CLOSE_CURSOR digunakan untuk menonaktifkan kursor. Saat kursor dinonaktifkan, sumber daya yang dialokasikan ke kursor dilepaskan dan kursor tidak dapat lagi digunakan.

CLOSE_CURSOR(c IN OUT INTEGER)

Parameters

Parameter	Deskripsi
c	ID kursor.

Examples

Contoh berikut menunjukkan cara menonaktifkan kursor.

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
            .
            .
            .
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

COLUMN_VALUE

Prosedur tersimpan COLUMN_VALUE digunakan untuk mendefinisikan variabel guna menerima nilai dari kursor.

COLUMN_VALUE(c INTEGER, posisi INTEGER, nilai OUT { BLOB |
  CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 }
  [, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

Parameters

Parameter	Deskripsi
c	ID kursor yang mengembalikan data ke variabel yang didefinisikan.
posisi	Posisi data yang dikembalikan dalam kursor. Nilai pertama dalam kursor adalah posisi 1.
nilai	Variabel yang menerima data yang dikembalikan dalam kursor oleh panggilan FETCH sebelumnya.
column_error	Jika terjadi kesalahan, parameter ini menunjukkan kode kesalahan yang terkait dengan kolom.
actual_length	Panjang aktual data sebelum pemotongan.

Examples

Contoh berikut menunjukkan bagian dari blok anonim yang menerima nilai dari kursor menggunakan prosedur tersimpan COLUMN_VALUE.

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
            .
            .
            .
    LOOP
        v_status := DBMS_SQL.FETCH_ROWS(curid);
        EXIT WHEN v_status = 0;
        DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);
        DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);
        DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || RPAD(v_ename,10) || '  ' ||
            TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||
            TO_CHAR(v_sal,'9,999.99') || ' ' ||
            TO_CHAR(NVL(v_comm,0),'9,999.99'));
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

COLUMN_VALUE_CHAR

Prosedur tersimpan COLUMN_VALUE_CHAR digunakan untuk mendefinisikan variabel guna menerima nilai CHAR dari kursor.

COLUMN_VALUE_CHAR(c INTEGER, posisi INTEGER, nilai OUT CHAR
  [, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

Parameters

Parameter	Deskripsi
c	ID kursor yang mengembalikan data ke variabel yang didefinisikan.
posisi	Posisi data yang dikembalikan dalam kursor. Nilai pertama dalam kursor adalah posisi 1.
nilai	Variabel bertipe data CHAR yang menerima data yang dikembalikan dalam kursor oleh panggilan FETCH sebelumnya.
column_error	Jika terjadi kesalahan, parameter ini menunjukkan kode kesalahan yang terkait dengan kolom.
actual_length	Panjang aktual data sebelum pemotongan.

COLUMN_VALUE_RAW

Prosedur tersimpan COLUMN_VALUE_RAW digunakan untuk mendefinisikan variabel guna menerima nilai RAW dari kursor.

COLUMN_VALUE_RAW(c INTEGER, posisi INTEGER, nilai OUT RAW
  [, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

Parameters

Parameter	Deskripsi
c	ID kursor yang mengembalikan data ke variabel yang didefinisikan.
posisi	Posisi data yang dikembalikan dalam kursor. Nilai pertama dalam kursor adalah posisi 1.
nilai	Variabel bertipe data RAW yang menerima data yang dikembalikan dalam kursor oleh panggilan FETCH sebelumnya.
column_error	Jika terjadi kesalahan, parameter ini menunjukkan kode kesalahan yang terkait dengan kolom.
actual_length	Panjang aktual data sebelum pemotongan.

DEFINE_COLUMN

Prosedur tersimpan DEFINE_COLUMN digunakan untuk mendefinisikan kolom atau ekspresi dalam daftar SELECT yang akan dikembalikan dan diambil dalam kursor.

DEFINE_COLUMN(c INTEGER, posisi INTEGER, kolom { BLOB |
  CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 }
  [, column_size INTEGER ])

Parameters

Parameter	Deskripsi
c	ID kursor yang terkait dengan pernyataan SELECT.
posisi	Posisi kolom atau ekspresi dalam daftar SELECT yang sedang didefinisikan.
kolom	Variabel yang sesuai dengan tipe data kolom atau ekspresi pada posisi tertentu dalam set hasil SELECT.
column_size	Panjang maksimum data yang dikembalikan. Parameter column_size harus ditentukan jika tipe data kolom adalah VARCHAR2. Data yang dikembalikan melebihi column_size akan dipotong menjadi panjang maksimum yang ditentukan oleh parameter column_size.

Examples

Contoh berikut menunjukkan cara menggunakan prosedur tersimpan DEFINE_COLUMN untuk mendefinisikan kolom empno, ename, hiredate, sal, dan comm dari tabel emp.

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);
            .
            .
            .
END;

Contoh berikut menunjukkan alternatif dari contoh sebelumnya yang menghasilkan hasil yang sama persis. Perhatikan bahwa panjang tipe data tidak relevan. Kolom empno, sal, dan comm masih akan mengembalikan data setara dengan NUMBER(4) dan NUMBER(7,2), meskipun v_num didefinisikan sebagai NUMBER(1). Kolom ename akan mengembalikan data hingga sepuluh karakter panjangnya seperti yang didefinisikan oleh parameter panjang dalam pemanggilan DEFINE_COLUMN. Panjang yang ditunjukkan oleh tipe data VARCHAR2(1) yang dinyatakan untuk v_varchar diabaikan. Ukuran sebenarnya dari data yang dikembalikan ditentukan oleh prosedur tersimpan DEFINE_COLUMN.

DECLARE
    curid           INTEGER;
    v_num           NUMBER(1);
    v_varchar       VARCHAR2(1);
    v_date          DATE;
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_num);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_varchar,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_date);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_num);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_num);
            .
            .
            .
END;

DEFINE_COLUMN_CHAR

Prosedur tersimpan DEFINE_COLUMN_CHAR digunakan untuk mendefinisikan kolom CHAR atau ekspresi dalam daftar SELECT yang akan dikembalikan dan diambil dalam kursor.

DEFINE_COLUMN_CHAR(c INTEGER, posisi INTEGER, kolom CHAR, column_size INTEGER)

Parameters

Parameter	Deskripsi
c	ID kursor yang terkait dengan pernyataan SELECT.
posisi	Posisi kolom atau ekspresi dalam daftar SELECT yang sedang didefinisikan.
kolom	Variabel CHAR.
column_size	Panjang maksimum data yang dikembalikan. Data yang dikembalikan melebihi column_size akan dipotong menjadi column_size karakter.

DEFINE_COLUMN_RAW

Prosedur tersimpan DEFINE_COLUMN_RAW digunakan untuk mendefinisikan kolom RAW atau ekspresi dalam daftar SELECT yang akan dikembalikan dan diambil dalam kursor.

DEFINE_COLUMN_RAW(c INTEGER, posisi INTEGER, kolom RAW,
  column_size INTEGER)

Parameters

Parameter	Deskripsi
c	ID kursor yang terkait dengan pernyataan SELECT.
posisi	Posisi kolom atau ekspresi dalam daftar SELECT yang sedang didefinisikan.
kolom	Variabel RAW.
column_size	Panjang maksimum data yang dikembalikan. Data yang dikembalikan melebihi column_size akan dipotong menjadi column_size karakter.

DEFINE_ARRAY

Prosedur tersimpan DEFINE_ARRAY mendefinisikan kolom-kolom ke dalam array tempat Anda ingin mengambil baris. Sintaks:

DEFINE_ARRAY (
   c           IN INTEGER,
   posisi      IN INTEGER,
   <table_variable>    IN <tipe_data>,
   cnt         IN INTEGER,
   lower_bnd   IN INTEGER);

Parameters

Parameter	Deskripsi
c	ID kursor yang akan diikat ke array.
posisi	Posisi relatif kolom dalam array.
table_variable	Variabel yang dideklarasikan sebagai <tipe_data>. Nilai valid dari <tipe_data>: varchar2_table clob_table binary_float_table binary_double_table blob_table date_table number_table timestamp_table
cnt	Jumlah baris yang diambil. Harus berupa integer yang lebih besar dari 0.
lower_bnd	Hasil disalin ke dalam array, dimulai dari indeks batas bawah ini.

Examples

Blok anonim berikut membuat tabel bernama t dan mengambil dua baris data dari tabel t.

create table t as select i as a,2 * i as b,3 * i as c from generate_series(1,3) i;

DECLARE
  c      INTEGER;
  d      NUMBER;
  n_tab  dbms_sql.varchar2_Table;
  n_tab1  dbms_sql.varchar2_Table;
BEGIN
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,
                 'select * from t',
                 dbms_sql.native);
  dbms_sql.define_array(c,1,n_tab,2,1);
  d := dbms_sql.execute(c);
  d := dbms_sql.fetch_rows(c);
  dbms_output.put_line('fetch rows is ' || d);
  dbms_sql.column_value(c,
                          1,
                          n_tab1);
    FOR i IN 1 .. d LOOP
      dbms_output.put_line(n_tab1(i));
    END LOOP;
  dbms_sql.close_cursor(c);
END;

Output serupa ditampilkan:

fetch rows is 2
1
2

DESCRIBE_COLUMNS

Prosedur tersimpan DESCRIBE_COLUMNS digunakan untuk mendeskripsikan kolom-kolom yang dikembalikan oleh kursor.

DESCRIBE_COLUMNS(c INTEGER, col_cnt OUT INTEGER, desc_t OUT
  DESC_TAB);

Parameters

Parameter	Deskripsi
c	ID kursor.
col_cnt	Jumlah kolom dalam set hasil kursor.
desc_tab	Tabel yang berisi deskripsi setiap kolom yang dikembalikan oleh kursor. Deskripsi tersebut bertipe DESC_REC, dan berisi nilai-nilai dalam tabel berikut.

Nama kolom	Tipe
col_type	INTEGER
col_max_len	INTEGER
col_name	VARCHAR2(128)
col_name_len	INTEGER
col_schema_name	VARCHAR2(128)
col_schema_name_len	INTEGER
col_precision	INTEGER
col_scale	INTEGER
col_charsetid	INTEGER
col_charsetform	INTEGER
col_null_ok	BOOLEAN

EXECUTE

Fungsi EXECUTE digunakan untuk menjalankan pernyataan SQL atau blok SPL yang telah diurai.

status INTEGER EXECUTE(c INTEGER)

Parameters

Parameter	Deskripsi
c	ID kursor dari pernyataan SQL atau blok SPL yang telah diurai.
status	Jika pernyataan SQL adalah DELETE, INSERT, atau UPDATE, parameter ini menunjukkan jumlah catatan yang diproses. Parameter ini tidak memiliki arti untuk pernyataan lainnya.

Examples

Blok anonim berikut menyisipkan baris ke dalam tabel dept.

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(50);
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'INSERT INTO dept VALUES (50, ''HR'', ''LOS ANGELES'')';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Jumlah baris diproses: ' || v_status);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

EXECUTE_AND_FETCH

Fungsi EXECUTE_AND_FETCH digunakan untuk menjalankan pernyataan SELECT yang telah diurai dan mengambil satu baris.

status INTEGER EXECUTE_AND_FETCH(c INTEGER
  [, exact BOOLEAN ])

Parameters

Parameter	Deskripsi
c	ID kursor untuk pernyataan SELECT.
exact	Jika parameter ini diatur ke FALSE, tidak ada pengecualian yang terjadi. Nilai default: FALSE. Jika parameter ini diatur ke TRUE dan jumlah baris dalam set hasil tidak sama dengan 1, pengecualian terjadi. Jika parameter ini diatur ke TRUE dan set hasil tidak berisi catatan, pengecualian NO_DATE_FOUND akan terjadi. Jika parameter ini diatur ke TRUE dan set hasil berisi beberapa catatan, pengecualian TOO_MANY_ROWS akan terjadi.
status	Jika baris diambil, 1 dikembalikan. Jika tidak ada baris yang diambil, 0 dikembalikan. Jika terjadi pengecualian, tidak ada nilai yang dikembalikan.

Examples

Prosedur tersimpan berikut menggunakan fungsi EXECUTE_AND_FETCH untuk mengambil satu karyawan menggunakan nama karyawan. Jika karyawan tidak ditemukan, atau lebih dari satu karyawan dengan nama yang sama ditemukan, pengecualian akan terjadi.

CREATE OR REPLACE PROCEDURE select_by_name(
    p_ename         emp.ename%TYPE
)
IS
    curid           INTEGER;
    v_empno         emp.empno%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_comm          emp.comm%TYPE;
    v_dname         dept.dname%TYPE;
    v_disp_date     VARCHAR2(10);
    v_sql           VARCHAR2(120) := 'SELECT empno, hiredate, sal, ' ||
                                     'NVL(comm, 0), dname ' ||
                                     'FROM emp e, dept d ' ||
                                     'WHERE ename = :p_ename ' ||
                                     'AND e.deptno = d.deptno';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.BIND_VARIABLE(curid,':p_ename',UPPER(p_ename));
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_comm);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_dname,14);
    v_status := DBMS_SQL.EXECUTE_AND_FETCH(curid,TRUE);
    DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
    DBMS_SQL.COLUMN_VALUE(curid,2,v_hiredate);
    DBMS_SQL.COLUMN_VALUE(curid,3,v_sal);
    DBMS_SQL.COLUMN_VALUE(curid,4,v_comm);
    DBMS_SQL.COLUMN_VALUE(curid,5,v_dname);
    v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY');
    DBMS_OUTPUT.PUT_LINE('Nomor    : ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Nama      : ' || UPPER(p_ename));
    DBMS_OUTPUT.PUT_LINE('Tanggal Masuk : ' || v_disp_date);
    DBMS_OUTPUT.PUT_LINE('Gaji    : ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('Komisi: ' || v_comm);
    DBMS_OUTPUT.PUT_LINE('Departemen: ' || v_dname);
    DBMS_SQL.CLOSE_CURSOR(curid);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Karyawan ' || p_ename || ' tidak ditemukan');
        DBMS_SQL.CLOSE_CURSOR(curid);
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Terlalu banyak karyawan bernama, ' ||
            p_ename || ', ditemukan');
        DBMS_SQL.CLOSE_CURSOR(curid);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Berikut adalah SQLERRM:');
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
        DBMS_OUTPUT.PUT_LINE('Berikut adalah SQLCODE:');
        DBMS_OUTPUT.PUT_LINE(SQLCODE);
        DBMS_SQL.CLOSE_CURSOR(curid);
END;

Output serupa ditampilkan:

EXEC select_by_name('MARTIN')

Nomor    : 7654
Nama      : MARTIN
Tanggal Masuk : 09/28/1981
Gaji    : 1250
Komisi: 1400
Departemen: SALES

FETCH_ROWS

Fungsi FETCH_ROWS digunakan untuk mengambil baris dari kursor.

status INTEGER FETCH_ROWS(c INTEGER)

Parameters

Parameter	Deskripsi
c	ID kursor yang digunakan untuk mengambil baris.
status	Jika baris diambil, 1 dikembalikan. Jika tidak ada baris yang diambil, 0 dikembalikan.

Examples

Contoh berikut mengambil baris dari tabel emp dan menampilkan hasilnya.

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);

    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME       HIREDATE    SAL       COMM');
    DBMS_OUTPUT.PUT_LINE('-----  ----------  ----------  --------  ' ||
        '--------');
    LOOP
        v_status := DBMS_SQL.FETCH_ROWS(curid);
        EXIT WHEN v_status = 0;
        DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);
        DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);
        DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || RPAD(v_ename,10) || '  ' ||
            TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||
            TO_CHAR(v_sal,'9,999.99') || ' ' ||
            TO_CHAR(NVL(v_comm,0),'9,999.99'));
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

Output serupa ditampilkan:

EMPNO  ENAME       HIREDATE    SAL       COMM
-----  ----------  ----------  --------  --------
7369   SMITH       1980-12-17    800.00       .00
7499   ALLEN       1981-02-20  1,600.00    300.00
7521   WARD        1981-02-22  1,250.00    500.00
7566   JONES       1981-04-02  2,975.00       .00
7654   MARTIN      1981-09-28  1,250.00  1,400.00
7698   BLAKE       1981-05-01  2,850.00       .00
7782   CLARK       1981-06-09  2,450.00       .00
7788   SCOTT       1987-04-19  3,000.00       .00
7839   KING        1981-11-17  5,000.00       .00
7844   TURNER      1981-09-08  1,500.00       .00
7876   ADAMS       1987-05-23  1,100.00       .00
7900   JAMES       1981-12-03    950.00       .00
7902   FORD        1981-12-03  3,000.00       .00
7934   MILLER      1982-01-23  1,300.00       .00

IS_OPEN

Fungsi IS_OPEN digunakan untuk memeriksa apakah kursor yang ditentukan diaktifkan.

status BOOLEAN IS_OPEN(c INTEGER)

Parameters

Parameter	Deskripsi
c	ID kursor yang akan diperiksa.
status	Jika kursor diaktifkan, parameter ini disetel ke TRUE. Jika kursor dinonaktifkan, parameter ini disetel ke FALSE.

JUMLAH BARIS TERAKHIR

Fungsi LAST_ROW_COUNT digunakan untuk mengembalikan jumlah total baris yang diambil.

rowcnt INTEGER LAST_ROW_COUNT

Parameters

Parameter	Deskripsi
rowcnt	Jumlah total baris yang diambil.

Examples

Contoh berikut menggunakan fungsi LAST_ROW_COUNT untuk menampilkan jumlah total baris yang diambil dalam kueri.

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);

    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME       HIREDATE    SAL       COMM');
    DBMS_OUTPUT.PUT_LINE('-----  ----------  ----------  --------  ' ||
        '--------');
    LOOP
        v_status := DBMS_SQL.FETCH_ROWS(curid);
        EXIT WHEN v_status = 0;
        DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);
        DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);
        DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || RPAD(v_ename,10) || '  ' ||
            TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||
            TO_CHAR(v_sal,'9,999.99') || ' ' ||
            TO_CHAR(NVL(v_comm,0),'9,999.99'));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Jumlah baris: ' || DBMS_SQL.LAST_ROW_COUNT);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

Output serupa ditampilkan:

EMPNO  ENAME       HIREDATE    SAL       COMM
-----  ----------  ----------  --------  --------
7369   SMITH       1980-12-17    800.00       .00
7499   ALLEN       1981-02-20  1,600.00    300.00
7521   WARD        1981-02-22  1,250.00    500.00
7566   JONES       1981-04-02  2,975.00       .00
7654   MARTIN      1981-09-28  1,250.00  1,400.00
7698   BLAKE       1981-05-01  2,850.00       .00
7782   CLARK       1981-06-09  2,450.00       .00
7788   SCOTT       1987-04-19  3,000.00       .00
7839   KING        1981-11-17  5,000.00       .00
7844   TURNER      1981-09-08  1,500.00       .00
7876   ADAMS       1987-05-23  1,100.00       .00
7900   JAMES       1981-12-03    950.00       .00
7902   FORD        1981-12-03  3,000.00       .00
7934   MILLER      1982-01-23  1,300.00       .00
Jumlah baris: 14

OPEN_CURSOR

Fungsi OPEN_CURSOR digunakan untuk membuat kursor. Kursor harus digunakan untuk mengurai dan menjalankan pernyataan SQL dinamis. Setelah diaktifkan, kursor dapat digunakan kembali dengan pernyataan SQL yang sama atau berbeda tanpa perlu menonaktifkan dan mengaktifkan kursor lagi.

c INTEGER OPEN_CURSOR

Parameters

Parameter	Deskripsi
c	ID kursor baru yang dibuat.

Examples

Contoh berikut menunjukkan cara membuat kursor baru.

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
            .
            .
            .
END;

PARSE

Prosedur tersimpan PARSE digunakan untuk mengurai pernyataan SQL atau blok SPL. Untuk pernyataan DDL, pernyataan tersebut langsung dieksekusi dan tidak memerlukan pemanggilan fungsi EXECUTE.

PARSE(c INTEGER, statement VARCHAR2, language_flag INTEGER)

Parameters

Parameter	Deskripsi
c	ID kursor yang telah diaktifkan.
statement	Pernyataan SQL atau blok SPL yang akan diurai. Pernyataan SQL tidak boleh diakhiri dengan titik koma (;). Blok SPL harus diakhiri dengan titik koma (;).
language_flag	Digunakan untuk kompatibilitas dengan sintaks Oracle. Nilai valid: DBMS_SQL.V6, DBMS_SQL.V7, dan DBMS_SQL.native. Bendera ini diabaikan, dan semua sintaks berada dalam format PolarDB for PostgreSQL (Kompatibel dengan Oracle).

Examples

Blok anonim berikut membuat tabel bernama job. Perhatikan bahwa pernyataan DDL langsung dieksekusi oleh prosedur tersimpan PARSE dan tidak memerlukan pemanggilan fungsi EXECUTE.

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid, 'CREATE TABLE job (jobno NUMBER(3), ' ||
        'jname VARCHAR2(9))',DBMS_SQL.native);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

Kode berikut menyisipkan dua baris ke dalam tabel job.

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(50);
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'INSERT INTO job VALUES (100, ''ANALYST'')';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Jumlah baris diproses: ' || v_status);
    v_sql := 'INSERT INTO job VALUES (200, ''CLERK'')';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Jumlah baris diproses: ' || v_status);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

Output serupa ditampilkan:

Jumlah baris diproses: 1
Jumlah baris diproses: 1

Blok anonim berikut menggunakan paket DBMS_SQL untuk menjalankan blok yang berisi dua pernyataan INSERT. Perhatikan bahwa akhir blok berisi tanda titik koma penutup (;), sedangkan dalam contoh OPEN_CURSOR, setiap pernyataan INSERT individu tidak memiliki tanda titik koma penutup (;).

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(100);
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'BEGIN ' ||
               'INSERT INTO job VALUES (300, ''MANAGER''); '  ||
               'INSERT INTO job VALUES (400, ''SALESMAN''); ' ||
             'END;';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;