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;
/
thank’s
waaah., mantap bang.,.
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