(oracle学习笔记)游标实例:通过游标遍历合并数据

最新推荐文章于 2021-04-14 21:13:40 发布

最新推荐文章于 2021-04-14 21:13:40 发布 · 290 阅读

文章标签：

本文介绍了一种使用Oracle存储过程将源表中的分散数据按ID聚合的方法。通过定义游标和记录变量，实现数据的逐条读取和更新。对于相同ID的数据项，采用追加的方式合并到目标表中，不同ID则插入新记录。该方法适用于需要将多个属性值按ID聚合为单条记录的应用场景。

数据表a:
id attr value
-------------------------------------------------------
001 A aa
001 B bb
002 C cc
002 D dd
002 E ee

转成---->

目标表b:

id attr_list value_list
----------------------------------------------------------
001 A+B aa+bb
002 C+D+Ecc+dd+ee

处理思路:

建立一个游标,并设置临时变量:
tmpid 保存"上一次游标取出的id".
currid 保存"当前游标取出的id".
判断:
if (tmpid != currid) //不同的id号
{
insert into b values ( a.id, a.attr, a.value);
tmpid = currid;
}
else //同个id号
{
update b set b.attr_list = b.attr_list || '+' || a.attr , b.value_list = b.value_list || '+' || a.value;
tmpid = currid;
}

存储过程如下:

CREATE OR REPLACE PROCEDURE proc_change_attr IS
BEGIN

DECLARE

val INTEGER;
cur INTEGER;

CURSOR nextRecodeRow IS SELECT * FROM a ORDER BY id;

--用%ROWTYPE属性声名的记录变量自动拥有对应于所引用镖的字段的字段名
RecodeRow_a a%ROWTYPE;

BEGIN
val := 0;

DELETE FROM b;
COMMIT;

FOR RecodeRow_a IN nextRecodeRow LOOP
cur := RecodeRow_a.id;
CASE val
WHEN cur THEN
BEGIN
DBMS_OUTPUT.put_line('val = currid --------------->'||'cur = '||RecodeRow_a.id);
val := cur;
UPDATE b
SET
b.attr_list = b.attr_list || ',' || RecodeRow_a.attr || '=' ||RecodeRow_a.value
WHERE
b.id = val;
COMMIT;
END;
ELSE
BEGIN
DBMS_OUTPUT.put_line('val != currid --------------->'||'cur = '||RecodeRow_a.id);
val := cur;

INSERT INTO b VALUES ( RecodeRow_a.id , RecodeRow_a.attr || '=' || RecodeRow_a.value, RecodeRow_a.value);
COMMIT;
END;
END CASE;

END LOOP;

END;

END proc_change_attr;

===================================================================================================================================================
实例:

1.源表及数据:

SQL> select * from v_process_attr3 where process_num ='030079' ;

2.目标表及数据:

select * from attr_list where process_num='030079';

目标表记录-->Attr_list

3.存储过程:

CREATE OR REPLACE PROCEDURE proc_change_attr3 (dm_input IN INTEGER) IS
BEGIN

DECLARE

val INTEGER; --保存"上一次游标取出的userid"
cur INTEGER; --保存"当前游标取出的userid"

CURSOR nextRecodeRow IS
SELECT * FROM v_process_attr3 WHERE dm=dm_input ORDER BY dm,userid,process_num;

--用%ROWTYPE属性声名的记录变量自动拥有对应于所引用镖的字段的字段名
RecodeRow_v_process_attr v_process_attr3%ROWTYPE;

BEGIN
val := 0;

DELETE FROM attr_list;
COMMIT;

FOR RecodeRow_v_process_attr IN nextRecodeRow LOOP
cur := RecodeRow_v_process_attr.userid;
CASE val
WHEN cur THEN
BEGIN
val := cur;
UPDATE attr_list
SET
attr_list.attr_list = attr_list.attr_list || ',' || RecodeRow_v_process_attr.attribute_code || '=' ||RecodeRow_v_process_attr.attribute_value
WHERE
attr_list.userid = val
and
RecodeRow_v_process_attr.dm=dm_input;
COMMIT;
END;
ELSE
BEGIN
val := cur;

INSERT INTO attr_list
VALUES
( dm_input,
RecodeRow_v_process_attr.userid ,
RecodeRow_v_process_attr.process_num,
RecodeRow_v_process_attr.attribute_code || '=' || RecodeRow_v_process_attr.attribute_value,
RecodeRow_v_process_attr.timeout );
COMMIT;
END;
END CASE;

END LOOP;

END;

END proc_change_attr3;