--==================
-- PL/SQL --> 函数
--==================
函数通常用于返回特定的数据。其实质是一个有名字的 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 操作 多用于特定的数据如选择等
七、更多参考
有关 SQL 请参考
SQL 基础--> ROLLUP 与CUBE 运算符实现数据汇总
SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)
有关 PL/SQL 请参考
