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、函数例子

--定义函数
create or replace function NumAdd(num1 number,num2 number,num3 out number)return number
as
    num number;
begin
    num3:=num1-num2;  --out 参数的传出参数
    num:=num1+num2;
    return num;  --return 参数
end;

--使用函数返回值
declare
    num number;
    num2 number;
begin
    num:=NumAdd(5,2,num2);
    dbms_output.put_line('num='||num);  --结果为：7
    dbms_output.put_line('num2='||num2);  --结果为：3
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;