oracle 如何将数据插入到 TABLE 类型变量中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32205693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert data into TABLE type variable
提问by Radu Gheorghiu
I'm expecting a lot of downvotes, since I think this is a silly question, but here it goes:
我期待很多downvotes，因为我认为这是一个愚蠢的问题，但它是这样的：
I'm attempting at inserting data into a TABLE TYPE variable. I have a pre-existing TABLE TYPE, defined as:
我正在尝试将数据插入到 TABLE TYPE 变量中。我有一个预先存在的表类型，定义为：
create or replace type m_code_object
is object (m_code varchar2(25 char));
create or replace type m_code_tabletype
is table of m_code_object;
and the table that I want to define based on this and insert data into, is below:
我想基于此定义并向其中插入数据的表如下：
declare
vtable m_code_tabletype;
begin
insert into vtable values ('a');
insert into vtable values ('b');
end;
Now, when running this in SQL Developer I get PL/SQL: ORA-00942: table or view does not existfor both rows.
现在，当在 SQL Developer 中运行它时，我得到PL/SQL: ORA-00942: table or view does not exist了两行。
My understanding was that this is a table variable of type m_code_tabletypeand that it's enough to declare it in a block before trying to insert data into it.
我的理解是，这是一个类型的表变量，m_code_tabletype在尝试向其中插入数据之前在块中声明它就足够了。
Can anyone please explain what I'm doing wrong or what's missing from my understanding?
谁能解释一下我做错了什么或我的理解中缺少什么？
Thanks
谢谢
回答by atik
This is how you populate a table type of objects :
这是填充表类型对象的方式：
declare
vtable m_code_tabletype := m_code_tabletype();
begin
vtable.extend;
vtable(vtable.count) := m_code_object('a');
vtable.extend;
vtable(vtable.count) := m_code_object('b');
end;
回答by Husqvik
DECLARE
c_varray SYS.ODCIVARCHAR2LIST;
BEGIN
c_varray := SYS.ODCIVARCHAR2LIST();
c_varray.EXTEND(2);
c_varray(1) := '1';
c_varray(2) := '2';
END;
Or
或者
DECLARE
c_varray SYS.ODCIVARCHAR2LIST;
BEGIN
SELECT LEVEL BULK COLLECT INTO c_varray FROM DUAL CONNECT BY LEVEL <= 10;
END;
回答by abhi
There is a detailed explanation on table types here.
有上表类型的详细解释在这里。
Try this:
尝试这个：
declare
vtable m_code_tabletype;
vrow m_code_object;
begin
vrow := m_code_object('a');
vtable := m_code_tabletype(vrow);
abs_test(vtable);
end;
In order to pass a table type to a stored procedure:
为了将表类型传递给存储过程：
Create or replace procedure abs_test(mct IN m_code_tabletype)
AS
BEGIN
FOR i in 1..mct.count
LOOP
dbms_output.put_line('hello' || to_char(mct.count));
END LOOP;
END;
