SQL

PLSQLテンプレート

  • このエントリーをはてなブックマークに追加

ロジックを組み込んだ抽出処理や更新処理などを作成する場合、複雑なSQLを作成するよりも、PLSQLを使った方が直感的にわかりやすいロジックを作ることができます。

ただ、PLSQLの構文は記憶はしていないので、ここによく使いそうなPLSQLのテンプレートを作っておいて、毎回ここからコピペできるようにしておこうと思います。

テンプレートのPLSQLはDBを汚さないようにするために、無名ブロックで定義しています。

PLSQLテンプレート

1件データ抽出及びデータ出力

SET SERVEROUTPUT ON
SET LINESIZE 32767
SET PAGESIZE 50000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TAB OFF
SET TIMING OFF
SET TRIMSPOOL ON

SPOOL 1件データ抽出及びデータ出力.tsv

----------------------------------------
-- 宣言部
----------------------------------------
DECLARE
    -- 変数の宣言
    WRK_ID   NUMBER(4);
    WRK_NAME VARCHAR2(20);
----------------------------------------
-- 実行部
----------------------------------------
BEGIN
    SELECT
        ID
      , NAME
    INTO
        WRK_ID
      , WRK_NAME
    FROM
        TB_SAMPLE
    WHERE
        ID = 1
    ;
    DBMS_OUTPUT.PUT_LINE(WRK_ID || CHR(9) || WRK_NAME);
----------------------------------------
-- 例外部
----------------------------------------
EXCEPTION
    WHEN OTHERS THEN
        -- 例外情報を出力
        DBMS_OUTPUT.PUT_LINE('エラーコード:'|| SQLCODE || ' ' || 'エラーメッセージ:' || SQLERRM(SQLCODE));
END;
/

SPOOL OFF

カーソルを使ってデータ抽出及びデータ出力(パラメータなし)

SET SERVEROUTPUT ON
SET LINESIZE 32767
SET PAGESIZE 50000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TAB OFF
SET TIMING OFF
SET TRIMSPOOL ON


SPOOL カーソルを使ってデータ抽出及びデータ出力(パラメータなし).tsv

----------------------------------------
-- 宣言部
----------------------------------------
DECLARE
    -- カーソルの宣言
    CURSOR CUR_SAMPLE IS
    SELECT
        ID
      , NAME
    FROM
        TB_SAMPLE
    ORDER BY
        ID
    ;
    REC_SAMPLE CUR_SAMPLE%ROWTYPE;
----------------------------------------
-- 実行部
----------------------------------------
BEGIN
    OPEN CUR_SAMPLE;
    LOOP
        FETCH CUR_SAMPLE INTO REC_SAMPLE;
        EXIT WHEN CUR_SAMPLE%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(REC_SAMPLE.ID || CHR(9) || REC_SAMPLE.NAME);
    END LOOP;
    CLOSE CUR_SAMPLE;
----------------------------------------
-- 例外部
----------------------------------------
EXCEPTION
    WHEN OTHERS THEN
        -- 例外情報を出力
        DBMS_OUTPUT.PUT_LINE('エラーコード:'|| SQLCODE || ' ' || 'エラーメッセージ:' || SQLERRM(SQLCODE));
        CLOSE CUR_SAMPLE;
END;
/

SPOOL OFF

カーソルを使ってデータ抽出及びデータ出力(パラメータあり)

SET SERVEROUTPUT ON
SET LINESIZE 32767
SET PAGESIZE 50000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TAB OFF
SET TIMING OFF
SET TRIMSPOOL ON


SPOOL カーソルを使ってデータ抽出及びデータ出力(パラメータあり).tsv

----------------------------------------
-- 宣言部
----------------------------------------
DECLARE
    -- カーソルの宣言
    CURSOR CUR_SAMPLE(PARAM_ID IN NUMBER) IS
    SELECT
        ID
      , NAME
    FROM
        TB_SAMPLE
    WHERE
        ID = PARAM_ID
    ORDER BY
        ID
    ;

    REC_SAMPLE CUR_SAMPLE%ROWTYPE;
----------------------------------------
-- 実行部
----------------------------------------
BEGIN
    OPEN CUR_SAMPLE(2);
    LOOP
        FETCH CUR_SAMPLE INTO REC_SAMPLE;
        EXIT WHEN CUR_SAMPLE%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(REC_SAMPLE.ID || CHR(9) || REC_SAMPLE.NAME);
    END LOOP;
    CLOSE CUR_SAMPLE;
----------------------------------------
-- 例外部
----------------------------------------
EXCEPTION
    WHEN OTHERS THEN
        -- 例外情報を出力
        DBMS_OUTPUT.PUT_LINE('エラーコード:'|| SQLCODE || ' ' || 'エラーメッセージ:' || SQLERRM(SQLCODE));
        CLOSE CUR_SAMPLE;
END;
/

SPOOL OFF

カーソルを使ってデータ抽出及びデータ登録・更新・削除

SET SERVEROUTPUT ON

----------------------------------------
-- 宣言部
----------------------------------------
DECLARE
    -- カーソルの宣言
    CURSOR CUR_SAMPLE IS
    SELECT
        ID
      , NAME
    FROM
        TB_SAMPLE
    ORDER BY
        ID
    ;

    REC_SAMPLE CUR_SAMPLE%ROWTYPE;
----------------------------------------
-- 実行部
----------------------------------------
BEGIN
    OPEN CUR_SAMPLE;
    LOOP
        FETCH CUR_SAMPLE INTO REC_SAMPLE;
        EXIT WHEN CUR_SAMPLE%NOTFOUND;

        -- 必要な箇所のコメントを外して使ってください

        -- サンプル・INSERT
        -- INSERT INTO TB_SAMPLE2 (ID, NAME) VALUES (REC_SAMPLE.ID, REC_SAMPLE.NAME);

        -- サンプル・UPDATE
        -- UPDATE TB_SAMPLE2 SET NAME = REC_SAMPLE.NAME WHERE ID = REC_SAMPLE.ID;

        -- サンプル・DELETE
        -- DELETE FROM TB_SAMPLE2 WHERE ID = REC_SAMPLE.ID;

        -- サンプル・MERGE
        -- MERGE INTO TB_SAMPLE2 T1
        -- USING
        --     ( SELECT REC_SAMPLE.ID AS ID, REC_SAMPLE.NAME AS NAME FROM DUAL) T2
        -- ON (T1.ID = T2.ID)
        -- WHEN NOT MATCHED THEN
        --     INSERT (ID, NAME) VALUES (T2.ID, T2.NAME)
        -- ;

    END LOOP;
    CLOSE CUR_SAMPLE;
----------------------------------------
-- 例外部
----------------------------------------
EXCEPTION
    WHEN OTHERS THEN
        -- 例外情報を出力
        DBMS_OUTPUT.PUT_LINE('エラーコード:'|| SQLCODE || ' ' || 'エラーメッセージ:' || SQLERRM(SQLCODE));
        CLOSE CUR_SAMPLE;
END;
/
  • このエントリーをはてなブックマークに追加

コメントを残す

*