在嵌套表中包含RowId值 [英] Include RowId value in Nested Table

问题描述

我有以下示例表:

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

在下面的示例存储过程中，我想通过以下声明将其用作嵌套表参数:

I want to use this as a nested table parameter in the below sample Stored Procedure by doing the below declaration:

create or replace package dat_pkg is

    type typ_dat_tst is table of data_test%rowtype index by pls_integer;

    procedure proc_test (p_dat  typ_dat_tst);

end dat_pkg;
/

我希望proc_test根据嵌套表的rowid更新data_test的行:

I want proc_test to update the rows of data_test based on the rowid of the nested table:

create or replace package body dat_pkg is

    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).rowid;

        end loop;

    end proc_test;

end dat_pkg;
/    

但是我收到错误PLS-00302: component 'ROWID' must be declared，因为它在嵌套表中寻找物理列rowid. 当我使用功能rowidtochar()时，会引发相同的错误.

however i am getting the error PLS-00302: component 'ROWID' must be declared because its looking for the physical column rowid in the Nested Table. The same error is being raised when i use the function rowidtochar().

如何在类型声明中包括rowid作为物理列?

How can i include rowid as a physicial column in the Type declaration?

推荐答案

ROWID是伪列，它不是表的数据字典视图的一部分(例如，它没有出现在dba_tab_columns中)，因此它不包含在%rowtype中.一个PL/SQL记录-这是您要构建的PL/SQL表的内容-没有物理存储，因此没有真实或伪的rowid.

ROWID is a pseudocolumn, it isn't part of the data dictionary view of the table (e.g. it doesn't appear in dba_tab_columns), so it isn't included in the %rowtype. A PL/SQL record - which is what you are constructing a PL/SQL table of - has no physical storage, so no real or pseudo rowid.

如果您真的想将行ID存储在记录/表中，则必须明确声明类型:

If you really want to store the row ID in a record/table you would have to declare the type explicitly:

create or replace package dat_pkg is

    type typ_dat_rec is record (
        data_id     data_test.data_id%type,
        data_value  data_test.data_value%type,
        data_rowid  rowid);

    type typ_dat_tst is table of data_test%rowtype index by pls_integer;

    procedure proc_test (p_dat  typ_dat_tst);

end dat_pkg;
/

您不能仅将rowid称为记录字段，因为这是一种数据类型，因此我给它加上了data_前缀，但您可能希望使用其他名称.然后，您需要在包主体中使用该字段名称，显然:

You can't call the record field just rowid as that is a data type, so I've prefixed it with data_ but you might prefer something else. And then you need to use that field name in your package body, obviously:

create or replace package body dat_pkg is

    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;
/

您可以按照建议将整个行类型和存储为记录类型中的两个字段:

You could, as you suggested, store the entire row type and the row ID as two fields in the record type:

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 proc_test (p_dat  typ_dat_tst);

end dat_pkg;
/

但这会使对字段的引用更加尴尬:

but that makes referring to the fields a bit more awkward:

...
        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;
...

，这也可能会使集合的填充更加尴尬.因为您必须知道所有列/字段名称才能在循环中引用它们，所以我不确定是否有很多优点，但是您可能会发现它更整洁.

and it will probably make populating the collection more awkward too. As you have to know all the column/field names anyway to be able to refer to them in the loop, I'm not sure there's much advantage, but you may find it neater.

当然，这样做完全是假设您的集合是从同一DB甚至会话中的表中的数据子集填充的，因为行的rowid会随时间变化.您可能还想研究forall语法来替换您的for循环，具体取决于您的实际工作. (但是您还应该考虑是否完全需要该集合-如果您只是填充集合，然后使用该集合进行更新，那么单个SQL更新仍然会更快...)

Of course, doing this at all assumes your collection is being populated from a subset of data from the table in the same DB and even session, since a row's rowid can change over time. You might also want to look into the forall syntax to replace your for loop, depending on what you are really doing. (But you should also consider whether you need the collection at all - if you are just populating the collection and then using that for the update then a single SQL update would be faster still...)

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