Oracle 存储过程与函数

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

1. 概述

存储过程与函数是 PL/SQL 程序单元[1]：

区别：

过程：执行操作
函数：返回值

2. 存储过程

2.1 创建

CREATE OR REPLACE PROCEDURE hire_employee(
  p_name IN employees.name%TYPE,
  p_salary IN employees.salary%TYPE,
  p_dept_id IN employees.dept_id%TYPE,
  p_id OUT employees.id%TYPE
) IS
  v_count NUMBER;
BEGIN
  -- 检查
  SELECT COUNT(*) INTO v_count FROM departments WHERE id = p_dept_id;
  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Department not found');
  END IF;
  
  -- 插入
  INSERT INTO employees (id, name, salary, dept_id)
  VALUES (emp_seq.NEXTVAL, p_name, p_salary, p_dept_id)
  RETURNING id INTO p_id;
  
  -- 日志
  INSERT INTO emp_log (emp_id, action, action_time)
  VALUES (p_id, 'HIRE', SYSTIMESTAMP);
  
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END hire_employee;
/

2.2 调用

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

-- SQL 中不能直接调用有 OUT 参数的过程

2.3 参数模式

IN：输入（默认）
OUT：输出
IN OUT：双向

2.4 默认值

CREATE OR REPLACE PROCEDURE my_proc(
  p_a IN NUMBER,
  p_b IN NUMBER DEFAULT 10
) IS ...

-- 调用
EXEC my_proc(1);
EXEC my_proc(1, 20);
EXEC my_proc(p_a => 1, p_b => 20);

3. 函数

3.1 创建

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

3.2 调用

-- SQL 中
SELECT get_emp_count(10) FROM dual;
SELECT dept_id, get_emp_count(dept_id) AS emp_count
FROM departments;

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

3.3 DETERMINISTIC

CREATE OR REPLACE FUNCTION calc_tax(p_amount NUMBER)
RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN p_amount * 0.1;
END;
/

-- 用于函数索引
CREATE INDEX idx_tax ON orders(calc_tax(amount));

3.4 RESULT_CACHE

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

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

4. AUTHID

4.1 DEFINER（默认）

CREATE OR REPLACE PROCEDURE my_proc AUTHID DEFINER IS ...
-- 以定义者权限

4.2 CURRENT_USER

CREATE OR REPLACE PROCEDURE my_proc AUTHID CURRENT_USER IS ...
-- 以调用者权限

5. 局部过程

CREATE OR REPLACE PROCEDURE outer_proc IS
  -- 局部过程
  PROCEDURE inner_proc(p_val NUMBER) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Inner: ' || p_val);
  END;
  
  FUNCTION inner_func(p_val NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_val * 2;
  END;
BEGIN
  inner_proc(10);
  DBMS_OUTPUT.PUT_LINE('Func: ' || inner_func(10));
END outer_proc;
/

6. 重载

CREATE OR REPLACE PACKAGE calc_pkg AS
  FUNCTION add(a NUMBER, b NUMBER) RETURN NUMBER;
  FUNCTION add(a VARCHAR2, b VARCHAR2) RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY calc_pkg AS
  FUNCTION add(a NUMBER, b NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN a + b;
  END;
  
  FUNCTION add(a VARCHAR2, b VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN a || b;
  END;
END;
/

7. 递归

CREATE OR REPLACE FUNCTION factorial(n NUMBER)
RETURN NUMBER IS
BEGIN
  IF n <= 1 THEN
    RETURN 1;
  ELSE
    RETURN n * factorial(n - 1);
  END IF;
END;
/

SELECT factorial(5) FROM dual;  -- 120

8. 异常处理

CREATE OR REPLACE PROCEDURE safe_proc IS
  v_name employees.name%TYPE;
BEGIN
  SELECT name INTO v_name FROM employees WHERE id = 100;
  DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Not found');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Too many');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RAISE;
END;
/

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

9. 事务

CREATE OR REPLACE PROCEDURE transfer(
  p_from NUMBER, p_to NUMBER, p_amount NUMBER
) IS
BEGIN
  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
  
  INSERT INTO transaction_log (from_id, to_id, amount, time)
  VALUES (p_from, p_to, p_amount, SYSTIMESTAMP);
  
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

10. 游标

CREATE OR REPLACE PROCEDURE process_employees IS
  CURSOR c_emp IS SELECT * FROM employees WHERE active = 1;
BEGIN
  FOR rec IN c_emp LOOP
    UPDATE employees SET salary = salary * 1.1 WHERE id = rec.id;
  END LOOP;
END;
/

详细见：Oracle PL/SQL 游标。

11. 动态 SQL

CREATE OR REPLACE PROCEDURE dynamic_query(
  p_table IN VARCHAR2,
  p_id IN NUMBER
) IS
  v_name VARCHAR2(100);
BEGIN
  EXECUTE IMMEDIATE 'SELECT name FROM ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_table) 
    || ' WHERE id = :id'
    INTO v_name
    USING p_id;
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

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

12. BULK

CREATE OR REPLACE PROCEDURE bulk_update IS
  TYPE id_array IS TABLE OF NUMBER;
  TYPE sal_array IS TABLE OF NUMBER;
  v_ids id_array;
  v_sals sal_array;
BEGIN
  SELECT id BULK COLLECT INTO v_ids FROM employees WHERE active = 1;
  
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = salary * 1.1 WHERE id = v_ids(i);
END;
/

详细见：Oracle BULK COLLECT 与 FORALL。

13. 调试

13.1 DBMS_OUTPUT

CREATE OR REPLACE PROCEDURE debug_proc IS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

SET SERVEROUTPUT ON;
EXEC debug_proc;

13.2 日志表

CREATE TABLE proc_log (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  proc_name VARCHAR2(100),
  log_msg VARCHAR2(4000),
  log_time TIMESTAMP
);

CREATE OR REPLACE PROCEDURE log_msg(p_proc VARCHAR2, p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO proc_log (proc_name, log_msg, log_time)
  VALUES (p_proc, p_msg, SYSTIMESTAMP);
  COMMIT;
END;
/

14. 权限

GRANT EXECUTE ON hire_employee TO hr_app;
GRANT EXECUTE ON get_emp_count TO public;

-- 调用
EXEC scott.hire_employee(...);

15. 管理

15.1 编译

ALTER PROCEDURE my_proc COMPILE;
ALTER FUNCTION my_func COMPILE;

15.2 查看

SELECT object_name, object_type, status, last_ddl_time
FROM user_objects
WHERE object_type IN ('PROCEDURE', 'FUNCTION');

SELECT text FROM user_source WHERE name = 'MY_PROC' ORDER BY line;

15.3 依赖

SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'MY_PROC';

16. 常见坑与排错

16.1 PLS-00103

- 语法错误
- 检查

16.2 ORA-06575

- 函数无效
- 重新编译

16.3 OUT 参数 SQL 调用

- SQL 不能直接调用 OUT 过程
- 用绑定变量

17. 最佳实践

过程操作，函数返回：清晰
%TYPE/%ROWTYPE：解耦
异常完整：健壮
事务边界：明确
BULK 批量：性能
绑定变量：安全
DETERMINISTIC/RESULT_CACHE：优化
AUTHID 选择：权限
日志调试：可维护
文档化：接口

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