子程序二(子程序的table、參數模式、NOCOPY) (PL/SQL 十二)

※子程序的table

．USER_OBJECTS：查看table、index、sequence、procedure、function、type(集合)的大略訊息，而constraint xxx_pk ~~，xxx_pk就是index

．USER_PROCEDURES：查看procedure和function

．USER_TYPES：查看type(集合)

．USER_DEPENDENCIES：査看procedure、function、type(集合)的相依，主要是REFERENCED_NAME這個欄位

．USER_SOURCE：查看Procedure、function、type(集合)的詳細訊息，會將每一行程式碼變成一筆記錄

．USER_ERRORS：查看錯誤訊息

在命令模式(Command Window)裡，可以用view、edit命令，後面接procedure、function、type(集合)、table等名稱就可以開啟相關的畫面，view不能修改，edit可以修改

※參數模式

．IN：只進不出，不能修改，預設值

．OUT：只出不進，傳值進去無效

．IN OUT：又進又出

※IN (Procedure)

CREATE OR REPLACE PROCEDURE in_proc(
    p1 IN VARCHAR2,
    p2 NUMBER
) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(p1 || '|' || p2);
END;
    
    
    
DECLARE
BEGIN
    in_proc('apple', null);
END;

※上面的null感覺很沒意義，但是不給又會錯，所以可將in_proc修改一下
p2 NUMBER DEFAULT 30，就是在後面多個DEFAULT
這時呼叫端就可以不用給了 in_proc('apple');

※注意順序是VARCHAR2, NUMBER，如果給NUMBER, VARCHAR2就會錯(廢話)
但有個功能是可以的，in_proc(p2=>null, p1=>'apple');這樣子很明顯參數給相反了，p1和p2要和in_proc的參數名對應，然後用「=>」就可以自動對應了

※IN (Function)

CREATE OR REPLACE FUNCTION in_func(
    p1 IN VARCHAR2,
    p2 NUMBER DEFAULT 30
) RETURN VARCHAR2 IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(p1 || '|' || p2);
    RETURN p1;
END;
    
    
    
DECLARE
BEGIN
    DBMS_OUTPUT.PUT_LINE(in_func('apple'));
END;

※如果是在in_func (in_proc也一樣) 將傳進去的p1或p2修改，如「p1 := 'xxx';」就會錯，因為只進不出，不能修改

※OUT

CREATE OR REPLACE PROCEDURE out_proc(
    p1 OUT VARCHAR2,
    p2 OUT NUMBER
) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(p1 || '|' || p2);
    p1 := 'banana';
    p2 := 30;
END;
    
    
    
DECLARE
    pp1 VARCHAR2(10) := 'xxxx';
    pp2 NUMBER := 20;
BEGIN
    out_proc(pp1, pp2);
    DBMS_OUTPUT.PUT_LINE(pp1 || ',' || pp2);
END;

※DECLARE的參數有參設值，但傳進去的值印出來是空的，因為只出不進

※如果是顯式的呼叫，如「out_proc('apple', 30);」會錯，它一定要傳變數進去

※IN OUT

CREATE OR REPLACE PROCEDURE in_out_proc(
    p1 IN OUT VARCHAR2,
    p2 IN OUT NUMBER
) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(p1 || '|' || p2);
    p1 := 'banana';
    p2 := 30;
END;
    
    
    
DECLARE
    pp1 VARCHAR2(10) := 'xxxx';
    pp2 NUMBER := 20;
BEGIN
    in_out_proc(pp1, pp2);
    DBMS_OUTPUT.PUT_LINE(pp1 || ',' || pp2);
END;

※IN OUT很像java的傳參考，如傳集合進去，裡面做什麼更動，外面也知道

※它和OUT一樣，一定要傳變數進去，所以「in_out_proc('apple', 30);」還是會錯

※NOCOPY

．IN-->傳進去的值是一個資料庫位址，所以進去的值都不能改，效能較好，所以是預設值，所以也沒有NOCOPY參數

．OUT、IN OUT-->傳進去的值是COPY一份進去的，如果不想COPY，就要加NOCOPY，可以提升效能，不過要注意以下的不同點：

※測試

DECLARE
    xxx NUMBER := 1;
    
    PROCEDURE ooo(p OUT NUMBER) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('裡1=' || xxx);
        p := 0;
        DBMS_OUTPUT.PUT_LINE('裡2=' || xxx);
    END;
BEGIN
    ooo(xxx);
    DBMS_OUTPUT.PUT_LINE('外=' || xxx);
END;

OUT：
裡1=1
裡2=1
外=0

OUT NOCOPY：
裡1=
裡2=0
外=0

IN OUT：
裡1=1
裡2=1
外=0

IN OUT NOCOPY：
裡1=1
裡2=0
外=0

※可以看出IN和IN OUT在沒加NOCOPY時，都是一樣的

※加了NOCOPY後，因為過程中不是複制的，所以是空的，這時裡面的值賦值給它，它就會接收

※外面都是0，但要注意如果是EXCEPTION就會接到裡面的值，如下：

DECLARE
    xxx NUMBER := 1;
    
    PROCEDURE ooo(p OUT NOCOPY NUMBER) IS
    BEGIN
        p := 0;
        RAISE_APPLICATION_ERROR(-20000, '結束');
    END;
BEGIN
    BEGIN
        ooo(xxx);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(xxx);
    END;
END;

※NOCOPY會是0，不加NOCOPY會是1