将plsql嵌套表类型与另一个正则sql表连接起来。

Question

我想使用常规sql从嵌套表类型中进行选择。

create table invoices(invoice_id number);
insert into invoices values(100);
insert into invoices values(200);
insert into invoices values(300);
insert into invoices values(500);
create or replace type invoice_obt
as object (
invoice_id number
);
/

create type invoices_ntt
as table of invoice_obt;
/

下面是我的plsql

declare                                                        
l_invoices invoices_ntt := invoices_ntt();                     
begin                                                          
l_invoices.extend(3);                                          
l_invoices(1) := invoice_obt(100);                             
l_invoices(2) := invoice_obt(200);                             
l_invoices(3) := invoice_obt(200);                          
select invoice_id from invoices where invoice_id in (select * from table(l_invoices));                                
end;

我遇到了一个错误

select invoice_id from table(l_invoices);
*
ERROR at line 8:
ORA-06550: line 8, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

我想加入这张桌子_发票与我的常规发票表。我该怎么做呢？

Answer 1

问题不在于您使用类型的方式，而在于您试图从Pl/SQL块中执行select查询，而不是将结果提取到任何变量中。

你的代码可以是：

DECLARE
    l_invoices   invoices_ntt := invoices_ntt ();

    /* define a variable to host the result of the query */
    TYPE tIdList IS TABLE OF NUMBER;
    vIdList      tIdList;
BEGIN
    l_invoices.EXTEND (3);
    l_invoices (1) := invoice_obt (100);
    l_invoices (2) := invoice_obt (200);
    l_invoices (3) := invoice_obt (200);

    SELECT invoice_id
      BULK COLLECT INTO vIdList  /* BULK COLLECT because you can have more than one row */
      FROM invoices
     WHERE invoice_id IN (SELECT * FROM TABLE (l_invoices));
END;