Oracleメモ - PL/SQL(Oracle)

PL/SQL

  • PL/SQLとは
     Oracle社が開発したプログラミング言語で、データベース問い合わせ言語の標準であるSQLに
    手続き型プログラミング言語を組み合わせたような仕様を持つ。
    
     同社のデータベース管理システムである「Oracle」と組み合わせて使用し、プログラミング言語による処理の記述と、
    SQLコマンドによるデータベースの検索やデータの更新などを組み合わせ、データベースアプリケーションを開発することができる。
    
    高速な処理が必要なプログラムは、ストアドプログラムとしてシステム内に保存することができ、
    高速に実行することができる。また、ストアドプログラムを外部から呼び出しやすいようパッケージ化することにより、
    オブジェクト指向的な開発が可能となっている。
    
    ちなみに、PostgreSQLのPL/pgSQLは、このPL/SQLを参考に実装されたものであるが、データベース管理システム (DBMS) の
    実装に依存する部分(トランザクション管理の概念が異なる、例外を発生させることはできるが、キャッチできないなど)や、
    構文中のシングルクォーテーションをエスケープしなければならないなど完全な互換性はない。
    ( http://ja.wikipedia.org/wiki/PL/SQL 、 http://e-words.jp/w/PL2FSQL.html より抜粋 )   

  • PL/SQLの構文
    DECLARE
        変数,カーソル,関数,例外等の宣言
    BEGIN
        メイン処理
    END;
    
    ※但し、変数などを宣言する必要がなければ DECLARE部は不要。

  • PL/SQLの使用例

  • 変数の宣言
    DECLARE
    
        /* char型 */
        char01       CHAR(10);
        char02       CHAR(10)  :=  '1234567890';  -- 初期値を設定する場合は 宣言に続けて := でセットする
    
        /* varchar2型 */
        varchar01    VARCHAR2(10);
    
        /* number型 */
        number01     NUMBER(10);
        number02     NUMBER(10,2);                -- 全桁数,小数点以下の桁値
    
        /* BOOLEAN型 */
        boolean01    BOOLEAN;
    
        /* ユーザ定義例外 */
        exception01  EXCEPTION;
    
        /* ファイルハンドラ */
        file01       UTL_FILE.FILE_TYPE;
    
        /* レコードタイプ */
        TYPE type01 IS RECORD (
                               ymd     CHAR(6)
                              ,shn_cd  CHAR(10)
                              ,uriage  NUMBER(10)
                              );
        /* レコード変数 */
        rec01 type01;
    
        /* カーソル宣言 */
        CURSOR cur01 IS
               SELECT
                    TABLE_NAME
                   ,TABLESPACE_NAME
               FROM
                    USER_TABLES;
    
        /* カーソルデータ格納用レコード */
        cur01_rec   cur01%ROWTYPE;
    
    BEGIN
       ・
       ・

  • 変数への値代入

  • :=を使用しての値代入
    DECLARE
        varchar01    VARCHAR2(10);
        varchar02    VARCHAR2(10);
    BEGIN
        varchar01 := '1234567890';
        varchar02 := varchar01;
    END;

  • SELECT文からの値代入
    DECLARE
         date_str   VARCHAR2(10);
    BEGIN
         SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') 
           INTO date_str
           FROM DUAL;
    
         DBMS_OUTPUT.PUT_LINE(date_str);
    END;

  • 条件分岐
    BEGIN
    
       char01 := '1';
    
       IF char01 = '1' THEN
           /* char01 = 1 時の処理 */
       ELSE
           /* char01 ≠ 時の処理 */
       END IF;
    
    END;

  • コマンドライン引数の受取
    DECLARE
       /* 1つめの引数を取得し、変数 args01 へセット */
       args01  VARCHAR2(255) := '&1';
       ・
       ・

  • ラベル宣言とGOTO文
    DECLARE
       args01  VARCHAR2(255) := '&1';
    BEGIN
    
       /* 引数から得た値がNULLまたは空文字の時は処理しない */ 
       IF args01 IS NULL OR args01 = '' THEN
           GOTO end_proc;
       END IF;
    
       ・
       ・
    
       <<end_proc>>
       NULL;
    
    END;

  • 繰り返し処理

  • 繰り返し処理(LOOP)
    DECLARE
        LOOP_CNT NUMBER(10);
    BEGIN
    
        -- カウンタ初期化
        LOOP_CNT := 0;
    
        -- 繰り返し処理
        LOOP
            -- カウンタ値を加算
            LOOP_CNT := LOOP_CNT + 1;
    
            -- カウンタが10以上の場合は繰り返し処理終了
            IF LOOP_CNT > 10 THEN
                EXIT;
            END IF;
            /* 
              もしくは
    
              EXIT WHEN LOOP_CNT > 10;
    
              でも可。
            */
    
            /* 繰り返して行う処理 */
             ・
             ・
    
        END LOOP;
    
    END;

  • 繰り返し処理(WHILE LOOP)
    DECLARE
        LOOP_CNT NUMBER(10);
    BEGIN
    
        -- カウンタ初期化
        LOOP_CNT := 0;
    
        -- 繰り返し処理
        WHILE LOOP_CNT < 10 LOOP
    
            -- カウンタ値を加算
            LOOP_CNT := LOOP_CNT + 1;
    
            /* 繰り返して行う処理 */
             ・
             ・
    
        END LOOP;
    
    END;

  • 繰り返し処理(FOR LOOP)
    BEGIN
    
        -- 繰り返し処理
        FOR i IN 1..10 LOOP
    
            /* 繰り返して行う処理 */
             ・
             ・
    
        END LOOP;
    
    END;

  • カーソルの利用

  • カーソルからのデータ読込
    -- DBMS_OUTPUTパッケージからの出力をON
    SET SERVEROUTPUT ON SIZE 1000000;
    
    -- ここからPL/SQLブロック
    DECLARE
    
        /* カーソル宣言 */
        CURSOR cur01 IS
               SELECT
                    TABLE_NAME
                   ,TABLESPACE_NAME
               FROM
                    USER_TABLES;
    
        /* カーソルデータ格納用レコード */
        cur01_rec   cur01%ROWTYPE;
    BEGIN
    
        -- カーソルのOPEN
        OPEN cur01;
    
        -- 1件目の取得
        FETCH cur01 INTO cur01_rec;
    
        -- 繰り返し処理
        LOOP
    
            -- カーソルにデータがない時は繰り返し処理を終了
            IF cur01%NOTFOUND THEN
                EXIT;
            END IF;
    
            -- テーブル名、表領域を表示
            DBMS_OUTPUT.PUT_LINE(cur01_rec.TABLE_NAME || ',' || cur01_rec.TABLESPACE_NAME);
    
            -- 2件目以降の取得
            FETCH cur01 INTO cur01_rec;
    
        END LOOP;
    
        -- カーソルをCLOSE
        CLOSE cur01;
    
    END;

  • カーソルFOR LOOP
    -- DBMS_OUTPUTパッケージからの出力をON
    SET SERVEROUTPUT ON SIZE 1000000;
    
    -- ここからPL/SQLブロック
    DECLARE
    
        /* カーソル宣言 */
        CURSOR cur01 IS
               SELECT
                    TABLE_NAME
                   ,TABLESPACE_NAME
               FROM
                    USER_TABLES;
     BEGIN
    
        -- 繰り返し処理
        FOR rec IN cur01 LOOP
    
            -- テーブル名、表領域を表示
            DBMS_OUTPUT.PUT_LINE(rec.TABLE_NAME || ',' || rec.TABLESPACE_NAME);
    
        END LOOP;
    END;

  • 戻り値の設定
    /* シェルからSQL*PLUSを起動してPL/SQLファイルを実行する場合の戻り値の設定方法 */
    
    --システムエラー発生時、1を戻り値にセットして終了
    WHENEVER OSERROR EXIT 1;
    --ORACLEエラー発生時、独自の戻り値をセットして終了
    WHENEVER SQLERROR EXIT :return_cd;
    --変数宣言
    -- エラーコード(戻り値)
    VARIABLE return_cd NUMBER;
    DECLARE
      ・
      ・
    BEGIN
      ・
      ・
    EXCEPTION
      WHEN XXXXXXXX THEN
        :return_cd := 2;
          ・
          ・
      WHEN OTHERS THEN
        :return_cd := 3;
          ・
          ・
    END;
    /
    EXIT :return_cd
    /

  • 例外の利用

  • 例外のCATCH
    DECLARE
        tbl_name    VARCHAR2(255);
    BEGIN
        SELECT TABLE_NAME
          INTO tbl_name
          FROM USER_TABLES
         WHERE TABLE_NAME = 'DUMMY';
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('データなし!');
    END;
    
    /* 例外時も処理を継続する場合は
       BEGINブロックをネストさせて処理を記述する。
       (極力 functionやprocedureを利用して見やすくする事。)*/
    BEGIN
    
        BEGIN
            SELECT TABLE_NAME
              INTO tbl_name
              FROM USER_TABLES
             WHERE TABLE_NAME = 'DUMMY';
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('データなし!');
        END;
    
       /* 後続処理 */
           ・
           ・
           ・
    END;

  • ユーザ定義例外の利用
    DECLARE
        /* ユーザ定義例外の宣言 */
        exception01  EXCEPTION;
    BEGIN
    
        -- 例外を発生させる
        RAISE exception01;
    
       /* 後続処理 */
        ・
        ・
        ・
    
    EXCEPTION
        WHEN exception01 THEN
            DBMS_OUTPUT.PUT_LINE('exception01が発生しました。');
    END;

  • Functionの利用
    DECLARE
    
        /* 加算処理 */
        FUNCTION func01 ( arg1  IN NUMBER
                         ,arg2  IN NUMBER)
        RETURN NUMBER IS
        BEGIN
     
            RETURN (arg1 + arg2);
     
        END func01;
     
    BEGIN
     
        DBMS_OUTPUT.PUT_LINE('10 + 20 = ' || func01(10,20));
     
    END;

  • Procedureの利用
    DECLARE
     
        -- 計算結果格納用
        result_num NUMBER(10);
     
        /* 加算処理 */
        PROCEDURE proc01 ( arg1  IN NUMBER
                          ,arg2  IN NUMBER
                          ,arg3  IN OUT NUMBER
        ) IS
        BEGIN
     
            arg3 = arg1 + arg2;
     
        END proc01;
     
    BEGIN
     
        proc01(10,20,result_num);
     
        DBMS_OUTPUT.PUT_LINE('10 + 20 = ' || result_num);
     
    END;

  • パッケージの利用

  • 標準出力への表示
    -- 標準出力への表示をON ( SQL*PLUSコマンド )
    SET SERVEROUTPUT ON SIZE 10000;
    
    -- ここからPL/SQLブロック
    BEGIN
        DBMS_OUTPUT.PUT_LINE('TEST!!');
    END;

  • ファイルの読み書き
    DECLARE
        uf01 UTL_FILE.FILE_TYPE;
        file_rec VARCHAR2(255);
    BEGIN
    
        /* ディレクトリ作成
          (PL/SQLでDDLを実行する場合は、動的SQL文かDBMS_SQLパッケージを使用する) */
        EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY TEST_DIR AS ''c:/tmp''';
    
        -- 書込テスト
        uf01 := UTL_FILE.FOPEN( 'TEST_DIR' , 'utl_file_test.txt', 'a', 256);
        UTL_FILE.PUT_LINE ( uf01, '書き込みTEST!!', true);
        UTL_FILE.FCLOSE(uf01);
    
        -- 読込テスト
        uf01 := UTL_FILE.FOPEN( 'TEST_DIR' , 'utl_file_test.txt', 'r', 256);
        LOOP
            file_rec := '';
            BEGIN
                UTL_FILE.GET_LINE ( uf01, file_rec, 256);
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    NULL;
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('READ ERROR!');
            END;
            IF file_rec IS NULL OR file_rec = '' THEN
                EXIT;
            END IF;
            DBMS_OUTPUT.PUT_LINE(file_rec);
        END LOOP;
        UTL_FILE.FCLOSE(uf01);
    END;
    
    ※CREATE DIRECTORYを行うには CREATE ANY DIRECTORY 権限が必要。
      ( grant CREATE ANY DIRECTORY to ユーザ; )
    ※DIRECTORY下のファイルに読み書きを行うには、該当DIRECTORYへの READ,WRITE権限が必要。 
      (GRANT READ,WRITE ON DIRECTORY ディレクトリ名 TO ユーザ; )

  • 乱数の利用
    BEGIN
        -- 1〜10の間の乱数を生成
        DBMS_OUTPUT.PUT_LINE( DBMS_RANDOM.VALUE(1,10) )
    END;

添付ファイル: fileユーザーズ・ガイドおよびリファレンス.pdf 269件 [詳細] filePLSQLパッケージ・プロシージャおよびタイプ・リファレンス.pdf 347件 [詳細]

トップ   差分 バックアップ リロード   一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2009-06-11 (木) 00:48:37 (4351d)