網頁

2020/8/5

Oracle PL/SQL test Procedure in Package

本篇介紹如何以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;

沒有留言:

張貼留言