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:

  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;
    /
This entry was posted in SQL and PL/SQL and tagged , , , , . Bookmark the permalink.

3 Responses to PL/SQL: Membuat Prosedur

  1. doez says:
    November 6, 2008 at 9:09 am

    thank’s

  2. agus says:
    December 15, 2011 at 5:54 am

    waaah., mantap bang.,.

  3. 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

Comments are closed.