oracle学习笔记(二十八)：存储函数

---

存储函数（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；