通过使用Oracle存储过程和游标实现复杂更新逻辑

1. 业务背景

最近开发过程中遇见一个业务场景，简单概括如下：

1. 首先根据特定条件查询表A中符合条件的结果集；
2. 根据将结果集中的数据作为条件分别去更新表B和表C中的数据；

当我以为这是一个简简单单的的问题时，殊不知在后面的处理中会满头大汗😓。经过简单思索后我想到了以下的几种处理方案：

• 直接通过一个SQL语句去实现，奈何本人的SQL功力着实有限，实在想不出该如何写出符合条件的SQL只能作罢😳
• 在项目中首先查出这个集合然后循环遍历，拿到集合中的数据信息再去更新其他表中的数据。

这个方法确实可以实现上边的业务场景，但是在后期发现一旦集合中数据量较大，在循环更新的过程中会频繁通过持久层框架（MyBatis）去执行更新SQL这会给系统带来不小的压力🍐开销导致系统运行缓慢。

• 在项目中查出这个集合，之后手动拼接大量的SQL语句，之后通过SqlSession手动批量执行。但总感觉不够优雅🍷，而且拼接的SQL数量也是不确定。
• Oracle存储过程（定义处理逻辑）+ 游标（处理结果集），这俩一组合岂不美滋滋🌟

2. Oracle存储过程

• Oracle 存储过程是一段预先编译好的 SQL 代码块，可以接受参数、执行特定的任务，并返回结果。可用于应用程序中，存储过程可以将常用的操作流程封装成一个单独的实体，方便管理和调用。它具有以下优点：

1. 减少网络流量：存储过程可以在数据库服务器上执行，减少了客户端和服务器之间传递数据的网络流量。
2. 提高性能：存储过程是预编译的，因此执行速度更快。此外，由于存储过程在数据库中执行，减少了连接数据库的开销。
3. 简化代码：存储过程可以将多个 SQL 语句和业务逻辑组合成一个单独的实体，简化了应用程序的代码。
4. 提高安全性：存储过程可以使用参数化查询，防止 SQL 注入攻击。

• 举个🌰：

-- 创建语法 create or replace procedure xxx
create or replace procedure set_score_status (p_user_id in varchar2) as
   -- 变量
   score_date date;
   score_status varchar2(20);
-- 开始业务
begin
   select score into score_date from students where id = p_user_id;
   
   if score_date < 60
      score_status := '不及格';
   else
      score_status := '及格';
   end if;
   
   update students set status = score_status where id = p_user_id;
   -- 事务提交
   commit;
-- 业务结束
end;

上面是一个简单Oracle 存储过程的示例，它接受一个学生ID（p_user_id），查询该学生的成绩，成绩小于60将状态设置为不及格，反之则设置为几个。在这个存储过程中，首先定义了一个名为 set_score_status 的存储过程，存储过程中使用变量 score_date 和 score_status 来存储查询结果和状态，然后根据查询结果更新学生成绩状态，最后提交事务。

• 在程序中使用以MyBatis为例：

<update id="upScoreState" parameterType="String" statementType="CALLABLE">
    { call set_score_status (#{user_id, mode=IN, jdbcType=VARCHAR}) }
</update>

3. Oracle游标

• Oracle 游标是一种用于处理返回结果集的数据库对象。可以将游标看作是类似于文件指针的操作对象，它可以指向查询结果集中的一行或多行数据，然后通过操作游标来对这些数据进行处理。

Oracle游标可以实现以下操作：

1. 打开游标：使用 DECLARE 声明游标并打开游标。
2. 定位游标：使用 FETCH 命令将游标定位到一个或多个结果集的行，以执行后续操作。
3. 读取游标：使用 FETCH 命令获取游标当前定位到的行的数据。
4. 处理游标：对游标获取到的数据进行处理，例如，将它们插入到另外一个表中或者进行计算。
5. 关闭游标：使用 CLOSE 命令关闭游标。

• 举个🌰

-- 创建语法 declare cursor xxx
declare
  cursor student_cursor is
  	-- 查询
    select name, age, score from students;
  -- 使用 student_info 存储查询结果；%rowtype声明 student_info 中数据类型和查询结果保持一致
  student_info student_cursor%rowtype;
begin
  -- 打开游标
  open student_cursor;
  loop
    fetch student_cursor
      into student_info;
    exit when student_cursor%notfound;
    -- 处理 student_info 中的数据；dbms_output.put_line 输出命令
    dbms_output.put_line(student_info.name || ' ' || student_info.age || ': ' ||
                         student_info.score);
  end loop;
  close student_cursor;
end;

在这个游标示例中，首先使用 DECLARE 声明了一个名为 student_cursor 的游标，它获取了 students 表中的姓名、年龄和成绩。然后使用 OPEN 命令打开游标、使用 FETCH 命令读取游标中的数据，并使用 EXIT 命令退出游标循环。循环中使用 DBMS_OUTPUT 命令输出每个学生的姓名、年龄和成绩。最后使用 CLOSE 命令关闭游标。

• 但是，使用游标应该谨慎，需要考虑到游标操作的成本和开销，以及可能产生的死锁、并发问题等。简简单单用一下还是可以的🤭

4. 复杂更新最终处理

最终结合存储过程和游标实现如下：

create or replace procedure complex_up(params in varchar2) is
cursor c_data is
    select column1, column2, user_name
      from temporary_table t
     where t.user_id = params;
  row_data c_data%rowtype;
begin
  open c_data;
  loop
    fetch c_data
      into row_data;
    exit when c_data%notfound;
  
    update table_b d
       set d.name  = row_data.user_name,
     where d.id = row_data.column1;
  
    update table_c p
       set p.other_name = row_data.user_name
     where p.other_id = row_data.column2;
  
  end loop;
  close c_data;
end complex_up;