本篇介紹如何以SQL測試Oracle套裝程式(Package)中的程序(Procedure)。
FRUIT為程序查詢用的資料表。
FRUIT
+----+---------+
| ID | NAME |
+----+---------+
| 1 | apple |
+----+---------+
| 2 | banana |
+----+---------+
| 3 | coconut |
+----+---------+
Package要分別定義spec與body，類似Java的介面與實作。下面定義一個Pacakge名稱為pg_demo。
pg_demo (spec)
CREATE OR REPLACE PACKAGE pg_demo AS
PROCEDURE p1;
PROCEDURE p2 (
i_var IN VARCHAR2
);
PROCEDURE p3 (
o_var OUT NUMBER
);
PROCEDURE p4 (
o_rc OUT SYS_REFCURSOR
);
END pg_demo;
pg_demo (body)
CREATE OR REPLACE PACKAGE BODY pg_demo AS
-- pg_demo.p1
PROCEDURE p1
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('hello world');
END p1;
-- pg_demo.p2
PROCEDURE p2 (
i_var IN VARCHAR2
)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE(i_var);
END p2;
-- pg_demo.p3
PROCEDURE p3 (
o_var OUT NUMBER
)
AS
BEGIN
o_var := 12345;
END p3;
-- pg_demo.p4
PROCEDURE p4 (
o_rc OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN o_rc FOR
SELECT * FROM FRUIT;
END p4;
END pg_demo;
以下SQL的方式呼叫來測試。
SET SERVEROUTPUT ON -- 開啟DBMS_OUTPUT.PUT_LINE()輸出
-- 測試pg_demo.p1
EXEC pg_demo.p1;
-- 測試pg_demo.p2
EXEC pg_demo.p2('This is a book');
-- 測試pg_demo.p3
DECLARE
v_num NUMBER;
BEGIN
pg_demo.p3(v_num);
DBMS_OUTPUT.PUT_LINE(v_num);
END;
-- 測試pg_demo.p4
DECLARE
v_rc SYS_REFCURSOR;
v_fruit_row FRUIT%ROWTYPE;
BEGIN
pg_demo.p4(v_rc);
LOOP
FETCH v_rc INTO v_fruit_row;
EXIT WHEN v_rc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_fruit_row.ID);
END LOOP;
END;
沒有留言:
張貼留言