oracle中的function怎么建

一、函数概述
PL/SQL中的过程和函数（通常称为子程序）是PL/SQL块的一种特殊的类型，这种类型的子程序可以以编译的形式存放在数据库中，并为后续的程序块调用。
　　相同点：完成特定功能的程序
　　不同点：是否用return语句返回值

二、函数语法
　　CREATE [OR REPLACE] FUNCTION <函数名> [(<参数名> [IN | OUT | IN OUT] <数据类型> ,…)]
　　　　RETURN <返回值类型>
　　IS | AS
　　　　[<变量或常量声明>;]
　　BEGIN
　　　　<子程序块...>;
　　　　RETURN <需要返回的变量或常量名>;
　　　　[EXCEPTION
　　　　　　<异常处理...>;
　　　　　　RETURN <当出现异常时的返回值、变量或常量>;]
　　END [<函数名>];
/

三、函数参数
函数的参数有3种类型：
1、IN 参数类型：表示输入给函数的参数，该参数只能用于传值，不能被赋值。
2、OUT 参数类型：表示参数在函数中被赋值，能够传给函数调用程序，该参数只能用于赋值，不能用于传值。
3、IN OUT 参数类型：表示参数既能够传值，也能够被赋值。

四、无参函数
1）定义无参函数---------------
create or replace function fn_PrintToday
return date
is
begin
return sysdate;
end;
2）调用无参函数---------------
--调用方式1
SELECT fn_PrintToday() FROM DUAL;
--调用方式2
begin
dbms_output.put_line(fn_PrintToday);
end;

五、有参函数
5.1、带输入参数函数
1）定义带输入参数函数----------------------------
create or replace function fn_SearchEmpByEmpno (p_empno varchar2)
return varchar2 --返回值类型
is
v_ename emp.ename%type; --临时变量
begin
select ename into v_ename from emp where empno=p_empno;
return v_ename;
exception
when no_data_found then
raise_application_error(-20001,'您输入的empno无效!');
end;
2）调用带输入参数函数--------------------------
begin
dbms_output.put_line(fn_SearchEmpByEmpno(p_empno=>'1001'));
end;

5.2、带输出参数函数
1）定义带输出参数函数--------------------------
create or replace function fn_CountEmp
(
p_ename in varchar2,
out_value out int
)
return varchar2
is
begin
select count(1) into out_value from emp where ename=p_ename;
return p_ename;
end;

2）调用带输出参数函数---------------------------
declare
ename emp.ename%type;
result int;
begin
ename:=fn_CountEmp(p_ename=>'James',out_value=>result);
dbms_output.put_line(ename || '名字的有' || to_char(result) || '个人');
end;

六、维护函数
修改函数：与创建的语法相同，使用REPLACE替换即可。
删除函数：drop function [schema.]函数名

七、函数相关
查看函数：SELECT * FROM USER_SOURCE WHERE USER_SOURCE.NAME='<函数名>';
SELECT * FROM USER_SOURCE WHERE NAME='FN_COUNTEMP';
编译函数：ALTER FUNCTION 函数名 COMPILE;
ALTER FUNCTION FN_COUNTEMP COMPILE;

八、自定义函数返回结果集
1、基本说明
Oracle 中的函数可以返回表类型，但是这个表类型实际上是集合类型（与数组类似）。从 Oracle 9i 开始，提供了一个叫做"管道化表函数"来解决此问题。
管道化表函数，必须返回一个集合类型，且标明 pipelined。它不能返回具体变量，必须以一个空 return 返回，通过 pipe row () 语句来送出要返回的表中的每一行。调用这个函数的时候，通过 table() 关键字把管道流仿真为一个数据集。
2、常见形式
2.1、以游标形式返回结果集
1）创建函数---------------
create or replace function fn_GetEmpnoByCursor(p_ename in varchar2)
return sys_refcursor
is
c_empno sys_refcursor;
begin
open c_empno for
select empno,ename from emp where ename=p_ename;
return(c_empno);
end;
2）调用函数---------------
select fn_GetEmpnoByCursor(p_ename => 'James') from dual;
2.2、以Table形式返回结果集
1）创建函数---------------
--定义一个行类型
create or replace type "emp_row_type" as object(empno varchar2(10),ename varchar2(20));
--定义一个表类型
create or replace type "emp_table_type" as table of "emp_row_type";
--创建函数
create or replace function fn_GetEmpnoByTable (p_ename in varchar2)
return "emp_table_type"
is
emp_row "emp_row_type"; --定义单行
emp_table "emp_table_type" :="emp_table_type"(); --定义返回结果，并初始化。
begin
for currow in (select empno,ename from emp where ename=p_ename)
loop
emp_row := "emp_row_type"(currow.empno,currow.ename);
emp_table.extend;
emp_table(emp_table.count) := emp_row;
end loop;
return(emp_table);
end;
2）调用函数----------------
select * from table(fn_GetEmpnoByTable(p_ename => 'James'));
2.3、以管道形式返回结果集
1）创建函数----------------
--定义一个行类型
create or replace type "emp_row_type" as object(empno varchar2(10),ename varchar2(20));
--定义一个表类型
create or replace type "emp_table_type" as table of "emp_row_type";
--创建函数
create or replace function fn_GetEmpnoByPipe(p_ename in varchar2)
return "emp_table_type" pipelined
is
emp_row "emp_row_type"; --定义emp_row为行对象类型
begin
for currow in (select empno,ename from emp where ename=p_ename)
loop
emp_row := "emp_row_type"(currow.empno,currow.ename);
pipe row (emp_row);
end loop;
return;
end;
2）调用函数-----------------
select * from table(fn_GetEmpnoByPipe(p_ename => 'James'));