Oracle Database | rohmad.net

Tulisan ini merupakan lanjutan dari dasar-dasar pengenalan PL/SQL yang telah saya tulis sebelumnya, yaitu contoh penggunaan PL/SQL dan Struktur PL/SQL.

Prosedur merupakan subprogram PL/SQL yang berdiri sendiri. Kalau kita punya pekerjaan rutin dan command-commandnya pun itu-itu saja, kita bisa menyimpan comand-command tersebut dan memanggilnya kapan saja kita mau. Itulah filosofi dari prosedur.

Cara Membuat Prosedur

Caranya sama persis dengan membuat blok PL/SQL biasa, cuma ganti :
DECLARE Menjadi :
create or replace procedure NAMA_PROSEDUR as

User yang membuat prosedur harus punya privilege “create procedure”. Contoh, memberi privilege kepada user ROHMAD agar bisa membuat prosedur
SQL> conn SYSTEM
SQL> grant create procedure to roh;

User yang tidak punya privilege “create procedure” , kalau membuat prosedur akan mendapat error berikut:
ORA-01031: insufficient privileges

Contoh

Sebagai contoh, saya punya tabel MYTAB
create table MYTAB (SYS_CREATION_DATE date, RCG_ID number);

Berikut ini Block PL/SQL untuk mengosongkan dan mengisi ulang table MYTAB. Prosesnya dalah sbb:

1. Truncate table mytab
2. Insert ke tabel mytab, mulai dari rcg_id minimal (10) sampai rcg_id maksimal (100)

DECLARE
V_rcg_min NUMBER;
v_rcg_max number;
V_iterasi NUMBER;
v_date	  DATE;
BEGIN
V_rcg_min := 10;
v_rcg_max := 100;
V_iterasi := V_rcg_min;
V_DATE    := sysdate;
EXECUTE IMMEDIATE 'truncate table MYTAB';
WHILE V_iterasi <= v_rcg_max LOOP
insert into MYTAB values (v_date, V_iterasi);
commit;
v_date   := v_date+1;
V_iterasi:= V_iterasi+1;
END LOOP;
END;
/

Selanjutnya, mari kita coba membuat prosedur berdasarkan block PL/SQL di atas. Ingat kuncinya, ganti kata “DECLARE” menjadi “create or replace procedure NAMA_PROSEDUR as”. Di contoh ini prosedurnya saya beri nama PROC_REFRESH_MYTAB:

create or replace procedure PROC_REFRESH_MYTAB as
V_rcg_min NUMBER;
v_rcg_max number;
V_iterasi NUMBER;
v_date	  DATE;
BEGIN
V_rcg_min := 10;
v_rcg_max := 100;
V_iterasi := V_rcg_min;
V_DATE    := sysdate;
EXECUTE IMMEDIATE 'truncate table MYTAB';
WHILE V_iterasi <= v_rcg_max LOOP
insert into MYTAB values (v_date, V_iterasi);
commit;
v_date   := v_date+1;
V_iterasi:= V_iterasi+1;
END LOOP;
END;
/

Untuk menjalankan prosedur, jalankan:

1. Di SQLPlus
   SQL> exec PROC_REFRESH_MYTAB; atau
   SQL> execute PROC_REFRESH_MYTAB;
2. Di block PL/SQL, tulis saja nama prosedur tersebut
   DECLARE
BEGIN
PROC_REFRESH_MYTAB;
END;
/