自定义函数是Oracle数据库中可重用的PL/SQL代码块，它接受参数、执行操作并返回一个值。函数可以在SQL语句中直接调用，极大增强了SQL的处理能力。
| 分类 | 调用 | ||
| 没有参数 | 没有输入输出 | 写SQL不加参数 | |
| 有参数 | 有输入无输出 | 写SQL加参数 | |
| 无输入有输出 | 输出变量 | 写PL/SQL匿名块或者自定义函数、存储过程调用 | |
| 输出动态游标 | |||
| 有输入有输出 | 写PL/SQL匿名块或者自定义函数、存储过程调用 | ||
目录
一、函数基本结构
1. 创建语法：必须有返回值
【语法】关键词【function,return】
CREATE [OR REPLACE] FUNCTION function_name
[(parameter1 [IN|OUT|IN OUT] datatype [DEFAULT|:= value],
parameter2 [IN|OUT|IN OUT] datatype [DEFAULT|:= value],
...)]
RETURN return_datatype
[IS|AS]
[declaration_section]
BEGIN
executable_section
[RETURN value;]
[EXCEPTION
exception_section]
END [function_name];
create {or replace} function fun_name
(v1 in|out|in out 数据类型{无长度}/*可以加多各参数*/)
return 类型{无长度}
as|is
{声明变量/声明游标}---如果没有变量{类型长度}声明，也可以不用声明
begin
要执行的语句；
[return 值|游标;]
[exception]
[return 值|游标;]
end;
/2. 基本示例
CREATE OR REPLACE FUNCTION get_employee_name(
p_emp_id IN employees.employee_id%TYPE
) RETURN VARCHAR2
IS
v_full_name VARCHAR2(100);
BEGIN
SELECT first_name || ' ' || last_name INTO v_full_name
FROM employees
WHERE employee_id = p_emp_id;
RETURN v_full_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '员工不存在';
END get_employee_name;
/二、函数参数模式
| 模式 | 描述 | 示例 | 调用 |
| IN | 只读参数(默认) 输入值{可以不用写} |
p_id IN NUMBER | sql调用 |
| OUT | 只写参数 输出值/*必须写*/ |
p_result OUT VARCHAR2 | plsql调用 |
| IN OUT | 可读写参数 既输入也输出 |
p_counter IN OUT NUMBER | plsql调用 |
sys_refcursor |
动态游标 |
||
dbms_output.put_line |
输出 |
三、无参数：没有输入输出
--创建函数
create or replace function π return number as
begin
return ACOS(-1);
end;
--使用函数，求半径为1的圆的面积
select π*1*1 from dual四、有参数：只输入IN--用select调用
【举例】
--手动制作一个函数sign()
--创建
create or replace function mysign(v1 in number) return number as
begin
if v1 > 0 then
return 1;/*这里的意思是输出一个表*/
elsif v1 < 0 then
return - 1;
else
return 0;
end if;
end;
/
--调用
select mysign(23333333) from dual--只输入用select
/*############################################################################*/
【例题】
/*=====================================================================================*/
【1】制作一个函数，输入一个数值，返回它的相反数
--创建
CREATE OR REPLACE FUNCTION FUN_XFS(V1 NUMBER) RETURN NUMBER AS
BEGIN
IF V1 > 0 THEN
RETURN '-' || V1;
ELSIF V1 < 0 THEN
RETURN ABS(V1);
ELSE
RETURN 0;
END IF;
END;
/
--引用
SELECT fun_xfs(0) FROM dual
--调用
CREATE OR REPLACE FUNCTION FUN_XFS1(V1 NUMBER) RETURN NUMBER AS
BEGIN
RETURN(-V1);
END;
/
--调用
SELECT fun_xfs1(-1) FROM dual
/*=====================================================================================*/
【2】编写一个函数，输入一个员工编号，返回其工资等级
--创建
create or replace function 工资等级1(v1 number) return number as
v_g salgrade.grade%type;
begin
select b.grade
into v_g
from emp a, salgrade b
where a.empno = v1
and a.sal between b.losal and b.hisal;
return v_g;
end;
/
--调用
select 工资等级1(7788) from dual
----------------------------------------------
--创建
create or replace function 工资等级2(v1 number) return number as
v_g salgrade.grade%type;
begin
select b.grade
into v_g
from emp a
inner join salgrade b
on a.sal between b.losal and b.hisal
where a.empno = v1;
return v_g;
end;
/
--调用
select 工资等级2(7788) from dual
select 查询工资等级(7369) from dual--有输入，
没有 dnms_output.put_line()输出，用select调用
/*=====================================================================================*/
【3】输入一个日期，返回这个月有多少个周一
--创建
create or replace function fun_3(v1 date) return number as
v2 number;
begin
select count(trunc(v1, 'mm') - 1 + level)
into v2
from dual
where to_char((trunc(v1, 'mm') - 1 + level), 'd') = 2
connect by level <= to_char(last_day(v1), 'dd');
return v2;
end;----有输入，没有 dnms_output.put_line()输出，用select调用
/
--调用
select fun_3(date'2019-4-1') from dual
--------------------------------------------------------------------
--创建
create or replace function 某月有多少周一(v1 date) return number as
v_count number;
begin
select count(mm)
into v_count
from (select level + (trunc(v1, 'mm') - 1) mm
from dual
connect by level <= to_char(last_day(v1), 'dd'))
where to_char(mm, 'd') = 2;
return v_count;
end;
/
--调用
select 某月有多少周一(date'2019-01-02') from dual
/*=====================================================================================*/
【4】输入一个部门编号和日期，返回这个部门在这个日期之后入职的员工人数
--创建
CREATE OR REPLACE FUNCTION FUN_4(V1 NUMBER, V2 DATE) RETURN NUMBER AS
V3 NUMBER;
BEGIN
SELECT COUNT(*)
INTO V3
FROM EMP
WHERE DEPTNO = V1
AND HIREDATE > V2;
RETURN V3;
END;
/
--调用
SELECT fun_4(10,DATE'1981-12-01') FROM dual五、有参数：有输出OUT--plsql调用
1、输出变量
【举例】
CREATE OR REPLACE FUNCTION calculate_tax(
p_salary IN NUMBER,
p_tax_rate OUT NUMBER
) RETURN NUMBER
IS
v_tax_amount NUMBER;
BEGIN
IF p_salary <= 5000 THEN
p_tax_rate := 0;
ELSIF p_salary <= 10000 THEN
p_tax_rate := 0.1;
ELSE
p_tax_rate := 0.2;
END IF;
v_tax_amount := p_salary * p_tax_rate;
RETURN v_tax_amount;
END calculate_tax;
/
-- 调用示例
DECLARE
v_rate NUMBER;
v_tax NUMBER;
BEGIN
v_tax := calculate_tax(15000, v_rate);
DBMS_OUTPUT.PUT_LINE('税率: ' || v_rate || ', 税额: ' || v_tax);
END;
/
【练习】
/*=====================================================================================*/
【1】输入一个部门编号获取部门的工资总和和总人数
-- 创建
create or replace function fun3(v1 number, v_sum out number)
return number as
v_count number;
begin
select sum(sal), count(*) into v_sum, v_count from emp where deptno = v1;
return v_count;
end;
-- 调用
declare
v_sum number;
v_count number;
begin
v_count := fun3(10, v_sum);--给返回值赋值：=fun_name(参数，参数)
dbms_output.put_line('总工资：' || v_sum || ' 总人数：' || v_count);
end;
/
/*=====================================================================================*/
【2】输入一个员工编号，输出它的月薪和职位
-- 创建
create or replace function 查询月薪和职位(v1 number, v_s out number)
return varchar2 as
v_j varchar2(20);
begin
select e.sal,e.job into v_s,v_j from emp e where e.empno = v1;
return v_j;
end;
-- 调用
declare
v_s number;
v_j varchar2(20);
begin
v_j:=查询月薪和职位(&a,v_s);
dbms_output.put_line('月薪'||v_s||'职位'||v_j);
end;
/
/*=====================================================================================*/
【2】输入一个工资等级,输出这个等级的最低工资，最高工资
CREATE OR REPLACE FUNCTION FUN_5(V1 NUMBER, V2 OUT NUMBER) RETURN NUMBER AS
V3 NUMBER;
BEGIN
SELECT LOSAL, HISAL INTO V2, V3 FROM SALGRADE WHERE GRADE = V1;
RETURN V3;
END;
/
-- 调用示例
DECLARE
V1 NUMBER := &A;
V2 NUMBER;
V3 NUMBER;
BEGIN
V3 := FUN_5(V1, V2);
DBMS_OUTPUT.PUT_LINE('最低工资' || V2 || CHR(10) || '最高工资' || V3);
END;
/
2、输出游标
-------------------------
（一）【返回一个动态游标】
-------------------------
【1】输入一个部门编号，输入部门员工信息，
--创建
create or replace function fun4(v1 number) return sys_refcursor as
cur_emp sys_refcursor;
begin
open cur_emp for
select * from emp where deptno =v1;
return cur_emp;
end;
--调用
declare
cur_a sys_refcursor;
v_emp emp%rowtype;
begin
cur_a := fun4(&a);
loop
fetch cur_a
into v_emp;
exit when cur_a%notfound;
dbms_output.put_line(v_emp.empno || v_emp.ename);
end loop;
end;
/*#####################################################################################*/
-------------------------------
（二）【输出和返回都是动态游标】
-------------------------------
【1】输入一个部门编号，输出这个部门的员工信息和部门信息
--创建
create or replace function fun5(v1 number, cur_dept out sys_refcursor)--输出是动态游标
return sys_refcursor as--返回是动态游标
cur_emp sys_refcursor;
begin
open cur_emp for
select * from emp where deptno = v1;
open cur_dept for
select * from dept where deptno = v1;
return cur_emp;
end;
--调用
declare
cur_emp sys_refcursor;
cur_dept sys_refcursor;
v_emp emp%rowtype;
v_dept dept %rowtype;
begin
cur_emp := fun5(20, cur_dept);
loop
fetch cur_emp
into v_emp;
exit when cur_emp%notfound;
dbms_output.put_line(v_emp.empno || v_emp.ename || v_emp.deptno);
end loop;
loop
fetch cur_dept into v_dept;
exit when cur_dept%notfound;
dbms_output.put_line(v_dept.deptno || v_dept.dname);
end loop;
end;
【2】输入一个经理编号，输出他所有的下级姓名和员工编号和他自己的员工编号、入职日期
--创建
create or replace function fun6(v1 number, cur_1 out sys_refcursor)
return sys_refcursor as
cur_2 sys_refcursor;
begin
open cur_1 for
select empno, ename from emp where mgr = v1;
open cur_2 for
select empno, hiredate from emp where empno = v1;
return cur_2;
end;
--调用
declare
cur_1 sys_refcursor;
cur_2 sys_refcursor;
v1 emp.empno%type;
v2 varchar2(20);
v3 number;
v4 date;
begin
cur_2 := fun6(&a, cur_1);
loop
fetch cur_1
into v1, v2;
exit when cur_1%notfound;
dbms_output.put_line('下级编号:' || v1 || '下级姓名:' || v2);
end loop;
loop
fetch cur_2
into v3, v4;
exit when cur_2%notfound;
dbms_output.put_line('经理编号:' || v3 || '入职日期:' || v4);
end loop;
end;六、有参数：有输入输出值IN OUT--必须用宏代换&
--创建
create or replace function fun7(v_empno number, v_jia in out number)
return varchar2 as
v_name emp.ename%type;
begin
update emp001
set sal = sal + v_jia
where empno = v_empno
returning ename, sal into v_name, v_jia;
return v_name;
end;
--调用
declare
v_empno emp.empno%type := &empno;---必须用宏代换
v_name emp.ename%type;
v_jia emp.sal%type := &jia;
begin
v_name := fun7(v_empno, v_jia);
dbms_output.put_line('姓名:' || v_name || '新工资:' || v_jia);
end;七、函数返回值
1. 返回标量值
CREATE OR REPLACE FUNCTION get_department_name(
p_dept_id IN departments.department_id%TYPE
) RETURN VARCHAR2
IS
v_dept_name departments.department_name%TYPE;
BEGIN
SELECT department_name INTO v_dept_name
FROM departments
WHERE department_id = p_dept_id;
RETURN v_dept_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '部门不存在';
END get_department_name;
/2. 返回复合类型
（1）使用%ROWTYPE
CREATE OR REPLACE FUNCTION get_employee_info(
p_emp_id IN employees.employee_id%TYPE
) RETURN employees%ROWTYPE
IS
v_emp employees%ROWTYPE;
BEGIN
SELECT * INTO v_emp
FROM employees
WHERE employee_id = p_emp_id;
RETURN v_emp;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END get_employee_info;
/（2）使用自定义类型
-- 先创建对象类型
CREATE OR REPLACE TYPE emp_obj AS OBJECT (
emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER,
hire_date DATE
);
/
-- 创建返回对象的函数
CREATE OR REPLACE FUNCTION get_employee_obj(
p_emp_id IN NUMBER
) RETURN emp_obj
IS
v_emp emp_obj;
BEGIN
SELECT emp_obj(
employee_id,
first_name || ' ' || last_name,
salary,
hire_date
) INTO v_emp
FROM employees
WHERE employee_id = p_emp_id;
RETURN v_emp;
END get_employee_obj;
/八、函数中的DML操作
函数可以包含DML操作，但有以下限制：
不能执行事务控制语句(COMMIT/ROLLBACK)
调用时可能需要特殊权限
示例：审计函数
CREATE OR REPLACE FUNCTION update_salary_with_log(
p_emp_id IN employees.employee_id%TYPE,
p_new_salary IN employees.salary%TYPE
) RETURN VARCHAR2
IS
v_old_salary employees.salary%TYPE;
v_result VARCHAR2(100);
BEGIN
-- 获取旧薪资
SELECT salary INTO v_old_salary
FROM employees
WHERE employee_id = p_emp_id;
-- 更新薪资
UPDATE employees
SET salary = p_new_salary
WHERE employee_id = p_emp_id;
-- 记录审计日志
INSERT INTO salary_audit (
audit_id, employee_id,
old_salary, new_salary,
change_date
) VALUES (
audit_seq.NEXTVAL, p_emp_id,
v_old_salary, p_new_salary,
SYSDATE
);
v_result := '薪资从 ' || v_old_salary || ' 更新为 ' || p_new_salary;
RETURN v_result;
EXCEPTION
WHEN OTHERS THEN
RETURN '错误: ' || SQLERRM;
END update_salary_with_log;
/九、确定性函数与并行处理
1. 确定性函数(DETERMINISTIC)
对于相同输入总是返回相同结果的函数可以声明为DETERMINISTIC，提高性能：
CREATE OR REPLACE FUNCTION calculate_bonus(
p_salary IN NUMBER,
p_years IN NUMBER
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN p_salary * p_years * 0.01;
END calculate_bonus;
/2. 并行处理(PARALLEL_ENABLE)
CREATE OR REPLACE FUNCTION process_large_data(
p_input IN NUMBER
) RETURN NUMBER PARALLEL_ENABLE
IS
BEGIN
-- 复杂计算
RETURN p_input * 2;
END process_large_data;
/十、管道表函数(PIPELINED)
返回结果集的函数，可以逐行返回数据：
1. 基本管道函数
-- 创建集合类型
CREATE OR REPLACE TYPE emp_table_type AS TABLE OF emp_obj;
/
-- 创建管道函数
CREATE OR REPLACE FUNCTION get_dept_employees(
p_dept_id IN NUMBER
) RETURN emp_table_type PIPELINED
IS
BEGIN
FOR emp_rec IN (
SELECT employee_id, first_name, last_name, salary, hire_date
FROM employees
WHERE department_id = p_dept_id
) LOOP
PIPE ROW(emp_obj(
emp_rec.employee_id,
emp_rec.first_name || ' ' || emp_rec.last_name,
emp_rec.salary,
emp_rec.hire_date
));
END LOOP;
RETURN;
END get_dept_employees;
/
-- 调用示例
SELECT * FROM TABLE(get_dept_employees(10));2. 带参数的管道函数
CREATE OR REPLACE FUNCTION filter_employees(
p_min_sal IN NUMBER DEFAULT 0,
p_max_sal IN NUMBER DEFAULT 999999
) RETURN emp_table_type PIPELINED
IS
BEGIN
FOR emp_rec IN (
SELECT employee_id, first_name, last_name, salary, hire_date
FROM employees
WHERE salary BETWEEN p_min_sal AND p_max_sal
) LOOP
PIPE ROW(emp_obj(
emp_rec.employee_id,
emp_rec.first_name || ' ' || emp_rec.last_name,
emp_rec.salary,
emp_rec.hire_date
));
END LOOP;
RETURN;
END filter_employees;
/
-- 调用示例
SELECT * FROM TABLE(filter_employees(5000, 10000));十一、函数的调用方式
1. 在PL/SQL中调用
DECLARE
v_name VARCHAR2(100);
v_tax NUMBER;
BEGIN
v_name := get_employee_name(100);
v_tax := calculate_tax(8000);
DBMS_OUTPUT.PUT_LINE(v_name || '的税额: ' || v_tax);
END;
/2. 在SQL语句中调用
-- 在SELECT中使用
SELECT employee_id,
get_employee_name(employee_id) AS full_name,
calculate_bonus(salary, 5) AS bonus
FROM employees
WHERE department_id = 10;
-- 在WHERE条件中使用
SELECT * FROM employees
WHERE calculate_tax(salary) > 1000;
-- 在ORDER BY中使用
SELECT * FROM employees
ORDER BY calculate_bonus(salary, years_of_service) DESC;3. 在DML语句中调用
-- 在INSERT中使用
INSERT INTO employee_audit
VALUES (audit_seq.NEXTVAL,
get_employee_name(100),
SYSDATE);
-- 在UPDATE中使用
UPDATE employees
SET salary = salary + calculate_bonus(salary, years_of_service)
WHERE employee_id = 100;十二、函数的管理与维护
1. 查看函数定义
-- 查看源代码
SELECT text FROM user_source
WHERE name = 'GET_EMPLOYEE_NAME'
ORDER BY line;
-- 查看函数状态
SELECT object_name, status
FROM user_objects
WHERE object_type = 'FUNCTION';2. 重新编译函数
ALTER FUNCTION function_name COMPILE;3. 删除函数
DROP FUNCTION function_name;4. 函数权限控制
-- 授予执行权限
GRANT EXECUTE ON get_employee_name TO user1;
-- 创建公有同义词
CREATE PUBLIC SYNONYM get_emp_name FOR hr.get_employee_name;十三、函数设计最佳实践
1. 单一职责原则：
- 每个函数只完成一个明确的任务
2. 合理命名：
- 使用动词+名词形式，如
calculate_tax、get_employee_name
3. 参数设计：
- 限制参数数量(通常不超过5个)
- 使用默认参数减少重载
- 避免使用OUT参数(影响可读性)
4. 错误处理：
- 捕获并处理预期异常
- 对意外异常提供有意义的错误信息
5. 性能考虑：
- 对频繁调用的函数使用DETERMINISTIC
- 避免在函数中执行复杂SQL
- 考虑使用管道函数处理大数据集
6. 文档注释：
CREATE OR REPLACE FUNCTION calculate_tax(
p_salary IN NUMBER -- 输入:员工薪资
) RETURN NUMBER -- 返回:计算出的税额
IS
/*
* 功能: 根据薪资计算应缴税额
* 逻辑:
* - 5000以下: 免税
* - 5000-10000: 10%
* - 10000以上: 20%
* 创建: 2023-01-01
* 作者: DBA
*/
BEGIN
-- 函数实现
END;
/十四、高级函数特性（了解）
1. 函数重载(在包中)
关于包的内容会在后面详细讲到
CREATE OR REPLACE PACKAGE employee_pkg AS
-- 根据ID获取员工名
FUNCTION get_name(p_emp_id IN NUMBER) RETURN VARCHAR2;
-- 根据邮箱获取员工名
FUNCTION get_name(p_email IN VARCHAR2) RETURN VARCHAR2;
END employee_pkg;
/
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
FUNCTION get_name(p_emp_id IN NUMBER) RETURN VARCHAR2 IS
v_name VARCHAR2(100);
BEGIN
SELECT first_name || ' ' || last_name INTO v_name
FROM employees
WHERE employee_id = p_emp_id;
RETURN v_name;
END;
FUNCTION get_name(p_email IN VARCHAR2) RETURN VARCHAR2 IS
v_name VARCHAR2(100);
BEGIN
SELECT first_name || ' ' || last_name INTO v_name
FROM employees
WHERE email = p_email;
RETURN v_name;
END;
END employee_pkg;
/2. 递归函数
CREATE OR REPLACE FUNCTION factorial(
p_num IN NUMBER
) RETURN NUMBER
IS
BEGIN
IF p_num <= 1 THEN
RETURN 1;
ELSE
RETURN p_num * factorial(p_num - 1);
END IF;
END factorial;
/
-- 调用示例
SELECT factorial(5) FROM dual; -- 返回1203. 使用上下文函数(12c+)
CREATE OR REPLACE FUNCTION get_session_user
RETURN VARCHAR2
ACCESSED UNDER CURRENT_USER
IS
BEGIN
RETURN SYS_CONTEXT('USERENV', 'SESSION_USER');
END;
/自定义函数是Oracle PL/SQL编程的核心组件，合理设计和使用函数可以显著提高代码的可重用性、可维护性和性能。