oracle 函数

一、开发函数
函数用于返回特定数据，如果在应用程序中经常需要返回特定数据，可以基于操作建立一个函数，通过使用函数，不仅可以简化客户端程序的开发和维护，而且还可以提高应用程序的性能，语法如下：

create [or replace]function function_name   
(argument1[mode] dateType1,argument2[mode] dateType2........)
return dateType  
IS/AS    
PL/SQL block 

如上所示：function_name用于指定函数名称，argument1、argument2用于指定函数的参数，mode用于指定参数模式，dateType1、dateType2用于指定函数参数类型；IS/AS用于表示开始一个PL/SQL块。当指定参数类型时不能指定参数长度，另外当建立函数时即可以指定输入参数(IN),也可以指定输出参数(OUT),又可以指定输入输出参数(INOUT),return子句用于指定函数返回值的数据类型。当建立函数时，在函数头部必须要带有return子句，在函数体内至少要包括一条return 语句，
1、建立函数：不带任何参数

create or replace function function_test
return varchar2
is
v_name varchar2(10);
begin
select name into v_name from cip_temps where id=29;
return v_name;
end;

调用函数，代码如下：

 

SQL> var name varchar2(10)
SQL> exec :name:=function_test1;
SQL> print name;

2、建立函数：带有IN参数
当创建函数时，可以通过输入参数将应用程序的数据传递到函数中，最终通过执行函数将结果返回到应用程序中。当定义函数参数时，如果不指定函数模式，则默认为输入参数，所以IN关键字可写可不写。

create or replace function function_in(v_id number)
return varchar2
is
v_name varchar2(10);
begin
select name into v_name from cip_temps where id=v_id;
return v_name;
end;

调用函数，代码如下：

SQL> var name varchar2(10)
SQL> exec :name:=function_in(29);
SQL> print name;

3、建立函数：带有OUT参数
一般情况下，函数之需要返回单个数据，如果希望使用函数同时返回多个数据，则就需要使用OUT参数了。

create or replace function function_out
(v_id number,v_age out varchar2)
return varchar2
is
v_name varchar2(10);
begin
select name,age into v_name,v_age from cip_temps where id=v_id;
return v_name;
end;

该函数带有OUT参数，所以不能在SQL语句中调用该函数，必须通过定义变量接受OUT参数和函数的返回值。
调用函数，代码如下：

SQL> var name varchar2(10)
SQL> var age varchar2(10)
SQL> exec :name:=function_out(29,:age);
SQL> print name age;

4、建立函数：带有IN OUT参数
定义函数时，不仅可以定义IN和OUT参数，也可以指定IN OUT参数，IN OUT参数为输入输出参数，当使用这种参数时，在调用函数之前需要通过变量给该参数传递数据，在调用结束之后，oracle会通过该变量将过程结果传递给应用程序，示例如下：

create or replace function function_inout
(num1 number,num2 in out number)
return number
is
v1 number;
v2 number;
begin
  v1:=num1+num2;
  v2:=num1*num2;
  num2:=v2;
  return v1;
end;
/

该函数带有OUT参数，所以不能在SQL语句中调用该函数，必须通过定义变量接受OUT参数和函数的返回值。
调用函数，代码如下：

SQL> var num1 number
SQL> var num2 number
SQL> exec :num2:=10;
SQL> exec :num1:=function_inout(10,:num2);
SQL> print num1 num2;

5、函数调用限制
并不是所有的函数都可以在SQL语句下调用，在SQL语句中调用函数有以下限制：
(1)、在SQL语句中只能调用存储函数(服务器端)，而不能调用客户端函数。
(2)、在SQL语句中调用的函数只能带有输入参数(IN),而不能带有输出参数(OUT)和输入输出参数(IN OUT)。
(3)、在SQL语句中调用的函数不能包括INSERT、UPDATE和DELETE语句。
(4)、在SQL语句中调用的函数只能使用SQL语句所支持的标准数据类型，而不能使用PL/SQL的特有数据类型(例如：BOOLEAN，TABLE和RECODE)。