Oracle数据库数据编程SQL＜3.4 PL/SQL 自定义函数＞

自定义函数是Oracle数据库中可重用的PL/SQL代码块，它接受参数、执行操作并返回一个值。函数可以在SQL语句中直接调用，极大增强了SQL的处理能力。

分类			调用
没有参数	没有输入输出		写SQL不加参数
有参数	有输入无输出		写SQL加参数
	无输入有输出	输出变量	写PL/SQL匿名块或者自定义函数、存储过程调用
		输出动态游标
	有输入有输出		写PL/SQL匿名块或者自定义函数、存储过程调用

目录

一、函数基本结构

1. 创建语法：必须有返回值

2. 基本示例

二、函数参数模式

三、无参数：没有输入输出

四、有参数：只输入IN--用select调用

五、有参数：有输出OUT--plsql调用

1、输出变量

2、输出游标

六、有参数：有输入输出值IN OUT--必须用宏代换&

七、函数返回值

1. 返回标量值

2. 返回复合类型

（1）使用%ROWTYPE

（2）使用自定义类型

八、函数中的DML操作

示例：审计函数

九、确定性函数与并行处理

1. 确定性函数(DETERMINISTIC)

2. 并行处理(PARALLEL_ENABLE)

十、管道表函数(PIPELINED)

1. 基本管道函数

2. 带参数的管道函数

十一、函数的调用方式

1. 在PL/SQL中调用

2. 在SQL语句中调用

3. 在DML语句中调用

十二、函数的管理与维护

1. 查看函数定义

2. 重新编译函数

3. 删除函数

4. 函数权限控制

十三、函数设计最佳实践

1. 单一职责原则：

2. 合理命名：

3. 参数设计：

4. 错误处理：

5. 性能考虑：

6. 文档注释：

十四、高级函数特性（了解）

1. 函数重载(在包中)

2. 递归函数

3. 使用上下文函数(12c+)

---

一、函数基本结构

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]
BEGINexecutable_section[RETURN value;]
[EXCEPTIONexception_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
ISv_full_name VARCHAR2(100);
BEGINSELECT first_name || ' ' || last_name INTO v_full_nameFROM employeesWHERE employee_id = p_emp_id;RETURN v_full_name;
EXCEPTIONWHEN NO_DATA_FOUND THENRETURN '员工不存在';
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 asbeginif v1 > 0 thenreturn 1;/*这里的意思是输出一个表*/elsif v1 < 0 thenreturn - 1;elsereturn 0;end if;end;
/
--调用
select mysign(23333333) from dual--只输入用select
/*############################################################################*/【例题】
/*=====================================================================================*/
【1】制作一个函数，输入一个数值，返回它的相反数
--创建
CREATE OR REPLACE FUNCTION FUN_XFS(V1 NUMBER) RETURN NUMBER AS
BEGINIF V1 > 0 THENRETURN '-' || V1;ELSIF V1 < 0 THENRETURN ABS(V1);ELSERETURN 0;END IF;
END;
/
--引用
SELECT fun_xfs(0) FROM dual--调用
CREATE OR REPLACE FUNCTION FUN_XFS1(V1 NUMBER) RETURN NUMBER AS
BEGINRETURN(-V1);
END;
/
--调用
SELECT fun_xfs1(-1) FROM dual
/*=====================================================================================*/
【2】编写一个函数，输入一个员工编号，返回其工资等级
--创建
create or replace function 工资等级1(v1 number) return number asv_g salgrade.grade%type;
beginselect b.gradeinto v_gfrom emp a, salgrade bwhere a.empno = v1and 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 asv_g salgrade.grade%type;
beginselect b.gradeinto v_gfrom emp ainner join salgrade bon a.sal between b.losal and b.hisalwhere 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 asv2 number;
beginselect count(trunc(v1, 'mm') - 1 + level)into v2from dualwhere 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 asv_count number;
beginselect count(mm)into v_countfrom (select level + (trunc(v1, 'mm') - 1) mmfrom dualconnect 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 ASV3 NUMBER;
BEGINSELECT COUNT(*)INTO V3FROM EMPWHERE DEPTNO = V1AND 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
ISv_tax_amount NUMBER;
BEGINIF p_salary <= 5000 THENp_tax_rate := 0;ELSIF p_salary <= 10000 THENp_tax_rate := 0.1;ELSEp_tax_rate := 0.2;END IF;v_tax_amount := p_salary * p_tax_rate;RETURN v_tax_amount;
END calculate_tax;
/-- 调用示例
DECLAREv_rate NUMBER;v_tax NUMBER;
BEGINv_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 asv_count number;
beginselect sum(sal), count(*) into v_sum, v_count from emp where deptno = v1;return v_count;
end;
-- 调用
declarev_sum   number;v_count number;
beginv_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 asv_j varchar2(20);beginselect e.sal,e.job into v_s,v_j from emp e where e.empno = v1;return v_j;end;
-- 调用declarev_s number;v_j varchar2(20);beginv_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 ASV3 NUMBER;
BEGINSELECT LOSAL, HISAL INTO V2, V3 FROM SALGRADE WHERE GRADE = V1;RETURN V3;
END;
/
-- 调用示例
DECLAREV1 NUMBER := &A;V2 NUMBER;V3 NUMBER;
BEGINV3 := 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 ascur_emp sys_refcursor;
beginopen cur_emp forselect * from emp where deptno =v1;return cur_emp;
end;
--调用                                     
declarecur_a sys_refcursor;v_emp emp%rowtype;
begincur_a := fun4(&a);loopfetch cur_ainto 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;
beginopen cur_emp forselect * from emp where deptno = v1;open cur_dept forselect * from dept where deptno = v1;return cur_emp;
end;
--调用
declarecur_emp  sys_refcursor;cur_dept sys_refcursor;v_emp    emp%rowtype;v_dept   dept %rowtype;
begincur_emp := fun5(20, cur_dept);loopfetch cur_empinto v_emp;exit when cur_emp%notfound;dbms_output.put_line(v_emp.empno || v_emp.ename || v_emp.deptno);end loop;loopfetch 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 ascur_2 sys_refcursor;
beginopen cur_1 forselect empno, ename from emp where mgr = v1;open cur_2 forselect empno, hiredate from emp where empno = v1;return cur_2;
end;
--调用
declarecur_1 sys_refcursor;cur_2 sys_refcursor;v1    emp.empno%type;v2    varchar2(20);v3    number;v4    date;
begincur_2 := fun6(&a, cur_1);loopfetch cur_1into v1, v2;exit when cur_1%notfound;dbms_output.put_line('下级编号:' || v1 || '下级姓名:' || v2);end loop;loopfetch cur_2into 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 asv_name emp.ename%type;
beginupdate emp001set sal = sal + v_jiawhere empno = v_empnoreturning ename, sal into v_name, v_jia;return v_name;
end;
--调用
declarev_empno emp.empno%type := &empno;---必须用宏代换v_name  emp.ename%type;v_jia   emp.sal%type := &jia;
beginv_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
ISv_dept_name departments.department_name%TYPE;
BEGINSELECT department_name INTO v_dept_nameFROM departmentsWHERE department_id = p_dept_id;RETURN v_dept_name;
EXCEPTIONWHEN NO_DATA_FOUND THENRETURN '部门不存在';
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
ISv_emp employees%ROWTYPE;
BEGINSELECT * INTO v_empFROM employeesWHERE employee_id = p_emp_id;RETURN v_emp;
EXCEPTIONWHEN NO_DATA_FOUND THENRETURN 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
ISv_emp emp_obj;
BEGINSELECT emp_obj(employee_id, first_name || ' ' || last_name,salary,hire_date) INTO v_empFROM employeesWHERE 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
ISv_old_salary employees.salary%TYPE;v_result VARCHAR2(100);
BEGIN-- 获取旧薪资SELECT salary INTO v_old_salaryFROM employeesWHERE employee_id = p_emp_id;-- 更新薪资UPDATE employeesSET salary = p_new_salaryWHERE 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;
EXCEPTIONWHEN OTHERS THENRETURN '错误: ' || 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
BEGINRETURN 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
BEGINFOR emp_rec IN (SELECT employee_id, first_name, last_name, salary, hire_dateFROM employeesWHERE department_id = p_dept_id) LOOPPIPE 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
BEGINFOR emp_rec IN (SELECT employee_id, first_name, last_name, salary, hire_dateFROM employeesWHERE salary BETWEEN p_min_sal AND p_max_sal) LOOPPIPE 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中调用

DECLAREv_name VARCHAR2(100);v_tax NUMBER;
BEGINv_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 ASFUNCTION get_name(p_emp_id IN NUMBER) RETURN VARCHAR2 ISv_name VARCHAR2(100);BEGINSELECT first_name || ' ' || last_name INTO v_nameFROM employeesWHERE employee_id = p_emp_id;RETURN v_name;END;FUNCTION get_name(p_email IN VARCHAR2) RETURN VARCHAR2 ISv_name VARCHAR2(100);BEGINSELECT first_name || ' ' || last_name INTO v_nameFROM employeesWHERE email = p_email;RETURN v_name;END;
END employee_pkg;
/

2. 递归函数

CREATE OR REPLACE FUNCTION factorial(p_num IN NUMBER
) RETURN NUMBER
IS
BEGINIF p_num <= 1 THENRETURN 1;ELSERETURN p_num * factorial(p_num - 1);END IF;
END factorial;
/-- 调用示例
SELECT factorial(5) FROM dual;  -- 返回120

3. 使用上下文函数(12c+)

CREATE OR REPLACE FUNCTION get_session_user
RETURN VARCHAR2
ACCESSED UNDER CURRENT_USER
IS
BEGINRETURN SYS_CONTEXT('USERENV', 'SESSION_USER');
END;
/

自定义函数是Oracle PL/SQL编程的核心组件，合理设计和使用函数可以显著提高代码的可重用性、可维护性和性能。