Oracle存储过程如何创建与调用？-杰瑞科技汇

Oracle 存储过程完整教程

什么是存储过程？
为什么使用存储过程？
创建存储过程的基本语法
第一个存储过程：Hello, World!
参数详解
- IN (输入参数)
- OUT (输出参数)
- IN OUT (输入输出参数)
变量与常量
- 声明变量
- 赋值方式 ( 和 SELECT ... INTO ...)
流程控制
- 条件判断: IF-THEN-ELSE, CASE
- 循环: LOOP, WHILE-LOOP, FOR-LOOP
游标
- 显式游标
- 隐式游标 (SQL%FOUND, SQL%NOTFOUND 等)
异常处理
- DECLARE ... EXCEPTION
- EXCEPTION WHEN ... THEN ...
- 预定义异常与自定义异常
调用存储过程
查看、修改和删除存储过程
最佳实践与注意事项
综合示例：一个完整的业务逻辑存储过程

什么是存储过程？

存储过程是存储在数据库中的一段 PL/SQL (Procedural Language/SQL) 代码块，它被命名并编译，可以被应用程序或其他存储过程多次调用。

（图片来源网络，侵删）

你可以把它想象成一个在数据库服务器上预先编写好的“函数”或“脚本”，它接收输入，执行一系列操作（如查询、插入、更新、删除数据），并可以返回结果。

为什么使用存储过程？

性能提升：存储过程在数据库中预先编译好，调用时无需再次编译，减少了网络传输和解析时间。
减少网络流量：客户端只需发送一个简单的调用指令（如 EXEC my_proc），而不是将多条 SQL 语句发送到数据库。
代码重用与模块化：将常用的业务逻辑封装成存储过程，可以在不同应用中重复调用，提高了代码的可维护性。
安全性：可以授予用户执行存储过程的权限，而不需要授予他们对底层表的直接操作权限（如 SELECT, UPDATE），实现更精细的权限控制。
数据一致性：通过将一系列操作封装在一个事务中，可以确保数据操作的原子性（要么全部成功，要么全部失败）。

创建存储过程的基本语法

CREATE OR REPLACE PROCEDURE procedure_name (
    -- 参数列表
    parameter1 [IN | OUT | IN OUT] datatype1,
    parameter2 [IN | OUT | IN OUT] datatype2
)
IS
    -- 声明部分: 定义变量、常量、游标、异常等
    -- 注意: 这里用 IS 或 AS 都可以
BEGIN
    -- 执行部分: 存储过程的主要逻辑代码
    NULL; -- 至少需要一条语句，NULL 是一个空操作
    -- 异常处理部分 (可选)
EXCEPTION
    WHEN exception_name THEN
        -- 异常发生时的处理逻辑
        NULL;
END procedure_name;
/

CREATE OR REPLACE：创建一个新的存储过程，或者如果已存在同名存储过程，则替换它，这非常方便用于开发和调试。
在 SQL*Plus, SQL Developer 等工具中，斜杠表示执行前面的 PL/SQL 代码块。

第一个存储过程：`Hello, World!`

这是一个最简单的存储过程,它不接受任何参数，只是向控制台打印一条消息。

CREATE OR REPLACE PROCEDURE say_hello
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, World!');
END say_hello;
/

如何调用和查看结果？

在 SQL Developer 或 SQL*Plus 中，你需要先开启服务器输出，然后调用过程。

-- 1. 开启服务器输出
SET SERVEROUTPUT ON;
-- 2. 调用存储过程
EXEC say_hello;
-- 或者使用 BEGIN ... END 块
BEGIN
    say_hello;
END;
/

预期输出：

Hello, World!
PL/SQL procedure successfully completed.

参数详解

参数是存储过程与外部交互的桥梁,Oracle 支持三种类型的参数。

IN (输入参数)

这是默认的参数类型,数据从调用者传递到存储过程，在过程内部可以被读取，但不能被修改。

CREATE OR REPLACE PROCEDURE greet_user (p_user_name IN VARCHAR2)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, ' || p_user_name || '!');
END greet_user;
/

调用：

EXEC greet_user('Alice');

输出：

Hello, Alice!

OUT (输出参数)

数据从存储过程内部传递回调用者,调用时必须传递一个变量来接收结果，初始值在过程内部被赋予。

CREATE OR REPLACE PROCEDURE get_user_count (
    p_dept_id IN NUMBER,
    p_count   OUT NUMBER
)
IS
BEGIN
    -- 统计指定部门的人数
    SELECT COUNT(*) INTO p_count
    FROM employees
    WHERE department_id = p_dept_id;
    DBMS_OUTPUT.PUT_LINE('Query executed.');
END get_user_count;
/

调用：

-- 必须先声明一个变量来接收 OUT 参数
DECLARE
    v_employee_count NUMBER;
BEGIN
    get_user_count(10, v_employee_count); -- 传入部门ID 10
    DBMS_OUTPUT.PUT_LINE('Employee count in dept 10: ' || v_employee_count);
END;
/

输出 (假设部门10有5名员工)：

Query executed.
Employee count in dept 10: 5

IN OUT (输入输出参数)

数据可以从调用者传递到过程,也可以在过程内部修改后返回给调用者。

CREATE OR REPLACE PROCEDURE format_name (
    p_name IN OUT VARCHAR2
)
IS
BEGIN
    -- 将名字转换为大写，并加上前缀
    p_name := 'Mr. ' || UPPER(p_name);
END format_name;
/

调用：

DECLARE
    v_full_name VARCHAR2(100) := 'john doe';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Before: ' || v_full_name);
    format_name(v_full_name); -- 传递变量，它会被修改
    DBMS_OUTPUT.PUT_LINE('After: ' || v_full_name);
END;
/

输出：

Before: john doe
After: Mr. JOHN DOE

变量与常量

在 IS 和 BEGIN 之间声明。

声明变量

DECLARE
    v_emp_id        NUMBER(6) := 100; -- 声明并初始化
    v_emp_name      VARCHAR2(100);
    v_salary        employees.salary%TYPE; -- 使用 %TYPE 引用表列的数据类型，推荐！
    v_dept_rec      departments%ROWTYPE; -- 使用 %ROWTYPE 引用整行的数据结构
BEGIN
    -- ...
END;

赋值方式

赋值操作符

v_counter := v_counter + 1;
v_emp_name := 'Scott';

SELECT ... INTO ... 语句 这是将单行查询结果赋值给变量最常用的方法。

-- 查询员工名并赋值给 v_emp_name
SELECT first_name || ' ' || last_name INTO v_emp_name
FROM employees
WHERE employee_id = 100;
-- 注意：如果查询返回0行或多于1行，会抛出异常

流程控制

条件判断

IF-THEN-ELSIF-ELSE

CREATE OR REPLACE PROCEDURE check_salary (p_emp_id IN NUMBER)
IS
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_emp_id;
    IF v_salary < 5000 THEN
        DBMS_OUTPUT.PUT_LINE('Salary is low.');
    ELSIF v_salary BETWEEN 5000 AND 10000 THEN
        DBMS_OUTPUT.PUT_LINE('Salary is average.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Salary is high.');
    END IF;
END check_salary;
/

CASE 语句

CREATE OR REPLACE PROCEDURE print_job_level (p_job_id IN VARCHAR2)
IS
BEGIN
    CASE p_job_id
        WHEN 'IT_PROG' THEN
            DBMS_OUTPUT.PUT_LINE('Job Level: Programmer');
        WHEN 'SA_REP' THEN
            DBMS_OUTPUT.PUT_LINE('Job Level: Sales Representative');
        WHEN 'ST_CLERK' THEN
            DBMS_OUTPUT.PUT_LINE('Job Level: Stock Clerk');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Job Level: Other');
    END CASE;
END print_job_level;
/

循环

WHILE-LOOP

CREATE OR REPLACE PROCEDURE print_numbers (p_limit IN NUMBER)
IS
    v_counter NUMBER := 1;
BEGIN
    WHILE v_counter <= p_limit LOOP
        DBMS_OUTPUT.PUT_LINE(v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END print_numbers;
/

FOR-LOOP (推荐)

CREATE OR REPLACE PROCEDURE print_for_loop (p_limit IN NUMBER)
IS
BEGIN
    -- i 是一个隐式声明的变量，类型为 INTEGER
    FOR i IN 1..p_limit LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END print_for_loop;
/

LOOP ... EXIT WHEN

CREATE OR REPLACE PROCEDURE print_loop_exit (p_limit IN NUMBER)
IS
    v_counter NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(v_counter);
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > p_limit;
    END LOOP;
END print_loop_exit;
/

游标

游标是一个指向 SQL 查询结果集的指针，当查询返回多行数据时，必须使用游标来逐行处理。

显式游标

当需要精细控制时使用。

CREATE OR REPLACE PROCEDURE list_employees_by_dept (p_dept_id IN NUMBER)
IS
    -- 1. 声明游标
    CURSOR c_employees IS
        SELECT first_name, last_name, salary
        FROM employees
        WHERE department_id = p_dept_id
        ORDER BY salary DESC;
    v_first_name employees.first_name%TYPE;
    v_last_name  employees.last_name%TYPE;
    v_salary     employees.salary%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Employees in Department ' || p_dept_id || ' ---');
    -- 2. 打开游标
    OPEN c_employees;
    -- 3. 循环获取游标中的每一行
    LOOP
        -- FETCH 将当前行数据存入变量
        FETCH c_employees INTO v_first_name, v_last_name, v_salary;
        -- EXIT WHEN 当没有更多数据时退出循环
        EXIT WHEN c_employees%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ': ' || v_salary);
    END LOOP;
    -- 4. 关闭游标 (非常重要！)
    CLOSE c_employees;
    DBMS_OUTPUT.PUT_LINE('--- End of List ---');
END list_employees_by_dept;
/

隐式游标

对于 INSERT, UPDATE, DELETE 或只返回单行的 SELECT 语句，Oracle 会自动创建一个隐式游标，可以通过以下属性检查状态：

SQL%FOUND: 布尔值，SQL 语句是否影响了至少一行。
SQL%NOTFOUND: 与 SQL%FOUND 相反。
SQL%ROWCOUNT: 受影响的行数。
SQL%ISOPEN: 布尔值，隐式游标在执行后总是关闭，所以永远是 FALSE。

CREATE OR REPLACE PROCEDURE update_employee_salary (
    p_emp_id IN NUMBER,
    p_new_salary IN NUMBER
)
IS
BEGIN
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_emp_id;
    -- 检查是否更新成功
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Successfully updated salary for employee ' || p_emp_id);
        DBMS_OUTPUT.PUT_LINE('Rows affected: ' || SQL%ROWCOUNT);
    ELSE
        DBMS_OUTPUT.PUT_LINE('No employee found with ID ' || p_emp_id);
    END IF;
END update_employee_salary;
/

异常处理

异常处理是保证程序健壮性的关键,当运行时错误发生时，会抛出异常，正常流程会跳转到 EXCEPTION 块。

预定义异常

Oracle 为常见错误预定义了异常名。

CREATE OR REPLACE PROCEDURE get_employee_name (p_emp_id IN NUMBER)
IS
    v_name employees.first_name%TYPE;
BEGIN
    SELECT first_name INTO v_name
    FROM employees
    WHERE employee_id = p_emp_id;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
EXCEPTION
    -- 当 SELECT ... INTO 返回0行时抛出
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Employee with ID ' || p_emp_id || ' does not exist.');
    -- 当 SELECT ... INTO 返回多行时抛出
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Error: Multiple employees found with ID ' || p_emp_id || '.');
    -- 其他所有错误
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END get_employee_name;
/

自定义异常

CREATE OR REPLACE PROCEDURE check_bonus (
    p_emp_id IN NUMBER,
    p_bonus IN NUMBER
)
IS
    v_salary employees.salary%TYPE;
    e_bonus_too_high EXCEPTION; -- 1. 声明异常
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_emp_id;
    IF p_bonus > v_salary * 0.5 THEN
        RAISE e_bonus_too_high; -- 2. 手动抛出异常
    ELSE
        DBMS_OUTPUT.PUT_LINE('Bonus is acceptable.');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found.');
    WHEN e_bonus_too_high THEN -- 3. 捕获自定义异常
        DBMS_OUTPUT.PUT_LINE('Error: Bonus cannot exceed 50% of salary!');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END check_bonus;
/

调用存储过程

在 SQL*Plus 或 SQL Developer 中使用 EXEC 或 EXECUTE 命令：
```
EXEC greet_user('Bob');
```

在 PL/SQL 块中调用：

BEGIN
    -- 可以调用多个过程
    greet_user('Charlie');
    check_salary(101);
END;
/

在 Java, Python, .NET 等应用程序中，通过 JDBC, ODBC, cx_Oracle 等数据库驱动来调用，通常使用 CallableStatement。

查看、修改和删除存储过程

查看源代码：
```
SELECT text FROM all_source WHERE name = 'SAY_HELLO' AND type = 'PROCEDURE' ORDER BY line;
```
- USER_SOURCE：查看当前用户拥有的对象。
- ALL_SOURCE：查看当前用户有权限查看的对象。
- DBA_SOURCE：数据库管理员查看所有对象的源代码。
编译（重新验证）： 如果存储过程依赖的对象发生了变化，存储过程可能会变为无效状态，可以手动重新编译它。
```
ALTER PROCEDURE say_hello COMPILE;
```
删除：
```
DROP PROCEDURE say_hello;
```

最佳实践与注意事项

命名规范：使用有意义的名称，如 proc_get_customer_by_id。
参数使用 %TYPE 和 %ROWTYPE：这会使你的代码更具弹性，当表结构变化时，存储过程可能无需修改。
最小权限原则：授予用户执行存储过程的权限，而不是直接操作表的权限。
事务管理：在存储过程内部使用 COMMIT 和 ROLLBACK 要非常谨慎，让调用存储过程的应用程序来管理事务是更好的做法，以避免长事务和锁定问题。
注释：为复杂的逻辑添加注释，方便日后维护。
避免在循环中进行 DML 操作：如果可能，尽量将 DML 操作移到循环之外，以减少上下文切换和性能开销。
处理所有可能的异常：至少要有 WHEN OTHERS 块来捕获未预料到的错误，并记录详细的错误信息。

综合示例：一个完整的业务逻辑存储过程

假设我们要为员工涨薪,涨薪规则如下：

如果职位是 'IT_PROG'，涨薪 10%。
如果职位是 'SA_REP'，涨薪 8%。
如果职位是 'ST_CLERK'，涨薪 5%。
其他职位,涨薪 3%。
涨薪后,如果新工资低于 4000，则自动调整到 4000。
记录所有涨薪操作到一个日志表。

-- 1. 准备一个日志表
CREATE TABLE salary_increase_log (
    log_id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_id     NUMBER,
    old_salary      NUMBER(10, 2),
    new_salary      NUMBER(10, 2),
    increase_percent NUMBER(5, 2),
    change_date     DATE DEFAULT SYSDATE
);
-- 2. 创建存储过程
CREATE OR REPLACE PROCEDURE give_salary_raise (
    p_emp_id IN NUMBER
)
IS
    v_job_id          employees.job_id%TYPE;
    v_old_salary      employees.salary%TYPE;
    v_new_salary      employees.salary%TYPE;
    v_raise_percent   NUMBER(5, 2) := 0;
    e_invalid_salary EXCEPTION;
BEGIN
    -- 1. 获取员工当前职位和薪水
    SELECT job_id, salary INTO v_job_id, v_old_salary
    FROM employees
    WHERE employee_id = p_emp_id;
    -- 2. 根据职位确定涨薪比例
    CASE v_job_id
        WHEN 'IT_PROG' THEN v_raise_percent := 10;
        WHEN 'SA_REP' THEN v_raise_percent := 8;
        WHEN 'ST_CLERK' THEN v_raise_percent := 5;
        ELSE v_raise_percent := 3;
    END CASE;
    -- 3. 计算新薪水
    v_new_salary := v_old_salary * (1 + v_raise_percent / 100);
    -- 4. 确保新薪水不低于4000
    IF v_new_salary < 4000 THEN
        v_new_salary := 4000;
    END IF;
    -- 5. 更新员工薪水
    UPDATE employees
    SET salary = v_new_salary
    WHERE employee_id = p_emp_id;
    -- 6. 记录到日志表
    INSERT INTO salary_increase_log (employee_id, old_salary, new_salary, increase_percent)
    VALUES (p_emp_id, v_old_salary, v_new_salary, v_raise_percent);
    DBMS_OUTPUT.PUT_LINE('Salary raised for employee ' || p_emp_id || '. Old: ' || v_old_salary || ', New: ' || v_new_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Employee with ID ' || p_emp_id || ' not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error in give_salary_raise for emp ' || p_emp_id || ': ' || SQLERRM);
        -- 如果发生错误，回滚本次操作（假设整个操作在一个事务中）
        -- ROLLBACK;
END give_salary_raise;
/
-- 3. 调用存储过程
SET SERVEROUTPUT ON;
BEGIN
    -- 假设员工 100 (IT_PROG) 当前薪水为 9000
    give_salary_raise(100);
    -- 假设员工 145 (ST_CLERK) 当前薪水为 3000
    give_salary_raise(145);
    -- 假设一个不存在的员工
    give_salary_raise(9999);
END;
/

这个示例涵盖了参数、变量、查询、DML、CASE 语句、异常处理和业务逻辑封装，是存储过程一个很好的应用场景。

希望这份详尽的教程能对你有所帮助！

Oracle存储过程如何创建与调用？

Oracle 存储过程完整教程

目录

什么是存储过程？

为什么使用存储过程？

创建存储过程的基本语法

第一个存储过程：Hello, World!

参数详解

IN (输入参数)

OUT (输出参数)

IN OUT (输入输出参数)

变量与常量

声明变量

赋值方式

流程控制

条件判断

循环

游标

显式游标

隐式游标

异常处理

预定义异常

自定义异常

调用存储过程

查看、修改和删除存储过程

最佳实践与注意事项

综合示例：一个完整的业务逻辑存储过程

相关推荐

Python数据分析视频教程怎么学更高效？

第一个存储过程：`Hello, World!`