Oracle 的過程和函數

1、使用過程或函數的好處

     1）確保資料安全性：例如，現有一個過程能夠更新某資料表，管理員不必授予使用者直接存取資料表的許可權，而是授予使用者訪問此過程的許可權。

     2）提升效能：使用預存程序時通過網路傳輸的資料量較小，減少網路傳輸量。

     3）減少記憶體：預存程序可以利用 Oracle 的共用記憶體特性，多個使用者執行同一個過程時只需將一份過程副本載入到記憶體中。通過在多個使用者間共用相同的代碼，能夠顯著地減少應用程式所需的 Oracle 記憶體。

    4）開發完整性，及提高開發效率。

2、兩者的相同及不同點

      1）函數必須有一個傳回值（return type），而過程不必。

      2）函數可以單獨執行，如：Dbms_Output.put_line('5+6='||tAdd(5,6));--tAdd是一個函數

 

           而過程不能單位執行，而過程不可以;

 

      3)函數可以嵌入到SQL語句中執行，而過程不能。如：select my_pak.tAdd(1,2) from dual;

 

      4)兩者的輸入參數類型都是in，out，in out，預設都是in。

3、函數例子

 1 --定義函數
 2 create or replace function NumAdd(num1 number,num2 number,num3 out number)return number
 3 as
 4     num number;
 5 begin
 6     num3:=num1-num2;  --out 參數的傳出參數
 7     num:=num1+num2;
 8     return num;  --return 參數
 9 end;
10 
11 --使用函數傳回值
12 declare
13     num number;
14     num2 number;
15 begin
16     num:=NumAdd(5,2,num2);
17     dbms_output.put_line('num='||num);  --結果為：7
18     dbms_output.put_line('num2='||num2);  --結果為：3
19 end;

 

3、過程例子

3.1、無參無傳回值的預存程序

--無參無傳回值的預存程序
create or replace procedure pro_test
as
begin
    dbms_output.put_line('無參，無傳回值的預存程序');
end;

--使用
declare
begin
    pro_test;
end;

3.1、有傳回值的預存程序

--通過輸入班級號cid，返回該班最大年齡的姓名maxAgeName，及全部人員c_stuList
create or replace procedure pro_test(cid varchar2,maxAgeName out student.name%type
    ,c_stuList out sys_refcursor)
as
    c_stu sys_refcursor;
    type record_stu is record(
        tname student.name%type,
        tage  student.age%type
    );
    re_stu record_stu;

    ageTemp student.age%type;
    nameTemp student.name%type;
begin
open c_stu for select name,age from student where classid=cid;
    c_stuList:=c_stu;  --因為在過程中要使用c_stu，使用後無資料，所以...
    ageTemp:=0;
    loop
fetch c_stu into re_stu;
exit when c_stu%notfound;
        dbms_output.put_line('name='||re_stu.tname||' age='||re_stu.tage);
if ageTemp<re_stu.tage then
            ageTemp:=re_stu.tage;
            nameTemp:=re_stu.tname;
end if;
end loop;
    maxAgeName:=ageTemp;
end;

--調用預存程序
declare
    c_stu sys_refcursor;
    maxAgeName student.name%type;
    tname student.name%type;
    tage  student.age%type;
begin
    pro_test('C001',maxAgeName,c_stu);
    dbms_output.put_line('最大人年齡是'||maxAgeName);
    loop
fetch c_stu into tname,tage;
exit when c_stu%notfound;
        dbms_output.put_line('name='||tname||' age='||tage);
end loop;
end;