【DB】Oracle存储过程

目录

什么是存储过程？

为什么要使用存储过程？

创建存储过程

无参存储过程语法：

带参存储过程语法：

带有输入参数的存储过程

带有输出参数的存储过程

带有输入输出参数的存储过程

带有异常处理的存储过程

存储过程中游标定义使用

基本语法

示例

简单的游标使用

带有参数的游标

隐式游标属性

示例

检查UPDATE语句影响的行数

使用FOR循环遍历用户表中的所有记录

使用BEGIN和DECLARE关键字调用存储过程的区别

使用BEGIN

使用DECLARE

总结

什么是存储过程？

存储过程（Store Procedure）是一种在数据库中预先编译并存储的SQL代码合集，可以包含SQL语句和控制结构（如条件语句、循环等）,用于完成一个或一系列数据库操作（比如对查询订单然后对订单进行修改）。存储过程可以接受输入参数、返回输出参数，并且可以返回结果集。它们通常用于执行复杂的数据操作和业务逻辑。

为什么要使用存储过程？

预编译：存储过程在首次执行时会被编译并存储在数据库中，后续调用时不需要重新编译，执行速度更快。

减少连接：当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句需要等待执行结果进行后续操作，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。

代码重用：存储过程可以将复杂的业务逻辑封装在一个模块中，便于重用和维护。

事务管理：存储过程可以包含多个SQL语句，并且可以作为一个事务执行，确保操作的原子性。在存储过程中可以使用事务控制语句（如COMMIT和ROLLBACK）来管理事务，确保数据的一致性。

创建存储过程

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

无参存储过程语法：

CREATE OR REPLACE PROCEDURE NoParPro  -- NoParPro存储过程名称
AS  -- 声明部分
BEGIN  -- 执行部分
    -- 存储过程的主体代码
EXCEPTION  -- 存储过程异常处理部分
    -- 异常处理代码
END;

解释

1. CREATE OR REPLACE PROCEDURE NoParPro：

   • CREATE OR REPLACE PROCEDURE：这是创建或替换存储过程的关键字。如果存储过程已经存在，则会替换它；如果不存在，则会创建一个新的存储过程。

   • NoParPro：这是存储过程的名称。

2. AS：

   • 这是声明部分的开始。在这里可以声明变量、游标等。

3. BEGIN：

   • 这是存储过程的执行部分的开始。在这里编写存储过程的主体代码，包括SQL语句和PL/SQL控制结构（如条件语句、循环等）。

4. EXCEPTION：

   • 这是存储过程的异常处理部分的开始。在这里可以编写异常处理代码，用于捕获和处理在执行部分中可能发生的异常。

5. END：

   • 这是存储过程的结束标志。

举例：

CREATE OR REPLACE PROCEDURE NoParPro
AS
    v_count NUMBER;  -- 声明一个数字变量
BEGIN
    -- 执行部分
    SELECT COUNT(*) INTO v_count
    FROM Users;
    /* dbms_output.put_line(); 相当相当于JAVA中的System.out.println,
 注意记住一句话的结束使用分号结束,存储过程写完一定要执行
 将它保存到数据库中 (F8)快捷键,或者点击左上角执行*/
    DBMS_OUTPUT.PUT_LINE('用户总人数为: ' || v_count);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('执行出现异常。'|| SQLERRM);
END;
​

带参存储过程语法：

带有输入参数的存储过程

创建一个存储过程，用于插入一条记录到用户表中：

CREATE OR REPLACE PROCEDURE InsertUser (
    p_UserName IN VARCHAR2,
    p_Email IN VARCHAR2
) AS
BEGIN
    INSERT INTO Users (UserName, Email)
    VALUES (p_UserName, p_Email);  
END;

调用存储过程：

BEGIN
    InsertUser('JohnDoe', 'john.doe@example.com');
END;

带有输出参数的存储过程

创建一个存储过程，用于查询用户的邮箱，并将结果返回给调用者：

CREATE OR REPLACE PROCEDURE GetUserEmail (
    p_UserName IN VARCHAR2, // 输入参数
    p_Email OUT VARCHAR2   // 输出参数
) AS
BEGIN
    SELECT Email INTO p_Email
    FROM Users
    WHERE UserName = p_UserName;
END;

调用存储过程：

DECLARE
    v_Email VARCHAR2(100);
BEGIN
    GetUserEmail('JohnDoe', v_Email);
    DBMS_OUTPUT.PUT_LINE('Email: ' || v_Email);
END;

带有输入输出参数的存储过程

创建一个存储过程，用于更新用户的邮箱，并返回更新后的邮箱：

CREATE OR REPLACE PROCEDURE UpdateUserEmail (
    p_UserName IN VARCHAR2,
    p_Email IN OUT VARCHAR2
) AS
BEGIN
    UPDATE Users
    SET Email = p_Email
    WHERE UserName = p_UserName;
​
    SELECT Email INTO p_Email
    FROM Users
    WHERE UserName = p_UserName;
END;

调用存储过程：

​DECLARE
    v_Email VARCHAR2(100);
BEGIN
    v_Email := 'new.email@example.com';
    UpdateUserEmail('JohnDoe', v_Email);
    DBMS_OUTPUT.PUT_LINE('Updated Email: ' || v_Email);
END;

带有异常处理的存储过程

创建一个存储过程，用于删除用户，并处理可能的异常：

CREATE OR REPLACE PROCEDURE DeleteUser (
    p_UserName IN VARCHAR2
) AS
BEGIN
    DELETE FROM Users
    WHERE UserName = p_UserName;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('User not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

调用存储过程：

BEGIN
    DeleteUser('JohnDoe');
END;

存储过程中游标定义使用

在Oracle PL/SQL中，游标（Cursor）是用于逐行处理查询结果集的机制。游标允许你遍历查询结果集中的每一行，并对每一行执行特定的操作。以下是游标的基本语法和示例。

基本语法

声明游标：

CURSOR cursor_name IS
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;

打开游标：

OPEN cursor_name;

获取游标数据：

FETCH cursor_name INTO variable1, variable2, ...;

关闭游标：

CLOSE cursor_name;

示例

简单的游标使用

创建一个存储过程，用于遍历用户表中的所有记录，并输出每个用户的用户名和邮箱：

CREATE OR REPLACE PROCEDURE ListUsers
AS
    -- 声明游标
    CURSOR user_cursor IS
        SELECT UserName, Email
        FROM Users;
​
    -- 声明变量v_UserName/v_Email，其数据类型与表Users中的列UserName/v_Email相同
    v_UserName Users.UserName%TYPE;
    v_Email Users.Email%TYPE;
BEGIN
    -- 打开游标
    OPEN user_cursor;
​
    -- 循环遍历游标
    LOOP
        FETCH user_cursor INTO v_UserName, v_Email;
        EXIT WHEN user_cursor%NOTFOUND;
​
        -- 输出用户信息
        DBMS_OUTPUT.PUT_LINE('UserName: ' || v_UserName || ', Email: ' || v_Email);
    END LOOP;
​
    -- 关闭游标
    CLOSE user_cursor;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

调用存储过程：

BEGIN
    ListUsers;
END;

带有参数的游标

创建一个存储过程，用于根据用户名查询用户信息，并输出用户的邮箱：

CREATE OR REPLACE PROCEDURE GetUserInfo (
    p_UserName IN VARCHAR2
) AS
    -- 声明游标
    CURSOR user_cursor IS
        SELECT Email
        FROM Users
        WHERE UserName = p_UserName;
​
    -- 声明变量
    v_Email Users.Email%TYPE;
BEGIN
    -- 打开游标
    OPEN user_cursor;
​
    -- 获取游标数据
    FETCH user_cursor INTO v_Email;
​
    -- 检查是否找到记录
    IF user_cursor%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Email: ' || v_Email);
    ELSE
        DBMS_OUTPUT.PUT_LINE('User not found.');
    END IF;
​
    -- 关闭游标
    CLOSE user_cursor;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

调用存储过程：

BEGIN
    GetUserInfo('JohnDoe');
END;

隐式游标属性

在PL/SQL中，SQL%ROWCOUNT是一个隐式游标属性，用于返回最近执行的SQL语句（如INSERT、UPDATE、DELETE）影响的行数。这个属性可以用于检查SQL语句的执行结果，从而进行相应的处理。

示例

以下是一个示例，展示了如何使用SQL%ROWCOUNT属性来检查UPDATE语句影响的行数，并根据结果执行不同的操作。

检查UPDATE语句影响的行数

创建一个存储过程，用于更新用户的邮箱，并检查更新操作影响的行数：

​
CREATE OR REPLACE PROCEDURE UpdateUserEmail (
    p_UserName IN VARCHAR2,
    p_NewEmail IN VARCHAR2
) AS
BEGIN
    -- 更新用户的邮箱
    UPDATE Users
    SET Email = p_NewEmail
    WHERE UserName = p_UserName;
​
    -- 检查更新操作影响的行数
    IF SQL%ROWCOUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No rows updated. User not found.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Email updated successfully for user: ' || p_UserName);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

调用存储过程：

BEGIN
    UpdateUserEmail('JohnDoe', 'new.email@example.com');
END;

在PL/SQL中，FOR循环可以用于遍历游标返回的结果集。通过使用FOR循环，可以简化游标的打开、获取和关闭操作。以下是一个示例，展示了如何使用FOR循环在存储过程中遍历游标返回的结果集。

使用FOR循环遍历用户表中的所有记录

创建一个存储过程，用于遍历用户表中的所有记录，并输出每个用户的用户名和邮箱：

CREATE OR REPLACE PROCEDURE ListUsers
AS
    -- 声明游标
    CURSOR user_cursor IS
        SELECT UserName, Email
        FROM Users;
BEGIN
    -- 使用FOR循环遍历游标
    FOR user_record IN user_cursor LOOP
        -- 输出用户信息
        DBMS_OUTPUT.PUT_LINE('UserName: ' || user_record.UserName || ', Email: ' || user_record.Email);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

调用存储过程：

BEGIN
    ListUsers;
END;

使用BEGIN和DECLARE关键字调用存储过程的区别

BEGIN
    UpdateOrInsertUser('JohnDoe', 'john.doe@example.com');
END;
​
DECLARE
    v_Email VARCHAR2(100);
BEGIN
    GetUserEmail('JohnDoe', v_Email);
    DBMS_OUTPUT.PUT_LINE('Email: ' || v_Email);
END;

在调用Oracle存储过程时，使用BEGIN和DECLARE关键字的区别主要在于是否需要声明变量或处理异常。以下是详细解释：

使用BEGIN

当你只需要简单地调用存储过程，而不需要声明变量或处理异常时，可以直接使用BEGIN关键字。例如：

​BEGIN
    NoParPro;
END;

在这个例子中，NoParPro是一个不需要参数的存储过程。BEGIN和END关键字用于标记PL/SQL代码块的开始和结束。

使用DECLARE

当你需要在调用存储过程之前声明变量或处理异常时，可以使用DECLARE关键字。DECLARE块用于声明变量、游标等，并且可以包含异常处理部分。例如：

DECLARE
    v_count NUMBER;  -- 声明一个数字变量
BEGIN
    NoParPro;  -- 调用存储过程
    -- 其他PL/SQL代码
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

在这个例子中：

1. DECLARE：

   • 用于声明变量、游标等。在这里，我们声明了一个数字变量v_count。

2. BEGIN：

   • 用于标记PL/SQL代码块的开始。在这里，我们调用了存储过程NoParPro，并且可以包含其他PL/SQL代码。

3. EXCEPTION：

   • 用于处理在BEGIN块中可能发生的异常。在这里，我们捕获了NO_DATA_FOUND异常和其他异常，并输出相应的错误消息。

总结

• 使用BEGIN：当你只需要简单地调用存储过程，而不需要声明变量或处理异常时。

• 使用DECLARE：当你需要在调用存储过程之前声明变量或处理异常时。