基本ステートメント - PolarDB - Alibaba Cloud ドキュメントセンター

このトピックでは、基本的なPL/SQLステートメントについて説明します。

値の割り当て

次の構文を使用して、PL/SQL変数に値を割り当てることができます。

変数 { := | = } 式;

このステートメントの式は、SQL SELECTステートメントがプライマリデータベースエンジンに送信されるように評価されます。この式の結果値は単一の値でなければなりません。変数が行またはレコード変数である場合、結果値は行値であり得る。値を割り当てる変数は、単純変数、行またはレコード変数のフィールド、または単純変数またはフィールドの配列要素にすることができます。変数が単純な変数の場合は、ブロック名で修飾できます。 PL/SQLと互換性のある := の代わりに等号 (=) を使用できます。

式の結果データ型が変数の結果データ型と一致しない場合、前者は強制的に後者に変換されます。これは代入キャストと同様です。関連するデータ型のペアに対して割り当てキャストが使用できない場合、PL/SQLインタプリタは結果値を文字列に変換しようとします。これは、変数型の入力関数を適用する前に、結果のデータ型の出力関数を適用することを意味します。文字列形式の結果値が入力関数によって受け入れられない場合、実行時エラーが入力関数によって返される可能性があります。

例：

税:=小計 * 0.06;
    my_record.user_id := 20;

結果なしでステートメントを実行する

RETURNING句のないINSERT文など、行を返さないSQL文の場合は、PL/SQL関数に書き込んだ後に実行できます。

この場合、ステートメントテキストに表示されるPL/SQL変数名はすべてパラメーターと見なされ、ステートメントの実行時に変数の現在の値がパラメーターの値として提供されます。これは、式の処理とまったく同じです。

SQL文がPL/SQL関数で直接実行された場合、PL/SQLはその文の実行計画をキャッシュして再使用します。

式またはSELECTクエリを評価し、結果を破棄すると便利な場合があります。たとえば、この方法では、副作用はあるが有用な結果値がない関数を呼び出すことができます。 PL/SQLでこれを行うには、PERFORMステートメントを使用します。

PERFORMクエリ;

クエリは実行されますが、結果は破棄されます。 SQL SELECTステートメントと同じ方法でクエリを記述し、最初のキーワードSELECTをPERFORMに置き換えます。WITHクエリを実行する場合は、PERFORMを使用してクエリを括弧で囲みます。この場合、1行のデータのみが返されます。 PL/SQL変数がクエリに代入され、結果を返さないステートメントについて前述したように、PL/SQLはステートメントの実行プランをキャッシュします。特殊変数FOUNDは、ステートメントが少なくとも1行のデータを返す場合はtrueに設定され、ステートメントがデータを返さない場合はfalseに設定されます。

説明

ステートメントでSELECTを直接使用すると、結果が得られます。ただし、ステートメントでPERFORMを使用することだけが受け入れられます。 SELECTステートメントなどの結果を返すSQLステートメントの場合、次のセクションで説明するように、INTO句がない限りエラーとして拒否されます。

例：

PERFORM create_mv('cs_session_page_requests_mv ', my_query);

単一行のデータを返すクエリの実行

複数の列を持つ可能性のある単一の行を返すSQLステートメントの結果は、レコード変数、行変数、またはスカラー変数のリストに割り当てることができます。この効果は、基本的なSQL文を記述し、INTO句を追加することによって実現されます。例：

SELECT select_expression INTO [STRICT] ターゲットFROM ...;
    挿入... 式を [STRICT] ターゲットに戻す。
    更新... 式を [STRICT] ターゲットに戻す。
    削除... 式を [STRICT] ターゲットに戻す。

targetには、レコード変数、行変数、またはコンマ (,) で区切られた単純な変数とレコードまたは行フィールドのリストを指定できます。行を返さないステートメントについて前述したように、PL/SQL変数が残りのクエリに代入され、実行プランがキャッシュされます。この場合、SELECTステートメント、INSERT、UPDATE、DELETEステートメントをRETURNING句で実行できます。また、EXPLAINステートメントなどの行セットを返すその他のステートメントも実行できます。 INTO句を除いて、PL/SQL文は通常のSQL文と同じ方法で記述されます。

説明

INTO句を使用したSELECTステートメントのこの解釈は、通常のSELECT INTOステートメントとは異なります。通常のステートメントでは、INTO句の宛先は新しいテーブルです。 PL/SQL関数のSELECTステートメントの結果からテーブルを作成する場合は、create table... を使用します。 SELECTとして。

結果値を行または変数のリストに割り当てる場合、クエリの結果列は、行または変数の構造 (数値やデータ型など) と正確に一致する必要があります。それ以外の場合は、実行時エラーが発生します。結果値をレコード変数に割り当てる場合、レコード変数は自動的にクエリ結果列の行タイプに適応します。

INTO句は、SQL文のほぼどこにでも表示できます。ほとんどの場合、INTO句は、selectステートメントのSELECT_expressionのリストの前後、または他の型のステートメントの末尾に配置されます。将来のバージョンでPL/SQLインタプリタが厳しくなる場合に備えて、この規則に従うことをお勧めします。

INTO句でSTRICTが指定されていない場合、targetはクエリによって返される最初の行に設定され、クエリが行を返さない場合はnullに設定されます。「最初の行」の定義は、ORDER BYが使用されない限り明確ではありません。最初の行以降のすべての結果行は破棄されます。特殊な変数FOUNDをチェックして、単一行のデータが返されるかどうかを判断できます。

SELECT * INTO myrec FROM emp WHERE empname = myname;
    見つからなかった場合
        RAISE EXCEPTION「従業員 % が見つかりません」、myname;
    エンドIF;

STRICTを指定した場合、クエリは1行だけ返す必要があります。それ以外の場合は、実行時エラーが発生します。エラーは、NO_DATA_FOUNDまたはTOO_MANY_ROWSである可能性があります。エラーをキャッチする場合は、例外ブロックを使用できます。例：

BEGIN
    SELECT * STRICT myrecからempname = myname;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                RAISE EXCEPTION「従業員 % が見つかりません」、myname;
            WHEN TOO_MANY_ROWS THEN
                RAISE EXCEPTION「従業員 % はユニークではありません」、myname;
    エンド;

STRICTのステートメントが成功した場合、FOUNDはtrueに設定されます。

RETURNING句を持つINSERT、UPDATE、およびDELETE文の場合、STRICTが指定されていなくても複数行のデータが返されると、PL/SQLはエラーを報告します。これは、影響を受けた行を返す必要があるかを判断するために、ORDER BYなどのオプションが使用できないためです。

関数に対してprint_strict_paramsパラメーターが有効になっていて、STRICTの条件が満たされていないためにエラーが報告された場合、エラーメッセージのDETAILセクションには、クエリに渡されるパラメーターが含まれます。 plpgsql.print_strict_paramsパラメーターを変更することで、すべての関数にprint_strict_paramsパラメーター設定を指定できます。ただし、これは変更後にコンパイルされた関数にのみ有効です。コンパイラオプションを使用して、関数のprint_strict_paramsパラメーターを有効にすることもできます。例：

CREATE FUNCTION get_userid (ユーザー名テキスト) RETURN int
    IS
    # print_strict_params on
    DECLARE
    userid int;
    BEGIN
        SELEC T users.us erid INTO STRICT userid
            ユーザーからWHER E users.us ername = get_userid.username;
        戻るuserid;
    エンド;

ステートメントの実行に失敗した場合、次のようなエラーメッセージが返されます。

ERROR: クエリは行を返さなかった
    詳細: パラメーター: $1 = 'nosuchuser'
    CONTEXT: SQL文

のPL/SQL関数get_userid(text) 6行目

説明

STRICTオプションは、SELECT INTOおよび関連するOracle PL/SQLステートメントの動作と一致します。

動的ステートメントの実行

多くのシナリオでは、PL/SQL関数で動的ステートメントを実行することができます。動的ステートメントには、実行ごとに異なるテーブルまたはデータ型が含まれます。ほとんどの場合、PL/SQLは動的ステートメントの実行計画をキャッシュできません。この問題を解決するには、EXECUTEステートメントを使用します。例：

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ] ];

command-stringは、実行されるステートメントを含むtext型の文字列を生成する式です。オプションのターゲットは、レコード変数、行変数、またはカンマ (,) で区切られた単純な変数とレコードまたは行フィールドのリストで、ステートメントの結果が格納されます。オプションのUSING式は、ステートメントに挿入される値を提供します。

ステートメント文字列にはPL/SQL変数は代入されません。文字列の生成時に、必要なすべての変数値をステートメント文字列に挿入する必要があります。または、以下で説明するように、パラメーターを使用できます。

さらに、EXECUTEを使用して実行されるステートメントの実行プランはキャッシュされません。代わりに、このステートメントが実行されるたびに実行プランが生成されます。このようにして、さまざまなテーブルや列に対してアクションを実行する関数で、ステートメント文字列を動的に作成できます。

INTO句は、行を返すSQL文の結果値の割り当て先を指定します。値を行または変数のリストに割り当てる場合は、行または変数が結果値の構造と完全に一致する必要があります。値をレコード変数に割り当てる場合、レコード変数は結果値の構造に自動的に適応します。複数の行が返された場合、最初の行のみがINTO変数に割り当てられます。行が返されない場合、NULL値がINTO変数に割り当てられます。 INTO句を指定しない場合、結果値は破棄されます。

STRICTを指定した場合、ステートメントが1行のデータを正確に返さない限り、エラーが報告されます。

ステートメント文字列でパラメーター値を使用でき、パラメーター値は $1や $2などの記号を使用して参照されます。これらの記号は、USING句で提供される値を参照するために使用されます。このメソッドは、文文字列にデータ値をテキストとして挿入する場合に適用できます。これにより、値をテキストに変換して戻す実行時の負荷がなくなり、引用やエスケープが不要になるため、SQLインジェクション攻撃の影響を受けにくくなります。例：

EXECUTE 'SELECT count(*) FROM mytableどこinserted_by = $1 AND inserted <= $2'
       インにc
       使用checked_user、checked_date;

パラメータシンボルはデータ値にのみ使用できます。動的に決定されるテーブル名または列名を使用する場合は、ステートメント文字列にテキストとしてテーブル名または列名を挿入する必要があります。たとえば、動的に選択されたテーブルに対して前のクエリを実行する必要がある場合は、次の構文を使用できます。

EXECUTE 'SELECT count(*) FROM'
        | | quote_ident(tabname)
        | | 'WHERE inserted_by = $1 AND inserted <= $2'
       インにc
       使用checked_user、checked_date;

または、テーブル名または列名にformat() の % I仕様を使用することもできます。このようにして、ラインフィードによって分離されたストリングが連結される。この方法はより簡単です。例：

EXECUTE形式 ('SELECT count(*) FROM % I ')
       'WHERE inserted_by = $1 AND inserted <= $2 '、tabname)
       インにc
       使用checked_user、checked_date;

さらに、パラメーターシンボルは、SELECT、INSERT、UPDATE、およびDELETEステートメントでのみ機能します。ユーティリティステートメントと呼ばれる他のステートメントでは、値がデータ値であっても、ステートメント文字列に値をテキストとして挿入する必要があります。

このセクションで提供される最初の例では、単純な定数ステートメント文字列といくつかのUSINGパラメーターを持つEXECUTEステートメントは、機能的にはPL/SQLで直接記述されたステートメントと同等であり、自動PL/SQL変数置換を可能にします。主な違いは、EXECUTEは現在のパラメーター値に基づいて各実行中にステートメントをリプライしますが、PL/SQLはジェネリックプランを作成し、再利用のためにキャッシュします。最適なプランがパラメーター値に強く依存する場合は、EXECUTEを使用して、汎用プランが選択されないように明示的に確認すると便利です。

EXECUTEはSELECT INTOをサポートしていません。ただし、プレーンなSELECTステートメントを実行し、executeステートメントでINTO句を指定できます。

説明

PL/SQLのEXECUTEステートメントは、データベースサーバーでサポートされているSQL EXECUTEステートメントとは独立しています。データベースサーバーのSQL EXECUTEステートメントは、PL/SQL関数で直接使用することはできません。

多くの場合、動的ステートメントを使用する場合、単一引用符 (') のエスケープに対処する必要があります。固定テキストを引用するには、関数本文でドル記号 ($) を使用することを推奨します。

引用符が含まれている可能性があるため、動的値の処理に注意してください。次のサンプルコードは、format() の使用例を示しています。この例では、関数本文での引用にドル記号 ($) が使用されているため、引用符は単一引用符 (') です。

EXECUTE形式 ('UPDATE tbl SET % I = $1 ')
       'WHERE key = $2 '、colname) 新しい値、キー値を使用します。

引用関数を直接呼び出すこともできます。例：

EXECUTE「UPDATE tbl SET」
            | | quote_ident(colname)
            || ' = '
            | | quote_literal(newvalue)
            | | 'WHERE key = '
            | | quote_literal (キー値);

この例では、quote_identおよびquote_literal関数が使用されています。安全のため、動的クエリに値を挿入する前に、列またはテーブル識別子を含む式をquot_identを使用して渡す必要があります。式に、構築されたステートメントの文字列である必要がある値が含まれている場合は、quote_literalを使用して式を渡す必要があります。これらの関数は適切な手順を実行して、二重引用符 (") または一重引用符 (') で囲まれたテキストを返します。埋め込まれた特殊文字はすべて適切にエスケープされます。

quote_literalはSTRICTとして指定されているため、nullパラメーターで呼び出されると常にnullを返します。上記の例では、newvalueまたはkeyvalueがnullの場合、動的クエリ文字列全体がnullになり、EXECUTEステートメントがエラーになります。この問題を防ぐには、quote_nullable関数を使用します。この関数はquote_literalと同じですが、quote_nullableがNULLパラメーターで呼び出されると文字列nullが返されます。例：

EXECUTE「UPDATE tbl SET」
            | | quote_ident(colname)
            || ' = '
            | | quote_nullable(newvalue)
            | | 'WHERE key = '
            | | quote_nullable (キー値);

nullの可能性のあるパラメーター値を扱う場合は、quote_literalをquote_nullableに置き換える必要があります。

クエリのnull値が予期しない結果を出さないように注意してください。たとえば、keyvalueがnullの場合、次のWHERE句は成功しません。

'WHERE key = '| | quote_nullable(keyvalue)

これは、=演算子でnullオペランドを使用した結果が常にnullになるためです。 null値を通常のキー値のように機能させる場合は、次の方法で上記のステートメントを書き直すことができます。

'WHERE key IS NOT DISTINCT FROM '| | quote_nullable(keyvalue)

ドル記号 ($) の引用は、固定テキストの引用にのみ役立ちます。次のようなステートメントを書かないでください。

EXECUTE「UPDATE tbl SET」
            | | quote_ident(colname)
            | | ' = $$'
            | | newvalue
            | | '$$ WHERE key = '
            | | quote_literal (キー値);

これは、newvalueの内容に $$ が含まれている場合、エラーが発生するためです。これは、他のドル引用区切り文字を使用すると発生する可能性があります。したがって、事前に不明なテキストを安全に引用するには、quote_literal、quote_nullable、またはquote_identを正しく使用する必要があります。

動的SQLステートメントは、format関数を使用して安全に構築することもできます。例：

EXECUTE形式 ('UPDATE tbl SET % I = % L ')
       'WHERE key = % L' 、colname、newvalue、keyvalue);

% Iはquote_identに相当し、% Lはquote_nullableに相当します。 format関数は、USING句と一緒に使用できます。例：

EXECUTE形式 ('UPDATE tbl SET % I = $1 WHERE key = $2 '、colname)
       使用のnewvalue、keyvalue;

この方法は、変数が強制的にテキストに変換されて % Lを使用して引用されるのではなく、ネイティブのデータ型形式で処理されるため、優れています。この方法もより効率的です。

結果ステータスの取得

複数のメソッドを使用して、ステートメントの効果を判断できます。最初の方法は、GET DIAGNOSTICSステートメントを使用することです。構文：

GET [現在] DIAGNOSTICS変数 { = | := } item [ , ... ];

このステートメントを使用すると、システムステータスインジケータを取得できます。 CURRENTはノイズワードです。各項目は、特定の変数に割り当てられる状態値を識別するキーワードである。状態値を受け取るには、変数が正しいデータ型である必要があります。次の表に、使用可能な診断項目を示します。 colon-equal sign (:=) は、SQL文の等号 (=) を置き換えるために使用できます。例：

診断integer_var = ROW_COUNT;

利用可能な診断アイテム

項目	種類	説明
`ROW_COUNT`	`ビギント`	最新のSQL文によって処理される行の数。
`PG_コンテキスト`	`テキスト`	現在の呼び出しスタックを記述するテキスト行。

2番目の方法は、ブール型の特殊変数FOUNDをチェックすることです。 PL/SQL関数が呼び出されるたびに、FOUNDの初期値はfalseになります。値は、次のステートメントの結果に基づいて設定されます。

SELECT INTOステートメントで1つの行が割り当てられた場合、FOUNDの値はtrueに設定されます。行が返されない場合、値はfalseに設定されます。
PERFORMステートメントが1つ以上の行を返し、結果を破棄する場合、FOUNDの値はtrueに設定されます。行が返されない場合、値はfalseに設定されます。
UPDATE、INSERT、またはDELETEステートメントが少なくとも1つの行に影響する場合、FOUNDの値はtrueに設定されます。行が影響を受けない場合、値はfalseに設定されます。
FETCHステートメントが1行を返す場合、FOUNDの値はtrueに設定されます。行が返されない場合、値はfalseに設定されます。
MOVEステートメントが正常にカーソルの位置を変更した場合、FOUNDの値はtrueに設定されます。それ以外の場合、値はfalseに設定されます。
FORまたはFOREACHステートメントが1回以上反復された場合、FOUNDの値はtrueに設定されます。それ以外の場合、値はfalseに設定されます。ステートメントがループを終了すると、FOUNDの値が同じ方法で設定されます。ループ実行中に、FOUNDはループ本体内の他のステートメントの実行によって変更される可能性がありますが、値はループステートメントによって変更されません。
RETURN QUERYまたはRETURN QUERY EXECUTEステートメントが少なくとも1つの行を返す場合、FOUNDの値はtrueに設定されます。行が返されない場合、値はfalseに設定されます。

上記以外のPL/SQL文を実行しても、FOUNDの値は変わりません。 EXECUTEはGET DIAGNOSTICSの出力を変更しますが、FOUNDの値は変更しません。

FOUNDはすべてのPL/SQL関数にローカルであり、それに対する変更は現在の関数にのみ影響します。

プレースホルダーステートメントの実行

何もしないプレースホルダーステートメントは便利です。たとえば、if-then-elseチェーンで意図的に確保されている空のブランチを示すことができます。これを行うには、NULLステートメントを使用します。

NULL;

たとえば、次の2つのステートメントで同じ効果が得られます。

BEGIN
        y := x / 0;
    EXCEPTION
        division_by_zeroがあったとき
            NULL; -- エラーを無視します。
    END;

BEGIN
        y := x / 0;
    EXCEPTION
        いつdivision_by_zero THEN -- エラーを無視します。
    エンド;

あなたの個人的な好みに基づいてそれらの1つを選ぶことができます。

説明

OracleのPL/SQLでは、空のステートメントリストは許可されません。したがって、代わりにNULLステートメントを使用する必要があります。ただし、PL/SQLでは空のステートメントを使用できます。