ORACLE 11203，
同事咨询：下面的这段存储过程getNCInventory，在PL/SQL DEVELOPER开发工具的对该过程调用TEST功能来测试(只输入2个参数)，但
放到SQLPLUS里就报错：
YQ@oeldzdz>declare
2 cur_calling pkg_nc_getdata.t_cursor;
3begin
4 pkg_nc_getdata.getncinventory('1047V610000000000G7O','0001V6100000000R1V7X,0001V6100000000SYZUR,0001V6100000000SFB12,0001V6100000000SDYW3,0001V6100000000M6EST,0001V6100000000R9A3T',cur_calling);
5end;
6/
declare
*
ERROR at line 1:
ORA-00907: missing right parenthesis
ORA-06512: at "YQ.PKG_NC_GETDATA", line 645
ORA-06512: at line 4
Elapsed: 00:00:00.18
包头里定义了游标的数据类型。
CREATE OR REPLACE PACKAGE PKG_NC_GETDATA is
TYPE T_CURSOR IS REF CURSOR;
包体里的过程
procedure getNCInventory(v_pk_salestruin varchar2, --销售组织主键
v_pk_invbasdoc in varchar2, --产品主键，如果有多个产品用逗号分开
results out T_CURSOR) is
strSql long(15000);
--strSql2 long(5000);
v_int number := 1;
--v_pkinv PKG_NC_GETDATA.pkinvbasdocArray;
begin
--DBMS_REFRESH.REFRESH('TYATP');
execute immediate 'truncate table TMP_PK_INVBASDOC_INFO';
loop
exit when regexp_substr(v_pk_invbasdoc,'[^,]+',1,v_int) is null;
insert into TMP_PK_INVBASDOC_INFO values (replace(regexp_substr(v_pk_invbasdoc,'[^,]+',1,v_int),'''','')) ;
v_int := v_int +1;
end loop;
commit;
strSql :='select
' ||v_pk_salestru|| ',
b.pk_calbody,
b.pk_invbasdoc,
nvl(ccl_num, 0) invnum, --现存量
/*nvl(l_num, 0) - nvl(a.nonsonum, 0) - nvl(a.nonreceiptnum, 0) +
nvl(nonponum, 0) + nvl(ntraninnum, 0) - nvl(ntranoutnum, 0) -
nvl(npickmnum, 0), --可用量*/
(nvl(nonponum,0)+nvl(ntraninnum,0)) waynum, --在途数量
(nvl(l_num, 0) - nvl(a.nonsonum, 0) - nvl(a.nonreceiptnum, 0) -
nvl(ntranoutnum, 0) - nvl(npickmnum, 0)) salenum --可销售数量
from (SELECT albodyid,
bd_invbasdoc.pk_invbasdoc,
sum(nvl(nonponum, 0)) AS nonponum, --订单在途
sum(nvl(nonsonum, 0)) AS nonsonum, --销售订单承诺量
sum(nvl(nonreceiptnum, 0)) AS nonreceiptnum, --待发货单
sum(nvl(nrsvnum1, 0)) AS ntraninnum, --调拨在途,
sum(nvl(nrsvnum2, 0)) as ntranoutnum, --调拨单代转出量
sum(nvl(npickmnum, 0)) as npickmnum --备货计划量
from ic_atp
left join bd_stordoc on ic_atp.cwarehouseid =
bd_stordoc.pk_stordoc
INNER JOIN bd_invmandoc ON ic_atp.cinventoryid =
bd_invmandoc.pk_invmandoc
INNER JOIN bd_invbasdoc ON bd_invmandoc.pk_invbasdoc =
bd_invbasdoc.pk_invbasdoc
where isatpaffected = ''Y''
and ccalbodyid in
(select pk_calbody
from T_SALESTRU_CALBODY
where pk_salestru =' ||v_pk_salestru|| ')
and cwarehouseid in (select pk_stordoc
from T_CALBODY_STORDOC
where pk_calbody in
(select pk_calbody
from T_SALESTRU_CALBODY
where pk_salestru = ' ||v_pk_salestru|| ')
and isuse = ''0'' )
and bd_invbasdoc.pk_invbasdoc in (select PKINVBASDOC from TMP_PK_INVBASDOC_INFO )
group by albodyid , bd_invbasdoc.pk_invbasdoc
) a,
(select stor.pk_calbody ,bas.pk_invbasdoc,
sum(icin2.num) ccl_num --现存量
from icin2
inner join bd_stordoc stor on stor.pk_stordoc =
icin2.cwarehouseid
inner join bd_invmandoc man on man.pk_invmandoc =
icin2.cinventoryid
inner join bd_invbasdoc bas on bas.pk_invbasdoc =
man.pk_invbasdoc
where stor.pk_calbody in
(select pk_calbody
from T_SALESTRU_CALBODY
where pk_salestru = ' ||v_pk_salestru|| ')
and icin2.cwarehouseid in
(select pk_stordoc
from T_CALBODY_STORDOC
where pk_calbody in
(select pk_calbody
from T_SALESTRU_CALBODY
where pk_salestru = ' ||v_pk_salestru|| ')
and isuse = ''0'')
and bas.pk_invbasdoc in (select PKINVBASDOC from TMP_PK_INVBASDOC_INFO )
group by stor.pk_calbody ,bas.pk_invbasdoc
) b
where albodyid (+) = b.pk_calbody
and a.pk_invbasdoc (+)= b.pk_invbasdocand nvl(ccl_num, 0) <> 0 ';
open results for strSql;
/* insert into tes_sql (testsql,username,createdate) values ((strSql),'liu-jh',sysdate);
commit;*/
end;
请教，在SQLPLUS里怎么调用返回游标值的过程？
