Oracle存储过程编写指南：从基础到实战的完整教程

原创于 2025-07-30 15:33:34 发布 · 452 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

一、存储过程核心价值

存储过程是Oracle数据库中实现业务逻辑封装的核心工具，具有以下优势：

性能提升：预编译执行，减少网络传输
安全加固：通过权限控制保护数据
代码复用：集中管理业务逻辑
事务控制：保证数据一致性

二、基础语法全解

2.1 存储过程模板

CREATE OR REPLACE PROCEDURE procedure_name
    (param1 IN/OUT datatype, param2 IN/OUT datatype)
IS
    -- 变量声明
    v_counter NUMBER := 0;
BEGIN
    -- 业务逻辑
    FOR i IN 1..10 LOOP
        v_counter := v_counter + i;
    END LOOP;
    
    -- 输出结果
    DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
EXCEPTION
    -- 异常处理
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

2.2 参数模式详解

模式	说明	示例场景
IN	输入参数（默认）	查询条件传递
OUT	输出参数	返回计算结果
IN OUT	输入输出双向参数	修改并返回参数值

三、核心功能实现

3.1 游标使用实战

CREATE OR REPLACE PROCEDURE get_dept_employees(p_dept_id IN NUMBER)
IS
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
    
    v_emp_rec emp_cursor%ROWTYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp_rec;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_rec.first_name || ' - ' || v_emp_rec.salary);
    END LOOP;
    CLOSE emp_cursor;
END;

3.2 事务控制示例

CREATE OR REPLACE PROCEDURE adjust_salary(
    p_emp_id IN NUMBER,
    p_percent IN NUMBER
)
IS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_percent/100)
    WHERE employee_id = p_emp_id;
    
    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
    END IF;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;

四、高级编程技巧

4.1 动态SQL实现

CREATE OR REPLACE PROCEDURE dynamic_update(
    p_table IN VARCHAR2,
    p_column IN VARCHAR2,
    p_value IN NUMBER,
    p_condition IN VARCHAR2
)
IS
    v_sql VARCHAR2(1000);
BEGIN
    v_sql := 'UPDATE ' || p_table || 
             ' SET ' || p_column || ' = :1' ||
             ' WHERE ' || p_condition;
    
    EXECUTE IMMEDIATE v_sql USING p_value;
END;

4.2 集合类型应用

CREATE OR REPLACE PROCEDURE process_employees
IS
    TYPE emp_table_type IS TABLE OF employees%ROWTYPE;
    v_emp_table emp_table_type;
BEGIN
    SELECT * BULK COLLECT INTO v_emp_table
    FROM employees
    WHERE department_id = 10;
    
    FOR i IN 1..v_emp_table.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_emp_table(i).first_name);
    END LOOP;
END;

五、调试与优化

5.1 调试技巧

-- 启用输出
SET SERVEROUTPUT ON;

-- 调试存储过程
BEGIN
    get_dept_employees(10);
END;

5.2 性能优化建议

减少上下文切换：使用BULK COLLECT替代逐行处理
索引利用：确保WHERE条件字段有索引
绑定变量：防止硬解析
异常处理：避免捕获OTHERS后不处理

六、安全实践

6.1 权限控制

-- 授予执行权限
GRANT EXECUTE ON adjust_salary TO hr_user;

-- 撤销权限
REVOKE EXECUTE ON adjust_salary FROM hr_user;

6.2 防SQL注入

CREATE OR REPLACE PROCEDURE safe_search(
    p_name IN VARCHAR2
)
IS
BEGIN
    -- 使用绑定变量
    OPEN emp_cursor FOR
        'SELECT * FROM employees WHERE first_name LIKE :name'
    USING p_name || '%';
END;

七、实战案例：工资调整系统

7.1 需求分析

根据部门ID批量调整工资
调整比例需在5%-20%之间
记录调整日志

7.2 完整实现

CREATE TABLE salary_adjust_log (
    adjust_id NUMBER PRIMARY KEY,
    employee_id NUMBER,
    old_salary NUMBER,
    new_salary NUMBER,
    adjust_date DATE
);

CREATE OR REPLACE PROCEDURE batch_adjust_salary(
    p_dept_id IN NUMBER,
    p_percent IN NUMBER
)
IS
    v_old_salary employees.salary%TYPE;
BEGIN
    IF p_percent < 5 OR p_percent > 20 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Adjustment percentage out of range');
    END IF;
    
    FOR emp_rec IN (
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = p_dept_id
    ) LOOP
        v_old_salary := emp_rec.salary;
        
        UPDATE employees
        SET salary = salary * (1 + p_percent/100)
        WHERE employee_id = emp_rec.employee_id;
        
        INSERT INTO salary_adjust_log VALUES (
            salary_adjust_seq.NEXTVAL,
            emp_rec.employee_id,
            v_old_salary,
            emp_rec.salary * (1 + p_percent/100),
            SYSDATE
        );
    END LOOP;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;