时间戳

首页 > 解决方案 > 将 plsql 嵌套表类型与另一个常规 sql 表连接

plsql - 将 plsql 嵌套表类型与另一个常规 sql 表连接

问题描述

我想使用常规 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

我想将此表 l_invoices 与我的常规发票表一起加入。关于我该怎么做的任何想法？

标签： plsql

解决方案

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

您的代码可能是：

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;

推荐阅读