Day 26 我們認識了自訂函數(Function)、Day 27 我們認識了事件觸發 (Trigger)，接下來要來認識一個跟函數蠻像的東西，預存程序 (Stored Procedure)。

什麼是預存程序?

預存程序的內容是執行一連串SQL操作與邏輯判斷，聽起來跟函數超級像，事實上在PostgreSQL11之前預存程序就是某種特殊規則的函數，直到PostgreSQL11之後才獨立出來，那麼跟函數不一定在哪裡呢?我們來認識以下差異。

1. 自訂函數無法執行交易，也就是說無法commit or rollback，預存程序可以執行交易(Transaction)
2. 與自訂函數不同，預存程序不返回任何值。

舊版宣告方法

PostgreSQL11之前使用Function來創建Stored Procedure。

要宣告Stored Procedure，需要把回傳值設定為void，因為Stored Procedure的一條規則是不回傳任何內容。

CREATE FUNCTION sp_close_accounts() RETURNS void
LANGUAGE SQL
AS $$
  UPDATE accounts SET active = false;
$$;

然後使用 SELECT 呼叫 Stored Procedure

SELECT sp_close_accounts();

新版宣告方法

PostgreSQL11之後，可以更改為宣告PROCEDURE。

CREATE PROCEDURE sp_close_accounts() 
LANGUAGE SQL
AS $$
  UPDATE accounts SET active = false;
$$;

執行的方式改為CALL。

CALL sp_close_accounts();

預存程序範例

我們有兩張資料表，一張是products table，有商品資訊及庫存，另一張是銷售sales table，包含了商品代碼與費用等等的銷售資訊，這個預存程序要做的事情是銷售一筆資料就自動更新庫存，程式會包含三個步驟:

1. 取得商品代碼與價格
2. 寫入銷售
3. 更新庫存

CREATE OR REPLACE PROCEDURE sp_buy_products()
LANGUAGE plpgsql
AS $$
DECLARE
	-- 使用v_...來做為變數命名方式 (方便識別)
	v_product_code VARCHAR(20);
	v_price FLOAT;
BEGIN
	-- 取得商品代碼與價格
	SELECT product_code, price
	INTO v_product_code, v_price
	FROM products
	WHERE product_name = 'Google Pixel 7 Pro';

	-- 寫入銷售
	INSERT INTO sales(order_date, product_code, quantity_ordered, sale_price)
	VALUES (current_date, v_product_code, 1, (v_price * 1));

	-- 更新庫存
	UPDATE products
	SET quantity_remaining = (quantity_remaining -1),
		  quantity_sold = (quantity_sold +1)
	WHERE product_code = v_prodict_code;

	RAISE NOTICE '銷售成功';
END;
$$

帶有參數的預存程序範例

在範例中已經稍微體會了預存程序的妙處了，不過並不太靈活，因為銷售量是固定的，使用參數來讓整個預存程序更加靈活，同時這個範例會再加上一些對於庫存的判斷。

-- 傳入兩個參數，1.商品名稱 2.銷售數量
CREATE OR REPLACE PROCEDURE prBuyProducts(IN p_product_name VARCHAR, IN p_quantity INT)
LANGUAGE plpgsql
AS $$
DECLARE
	v_product_code VARCHAR(20);
	v_price        FLOAT;
	v_cnt          INT;
BEGIN
	-- 判斷庫存是否足夠這次的銷售量
	SELECT COUNT(1)
	INTO v_cnt
	FROM products
	WHERE product_name = p_product_name
  AND quantity_remaining >= p_quantity;

	-- 判斷庫存是否足夠銷售
	IF v_cnt > 0 THEN

		SELECT product_code, price
		INTO v_product_code, v_price
		FROM products
		WHERE product_name = 'Google Pixel 7 Pro';
	
		-- 現在銷售總額會用商品價格x數量了
		INSERT INTO sales(order_date, product_code, quantity_ordered, sale_price)
		VALUES (current_date, v_product_code, p_quantity, (v_price * p_quantity));
	
		-- 更新庫存也可以扣掉所傳入的參數量
		UPDATE products
		SET quantity_remaining = (quantity_remaining - p_quantity),
			  quantity_sold = (quantity_sold + p_quantity)
		WHERE product_code = v_prodict_code;
	
		RAISE NOTICE '成功銷售';

	ELSE

		RAISE NOTICE '數量不足';

	END IF;
END;
$$

使用交易的預存程序

預存程序與自訂函數最大的差異之一是預存程序可以使用交易而自訂函數不行。

CREATE OR REPLACE PROCEDURE prBuyProducts(IN p_product_name VARCHAR, IN p_quantity INT)
LANGUAGE plpgsql
AS $$
DECLARE
	v_product_code VARCHAR(20);
	v_price        FLOAT;
	v_cnt          INT;
BEGIN
	SELECT COUNT(1)
	INTO v_cnt
	FROM products
	WHERE product_name = p_product_name
  AND quantity_remaining >= p_quantity;

	IF v_cnt > 0 THEN

		SELECT product_code, price
		INTO v_product_code, v_price
		FROM products
		WHERE product_name = 'Google Pixel 7 Pro';
	
		INSERT INTO sales(order_date, product_code, quantity_ordered, sale_price)
		VALUES (current_date, v_product_code, p_quantity, (v_price * p_quantity));

		UPDATE products
		SET quantity_remaining = (quantity_remaining - p_quantity),
			  quantity_sold = (quantity_sold + p_quantity)
		WHERE product_code = v_prodict_code;
	
		RAISE NOTICE '成功銷售';
		
		-- 交易
		COMMIT;
		EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;

	ELSE

		RAISE NOTICE '數量不足';

	END IF;
END;
$$