Oracle 数据库存储过程：从入门到精通

McRfee

于 2025-02-05 13:15:00 发布

阅读量1.3k

点赞数 20

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_40574354/article/details/145453330

版权

Oracle 数据库存储过程：从入门到精通

Oracle 数据库存储过程：从入门到精通

Oracle 数据库存储过程：从入门到精通

一、存储过程简介

存储过程（Stored Procedure）是 Oracle 数据库中一种重要的数据库对象，它可以将一组复杂的 SQL 语句封装起来，存储在数据库中，并通过调用存储过程来执行这些 SQL 语句。存储过程具有以下优点：

提高代码复用性： 将常用的 SQL 语句封装成存储过程，可以避免重复编写代码，提高开发效率。
增强数据安全性： 通过存储过程可以控制用户对数据的访问权限，提高数据安全性。
提高执行效率： 存储过程在第一次执行时会被编译并存储在数据库中，后续调用时可以直接执行，无需再次编译，从而提高执行效率。
减少网络流量： 将复杂的业务逻辑放在存储过程中，可以减少客户端与数据库服务器之间的网络通信量。

二、存储过程语法

创建存储过程的基本语法如下：

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter1 [IN | OUT | IN OUT] datatype [, parameter2 [IN | OUT | IN OUT] datatype]...)]
IS
    -- 声明部分
BEGIN
    -- 执行部分
[EXCEPTION
    -- 异常处理部分]
END [procedure_name];

procedure_name: 存储过程的名称。
parameter1, parameter2, …: 存储过程的参数列表，参数可以是输入参数（IN）、输出参数（OUT）或输入输出参数（IN OUT）。
datatype: 参数的数据类型。
IS: 声明部分开始的关键字。
BEGIN: 执行部分开始的关键字。
EXCEPTION: 异常处理部分开始的关键字。
END: 存储过程结束的关键字。

三、存储过程示例

1. 简单示例

以下示例创建一个简单的存储过程，用于查询指定部门的员工信息：

CREATE OR REPLACE PROCEDURE get_employee_by_dept (
    p_dept_id IN NUMBER,
    p_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_cursor FOR
        SELECT * FROM employees WHERE department_id = p_dept_id;
END get_employee_by_dept;

2. 带输出参数的示例

以下示例创建一个存储过程，用于计算两个数的和，并将结果通过输出参数返回：

CREATE OR REPLACE PROCEDURE add_numbers (
    p_num1 IN NUMBER,
    p_num2 IN NUMBER,
    p_sum OUT NUMBER
)
IS
BEGIN
    p_sum := p_num1 + p_num2;
END add_numbers;

3. 异常处理示例

以下示例创建一个存储过程，用于删除指定部门的员工信息，并处理可能出现的异常：

CREATE OR REPLACE PROCEDURE delete_employee_by_dept (
    p_dept_id IN NUMBER
)
IS
BEGIN
    DELETE FROM employees WHERE department_id = p_dept_id;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, '删除员工信息失败：' || SQLERRM);
END delete_employee_by_dept;

四、存储过程调用

存储过程可以通过以下方式调用：

PL/SQL 块中调用：

BEGIN
    get_employee_by_dept(10, :cursor);
END;

SQL*Plus 中调用：

EXEC get_employee_by_dept(10, :cursor);

Java 程序中调用：

CallableStatement cstmt = conn.prepareCall("{call get_employee_by_dept(?, ?)}");
cstmt.setInt(1, 10);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
ResultSet rs = (ResultSet) cstmt.getObject(2);