我正在尝试编写一个具有引用游标和自定义类型的存储过程。 我想将结果提取到自定义类型中，然后在循环中打印结果。 到目前为止，我在下面通过对如何做到这一点进行一些研究来尝试这一点。但是我遇到了一些错误。 任何人都可以指导我正确的方法。
CREATE OR REPLACE type BUNDLE_OBJECT as object
(
customer_ref varchar2(20),
product_id number(9),
bundle_name varchar2(100)
);
/
create or replace type t_bundle IS TABLE OF BUNDLE_OBJECT;
/
create or replace procedure bundledata(plist VARCHAR2, bundle_f varchar2 /*either not or empty string*/
) is
C1 sys_refcursor;
lt_bundle_recd t_bundle := t_bundle();
querystring varchar2(1000);
begin
querystring := 'select BUNDLE_OBJECT(customer_ref,product_id,bundle_name)
from (select cpad.customer_ref,
cpad.product_seq,
cpad.product_id,
pa.attribute_bill_name,
cpad.attribute_value
from PVCustProductAttrDetails2 cpad,
productattribute pa
where cpad.product_id = pa.product_id
and cpad.product_attribute_subid = pa.product_attribute_subid
and pa.attribute_bill_name in
(''BUNDLE_NAME'', ''ENTITY_ID'', ''SERVICE_ACTIVATION_DATE''))
pivot(max(attribute_value)
for attribute_bill_name in(''BUNDLE_NAME'' as BUNDLE_NAME,
''ENTITY_ID'' as SALES_ID,
''SERVICE_ACTIVATION_DATE'' as
SERVICE_ACTIVATION_DATE))
where product_id in (' || plist || ') or BUNDLE_NAME is ' || bundle_f || ' NULL';
open C1 for querystring;
FETCH C1 bulk collect into lt_bundle_recd;
close c1;
FOR IDX IN 1 .. lt_bundle_recd.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(lt_bundle_recd(IDX).CUSTOMER_REF || ' ' || lt_bundle_recd(IDX).PRODUCT_ID || ' ' || lt_bundle_recd(IDX).BUNDLE_NAME);
END LOOP;
END;
/
我面临的错误如下：
SQL> show errors
Errors for PROCEDURE M_ADMIN.BUNDLEDATA:
LINE/COL ERROR
-------- -------------------------------------------------------------------------------------------
4/33 PLS-00310: with %ROWTYPE attribute, 'T_BUNDLE' must name a table, cursor or cursor-variable
4/1 PL/SQL: Item ignored
4/64 PLS-00320: the declaration of the type of this expression is incomplete or malformed
4/64 PL/SQL: Item ignored
29/6 PLS-00455: cursor 'C1' cannot be used in dynamic SQL OPEN statement
29/1 PL/SQL: Statement ignored
30/20 PLS-00320: the declaration of the type of this expression is incomplete or malformed
30/6 PL/SQL: SQL Statement ignored
31/58 PLS-00320: the declaration of the type of this expression is incomplete or malformed
31/58 PL/SQL: ORA-00904: "C2"."BUNDLE_NAME": invalid identifier
31/5 PL/SQL: SQL Statement ignored
36/45 PLS-00201: identifier 'INDX' must be declared
36/9 PL/SQL: Statement ignored
新错误是：
SQL> exec bundledata('2205','');
begin bundledata('2205',''); end;
ORA-00942: table or view does not exist
ORA-06512: at "M_ADMIN.BUNDLEDATA", line 29
ORA-06512: at line 1
尝试这样的事情 - 即，在查询时构造对象，然后只是一个简单的批量集合来填充嵌套表
CREATE OR REPLACE type BUNDLE_OBJECT as object
(
customer_ref varchar2(20),
product_id number(9),
bundle_name varchar2(100)
);
/
create or replace type t_bundle IS TABLE OF BUNDLE_OBJECT;
/
create or replace procedure bundledata(plist VARCHAR2, bundle_f varchar2 /*either not or empty string*/
) is
C1 sys_refcursor;
lt_bundle_recd t_bundle := t_bundle();
querystring varchar2(1000);
begin
querystring := 'select BUNDLE_OBJECT(customer_ref,product_id,bundle_name)
from (select cpad.customer_ref,
cpad.product_seq,
cpad.product_id,
pa.attribute_bill_name,
cpad.attribute_value
from CPADATTRS cpad,
PRATTRS pa
where cpad.product_id = pa.product_id
and cpad.product_attribute_subid = pa.product_attribute_subid
and pa.attribute_bill_name in
(''BUNDLE_NAME'', ''ENTITY_ID'', ''SERVICE_ACTIVATION_DATE''))
pivot(max(attribute_value)
for attribute_bill_name in(''BUNDLE_NAME'' as BUNDLE_NAME,
''ENTITY_ID'' as SALES_ID,
''SERVICE_ACTIVATION_DATE'' as
SERVICE_ACTIVATION_DATE))
where product_id in (' || plist || ') or BUNDLE_NAME is ' || bundle_f || ' NULL';
open C1 for querystring;
FETCH C1 bulk collect into lt_bundle_recd;
close c1;
FOR IDX IN 1 .. lt_bundle_recd.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(lt_bundle_recd(INDX).CUSTOMER_REF || ' ' || lt_bundle_recd(INDX).PRODUCT_ID || ' ' || lt_bundle_recd(INDX).BUNDLE_NAME);
END LOOP;
END;
/