Oracle UT 自製Assert

參考Oracle PL/SQL 5th

Oracle沒有提供UT的元件，
所以必須要自己撰寫類似Assert的檢核機制。
ex.

PROCEDURE ASSERT (description IN VARCHAR2 , expected_value IN VARCHAR2, actual_value  IN VARCHAR2) --驗證用
IS
BEGIN
    DBMS_OUTPUT.PUT(description || ': ');
    IF expected_value = actual_value OR (expected_value IS NULL AND actual_value IS NULL) 
        THEN DBMS_OUTPUT.PUT_LINE('PASSED'); 
        ELSE DBMS_OUTPUT.PUT_LINE('FAILED. Expected ' || expected_value || '; got ' || actual_value); 
    END IF;
END;
PROCEDURE ASSERT (description IN VARCHAR2 , expected_value IN NUMBER, actual_value IN NUMBER) --驗證用
IS
BEGIN
    DBMS_OUTPUT.PUT(description || ': ');
    IF expected_value = actual_value OR (expected_value IS NULL AND actual_value IS NULL) 
        THEN DBMS_OUTPUT.PUT_LINE('PASSED'); 
        ELSE DBMS_OUTPUT.PUT_LINE('FAILED. Expected ' || expected_value || '; got ' || actual_value); 
    END IF;
END;

-------------------------------
<!--more>
所以UT的撰寫時，可以獨立的把上述兩個Procedure撰寫在oracle中，
或者可以直接寫在Declare裡。
ex.

Declare
    A number;--預計資料筆數
    B number;--實際資料筆數(從table撈)
    PROCEDURE ASSERT (description IN VARCHAR2 , expected_value IN VARCHAR2, actual_value  IN VARCHAR2) --驗證用
    IS
    BEGIN
        DBMS_OUTPUT.PUT(description || ': ');
        IF expected_value = actual_value OR (expected_value IS NULL AND actual_value IS NULL) 
            THEN DBMS_OUTPUT.PUT_LINE('PASSED'); 
            ELSE DBMS_OUTPUT.PUT_LINE('FAILED. Expected ' || expected_value || '; got ' || actual_value); 
        END IF;
    END;
    PROCEDURE ASSERT (description IN VARCHAR2 , expected_value IN NUMBER, actual_value IN NUMBER) --驗證用
    IS
    BEGIN
        DBMS_OUTPUT.PUT(description || ': ');
        IF expected_value = actual_value OR (expected_value IS NULL AND actual_value IS NULL) 
            THEN DBMS_OUTPUT.PUT_LINE('PASSED'); 
            ELSE DBMS_OUTPUT.PUT_LINE('FAILED. Expected ' || expected_value || '; got ' || actual_value); 
        END IF;
    END;

BEGIN
       A : =10;(預計10筆)
       SELECT Count(*) INTO B FROM table;
       ASSERT('筆數檢核：',A,B) ;
END;

------------------------------------------------------------------------------------------------
結果(DBMS_OUTPUT)：
通過→
筆數檢核：PASSED
失敗→
筆數檢核：FAILED. Expected : 10 ;got 9