Oracle存储过程学习（显示游标，隐式游标）

Oracle存储过程是一种在Oracle数据库中创建、存储和执行的一组SQL语句的命名代码块。存储过程通常用于执行特定的任务或操作，并且可以在数据库中多次调用，以提高性能、可维护性和安全性。

一、基础存储过程语法

1、创建存储过程：

在Oracle中，可以使用CREATE PROCEDURE语句创建存储过程。存储过程由PL/SQL语言编写，并且可以包含SQL查询、控制结构、变量声明等。例如：

CREATE PROCEDURE procedure_name
(parameter1 datatype, parameter2 datatype, ...)--括号内为参数
IS
-- 变量声明
BEGIN
-- 代码块
END;

2、参数：

存储过程可以接受零个或多个参数。参数可以是输入参数、输出参数或输入/输出参数，用于在调用过程时向其提供数据或从中检索数据。参数在存储过程的CREATE语句中声明，并在调用过程时使用。例如：

CREATE PROCEDURE procedure_name
(parameter1 IN datatype, parameter2 OUT datatype, ...)
IS
...

3、执行存储过程：

存储过程可以使用EXECUTE或EXEC命令执行，也可以通过调用它们来执行。例如：

EXECUTE procedure_name;
--或者
CALL procedure_name;

4、修改存储过程：

可以使用ALTER PROCEDURE语句修改现有的存储过程。例如：

ALTER PROCEDURE procedure_name
...

5、删除存储过程：

可以使用DROP PROCEDURE语句删除存储过程。例如：

DROP PROCEDURE procedure_name;

6、存储过程的优势：

• 性能提升：存储过程在数据库中编译和存储，因此可以减少网络流量和数据库服务器的负载。
• 安全性：通过存储过程可以实现访问控制和数据保护，提高数据的安全性。
• 重用性：存储过程可以在多个应用程序中调用，提高代码的重用性和可维护性。
• 封装性：存储过程将一组SQL语句封装在一个单独的单元中，提高了代码的模块化程度。

二、增删改查简单例子

1、插入（INSERT）操作的存储过程示例：

CREATE OR REPLACE PROCEDURE insert_employee
(
    p_employee_id IN NUMBER,
    p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2
)
IS
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name)
    VALUES (p_employee_id, p_first_name, p_last_name);
    COMMIT;
END;
/

2、删除（DELETE）操作的存储过程示例：

CREATE OR REPLACE PROCEDURE delete_employee
(
    p_employee_id IN NUMBER
)
IS
BEGIN
    DELETE FROM employees
    WHERE employee_id = p_employee_id;
    COMMIT;
END;
/

3、更新（UPDATE）操作的存储过程示例：

CREATE OR REPLACE PROCEDURE update_employee
(
    p_employee_id IN NUMBER,
    p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2
)
IS
BEGIN
    UPDATE employees
    SET first_name = p_first_name, last_name = p_last_name
    WHERE employee_id = p_employee_id;
    COMMIT;
END;
/

4、查询（SELECT）操作的存储过程示例：

CREATE OR REPLACE PROCEDURE select_employee
(
    p_employee_id IN NUMBER
)
IS
BEGIN
    FOR emp IN (SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = p_employee_id) LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.employee_id || ', First Name: ' || emp.first_name || ', Last Name: ' || emp.last_name);
    END LOOP;
END;
/

 5、汇总示例

下面是一个简单的存储过程示例，用于实现增删改查（CRUD）操作：

CREATE OR REPLACE PROCEDURE CRUD_example
(
    p_action IN VARCHAR2, -- 操作类型（'INSERT'、'DELETE'、'UPDATE'、'SELECT'）
    p_employee_id IN NUMBER DEFAULT NULL, -- 员工ID（用于DELETE、UPDATE、SELECT操作）
    p_first_name IN VARCHAR2 DEFAULT NULL, -- 员工名字（用于INSERT、UPDATE操作）
    p_last_name IN VARCHAR2 DEFAULT NULL -- 员工姓氏（用于INSERT、UPDATE操作）
)
IS
BEGIN
    IF p_action = 'INSERT' THEN
        INSERT INTO employees (employee_id, first_name, last_name)
        VALUES (p_employee_id, p_first_name, p_last_name);
    ELSIF p_action = 'DELETE' THEN
        DELETE FROM employees
        WHERE employee_id = p_employee_id;
    ELSIF p_action = 'UPDATE' THEN
        UPDATE employees
        SET first_name = p_first_name, last_name = p_last_name
        WHERE employee_id = p_employee_id;
    ELSIF p_action = 'SELECT' THEN
        -- 简单查询示例，将结果打印到控制台
        FOR emp IN (SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = p_employee_id) LOOP
            DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.employee_id || ', First Name: ' || emp.first_name || ', Last Name: ' || emp.last_name);
        END LOOP;
    ELSE
        -- 其他操作类型处理
        NULL;
    END IF;
END;
/

三、游标

1、显示游标

CREATE OR REPLACE PROCEDURE display_employees_cursor
IS
    -- 定义游标
    CURSOR employee_cursor IS
        SELECT employee_id, first_name, last_name
        FROM employees;

    -- 定义变量
    v_employee_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
BEGIN
    -- 打开游标
    OPEN employee_cursor;

    -- 循环遍历游标
    LOOP
        -- 获取下一个记录
        FETCH employee_cursor INTO v_employee_id, v_first_name, v_last_name;

        -- 退出循环，如果游标没有更多记录
        EXIT WHEN employee_cursor%NOTFOUND;

        -- 输出员工信息到控制台
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', First Name: ' || v_first_name || ', Last Name: ' || v_last_name);
    END LOOP;

    -- 关闭游标
    CLOSE employee_cursor;
END;
/

在这个存储过程中，FOR emp IN (SELECT ...)语句定义了一个隐式游标，它从员工表中选择员工的 employee_id、first_name 和 last_name 列。然后，在 LOOP 中使用 FOR 循环遍历查询结果，对于每个迭代，游标会自动获取下一个记录，并将其存储在 emp 变量中。然后，存储过程将每个员工的信息打印到控制台上。 

2、隐式游标

CREATE OR REPLACE PROCEDURE display_employees_implicit_cursor
IS
BEGIN
    -- 循环遍历查询结果
    FOR emp IN (SELECT employee_id, first_name, last_name FROM employees) LOOP
        -- 输出员工信息到控制台
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.employee_id || ', First Name: ' || emp.first_name || ', Last Name: ' || emp.last_name);
    END LOOP;
END;
/

在这个存储过程中，FOR emp IN (SELECT ...)语句定义了一个隐式游标，它从员工表中选择员工的 employee_id、first_name 和 last_name 列。然后，在 LOOP 中使用 FOR 循环遍历查询结果，对于每个迭代，游标会自动获取下一个记录，并将其存储在 emp 变量中。然后，存储过程将每个员工的信息打印到控制台上。 

注：在Oracle中，隐式游标（使用FOR...IN语句声明的游标）会在循环结束时自动关闭。这意味着当循环遍历完游标返回的所有记录后，或者在循环内部使用EXIT语句提前退出循环时，游标会被自动关闭。

隐式游标的打开和关闭是隐含在FOR...IN循环语句中的，因此不需要显式地编写打开和关闭游标的代码。这简化了代码，并且使得代码更加易读和易于维护。

总之，在使用隐式游标时，无需显式地关闭游标，它会在循环结束时自动关闭。

3、区别点

1. 显式游标循环：

   • 使用CURSOR...IS SELECT...声明游标。
   • 需要显式地打开和关闭游标。
   • 使用FETCH语句获取下一个记录。
   • 代码相对较为冗长，但更加灵活。

2. 隐式游标循环：

   • 使用FOR...IN (SELECT...) LOOP声明和定义游标。
   • 游标在FOR语句中隐式地打开和关闭，不需要显式操作。
   • 在FOR循环中直接通过游标获取下一个记录，无需使用FETCH语句。
   • 代码较为简洁，更加紧凑。

在实际使用中，选择使用哪种方式取决于个人偏好以及具体情况。隐式游标循环通常在简单的情况下更为方便，但在需要更多控制或处理游标的特殊情况下，显式游标循环可能更合适。