在Oracle数据库中,存储过程和存储函数都属于命名PL/SQL块,它们被编译后保存在数据库服务器端,可以由应用程序、作业调度或触发器反复调用。虽然二者共享变量声明、异常处理、游标和事务访问等PL/SQL能力,但在返回值、调用位置和事务控制方面存在明显边界。如果只是按照印象随意选择,很容易在SQL查询中调用带DML的过程或试图从函数返回多个结果集,最终遇到ORA-06503或ORA-14551等错误。下面从语法结构、执行限制、事务处理和实际选择几个维度展开。

一、创建语法与参数模式的区别
存储过程的语法相对简单,CREATE OR REPLACE PROCEDURE后跟过程名和可选的参数列表。参数可以声明为IN、OUT或IN OUT三种模式,过程体不需要指定返回类型。一个典型的过程定义如下:
CREATE OR REPLACE PROCEDURE update_salary (
p_empno IN NUMBER,
p_new_sal IN NUMBER,
p_updated OUT VARCHAR2
)
IS
BEGIN
UPDATE emp SET sal = p_new_sal
WHERE empno = p_empno;
p_updated := 'SUCCESS';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
p_updated := SQLERRM;
ROLLBACK;
END update_salary;
/
这里p_updated在调用后可以把处理状态返回给调用方。过程本身没有RETURN子句,执行完最后一条语句后自然结束。可以看到,过程更强调完成一批操作,返回值并不是语法上的强制要求。
存储函数则必须声明返回类型,函数体至少包含一条RETURN语句,把结果返回给调用环境。例如一个根据员工编号计算年度收入的函数:
CREATE OR REPLACE FUNCTION calc_annual_income (
p_empno IN NUMBER
) RETURN NUMBER
IS
v_sal NUMBER;
v_comm NUMBER;
BEGIN
SELECT sal, NVL(comm, 0)
INTO v_sal, v_comm
FROM emp
WHERE empno = p_empno;
RETURN (v_sal + v_comm) * 12;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END calc_annual_income;
/
从语法上能看出,函数更像一个可复用的表达式,它必须给出确定类型的结果;而过程更像一个动作单元,重点在完成一批操作。需要注意,函数虽然只能通过RETURN返回一个值,但也可以声明OUT参数,只是这种用法在SQL语句中会受到严格限制。
从工程角度看,参数的IN/OUT设计直接影响接口清晰度。如果某个模块只需要读取一些条件并返回一个计算结果,函数加上IN参数通常更直观;如果模块需要同时输出多个处理状态、错误信息或游标,则过程配合OUT参数更容易维护。实践中常见做法是函数只保留IN参数并返回不可分割的业务结果,当需要返回多列或数据集时,优先使用过程并通过SYS_REFCURSOR类型的OUT参数传出,避免函数在SQL中触发纯度限制。
二、调用位置与SQL中的使用限制
存储过程是PL/SQL可执行语句,不能直接出现在SQL查询中。可以使用CALL或EXEC命令在SQL*Plus、SQLcl或客户端中执行,也可以在匿名PL/SQL块中调用。示例:
BEGIN
update_salary(p_empno => 7369, p_new_sal => 9000, p_updated => :status);
END;
/
存储函数既可以像过程一样在PL/SQL中调用并赋值给变量,也可以作为表达式的一部分直接在SQL语句中使用。例如:
SELECT empno, ename, calc_annual_income(empno) AS annual_income FROM emp WHERE calc_annual_income(empno) > 20000;
这里函数被调用两次,一次在SELECT列表,一次在WHERE中。这种便利性正是函数与过程最直观的区别。但SQL引擎对函数调用有纯度要求,如果在函数体内执行INSERT、UPDATE、DELETE或包含COMMIT、ROLLBACK,直接放在SELECT中会触发ORA-14551: cannot perform a DML operation inside a query。举例:
CREATE OR REPLACE FUNCTION bad_func (p_id NUMBER) RETURN VARCHAR2
IS
BEGIN
DELETE FROM audit_log WHERE id = p_id;
RETURN 'OK';
END bad_func;
/
SELECT bad_func(100) FROM dual;
-- ORA-14551: cannot perform a DML operation inside a query
这个限制保证了查询期间事务状态和数据视图的稳定性,也是很多开发者容易踩到的地方。如果确实需要在查询中执行带写操作的逻辑,可以结合自治事务,但自治事务不能访问查询所在事务未提交的数据,而且会带来一致性风险,一般不推荐。
除了DML限制,函数在SQL中还被要求不能修改任何数据库状态或包变量,这就是WNDS和RNPS约束。Oracle提供PRAGMA RESTRICT_REFERENCES可以在声明函数时强制纯度级别,例如:
CREATE OR REPLACE FUNCTION get_dept_name (p_deptno NUMBER) RETURN VARCHAR2
IS
v_name VARCHAR2(40);
BEGIN
SELECT dname INTO v_name FROM dept WHERE deptno = p_deptno;
RETURN v_name;
END get_dept_name;
/
SELECT get_dept_name(deptno) FROM emp;
如果函数只是读取数据,通常可以安全地用于SQL。对于需要返回结果集的过程,函数不能直接返回多行,除非使用集合类型或管道函数,这两个方向开发成本明显高于用过程传REF CURSOR。因此看到函数返回多行需求时,建议将其设计为过程,避免强行把简单需求复杂化。
三、事务控制与异常处理
事务控制是存储函数与过程差异较大的部分。PL/SQL块可以使用COMMIT和ROLLBACK显式结束事务。存储过程通常承担数据修改任务,在过程体内部提交或回滚很常见,比如批量更新后记录成功或失败。但存储函数如果被SQL语句调用,则不能使用COMMIT或ROLLBACK。即便函数在PL/SQL中单独调用时某些版本允许事务控制,也不建议这么做,因为函数一旦被其他SQL间接调用,就会受到限制,导致程序行为不一致。
CREATE OR REPLACE PROCEDURE batch_close_orders IS
BEGIN
UPDATE orders SET status = 'CLOSED' WHERE status = 'OPEN';
COMMIT;
END batch_close_orders;
/
而函数应把事务决策交给外层调用者,只负责计算和返回。例如函数只计算折扣:
CREATE OR REPLACE FUNCTION get_discount (p_amount NUMBER) RETURN NUMBER
IS
BEGIN
IF p_amount > 10000 THEN
RETURN 0.15;
ELSE
RETURN 0.05;
END IF;
END get_discount;
/
这样外层过程可以在获得多个计算结果后统一提交或回滚,保持事务边界清晰。异常处理方面,两者都可以使用EXCEPTION WHEN ... THEN捕获预定义或自定义异常。函数与过程的区别在于函数必须在异常分支中也提供返回值,否则运行时会出现ORA-06503: PL/SQL: Function returned without value。如果一个查询中函数抛出未处理的NO_DATA_FOUND或其他异常,SQL语句整体会失败。相较之下,过程遇到异常可以选择记录日志后继续循环或重新抛出,没有强制返回值的压力。
有时候确实需要在函数中记录日志或写审计表,又想保持函数可被SQL调用,一个技术手段是使用PRAGMA AUTONOMOUS_TRANSACTION。它让函数在独立事务中执行DML并提交,不会干扰外层查询事务。
CREATE OR REPLACE FUNCTION log_access (p_username IN VARCHAR2) RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO access_log(username, log_time) VALUES (p_username, SYSDATE);
COMMIT;
RETURN 1;
END log_access;
/
但要注意自治事务无法读取主事务中尚未提交的数据,而且一旦被高频调用,可能在日志表上产生大量提交,影响性能。因此仅适合审计、日志等低耦合场景,不应该用自治事务实现核心业务写入。
四、权限、依赖关系与调试
过程和函数创建后都作为对象存储在USER_OBJECTS中,源代码可通过USER_SOURCE查询。编译错误用SHOW ERRORS查看。两者都受CREATE PROCEDURE系统权限约束,若创建带有INVOKER RIGHTS的AUTHID子句,还需要考虑调用者权限。
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type IN ('PROCEDURE', 'FUNCTION');
调用权限方面,过程和函数均需授予EXECUTE权限。例如:
GRANT EXECUTE ON update_salary TO scott; GRANT EXECUTE ON calc_annual_income TO scott;
函数如果要在其他用户的SQL语句中使用,光有EXECUTE权限还不够,需要函数在查询所涉及的对象上具有相应权限。如果使用定义者权限,则函数以定义者身份访问表;如果使用AUTHID CURRENT_USER,则以调用者身份访问,需要调用者具备表权限。这一点在跨用户调用时容易忽略。
当表结构发生改变,如删除列或修改类型,函数和过程可能变为INVALID状态。可以查询USER_DEPENDENCIES查看依赖链,重新编译:
ALTER PROCEDURE update_salary COMPILE; ALTER FUNCTION calc_annual_income COMPILE;
与匿名PL/SQL块相比,命名存储程序首次执行时需要加载编译后的代码,但后续调用会跳过解析,因此性能更好。函数在SQL中被大量调用时,如果逻辑复杂且使用了PL/SQL逐行处理,可能会产生SQL引擎与PL/SQL引擎之间的上下文切换开销,这也是进行性能优化时需关注的点。
五、选择标准与工程实践
是否使用过程或函数,核心看语义。函数表达的是输入到输出的映射,适合计算薪资、折扣、校验ID格式、转换编码等纯计算或只读查询。过程表达的是业务流程,适合批量更新、多步骤数据清洗、发送接口请求、调度作业等。如果一个模块需要输出多个值,函数虽然可通过OUT参数实现,但会让接口显得别扭,也失去在SQL中直接使用的灵活性,此时应使用过程。
举例:一个订单结算流程需要验证库存、冻结金额、生成发货单,同时返回错误码和订单号,这个过程应定义为存储过程;而其中根据订单金额计算运费的小逻辑,则可以单独定义为函数,由过程调用。
CREATE OR REPLACE FUNCTION calc_freight (p_amount NUMBER) RETURN NUMBER
IS
BEGIN
RETURN ROUND(p_amount * 0.03, 2);
END calc_freight;
/
CREATE OR REPLACE PROCEDURE settle_order (
p_order_id IN NUMBER,
p_result OUT VARCHAR2,
p_ship_no OUT VARCHAR2
)
IS
v_amount NUMBER;
BEGIN
SELECT amount INTO v_amount FROM orders WHERE order_id = p_order_id;
UPDATE inventory SET stock = stock - 1 WHERE order_id = p_order_id;
p_ship_no := 'SN' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS');
p_result := 'OK';
COMMIT;
END settle_order;
/
这种组合方式让函数保持无副作用,过程负责编排事务和状态变化,符合单一职责原则。将业务逻辑放在数据库层的命名块中,可以减少网络往返次数,比客户端逐条发送SQL效率高。但如果函数在SQL中对大量行被调用,函数内部的SELECT或复杂PL/SQL逻辑可能拖慢查询。一个常见优化是将小函数改写成内联视图或直接写在SQL表达式中,或使用DETERMINISTIC关键字声明确定性函数,让Oracle在相同输入时缓存结果。但DETERMINISTIC只能用于查询期间的缓存优化,不能真正保证业务上的确定性,使用时需要保证逻辑确实不依赖易变数据。
CREATE OR REPLACE FUNCTION get_tax_rate (p_region NUMBER) RETURN NUMBER
DETERMINISTIC
IS
BEGIN
RETURN 0.13;
END get_tax_rate;
/
总结来说,存储过程与存储函数不是竞争关系,而是PL/SQL编程中面向不同职责的两种封装形式。掌握它们在语法、调用位置、事务限制和SQL纯度上的差异后,再结合具体业务边界做选择,能有效减少运行期错误,也能让数据库层的接口更加清晰、易于维护。
Oracle存储函数存储过程PL/SQL修改时间:2026-11-03 09:03:55