函数通常用于返回特定的数据。其实质是一个有名字的PL/SQL块，作为一个schema对象存储于数据库，可以被反复执行。函数通常被作为一个表达式来调用或存储过程的一个参数，具有返回值。 一、建立函数的语法 CREATE [ OR REPLACE ] FUNCTION function_name (argument1 [mode1] datatype1, argument2 [mode2] datetype2, ...) RETURN datatype IS | AS [local_variable_declarations;...] BEGIN --actions; RETURN expression; END [function_name]; 建立函数的几点注意事项： 1.指定参数数据类型时(argument)，不能指定其长度 2.函数头部必须指定return子句，函数体内至少要包含一条return语句 3.可以指定in参数，也可以指定out参数，以及in out 参数 4.可以为参数指定缺省值。指定缺省值时使用default关键字。如arg1 varchar2 default 'SCOTT'
二、使用函数的优点 1.增加了代码的灵活性，可以完成一些较为复杂的任务，以及仅仅通过SQL无法完成的任务 2.可以直接将函数使用到where子句中来过滤数据 3.可以作为存储过程的参数使用，是存储过程的一种补充
三、建立函数 1.建立不带参数的函数 scott@ORCL> create or replace function get_user 2 return varchar2 3 is 4 v_user varchar2(20); 5 begin 6 select username into v_user from user_users; 7 return v_user; 8 end; 9 / Function created.
--使用全局变量接收函数的返回值 scott@ORCL> var v1 varchar2(20) scott@ORCL> exec :v1:=get_user PL/SQL procedure successfully completed. scott@ORCL> print v1; V1 -------------------------------- SCOTT --使用本地变量接收函数的返回值 scott@ORCL> set serveroutput on; scott@ORCL> declare user_name varchar2(20); 2 begin 3 user_name:=get_user(); 4 dbms_output.put_line('Current user: '||user_name); 5 end; 6 / Current user: SCOTT PL/SQL procedure successfully completed. --在SQL语句中直接调用函数 scott@ORCL> select get_user from dual; GET_USER --------------------- SCOTT --使用dbms_output调用函数(此调用作为存储过程的一个参数来进行调用) scott@ORCL> set serveroutput on; scott@ORCL> exec dbms_output.put_line('Current user: '||get_user); Current user: SCOTT 2.建立带有in 参数的函数 scott@ORCL> create or replace function raise_sal(name in varchar2) --注意此处定义时参数并为指定类型的长度 2 return number 3 as 4 v_sal emp.sal%type; 5 begin 6 select sal*1.2 into v_sal from emp 7 where upper(ename)=upper(name); 8 return v_sal; 9 exception 10 when no_data_found then 11 raise_application_error(-20000,'Current Employee is not exists'); 12 end; 13 / Function created. scott@ORCL> select sal,raise_sal('SCOTT') from emp where ename='SCOTT'; SAL RAISE_SAL('SCOTT') ---------- ------------------ 3100 3720 scott@ORCL> select raise_sal('Robinson') from dual; select raise_sal('Robinson') from dual * ERROR at line 1: ORA-20000: Current Employee is not exists ORA-06512: at "SCOTT.GET_SAL", line 11 3.建立带有out参数的函数 scott@ORCL> create or replace function get_info 2 (name varchar2,title out varchar2) 3 return varchar2 4 as 5 deptname dept.dname%type; 6 begin 7 select e.job,d.dname into title,deptname 8 from emp e inner join dept d 9 on e.deptno=d.deptno 10 where upper(e.ename)=upper(name); 11 return deptname; 12 exception 13 when no_data_found then 14 raise_application_error(-20000,'Current Employee is not exists'); 15 end; 16 / Function created. 注意：对于使用out参数的函数，不能使用SQL语句来调用。而必须定义变量接收out参数和函数的返回值。 调用如下 scott@ORCL> var job varchar2(20); scott@ORCL> var dname varchar2(20); scott@ORCL> exec :dname:=get_info('scott',:job); PL/SQL procedure successfully completed. scott@ORCL> print dname job; DNAME -------------------------------- RESEARCH JOB -------------------------------- ANALYST 3.建立带有in out参数的函数 scott@ORCL> create or replace function comp 2 (num1 number,num2 in out number) 3 return number 4 as 5 v_result number(6); 6 v_remainder number; 7 begin 8 v_result:=num1*num2; 9 v_remainder:=mod(num1,num2); 10 num2:=v_remainder; 11 return v_result; 12 exception 13 when zero_divide then 14 raise_application_error(-20000,'Divison by zero'); 15 end; 16 / Function created. scott@ORCL> var result1 number; scott@ORCL> var result2 number; scott@ORCL> exec :result2:=10 PL/SQL procedure successfully completed.
scott@ORCL> exec :result1:=comp(16,:result2); PL/SQL procedure successfully completed. scott@ORCL> print result1 result2; RESULT1 ---------- 160 RESULT2 ---------- 6 四、函数的调用及限制 1.函数的调用(其具体调用方法参照上面的演示) a.使用全局变量接收函数的返回值 b.使用本地变量接受函数的返回值 c.在SQL语句中直接调用函数 d.使用dbms_output调用函数 注：函数在调用的时候需要按位置指定参数，没有存储过程参数传递灵活 必须具有execute 函数的权限 2.函数在SQL中调用的主要场合 由于函数必须要返回数据，因此只能作为表达式的一部分调用。此外函数可以在SQL语句的以下部分调用 a. select 命令的选择列表或子查询中 b. 条件表达式where, having子句中 c. connect by , start with ,order by 以及group by 子句中 d. insert 命令的values子句中 f. update 命令的set 子句中 3.函数在SQL中调用的限制 a. SQL语句中只能调用存储在服务器端的函数，而不能调用存储于客户端的函数 b. SQL语句中调用的函数只能带有输入参数IN，而不能带有输出参数OUT 以及输入输出参数IN OUT c. SQL语句中调用的函数只能使用SQL支持的标准数据类型，不能使用PL/SQL特有的类型，如boolean,table,record等 d. SQL语句中调用的函数不能包含insert ,update 和delete 语句 下面演示SQL调用时不能完整DML操作示例 --创建一张表tb_emp scott@ORCL> create table tb_emp as select * from emp; --创建一个函数，用于删除tb_emp表中指定的empno号的雇员信息，并返回其薪资 scott@ORCL> create or replace function delete_oper(no number) 2 return number 3 as 4 v_sal emp.sal%type; 5 begin 6 select sal into v_sal from tb_emp where empno=no; 7 delete from tb_emp where empno=no; 8 return v_sal; 9 end; 10 / Function created. --使用SQL语句调用时，收到了错误信息，在内部查询内不能完成DML操作 scott@ORCL> select delete_oper(7788) from dual; select delete_oper(7788) from dual * ERROR at line 1: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "SCOTT.DELETE_OPER", line 7 --使用exec执行时函数被成功执行 scott@ORCL> var v_no number; scott@ORCL> exec :v_no:=delete_oper(7788); PL/SQL procedure successfully completed. scott@ORCL> print v_no; V_NO ---------- 3100 scott@ORCL> select * from tb_emp where empno=7788; no rows selected --下面的演示表明，不能使用DML语句来调用函数 scott@ORCL> update emp set sal=raise_sal('SCOTT') where ename='SCOTT'; update emp set sal=raise_sal('SCOTT') where ename='SCOTT' * ERROR at line 1: ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it ORA-06512: at "SCOTT.RAISE_SAL", line 6 五、函数的管理 函数使用了与存储过程相关的视图，可以从系统视图中获得函数的相关信息 DBA_OBJECTS DBA_SOURCE USER_OBJECTS USER_SOURCE --查看函数的源码 scott@ORCL> select text from user_source where name='DELETE_OPER' order by line; TEXT ------------------------------------------------------------ function delete_oper(no number) return number as v_sal emp.sal%type; begin select sal into v_sal from tb_emp where empno=no; delete from tb_emp where empno=no; commit; return v_sal; end; --查看函数的参数信息 scott@ORCL> desc delete_oper; FUNCTION delete_oper RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NO NUMBER IN 六、函数与存储过程的差异
函数与存储过程的差异
| 存储过程 | 函数 |
| 不能被作为表达式调用 | 只能作为表达式被调用 |
| 声明头部关键字为procedure | 声明头部关键字为function |
| 声明头部不包含return关键字来描述返回类型 | 头部必须包含return关键字,且PL/SQL块中至少包含一个有效的return语句 |
| 可以通过out,in out返回零个或多个值 | 通过return语句返回一个与头部声明中类型一致的值,也可使用in,in out返回值 |
| SQL语句中不可调用存储过程 | SQL语句可以调用函数 |
| 多用于数据库中完成特定的操作,如删除,更新,插入等DML操作 | 多用于特定的数据如选择等 |
[摘自]