PLSQL开发笔记和小结（5）——Oracle存储过程和函数（转载）

*****************************************

Oracle存储过程

*****************************************

在谈存储过程书写中的一些规则时，先看一下执行它的规则，在命令窗口执行存储过程say_hello

     var v_msg_result varchar2(30);

     exec say_hello('zhangsan', :v_msg_result); --或execute

     或

     declare

        v_msg_result varchar2(30);

     begin

        say_hello('zhangsan', v_msg_result);

        dbms_output.put_line(v_msg_result);

     end;

存储过程入参，不论类型，缺省情况下值都为null,入参和出参不能有长度,其中关键字as可以替换成is,存储过程中变量声明在as和begin之间，同时，存储过程中可以再调用其它的存储过程，如果要保证存储过程之间的事务处理不受影响，可以定义为自治事务。

     create or replace procedure say_hello(v_name in varchar2,v_flag number,o_ret out number)

     as

     begin

       if v_name is null and v_flag is null then --v_name和v_flag都等于null

           o_ret := 10;

       else

           o_ret := 100;

       end if;

     end;

对于入参为null情况下给予缺省值

     create or replace procedure say_hello(i_name in varchar2,i_flag number,o_ret out number)

     as

       v_name  varchar2(100);

     begin

       if i_name is null then

          v_name := '0';

       else

          v_name := i_name;

       end if;

       insert into phone(..,wname..,) values(..,v_name,..);

     end;

或直接在insert语句中调用nvl函数赋缺省值

     insert into phone(..,wname..,) values(..,nvl(v_name,' '),..); --如果将' '写成'',则insert进来的v_name值还是为''等价于null值

带一个参数的存储过程

   输入参数in，输入参数不能进行:=赋值，但可以将它赋给as后面定义的变量；

   输入参数in，可以作为变量进行条件判断；

   默认不写就是in；

   存储过程没有重载，这个新建的say_hello会替代已经存在的say_hello。

     create or replace procedure say_hello(v_name in varchar2)

     as

     begin

        --v_name:='a'; --存储过程入参v_name不能做为赋值目标

        dbms_output.put_line(v_name);

     end;

存储过程输入参数作为变量进行条件判断

     create or replace procedure say_hello(i_opFlag in number)

     as

        v_name varchar2(100);

     begin

        if i_opFlag = 1 then

           v_name :='0';

        else

           v_name :='haha';

        end if;

        dbms_output.put_line(v_name);

     end;

利用存储过程中定义的变量对入参的空值处理：

     create or replace procedure say_hello(i_name in varchar2)

     as

        v_name varchar2(100);

     begin

        if i_name is null then

           v_name :='0';

        else

           v_name :=i_name;--将入参赋值给定义变量

        end if;

        dbms_output.put_line(v_name);

     end;

out输出参数，用于利用存储过程给一个或多个变量赋值，类似于返回值

     create or replace procedure say_hello(v_name in varchar2,v_content out varchar2)

     as

begin

        v_content:='hello '||v_name;

     end;

     调用：

     Declare

v_in varchar2(20):='wang';

        v_con varchar2(30);

     begin

        say_hello(v_in,v_con);

        dbms_output.put_line(v_con);

     end;

in out参数，既赋值又取值

     create or replace procedure say_hello(v_name in out varchar2)

     as

     begin

        v_name:='hello '||v_name;

     end;

     调用：

     declare

        v_inout varchar2(20):='wangsu';

     begin

        say_hello(v_inout);

        dbms_output.put_line(v_inout);

     end;

对存储过程入参赋缺省值

     create or replace procedure say_hello(v_name varchar2 default 'susu', v_content varchar2 default 'hello')

     as

     begin

        dbms_output.put_line(v_name||' '||v_content);

     end;

     调用：(用指明形参名的方式调用更好)

     begin

        say_hello();

     end;

     或

     begin

        say_hello('cheng');

     end;

     或

     begin

        say_hello(v_name=>'cheng');

     end;

*****************************************

 PLSQL中的函数

*****************************************

function和procedure的区别：

1、函数有返回值，过程没有。

2、函数调用在一个表达式中，过程则是作为pl/sql程序的一个语句。

过程和函数都以编译后的形式存放在数据库中，函数可以有零个或多个参数并有一个返回值。过程可以有零个或多个参数但没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值，函数和过程的主要区别不在于返回值，而在于他们的调用方式，函数以合法表达式的方式调用，过程是作为一个独立执行语句调用的。

    create or replace function func(v_name in varchar2)

    return varchar2

    is     --也可以用as

    begin

       return (v_name||' hello');

    end;

    调用：

    declare

       v_name varchar2(20);

    begin

       v_name:=func('cheng');

       dbms_output.put_line(v_name);

    end;

带out参数的函数

    create or replace function func(v_name in varchar2,v_content out varchar2)

    return varchar2

    is

    begin

       v_content:=v_name||' hello';

       return v_content;

    end;

    调用：

    declare

       v_name varchar2(20);

       v_name1 varchar2(20);

    begin

       v_name1:=func('susu',v_name);--返回v_name值

       dbms_output.put_line(v_name1);--打印func结果

       dbms_output.put_line(v_name);--打印v_name结果

    end;

带in out 参数的函数

    create or replace function func(v_name in out varchar2)

    return varchar2

    is

    begin

       v_name:=v_name||' hello';

       return v_name;

    end;

    调用：

    declare

       v_inout varchar2(20):='world';

       v_ret varchar2(20);

    begin

       v_ret:=func(v_inout);--返回调用v_inout值(作为出参)

       dbms_output.put_line(v_ret);--打印func结果

       dbms_output.put_line(v_inout);--返回v_name结果

    end;  

 

转载自：http://www.blogjava.net/cheneyfree/archive/2008/07/19/216090.html