1. 业务背景
最近开发过程中遇见一个业务场景,简单概括如下:
- 首先根据特定条件查询表A中符合条件的结果集;
- 根据将结果集中的数据作为条件分别去更新表B和表C中的数据;
当我以为这是一个简简单单的的问题时,殊不知在后面的处理中会满头大汗😓。经过简单思索后我想到了以下的几种处理方案:
- 直接通过一个
SQL
语句去实现,奈何本人的SQL
功力着实有限,实在想不出该如何写出符合条件的SQL
只能作罢😳 - 在项目中首先查出这个集合然后循环遍历,拿到集合中的数据信息再去更新其他表中的数据。
这个方法确实可以实现上边的业务场景,但是在后期发现一旦集合中数据量较大,在循环更新的过程中会频繁通过持久层框架(MyBatis
)去执行更新SQL
这会给系统带来不小的压力🍐开销导致系统运行缓慢。
- 在项目中查出这个集合,之后手动拼接大量的
SQL
语句,之后通过SqlSession
手动批量执行。但总感觉不够优雅🍷,而且拼接的SQL
数量也是不确定。 Oracle
存储过程(定义处理逻辑)+ 游标(处理结果集),这俩一组合岂不美滋滋🌟
2. Oracle
存储过程
Oracle
存储过程是一段预先编译好的SQL
代码块,可以接受参数、执行特定的任务,并返回结果。可用于应用程序中,存储过程可以将常用的操作流程封装成一个单独的实体,方便管理和调用。它具有以下优点:
- 减少网络流量:存储过程可以在数据库服务器上执行,减少了客户端和服务器之间传递数据的网络流量。
- 提高性能:存储过程是预编译的,因此执行速度更快。此外,由于存储过程在数据库中执行,减少了连接数据库的开销。
- 简化代码:存储过程可以将多个
SQL
语句和业务逻辑组合成一个单独的实体,简化了应用程序的代码。 - 提高安全性:存储过程可以使用参数化查询,防止
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
游标可以实现以下操作:
- 打开游标:使用
DECLARE
声明游标并打开游标。 - 定位游标:使用
FETCH
命令将游标定位到一个或多个结果集的行,以执行后续操作。 - 读取游标:使用
FETCH
命令获取游标当前定位到的行的数据。 - 处理游标:对游标获取到的数据进行处理,例如,将它们插入到另外一个表中或者进行计算。
- 关闭游标:使用
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;