Oracle自定义函数记录

smith88

　　函数主要是用来操作各种数据，并返回相应的操作结果。用户自定义函数是存储在数据库中的代码块，可以把值返回到调用程序。
　　函数的语法：
　　Create [or replace] function function_name [in datatype,out datatype, in out datatype]
　　Return datatype
　　Is|as
　　……..
　　函数：
　　1、函数可在sql中的以下部分被调用：
　　select、where、having、connect by、start with、order by、group by、insert的values中、update的set中。
　　2、在函数内，是通过return语句来返回值的。函数体可以有多条return语句，但只能有一条被执行。函数结束时，还没有执行return语句会产生错误。
　　另外，return语句也可以使用在存储过程中，这种情况下它没有参数，当执行不带参数的return语句后，立刻结束并跳出存储过程，将out,in out形式的形参的当前值传给实参，并将控制权返回到调用环境。
　　3、函数和存储中的 is 和 as 是没有区别的，游标是is。
　　4、被调用的函数满足以下限制：
　　1）、只能调用服务端函数，即是不能作为块的一部分。
　　2）、函数只能有输入参数，不能有out\in out。
　　3）、函数只能使用sql支持的标准数据类型，不能使用PL/SQL特有的数据类型。
　　4）、函数内不能包含DML语句。
　　5、函数的参数有3种类型：
　　1）、in参数类型：表示输入给函数的参数，该参数只能用于传值，不能被赋值。
　　eg:
　　

create or replace function f_test1(h  in  varchar2) return varchar2　　as
　　begin
　　---   h:=2;
　　dbms_output.enable(buffer_size=>null);
　　dbms_output.put_line(h);
　　return h;
　　end;
　　
select f_test1(4) from dual;
　　

　　2)、out参数类型：表示参数在函数中被赋值，可以传给函数调用程序，该参数只能用于赋值，不能用于传值。
　　eg:
　　

create or replace function f_test3 (h varchar2,b out varchar2) return varchar2　　as
　　begin
　　b:=h+10;
　　--- dbms_output.put_line(b);
　　return h;
　　end;
　　
.....调用......
　　
declare
　　
v_1 varchar2(10);
　　
v_2 varchar2(10);
　　
begin
　　v_1:=f_test3(10,v_2);
　　dbms_output.put_line(v_1);
　　dbms_output.put_line(v_2);
　　end;
　　

　　3）、in out参数类型：表示参数既可以传值，也可以被赋值。
　　

create or replace function f_test2(h  in out varchar2) return varchar2　　as
　　begin
　　h:=2;
　　dbms_output.enable(buffer_size=>null);
　　dbms_output.put_line(h);
　　return h;
　　end;
　　

　　
declare
　　
v_1 varchar2(10):='4';
　　
begin
　　v_1:=f_test2(v_1);
　　end;
　　

　　（说明：带out参数的字定义函数不能用于 SQL语句，只能在PL/SQL程序里）
　　示例部分：
　　1.function 调用function
　　

create or replace function f_test4 (h varchar2) return varchar2　　
as
　　
g varchar2(10);
　　
f varchar2(10);
　　
j varchar2(10);
　　
begin
　　g:=f_test3(h,f);
　　j:=h+f;
　　return j;
　　end;
　　select f_test4(10) from dual;
　　

　　2.取权利人姓名：
　　create or replace function f_get_qlr (v_slbh varchar2) return varchar2
　　as
　　v_qlr varchar2(50);
　　begin
　　for i in (select q.* from dj_qlrgl gl inner join dj_qlr q on gl.qlrid=q.qlrid where gl.qlrlx='权利人' and  gl.slbh=v_slbh) loop
　　v_qlr:=v_qlr||i.qlrmc||'、';
　　end loop;
　　return substr(v_qlr,0,instr(v_qlr,'、',-1)-1);
　　---return v_qlr;
　　end;
　　by   wolihaito 2018.02.20