在嵌套表中填充关联数组 [英] Populate an Associative Array inside a Nested Table

问题描述

关于我的上一个问题，在嵌套表中包含RowId值 ，

In relation to my Previous Question, Include RowId value in Nested Table,

我有以下示例表:

create table data_test
(
    data_id     number,
    data_value  varchar2(100),
    batch_name  varchar2(100)
);

我已将此表用作包含rowid的参数:

I have used this table as a parameter which includes the rowid:

create or replace package dat_pkg is

    type typ_dat_rec is record (
        data_rec    data_test%rowtype,
        data_rowid  rowid);

    type typ_dat_tst is table of typ_dat_rec index by pls_integer;

    procedure transform_dat (p_batch_name data_test.batch_name%type);

    procedure proc_test (p_dat typ_dat_tst);

end dat_pkg;
/

使用过程transform_dat，我想用data_test表中的过滤记录填充变量l_dat_rec，转换数据，最后使用proc_test过程更新记录:

Using the procedure transform_dat, I want to populate the variable l_dat_rec with the filtered records from the data_test table, transform the data, and finally update the records using the proc_test procedure:

create or replace package body dat_pkg is

    procedure transform_dat (p_batch_name data_test.batch_name%type)
    is

        cursor cur_dat is
        select rowid, a.*
        from   data_test a
        where  batch_name = p_batch_name;

        l_dat_rec typ_dat_tst;

    begin

        open    cur_dat;
        fetch   cur_dat
        BULK COLLECT
        into    l_dat_rec;
        close   cur_dat;

        -- Do the Transformation here. Example --            
        for i in l_dat_rec.count loop
            if l_dat_rec(i).data_value = 'hello' then
                l_dat_rec(i).data_id := l_dat_rec(i).data_id + l_dat_rec(i).data_id;
            else            
                l_dat_rec(i).data_id := l_dat_rec(i).data_id * l_dat_rec(i).data_id;
            end if;
        end loop;

        -- update the table            
        proc_test (p_dat => l_dat_rec);

    end transform_dat;

    procedure proc_test (p_dat  typ_dat_tst)
    is
    begin

        for i in 1..p_dat.count loop

            update  data_test        
            set     data_value  = p_dat(i).data_value  
            where   data_id     = p_dat(i).data_id
            and     rowid       = p_dat(i).data_rowid;

        end loop;

    end proc_test;

end dat_pkg;
/    

但是我收到错误PLS-00597: expression 'L_DAT_REC' in the INTO list is of wrong type.当我使用BULK COLLECT时，会引发相同的错误.

however i am getting the error PLS-00597: expression 'L_DAT_REC' in the INTO list is of wrong type. The same error is being raised when i use BULK COLLECT.

我应该使用什么填充l_dat_rec?

推荐答案

在回答上一个问题时，我提到使用%rowtype字段填充集合会比较困难.据我所知，除非您声明一个SQL级别的对象类型而不是一个记录类型，否则您不能使用bulk collect(尽管值得检查一下它是否在12c中已更改).

In the answer to your previous question, I mentioned that populating the collection would be harder with the %rowtype field. As far as I'm aware, unless you declare an SQL-level object type instead of a record type you can't use bulk collect for this (though it's worth checking if that has changed in 12c perhaps).

我相信您会坚持使用更简单的光标循环，该循环将分别构建您类型中的两个字段(即%rowtype子字段和rowid字段)，然后在时间:

I believe you are stuck with using a simpler cursor loop that builds the two fields in your type (i.e. the %rowtype sub-field and the rowid field) separately, and then builds up the collection a line at a time:

create or replace package body dat_pkg is

    procedure transform_dat (p_batch_name data_test.batch_name%type)
    is

        cursor cur_dat is
        select rowid, a.*
        from   data_test a
        where  batch_name = p_batch_name;

        l_dat_tst typ_dat_tst;
        l_rec data_test%rowtype;

    begin

        for rec_dat in cur_dat loop
            l_rec.data_id := rec_dat.data_id;
            l_rec.data_value := rec_dat.data_value;
            l_rec.batch_name := rec_dat.batch_name;
            -- or use a counter you increment for this...
            l_dat_tst(l_dat_tst.count + 1).data_rec := l_rec;
            l_dat_tst(l_dat_tst.count).data_rowid := rec_dat.rowid;
        end loop;

        -- Do the Transformation here. Example --            

        for i in 1..l_dat_tst.count loop
            if l_dat_tst(i).data_rec.data_value = 'hello' then
                l_dat_tst(i).data_rec.data_value := 'was hello';
            else            
                l_dat_tst(i).data_rec.data_value := 'was not hello';
            end if;
        end loop;

        -- update the table            
        proc_test (p_dat => l_dat_tst);

    end transform_dat;

    procedure proc_test (p_dat  typ_dat_tst)
    is
    begin

        for i in 1..p_dat.count loop

            update  data_test        
            set     data_value  = p_dat(i).data_rec.data_value  
            where   data_id     = p_dat(i).data_rec.data_id
            and     rowid       = p_dat(i).data_rowid;

        end loop;

    end proc_test;

end dat_pkg;
/    

如前所述，对子字段记录的字段的引用必须正确限定，因此我在这两个过程的引用中都插入了.data_rec.我已经更改了虚拟转换，以修改值而不是ID，因为这意味着永远不会发生更新.

As also discussed before, the references to the sub-field-record's fields have to be qualified properly, so I've inserted .data_rec in the references in both procedures. I've changed the dummy transformation to modify the value instead of the ID, as that means no updates were ever going to happen.

具有一些虚拟数据的演示:

Demo with some dummy data:

insert into data_test values (1, 'hello', 'test');
insert into data_test values (2, 'hello', 'test');
insert into data_test values (3, 'hello', 'exclude');
insert into data_test values (4, 'goodbye', 'test');


exec dat_pkg.transform_dat('test');

select * from data_test;

   DATA_ID DATA_VALUE           BATCH_NAME          
---------- -------------------- --------------------
         1 was hello            test                
         2 was hello            test                
         3 hello                exclude             
         4 was not hello        test                

这篇关于在嵌套表中填充关联数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！