Oracle存储过程教程该怎么学？-杰瑞科技汇

Oracle 存储过程终极教程

什么是存储过程？
为什么使用存储过程？
创建第一个存储过程
- 语法结构
- 一个简单的 "Hello World" 示例
存储过程的组成部分详解
- CREATE OR REPLACE PROCEDURE 语句
- IS / AS 关键字
- 声明部分
- 可执行部分
- 异常处理部分
参数模式
- IN (输入参数)
- OUT (输出参数)
- IN OUT (输入输出参数)
- 参数传递方式：位置表示法、名称表示法、混合表示法
高级特性
- 游标
- 包
- 动态 SQL (使用 EXECUTE IMMEDIATE)
管理和调用存储过程
- 如何编译（重新编译）存储过程？
- 如何查看存储过程的源代码？
- 如何删除存储过程？
- 如何在 PL/SQL 块和外部应用（如 Java, Python）中调用？
最佳实践与注意事项
综合示例：一个完整的业务逻辑存储过程

什么是存储过程？

存储过程是一组为了完成特定功能的 SQL 语句和 PL/SQL 代码的集合，它被编译并存储在数据库中，你可以通过一个名称（存储过程的名字）来调用它,就像调用一个函数一样。

（图片来源网络，侵删）

你可以把它想象成数据库服务器上的一段预编译好的程序。

为什么使用存储过程？

使用存储过程有很多好处,这也是它在企业级应用中被广泛使用的原因：

性能提升：存储过程在创建时会被数据库编译和优化，首次执行后，其执行计划会被缓存，后续调用时无需再次编译,执行速度更快。
网络流量减少：客户端只需发送一个简单的调用指令（如 EXEC my_proc），而不是一长串的 SQL 语句,这大大减少了网络传输的数据量。
代码重用与模块化：将通用的业务逻辑封装在存储过程中，可以在多个应用程序或地方重复调用，避免了代码冗余,提高了开发效率。
安全性增强：可以授予用户执行某个存储过程的权限，而不需要授予他们对底层表的直接 SELECT, INSERT, UPDATE, DELETE 权限，这实现了“最小权限原则”。
数据一致性：通过将一系列操作封装在一个事务中，可以确保数据操作的原子性（要么全部成功，要么全部失败）,保证业务逻辑的完整性。
集中式业务逻辑：将业务逻辑放在数据库层，使得应用程序（如 Java, Python）层更加“瘦”，只需负责展示和调用,而核心数据处理由数据库完成。

创建第一个存储过程

语法结构

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter1 [ { IN | OUT | IN OUT } ] datatype1,
      parameter2 [ { IN | OUT | IN OUT } ] datatype2,
      ... ) ]
IS
    -- 声明部分 (变量、游标、异常等)
BEGIN
    -- 可执行部分 (SQL 和 PL/SQL 语句)
    NULL; -- 至少要有一条可执行语句，即使是 NULL
EXCEPTION
    -- 异常处理部分 (可选)
    WHEN exception_name THEN
        -- 处理异常的代码
END procedure_name;
/

CREATE OR REPLACE：CREATE 用于首次创建，OR REPLACE 用于如果已存在同名过程则覆盖它,非常方便调试。
(斜杠)：在 SQL*Plus, SQL Developer 等工具中，表示执行上面编写的 PL/SQL 代码块。

一个简单的 "Hello World" 示例

-- 创建一个名为 say_hello 的存储过程
CREATE OR REPLACE PROCEDURE say_hello
IS
BEGIN
    -- 使用 DBMS_OUTPUT.PUT_LINE 在控制台输出信息
    DBMS_OUTPUT.PUT_LINE('Hello, World from an Oracle Procedure!');
END say_hello;
/

调用存储过程：

-- 执行 (调用) 存储过程
EXEC say_hello;
-- 或者使用 PL/SQL 块调用
BEGIN
    say_hello;
END;
/

*在 SQL Developer 或 SQLPlus 中查看输出：** 默认情况下，DBMS_OUTPUT 是关闭的，你需要先设置 SERVEROUTPUT 为 ON。

（图片来源网络，侵删）

SET SERVEROUTPUT ON;
EXEC say_hello;
-- 输出: Hello, World from an Oracle Procedure!

存储过程的组成部分详解

我们用一个带参数的例子来分解：

CREATE OR REPLACE PROCEDURE greet_employee (
    p_emp_id IN NUMBER,       -- 参数部分
    p_emp_name OUT VARCHAR2  -- 参数部分
)
IS
    v_dept_name VARCHAR2(100); -- 声明部分: 定义一个局部变量
BEGIN
    -- 可执行部分
    SELECT e.first_name || ' ' || e.last_name, d.department_name
    INTO p_emp_name, v_dept_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE e.employee_id = p_emp_id;
    DBMS_OUTPUT.PUT_LINE('Employee ' || p_emp_name || ' works in ' || v_dept_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Employee with ID ' || p_emp_id || ' not found.');
        p_emp_name := NULL; -- 确保在异常发生时输出参数也被正确设置
END greet_employee;
/

参数部分：定义了过程接收和返回的参数。p_emp_id 是输入参数，p_emp_name 是输出参数。
声明部分：IS 和 BEGIN 之间，用于定义在过程中使用的变量、常量、游标等,它们的作用域仅限于该过程。
可执行部分：BEGIN 和 EXCEPTION 之间，是过程的核心逻辑，包含 SQL 查询、PL/SQL 控制语句等。
异常处理部分：EXCEPTION 和 END 之间，用于捕获和处理在执行过程中可能发生的错误（如除零、数据未找到等）。

参数模式

参数是存储过程与外部交互的接口，Oracle 支持三种参数模式：

`IN` (输入参数)

默认模式，可以省略 IN 关键字。
数据从外部传入过程内部，在过程内部可以读取该参数的值,但不能修改。
相当于将值传递给一个过程的局部变量。

CREATE OR REPLACE PROCEDURE add_numbers (
    num1 IN NUMBER,
    num2 IN NUMBER,
    result OUT NUMBER -- 这个例子为了演示结果，用了 OUT
)
IS
BEGIN
    result := num1 + num2;
END add_numbers;

`OUT` (输出参数)

数据从过程内部计算并传出给调用者。
调用者必须在调用前声明一个变量来接收这个 OUT 参数的值。
在过程内部，可以为 OUT 参数赋值。
过程开始时，OUT 参数的值为 NULL。

-- 调用上面的 add_numbers 过程
DECLARE
    v_sum NUMBER;
BEGIN
    add_numbers(10, 20, v_sum);
    DBMS_OUTPUT.PUT_LINE('The sum is: ' || v_sum); -- 输出: The sum is: 30
END;
/

`IN OUT` (输入输出参数)

兼具 IN 和 OUT 的功能。
调用时传入一个变量的值，过程内部可以读取和修改这个值,修改后的值会返回给调用者。

CREATE OR REPLACE PROCEDURE swap_values (
    x IN OUT NUMBER,
    y IN OUT NUMBER
)
IS
    temp NUMBER;
BEGIN
    temp := x;
    x := y;
    y := temp;
END swap_values;
-- 调用
DECLARE
    a NUMBER := 100;
    b NUMBER := 200;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Before swap: A = ' || a || ', B = ' || b);
    swap_values(a, b);
    DBMS_OUTPUT.PUT_LINE('After swap:  A = ' || a || ', B = ' || b);
END;
/

参数传递方式

位置表示法：按参数定义的顺序传递值，最常用,但参数顺序不能错。
```
add_numbers(10, 20, v_sum);
```
名称表示法：通过 => 符号明确指定参数名和值，顺序可以任意,可读性更强。
（图片来源网络，侵删）
```
add_numbers(num1 => 10, num2 => 20, result => v_sum);
add_numbers(result => v_sum, num2 => 20, num1 => 10); -- 顺序不影响
```

混合表示法：结合位置和名称表示法,但位置参数必须在命名参数之前。

add_numbers(10, num2 => 20, result => v_sum); -- 正确
-- add_numbers(num1 => 10, 20, result => v_sum); -- 错误！

高级特性

游标

游标用于处理查询返回的多行数据,它相当于一个指向结果集的指针。

隐式游标：用于 INSERT, UPDATE, DELETE, SELECT ... INTO，Oracle 自动管理。

BEGIN
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
    END IF;
END;
/

显式游标：用于返回多行数据的 SELECT 语句。

CREATE OR REPLACE PROCEDURE list_employees_by_dept (
    p_dept_id IN NUMBER
)
IS
    CURSOR c_emp IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = p_dept_id
        ORDER BY last_name;
    v_employee_id employees.employee_id%TYPE;
    v_first_name  employees.first_name%TYPE;
    v_last_name   employees.last_name%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employees in Department ' || p_dept_id || ':');
    -- 打开游标
    OPEN c_emp;
    LOOP
        -- 从游标中获取一行数据
        FETCH c_emp INTO v_employee_id, v_first_name, v_last_name;
        EXIT WHEN c_emp%NOTFOUND; -- 当没有更多数据时退出循环
        DBMS_OUTPUT.PUT_LINE('  - ' || v_last_name || ', ' || v_first_name || ' (ID: ' || v_employee_id || ')');
    END LOOP;
    -- 关闭游标
    CLOSE c_emp;
END list_employees_by_dept;
/

包

当存储过程、函数、游标、变量等变多时，可以将它们组织在一个包中，包就像一个容器,提供了命名空间和更好的封装性。

包规范：声明包的公共元素（可以被外部调用的过程、函数、游标等）。
包体：实现包规范中声明的元素，并可以包含私有的元素（仅包内可见）。

-- 1. 创建包规范
CREATE OR REPLACE PACKAGE hr_admin_pkg
IS
    -- 公共过程声明
    PROCEDURE list_employees_by_dept (p_dept_id IN NUMBER);
    PROCEDURE add_new_employee (
        p_first_name IN VARCHAR2,
        p_last_name IN VARCHAR2,
        p_email IN VARCHAR2,
        p_hire_date IN DATE,
        p_job_id IN VARCHAR2,
        p_salary IN NUMBER,
        p_dept_id IN NUMBER
    );
END hr_admin_pkg;
/
-- 2. 创建包体 (实现)
CREATE OR REPLACE PACKAGE BODY hr_admin_pkg
IS
    -- 私有游标 (包外部不可见)
    CURSOR c_emp (p_dept_id NUMBER) IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = p_dept_id;
    -- 实现 list_employees_by_dept 过程
    PROCEDURE list_employees_by_dept (
        p_dept_id IN NUMBER
    )
    IS
        v_rec c_emp%ROWTYPE;
    BEGIN
        OPEN c_emp(p_dept_id);
        LOOP
            FETCH c_emp INTO v_rec;
            EXIT WHEN c_emp%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('  - ' || v_rec.last_name || ', ' || v_rec.first_name);
        END LOOP;
        CLOSE c_emp;
    END;
    -- 实现 add_new_employee 过程 (简化版)
    PROCEDURE add_new_employee (
        p_first_name IN VARCHAR2,
        p_last_name IN VARCHAR2,
        p_email IN VARCHAR2,
        p_hire_date IN DATE,
        p_job_id IN VARCHAR2,
        p_salary IN NUMBER,
        p_dept_id IN NUMBER
    )
    IS
        v_new_id employees.employee_id%TYPE;
    BEGIN
        -- 获取下一个员工ID
        SELECT employees_seq.NEXTVAL INTO v_new_id FROM dual;
        -- 插入新员工
        INSERT INTO employees (
            employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id
        ) VALUES (
            v_new_id, p_first_name, p_last_name, p_email, p_hire_date, p_job_id, p_salary, p_dept_id
        );
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Employee ' || p_first_name || ' ' || p_last_name || ' added successfully with ID: ' || v_new_id);
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('Error adding employee: ' || SQLERRM);
    END;
END hr_admin_pkg;
/
-- 调用包中的过程
EXEC hr_admin_pkg.list_employees_by_dept(90);

动态 SQL

当 SQL 语句在编译时无法确定，需要在运行时动态构建时，使用动态 SQL。

EXECUTE IMMEDIATE：执行一个动态构建的 SQL 字符串，通常用于 DDL 语句或非查询的 DML 语句。

CREATE OR REPLACE PROCEDURE raise_salary_for_dept (
    p_dept_id IN NUMBER,
    p_raise_percent IN NUMBER
)
IS
    v_sql_stmt VARCHAR2(1000);
BEGIN
    -- 动态构建 SQL 语句
    v_sql_stmt := 'UPDATE employees SET salary = salary * (1 + :p_raise) WHERE department_id = :p_dept_id';
    -- 使用 EXECUTE IMMEDIATE 执行，并绑定变量
    EXECUTE IMMEDIATE v_sql_stmt
    USING p_raise_percent / 100, p_dept_id; -- USING 子句用于绑定变量，防止 SQL 注入
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Salaries updated for department ' || p_dept_id);
END raise_salary_for_dept;
/

管理和调用存储过程

重新编译：
```
ALTER PROCEDURE procedure_name COMPILE;
```

查看源代码：

-- 查询数据字典视图 USER_SOURCE
SELECT text FROM user_source WHERE name = 'PROCEDURE_NAME' ORDER BY line;

删除存储过程：
```
DROP PROCEDURE procedure_name;
```

调用：

在 SQL*Plus 或 SQL Developer 中：EXEC procedure_name;
在 PL/SQL 块中：BEGIN procedure_name; END;

在 Java (JDBC) 中：

CallableStatement cstmt = connection.prepareCall("{call procedure_name(?, ?)}");
cstmt.setInt(1, 100);
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.execute();
String result = cstmt.getString(2);

最佳实践与注意事项

明确的命名：使用有意义的名称，如 get_customer_by_id 而不是 proc1。
错误处理：始终为存储过程添加异常处理块 (EXCEPTION WHEN ...)，确保事务的完整性（COMMIT / ROLLBACK）。
参数模式：明确指定参数模式 (IN, OUT, IN OUT),不要依赖默认值。
安全性：遵循最小权限原则，只授予用户执行存储过程的权限,而不是直接操作表的权限。
注释：为复杂的存储过程添加注释，解释其功能、参数和业务逻辑。
避免过度使用：虽然存储过程很强大，但也要注意不要将所有业务逻辑都放在数据库中,保持应用层和数据库层的职责清晰。
绑定变量：在动态 SQL 中，永远使用 USING 子句来绑定变量，而不是直接拼接字符串，以防止 SQL 注入攻击。

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

假设我们需要一个存储过程，用于给指定部门的员工涨薪,并记录这次调薪操作到一张日志表。

-- 1. 假设我们有一张调薪日志表
CREATE TABLE salary_raise_log (
    log_id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    operation_date  DATE DEFAULT SYSDATE,
    department_id   NUMBER,
    raise_percent   NUMBER,
    affected_rows   NUMBER,
    performed_by    VARCHAR2(50)
);
-- 2. 创建存储过程
CREATE OR REPLACE PROCEDURE give_department_raise (
    p_dept_id        IN NUMBER,
    p_raise_percent  IN NUMBER,
    p_performed_by   IN VARCHAR2,
    p_success        OUT NUMBER, -- 0 for failure, 1 for success
    p_error_msg      OUT VARCHAR2
)
IS
    v_affected_rows NUMBER;
BEGIN
    -- 参数校验
    IF p_dept_id IS NULL OR p_raise_percent IS NULL OR p_raise_percent <= 0 THEN
        p_success := 0;
        p_error_msg := 'Invalid input parameters.';
        RETURN;
    END IF;
    -- 开始事务
    SAVEPOINT before_raise;
    -- 执行涨薪
    UPDATE employees
    SET salary = salary * (1 + p_raise_percent / 100)
    WHERE department_id = p_dept_id;
    -- 获取影响的行数
    v_affected_rows := SQL%ROWCOUNT;
    -- 记录到日志表
    INSERT INTO salary_raise_log (department_id, raise_percent, affected_rows, performed_by)
    VALUES (p_dept_id, p_raise_percent, v_affected_rows, p_performed_by);
    -- 提交事务
    COMMIT;
    -- 设置输出参数
    p_success := 1;
    p_error_msg := 'Successfully raised salary for ' || v_affected_rows || ' employees.';
EXCEPTION
    WHEN OTHERS THEN
        -- 发生错误，回滚到保存点
        ROLLBACK TO before_raise;
        p_success := 0;
        p_error_msg := 'Error: ' || SQLERRM;
END give_department_raise;
/
-- 3. 调用存储过程
DECLARE
    v_status NUMBER;
    v_message VARCHAR2(200);
BEGIN
    give_department_raise(
        p_dept_id => 80,
        p_raise_percent => 5,
        p_performed_by => 'SCOTT',
        p_success => v_status,
        p_error_msg => v_message
    );
    IF v_status = 1 THEN
        DBMS_OUTPUT.PUT_LINE('SUCCESS: ' || v_message);
    ELSE
        DBMS_OUTPUT.PUT_LINE('FAILURE: ' || v_message);
    END IF;
END;
/
-- 4. 验证结果
SELECT * FROM employees WHERE department_id = 80;
SELECT * FROM salary_raise_log ORDER BY log_id DESC;

这个例子涵盖了参数校验、事务控制、DML 操作、DML 返回值 (SQL%ROWCOUNT)、日志记录和完整的错误处理,是一个比较完整的业务场景应用。

Oracle存储过程教程该怎么学？

Oracle 存储过程终极教程

目录

什么是存储过程？

为什么使用存储过程？

创建第一个存储过程

语法结构

一个简单的 "Hello World" 示例

存储过程的组成部分详解

参数模式

`IN` (输入参数)

`OUT` (输出参数)

`IN OUT` (输入输出参数)

参数传递方式

高级特性

游标

包

动态 SQL

管理和调用存储过程

最佳实践与注意事项

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

99ANYc3cd6

Python3中key in dict如何高效判断？

Java webservice接口如何调用与调试？

Python中UTF-8如何转为Unicode？

NetBeans for Java还值得新手入门学习吗？

Python中Unicode如何转为UTF-8？

HDR Light Studio教程如何快速上手？

Android Studio安装教程，步骤详解与常见问题？

java printstacktrace

KindEditor图片上传Java如何实现？

Photoshop CS3入门教程视频该怎么学？

Visual Studio视频教程该怎么选？

python thread(target

Java StringTokenizer如何正确分割字符串？

Mac如何用U盘安装Win7系统？

Eclipse Java项目创建步骤是什么？

Python little endian 是什么意思？

Oracle存储过程教程该怎么学？

Oracle 存储过程终极教程

目录

什么是存储过程？

为什么使用存储过程？

创建第一个存储过程

语法结构

一个简单的 "Hello World" 示例

存储过程的组成部分详解

参数模式

IN (输入参数)

OUT (输出参数)

IN OUT (输入输出参数)

参数传递方式

高级特性

游标

包

动态 SQL

管理和调用存储过程

最佳实践与注意事项

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

相关推荐

HDR Light Studio教程如何快速上手？

`IN` (输入参数)

`OUT` (输出参数)

`IN OUT` (输入输出参数)