コントロール構造 - PolarDB - Alibaba Cloud ドキュメントセンター

このトピックでは、制御構造について説明します。

背景情報

制御構造はおそらくPL/SQLの最も有用で重要な部分です。 PL/SQLの制御構造を使用することで、PolarDB for PostgreSQL (Oracle互換) クラスターのデータを柔軟かつ効果的に操作できます。

Return from a function

RETURNおよびRETURN NEXTコマンドを使用すると、関数からデータを返すことができます。

リターン

RETURN表現;

RETURNの後に式が続くと、関数が終了し、式の値が呼び出し元に返されます。このフォームは、セットを返さないPL/SQL関数に使用されます。

関数がスカラー型を返す場合、式の結果は関数の戻り値型に自動的に変換されます。ただし、コンポジット (行) 値を返すには、必要な列のセットを正確に生成する式を記述する必要があります。これは、明示的キャスティングの使用を必要とし得る。

出力パラメーターを持つ関数を宣言する場合は、式なしでRETURNと記述します。出力パラメーター変数の現在の値が返されます。

voidを返すように関数を宣言した場合、事前にreturnステートメントを使用して関数を終了できます。この場合、RETURNの後に式を記述しないでください。

関数の戻り値を未定義にすることはできません。コントロールがRETURNステートメントに遭遇せずに関数の最上位ブロックに到達すると、実行時エラーが発生します。ただし、この制限は、出力パラメーターを持つ関数やvoidを返す関数には適用されません。このような場合、最上位ブロックが終了すると、RETURNステートメントが自動的に実行されます。

例：

-- スカラー型を返す関数
    リターン1 + 2;
    RETURN scalar_var;

    -- 複合型を返す関数
    RETURN composite_type_var;
    RETURN (1,2, 'three'::text); -- 列は正しい型にキャストする必要があります。

RETURN NEXTおよびRETURN QUERY

RETURN NEXT式;
    RETURN QUERYクエリ;
    RETURN QUERY EXECUTEコマンド文字列 [USING expression [, ... ] ];

PL/SQL関数がSETOF "sometypeを返すと宣言された場合、従う手順はわずかに異なります。この場合、返される個々の項目は、RETURN NEXTまたはRETURN QUERYコマンドのシーケンスを使用して指定され、引数のない最後のRETURNコマンドを使用して、関数が実行されたことを示します。 RETURN NEXTは、スカラーおよび複合データ型に使用できます。複合型の場合、結果の完全な「テーブル」が返されます。 RETURN QUERYは、クエリの実行結果を関数の結果セットに追加します。セットを返す単一の関数では、RETURN NEXTとRETURN QUERYを任意に混合し、それらの結果を連結することができます。

RETURN NEXTとRETURN QUERYは実際には関数から返されません。関数の結果セットに0行以上を追加するだけです。次に、システムは、PL/SQL関数内の次のステートメントを実行し続ける。後続のRETURN NEXTおよびRETURN QUERYコマンドが実行されると、結果セットが構築される。最後のRETURNは引数を持たないはずですが、コントロールは関数を終了します。コントロールを関数の最後に到達させることもできます。

RETURN QUERYには、実行するクエリを動的に指定するバリアントRETURN QUERY EXECUTEがあります。パラメーター式は、EXECUTEコマンドと同じ方法で、usingを使用して計算クエリ文字列に挿入できます。

出力パラメーターを持つ関数を宣言する場合は、式なしでRETURN NEXTと記述します。関数が実行されるたびに、出力パラメーター変数の現在の値が返される結果の行として保存されます。セットを返す出力パラメーターを持つ関数を作成するには、複数の出力パラメーターが返された場合はSETOFレコードを返すように関数を宣言するか、sometype型の出力パラメーターが1つだけ返された場合はSETOF ''sometypeを返すように関数を宣言する必要があります。

RETURN NEXTを使用する関数の例を次に示します。

CREATE TABLE foo (fooid INT、foosubid INT、fooname TEXT);
    foo値に挿入 (1、2、「3」);
    foo値に挿入する (4、5、'six ');

    CREATE OR REPLACE FUNCTION get_all_foo() RETURN SETOF foo IS
    DECLARE
        r foo % rowtype;
    BEGIN
        r INのため
            SELECT * fooからfooid > 0
        LOOP
            -ここでいくつかの処理を行うことができます。
            RETURN NEXT r; -- SELECTステートメントの現在の行を返します。
        END LOOP;
        RETURN;
    END; 

    SELECT * FROM get_all_foo();

次の例は、RETURN QUERYを使用する関数を示しています。

CREATE FUNCTION get_available_flightid(date) RETURN SETOF整数IS
    BEGIN
        戻るクエリ選択flightid
                       フライトから
                      WHERE flightdate >= $1
                        そしてフライトデート < ($1 + 1);

        -- 関数が実行されているときに、行が返されるかどうかを確認できます。
        -- 行が返されない場合は、例外をスローします。 
        見つからなかった場合
            昇格例外「 % でフライトなし」、1ドル。
        END IF;

        RETURN;
     END; 

     -利用可能なフライトを返すか、利用可能なフライトがない場合は例外を投げます。 
     SELECT * FROM get_available_flightid(CURRENT_DATE);

説明

上記のように、現在のRETURN NEXTおよびRETURN QUERY実装は、関数から戻る前に結果セット全体を保存します。つまり、PL/SQL関数が非常に大きな結果セットを生成する場合、パフォーマンスが低下する可能性があります。メモリの枯渇を防ぐためにデータがディスクに書き込まれますが、結果セット全体が生成されるまで関数自体は終了しません。 PL/SQLの将来のバージョンでは、ユーザーはこの制限なしでセットを返す関数を定義できます。現在、データのディスクへの書き込みが開始される時点は、設定変数work_memによって決まります。大きな結果セットを格納するのに十分なメモリがある管理者は、このパラメーターの値を増やすことを検討します。

Return from a procedure

プロシージャは値を返しません。したがって、プロシージャはRETURNステートメントなしで終了できます。 RETURNステートメントを使用して事前にコードを終了する場合は、式なしでRETURNと記述します。

プロシージャに出力パラメータがある場合、出力パラメータの最終値が呼び出し元に返されます。

プロシージャの呼び出し

PL/SQL関数、プロシージャ、またはDOブロックは、callを使用してプロシージャを呼び出すことができます。出力パラメーターは、CALLがプレーンSQLで機能する方法とは異なる方法で処理されます。プロシージャの各INOUTパラメーターは、CALLステートメントの変数に対応している必要があり、プロシージャが返すものはすべて、返された後に変数に割り当てられます。例：

CREATE PROCEDUREトリプル (INOUT x int)
    IS
    BEGIN
        x := x * 3;
    END;

    DECLARE myvar int := 5;
    BEGIN
      CALLトリプル (myvar);
      RAISE NOTICE 'myvar = % '、myvar; -プリント15
    エンド;

条件

IFおよびCASEステートメントを使用すると、特定の条件に基づいて代替コマンドを実行できます。 PL/SQLには3つの形式のIFがあります。

IF... その後... エンドIF
IF... その後... ELSE... エンドIF
IF... その後... ELSIF... その後... ELSE... エンドIF

PL/SQLには2つの形式のCASEがあります。

ケース... WHEN ... その後... ELSE... エンドケース
の場合... その後... ELSE... エンドケース

IF-THEN

IFブール式THEN
        statements
    エンドIF;

IF-THENステートメントは、IFの最も単純な形式です。条件がtrueの場合、THENとEND Ifの間のステートメントが実行されます。それ以外の場合、ステートメントはスキップされます。

例：

IF v_user_id <> 0 THEN
    UPDATEユーザーSET email = v_email WHERE user_id = v_user_id;
    エンドIF;

IF-THEN-ELSE

IFブール式THEN
        statements
    ELSE
        statements
    エンドIF;

IF-THEN-ELSEはIF-THENの拡張です。 IF-THEN-ELSEでは、条件がtrueでない場合に実行するステートメントのセットを指定できます。これは、条件がNULLに評価される場合も含む。

例：

IF parentid IS NULL OR parentid = ''
    THEN
        戻るfullname;
    ELSE
        RETURN hp_true_filename(parentid) | | '/' | | fullname;
    END IF;

IF v_count > 0 THEN
    INSERT INTO users_count (count) VALUES (v_count);
    リターン 't';
    ELSE
        リターン 'f';
    エンドIF;

IF-THEN-ELSIF

IFブール式THEN
        statements
    [ELSIFブール式THEN]
        statements
    [ELSIFブール式THEN]
        statements
        ...
    ]
    ]
    [ELSE
        ステートメント]
    エンドIF;

場合によっては、3つ以上の条件が必要である。 IF-THEN-ELSIFステートメントは、複数の条件を簡単に確認できます。 IF条件は、条件が真であることが分かるまで順番にチェックされる。次いで、関連するステートメントが実行される。その後、制御は、後続のIF条件をチェックせずに、END IFの後の次のステートメントに移る。いずれのIf条件も真でない場合、ELSEブロックが実行される。

例：

IF番号=0 THEN
        結果:= 'zero';
    ELSIF番号> 0 THEN
        結果:= 'positive';
    ELSIF番号 <0 THEN
        result := 'negative ';
    ELSE
        -他の唯一の可能性は、番号がnullであることです。
        結果:= 'NULL';
    エンドIF;

ELSIFキーワードは、ELSEIFと綴ることもできます。

同じ効果を生み出す別の方法は、IF-THEN-ELSEステートメントをネストすることです。例：

IF demo_row.sex = 'm' THEN
        pretty_sex := 'man';
    ELSE
        IF demo_row.sex = 'f' THEN
            pretty_sex := 'woman';
        END IF;
    エンドIF;

しかし、この方法では、IFごとに一致するEND IFを書き込む必要がある。複数の条件が必要な場合、この方法はELSIFほど効率的ではありません。

シンプルなケース

CASE検索式
        WHEN式 [, 式 [ ... ]] その後
          statements
      [WHEN式 [, 式 [ ... ]]]
          statements
        ... ]
      [ELSE
          ステートメント]
    エンドケース;

CASEの単純な形式は、オペランド等価性に基づく条件付き実行を提供する。 search-expressionは1回評価され、WHEN句の各expressionと順番に比較されます。一致が見つかった場合、対応するステートメントが実行され、制御は、後続のWHEN式を評価せずに、END CASEの後の次のステートメントに移る。一致するものが見つからない場合、ELSEステートメントが実行されます。ただし、ELSEが存在しない場合は、CASE_NOT_FOUND例外がスローされます。

例：

ケースx
        1、2があったとき
            msg := 'one or two';
        ELSE
            msg := '1つまたは2つ以外の値';
    エンドケース;

検索されたケース

ケース
        ブール式が存在するとき
          statements
      [ブール式があれば
          statements
        ... ]
      [ELSE
          ステートメント]
    エンドケース;

CASEの検索形式は、ブール式に基づいて条件付き実行を実行するのに役立ちます。各WHEN句のブール式は、1つの句がtrueになるまで評価されます。次に、対応するステートメントが実行され、制御は、後続のWHEN式を評価せずに、END CASEの後の次のステートメントに渡される。真のブール式が見つからない場合、ELSEステートメントが実行されます。ただし、ELSEが存在しない場合は、CASE_NOT_FOUND例外がスローされます。

例：

ケース
        xが0と10の間のとき
            msg := 'valueはゼロとテンの間です。
        xが11と20の間にあるとき
            msg := 'valueは11から20の間です ';
    エンドケース;

この形式のCASEは全体としてIF-THEN-ELSIFと同等ですが、CASEは何もしない代わりに省略されたELSE句に達した場合に例外をスローします。

シンプルなループ

LOOP、EXIT、CONTINUE、WHILE、FOR、FOREACHステートメントを使用して、一連のコマンドを繰り返すようにPL/SQLを配置できます。

ループ

[ <<ラベル>> ]
    LOOP
        statements
    END LOOP [ラベル];

LOOPは、EXITまたはRETURNステートメントで終了するまで無期限に繰り返す無条件ループを定義します。オプションのラベルは、ネストされたループのEXITステートメントとCONTINUEステートメントで使用して、これらのステートメントが参照するループのレベルを指定できます。

EXIT

EXIT [ラベル] [ WHEN boolean-expression ];

labelが指定されていない場合、最も内側のループは終了し、END loopの後のステートメントが実行されます。 ラベルが指定されている場合、それは、ネストされたループまたはステートメントブロックの現在のレベルまたは外側のレベルのラベルでなければなりません。次いで、名前付きループまたはブロックは終了し、制御は、ループまたはブロックの対応するENDの後のステートメントに移る。

WHENが指定されている場合、boolean-expressionがtrueの場合にのみループが終了します。それ以外の場合、コントロールはEXITの後のステートメントに渡されます。

EXITは、無条件ループを含むすべてのタイプのループで使用できます。

BEGINブロックと一緒に使用される場合、EXITはブロック終了後の次のステートメントを制御します。ラベルを使用する必要があることに注意してください。ラベルのないEXITは、PostgreSQLの最近のバージョンではBEGINブロックと一致すると見なすことはできません。 PostgreSQL 8.4以前のみ、ラベルなしのEXITをBEGINブロックと一致させることができます。

例：

ループ
        -いくつかの計算
        IFカウント> 0 THEN
            EXIT; -- ループを終了します。
        END IF;
    END LOOP;

    LOOP
        -いくつかの計算
        EXIT WHEN count > 0; -- 前の例と同じ結果
    END LOOP;

    <<ablock>>
    BEGIN
        -いくつかの計算
        IF株> 100000 THEN
            EXIT ablock; -BEGINブロックからの終了を引き起こします
        END IF;
        -- stocks > 100000の場合、ここでの計算はスキップされます。
    エンド;

継続

CONTINUE [ label ] [ WHEN boolean-expression ];

ラベルが指定されていない場合、最も内側のループの次の反復が開始されます。これは、ループ本体内の残りのすべてのステートメントがスキップされ、制御がループ制御式に戻り、別のループ反復が必要かどうかを判断することを意味します。 labelが指定されている場合は、実行を続行するループのラベルを指定します。

WHENが指定されている場合、ループの次の反復はboolean式がtrueの場合にのみ開始されます。それ以外の場合、制御はCONTINUEに続くステートメントに渡されます。

CONTINUEは、無条件ループを含むすべてのタイプのループで使用できます。

例：

ループ
        -いくつかの計算
        出口カウント> 100;
        カウント <50のときに継続する。
        -カウントINのためのいくつかの計算 [50 .. 100]
    エンドループ;

WILE

[ <<ラベル>> ]
    ブール式のループ
        statements
    END LOOP [ラベル];

WHILEステートメントは、boolean-expressionがtrueと評価される限り、一連のステートメントを繰り返します。式は、ループ本体が入力される前に毎回チェックされます。

例：

量なし> 0 AND gift_certificate_balance > 0ループ
        -いくつかの計算
    END LOOP;

    ループが行われていない間
        -いくつかの計算
    エンドループ;

FOR (整数バリアント)

[ <<ラベル>> ]
    名前のために [逆] 式 .. 式 [BY expression ] LOOP
        statements
    END LOOP [ラベル];

この形式のFORは、整数の範囲を反復するループを作成します。変数名は整数型として自動的に定義され、ループ内にのみ存在します。その変数名の既存の定義は、ループ内で無視されます。範囲の上限と下限を与える2つの式は、ループに入ると1回評価されます。 BY句を指定しない場合、反復ステップは1です。それ以外の場合、ステップ値はBY句で指定された値であり、これもループに入るときに1回だけ評価されます。 REVERSEが指定されている場合、ステップ値は各反復後にインクリメントされるのではなく減算されます。

例：

FOR i IN 1 .. 10ループ
        -- 値1、2、3、4、5、6、7、8、9、および10がループに取り込まれます。
    END LOOP;

    逆のiのために10 .. 1ループ
        -- 値10、9、8、7、6、5、4、3、2、および1がループに取り込まれます。
    END LOOP;

    逆のiのために10 .. 1によって2ループ
        -- 値10、8、6、4、および2がループで取得されます。
    エンドループ;

下限が上限より大きい (またはREVERSEの場合より小さい) 場合、ループ本体は実行されません。エラーはスローされません。

ラベルがFORループにアタッチされている場合、整数ループ変数は、そのラベルを使用する修飾名で参照できます。

クエリ結果のループ

異なるタイプのFORループを使用して、クエリの結果を反復処理し、対応するデータを操作できます。構文：

[ <<ラベル>> ]
    FOR target INクエリLOOP
        statements
    END LOOP [ラベル];

targetは、レコード変数、行変数、またはコンマ (,) で区切られたスカラー変数のリストです。 targetはクエリ結果から各行に順次割り当てられ、ループ本体は各行ごとに1回実行されます。例：

CREATE FUNCTION refresh_mviews() RETURN整数IS
    DECLARE
        mviewsレコード;
    BEGIN
        通知を上げる「すべての具体化されたビューをリフレッシュ... 」;

        のためのmviews IN
        SELECT n.nspname AS mv_schema,
                  c.relname AS mv_name,
                  pg_catalog.pg_get_userbyid(c.relowner) 所有者として
             pg_catalog.pg_class cから
        pg_catalog.pg_namespace nに参加します (n.oid = c.relnamespace)
            WHERE c.relkind = 'm'
         1による注文
         LOOP

            -現在、「mviews」には、具体化されたビューに関する情報を含む1つのレコードがあります

            RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...'、
                         quote_ident(mviews.mv_schema) 、
                         quote_ident(mviews.mv_name) 、
                         quote_ident(mviews.owner);
            EXECUTE形式 ('REFRESH MATERIALIZED VIEW % I.% I '、mviews.mv_schema、mviews.mv_name);
        END LOOP;

        RAISE NOTICE「具体化されたビューをリフレッシュしました。」;
        リターン1;
     エンド;

ループがEXITステートメントで終了した場合でも、ループの後に最後に割り当てられた行値にアクセスできます。

このタイプのFORステートメントで使用されるクエリは、呼び出し元に行を返す任意のSQLコマンドです。 SELECTコマンドが最も一般的に使用されます。 RETURNING句を含むINSERT、UPDATE、またはDELETEコマンドを使用することもできます。 EXPLAINなどの一部のユーティリティコマンドもここで使用できます。

PL/SQL変数はクエリパラメータに置き換えられ、クエリプランは再利用可能なようにキャッシュされます。

FOR-IN-EXECUTEステートメントは、行を反復処理するもう1つの方法です。

[ <<ラベル>> ]
    ターゲットのEXECUTE text_expression [ USING expression [, ... ] ] ループ
        statements
    END LOOP [ラベル];

このフォームは前のフォームと似ていますが、ソースクエリが文字列式として指定され、FORループに入るたびに評価および再計画されます。これにより、プログラマは、単純なEXECUTEステートメントの場合と同様に、事前に計画されたコマンドの速度と動的コマンドの柔軟性のどちらかを選択できます。 EXECUTEを使用する場合、usingを使用してパラメーター値を動的コマンドに挿入できます。

結果を反復処理するクエリを指定する別の方法は、クエリをカーソルとして宣言することです。

ループスルー配列

FOREACHループはFORループによく似ていますが、SQLクエリによって返された行を反復処理する代わりに、値の配列の要素を反復処理します。 FOREACHは、複合値式のコンポーネントを反復処理します。配列以外の複合型をループするためのバリアントが将来追加される可能性があります。構文：

[ <<ラベル>> ]
    FOREACH target [スライス番号] の配列式LOOP
        statements
    END LOOP [ラベル];

SLICEがない場合、またはSLICE 0が指定されている場合、ループは式を評価することによって生成された配列の個々の要素を反復処理します。 ターゲット変数には各要素の値が順番に割り当てられ、要素ごとにループ本体が実行されます。次の例は、整数の配列の要素をループすることを示しています。

CREATE FUNCTION sum(int[]) RETURN int8 IS
    DECLARE
      s int8 := 0;
      x int;
    BEGIN
      FOREACH x IN ARRAY $1
      LOOP
        s := s + x;
      END LOOP;
      リターンs;
    エンド;

要素は、配列の次元数に関係なく、格納されている順序でアクセスされます。通常、targetは単一の変数にすぎませんが、複合値 (レコード) の配列をループするときは変数のリストにすることができます。その場合、各配列要素に対して、複合値の連続する列から変数が割り当てられる。

SLICEが正の数の場合、FOREACHは単一の要素ではなく、配列のスライスを繰り返します。 SLICE値は、配列次元の数以下の整数定数である必要があります。 ターゲット変数は配列である必要があり、配列値の連続スライスを受け取ります。各スライスは、sliceによって指定される次元の数を有する。次の例は、1次元スライスの反復処理を示しています。

CREATE FUNCTION scan_rows(int[]) RETURN void IS
    DECLARE
      x int[];
    BEGIN
      ARRAY $1のFOREACH xスライス1
      LOOP
        RAISE NOTICE 'row = % ', x;
      END LOOP;
    END;  

    SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);

    注意: row = {1,2,3}
    注意: row = {4,5,6}
    注意: row = {7,8,9}
    注意: row = {10,11,12}

トラップエラー

デフォルトでは、PL/SQL関数で発生したエラーは、関数とその周辺のトランザクションの実行を中止します。 EXCEPTION句を含むBEGINブロックを使用すると、エラーをトラップして回復できます。構文は、BEGINブロックの通常の構文の拡張です。

[ <<ラベル>> ]
    [ DECLARE
        declarations ]
    BEGIN
        statements
    EXCEPTION
        WHEN condition [ OR condition ... ] その後
            handler_statements
        [WHEN condition [ OR condition ... ]
              handler_statements
          ... ]
    エンド;

エラーが発生しない場合、この形式のブロックは単にすべてのステートメントを実行し、制御はENDの後の次のステートメントに渡されます。ただし、ステートメント内でエラーが発生した場合、ステートメントの処理はキャンセルされ、制御はEXCEPTIONリストに渡されます。システムは、発生したエラーと一致するリストの最初の条件を検索します。一致が見つかった場合、対応するhandler_statementsが実行され、ENDの後の次のステートメントに制御が渡されます。一致しない場合、EXCEPTION句が存在しないかのようにエラーが伝播します。エラーは、EXCEPTIONを含む囲みブロックによってキャッチできます。そのような囲みブロックが存在しない場合、関数の処理は中止される。

条件名は、PostgreSQLエラーコードに対応する任意の名前にすることができます。カテゴリ名は、その中のすべてのエラーと一致します。特殊条件名OTHERSは、QUERY_CANCELEDとASSERT_FAILUREを除くすべてのエラータイプと一致します。これらの2つのエラータイプは、名前でトラップできます。ただし、そうすることはお勧めしません。条件名は大文字と小文字を区別しません。エラー条件は、SQLSTATEコードで指定することもできます。たとえば、次の句は同等です。

division_by_zeroがあったとき...
    SQLSTATE '22012' がその後...

選択したhandler_statements内で新しいエラーが発生した場合、このEXCEPTION句では検出できませんが、伝播されます。外側のEXCEPTION句はそれをキャプチャできます。

EXCEPTION句によってエラーが検出された場合、PL/SQL関数のローカル変数はエラー発生時の値を保持しますが、ブロック内の永続的なデータベース状態に対するすべての変更はロールバックされます。例：

INSERT INTO mytab(firstname、lastname) VALUES('Tom' 、'Jones ');
    BEGIN
        UPDATE mytab SET firstname = 'Joe' lastname = 'Jones ';
        x := x + 1;
        y := x / 0;
    EXCEPTION
        division_by_zeroがあったとき
            RAISE NOTICE 'cagged division_by_zero ';
            戻るx;
    エンド;

コントロールがyが割り当てられているステートメントに達すると、division_by_zeroエラーで失敗します。このエラーは、EXCEPTION句によってキャッチされます。 RETURNステートメントで返される値は、インクリメントされたxの値になります。ただし、UPDATEコマンドはロールバックされます。ただし、ブロックの前のINSERTコマンドはロールバックされないため、最終結果として、データベースにはTom Jonesが含まれていますが、Joe Jonesは含まれていません。

EXCEPTION句を含むブロックの入出力は、EXCEPTIONを含まないブロックよりもはるかに高価です。したがって、必要な場合にのみ例外を使用してください。

UPDATEおよびINSERTの例外。

この例では、例外処理を使用して、必要に応じてUPDATEまたはINSERTを実行します。アプリケーションは、実際にこのパターンを使用する代わりに、ON CONFLICT DO UPDATEを含むINSERTを使用することを推奨します。次の例は、PL/SQLがフローを制御する方法を示しています。

CREATE TABLE db (INT PRIMARYキー、bテキスト);

    CREATE FUNCTION merge_db (キーINT、データテキスト) RETURN VOID IS
    BEGIN
        LOOP
            -まず、キーを更新してみてください。
            UPDATE db SET b=データWHERE a=キー;
            見つかった場合は
                RETURN;
            END IF;
            -- キーが存在しません。 次に、キーを挿入してみてください。
            -他の誰かが同じキーを同時に挿入した場合、 
            -独自のキー障害が発生する可能性があります。
            BEGIN
                INSERT INTO db(a、b) VALUES (キー、データ);
                RETURN;
            unique_violationがあったときの例外
                -何もせずにループしてUPDATEをもう一度試します。
            END;
        END LOOP;
    END; 

    SELECT merge_db(1, 'david');
    SELECT merge_db(1, 'dennis');

このコードは、unique_violationエラーが、テーブルのトリガー関数のINSERTではなくINSERTによって発生していることを前提としています。テーブルに複数の一意のインデックスがある場合にもエラーが発生する可能性があります。これは、エラーの原因となったインデックスに関係なく、システムが操作を再試行するためです。次の機能を使用して、キャプチャされたエラーが予想されるかどうかを確認する方が安全です。

エラーに関する情報の取得

例外ハンドラーは、発生した特定のエラーを識別するためによく使用されます。特別な変数またはget STACKED DIAGNOSTICSコマンドを使用して、PL/SQLの現在の例外に関する情報を取得できます。

例外ハンドラー内の特殊変数SQLSTATEには、スローされた例外に対応するエラーコードが含まれます。特殊な変数SQLERRMには、例外に関連するエラーメッセージが含まれます。これらの変数は、例外ハンドラの外部では未定義です。

例外ハンドラー内では、次の形式のGET STACKED DIAGNOSTICSコマンドを使用して、現在の例外に関する情報を取得することもできます。

スタック診断変数 { = | := } item [ , ... ];

各項目は、値を受け取る正しいデータ型の指定された変数に割り当てられているステータス値を識別するキーワードです。現在使用可能なステータス項目を次の表に示します。

エラー診断項目

パラメーター名	種類	説明
`リターン_SQLSTATE`	`テキスト`	例外のSQLSTATEエラーコード。
`コラム_名前`	`テキスト`	例外に関連する列の名前。
`CONSTRAINT_NAME`	`テキスト`	例外に関連する制約の名前。
`PG_DATATYPE_NAME`	`テキスト`	例外に関連するデータ型の名前。
`メッセージ_テキスト`	`テキスト`	例外のプライマリメッセージのテキスト。
`TABLE_NAME`	`テキスト`	例外に関連するテーブルの名前。
`スキーマ_名前`	`テキスト`	例外に関連するスキーマの名前。
`PG_EXCEPTION_詳細`	`テキスト`	もしあれば、例外の詳細なメッセージのテキスト。
`PG_EXCEPTION_ヒント`	`テキスト`	例外のヒントメッセージ (ある場合) のテキスト。
`PG_EXCEPTION_コンテキスト`	`テキスト`	例外発生時の呼び出し履歴を記述するテキストの行。

診断項目に値が指定されていない場合は、空の文字列が返されます。

例：

DECLARE
      text_var1テキスト;
      text_var2テキスト;
      text_var3テキスト;
    BEGIN
      -- 例外を引き起こす可能性のある処理
      ...
    他の人がそこにいるときの例外
      スタック診断text_var1 = MESSAGE_TEXTを取得し、
                              text_var2 = PG_EXCEPTION_DETAIL、
                              text_var3 = PG_EXCEPTION_HINT;
    エンド;

実行場所情報の取得

GET DIAGNOSTICSコマンドは、現在の実行状態に関する情報を取得するのに役立ちます。上記のGET STACKED DIAGNOSTICSコマンドは、実行状態に関する情報を以前のエラーとして報告するのに役立ちます。 PG_CONTEXTステータス項目は、現在の実行場所を識別するために使用できます。 PG_CONTEXTステータス項目は、コールスタックを記述する複数行のテキストを含むテキスト文字列を返します。 1行目は、現在の関数と、現在実行中のGET DIAGNOSTICSコマンドを示しています。 2行目以降は、コールスタックの上位の呼び出し関数を記述している。例：

CREATE OR REPLACE FUNCTION outer_func() RETURN整数IS
    BEGIN
      RETURN inner_func();
    END;
    

    CREATE OR REPLACE FUNCTION inner_func() RETURN整数IS
    DECLARE
      スタックテキスト;
    BEGIN
      GET DIAGNOSTICSスタック=PG_CONTEXT;
      RAISE NOTICE E' --- 通話スタック ---\n % '、スタック;
      リターン1;
    END;
    

    SELECT outer_func();

    通知: --- コールスタック ---
    PL/SQL関数inner_func() 5行目GET DIAGNOSTICS
    PL/SQL関数outer_func() 行3 (RETURN)
    コンテキスト: PL/SQL関数outer_func() 行3 (RETURN)
     outer_func
     ------------
               1
    (1行)

スタック診断を取得... PG_EXCEPTION_CONTEXTは同じ種類のスタックトレースを返しますが、現在の場所ではなく、エラーが検出された場所を記述します。