Oracle PL/SQL 在Package建立一個簡單的查詢Stored Procedure

通常我們會在Java的Hibernate或MyBatis下SQL指令來查詢資料庫，但在某些情況會要求在資料庫寫Stored Procedure來查詢，而僅在Java中呼叫該Procedure來取得結果。

下面在Package建立一個簡單的查詢Stored Procedure範例，使用客戶id來查詢某個客戶。

-- spec
CREATE OR REPLACE PACKAGE customer_pkg AS
  PROCEDURE get_customer_by_id(i_customer_id IN VARCHAR2, o_customer OUT SYS_REFCURSOR);
END customer_pkg;

-- body
CREATE OR REPLACE PACKAGE BODY customer_pkg AS

    PROCEDURE get_customer_by_id (i_customer_id IN VARCHAR2, o_customer OUT SYS_REFCURSOR) AS
    BEGIN
      OPEN o_customer FOR
        SELECT customer_id, customer_name, customer_email 
        FROM customer
        WHERE customer_id = i_customer_id;
    END get_customer_by_id;

END customer_pkg;

參考：

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