oracle - PLSQL：将游标的结果插入 plsql 表的一列

Question

以下是我创建表对象的代码：

TYPE TempObjectsTable IS TABLE OF t_temp_objects%ROWTYPE
      INDEX BY BINARY_INTEGER;

nt_scb_temp_objects TempObjectsTable;

t_temp_objects 定义了以下列：

Name           Null? Type          
-------------- ----- ------------- 
INVC_REF             NUMBER        
ORDERS               NUMBER        
ORDER_POS_TYPE       NUMBER        
RULE_CONDITION       VARCHAR2(500) 
CHARGE               NUMBER        
CURRENCY             VARCHAR2(10)  
TXN_DT               DATE  

现在，我有一个游标，它返回一个订单列表，基本上是数字。

CURSOR c_orders_frm_grp IS 
select a.ordr_id from sa_order a 
WHERE a.invc_ref is NULL

我正在尝试nt_scb_temp_objects使用批量收集将这些添加到上面创建的 plsql 表中。nt_scb_temp_objects但是我现在希望填充其余的列null，因为我将在接下来的步骤中填充这些列。

目前这是我正在尝试的。

IF c_orders_frm_grp %ISOPEN THEN
         CLOSE c_orders_frm_grp ;
    END IF;
    OPEN c_orders_frm_grp;

            FETCH c_orders_frm_grp BULK COLLECT INTO nt_scb_temp_objects.orders;             

     CLOSE   c_orders_frm_grp;

这是我得到的错误：Error(44,74): PLS-00302: component 'ORDERS' must be declared

标签： oracleplsql

Answer 1

你不想要那个CURSOR和OPEN..FETCH构造。只需运行SELECT BULK COLLECT INTO 该集合。

DECLARE
TYPE TempObjectsTable IS TABLE OF t_temp_objects%ROWTYPE
      INDEX BY BINARY_INTEGER;
nt_scb_temp_objects TempObjectsTable;
BEGIN
select a.ordr_id as ORDERS,
    null as INVC_REF,
    null as ORDER_POS_TYPE,
    null as RULE_CONDITION,
    null as CHARGE,
    null as CURRENCY,
    null as TXN_DT
  BULK COLLECT INTO nt_scb_temp_objects from sa_order a
WHERE a.invc_ref is NULL ;             
END;
/

演示