数据表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+E cc+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';
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;