網頁

▼

2018/10/2

Oracle PL/SQL 在Package建立一個查詢Procedure，條件為另一查詢的結果

本範例主要是紀錄如何在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;

參考：

沒有留言:

張貼留言