Amazon Redshift は、パッチ 198 以降、新しい Python UDF の作成をサポートしなくなります。既存の Python UDF は、2026 年 6 月 30 日まで引き続き機能します。詳細については、[ブログ記事](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)を参照してください。

# Amazon Redshift のストアドプロシージャの作成
<a name="stored-procedure-overview"></a>

このトピックでは、Amazon Redshift でストアドプロシージャを作成して使用する方法について説明します。ストアドプロシージャとは、複数のプログラムが使用できる SQL ステートメントのコレクションです。

Amazon Redshift のストアドプロシージャを PostgreSQL プロシージャ言語 PL/pgSQL を使用して定義し、一連の SQL クエリと論理オペレーションを実行できます。プロシージャはデータベースに保存され、適切なデータベース権限を持つすべてのユーザーが使用できます。

ユーザー定義関数 (UDF) とは異なり、ストアドプロシージャは SELECT クエリに加えてデータ定義言語 (DDL) とデータ操作言語 (DML) を組み込むことができます。ストアドプロシージャは値を返す必要がありません。ループ式や条件式などのプロシージャ言語を使用して論理フローを制御できます。

ストアドプロシージャを作成および管理するための SQL コマンドの詳細については、以下のコマンドトピックを参照してください。
+ [CREATE PROCEDURE](r_CREATE_PROCEDURE.md)
+ [ALTER PROCEDURE](r_ALTER_PROCEDURE.md)
+ [DROP PROCEDURE](r_DROP_PROCEDURE.md)
+ [SHOW PROCEDURE](r_SHOW_PROCEDURE.md)
+ [CALL](r_CALL_procedure.md)
+ [GRANT](r_GRANT.md)
+ [REVOKE](r_REVOKE.md)
+ [ALTER DEFAULT PRIVILEGES](r_ALTER_DEFAULT_PRIVILEGES.md)

**Topics**
+ [Amazon Redshift でのストアドプロシージャの概要](stored-procedure-create.md)
+ [PL/pgSQL 言語リファレンス](c_pl_pgSQL_reference.md)

# Amazon Redshift でのストアドプロシージャの概要
<a name="stored-procedure-create"></a>

このトピックでは、ストアドプロシージャの目的と使用について詳しく説明します。

ストアドプロシージャは、通常、データ変換やデータ検証のロジック、ビジネス固有のロジックをカプセル化するために使用します。複数の SQL ステップをストアドプロシージャにまとめることで、アプリケーションとデータベースを往復する回数を減らすことができます。

細かいアクセスコントロールを行う場合、ストアドプロシージャを作成して、基礎となるテーブルへのアクセスをユーザーに許可することなく関数を実行できます。例えば、所有者またはスーパーユーザーのみがテーブルを切り詰めることができます。また、ユーザーがテーブルにデータを挿入する場合は書き込み権限が必要です。基となるテーブルへの権限をユーザーに付与することなく、タスクを実行するストアドプロシージャを作成できます。次に、このストアドプロシージャを実行するための権限をユーザーに付与します。

ストアドプロシージャは、DEFINER セキュリティ属性が設定されている場合、ストアドプロシージャの所有者の権限で実行されます。デフォルトでは、ストアドプロシージャには INVOKER セキュリティが設定されます。この場合、プロシージャはプロシージャを呼び出したユーザーの権限を使用します。

ストアドプロシージャを作成するには、[CREATE PROCEDURE](r_CREATE_PROCEDURE.md) コマンドを使用します。プロシージャを実行するには、[CALL](r_CALL_procedure.md) コマンドを使用します。このセクションで後ほど例を紹介します。

**注記**  
一部のクライアントは、Amazon Redshift のストアドプロシージャの作成時に次のエラーを表示する場合があります。  

```
ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$
```
このエラーは、セミコロン区切りのステートメントとドル記号 (\$1) 引用符を使用して CREATE PROCEDURE ステートメントをクライアントが正しく解析できないために発生します。これにより、ステートメントの一部のみ Amazon Redshift サーバーに送信されます。通常、このエラーは、クライアントの `Run as batch` オプションまたは `Execute selected` オプションを使用することで回避できます。  
たとえば、Aginity クライアントを使用する場合は、`Run entire script as batch` オプションを使用します。SQL Workbench/J を使用する場合は、バージョン 124 をお勧めします。SQL Workbench/J バージョン 125 を使用する場合は、回避策として代替の区切り文字を指定することを検討してください。  
CREATE PROCEDURE(手順の作成)には、セミコロン (;) で区切られた SQL ステートメントが含まれています。CREATE PROCEDURE にはセミコロン (;) で区切られた SQL ステートメントが含まれているため、スラッシュ (/) など、代替の区切り文字を定義し、CREATE PROCEDURE ステートメントの最後に配置すると、ステートメントが Amazon Redshift サーバーに送信され、処理されます。次に例を示します。  

```
CREATE OR REPLACE PROCEDURE test()
AS $$
BEGIN
  SELECT 1 a;
END;
$$
LANGUAGE plpgsql
;
/
```
[Amazon Redshift コンソールのクエリエディタ](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html)や TablePlus など、CREATE PROCEDURE ステートメントの解析をサポートするクライアントを使用できます。

**Topics**
+ [ストアドプロシージャの名前付け](stored-procedure-naming.md)
+ [ストアドプロシージャのセキュリティおよび権限](stored-procedure-security-and-privileges.md)
+ [ストアドプロシージャから結果セットを返す](stored-procedure-result-set.md)
+ [トランザクションの管理](stored-procedure-transaction-management.md)
+ [エラーのトラップ](stored-procedure-trapping-errors.md)
+ [ストアドプロシージャのログ記録](c_PLpgSQL-logging.md)
+ [ストアドプロシージャの制限事項](stored-procedure-constraints.md)

次の例は、出力引数を使用しないプロシージャを示しています。デフォルトでは、引数は入力 (IN) 引数です。

```
CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
AS $$
BEGIN
  RAISE INFO 'f1 = %, f2 = %', f1, f2;
END;
$$ LANGUAGE plpgsql;

call test_sp1(5, 'abc');
INFO: f1 = 5, f2 = abc
CALL
```



**注記**  
 ストアドプロシージャを記述する場合は、機密の値を保護するためのベストプラクティスに従うことをお勧めします。  
ストアドプロシージャロジックに機密情報をハードコーディングしないでください。例えば、ストアドプロシージャの本文の CREATE USER ステートメントにユーザーパスワードを割り当てないでください。ハードコードした値は、カタログテーブルにスキーマメタデータとして記録される可能性があるため、セキュリティ上のリスクが生じます。代わりに、パスワードなどの機密の値は、パラメータを使用して引数として、ストアドプロシージャに渡します。  
ストアドプロシージャの詳細については、「[CREATE PROCEDURE](r_CREATE_PROCEDURE.md)」および「[Amazon Redshift のストアドプロシージャの作成](stored-procedure-overview.md)」を参照してください。カタログテーブルの詳細については、「[システムカタログテーブル](c_intro_catalog_views.md)」を参照してください。

次の例は、出力引数を使用するプロシージャを示しています。引数は、入力 (IN)、入力および出力 (INOUT)、出力 (OUT) です。

```
CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;


call test_sp2(2,'2019');

         f2          | column2
---------------------+---------
 2019+2019+2019+2019 | 2
(1 row)
```

# ストアドプロシージャの名前付け
<a name="stored-procedure-naming"></a>

このトピックでは、ストアドプロシージャ名の詳細について説明します。

プロシージャを定義する際に同じ名前を付けて、異なる入力引数のデータ型 (署名) を使用すると、別のプロシージャが作成されます。その結果、プロシージャ名はオーバーロードされます。詳細については、「[プロシージャ名の多重定義](#stored-procedure-overloading-name)」を参照してください。Amazon Redshift では、出力引数に基づくプロシージャのオーバーロードを有効にしません。2 つのプロシージャで名前と入力引数のデータ型を同じにして、出力引数の型を異なるものにすることはできません。

所有者またはスーパーユーザーは、ストアドプロシージャの本文を、同じ署名を使用する新しいものに置き換えることができます。ストアドプロシージャの署名または戻り値の型を変更するには、ストアドプロシージャを削除して再作成します。詳細については、「[DROP PROCEDURE](r_DROP_PROCEDURE.md)」および「[CREATE PROCEDURE](r_CREATE_PROCEDURE.md)」を参照してください。

ストアドプロシージャを実装する前に、その命名規則を考慮することで、競合の可能性や予期しない結果を回避できます。プロシージャ名は多重定義できるため、Amazon Redshift の既存および将来のプロシージャ名と競合する可能性があります。

## プロシージャ名の多重定義
<a name="stored-procedure-overloading-name"></a>

プロシージャは、その名前と署名 (入力引数の数および引数のデータ型) で識別されます。名前が同じでも署名が異なる 2 つのプロシージャは、同じスキーマ内に存在できます。つまり、プロシージャ名は多重定義できます。

プロシージャを実行する際に、クエリエンジンは、指定された引数の数と引数のデータ型に基づいて、呼び出すプロシージャを決定します。多重定義を使用すると、CREATE PROCEDURE コマンドで許可されている制限まで、可変数の引数を使用してプロシージャをシミュレートできます。詳細については、「[CREATE PROCEDURE](r_CREATE_PROCEDURE.md)」を参照してください。

## 名前の競合の回避
<a name="stored-procedure-name-conflicts"></a>

プレフィックス `sp_` を使用してすべてのプロシージャに名前を付けることをお勧めします。Amazon Redshift は、ストアドプロシージャ専用に `sp_` プレフィックスを予約します。プロシージャ名に `sp_` プレフィックスを付けることで、プロシージャ名は Amazon Redshift の既存または将来のプロシージャ名と競合しなくなります。

# ストアドプロシージャのセキュリティおよび権限
<a name="stored-procedure-security-and-privileges"></a>

このトピックでは、ストアドプロシージャの作成と実行に必要なデータベース認証情報について説明します。

デフォルトでは、すべてのユーザーにプロシージャを作成する権限があります。プロシージャを作成するには、言語 PL/pgSQL に対する USAGE 権限が必要です。この権限はデフォルトで PUBLIC に付与されます。デフォルトでは、スーパーユーザーと所有者のみに、プロシージャを呼び出す権限があります。スーパーユーザーは、特定のユーザーに対してストアドプロシージャの作成を禁止する場合、このユーザーに対して PL/pgSQL の REVOKE USAGE を実行できます。

プロシージャを呼び出すには、プロシージャに対する EXECUTE 権限が付与されている必要があります。デフォルトでは、新しいプロシージャに対する EXECUTE 権限はプロシージャの所有者とスーパーユーザーに付与されます。詳細については、「[GRANT](r_GRANT.md)」を参照してください。

プロシージャを作成したユーザーがデフォルトで所有者になります。所有者は、デフォルトで、プロシージャに対する CREATE、DROP、EXECUTE の権限を持ちます。スーパーユーザーはすべての権限を持ちます。

SECURITY 属性は、データベースオブジェクトにアクセスするプロシージャの権限を制御します。ストアドプロシージャの作成時に、SECURITY 属性を DEFINER または INVOKER のいずれかに設定できます。この属性は、ストアドプロシージャの本文でステートメントを実行するときに使用する権限を決定します。SECURITY INVOKER を指定すると、プロシージャはプロシージャを呼び出したユーザーの権限を使用します。SECURITY DEFINER を指定すると、プロシージャはプロシージャの所有者の権限を使用します。デフォルトは INVOKER です。

SECURITY DEFINER プロシージャは、それを所有するユーザーの権限で実行されるため、プロシージャを誤用しないよう注意してください。SECURITY DEFINER プロシージャの誤用を確実に避けるには、以下を行います。
+ SECURITY DEFINER プロシージャに対する EXECUTE を特定のユーザーに付与し、PUBLIC には付与しません。
+ プロシージャがアクセスする必要があるすべてのデータベースオブジェクトをスキーマ名で修飾します。たとえば、単に `myschema.mytable` としないで、`mytable` を使用します。
+ オブジェクト名をスキーマで修飾できない場合は、プロシージャの作成時に SET オプションを使用して `search_path` を設定します。信頼されていないユーザーが書き込むことができるすべてのスキーマを除外するように `search_path` を設定します。このアプローチにより、プロシージャで使用する予定のオブジェクトをマスクするオブジェクト (テーブルやビューなど) を、このプロシージャの呼び出し元が作成できないようにします。SET オプションの詳細については、「[CREATE PROCEDURE](r_CREATE_PROCEDURE.md)」を参照してください。

次の例では、`search_path` を `admin` に設定することで、`user_creds` テーブルに対して `admin` スキーマからはアクセスできるが、パブリックや呼び出し元の `search_path` の他のスキーマからはアクセスできないようにします。

```
CREATE OR REPLACE PROCEDURE sp_get_credentials(userid int, o_creds OUT varchar)
AS $$
BEGIN
  SELECT creds INTO o_creds
  FROM user_creds
  WHERE user_id = $1;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
-- Set a secure search_path
SET search_path = admin;
```

# ストアドプロシージャから結果セットを返す
<a name="stored-procedure-result-set"></a>

このトピックでは、ストアドプロシージャがデータを返す方法について説明します。

カーソルや一時テーブルを使用して結果セットを返すことができます。

## カーソルを返す
<a name="stored-procedure-return-cursor"></a>

カーソルを返すには、`refcursor` データ型で定義した INOUT 引数を使用してプロシージャを作成します。このプロシージャを呼び出す場合は、カーソルに名前を付けます。そして、カーソルから名前を指定して結果を取得することができます。

次の例では、`get_result_set` という名前のプロシージャを作成し、`rs_out` をデータ型として `refcursor` という名前の INOUT 引数を使用します。プロシージャは SELECT ステートメントを使用してカーソルを開きます。

```
CREATE OR REPLACE PROCEDURE get_result_set (param IN integer, rs_out INOUT refcursor)
AS $$
BEGIN
  OPEN rs_out FOR SELECT * FROM fact_tbl where id >= param;
END;
$$ LANGUAGE plpgsql;
```

次の CALL コマンドは `mycursor` という名前のカーソルを開きます。カーソルはトランザクション内でのみ使用します。

```
BEGIN;
CALL get_result_set(1, 'mycursor');
```

カーソルが開いたら、次の例に示すように、カーソルから結果を取得できます。

```
FETCH ALL FROM mycursor;

    id | secondary_id | name
-------+--------------+---------
     1 |            1 | Joe
     1 |            2 | Ed
     2 |            1 | Mary
     1 |            3 | Mike
(4 rows)
```

最後に、トランザクションはコミットまたはロールバックされます。

```
COMMIT;   
```

ストアドプロシージャから返されるカーソルには、DECLARE CURSOR で説明している同じ制約とパフォーマンスの考慮事項が適用されます。。詳細については、「[カーソルの制約](declare.md#declare-constraints)」を参照してください。

次の例では、JDBC から `get_result_set` データ型を使用して `refcursor` ストアドプロシージャを呼び出しています。リテラル `'mycursor'` (カーソル名) は `prepareStatement` に渡されます。次に、結果が `ResultSet` から取得されます。

```
static void refcursor_example(Connection conn) throws SQLException {
    conn.setAutoCommit(false);
    PreparedStatement proc = conn.prepareStatement("CALL get_result_set(1, 'mycursor')");
    proc.execute();
    ResultSet rs = statement.executeQuery("fetch all from mycursor");
    while (rs.next()) {
      int n = rs.getInt(1);
      System.out.println("n " + n);
    }
```

## 一時テーブルの使用
<a name="stored-procedure-return-cursor"></a>

結果を返すには、結果行を含む一時テーブルへのハンドルを返すことができます。クライアントは、パラメータとして名前をストアドプロシージャに渡します。ストアドプロシージャ内では、動的 SQL を使用して一時テーブルを操作できます。例を以下に示します。

```
CREATE PROCEDURE get_result_set(param IN integer, tmp_name INOUT varchar(256)) as $$
DECLARE
  row record;
BEGIN
  EXECUTE 'drop table if exists ' || tmp_name;
  EXECUTE 'create temp table ' || tmp_name || ' as select * from fact_tbl where id <= ' || param;
END;
$$ LANGUAGE plpgsql;

CALL get_result_set(2, 'myresult');
 tmp_name
-----------
 myresult
(1 row)

SELECT * from myresult;
 id | secondary_id | name
----+--------------+------
  1 |            1 | Joe
  2 |            1 | Mary
  1 |            2 | Ed
  1 |            3 | Mike
(4 rows)
```

# トランザクションの管理
<a name="stored-procedure-transaction-management"></a>

ストアドプロシージャは、デフォルトのトランザクション管理動作または非アトミック動作で作成できます。

## デフォルトモードのストアドプロシージャのトランザクション管理
<a name="stored-procedure-transaction-management-default-mode"></a>

デフォルトのトランザクションモードの自動コミット動作では、個別に実行された SQL コマンドが個別にコミットされます。ストアドプロシージャへの呼び出しは、単一の SQL コマンドとして扱われます。プロシージャ内の SQL ステートメントは、呼び出しの開始時および終了時に暗黙的に開始および終了するトランザクションブロックに存在するかのように動作します。別のプロシージャに対するネストされた呼び出しは、他の任意の SQL ステートメントのように扱われ、呼び出し元と同じトランザクションのコンテキスト内で動作します。自動コミット動作の詳細については、「[Amazon Redshift の分離レベル](c_serial_isolation.md)」を参照してください。

ただし、ユーザー指定のトランザクションブロック (BEGIN... COMMIT で定義される) 内からストアドプロシージャを呼び出すとします。この場合、ストアドプロシージャのすべてのステートメントはユーザー指定のトランザクションのコンテキストで実行されます。プロシージャは終了時に暗黙でコミットしません。呼び出し元がプロシージャのコミットまたはロールバックを制御します。

ストアドプロシージャの実行中にエラーが発生した場合は、現在のトランザクションで行われたすべての変更がロールバックされます。

ストアドプロシージャでは、次のトランザクション制御ステートメントを使用できます。
+ COMMIT – 現在のトランザクションで行われたすべての作業をコミットし、暗黙的に新しいトランザクションを開始します。詳細については、「[COMMIT](r_COMMIT.md)」を参照してください。
+ ROLLBACK – 現在のトランザクションで行われた作業をロールバックし、暗黙的に新しいトランザクションを開始します。詳細については、「[ROLLBACK](r_ROLLBACK.md)」を参照してください。

TRUNCATE は、ストアドプロシージャ内から発行できる別のステートメントであり、トランザクション管理に影響します。Amazon Redshift では、TRUNCATE は暗黙でコミットを発行します。この動作は、ストアドプロシージャのコンテキストでも変わりません。TRUNCATE ステートメントは、ストアドプロシージャ内から発行されると、現在のトランザクションをコミットして新しいものを開始します。詳細については、「[TRUNCATE](r_TRUNCATE.md)」を参照してください。

COMMIT、ROLLBACK、または TRUNCATE ステートメントに続くステートメントはすべて、新しいトランザクションのコンテキストで実行されます。これらは、COMMIT、ROLLBACK、または TRUNCATE ステートメントが検出されるか、ストアドプロシージャが終了するまで行われます。

ストアドプロシージャ内から COMMIT、ROLLBACK、または TRUNCATE ステートメントを使用する場合は、次の制約が適用されます。
+ ストアドプロシージャがトランザクションブロック内から呼び出された場合、COMMIT、ROLLBACK、または TRUNCATE ステートメントを発行することはできません。この制限は、ストアドプロシージャ自体の本体内およびネストされたプロシージャ呼び出し内に適用されます。
+ ストアドプロシージャが `SET config` オプション付きで作成されている場合は、COMMIT、ROLLBACK、または TRUNCATE ステートメントを発行することはできません。この制限は、ストアドプロシージャ自体の本体内およびネストされたプロシージャ呼び出し内に適用されます。
+ すべての (明示的または暗黙的に) 開いているカーソルは、COMMIT、ROLLBACK、または TRUNCATE ステートメントの処理時に自動的に閉じられます。明示的および暗黙的なカーソルの制約については、「[ストアドプロシージャの制限事項](stored-procedure-constraints.md)」を参照してください。

また、動的 SQL を使用して COMMIT または ROLLBACK を実行することはできません。ただし、動的 SQL を使用して TRUNCATE を実行することができます。詳細については、「[動的 SQL](c_PLpgSQL-statements.md#r_PLpgSQL-dynamic-sql)」を参照してください。

ストアドプロシージャを使用する場合、PL/pgSQL の BEGIN および END ステートメントはグループ化のみを目的としている点を考慮してください。トランザクションを開始または終了することはありません。詳細については、「[ブロック](c_PLpgSQL-structure.md#r_PLpgSQL-block)」を参照してください。

次の例は、ストアドプロシージャを明示的なトランザクションブロック内から呼び出した場合のトランザクション動作を示しています。ストアドプロシージャの外部から発行された 2 つの挿入ステートメントと内部から発行された 1 つの挿入ステートメントは、すべて同じトランザクション (3382) に属します。トランザクションは、ユーザーが明示的なコミットを発行したときにコミットされます。

```
CREATE OR REPLACE PROCEDURE sp_insert_table_a(a int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table_a values (a);
END;
$$;

Begin;
  insert into test_table_a values (1);
  Call sp_insert_table_a(2);
  insert into test_table_a values (3);
Commit; 

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  | pid |  type   |               stmt_text
--------+------+-----+---------+----------------------------------------
    103 | 3382 | 599 | UTILITY | Begin;
    103 | 3382 | 599 | QUERY   | insert into test_table_a values (1);
    103 | 3382 | 599 | UTILITY | Call sp_insert_table_a(2);
    103 | 3382 | 599 | QUERY   | INSERT INTO test_table_a values ( $1 )
    103 | 3382 | 599 | QUERY   | insert into test_table_a values (3);
    103 | 3382 | 599 | UTILITY | COMMIT
```

それに対して、明示的なトランザクションブロックの外部から同じステートメントが発行され、セッションの自動コミットがオンに設定されている場合の例を示します。この場合、各ステートメントは独自のトランザクションで実行されます。

```
insert into test_table_a values (1);
Call sp_insert_table_a(2);
insert into test_table_a values (3);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  | pid |  type   |                                                                    stmt_text
--------+------+-----+---------+-------------------------------------------------------------------------------------------------------------------------------------------------
    103 | 3388 | 599 | QUERY   | insert into test_table_a values (1);
    103 | 3388 | 599 | UTILITY | COMMIT
    103 | 3389 | 599 | UTILITY | Call sp_insert_table_a(2);
    103 | 3389 | 599 | QUERY   | INSERT INTO test_table_a values ( $1 )
    103 | 3389 | 599 | UTILITY | COMMIT
    103 | 3390 | 599 | QUERY   | insert into test_table_a values (3);
    103 | 3390 | 599 | UTILITY | COMMIT
```

次の例では、TRUNCATE ステートメントを `test_table_a` 内に挿入後に発行しています。。TRUNCATE ステートメントは、現在のトランザクション (3335) をコミットして新しいトランザクション (3336) を開始する暗黙的なコミットを発行します。新しいトランザクションは、プロシージャの終了時にコミットされます。

```
CREATE OR REPLACE PROCEDURE sp_truncate_proc(a int, b int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table_a values (a);
  TRUNCATE test_table_b;
  INSERT INTO test_table_b values (b);
END;
$$;

Call sp_truncate_proc(1,2);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |  pid  |  type   |                                                                                             stmt_text
--------+------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    103 | 3335 | 23636 | UTILITY | Call sp_truncate_proc(1,2);
    103 | 3335 | 23636 | QUERY   | INSERT INTO test_table_a values ( $1 )
    103 | 3335 | 23636 | UTILITY | TRUNCATE test_table_b
    103 | 3335 | 23636 | UTILITY | COMMIT
    103 | 3336 | 23636 | QUERY   | INSERT INTO test_table_b values ( $1 )
    103 | 3336 | 23636 | UTILITY | COMMIT
```

次の例では、ネストされた呼び出しから TRUNCATE を発行しています。TRUNCATE は、トランザクション (3344) の外部および内部のプロシージャでそれまでに処理されたすべての作業をコミットします。また、新しいトランザクション (3345) を開始します。新しいトランザクションは、外部のプロシージャの終了時にコミットされます。

```
CREATE OR REPLACE PROCEDURE sp_inner(c int, d int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO inner_table values (c);
  TRUNCATE outer_table;
  INSERT INTO inner_table values (d);
END;
$$;

CREATE OR REPLACE PROCEDURE sp_outer(a int, b int, c int, d int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO outer_table values (a);
  Call sp_inner(c, d);
  INSERT INTO outer_table values (b);
END;
$$;

Call sp_outer(1, 2, 3, 4);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |  pid  |  type   |                                                                                              stmt_text
--------+------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    103 | 3344 | 23636 | UTILITY | Call sp_outer(1, 2, 3, 4);
    103 | 3344 | 23636 | QUERY   | INSERT INTO outer_table values ( $1 )
    103 | 3344 | 23636 | UTILITY | CALL sp_inner( $1 , $2 )
    103 | 3344 | 23636 | QUERY   | INSERT INTO inner_table values ( $1 )
    103 | 3344 | 23636 | UTILITY | TRUNCATE outer_table
    103 | 3344 | 23636 | UTILITY | COMMIT
    103 | 3345 | 23636 | QUERY   | INSERT INTO inner_table values ( $1 )
    103 | 3345 | 23636 | QUERY   | INSERT INTO outer_table values ( $1 )
    103 | 3345 | 23636 | UTILITY | COMMIT
```

次の例は、TRUNCATE ステートメントのコミット時にカーソル `cur1` が閉じられたことを示しています。

```
CREATE OR REPLACE PROCEDURE sp_open_cursor_truncate()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  cur1 cursor for select * from test_table_a order by 1;
BEGIN
  open cur1;
  TRUNCATE table test_table_b;
  Loop
    fetch cur1 into rec;
    raise info '%', rec.c1;
    exit when not found;
  End Loop;
END
$$;

call sp_open_cursor_truncate();
ERROR: cursor "cur1" does not exist
CONTEXT: PL/pgSQL function "sp_open_cursor_truncate" line 8 at fetch
```

次の例では、TRUNCATE ステートメントを発行します。明示的なトランザクションブロック内から呼び出すことはできません。

```
CREATE OR REPLACE PROCEDURE sp_truncate_atomic() LANGUAGE plpgsql
AS $$
BEGIN
  TRUNCATE test_table_b;
END;
$$;

Begin;
  Call sp_truncate_atomic();
ERROR: TRUNCATE cannot be invoked from a procedure that is executing in an atomic context.
HINT: Try calling the procedure as a top-level call i.e. not from within an explicit transaction block. 
Or, if this procedure (or one of its ancestors in the call chain) was created with SET config options, recreate the procedure without them.
CONTEXT: SQL statement "TRUNCATE test_table_b"
PL/pgSQL function "sp_truncate_atomic" line 2 at SQL statement
```

次の例は、スーパーユーザーまたはテーブルの所有者ではないユーザーが、テーブルに対して TRUNCATE ステートメントを発行できることを示しています。ユーザーは、`Security Definer`ストアドプロシージャを使用して、これを行います。この例では以下のアクションを示します。
+ ユーザー 1 はテーブル `test_tbl` を作成します。
+ ユーザー 1 はストアドプロシージャ `sp_truncate_test_tbl` を作成します。
+ ユーザー 1 は、ストアドプロシージャに対する `EXECUTE` 権限をユーザー 2 に付与します。
+ ユーザー 2 は、ストアドプロシージャを実行してテーブル `test_tbl` を切り捨てます。この例は、`TRUNCATE` コマンドの前後の行数を示しています。

```
set session_authorization to user1;
create table test_tbl(id int, name varchar(20));
insert into test_tbl values (1,'john'), (2, 'mary');
CREATE OR REPLACE PROCEDURE sp_truncate_test_tbl() LANGUAGE plpgsql
AS $$
DECLARE
  tbl_rows int;
BEGIN
  select count(*) into tbl_rows from test_tbl;
  RAISE INFO 'RowCount before Truncate: %', tbl_rows;
  TRUNCATE test_tbl;
  select count(*) into tbl_rows from test_tbl;
  RAISE INFO 'RowCount after Truncate: %', tbl_rows;
END;
$$ SECURITY DEFINER;
grant execute on procedure sp_truncate_test_tbl() to user2;
reset session_authorization;


set session_authorization to user2;
call sp_truncate_test_tbl();
INFO:  RowCount before Truncate: 2
INFO:  RowCount after Truncate: 0
CALL
reset session_authorization;
```

次の例では、COMMIT を 2 回発行しています。最初の COMMIT では、トランザクション 10363 で行われたすべての作業をコミットし、トランザクション 10364 を暗黙的に開始します。トランザクション 10364 は、2 番目の COMMIT ステートメントによってコミットされます。

```
CREATE OR REPLACE PROCEDURE sp_commit(a int, b int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table values (a);
  COMMIT;
  INSERT INTO test_table values (b);
  COMMIT;
END;
$$;

call sp_commit(1,2);

select userid, xid, pid, type, trim(text) as stmt_text
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;
 userid |  xid  | pid  |  type   |                                                                                    stmt_text
--------+-------+------+---------+-----------------------------------------------------------------------------------------------------------------
    100 | 10363 | 3089 | UTILITY | call sp_commit(1,2);
    100 | 10363 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10363 | 3089 | UTILITY | COMMIT
    100 | 10364 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10364 | 3089 | UTILITY | COMMIT
```

次の例では、`sum_vals` が 2 より大きい場合に ROLLBACK ステートメントを発行します。最初の ROLLBACK ステートメントでは、トランザクション 10377 で行われたすべての作業をロールバックし、新しいトランザクション 10378 を開始します。プロシージャが終了すると、トランザクション 10378 がコミットされます。

```
CREATE OR REPLACE PROCEDURE sp_rollback(a int, b int) LANGUAGE plpgsql
AS $$
DECLARE
  sum_vals int;
BEGIN
  INSERT INTO test_table values (a);
  SELECT sum(c1) into sum_vals from test_table;
  IF sum_vals > 2 THEN
    ROLLBACK;
  END IF;
  
  INSERT INTO test_table values (b);
END;
$$;

call sp_rollback(1, 2);

select userid, xid, pid, type, trim(text) as stmt_text
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

userid |  xid  | pid  |  type   |                                                                                    stmt_text
--------+-------+------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    100 | 10377 | 3089 | UTILITY | call sp_rollback(1, 2);
    100 | 10377 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10377 | 3089 | QUERY   | SELECT sum(c1) from test_table
    100 | 10377 | 3089 | QUERY   | Undoing 1 transactions on table 133646 with current xid 10377 : 10377
    100 | 10378 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10378 | 3089 | UTILITY | COMMIT
```

## 非アトミックモードのストアドプロシージャのトランザクション管理
<a name="stored-procedure-transaction-management-nonatomic-mode"></a>

NONATOMIC モードで作成されたストアドプロシージャは、デフォルトモードで作成されたプロシージャとはトランザクション制御動作が異なります。ストアドプロシージャ外の SQL コマンドの自動コミット動作と同様に、NONATOMIC プロシージャの内側の各 SQL ステートメントは、独自のトランザクションで実行され、自動的にコミットします。ユーザーが NONATOMIC ストアドプロシージャ内で明示的なトランザクションブロックを開始した場合、ブロック内の SQL ステートメントは自動的にコミットされません。トランザクションブロックは、その中のステートメントのコミットまたはロールバックを制御します。

NONATOMIC ストアドプロシージャでは、START TRANSACTION ステートメントを使用してプロシージャ内の明示的なトランザクションブロックを開くことができます。ただし、既に開いているトランザクションブロックがある場合、Amazon Redshift はサブトランザクションをサポートしていないため、このステートメントは何もしません。前のトランザクションは続行されます。

NONATOMIC プロシージャ内でカーソルの FOR ループを操作する場合は、クエリの結果を繰り返し処理する前に、必ず明示的なトランザクションブロックを開いてください。それ以外の場合は、ループ内の SQL ステートメントが自動的にコミットされたときにカーソルが閉じられます。

NONATOMIC モードの動作を使用する際の考慮事項は次のとおりです。
+ ストアドプロシージャ内の各 SQL ステートメントは、開いているトランザクションブロックがなく、セッションの自動コミットがオンに設定されている場合、自動的にコミットされます。
+ ストアドプロシージャがトランザクションブロック内から呼び出された場合、COMMIT、ROLLBACK、TRUNCATE ステートメントを発行してトランザクションを終了できます。これはデフォルトモードでは不可能です。
+ START TRANSACTION ステートメントを発行すると、ストアドプロシージャ内のトランザクションブロックを開始できます。

次の例は、NONATOMIC ストアドプロシージャを使用する場合のトランザクション動作を示しています。以下のすべての例のセッションでは、オートコミットが ON に設定されています。

次の例では、NONATOMIC ストアドプロシージャに 2 つの INSERT ステートメントがあります。プロシージャがトランザクションブロックの外部で呼び出されると、プロシージャ内のすべての INSERT ステートメントが自動的にコミットされます。

```
CREATE TABLE test_table_a(v int); 
CREATE TABLE test_table_b(v int); 

CREATE OR REPLACE PROCEDURE sp_nonatomic_insert_table_a(a int, b int) NONATOMIC AS
$$
BEGIN
    INSERT INTO test_table_a values (a);
    INSERT INTO test_table_b values (b);
END;
$$ 
LANGUAGE plpgsql;

Call sp_nonatomic_insert_table_a(1,2);

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |               stmt_text                
--------+------+------------+---------+----------------------------------------
      1 | 1792 | 1073807554 | UTILITY | Call sp_nonatomic_insert_table_a(1,2);
      1 | 1792 | 1073807554 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1792 | 1073807554 | UTILITY | COMMIT
      1 | 1793 | 1073807554 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1793 | 1073807554 | UTILITY | COMMIT
(5 rows)
```

ただし、プロシージャが BEGIN..COMMIT ブロック内から呼び出された場合、すべてのステートメントは同じトランザクションの一部です (xid=1799)。

```
Begin;
  INSERT INTO test_table_a values (10);
  Call sp_nonatomic_insert_table_a(20,30);
  INSERT INTO test_table_b values (40);
Commit; 

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |                stmt_text                 
--------+------+------------+---------+------------------------------------------
      1 | 1799 | 1073914035 | UTILITY | Begin;
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_a values (10);
      1 | 1799 | 1073914035 | UTILITY | Call sp_nonatomic_insert_table_a(20,30);
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_b values (40);
      1 | 1799 | 1073914035 | UTILITY | COMMIT
(7 rows)
```

この例では、トランザクションの開始とコミットの間に 2 つの INSERT ステートメントがあります。プロシージャがトランザクションブロックの外部で呼び出されると、2 つの INSERT ステートメントは同じトランザクションに含まれます (xid=1866)。

```
CREATE OR REPLACE PROCEDURE sp_nonatomic_txn_block(a int, b int) NONATOMIC AS
$$
BEGIN
    START TRANSACTION;
    INSERT INTO test_table_a values (a);
    INSERT INTO test_table_b values (b);
    COMMIT;
END;
$$ 
LANGUAGE plpgsql;

Call sp_nonatomic_txn_block(1,2);

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |               stmt_text                
--------+------+------------+---------+----------------------------------------
      1 | 1865 | 1073823998 | UTILITY | Call sp_nonatomic_txn_block(1,2);
      1 | 1866 | 1073823998 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1866 | 1073823998 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1866 | 1073823998 | UTILITY | COMMIT
(4 rows)
```

プロシージャが BBEGIN...COMMIT ブロック内から呼び出された場合、プロシージャ内の START TRANSACTION は、既に開いているトランザクションがあるため、何も実行しません。このプロシージャ内の COMMIT は、現在のトランザクション (xid=1876) をコミットして、新しいトランザクションをコミットします。

```
Begin;
  INSERT INTO test_table_a values (10);
  Call sp_nonatomic_txn_block(20,30);
  INSERT INTO test_table_b values (40);
Commit; 

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |               stmt_text                
--------+------+------------+---------+----------------------------------------
      1 | 1876 | 1073832133 | UTILITY | Begin;
      1 | 1876 | 1073832133 | QUERY   | INSERT INTO test_table_a values (10);
      1 | 1876 | 1073832133 | UTILITY | Call sp_nonatomic_txn_block(20,30);
      1 | 1876 | 1073832133 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1876 | 1073832133 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1876 | 1073832133 | UTILITY | COMMIT
      1 | 1878 | 1073832133 | QUERY   | INSERT INTO test_table_b values (40);
      1 | 1878 | 1073832133 | UTILITY | COMMIT
(8 rows)
```

この例では、カーソルループを操作する方法を示します。テーブル test\$1table\$1a には 3 つの値があります。目的は、3 つの値を繰り返し処理して、テーブル test\$1table\$1b に挿入することです。NONATOMIC ストアドプロシージャを次の方法で作成すると、最初のループで INSERT ステートメントを実行したあとに、「"cur1" は存在しません」というエラーカーソルが表示されます。これは、INSERT の自動コミットによって開いているカーソルが閉じるためです。

```
insert into test_table_a values (1), (2), (3);

CREATE OR REPLACE PROCEDURE sp_nonatomic_cursor() NONATOMIC
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  cur1 cursor for select * from test_table_a order by 1;
BEGIN
  open cur1;
  Loop
    fetch cur1 into rec;
    exit when not found;
    raise info '%', rec.v;
    insert into test_table_b values (rec.v);
  End Loop;
END
$$;

CALL sp_nonatomic_cursor();

INFO:  1
ERROR:  cursor "cur1" does not exist
CONTEXT:  PL/pgSQL function "sp_nonatomic_cursor" line 7 at fetch
```

カーソルをループさせるには、トランザクションの開始とコミットの間にカーソルを置いてください。

```
insert into test_table_a values (1), (2), (3);

CREATE OR REPLACE PROCEDURE sp_nonatomic_cursor() NONATOMIC
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  cur1 cursor for select * from test_table_a order by 1;
BEGIN
  START TRANSACTION;
  open cur1;
  Loop
    fetch cur1 into rec;
    exit when not found;
    raise info '%', rec.v;
    insert into test_table_b values (rec.v);
  End Loop;
  COMMIT;
END
$$;

CALL sp_nonatomic_cursor();

INFO:  1
INFO:  2
INFO:  3
CALL
```

# エラーのトラップ
<a name="stored-procedure-trapping-errors"></a>

このトピックでは、Amazon Redshift がエラーを処理する方法について説明します。

ストアドプロシージャのクエリまたはコマンドでエラーが発生すると、それ以降のクエリが実行されず、トランザクションがロールバックされます。しかし、 EXCEPTION ブロックを使用してエラーに対処できます。

**注記**  
デフォルトの動作では、ストアドプロシージャにエラーを生成する条件が他にない場合でも、エラーが発生すると後続のクエリは実行されません。

```
[ <<label>> ]
[ DECLARE
  declarations ]
BEGIN
  statements
EXCEPTION
  WHEN OTHERS THEN
    statements
END;
```

例外が発生し、例外処理ブロックを追加すると、REASE ステートメントおよび他のほとんどの PL/pgSQL ステートメントを書き込みできます。たとえば、カスタムメッセージで例外を発生させたり、ロギングテーブルにレコードを挿入したりできます。

例外処理ブロックに入ると、現在のトランザクションがロールバックされ、新しいトランザクションがブロック内のステートメントを実行するために作成されます。ブロック内のステートメントがエラーなしで実行されると、トランザクションはコミットされ、例外が再スローされます。最後に、ストアドプロシージャが終了します。

例外ブロックでサポートされている条件は OTHERS のみです。この条件は、クエリのキャンセルを除いて、すべてのエラータイプに一致します。また、例外処理ブロックでエラーが発生すると、エラーは外部の例外処理ブロックでキャッチできます。

NONATOMIC プロシージャ内でエラーが発生しても、例外ブロックで処理されればエラーは再スローされません。例外処理ブロックでキャッチされた例外をスローする方法については、「PL/pgSQL ステートメント `RAISE`」を参照してください。このステートメントは例外処理ブロックでのみ有効です。詳細については、「[RAISE](c_PLpgSQL-statements.md#r_PLpgSQL-messages-errors)」を参照してください。

**CONTINUE ハンドラを使用して、ストアドプロシージャでエラーが発生した後の処理を制御する**

 `CONTINUE` ハンドラは、NONATOMIC ストアドプロシージャ内の実行フローを制御する例外ハンドラの一種です。これを使用すると、既存のステートメントブロックを終了せずに例外をキャッチして処理できます。通常、ストアドプロシージャでエラーが発生すると、フローが中断され、エラーが呼び出し元に返されます。ただし、ユースケースによっては、フローの中断を必要とするほどエラー状態が深刻ではない場合があります。別のトランザクションで選択したエラー処理ロジックを使用してエラーをスムーズに処理し、エラーの後にステートメントを実行し続けることもできます。以下にその構文を示します。

```
[ DECLARE
  declarations ]
BEGIN
  statements
EXCEPTION
  [ CONTINUE_HANDLER | EXIT_HANDLER ] WHEN OTHERS THEN
    handler_statements
END;
```

さまざまなタイプのエラーに関する情報を収集するのに役立つシステムテーブルがいくつかあります。詳細については[STL\$1LOAD\$1ERRORS](r_STL_LOAD_ERRORS.md)、[STL\$1ERROR](r_STL_ERROR.md)、および[SYS\$1STREAM\$1SCAN\$1ERRORS](r_SYS_STREAM_SCAN_ERRORS.md)を参照してください。エラーのトラブルシューティングに使用できるその他のシステムテーブルもあります。これらに関する情報は、「[システムテーブルとビューのリファレンス](cm_chap_system-tables.md)」を参照してください。

## 例
<a name="stored-procedure-trapping-errors-examples"></a>

次の例は、例外処理ブロックでステートメントを書き込みする方法を示しています。ストアドプロシージャはデフォルトのトランザクション管理動作を使用しています。

```
CREATE TABLE employee (firstname varchar, lastname varchar);
INSERT INTO employee VALUES ('Tomas','Smith');
CREATE TABLE employee_error_log (message varchar);

CREATE OR REPLACE PROCEDURE update_employee_sp() AS
$$
BEGIN
    UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
    EXECUTE 'select invalid';
EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'An exception occurred.';
    INSERT INTO employee_error_log VALUES ('Error message: ' || SQLERRM);
END;
$$ 
LANGUAGE plpgsql;

CALL update_employee_sp();

INFO:  An exception occurred.
ERROR:  column "invalid" does not exist
CONTEXT:  SQL statement "select invalid"
PL/pgSQL function "update_employee_sp" line 3 at execute statement
```

この例では、`update_employee_sp`を呼び出すと、情報メッセージ*例外が発生しました。*が生成され、エラーメッセージがロギングテーブルの`employee_error_log`ログに挿入されます。元の例外は、ストアドプロシージャが終了する前に再度スローされます。次のクエリは、この例を実行した結果のレコードを示しています。

```
SELECT * from employee;

firstname | lastname 
-----------+----------
 Tomas     | Smith

SELECT * from employee_error_log;

          message                     
------------------------------------------------
 Error message: column "invalid" does not exist
```

フォーマットのヘルプや追加レベルのリストなど、RAISE の詳細については、「[サポートされている PL/pgSQL ステートメント](c_PLpgSQL-statements.md)」を参照してください。

次の例は、例外処理ブロックでステートメントを書き込みする方法を示しています。ストアドプロシージャは NONATOMIC トランザクション管理動作を使用しています。この例では、プロシージャコールが完了しても呼び出し元にエラーは返されません。次のステートメントのエラーのため、UPDATE ステートメントはロールバックされません。情報メッセージが表示され、エラーメッセージがロギングテーブルに挿入されます。

```
CREATE TABLE employee (firstname varchar, lastname varchar); 
INSERT INTO employee VALUES ('Tomas','Smith'); 
CREATE TABLE employee_error_log (message varchar);

-- Create the SP in NONATOMIC mode
CREATE OR REPLACE PROCEDURE update_employee_sp_2() NONATOMIC AS
$$
BEGIN
    UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
    EXECUTE 'select invalid';
EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'An exception occurred.';
    INSERT INTO employee_error_log VALUES ('Error message: ' || SQLERRM);
END;
$$ 
LANGUAGE plpgsql;

CALL update_employee_sp_2();
INFO:  An exception occurred.
CALL

SELECT * from employee;

 firstname | lastname 
-----------+----------
 Adam      | Smith
(1 row)

SELECT * from employee_error_log;

                    message                     
------------------------------------------------
 Error message: column "invalid" does not exist
(1 row)
```

この例では、2 つのサブブロックを持つプロシージャを作成する方法を示します。ストアドプロシージャが呼び出されると、最初のサブブロックからのエラーはその例外処理ブロックによって処理されます。最初のサブブロックが完了すると、プロシージャは引き続き 2 番目のサブブロックを実行します。結果から、プロシージャコールが完了してもエラーは発生しないことがわかります。テーブル従業員の UPDATE オペレーションと INSERT オペレーションがコミットされます。両方の例外ブロックからのエラーメッセージがログテーブルに挿入されます。

```
CREATE TABLE employee (firstname varchar, lastname varchar); 
INSERT INTO employee VALUES ('Tomas','Smith'); 
CREATE TABLE employee_error_log (message varchar);

CREATE OR REPLACE PROCEDURE update_employee_sp_3() NONATOMIC AS
$$
BEGIN
    BEGIN
        UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
        EXECUTE 'select invalid1';
    EXCEPTION WHEN OTHERS THEN
        RAISE INFO 'An exception occurred in the first block.';
        INSERT INTO employee_error_log VALUES ('Error message: ' || SQLERRM);
    END;
    BEGIN
        INSERT INTO employee VALUES ('Edie','Robertson');
        EXECUTE 'select invalid2';
    EXCEPTION WHEN OTHERS THEN
        RAISE INFO 'An exception occurred in the second block.';
        INSERT INTO employee_error_log VALUES ('Error message: ' || SQLERRM);
    END;
END;
$$ 
LANGUAGE plpgsql;

CALL update_employee_sp_3();
INFO:  An exception occurred in the first block.
INFO:  An exception occurred in the second block.
CALL

SELECT * from employee;

 firstname | lastname  
-----------+-----------
 Adam      | Smith
 Edie      | Robertson
(2 rows)

SELECT * from employee_error_log;

                     message                     
-------------------------------------------------
 Error message: column "invalid1" does not exist
 Error message: column "invalid2" does not exist
(2 rows)
```

次の例は、CONTINUE 例外ハンドラの使用方法を示しています。このサンプルでは 2 つのテーブルを作成し、ストアドプロシージャで使用します。CONTINUE ハンドラは、NONATOMIC トランザクション管理動作でストアドプロシージャの実行フローを制御します。

```
CREATE TABLE tbl_1 (a int);
CREATE TABLE tbl_error_logging(info varchar, err_state varchar, err_msg varchar);

CREATE OR REPLACE PROCEDURE sp_exc_handling_1() NONATOMIC AS
$$
BEGIN
    INSERT INTO tbl_1 VALUES (1);
    -- Expect an error for the insert statement following, because of the invalid value
    INSERT INTO tbl_1 VALUES ("val");
    INSERT INTO tbl_1 VALUES (2);
EXCEPTION CONTINUE_HANDLER WHEN OTHERS THEN
    INSERT INTO tbl_error_logging VALUES ('Encountered error', SQLSTATE, SQLERRM);
END;
$$ LANGUAGE plpgsql;
```

ストアドプロシージャを呼び出します。

```
CALL sp_exc_handling_1();
```

フローは以下のように進行します。

1. 互換性のないデータ型を列に挿入しようとして、エラーが発生します。EXCEPTION ブロックに制御が移ります。例外処理ブロックが入力されると、現在のトランザクションがロールバックされ、新しい暗黙的トランザクションが作成されてステートメントを実行します。

1. CONTINUE\$1HANDLER のステートメントがエラーなく実行された場合、例外が発生したステートメントの直後のステートメントに制御が移ります。(CONTINUE\$1HANDLER のステートメントによって新しい例外が発生した場合は、EXCEPTION ブロック内の例外ハンドラを使用してその例外を処理できます。)

サンプルストアドプロシージャを呼び出すと、テーブルに次のレコードが格納されます。
+ `SELECT * FROM tbl_1;` を実行すると、2 つのレコードが返されます。これらには値 `1` と `2` が含まれます。
+ `SELECT * FROM tbl_error_logging;` を実行すると、次の値を含むレコードが 1 つ返されます。「**エラーが発生しました」、「**42703」、「**val 列は tbl\$11 に存在しません」

以下の追加のエラー処理例では、EXIT ハンドラと CONTINUE ハンドラの両方を使用しています。データテーブルとロギングテーブルの 2 つのテーブルを作成します。また、エラー処理を示すストアドプロシージャも作成します。

```
CREATE TABLE tbl_1 (a int);
CREATE TABLE tbl_error_logging(info varchar, err_state varchar, err_msg varchar);

CREATE OR REPLACE PROCEDURE sp_exc_handling_2() NONATOMIC AS
$$
BEGIN
    INSERT INTO tbl_1 VALUES (1);
    BEGIN
        INSERT INTO tbl_1 VALUES (100);
        -- Expect an error for the insert statement following, because of the invalid value
        INSERT INTO tbl_1 VALUES ("val");
        INSERT INTO tbl_1 VALUES (101);
    EXCEPTION EXIT_HANDLER WHEN OTHERS THEN
        INSERT INTO tbl_error_logging VALUES ('Encountered error', SQLSTATE, SQLERRM);
    END;
    INSERT INTO tbl_1 VALUES (2);
    -- Expect an error for the insert statement following, because of the invalid value
    INSERT INTO tbl_1 VALUES ("val");
    INSERT INTO tbl_1 VALUES (3);
EXCEPTION CONTINUE_HANDLER WHEN OTHERS THEN
    INSERT INTO tbl_error_logging VALUES ('Encountered error', SQLSTATE, SQLERRM);
END;
$$ LANGUAGE plpgsql;
```

ストアドプロシージャを作成したら、次のように呼び出します。

```
CALL sp_exc_handling_2();
```

BEGIN と END の内部セットで囲まれた内部例外ブロックでエラーが発生すると、EXIT ハンドラによって処理されます。外側のブロックで発生したエラーは、CONTINUE ハンドラによって処理されます。

サンプルストアドプロシージャを呼び出すと、テーブルに次のレコードが格納されます。
+ `SELECT * FROM tbl_1;` を実行すると、値 1、2、3、および 100 を持つ 4 つのレコードが返されます。
+ `SELECT * FROM tbl_error_logging;` を実行すると、2 つのレコードが返されます。これらの値は次のとおりです。「**エラーが発生しました」、「**42703」、「**val 列は tbl\$11 に存在しません」

**tbl\$1error\$1logging** テーブルが存在しない場合、例外が発生します。

次の例は、CONTINUE 例外ハンドラを FOR ループで使用する方法を示しています。このサンプルでは 3 つのテーブルを作成し、ストアドプロシージャ内の FOR ループで使用します。FOR ループは結果セットバリアントであり、クエリの結果を反復処理します。

```
CREATE TABLE tbl_1 (a int);
INSERT INTO tbl_1 VALUES (1), (2), (3);
CREATE TABLE tbl_2 (a int);
CREATE TABLE tbl_error_logging(info varchar, err_state varchar, err_msg varchar);

CREATE OR REPLACE PROCEDURE sp_exc_handling_loop() NONATOMIC AS
$$
DECLARE
 rec RECORD;
BEGIN
    FOR rec IN SELECT a FROM tbl_1
    LOOP
        IF rec.a = 2 THEN
            -- Expect an error for the insert statement following, because of the invalid value
            INSERT INTO tbl_2 VALUES("val");
        ELSE
            INSERT INTO tbl_2 VALUES (rec.a);
        END IF;
    END LOOP;
EXCEPTION CONTINUE_HANDLER WHEN OTHERS THEN
    INSERT INTO tbl_error_logging VALUES ('Encountered error', SQLSTATE, SQLERRM);
END;
$$ LANGUAGE plpgsql;
```

ストアドプロシージャを呼び出します。

```
CALL sp_exc_handling_loop();
```

サンプルストアドプロシージャを呼び出すと、テーブルに次のレコードが格納されます。
+  `SELECT * FROM tbl_2;` を実行すると、2 つのレコードが返されます。これらには値 1 と 3 が含まれます。
+ `SELECT * FROM tbl_error_logging;` を実行すると、次の値を含むレコードが 1 つ返されます。「**エラーが発生しました」「**42703」、「**val 列は tbl\$12 に存在しません」

CONTINUE ハンドラに関する使用上の注意事項:
+ CONTINUE\$1HANDLER キーワードと EXIT\$1HANDLER キーワードは、NONATOMIC ストアドプロシージャでのみ使用できます。
+ CONTINUE\$1HANDLER キーワードと EXIT\$1HANDLER キーワードはオプションです。EXIT\$1HANDLER がデフォルトです。

# ストアドプロシージャのログ記録
<a name="c_PLpgSQL-logging"></a>

このトピックでは、Amazon Redshift がストアドプロシージャのログ記録に使用するストアドプロシージャとビューについて説明します。

ストアドプロシージャに関する詳細は、以下のシステムテーブルやビューに記録されます。
+ SVL\$1STORED\$1PROC\$1CALL – ストアドプロシージャ呼び出しの開始時刻や終了時刻、呼び出しが完了前に終了したかどうかなどの詳細が記録されます。詳細については、「[SVL\$1STORED\$1PROC\$1CALL](r_SVL_STORED_PROC_CALL.md)」を参照してください。
+ SVL\$1STORED\$1PROC\$1MESSAGES – RAISE クエリによって生成されるストアドプロシージャ内のメッセージが、対応するログ記録レベルで記録されます。詳細については、「[SVL\$1STORED\$1PROC\$1MESSAGES](r_SVL_STORED_PROC_MESSAGES.md)」を参照してください。
+ SVL\$1QLOG – ストアドプロシージャから呼び出されたクエリごとに、プロシージャ呼び出しのクエリ ID が記録されます。詳細については、「[SVL\$1QLOG](r_SVL_QLOG.md)」を参照してください。
+ STL\$1UTILITYTEXT – ストアドプロシージャ呼び出しが、完了後に記録されます。詳細については、「[STL\$1UTILITYTEXT](r_STL_UTILITYTEXT.md)」を参照してください。
+ PG\$1PROC\$1INFO – このシステムカタログビューは、ストアドプロシージャに関する情報を示します。詳細については、「[PG\$1PROC\$1INFO](r_PG_PROC_INFO.md)」を参照してください。

# ストアドプロシージャの制限事項
<a name="stored-procedure-constraints"></a>

このトピックでは、Amazon Redshift ストアドプロシージャの制限事項について説明します。

Amazon Redshift のストアドプロシージャを使用する場合は、以下の考慮事項が適用されます。

## Amazon Redshift および PostgreSQL のストアドプロシージャサポートに関する違い
<a name="stored-procedure-differences"></a>

 Amazon Redshift および PostgreSQL 間のストアドプロシージャサポートの違いは以下のとおりです。
+ Amazon Redshift はサブトランザクションをサポートしていないため、例外処理ブロックに対するサポートは制限されます。

## 考慮事項と制限
<a name="stored-procedure-limits"></a>

Amazon Redshift のストアドプロシージャに対する考慮事項は以下のとおりです。
+ データベースのストアドプロシージャの最大数は 10,000 です。
+ プロシージャのソースコードの最大サイズは 2 MB です。
+ ユーザーセッションで同時に開くことができる明示的および暗黙的なカーソルの最大数は 1 です。SQL ステートメントの結果セットを反復処理する FOR ロープは、暗黙的なカーソルを開きます。ネストされたカーソルはサポートされていません。
+ 明示的および暗黙的なカーソルには、結果セットのサイズについて Amazon Redshift の標準カーソルと同じ制限が適用されます。詳細については、「[カーソルの制約](declare.md#declare-constraints)」を参照してください。
+ ネストされた呼び出しの最大レベル数は 16 です。
+ プロシージャパラメータの最大数は、入力引数の場合は 32、出力引数の場合は 32 です。
+ ストアドプロシージャの変数の最大数は 1,024 です。
+ 独自のトランザクションコンテキストを必要とするすべての SQL コマンドは、ストアドプロシージャ内でサポートされていません。以下に例を示します。
  + PREPARE
  + データベースの作成/削除
  + CREATE EXTERNAL TABLE
  + VACUUM
  + ローカルに設定
  + ALTER TABLE APPEND
+ Java Database Connectivity (JDBC) ドライバー経由の `registerOutParameter` メソッド呼び出しは、`refcursor` データ型ではサポートされていません。データ型の使用例については、「`refcursor`」を参照してください。。[ストアドプロシージャから結果セットを返す](stored-procedure-result-set.md)

# PL/pgSQL 言語リファレンス
<a name="c_pl_pgSQL_reference"></a>

Amazon Redshift のストアドプロシージャは PostgreSQL PL/pgSQL プロシージャ言語に基づいています。ただし、いくつかの重要な違いがあります。このリファレンスでは、Amazon Redshift で実装されている PL/pgSQL 構文の詳細を確認できます。PL/pgSQL の詳細については、PostgreSQL ドキュメントの [PL/pgSQL - SQL 手続き言語](https://www.postgresql.org/docs/8.0/plpgsql.html)を参照してください。

**Topics**
+ [PL/pgSQL リファレンスの規則](c_PL_reference_conventions.md)
+ [PL/pgSQL の構造](c_PLpgSQL-structure.md)
+ [サポートされている PL/pgSQL ステートメント](c_PLpgSQL-statements.md)

# PL/pgSQL リファレンスの規則
<a name="c_PL_reference_conventions"></a>

このセクションでは、PL/pgSQL ストアドプロシージャ言語の構文を記述するために使用する規則を確認できます。

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_PL_reference_conventions.html)

# PL/pgSQL の構造
<a name="c_PLpgSQL-structure"></a>

PL/pgSQL は手続き言語であり、他の手続き言語と同じ構造体が多数含まれています。

**Topics**
+ [ブロック](#r_PLpgSQL-block)
+ [変数宣言](#r_PLpgSQL-variable-declaration)
+ [エイリアス宣言](#r_PLpgSQL-alias-declaration)
+ [組み込み変数](#r_PLpgSQL-builtin-variables)
+ [レコード型](#r_PLpgSQL-record-type)

## ブロック
<a name="r_PLpgSQL-block"></a>

PL/pgSQL はブロック構造言語です。プロシージャの完全な本文は、ブロック内で定義されます。ブロックには、変数宣言と PL/pgSQL ステートメントが含まれます。ステートメントは、ネストされたブロックまたはサブブロックである場合もあります。

宣言とステートメントはセミコロンで終了します。ブロックまたはサブブロックの END キーワードの後にセミコロンを続けます。DECLARE キーワードと BEGIN キーワードの後にセミコロンを使用しないでください。

すべてのキーワードと識別子は大文字と小文字を混用して記述できます。識別子は、二重引用符で囲まれていない場合、暗黙で小文字に変換されます。

二重ハイフン (--) は、行末までのコメントを開始します。/\$1 は、次の \$1/ が発生するまでのブロックコメントを開始します。ブロックコメントはネストできません。ただし、二重ハイフンコメントはブロックコメントで囲むことができます。また、二重ハイフンはブロックコメントの区切り記号 /\$1 および \$1/ を隠すことができます。

ブロックのステートメントセクションのいずれのステートメントもサブブロックにすることができます。サブブロックを使用して論理グループを構成したり、変数を小グループのステートメントにローカライズしたりできます。

```
[ <<label>> ]
[ DECLARE
  declarations ]
BEGIN
  statements
END [ label ];
```

ブロックに先行する宣言セクションで宣言された変数は、ブロックが入力されるたびにデフォルト値に初期化されます。つまり、初期化される回数は関数呼び出しごとに 1 回とは限りません。

例を以下に示します。

```
CREATE PROCEDURE update_value() AS $$
DECLARE
  value integer := 20;
BEGIN
  RAISE NOTICE 'Value here is %', value;  -- Value here is 20
  value := 50;
  --
  -- Create a subblock
  --
  DECLARE
    value integer := 80;
  BEGIN
    RAISE NOTICE 'Value here is %', value;  -- Value here is 80
  END;

  RAISE NOTICE 'Value here is %', value;  -- Value here is 50
END;
$$ LANGUAGE plpgsql;
```

EXIT ステートメントで使用するブロックを識別したり、ブロックで宣言する変数の名前を修飾したりするには、ラベルを使用します。

PL/pgSQL でステートメントをグループ分けする BEGIN/END と、トランザクションコントロール用のデータベースコマンドを混同しないでください。PL/pgSQL の BEGIN および END は単にグループ分けするためのものです。トランザクションを開始または終了することはありません。

## 変数宣言
<a name="r_PLpgSQL-variable-declaration"></a>

ループ変数を例外として、ブロック内のすべての変数をブロックの DECLARE セクションで宣言します。変数は任意の有効な Amazon Redshift データ型を使用できます。サポートされているデータ型については、[データ型](c_Supported_data_types.md) を参照してください。

PL/pgSQL 変数は Amazon Redshift でサポートされている任意のデータ型と、さらに `RECORD` および `refcursor` を使用できます。`RECORD` の詳細については、[レコード型](#r_PLpgSQL-record-type) を参照してください。`refcursor` の詳細については、[カーソル](c_PLpgSQL-statements.md#r_PLpgSQL-cursors) を参照してください。

```
DECLARE
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];
```

変数宣言の例を以下に示します。

```
customerID integer;
numberofitems numeric(6);
link varchar;
onerow RECORD;
```

整数の範囲を反復処理する FOR ループのループ変数は、自動的に整数変数として宣言されます。

DEFAULT 句 (ある場合) は、ブロックの入力時に変数に代入する初期値を指定します。DEFAULT 句がない場合、変数は SQL の NULL 値に初期化されます。CONSTANT オプションは、変数への代入を禁止して、ブロックの存続期間中、変数の値を一定に維持します。NOT NULL を指定すると、NULL 値を代入したときにランタイムエラーが発生します。NOT NULL として宣言されたすべての変数には、NULL 以外のデフォルト値を指定する必要があります。

デフォルト値はブロックを入力するたびに評価されます。例えば、`now()` を `timestamp` 型の変数に代入すると、変数は関数がプリコンパイルされた時刻ではなく、現在の関数呼び出しの時刻になります。

```
quantity INTEGER DEFAULT 32;
url VARCHAR := 'http://mysite.com';
user_id CONSTANT INTEGER := 10;
```

`refcursor` データ型は、ストアドプロシージャ内のカーソル変数のデータ型です。`refcursor` の値はストアドプロシージャ内から返される場合があります。詳細については、「[ストアドプロシージャから結果セットを返す](stored-procedure-result-set.md)」を参照してください

## エイリアス宣言
<a name="r_PLpgSQL-alias-declaration"></a>

ストアドプロシージャの署名から引数名が省略されている場合、その引数のエイリアスを宣言できます。

```
name ALIAS FOR $n;
```

## 組み込み変数
<a name="r_PLpgSQL-builtin-variables"></a>

以下の組み込み変数がサポートされています。
+ FOUND
+ SQLSTATE
+ SQLERRM
+ GET DIAGNOSTICS integer\$1var := ROW\$1COUNT;

FOUND は Boolean 型の特殊な変数です。FOUND は各プロシージャ呼び出しで最初は false に設定されます。FOUND は以下のタイプのステートメントで設定されます。
+ SELECT INTO

  行を返す場合は FOUND を true に設定し、行を返さない場合は false に設定します。
+ UPDATE、INSERT、および DELETE

  少なくとも 1 つの行が影響を受ける場合は FOUND を true に設定し、どの行も影響を受けない場合は false に設定します。
+ FETCH

  行を返す場合は FOUND を true に設定し、行を返さない場合は false に設定します。
+ FOR ステートメント

  FOR ステートメントが 1 回以上反復処理を行う場合は FOUND を true に設定し、それ以外の場合は false に設定します。これは FOR ステートメントの 3 つすべてのバリアント (整数 FOR ループ、レコードセット FOR ループ、動的レコードセット FOR ループ) に該当します。

  FOUND は FOR ループの終了時に設定されます。ループのランタイム内では、FOUND は FOR ステートメントによって変更されません。ただし、ループ本体内の他のステートメントの実行によって変更される場合があります。

例を以下に示します。

```
CREATE TABLE employee(empname varchar);
CREATE OR REPLACE PROCEDURE show_found()
AS  $$
DECLARE
  myrec record;
BEGIN
  SELECT INTO myrec * FROM employee WHERE empname = 'John';
  IF NOT FOUND THEN
    RAISE EXCEPTION 'employee John not found';
  END IF;
END;
$$ LANGUAGE plpgsql;
```

例外ハンドラ内では、特殊な変数 SQLSTATE に、発生した例外に対応するエラーコードが含まれます。特殊な変数 SQLERRM には、例外に関連するエラーメッセージが含まれます。これらの変数は、例外ハンドラー外では未定義であり、使用した場合はエラーが表示されます。

例を以下に示します。

```
CREATE OR REPLACE PROCEDURE sqlstate_sqlerrm() AS
$$
BEGIN
  UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
  EXECUTE 'select invalid';
  EXCEPTION WHEN OTHERS THEN
  RAISE INFO 'error message SQLERRM %', SQLERRM;
  RAISE INFO 'error message SQLSTATE %', SQLSTATE;
END;
$$ LANGUAGE plpgsql;
```

ROW\$1COUNT が GET DIAGNOSTICS コマンドと一緒に使用されています。これは、SQL エンジンに送信された最後の SQL コマンドで処理された行の数を示します。

例を以下に示します。

```
CREATE OR REPLACE PROCEDURE sp_row_count() AS
$$
DECLARE
  integer_var int;
BEGIN
  INSERT INTO tbl_row_count VALUES(1);
  GET DIAGNOSTICS integer_var := ROW_COUNT;
  RAISE INFO 'rows inserted = %', integer_var;
END;
$$ LANGUAGE plpgsql;
```

## レコード型
<a name="r_PLpgSQL-record-type"></a>

RECORD 型は、実際のデータ型ではなく、単なるプレースホルダです。レコード型の変数は、SELECT または FOR コマンドの実行中に割り当てられた行の実際の行構造を取ります。レコード変数のサブ構造は、値が割り当てられるたびに変わる場合があります。レコード変数が最初に割り当てられるまでは、変数にサブ構造はありません。サブ構造のフィールドにアクセスしようとすると、ランタイムエラーが発生します。

```
name RECORD;
```

例を以下に示します。

```
CREATE TABLE tbl_record(a int, b int);
INSERT INTO tbl_record VALUES(1, 2);
CREATE OR REPLACE PROCEDURE record_example()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT a FROM tbl_record
  LOOP
    RAISE INFO 'a = %', rec.a;
  END LOOP;
END;
$$;
```

# サポートされている PL/pgSQL ステートメント
<a name="c_PLpgSQL-statements"></a>

 PL/pgSQL ステートメントは、SQL コマンドをプロシージャ構造体 (ループや条件式など) で強化し、論理フローを制御します。大半の SQL コマンドを使用できます。これには、COPY、UNLOAD、INSERT などのデータ操作言語 (DML) と CREATE TABLE などのデータ定義言語 (DDL) が含まれます。包括的な SQL コマンドのリストについては、「[SQL コマンド](c_SQL_commands.md)」を参照してください。さらに、以下の PL/pgSQL ステートメントが Amazon Redshift でサポートされています。

**Topics**
+ [代入](#r_PLpgSQL-assignment)
+ [SELECT INTO](#r_PLpgSQL-select-into)
+ [no-op](#r_PLpgSQL-no-op)
+ [動的 SQL](#r_PLpgSQL-dynamic-sql)
+ [return](#r_PLpgSQL-return)
+ [条件: IF](#r_PLpgSQL-conditionals-if)
+ [条件: CASE](#r_PLpgSQL-conditionals-case)
+ [loop](#r_PLpgSQL-loops)
+ [カーソル](#r_PLpgSQL-cursors)
+ [RAISE](#r_PLpgSQL-messages-errors)
+ [トランザクションの制御](#r_PLpgSQL-transaction-control)

## 代入
<a name="r_PLpgSQL-assignment"></a>

代入ステートメントは変数に値を代入します。式は単一の値を返す必要があります。

```
identifier := expression;
```

`=` の代わりに、標準ではない `:=` を代入に使用することもできます。

式のデータ型が変数のデータ型に一致しない場合や、変数にサイズや精度が含まれている場合、結果の値は暗黙で変換されます。

次に例を示します。

```
customer_number := 20;
tip := subtotal * 0.15;
```

## SELECT INTO
<a name="r_PLpgSQL-select-into"></a>

SELECT INTO ステートメントは、複数の列 (ただし 1 つの行) の結果をレコード変数、またはスカラー変数のリスト内に割り当てます。

```
SELECT INTO target select_expressions FROM ...;
```

上の構文で、*target* はレコード変数であるか、カンマ区切りのシンプルな変数やレコードフィールドのリストです。*select\$1expressions* リストおよびコマンドの残りは、通常の SQL と同じです。

変数リストを *target* として使用する場合、選択する値はターゲットの構造と厳密に一致する必要があります。そうでないと、ランタイムエラーが発生します。レコード変数がターゲットである場合、レコード変数はクエリの結果列の行タイプに自動的に設定されます。

INTO 句は、SELECT ステートメントのほとんどあらゆる場所で使用できます。通常は、SELECT 句の直後または FROM 句の直前で使用します。つまり、*select\$1expressions* リストの直前または直後に使用します。

クエリが 0 行を返した場合は、 NULL 値が *target* に割り当てられます。クエリが複数の行を返した場合は、最初の行が *target* に割り当てられ、残りは破棄されます。ステートメントに ORDER BY が含まれている場合を除いて、最初の行は決定的ではありません。

割り当てが少なくとも 1 行を返したかどうかを確認するには、特殊な FOUND 変数を使用します。

```
SELECT INTO customer_rec * FROM cust WHERE custname = lname;
IF NOT FOUND THEN
  RAISE EXCEPTION 'employee % not found', lname;
END IF;
```

レコード結果が NULL であるかどうかをテストするには、IS NULL 条件を使用できます。その他の行が廃棄されたかどうかを確認する方法はありません。次の例は、行が 1 つも返されない場合を示しています。

```
CREATE OR REPLACE PROCEDURE select_into_null(return_webpage OUT varchar(256))
AS $$
DECLARE
  customer_rec RECORD;
BEGIN
  SELECT INTO customer_rec * FROM users WHERE user_id=3;
  IF customer_rec.webpage IS NULL THEN
    -- user entered no webpage, return "http://"
    return_webpage = 'http://';
  END IF;
END;
$$ LANGUAGE plpgsql;
```

## no-op
<a name="r_PLpgSQL-no-op"></a>

no-op ステートメント (`NULL;`) は何もしないプレースホルダステートメントです。no-op ステートメントは、IF-THEN-ELSE チェーンの 1 つの分岐が空であることを示す場合があります。

```
NULL;
```

## 動的 SQL
<a name="r_PLpgSQL-dynamic-sql"></a>

PL/pgSQL ストアドプロシージャから実行するたびに関与するテーブルやデータ型が異なる動的コマンドを生成するには、`EXECUTE` ステートメントを使用します。

```
EXECUTE command-string [ INTO target ];
```

上の構文で、*command-string* は実行対象のコマンドを含む文字列 (テキスト型) を生成する式です。この *command-string* の値は SQL エンジンに送信されます。コマンド文字列で PL/pgSQL 変数の代入は行われません。変数の値は、コマンド文字列の構築時に挿入する必要があります。

**注記**  
動的 SQL 内から COMMIT ステートメント、および ROLLBACK ステートメントを使用することはできません。ストアドプロシージャ内での COMMIT ステートメントおよび ROLLBACK ステートメントの使用方法については、[トランザクションの管理](stored-procedure-transaction-management.md) を参照してください。

動的コマンドを使用する場合は、単一引用符のエスケープ処理が必要になることがあります。ドル引用符付けを使用して関数本体の固定テキストを引用符で囲むことをお勧めします。構築されたクエリ内に挿入する動的な値は、それ自体に引用符が含まれている場合があるため、特別な処理が必要になります。次の例では、関数全体のドル引用符付けを行うため、引用符を二重化する必要はありません。

```
EXECUTE 'UPDATE tbl SET '
  || quote_ident(colname)
  || ' = '
  || quote_literal(newvalue)
  || ' WHERE key = '
  || quote_literal(keyvalue);
```

上の例は、関数 `quote_ident(text)` と関数 `quote_literal(text)` を示しています。この例では、列およびテーブルの識別子を含む変数を `quote_ident` 関数に渡します。また、構築されたコマンドにリテラル文字列を含む変数を `quote_literal` 関数に渡します。どちらの関数も適切なステップに従って二重引用符または単一引用符で囲まれた入力テキストをそれぞれ返します。埋め込まれた特殊文字は適切にエスケープされます。

ドル引用符付けは固定テキストの引用符付けに限り有効です。上の例を次の形式で記述しないでください。

```
EXECUTE 'UPDATE tbl SET '
  || quote_ident(colname)
  || ' = $$'
  || newvalue
  || '$$ WHERE key = '
  || quote_literal(keyvalue);
```

これを行うと、`newvalue` の内容に \$1\$1 が含まれていた場合に、この例は破綻します。同じ問題は、他のすべてのドル引用符区切り記号を選択した場合にも該当します。事前に確認できないテキストに対して安全に引用符付けを行うには、`quote_literal` 関数を使用します。

## return
<a name="r_PLpgSQL-return"></a>

RETURN ステートメントは、ストアドプロシージャから呼び出し元に戻ります。

```
RETURN;
```

例を以下に示します。

```
CREATE OR REPLACE PROCEDURE return_example(a int)
AS $$  
BEGIN
  FOR b in 1..10 LOOP
    IF b < a THEN
      RAISE INFO 'b = %', b;
    ELSE
      RETURN;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
```

## 条件: IF
<a name="r_PLpgSQL-conditionals-if"></a>

IF 条件ステートメントは、Amazon Redshift で使用する PL/pgSQL 言語で以下の形式を取ることができます。
+ IF ..。THEN

  ```
  IF boolean-expression THEN
    statements
  END IF;
  ```

  例を以下に示します。

  ```
  IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
  END IF;
  ```
+ IF ..。THEN ..。ELSE

  ```
  IF boolean-expression THEN
    statements
  ELSE
    statements
  END IF;
  ```

  例を以下に示します。

  ```
  IF parentid IS NULL OR parentid = ''
  THEN
    return_name = fullname;
    RETURN;
  ELSE
    return_name = hp_true_filename(parentid) || '/' || fullname;
    RETURN;
  END IF;
  ```
+ IF ..。THEN ..。ELSIF ..。THEN ..。ELSE 

  キーワード ELSIF は ELSEIF と表記される場合もあります。

  ```
  IF boolean-expression THEN
    statements
  [ ELSIF boolean-expression THEN
    statements
  [ ELSIF boolean-expression THEN
    statements
      ...] ]
  [ ELSE
    statements ]
  END IF;
  ```

  例を以下に示します。

  ```
  IF number = 0 THEN
    result := 'zero';
  ELSIF number > 0 THEN
    result := 'positive';
  ELSIF number < 0 THEN
    result := 'negative';
  ELSE
    -- the only other possibility is that number is null
    result := 'NULL';
  END IF;
  ```

## 条件: CASE
<a name="r_PLpgSQL-conditionals-case"></a>

CASE 条件ステートメントは、Amazon Redshift で使用する PL/pgSQL 言語で以下の形式を取ることができます。
+ シンプル CASE 

  ```
  CASE search-expression
  WHEN expression [, expression [ ... ]] THEN
    statements
  [ WHEN expression [, expression [ ... ]] THEN
    statements
    ... ]
  [ ELSE
    statements ]
  END CASE;
  ```

  シンプル CASE ステートメントは、オペランドの等値に基づいて条件実行を提供します。

  *search-expression* の値は、1 回評価され、WHEN 句の *expression* と順次比較されます。一致が見つかると、対応する *statements* が実行されます。次に、END CASE の後に続くステートメントに制御が渡されます。後続の WHEN expressions は評価されません。一致が見つからない場合は、ELSE *statements* が実行されます。ただし、ELSE が存在しない場合は、CASE\$1NOT\$1FOUND 例外が発生します。

  例を以下に示します。

  ```
  CASE x
  WHEN 1, 2 THEN
    msg := 'one or two';
  ELSE
    msg := 'other value than one or two';
  END CASE;
  ```
+ 検索 CASE 

  ```
  CASE
  WHEN boolean-expression THEN
    statements
  [ WHEN boolean-expression THEN
    statements
    ... ]
  [ ELSE
    statements ]
  END CASE;
  ```

  検索形式の CASE は、Boolean 式の真理値に基づいて条件実行を提供します。

  WHEN 句の *boolean-expression* のいずれかが真を生成するまで順に評価されます。次に、対応するステートメントが実行され、その後に END CASE に続くステートメントに制御が渡されます。後続の WHEN *expressions* は評価されません。真の結果が見つからない場合は、ELSE *statements* が実行されます。ただし、ELSE が存在しない場合は、CASE\$1NOT\$1FOUND 例外が発生します。

  例を以下に示します。

  ```
  CASE
  WHEN x BETWEEN 0 AND 10 THEN
    msg := 'value is between zero and ten';
  WHEN x BETWEEN 11 AND 20 THEN
    msg := 'value is between eleven and twenty';
  END CASE;
  ```

## loop
<a name="r_PLpgSQL-loops"></a>

loop ステートメントは、Amazon Redshift で使用する PL/pgSQL 言語で以下の形式を取ることができます。
+ simple loop 

  ```
  [<<label>>]
  LOOP
    statements
  END LOOP [ label ];
  ```

  simple loop は、EXIT または RETURN ステートメントによって終了されるまで無制限に繰り返される無条件ループを定義します。EXIT および CONTINUE ステートメントは、ネストされたループ内でオプションのラベルを使用することで、参照先のループを指定できます。

  例を以下に示します。

  ```
  CREATE OR REPLACE PROCEDURE simple_loop()
  LANGUAGE plpgsql
  AS $$
  BEGIN
    <<simple_while>>
    LOOP
      RAISE INFO 'I am raised once';  
      EXIT simple_while;
      RAISE INFO 'I am not raised';
    END LOOP;
    RAISE INFO 'I am raised once as well';
  END;
  $$;
  ```
+ Exit loop

  ```
  EXIT [ label ] [ WHEN expression ];
  ```

  *label* が存在しない場合は、最も内側のループが終了され、END LOOP に続くステートメントが次に実行されます。*label* が存在する場合、それはネストされたループやブロックの現在のレベルまたは他の外側のレベルのラベルであることが必要です。次に、指名されたループやブロックが終了され、そのループやブロックの対応する END に続くステートメントに制御が移ります。

  WHEN が指定されている場合は、*expression* が真である場合に限り、ループの終了が起こります。それ以外の場合は、EXIT の後に続くステートメントに制御が移ります。

  EXIT はすべてのタイプのループで使用できます。無条件ループでの使用に限定されません。

  BEGIN ブロックと一緒に使用した場合、EXIT はブロックの最後に続くステートメントに制御を渡します。この目的のためにラベルを使用する必要があります。ラベルなしの EXIT は、BEGIN ブロックに一致するとは見なされません。

  例を以下に示します。

  ```
  CREATE OR REPLACE PROCEDURE simple_loop_when(x int)
  LANGUAGE plpgsql
  AS $$
  DECLARE i INTEGER := 0;
  BEGIN
    <<simple_loop_when>>
    LOOP
      RAISE INFO 'i %', i;
      i := i + 1;
      EXIT simple_loop_when WHEN (i >= x);
    END LOOP;
  END;
  $$;
  ```
+ Continue loop 

  ```
  CONTINUE [ label ] [ WHEN expression ];
  ```

  *label* が存在しない場合、実行は最も内側であるループの次の反復処理にジャンプします。つまり、ループ本体に残っているすべてのステートメントはスキップされます。この場合、制御はループ制御式 (ある場合) に戻り、別のループ反復処理が必要であるかどうかが確認されます。*label* が存在する場合は、実行を継続するループのラベルが指定されます。

  WHEN が指定されている場合は、*expression* が真であるときに限り、次のループ反復処理が開始されます。指定されていない場合は、CONTINUE の後に続くステートメントに制御が移ります。

  CONTINUE はすべてのタイプのループで使用できます。無条件ループでの使用に限定されません。

  ```
  CONTINUE mylabel;
  ```
+ WHILE loop 

  ```
  [<<label>>]
  WHILE expression LOOP
    statements
  END LOOP [ label ];
  ```

  WHILE ステートメントは、*boolean-expression* の評価が真である間は、ステートメントのシーケンスを繰り返します。式は、ループ本体に入る直前にチェックされます。

  例を以下に示します。

  ```
  WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- some computations here
  END LOOP;
  
  WHILE NOT done LOOP
    -- some computations here
  END LOOP;
  ```
+ FOR loop (整数バリアント) 

  ```
  [<<label>>]
  FOR name IN [ REVERSE ] expression .. expression LOOP
    statements
  END LOOP [ label ];
  ```

  FOR loop (整数バリアント) は、整数値の範囲を反復処理するループを作成します。変数 name は整数型として自動的に定義され、ループ内にのみ存在します。変数 name の既存の定義はループ内では無視されます。範囲の下限と上限を指定する 2 つの式は、ループに入るときに 1 回評価されます。EVERSE を指定すると、各反復処理後に、ステップ値は加算されずに減算されます。

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

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

  例を以下に示します。

  ```
  FOR i IN 1..10 LOOP
    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
  END LOOP;
  
  FOR i IN REVERSE 10..1 LOOP
    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
  END LOOP;
  ```
+ FOR loop (結果セットバリアント) 

  ```
  [<<label>>]
  FOR target IN query LOOP
    statements
  END LOOP [ label ];
  ```

  *target* はレコード変数であるか、カンマ区切りのスカラー変数のリストです。target には、query の結果である各行が順次代入され、行ごとにループ本体が実行されます。

  FOR loop (結果セットバリアント) を使用すると、ストアドプロシージャはクエリの結果を反復処理し、そのデータを適切に操作できます。

  例を以下に示します。

  ```
  CREATE PROCEDURE cs_refresh_reports() AS $$
  DECLARE
    reports RECORD;
  BEGIN
    FOR reports IN SELECT * FROM cs_reports ORDER BY sort_key LOOP
      -- Now "reports" has one record from cs_reports
      EXECUTE 'INSERT INTO ' || quote_ident(reports.report_name) || ' ' || reports.report_query;
    END LOOP;
    RETURN;
  END;
  $$ LANGUAGE plpgsql;
  ```
+ FOR loop (動的 SQL を使用)

  ```
  [<<label>>]
  FOR record_or_row IN EXECUTE text_expression LOOP 
    statements
  END LOOP;
  ```

  動的 SQL を使用する FOR loop の場合、ストアドプロシージャは動的クエリの結果を反復処理し、そのデータを適切に処理できます。

  例を以下に示します。

  ```
  CREATE OR REPLACE PROCEDURE for_loop_dynamic_sql(x int)
  LANGUAGE plpgsql
  AS $$
  DECLARE
    rec RECORD;
    query text;
  BEGIN
    query := 'SELECT * FROM tbl_dynamic_sql LIMIT ' || x;
    FOR rec IN EXECUTE query
    LOOP
      RAISE INFO 'a %', rec.a;
    END LOOP;
  END;
  $$;
  ```

## カーソル
<a name="r_PLpgSQL-cursors"></a>

クエリ全体を一度に実行せずに、カーソルを設定できます。*カーソル *は、クエリをカプセル化し、クエリの結果を一度に数行ずつ読み取ります。これを行う理由の 1 つは、結果内に多数の行がある場合のメモリの枯渇を防ぐことです。別の理由は、呼び出し元が行を読み取ることができるように、ストアドプロシージャが作成したカーソルへの参照を返すことです。これにより、ストアドプロシージャから大きな行セットを返す際の効率が向上します。

NONATOMIC ストアドプロシージャでカーソルを使用するには、START TRANSACTION...COMMIT の間にカーソルループを置きます。

カーソルを設定するには、まずカーソル変数を宣言します。PL/pgSQL では、カーソルへのすべてのアクセスがカーソル変数を経由します。カーソル変数は常に特殊な `refcursor` データ型です。`refcursor` データ型は単にカーソルへの参照を保持します。

カーソル変数を作成するには、`refcursor` 型の変数としてカーソルを宣言します。または、次に示すカーソル宣言構文を使用することもできます。

```
name CURSOR [ ( arguments ) ] FOR query ;
```

上の構文で、*arguments* (指定されている場合) はカンマ区切りの *name datatype* ペアのリストです。各ペアは、*query* のパラメータ値で置き換えられる名前を定義します。これらの名前と置き換わる実際の値は、後で、カーソルを開いたときに指定されます。

次に例を示します。

```
DECLARE
  curs1 refcursor;
  curs2 CURSOR FOR SELECT * FROM tenk1;
  curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
```

これら 3 つすべての変数は `refcursor` データ型を持ちますが、最初の変数はすべてのクエリで使用できます。一方、2 番目の変数には完全に指定されたクエリがバインドされており、3 番目の変数にはパラメータ化クエリがバインドされています。`key` 値は、カーソルを開いたときに、整数パラメータ値に置き換えられます。変数 `curs1` は、特定のクエリにバインドされていないため、*非バインド*と呼ばれます。

カーソルを使用して行を取得する前に、カーソルを開く必要があります。PL/pgSQL には 3 つの形式の OPEN ステートメントがあります。そのうちの 2 つでは、非バインドカーソル変数を使用し、残りの 1 つではバインドカーソル変数を使用します。
+ Open for select: カーソル変数を開き、これに対して実行するクエリを指示します。すでに開いているカーソルを開くことはできません。また、カーソルは非バインドカーソル (つまり、シンプルな `refcursor` 変数) として宣言済みであることが必要です。SELECT クエリは、PL/pgSQL の他の SELECT ステートメントと同じ方法で扱われます。

  ```
  OPEN cursor_name FOR SELECT ...;                     
  ```

  例を以下に示します。

  ```
  OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;    
  ```
+ Open for execute: カーソル変数を開き、これに対して実行するクエリを指示します。すでに開いているカーソルを開くことはできません。また、カーソルは非バインドカーソル (つまり、シンプルな `refcursor` 変数) として宣言済みであることが必要です。クエリは、EXECUTE コマンドと同じ方法で、文字列式として指定されます。これに伴う柔軟性により、今回実行したものとは異なるクエリを次回に実行できます。

  ```
  OPEN cursor_name FOR EXECUTE query_string;
  ```

  例を以下に示します。

  ```
  OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
  ```
+ Open a bound cursor: この形式の OPEN では、宣言時にクエリがバインド済みであるカーソル変数を開きます。すでに開いているカーソルを開くことはできません。実際の引数値式が表示されるのは、カーソルが引数を取るように宣言済みである場合に限る必要があります。これらの値はクエリ内で置き換えられます。

  ```
  OPEN bound_cursor_name [ ( argument_values ) ];
  ```

  例を以下に示します。

  ```
  OPEN curs2;
  OPEN curs3(42);
  ```

カーソルが開いたら、以下に説明するステートメントを使用してカーソルを操作できます。これらのステートメントは、カーソルを開いた同じストアドプロシージャで使用する必要はありません。ストアドプロシージャから `refcursor` 値を返して、呼び出し元でカーソルを操作できます。すべてのポータルは、トランザクションの終わりに暗黙で閉じられます。したがって、`refcursor` 値を使用して開いているカーソルを参照できるのは、トランザクションの終わりまでです。
+ FETCH は、カーソルから次に続く行を取得して target に格納します。この target は、SELECT INTO の場合と同じように、行変数、レコード変数、カンマ区切りのシンプルな変数のリストのいずれかです。SELECT INTO の場合と同様に、特殊な FOUND 変数をチェックして、行が取得されたかどうかを確認できます。

  ```
  FETCH cursor INTO target;
  ```

  例を以下に示します。

  ```
  FETCH curs1 INTO rowvar;
  ```
+ CLOSE は、開いているカーソルの基になっているポータルを閉じます。このステートメントを使用してトランザクションの終了より前にリソースを解放できます。また、このステートメントを使用してカーソル変数を解放し、再度開くこともできます。

  ```
  CLOSE cursor;
  ```

  例を以下に示します。

  ```
  CLOSE curs1;
  ```

## RAISE
<a name="r_PLpgSQL-messages-errors"></a>

`RAISE level` ステートメントを使用して、メッセージを報告してエラーをレイズします。

```
RAISE level 'format' [, variable [, ...]];
```

使用可能なレベルは、NOTICE、INFO、LOG、WARNING、および EXCEPTION です。EXCEPTION はエラーを発生させ、このエラーは通常、現行のトランザクションをキャンセルします。その他のレベルでは、さまざまな優先度レベルのメッセージのみを生成します。

format 文字列内の % は、次の省略可能な引数の文字列表現に置き換えられます。リテラル % を表すには「%%」と記述します。現在、省略可能な引数はシンプルな変数にする必要があり (式ではなく)、format はシンプルな文字列リテラルにする必要があります。

次の例では、文字列内の % が `v_job_id` の値に置き換えられます。

```
RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
```

`RAISE` ステートメントを使用して、例外処理ブロックでキャッチされた例外を再スローします。このステートメントは、NONATOMIC モードのストアドプロシージャの例外処理ブロックでのみ有効です。

```
RAISE;
```

## トランザクションの制御
<a name="r_PLpgSQL-transaction-control"></a>

Amazon Redshift で使用する PL/pgSQL 言語のトランザクション制御ステートメントを使用できます。ストアドプロシージャ内での COMMIT、ROLLBACK、TRUNCATE ステートメントの使用方法については、[トランザクションの管理](stored-procedure-transaction-management.md) を参照してください。

NONATOMIC モードのストアドプロシージャでは、`START TRANSACTION` を使用してトランザクションブロックを開始します。

```
START TRANSACTION;
```

**注記**  
PL/pgSQL ステートメントの START TRANSACTION は、SQL コマンドの START TRANSACTION と次の点で異なります。  
ストアドプロシージャ内では、START TRANSACTION は BEGIN と同義ではありません。
PL/pgSQL ステートメントは、オプションの分離レベルとアクセス許可キーワードをサポートしていません。