Amazon Redshift 將不再支援從修補程式 198 開始建立新的 Python UDFs。現有 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 陳述式集合。

您可以使用 PostgreSQL 程序性語言 PL/pgSQL 來定義 Amazon Redshift 預存程序，以執行一組 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 陳述式的結尾，會將陳述式傳送至 Amazon Redshift 伺服器進行處理。以下是範例。  

```
CREATE OR REPLACE PROCEDURE test()
AS $$
BEGIN
  SELECT 1 a;
END;
$$
LANGUAGE plpgsql
;
/
```
您可以使用支援剖析 CREATE PROCEDURE 陳述式的用戶端，例如 [Amazon Redshift 主控台中的查詢編輯器](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html)或 TablePlus。

**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 不會啟用根據輸出引數的程序多載。您不能有兩個程序是具有相同的名稱和輸入引數資料類型，但卻有不同的輸出引數類型。

擁有者或超級使用者可以將預存程序的主體換成具有相同簽章的預存程序。若要變更預存程序的簽章或傳回類型，請捨棄預存程序再重新建立。如需詳細資訊，請參閱[DROP PROCEDURE](r_DROP_PROCEDURE.md)及[CREATE PROCEDURE](r_CREATE_PROCEDURE.md)。

在實作預存程序之前，您可以考量命名慣例，以避免潛在的衝突和非預期的結果。因為您可以多載程序名稱，它們可能會與現有和未來的 Amazon Redshift 程序名稱相衝突。

## 多載程序名稱
<a name="stored-procedure-overloading-name"></a>

程序是以其名稱和簽章來識別，而簽章是輸入引數的數目和引數的資料類型。如果相同結構描述中的兩個程序具有不同的簽章，則它們可以具有相同的名稱。換言之，您可以過載程序名稱。

當您執行程序時，查詢引擎會根據您提供的引數數目和引數的資料類型，以決定要呼叫哪個程序。您可以使用過載來模擬具有可變引數數目的程序，而此數目最多可為 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`，以確保從 `admin` 結構描述存取 `user_creds` 資料表，而不是從 public 或發起人的 `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 使用 `refcursor` 資料類型呼叫 `get_result_set` 預存程序。常值 `'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)。

下列範例示範從明確交易區塊內呼叫預存程序時的交易行為。從預存程序外發出的兩個 insert 陳述式和從它之內發出的一個 insert 陳述式，都是相同交易 (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
```

相對的，假設有同樣的陳述式從明確交易區塊外發出，而且該工作階段的自動遞交設定為 ON。在這種情況下，每個陳述式都會在自己的交易內運作。

```
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
```

下列範例會在插入 `test_table_a` 後發出 TRUNCATE 陳述式。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
```

下列範例顯示游標 `cur1` 已在 TRUNCATE 陳述式遞交時關閉。

```
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` 預存程序來執行此動作。該範例會出現下列操作：
+ user1 建立表格 `test_tbl`。
+ user1 建立預存程序 `sp_truncate_test_tbl`。
+ user1 授予預存程序的 `EXECUTE` 權限給 user2。
+ user2 執行預存程序以截斷表格 `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 兩次。第一個 COMMIT 會遞交 10363 交易中完成的所有工作，並隱含地啟動 10364 交易。第二個 COMMIT 陳述式則會遞交 10364 交易。

```
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>

在 NONATIC 模式中建立的預存程序與以預設模式建立的程序有不同的交易控制行為。與預存程序外部 SQL 命令的自動遞交行為類似，NONATOMIC 程序內的每個 SQL 陳述式都會在自己的交易中執行，並自動遞交。如果使用者在 NONATOMIC 預存程序中開始明確交易區塊，則區塊內的 SQL 陳述式不會自動確認。交易區塊控制項遞交或復原其中的陳述式。

在 NONATOMIC 預存程序中，您可以使用 START TRANSACTION 陳述式在程序內開啟明確的交易區塊。不過，如果已經有開啟的交易區塊，這個陳述式就不會執行任何動作，因為 Amazon Redshift 不支援子交易。上一筆交易會繼續進行。

當您在 NONATOMIC 程序內使用游標 FOR 迴圈時，請確定您在迭代查詢結果之前開啟明確的交易區塊。否則，當迴圈內的 SQL 陳述式自動遞交時，游標會關閉。

使用 NONATOMIC 模式行為時的一些注意事項如下：
+ 如果沒有開啟的交易區塊，且工作階段已自動遞交預存程序中的每個 SQL 陳述式設定為 ON。
+ 如果從交易區塊內呼叫預存程序，您可以發出 COMMIT/ROLLBACK/TRUNCATE 陳述式來結束交易。這在預設模式下是不可能的。
+ 您可以發出 START TRANSACTION 陳述式，以便在預存程序中開始交易區塊。

下列範例會示範使用 NONATOMIC 預存程序時的交易行為。下列所有範例的工作階段都將自動遞交設定為 ON。

在下列範例中，NONATOMIC 預存程序會有兩個 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)
```

在這個範例中，兩個 INSERT 陳述式在 START TRANSACTION...COMMIT 之間。當程序在交易區塊之外呼叫時，兩個 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)
```

當從 BEGIN...COMMIT 區塊內呼叫程序時，程序內的 START 交易不會執行任何動作，因為已經有開啟的交易。程序中的 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 資料表具有三個值。我們的目標是迭代這三個值，並將它們插入到資料表 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
```

若要讓游標迴圈運作，請將其放在 START TRANSACTION...COMMIT 之間。

```
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;
```

當發生例外狀況，而您新增例外狀況處理區塊時，您可以撰寫 RAISE 陳述式和大多數其他 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)
```

此範例示範如何使用兩個子區塊建立程序。呼叫預存程序時，第一個子區塊的錯誤會由其例外狀況處理區塊處理。在第一個子區塊完成之後，程序會繼續執行第二個子區塊。從結果可以看到，程序呼叫完成時沒有擲回任何錯誤。資料表 employee 上的 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 例外狀況處理常式。此範例會建立兩份資料表，並在預存程序中使用資料表。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;`，它會傳回兩個記錄。這些包含值 `1` 和 `2`。
+ 如果執行 `SELECT * FROM tbl_error_logging;`，它會傳回一個包含下列值的記錄：*發生的錯誤*、*42703* 和 *tbl\$11 中不存在資料欄 "val"*。

下列其他錯誤處理範例會同時使用 EXIT 處理常式和 CONTINUE 處理常式。此範例建立兩個表：一個資料表和一個記錄表。另外還建立一個展示如何處理錯誤的預存程序：

```
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。
+ 如果執行 `SELECT * FROM tbl_error_logging;`，它會傳回兩個記錄。它們具有以下值：*發生的錯誤*、*42703* 和 *tbl\$11 中不存在資料欄 "val"*。

如果表 **tbl\$1error\$1logging** 不存在，則會引發例外狀況。

下列範例展示如何使用 CONTINUE 例外狀況處理常式與 FOR 迴圈。此範例會建立三個資料表，並在預存程序內的 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;`，它會傳回兩個記錄。這些包含值 1 和 3。
+ 如果執行 `SELECT * FROM tbl_error_logging;`，它會傳回一個包含下列值的記錄：*發生的錯誤*、*42703* 和 *tbl\$12 中不存在資料欄 "val"*。

有關 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。
+ 您在使用者工作階段中可同時開啟的明確和隱含游標數目上限是一個。在 SQL 陳述式的結果集上反覆運算的 FOR 迴圈會開啟隱含游標。不支援巢狀游標。
+ 明確和隱含游標的結果集大小限制，與標準 Amazon Redshift 游標相同。如需詳細資訊，請參閱[游標限制條件](declare.md#declare-constraints)。
+ 巢狀呼叫層數上限為 16。
+ 輸入引數的程序參數數目上限為 32，輸出引數的上限也是 32。
+ 預存程序中的變數數目上限為 1,024。
+ 預存程序內不支援需要有自己的交易範圍的任何 SQL 命令。範例包括：
  + PREPARE
  + CREATE/DROP DATABASE
  + CREATE EXTERNAL TABLE
  + VACUUM
  + SET LOCAL
  + ALTER TABLE APPEND
+ 對於 `refcursor` 資料類型，不支援透過 Java 資料庫連線 (JDBC) 驅動程式發出的 `registerOutParameter` 方法呼叫。關於使用 `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/zh_tw/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 ];
```

在區塊前面的宣告區段中所宣告的變數，在每次進入區塊時會初始化為其預設值。換言之，在每次函數呼叫時不只初始化一次。

下列顯示一個範例。

```
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，則指派空值會導致執行時間錯誤。所有宣告為 NOT 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 是布林值類型的特殊變數。在每個程序呼叫內，FOUND 剛開始為 false。FOUND 由下列類型的陳述式來設定：
+ SELECT INTO

  將 FOUND 設為 true 表示傳回一列，設為 false 表示未傳回任何列。
+ UPDATE、INSERT 和 DELETE

  將 FOUND 設為 true 表示至少一列受影響，設為 false 表示不影響任何列。
+ FETCH

  將 FOUND 設為 true 表示傳回一列，設為 false 表示未傳回任何列。
+ FOR 陳述式

  將 FOUND 設為 true 表示 FOR 陳述式反覆運算一或多次，否則為設為 false。這適用於 FOR 陳述式的所有三種變體：整數 FOR 迴圈、記錄集 FOR 迴圈，以及動態記錄集 FOR 迴圈。

  在 FOR 迴圈結束時設定 FOUND。在迴圈執行期內，FOR 陳述式不會修改 FOUND。不過，迴圈主體內的其他陳述式執行可能變更它。

下列顯示一個範例。

```
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 命令，包括資料處理語言 (DML) (例如 COPY、UNLOAD 和 INSERT) 和資料定義語言 (DDL) (例如 CREATE TABLE)。如需完整 SQL 命令的清單，請參閱 [SQL 命令](c_SQL_commands.md)。此外，Amazon Redshift 還支援下列 PL/pgSQL 陳述式。

**Topics**
+ [指派](#r_PLpgSQL-assignment)
+ [SELECT INTO](#r_PLpgSQL-select-into)
+ [無操作](#r_PLpgSQL-no-op)
+ [動態 SQL](#r_PLpgSQL-dynamic-sql)
+ [傳回](#r_PLpgSQL-return)
+ [條件式：IF](#r_PLpgSQL-conditionals-if)
+ [條件式：CASE](#r_PLpgSQL-conditionals-case)
+ [迴圈](#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 陳述式將多欄 (但只有一列) 的結果指派給一個記錄變數或一個純量變數清單。

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

在上述語法中，*target* 可以是記錄變數，或簡單變數和記錄欄位的逗號分隔清單。*select\$1expressions* 清單和命令的剩餘部分與一般 SQL 中相同。

如果以變數清單作為 *target*，選取的值必須完全符合目標的結構，否則會發生執行時間錯誤。當記錄變數為目標時，它本身會自動設定為查詢結果欄的列類型。

INTO 子句幾乎可出現在 SELECT 陳述式中的任意處。通常就出現在 SELECT 子句之後，或就在 FROM 子句之前。亦即，就出現在 *select\$1expressions* 清單之前或之後。

如果查詢未傳回任何列，NULL 值會指派給 *target*。如果查詢傳回多列，第一列會指派給 *target*，其餘捨棄。除非陳述式包含 ORDER BY，否則無法確定第一列。

若要判斷指派是否傳回至少一列，請使用特殊的 FOUND 變數。

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

若要測試記錄結果是否為空值，您可以使用 IS NULL 條件。無法判斷是否已捨棄任何其他列。下列範例處理未傳回任何列的情況。

```
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;
```

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

無操作陳述式 (`NULL;`) 是不執行任何動作的預留位置陳述式。無操作陳述式可以表示 IF-THEN-ELSE 鏈的一個分支是空的。

```
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)。

使用動態命令時，您通常需要處理單引號逸出。建議使用 \$1 符號引用來圍住函數主體中放在引號內的固定字串。在建構的查詢中要插入的動態值需要特別處理，因為動態值本身可能包含引號。下列範例對整個函數採用 \$1 符號引用，因此引號不需要加倍。

```
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` 函數。這兩個函數都採取適當步驟，傳回分別以雙引號或單引號括住的輸入文字，並適當地逸出任何內嵌的特殊字元。

\$1 符號引用僅適用於括住固定文字。請勿將上述範例寫成下列格式。

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

不能這樣做，因為如果 `newvalue` 的內容碰巧包含 \$1\$1，則範例會失敗。您可能選擇的任何其他 \$1 符號引用分隔符號也有相同問題。若要安心地括住無法事先得知的文字，請使用 `quote_literal` 函數。

## 傳回
<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>

在 Amazon Redshift 所使用的 PL/pgSQL 語言中，IF 條件式陳述式有下列形式：
+ 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>

在 Amazon Redshift 所使用的 PL/pgSQL 語言中，CASE 條件式陳述式有下列形式：
+ 簡單 CASE 

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

  簡單 CASE 陳述式可根據運算元等式而有條件地執行。

  *search-expression* 值會評估一次，然後連續地與 WHEN 子句中的每個 *expression* 相比較。如果發現相符，則對應的 *statements* 會執行，接著控制權就移轉到 END CASE 之後的下一個陳述式。不會評估後續的 WHEN 表達式。如果找不到相符，則 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 可根據布林值表達式的真實性而有條件地執行。

  每個 WHEN 子句的 *boolean-expression* 會依次評估，直到發現產生 true 為止。然後對應的陳述式會執行，接著控制權就移轉到 END CASE 之後的下一個陳述式。不會評估後續的 WHEN *expressions*。如果找不到 true 結果，則 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;
  ```

## 迴圈
<a name="r_PLpgSQL-loops"></a>

在 Amazon Redshift 所使用的 PL/pgSQL 語言中，迴圈陳述式有下列形式：
+ 簡單迴圈 

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

  簡單迴圈定義無條件的迴圈，將會無限期重複，直到由 EXIT 或 RETURN 陳述式終止為止。巢狀迴圈內的 EXIT 和 CONTINUE 陳述式可使用選用標籤，以指定 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 [ label ] [ WHEN expression ];
  ```

  如果 *label* 不存在，最內層迴圈會終止，END LOOP 之後的陳述式會接下去執行。如果 *label* 存在，它必須是巢狀迴圈或區塊的目前或某個外層的標籤。然後，具名迴圈或區塊會終止，控制權會延續到迴圈或區塊相對應 END 之後的陳述式。

  如果指定 WHEN，只有在 *expression* 為 true 時，迴圈才會結束。否則，控制權會移轉到 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 [ label ] [ WHEN expression ];
  ```

  如果未提供 *label*，執行會跳到最內層迴圈的下一次反覆運算。亦即會略過迴圈主體中剩餘的所有陳述式。然後，控制權會返回到迴圈控制表達式 (如果有的話)，以決定是否需要再一次迴圈反覆運算。如果 *label* 存在，它指定繼續執行的迴圈的標籤。

  如果指定 WHEN，只有在 *expression* 為 true 時，迴圈的下一次反覆運算才會開始。否則，控制權會移轉到 CONTINUE 之後的陳述式。

  您可以對所有類型的迴圈使用 CONTINUE；不限於用於無條件的迴圈。

  ```
  CONTINUE mylabel;
  ```
+ WHILE 迴圈 

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

  只要 *boolean-expression* 評估為 true，WHILE 陳述式會重複一連串陳述式。就在進入迴圈主體之前會檢查表達式。

  下列顯示一個範例。

  ```
  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 迴圈 (整數變體) 

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

  FOR 迴圈 (整數變數) 建立迴圈對整數值範圍反覆運算。變數名稱會自動定義為整數類型，並且只在迴圈內結束。迴圈內會忽略變數名稱的任何現有定義。定義範圍下限和上限的兩個表達式會在進入迴圈時評估一次。如果您指定 REVERSE，則每一次反覆運算後會減去間距值，而不是相加。

  如果下限大於上限 (或在 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 迴圈 (結果集變體) 

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

  *target* 是記錄變數，或純量變數的逗號分隔清單。從查詢產生的每一列會連續指派給目標，而每一列會執行一次迴圈主體。

  FOR 迴圈 (結果集變體) 可讓預存程序逐一查看查詢的結果，並相應地操作該資料。

  下列顯示一個範例。

  ```
  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 迴圈搭配動態 SQL

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

  FOR 迴圈搭配動態 SQL 可讓預存程序逐一查看動態查詢的結果，並相應地操作該資料。

  下列顯示一個範例。

  ```
  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>

您可以設定游標，而不要一次執行整個查詢。*cursor* 封裝查詢，每次在查詢結果中讀取幾列。這麼做的一個理由是當結果包含大量的列時，避免記憶體溢位。另一個理由是為了傳回預存程序已建立之游標的參考，此游標可讓發起人讀取列。此方法可以很有效率地從預存程序傳回很大的列集。

若要在 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;
```

這三個變數全部都是資料類型 `refcursor`，但第一個可用於任何查詢。相反地，第二個已繫結完整指定的查詢，而最後一個已繫結參數化查詢。開啟游標時，`key` 值會換成整數參數值。變數 `curs1` 可說成*未繫結*，因為沒有繫結至任何特定查詢。

游標必須先開始，才能用來擷取列。PL/pgSQL 有三種形式的 OPEN 陳述式，其中兩種使用未繫結的游標變數，第三種使用繫結的游標變數：
+ 開啟以選取：開啟游標變數，並給予要執行的指定查詢。游標不能已開啟。另外，還必須已宣告為未繫結的游標 (亦即，宣告為簡單的 `refcursor` 變數)。SELECT 查詢的處理方式與 PL/pgSQL 中的其他 SELECT 陳述式相同。

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

  下列顯示一個範例。

  ```
  OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;    
  ```
+ 開啟以執行：開啟游標變數，並給予要執行的指定查詢。游標不能已開啟。另外，還必須已宣告為未繫結的游標 (亦即，宣告為簡單的 `refcursor` 變數)。將查詢指定為字串表達式的方式與 EXECUTE 命令中相同。此方法很有彈性，可讓查詢隨著每一次執行而變化。

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

  下列顯示一個範例。

  ```
  OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
  ```
+ 開啟繫結的游標：這種 OPEN 用於開啟已在宣告時繫結查詢的游標變數。游標不能已開啟。僅當游標宣告為接受引數時，實際引數值表達式的清單才必須出現。查詢中會替換這些值。

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

  下列顯示一個範例。

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

開啟游標後，您可以利用下述的陳述式來使用它。這些陳述式不一定要出現在開啟游標的同一預存程序中。您可以從預存程序傳回 `refcursor` 值，並讓發起人繼續操作游標。交易結束時，所有入口會隱含關閉。因此，只有在交易結束後，您才可以使用 `refcursor` 值來參考開啟的游標。
+ FETCH 從游標中將下一列擷取到目標。此目標可以是列變數、記錄變數，或簡單變數的逗號分隔清單，如同 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 會引發錯誤，通常會取消目前的交易。其他等級只產生不同優先等級的訊息。

在格式字串內，% 會換成下一個選用引數的字串表示法。撰寫 %% 以發出常值 %。目前，選用引數必須是簡單變數，不是表達式，而格式必須是簡單字串常值。

在下列範例中，`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 陳述式不支援選擇性的隔離層級和存取許可關鍵字。