oracle学习笔记(第十章:自定义函数)

创建语法格式：
Create  Or   Replace Function fun_name(arg1 in datatype default v_value,arg2 out datatype default v_value,…)
Return datatype
As
--在此定义变量
Begin
--函数具体过程;

Return (v_return_value);--这里的括号可有可无

End  fun_name; --这里的函数名称可有可无

说明：
1,function 返回值必须有,且被呼叫时须为表达式的一部份
2,参数可由function 中传进传出
3,可于PL/SQL表达式和 SQL 叙述中使用
4,最好不要用 stored function 传回多个值
5,如果函数没有参数,则不需要括号,调用时括号可有可无
6,in 和 out 以及 in out 模式参数，如果省略，则默认是 in，参数可以有默认值（default）
7,在调用function或procedure时out模式参数将被初始化为null
8,[ or replace ] 表示可以重复编译，可以省略
9,参数的数据类型不需加长度限制
10,END后的函数名称可以省略
11,用户自定义函数可以像系统函数一样在select语句中调用，但是该函数必须满足纯层的要求
关于纯层(purity level)：定义了函数所能读或修改的数据结构,函数有四个纯层:
WNDS  不能写数据库状态  　函数不能修改任何数据库表(　使用ＤＭＬ语句)
RNDS  不能读数据库状态    函数不能读数据库表(使用select语句)
WNPS  不能写包状态        函数能修改任何数据库表(变量不能出现在赋值语句的左边以及fetch语句中)
RNPS  不能读包状态        函数不能使用任何包变量(包变量不能出现在赋值语句的右边或作为过程的一部分,以及SQL表达式中)


例子1：
create or replace function test_fun(vemp_no in char)-------------此处变量不能加长度
return varchar2  --一定要有返回值﹐此处无分号
as
vemp_name varchar2(10);-------------此处变量要加长度
begin
  select emp_nm into vemp_name from empm where emp_no=vemp_no;
  return(vemp_name);
end test_fun;

例子2：
create or replace function fun(vemp_no in CHAR)
return empm%rowtype – 可以返回一行值
as
lyj_rec emp_m%rowtype;
begin
   select * INTO lyj_rec from emp_m where emp_no=vemp_no;
   return lyj_rec;
end;



function 与 procedure 的比较
--都可以通过使用out参数返回一个以上的值
--当返回多个值时,用procedure较好
--当返回一个值时,用function较好
--function可以在sql语句中调用,而procedure则不可

-------------检索存储过程
-------------Oracle中有个系统视图﹕user_source﹐它提供以下4个列
-------------Name:包括存储过程﹐函数﹐包或包体的名称
-------------Type﹕指出源代码是否属于存储过程﹐函数还是包体
-------------Text:包含源代码的一行
-------------Line﹕包含在Text中源代码中所包含的函数
----例子检索存储过程test_pro4的源代码
select line,text from user_source where name='test_pro2' order by line;