本範例主要是紀錄如何在Procedure中使用變數暫存查詢的結果，並以該變數做為另一查詢的條件。
例如有兩個資料表分別為customer及order，關係為一對多，也就是一個客人(customer)有多筆訂單(order)記錄。
customer資料表的主鍵(PK)為customer_id，並有另一email欄位是unique的。
order資料表的主鍵(PK)為order_id，並以外鍵(FK)customer_id與customer資料表關聯。
在Package中撰寫一支Procedure，以i_customer_email來查詢該customer下的所有order記錄。
-- spec
CREATE OR REPLACE PACKAGE customer_pkg AS -- package名稱為customer_pkg
PROCEDURE get_order_by_email(
i_customer_email IN VARCHAR2,
o_order OUT SYS_REFCURSOR); -- procedure名稱為get_order_by_email
END customer_pkg;
-- body
CREATE OR REPLACE PACKAGE BODY customer_pkg AS
PROCEDURE get_order_by_email (
i_customer_email IN VARCHAR2,
o_order OUT SYS_REFCURSOR) AS
v_customer_id customer.customer_id%TYPE; -- 宣告Procedure的變數
BEGIN
-- 以輸入的email參數做為條件查詢customer資料表的customer_id，並將結果存入v_customer_id變數
BEGIN
SELECT customer_id INTO v_customer_id
FROM customer
WHERE email = i_customer_email;
END;
-- 開啟o_order，並為以v_customer_id為條件查詢order資料表的結果
OPEN o_order FOR
SELECT * FROM order
WHERE customer_id = v_customer_id;
END get_order_by_email;
END customer_pkg;
參考：
沒有留言:
張貼留言