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