存储函数(function):
为一命名的存储程序,可带参数,并返回一个数值;
存储函数 和 存储过程的结构类似,但必须有一个 return 子句,用于返回函数值;
存储函数说明 要指定函数名、结果值的类型,以及参数等;
存储函数的语法:
create [or replace] function 函数名(参数列表)
return 返回值类型
as
PLSQL 子程序体(begin ... end;/ 没有 declare)
1、创建无参 存储函数 getName,有返回值:
SQL> -- 创建存储函数(如果没有,则创建;如果已有,则替换),返回一个字符串
SQL> create or replace function getName return varchar2
2 as
3 begin
4 -- 返回一个字符串
5 return 'hello function';
6 end;
7 /
函数已创建。
2、查看已经创建好的 存储函数:
SQL> select * from user_source;
NAME TYPE LINE TEXT
---------- ---------- ---- --------------------------------------------------
GETNAME FUNCTION 1 function getName return varchar2
GETNAME FUNCTION 2 as
GETNAME FUNCTION 3 begin
GETNAME FUNCTION 4 -- 返回一个字符串
GETNAME FUNCTION 5 return 'hello function';
GETNAME FUNCTION 6 end;
已选择6行。
3、删除存储函数:
SQL> drop function getName;
函数已删除。
4、调用存储函数:使用 PLSQL 程序调用;(注意:存储函数不能用 exec 调用,区别于 存储过程)
SQL> declare
2 -- 声明一个变量,接收函数的返回值
3 name varchar2(20);
4 begin
5 -- 调用函数 getName,将返回值赋给变量 name
6 name := getName();
7 -- 输出结果
8 dbms_output.put_line(name);
9 end;
10 /
hello function
PL/SQL 过程已成功完成。
5、创建有参存储函数 findEmpIncome_fun(编号),查询7369号员工的年收入,演示in的用法,默认in
SQL> -- 创建存储函数:带一个参数 pempno,in 表示该参数是 传入参数;如不写 in,默认就是传入参数
SQL> create or replace function findEmpIncome_fun(pempno in number) return number
2 as
3 -- 声明一个变量,存储年收入
4 income number;
5 begin
6 -- 查询年收入,并存入变量 income 中
7 select sal*12+NVL(comm,0) into income from emp where empno = pempno;
8 -- 返回
9 return income;
10 end;
11 /
函数已创建。
调用存储函数:
SQL> declare
2 -- 声明一个变量,接收存储函数返回的值
3 income number;
4 begin
5 -- 调用存储函数,传入一个参数,并返回一个数值
6 income := findEmpIncome_fun(7369);
7 -- 输出
8 dbms_output.put_line('7369 号员工的年收入为 ' || income);
9 end;
10 /
7369 号员工的年收入为 11520
PL/SQL 过程已成功完成。
6、创建有参存储函数 findEmpNameAndJobAndSal_fun(编号),查询7788号员工的的姓名(return),职位(out),月薪(out)
SQL> -- 创建存储函数,带一个传入参数,两个传出参数(out),返回一个字符串类型数据
SQL> create or replace function findEmpNameAndJobAndSal_fun(pempno in number,
2 pjob out varchar2, psal out number) return varchar2
3 as
4 -- 声明一个变量,存储返回的姓名
5 pename emp.ename%type;
6 begin
7 -- 查询数据,并存入变量 和 传出参数中
8 select ename,job,sal into pename,pjob,psal from emp where empno = pempno;
9 -- 返回姓名
10 return pename;
11 end;
12 /
函数已创建。
调用存储过程:
SQL> -- 调用存储函数
SQL> declare
2 -- 声明三个变量,用于接收存储函数传出的参数,以及存储函数返回的值
3 pename emp.ename%type;
4 pjob emp.job%type;
5 psal emp.sal%type;
6 begin
7 -- 调用存储函数
8 pename := findEmpNameAndJobAndSal_fun(7788, pjob, psal);
9 -- 输出
10 dbms_output.put_line('7788,' || pename || ',' || pjob || ',' || psal);
11 end;
12 /
7788,SCOTT,ANALYST,3000
PL/SQL 过程已成功完成。
总结:
7、什么情况下 适合使用 存储过程?什么情况下 适合使用 存储函数?
有且只有一个返回值时,适合使用 存储函数;因为存储函数有一个 return 子句,可以直接返回;
无返回值,或者有多个返回值时,适合使用 存储过程;
8、什么情况下 适合使用 存储过程或存储函数?什么情况下 适合使用 SQL 语句?
适合使用 存储过程或存储函数 的情况:
(1)、需要长期保存在数据库中;
(2)、需要被多个用户重复调用;
(3)、业务逻辑相同,只是参数不一样;
(4)、批操作大量数据,例如:批量插入很多数据;
适合使用 SQL 的情况:
(1)、凡是上述反面,都可使用 SQL;
(2)、对表、视图、序列、索引 的操作,还是要用 SQL;