Oracle 存储过程与函数详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

存储过程与函数是 PL/SQL 命名块[1]：

详细见：Oracle 存储过程与函数。

---

2. 过程

2.1 创建

CREATE OR REPLACE PROCEDURE hire_emp(
  p_name IN VARCHAR2,
  p_salary IN NUMBER,
  p_dept_id IN NUMBER,
  p_emp_id OUT NUMBER
) IS
  v_id NUMBER;
BEGIN
  SELECT emp_seq.NEXTVAL INTO v_id FROM dual;
  
  INSERT INTO employees (id, name, salary, dept_id, hire_date)
  VALUES (v_id, p_name, p_salary, p_dept_id, SYSDATE);
  
  p_emp_id := v_id;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END hire_emp;
/

2.2 调用

DECLARE
  v_id NUMBER;
BEGIN
  hire_emp('Alice', 5000, 10, v_id);
  DBMS_OUTPUT.PUT_LINE('Hired: ' || v_id);
END;
/

-- SQL 中不可调用过程
-- 但在 PL/SQL 块中可以

---

3. 函数

3.1 创建

CREATE OR REPLACE FUNCTION get_emp_count(p_dept_id NUMBER) RETURN NUMBER IS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees WHERE dept_id = p_dept_id;
  RETURN v_count;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END get_emp_count;
/

3.2 调用

-- SQL 中
SELECT get_emp_count(10) FROM dual;

-- PL/SQL 中
DECLARE
  v_count NUMBER;
BEGIN
  v_count := get_emp_count(10);
  DBMS_OUTPUT.PUT_LINE(v_count);
END;
/

3.3 RESULT CACHE

CREATE OR REPLACE FUNCTION get_dept_name(p_id NUMBER) RETURN VARCHAR2
  RESULT_CACHE RELIES_ON (departments)
IS
  v_name VARCHAR2(100);
BEGIN
  SELECT dept_name INTO v_name FROM departments WHERE id = p_id;
  RETURN v_name;
END;
/

---

4. 参数

4.1 模式

模式	说明
IN	输入（默认）
OUT	输出
IN OUT	输入输出

4.2 NOCOPY

PROCEDURE process(p_data IN OUT NOCOPY big_collection) IS
BEGIN
  ...
END;

4.3 默认值

PROCEDURE create_emp(
  p_name IN VARCHAR2,
  p_salary IN NUMBER DEFAULT 5000,
  p_dept_id IN NUMBER DEFAULT 10
) IS ...

-- 调用
create_emp('Alice');
create_emp('Bob', 6000);
create_emp(p_name => 'Charlie', p_dept_id => 20);

4.4 调用方式

-- 位置
hire_emp('Alice', 5000, 10, v_id);

-- 命名
hire_emp(p_name => 'Alice', p_salary => 5000, p_dept_id => 10, p_emp_id => v_id);

-- 混合
hire_emp('Alice', p_salary => 5000, p_dept_id => 10, p_emp_id => v_id);

---

5. 过程 vs 函数

项	过程	函数
返回值	可无	必须
SQL 调用	不可	可
DML	可	可（不推荐函数中）
事务控制	可	不推荐
调用	PL/SQL	SQL / PL/SQL

---

6. 权限

6.1 执行

GRANT EXECUTE ON hire_emp TO user1;
GRANT EXECUTE ON get_emp_count TO PUBLIC;

6.2 调用

-- Schema 限定
EXEC scott.hire_emp(...);

6.3 同义词

CREATE PUBLIC SYNONYM hire_emp FOR scott.hire_emp;

---

7. AUTHID

7.1 DEFINER（默认）

CREATE PROCEDURE p AUTHID DEFINER IS
-- 使用创建者权限

7.2 CURRENT_USER

CREATE PROCEDURE p AUTHID CURRENT_USER IS
-- 使用调用者权限

7.3 选择

- DEFINER：集中权限
- CURRENT_USER：用户隔离

---

8. 事务

8.1 COMMIT/ROLLBACK

CREATE PROCEDURE transfer(...) IS
BEGIN
  -- DML
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

8.2 自治事务

CREATE PROCEDURE log_action(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO log VALUES (p_msg, SYSTIMESTAMP);
  COMMIT;
END;
/

---

9. 异常

9.1 抛出

CREATE PROCEDURE hire_emp(...) IS
BEGIN
  IF p_salary <= 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary must be positive');
  END IF;
  ...
END;

9.2 处理

EXCEPTION
  WHEN NO_DATA_FOUND THEN ...
  WHEN OTHERS THEN
    log_error(...);
    RAISE;

详细见：Oracle PL/SQL 异常处理详解。

---

10. 重新编译

ALTER PROCEDURE hire_emp COMPILE;
ALTER FUNCTION get_emp_count COMPILE;

-- 查看
SELECT object_name, object_type, status 
FROM user_objects 
WHERE object_type IN ('PROCEDURE', 'FUNCTION');

---

11. 查看

11.1 源代码

SELECT name, type, line, text 
FROM user_source 
WHERE name = 'HIRE_EMP' 
ORDER BY line;

11.2 参数

SELECT object_name, argument_name, data_type, in_out, position
FROM user_arguments
WHERE object_name = 'HIRE_EMP'
ORDER BY position;

11.3 错误

SHOW ERRORS PROCEDURE hire_emp;

SELECT name, type, line, position, text 
FROM user_errors 
WHERE name = 'HIRE_EMP';

---

12. 删除

DROP PROCEDURE hire_emp;
DROP FUNCTION get_emp_count;

---

13. 调试

13.1 DBMS_OUTPUT

CREATE PROCEDURE p IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Start');
  ...
  DBMS_OUTPUT.PUT_LINE('End');
END;
/

SET SERVEROUTPUT ON;
EXEC p;

13.2 DBMS_TRACE

ALTER SESSION SET plsql_debug = TRUE;

---

14. 性能

14.1 BULK

PROCEDURE batch_insert IS
  TYPE id_tab IS TABLE OF NUMBER;
  v_ids id_tab;
BEGIN
  SELECT id BULK COLLECT INTO v_ids FROM ...;
  FORALL i IN 1..v_ids.COUNT
    INSERT INTO t VALUES (v_ids(i));
END;

详细见：Oracle PL/SQL 性能优化详解。

---

15. 安全

15.1 SQL 注入

-- 差
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = ' || p_id;

-- 好
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = :id' USING p_id;

详细见：Oracle PL/SQL 动态 SQL 详解。

15.2 权限

- 最小权限
- DEFINER 谨慎
- AUDIT

---

16. 常见坑与排错

16.1 ORA-06550

- 编译错误
- SHOW ERRORS

16.2 ORA-06508

- 无效对象
- 重新编译

16.3 函数 DML

- 函数中 DML
- PRAGMA AUTONOMOUS_TRANSACTION
- 或改过程

16.4 状态

- INVALID
- 依赖失效
- 重新编译

---

17. 最佳实践

1. 命名规范：清晰
2. 参数模式：明确
3. 默认值：便利
4. 异常处理：完整
5. RESULT_CACHE：缓存
6. AUTHID：权限
7. NOCOPY：大参数
8. BULK：批量
9. 绑定变量：安全
10. 文档：注释

---

18. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “Procedures and Functions” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/procedures-and-functions.html