多個 savepoint
在市場動盪時，銀行的一個高風險面向，是近期即將到期的定期存款數量。存款人很可能會把到期的定存領出，轉作其他投資。RCONHK07 + RCONHK12 儲存未來 3 個月內到期的金額，RCONHK08 + RCONHK13 則是 3 到 12 個月內到期的金額。
如果這些欄位的總額高於 $10 million，可動用資金將被拉扯去應付提領，會被評為負面；另外，若低於 $2 million，則被視為正面因素。
本練習屬於課程
PostgreSQL 的交易與錯誤處理
練習說明
- 在一個交易中，當總到期存款少於 $2 million 時，將
FIELD48設為mature+，並建立 savepointmatureplus_flag_set。 - 當總到期存款介於 $2 million 與 $10 million 之間時，將
FIELD48設為mature-，然後建立 savepointmatureminus_flag_set。 - 當總到期存款大於 $10 million 時，將
FIELD48設為mature--。 - 統計所有具有正向存款到期結構的銀行數量。
動手互動練習
試著完成這個範例程式碼，體驗一下這個練習。
BEGIN;
-- Update FIELD48 to indicate a positive maturity rathing when less than $2 million of maturing deposits.
UPDATE ffiec_reci
SET ___ = '___'
WHERE RCONHK07 + RCONHK12 + RCONHK08 + RCONHK13 < 2000000;
-- Set a savepoint
SAVEPOINT ___;
-- Update FIELD48 to indicate a negative maturity rathing when between $2 and $10 million
UPDATE ffiec_reci
SET ___ = '___'
WHERE RCONHK07 + RCONHK12 + RCONHK08 + RCONHK13 BETWEEN 2000000 AND 10000000;
-- Set a savepoint
SAVEPOINT ___;
-- Update FIELD48 to indicate a double negative maturity rathing when more than $10 million
UPDATE ffiec_reci
SET ___ = '___'
WHERE RCONHK07 + RCONHK12 + RCONHK08 + RCONHK13 > ___;
COMMIT;
-- Count the records where FIELD48 is a positive indicator
SELECT count(FIELD48)
FROM ffiec_reci
WHERE FIELD48 = 'mature+';