PL/SQL - 闘うITエンジニアの覚え書き

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
   /* １つめの引数を取得し、変数 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;

最新の20件

PL/SQL - 闘うITエンジニアの覚え書き

テスト

ビルド

開発メモ

プロジェクト管理

ｻｰﾊﾞ/ｼｽﾃﾑ管理

サーバ/DB関連

その他

Oracleメモ - PL/SQL(Oracle) †

PL/SQL †