Edo::World::Blog

〜Linux/Solaris関連の作業メモ〜

SQL

[Oracle] CSVデータをテーブルに格納

oracle標準添付の"DBMS_UTILITY.COMMA_TO_TABLE"は上手く動かなかったので、ネットから拾った方法で実施。

続きを読む

[Oracle] PL/SQL(その13: コレクション)

コレクションと一口に言っても結合配列、ネストした表、可変サイズの配列(VARRAY)の3種類がある。

本項では結合配列について記載。


続きを読む

[Oracle] PL/SQL(その11: ユーティリティ・パッケージ(スリープ処理))

DBMS_LOCK.SLEEP

  スリープ処理を実行できる(秒指定)。ただし、sysユーザからexecute権限が付与されている必要。

SQL> begin
    for i in 1..5 loop
      dbms_output.put_line(to_char(sysdate,'HH24:MI:SS'));
      dbms_lock.sleep(1);
    end loop;
end;
/

※1秒おきに現在時刻が表示されている
14:32:36
14:32:37
14:32:38
14:32:39
14:32:40

PL/SQLプロシージャが正常に完了しました。

[Oracle] PL/SQL(その10: ユーティリティ・パッケージ(ファイル操作))

UTL_FILE.FOPEN

読み込み

[事前作業]

  対象ファイルにアクセスする為、権限を付与する必要

SQL> show user
ユーザーは"SYS"です。
SQL> create or replace directory utl_data as '/tmp';

ディレクトリが作成されました。

SQL> col directory_path for a20
SQL> select directory_path from all_directories where directory_name = 'UTL_DATA';

DIRECTORY_PATH
--------------------
/tmp    ※'/tmp'が追加されていること

※オブジェクト名はプログラムで使う名前になる
SQL> grant read,write on directory utl_data to scott;

権限付与が成功しました。

SQL> !cat /tmp/test.txt  ※ファイルが作成されていること
hoge
foo
bar

[実行]

SQL> show user
ユーザーは"SCOTT"です。
SQL> declare
    dir_name  varchar2(30);
    file_name varchar2(20);
    fh        utl_file.file_type;
    read_line varchar2(1024);
begin
    dir_name  := 'UTL_DATA';    ※OSから見えるディレクトリ名ではなく、事前に作成したオブジェクト名を指定
    file_name := 'test.txt';
    fh        := utl_file.fopen(dir_name, file_name, 'r');
    begin
        loop
            utl_file.get_line(fh, read_line);    ※1行づつファイル読み取り
            dbms_output.put_line(read_line);    ※読み取った値を表示
        end loop;
    exception
        when no_data_found then null;
    end;
    utl_file.fclose(fh);
end;
/

hoge
foo
bar

PL/SQLプロシージャが正常に完了しました。


書き込み


declare
    dir_name  varchar2(30);
    file_name varchar2(20);
    fh        utl_file.file_type;
    cursor c_emp is select ename,sal from emp;
begin
    dir_name  := 'UTL_DATA';
    file_name := 'result.txt';
    fh        := utl_file.fopen(dir_name, file_name, 'w');
    for r_emp in c_emp loop
        utl_file.put_line(fh, r_emp.ename || ',' || r_emp.sal);
    end loop;
    utl_file.fclose(fh);
end;
/

SQL> !cat /tmp/result.txt
hoge,500
SMITH,800
JONES,2945
CLARK,2450
KING,5000
FORD,3000
MILLER,1300

PL/SQLプロシージャが正常に完了しました。


書き込み(PUTF)


  所謂、PRINTF

SQL> declare
    dir_name  varchar2(30);
    file_name varchar2(20);
    fh        utl_file.file_type;
    cursor c_emp is select ename,sal from emp;
begin
    dir_name  := 'UTL_DATA';
    file_name := 'result.txt';
    fh        := utl_file.fopen(dir_name, file_name, 'w');
    for r_emp in c_emp loop
        utl_file.putf(fh, '%sさんの給与: %s\n', r_emp.ename, r_emp.sal);
    end loop;
    utl_file.fclose(fh);
end;
/

SQL> !cat /tmp/result.txt
hogeさんの給与: 500
SMITHさんの給与: 800
JONESさんの給与: 2945
CLARKさんの給与: 2450
KINGさんの給与: 5000
FORDさんの給与: 3000
MILLERさんの給与: 1300

PL/SQLプロシージャが正常に完了しました。


UTL_FILEで出力される例外

例外 説明
INVALID_PATH 不正なパス指定
INVALID_MODE FOPENの指定モードの誤り
INVALID_FILEHANDLE ファイルハンドラが存在しない
INVALID_OPERATION ファイル操作が正常に実行できない
READ_ERROR 読み取り中にエラー
WRITE_ERROR 書き込み中にエラー
INTERNAL_ERROR 予期せぬエラー

[Oracle] PL/SQL(その9: ストアド・プロシージャとファンクションの違い)

項目 プロシージャ ファンクション
戻り値の数 複数 1つ
戻り値の指定 out return
パラメータモード in,out,in out in
SQLでの使用 不可

プロフィール

edworld

記事検索
アクセスカウンター
  • 今日:
  • 昨日:
  • 累計:

livedoor 天気
  • ライブドアブログ