通常我們會在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;
參考:
沒有留言:
張貼留言