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

来源：互联网发布：联通怎么开副卡软件编辑：程序博客网时间：2024/05/23 00:42

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

目标表记录-->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;