Oracle/PLSQL

Last-modified: 2008-10-17 (金) 14:54:31

参考サイト

SHIFT the Oracle - PL/SQL

Oracle PLSQL

  • PL/SQL内で使えるのは、DMLとトランザクション制御(TCL)
  • ホスト変数
    SQL*Plusが保持する変数
    [構文]VARIABLE 変数 データ型
    ホスト変数は、PRINTコマンドで表示できる
  • PL/SQLの使用方法
    1. ストアドプログラムをデータベースに登録して、データベース上で起動する方法
    2. PL/SQLブロックとして、クライアントから送って実行する方法
  • PL/SQLブロック（無名ブロックとも）
    • Begin End; のみ必須。他に、Declare、Exceptionなども定義できる。
  • 関数
    • Function・・・戻り値がひとつ。
    • Procedure・・・戻り値が複数。
  • ファイル入出力
    • UTL_FILEパッケージを使う。
    • ディレクトリに対するアクセス権の確認。初期化パラメータUTL_FILE_DIRが関係してくる。 
      SQL> SELECT NAME, VALUE FROM V$PARAMETER2 WHERE NAME = 'UTL_FILE_DIR';

PL/SQLの実行

  • SQL*Plusから実行可能
  • 実行結果を画面表示するには、SQL> SET SERVEROUTPUT ON を実行すること。

組み込み関数

  • mod(x,y) ・・・余りを返す
  • ランダム関数
    • dbms_random.value(最小値, 最大値)
    • dbms_random.string(ルール, 文字列)
      • dbms_random.string('X',10) ・・・大文字アルファベット＋数字の文字列、10桁
      • dbms_random.string('U',10) ・・・大文字アルファベットの文字列、10桁
      • dbms_random.string('L',10) ・・・小文字アルファベットの文字列、10桁
      • dbms_random.string('A',10) ・・・大文字/小文字混在のアルファベットの文字列、10桁

サンプル

  • サンプルをベースに、PLSQLが作れれば幸いです。

PL/SQLブロック編

  • 検索結果が複数あったとき、結果ごとに繰り返し処理を行って表示する。 
    BEGIN
   for data in (SELECT id, name FROM table) LOOP
      DBMS_OUTPUT.PUT_LINE(data.name);
   end LOOP;
END;

トラブル

ORA-29283 / 解消できず(ToT)

環境：Oracle10gXE, Linux
UTL_FILEを使ってみましたが、ORA-29283のエラーが出ました。
対象ディレクトリのオーナーをoracle:dbaに変更してみたり、フルアクセスにしてみたり。
CREATE DIRECTORYや、GRANT CREATE DIRECTORYをしてみたり。
でも、全然ダメっす。
UTL_FILE.FOPEN('/home/hogehoge/plsql','test.csv','r');
でエラーになります。

遊び

  • ここから下は、適当な遊び。
  • こんなPL/SQLを組んでみたくて・・・
    • 要件
      • 大量のデータを投入したい
      • データのバリエーションを外部ファイルで定義したい
      • バリエーション、ばらつき、件数、等柔軟に設定したい
      • テーブルのスキーマを読み込んで、plsqlなり、スクリプトなりを自動生成する。大量カラム対応。