>> i's SQUARE >> Oracleのノウハウ >> サンプル集

Oracle PROCEDUE PACKAGE や UNIXシェルのサンプル集



PL/SQLやUNIXシェルの雛形程度のサンプルを備忘録的な感じで書いてます。 といっても、数も少ないし単純だったり、最適化されてませんので恐縮ですが。。。

ちなみに僕自身色々な本を読んで勉強してきましたが、その中でも以下の本が Oracle や PL/SQL について基本から体系的に勉強し直したり、 参考書や入門書として特に良かったので紹介します!




▼プロシージャのサンプル
CREATE OR REPLACE PROCEDURE TEST01
IS
        CURSOR C1
        IS
        select tmp1, tmp2, tmp3 from sample1 where tmp1 is not null;
        W_C1_REC        C1%ROWTYPE;
BEGIN
        FOR W_C1_REC IN C1
        LOOP
                UPDATE sample2
                SET
                        tmp1 = W_C1_REC.tmp1
                WHERE   test = W_C1_REC.tmp2
                AND     hoge = W_C1_REC.tmp3
                ;
        END LOOP;
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('SUCCESS');
EXCEPTION
        WHEN OTHERS THEN
                IF      C1%ISOPEN THEN  CLOSE C1;       END IF;
                ROLLBACK;
                DBMS_OUTPUT.PUT_LINE('ORACLEエラー:' || SUBSTR(SQLERRM,1,100));
END;
/


▼プロシージャを実行するUNIXシェルのサンプル
#!/bin/csh

setenv NLS_LANG   Japanese_Japan.JA16SJIS
setenv ORACLE_SID SID
set userid      = ID
set passwd      = パスワード

sqlplus $userid/$passwd <<EOD
exec TEST01;
EOD

exit 0


▼CSV出力する PACKAGE のサンプル
CREATE OR REPLACE PACKAGE TEST02 IS
  PROCEDURE main(
        in_date         IN VARCHAR2 DEFAULT NULL
       ,ot_err_kbn      OUT NUMBER
       ,ot_err_info     OUT VARCHAR2
  );
        GL_HANDLE_FILE  UTL_FILE.FILE_TYPE;
        GL_FILE_DIR     VARCHAR2(255);
        GL_FILE_NAME    VARCHAR2(255);
END TEST02;
/


▼CSV出力する PACKAGE BODY のサンプル
CREATE OR REPLACE PACKAGE BODY TEST02 IS
  PROCEDURE main(
        in_date         IN VARCHAR2 DEFAULT NULL
       ,ot_err_kbn      OUT NUMBER
       ,ot_err_info     OUT VARCHAR2
  ) IS
        wk_yyyymm       VARCHAR2(6);
        sWork           VARCHAR2(10000);
        CURSOR C1 IS    select tmp1, tmp2, tmp3 from sample1 where tmp1 is not null;
        W_C1_REC        C1%ROWTYPE;
BEGIN
        select to_char(sysdate,'YYYYMM') into wk_yyyymm from dual;
        sWork:=NULL;
        GL_FILE_DIR := '/home/usr/utl_file_dir/';
        GL_FILE_NAME := 'SAMPLE_'|| wk_yyyymm || '_01.csv';

        GL_HANDLE_FILE:=UTL_FILE.FOPEN(GL_FILE_DIR,GL_FILE_NAME,'W');
        UTL_FILE.PUT(GL_HANDLE_FILE,sWork);
        UTL_FILE.FCLOSE(GL_HANDLE_FILE);
        GL_HANDLE_FILE:=UTL_FILE.FOPEN(GL_FILE_DIR,GL_FILE_NAME,'A',5000);

        sWork:='項目1,項目2,項目3,';
        UTL_FILE.PUT_LINE(GL_HANDLE_FILE,sWork);

        FOR W_C1_REC IN C1
        LOOP
                sWork:= W_C1_REC.tmp1;
                sWork:= sWork || ',' || W_C1_REC.tmp2;
                sWork:= sWork || ',' || W_C1_REC.tmp3;
                sWork:= sWork || ',';
                UTL_FILE.PUT_LINE(GL_HANDLE_FILE,sWork);
        END LOOP;
                UTL_FILE.FCLOSE(GL_HANDLE_FILE);
                ot_err_kbn := 0;
        EXCEPTION
                WHEN OTHERS THEN
                    IF  C1%ISOPEN THEN  CLOSE C1;  END IF;
                    ot_err_kbn := 1;
        END;
END TEST02;
/


▼パッケージを実行するUNIXシェルのサンプル
#!/bin/csh

setenv PROG_ID  TEST02
set root_path   = /home/usr
set script_path = $root_path/scripts
set log_path    = $root_path/log
set exec_date   = `date '+%Y%m%d%H%M%S'`
set log_file    = $PROG_ID.log.$exec_date
set userid      = ID
set passwd      = パスワード
setenv NLS_LANG   Japanese_Japan.JA16SJIS
setenv ORACLE_SID `cat $script_path/oracle_sid`

if ( -e $log_path/$log_file ) then
  echo >> $log_path/$log_file
  echo "##########" $PROG_ID "##########" >> $log_path/$log_file
else
  echo "##########" $PROG_ID "##########"  > $log_path/$log_file
  chmod 666 $log_path/$log_file
endif
uname -a >> $log_path/$log_file
date >> $log_path/$log_file

sqlplus -S /nolog >> $log_path/$log_file << EOD
conn $userid/$passwd
set serveroutput on;
var err_kbn      number;
var err_info     varchar2(1024);
declare
 begin
   TEST02.main($exec_date, :err_kbn, :err_info);
 end;
/
exit :err_kbn
EOD

set sts = $status
if ( $sts != 0 ) then
  echo "*** " $PROG_ID ": エラーが発生しました! ***" >> $log_path/$log_file
  exit $sts
endif

echo "##########" $PROG_ID END "##########" >> $log_path/$log_file
date >> $log_path/$log_file

exit $sts



牛乳を飲む人よりも、これを配達する人のほうが健康だ。
by 西洋のことわざ