好久没有静下心来写东西了, 1 是没有遇到好的案例 2 是最近好像 有点忙, 但又有点不知道自己在忙啥的感觉一样。 强制自己静下心写东西, 但这时候 又觉得 自己啰嗦,阐述
点东西说了 很多, 又不知道自己 在说什么。 好了废话 不说 上正题
开发需要支持一个 SQL, 现有逻辑是 更新 A表 name 字段, 需要通过ID关联B表。然后把B的name,赋值给A表的name 值。 本来这个PL/SQL 有。 但是现在 逻辑 变了。
说是 根据现在这个逻辑基础上, 如果 B表 中多个数据字段type 字段的值 为1, 则汇总B表所有name 的值,最后赋值给 A表数据。
这个需求 看似不难, 但是我后来做的过程中发现不少坑, 1 B表多条数据 和A表 关联上, 但是满足条件的 B表的name 值 一样, 最终导致 A表中 的name 更新后
如‘张三,张三,张三,张三’。 经过确认后 只要 一个张三 就可以。 这个又设计到 字段中重复数据去重?? 咋办??
另外如果 B表数据关联 有 20 多个!导致 A表的name 值过长! 此时 又经过确认, 截取 3个 不同值 就可以, 本来我打算 截取3 个最新的值,我想到的是,正则表达式。 但是木有必要。 能简单点就简单点吧。
考虑再三, 同一个字符串去重, 过于复杂 需要用 树形函数 分隔, 我想到 用 想到 相邻 函数 判断 相等,则为空, 然后在分组 数据中把 name 值提取出来。
还有就是性能问题, A表 2个G,B表 2G。 2 G关联 2G, 关联分组,去重, 截取。 更新 没有优化的话, 估计在半小时 开外。 那就优化。最终SQL
DECLARE
CURSOR dataCur IS
with taba as(
select /*+ materialize */ order_id, main_wo_flag, party_name, count(1) over(partition by order_id) cnt from
UOS_WORK_ORDER uwo where main_wo_flag = '1' and uwo.arch_flag = 0
),tabb as(
select /*+ materialize */ order_id, main_wo_flag, party_name,
case when lag(party_name ) over(partition by order_id order by party_name) = party_name then null else party_name end party_name2
from taba where cnt>1
), tabc as( select /*+ materialize */ order_id, to_char(wm_concat( party_name2)) party_name from tabb
group by order_id
), tabd as( select /*+ materialize */ rowid rid,uot.id from uos_order_track uot )
select rid, regexp_replace(party_name,'^([^,]+)(,)([^,]+)(,)([^,]+)(,)(.*)$','\1\2\3\4\5\6') from
tabc, tabd where tabc.order_id = tabd.id order by 1;
type rowid_table_type is table of rowid index by pls_integer;
type cur_org_name_type is table of varchar2(255);
v_rowid rowid_table_type;
v_cur_org_name cur_org_name_type;
BEGIN
OPEN dataCur;
loop
fetch dataCur bulk collect into v_rowid, v_cur_org_name LIMIT 20000;
dbms_output.put_line( 'v_rowid.count:'||v_rowid.count );
forall i in v_rowid.FIRST ..v_rowid.LAST
update uos_order_track set cur_org_name = v_cur_org_name(i) where rowid = v_rowid(i) ;
commit;
exit when dataCur%notfound;
end loop;
CLOSE dataCur;
END;
综合性能 稳定 在2 分组之内, 这个可是 大表,大表关联更新哦。。。。
with taba as 提取数据, 提高性能
select rid, regexp_replace(party_name,'^([^,]+)(,)([^,]+)(,)([^,]+)(,)(.*)$','\1\2\3\4\5\6') 表达式 截取 第三个逗号前面的 字符串。
case when lag(party_name ) over(partition by order_id order by party_name) = party_name then null else party_name end party_name2 上一条数据 如果一样 则为null.
wm_concat( party_name2) 合并字符串, 10G中, 11G不建议用。
order by 1 很重要, 提升性能关键, 这个就不说了, 太多,避免啰嗦。
fetch dataCur bulk collect into v_rowid, v_cur_org_name LIMIT 20000; 批量游标, 减少 SQL 引警 搜索引擎 频换 切换。 LIMIT 20000 防止 回滚段 压力过大,