網頁

2018/8/6

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;

參考:

沒有留言:

張貼留言