PL/SQL: Membuat Prosedur

Posted on July 15, 2008 by Rohmad

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:

Truncate table mytab
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:

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

Silahkan baca yang ini juga:

This entry was posted in SQL and PL/SQL and tagged Database, oracle, PL/SQL, procedure, program. Bookmark the permalink.

3 Responses to PL/SQL: Membuat Prosedur

doez says:

November 6, 2008 at 9:09 am

thank’s

Reply
agus says:

December 15, 2011 at 5:54 am

waaah., mantap bang.,.

Reply
veri says:

October 24, 2014 at 2:57 am

salam kenal mas..saya veri,,mohon bantuannya,,saya dapat tugas membuat proscedur oracel dari script di bawah ini,,itu bagai mana mna ya syntaxnya,,soalnya saya bingung,,mohon bantuannya,,
#!/usr/local/bin/bash

#Eksekusi Data H-1###########################################
exeDataHn()
{
echo “set trimspool on” >> /home/sitadm/snc_imsi/bin/Hn.sql
echo “set pagesize 0” >> /home/sitadm/snc_imsi/bin/Hn.sql
echo “set term off” >> /home/sitadm/snc_imsi/bin/Hn.sql
echo “set head off” >> /home/sitadm/snc_imsi/bin/Hn.sql
echo “set echo off” >> /home/sitadm/snc_imsi/bin/Hn.sql
echo “set feedback off” >> /home/sitadm/snc_imsi/bin/Hn.sql
echo “spool /home/sitadm/snc_imsi/bin/DtHn.txt” >> /home/sitadm/snc_imsi/bin/Hn.sql
echo “select to_char(to_date(‘$dt’,’yyyymmdd’) – 1,’yyyymmdd’) from dual;” >> /home/sitadm/snc_imsi/bin/Hn.sql
echo “spool off” >> /home/sitadm/snc_imsi/bin/Hn.sql
echo “exit” >> /home/sitadm/snc_imsi/bin/Hn.sql

sqlplus geneva1/geneva1@sitcvg @Hn.sql

chmod 777 DtHn.txt

DtHn=`cat DtHn.txt`

echo StartDate $start >> /home/sitadm/snc_imsi/log/log_$dt.txt

echo “set trimspool on” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “set pagesize 0” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “set term off” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “set head off” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “set echo off” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “set feedback off” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “spool /home/sitadm/snc_imsi/out/subs_postpaid.txt” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “SELECT” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “B.EVENT_SOURCE ||’|’|| ‘NEW'” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “FROM” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “CUSTHASPRODUCT A, CUSTEVENTSOURCE B, CUSTPRODUCTDETAILS C” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “WHERE” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “A.CUSTOMER_REF = B.CUSTOMER_REF AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “A.CUSTOMER_REF = C.CUSTOMER_REF AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “A.PRODUCT_SEQ = C.PRODUCT_SEQ AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “A.PRODUCT_SEQ = B.PRODUCT_SEQ AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “A.PRODUCT_ID = ’53’ AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “B.EVENT_TYPE_ID = ’50’ AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “B.END_DTM IS NULL AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “C.END_DAT IS NULL AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “TRUNC(B.START_DTM)= TO_DATE(‘$DtHn’,’yyyymmdd’)” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “UNION” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “SELECT” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “B.EVENT_SOURCE ||’|’|| ‘DEL'” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “FROM” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “CUSTHASPRODUCT A, CUSTEVENTSOURCE B, CUSTPRODUCTDETAILS C” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “WHERE” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “A.CUSTOMER_REF = B.CUSTOMER_REF AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “A.CUSTOMER_REF = C.CUSTOMER_REF AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “A.PRODUCT_SEQ = C.PRODUCT_SEQ AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “A.PRODUCT_SEQ = B.PRODUCT_SEQ AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “A.PRODUCT_ID = ’53’ AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “B.EVENT_TYPE_ID = ’50’ AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “B.END_DTM IS NOT NULL AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “C.END_DAT IS NOT NULL AND” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “TRUNC(B.END_DTM)= TO_DATE(‘$DtHn’,’yyyymmdd’);” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “spool off” >> /home/sitadm/snc_imsi/bin/imsiHn.sql
echo “exit” >> /home/sitadm/snc_imsi/bin/imsiHn.sql

sqlplus geneva1/geneva1@sitcvg @imsiHn.sql

end=`date +%Y%m%d_%H%M%S`
echo EndDate $end >> /home/sitadm/snc_imsi/log/log_$dt.txt

cd ../out
record=`cat subs_postpaid.txt | wc -l`

echo TotalRecord $record >> /home/sitadm/snc_imsi/log/log_$dt.txt
echo ” ” >> /home/sitadm/snc_imsi/log/log_$dt.txt

mv subs_postpaid.txt subs_postpaid_$DtHn\_$end.txt

cd ../bin

rm Hn.sql DtHn.txt imsiHn.sql
#dtHmin1.txt
}

transFile()
{
cd ../out

echo `sftp rbmpadm@10.128.1.135 1> /dev/null <> /home/sitadm/snc_imsi/bin/dtC.sql
echo “set pagesize 0” >> /home/sitadm/snc_imsi/bin/dtC.sql
echo “set term off” >> /home/sitadm/snc_imsi/bin/dtC.sql
echo “set head off” >> /home/sitadm/snc_imsi/bin/dtC.sql
echo “set echo off” >> /home/sitadm/snc_imsi/bin/dtC.sql
echo “set feedback off” >> /home/sitadm/snc_imsi/bin/dtC.sql
echo “spool /home/sitadm/snc_imsi/bin/dtCheck.txt” >> /home/sitadm/snc_imsi/bin/dtC.sql
echo “select to_char(to_date(‘$md’,’mmdd’) – 0,’mmdd’) from dual;” >> /home/sitadm/snc_imsi/bin/dtC.sql
echo “spool off” >> /home/sitadm/snc_imsi/bin/dtC.sql
echo “exit” >> /home/sitadm/snc_imsi/bin/dtC.sql

sqlplus geneva1/geneva1@sitcvg @dtC.sql

err=`grep ORA dtCheck.txt`
grep ORA dtCheck.txt >> flerror
ora=`cat flerror | cut -c1-3`

rm dtC.sql dtCheck.txt
}

###########################################################
# Mulai Proses
# Untuk menggunakan Manual script yaitu dengan cara
# ./imsirmsh1.sh yyyymmdd

start=`date +%Y%m%d_%H%M%S`

echo $1 >> dtcheck
var=`cat dtcheck | wc -m`

if [ $# -eq 0 ]; then
dt=`date +%Y%m%d`
exeDataHn
transFile
else
if [ $var -eq 9 ]; then
dtC
if [ “$ora” = “ORA” ]; then
error
echo “# $err ”
echo “###################################################################”
rm flerror
else
dt=$1
exeDataHn
transFile
rm dtcheck
rm flerror
fi
else
error
fi
fi

Reply

PL/SQL: Membuat Prosedur

Silahkan baca yang ini juga:

3 Responses to PL/SQL: Membuat Prosedur

Leave a Reply Cancel reply

Tulisan Terbaru

Categories

Archives

Recent Comments

Komunitas Oracle

Oracle Documentation

Oracle Database