存储过程中使用游标

[progsky]

我写的存储过程如下，但是结果返回的记录数是正确的，可就是每条记录里没有值，经查好像出现在游标的位置（bb为没有值），红字标志
create or replace procedure test(returnTable out sys_refcursor) authid current_user is
begin
DECLARE
TYPE TYPE_TEMP_TABLE IS RECORD(
CONSIGNNO VARCHAR2(80),
CONSIGNID VARCHAR2(80),
CONSIGNINDEXID VARCHAR2(50),
RECPARTITIONNAME VARCHAR2(50),
PAYPARTITIONNAME VARCHAR2(50),
Favalidate VARCHAR2(50),
Islocked VARCHAR2(50),
SHIPID VARCHAR2(50),
SHIPNAME VARCHAR2(50),
VOYAGENO VARCHAR2(50),
BOOKINGID VARCHAR2(50),
CONSIGNTYPE VARCHAR2(50),
BUSINESSTYPECODE VARCHAR2(50),
BUSITYPEID VARCHAR2(10),
OPERATETIME date,
customername VARCHAR2(50));
myTempTable TYPE_TEMP_TABLE;
ColStr VARCHAR2(1000);
ValueStr VARCHAR2(1000);
ROWSS number;
BEGIN
SELECT COUNT(*)
INTO ROWSS
FROM USER_TABLES
WHERE TABLE_NAME = 'TEMP_CY_NEW';
IF ROWSS > 0 THEN
EXECUTE IMMEDIATE 'DROP TABLE fee.TEMP_CY_NEW';
END IF;
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE fee.TEMP_CY_NEW(
CONSIGNNO VARCHAR2(80),
CONSIGNID VARCHAR2(80),
CONSIGNINDEXID VARCHAR2(50),
RECPARTITIONNAME VARCHAR2(50),
PAYPARTITIONNAME VARCHAR2(50),
Favalidate VARCHAR2(50),
Islocked VARCHAR2(50),
SHIPID VARCHAR2(50),
SHIPNAME VARCHAR2(50),
VOYAGENO VARCHAR2(50),
BOOKINGID VARCHAR2(50),
CONSIGNTYPE VARCHAR2(50),
BUSINESSTYPECODE VARCHAR2(50),
BUSITYPEID VARCHAR2(10),
OPERATETIME date,
customername VARCHAR2(50))' || 'ON COMMIT DELETE ROWS';
for bb in (SELECT * FROM T_FEE_ACCOUNT where BUSITYPEID='1') LOOP
IF SUBSTR(bb.CONSIGNID,LENGTH(bb.CONSIGNID)-1,1)='I' THEN
select CONSIGNID as CONSIGNNO,
CONSIGNID||'I' AS CONSIGNID,
CONSIGNINDEXID,
RECPARTITIONNAME,
Paypartitionname,
Favalidate,
Islocked,
SHIPID,
SHIPNAME,
VOYAGENO,
BOOKINGID,
CONSIGNTYPE,
BUSINESSTYPECODE ,
BUSITYPEID ,
OPERATETIME,
customername
into myTempTable
from (select EnterConsign.ConsignID,
EnterConsign.ConsignID || 'I' as ConsignTypeID,
decode(bb.Favalidate,1,'已锁定',0,'未锁定','未锁定') Favalidate,
decode(bb.Islocked,1,'已锁定',0,'未锁定','未锁定') Islocked,
TO_CHAR(bb.CONSIGNINDEXID) AS CONSIGNINDEXID ,
TO_CHAR(bb.RECPARTITIONNAME) AS RECPARTITIONNAME,
TO_CHAR(bb.Paypartitionname) AS Paypartitionname ,
EnterConsign.ShipID,
(Select ship.SHIPNAME
from BAS.t_Bas_Ship ship
where ship.shipid =
EnterConsign.ShipID) as SHIPNAME,
EnterConsign.VoyageNO,
EnterConsign.BookingID,
'进场' as ConsignType,
'1' as BUSITYPEID,
(Select busi.businesstypename
from bas.t_bas_businesstype busi
where busi.businesstypecode =
EnterConsign.Businesstypecode) as BusinessTypeCode,
EnterConsign.OperateTime,
(select customershortname
from crms.t_crm_customer a
where a.customerid = EnterConsign.customerid)
as customername
from CY.t_Cy_d_Enterdepotconsign EnterConsign)
WHERE CONSIGNID || 'I' = bb.CONSIGNID;
END IF;
ColStr := 'CONSIGNNO,CONSIGNID,CONSIGNINDEXID,RECPARTITIONNAME,PAYPARTITIONNAME,Favalidate,Islocked,SHIPID,SHIPNAME,VOYAGENO,BOOKINGID,CONSIGNTYPE,BUSINESSTYPECODE,BUSITYPEID,OPERATETIME,customername';
ValueStr := '''' || myTempTable.CONSIGNNO || ''',''' ||
myTempTable.CONSIGNID || ''',''' ||
myTempTable.CONSIGNINDEXID ||''',''' ||
myTempTable.RECPARTITIONNAME || ''',''' ||
myTempTable.PAYPARTITIONNAME || ''',''' ||
myTempTable.Favalidate || ''',''' ||
myTempTable.Islocked || ''',''' ||
myTempTable.SHIPID || ''',''' ||
myTempTable.SHIPNAME || ''',''' ||
myTempTable.VOYAGENO || ''',''' ||
myTempTable.BOOKINGID || ''',''' ||
myTempTable.CONSIGNTYPE || ''',''' ||
myTempTable.BUSINESSTYPECODE || ''',''' ||
myTempTable.BUSITYPEID || ''',''' ||
myTempTable.OPERATETIME || ''',''' ||
myTempTable.customername ||'''';
execute immediate 'insert into fee.TEMP_CY_NEW(' || ColStr || ') values(' || ValueStr || ')';
END LOOP;
OPEN returnTable FOR 'SELECT * FROM TEMP_CY_New ';
END;
END test;

[sxx_tt]

你把语句
IF SUBSTR(bb.CONSIGNID,LENGTH(bb.CONSIGNID)-1,1)='I' THEN
改为：
IF SUBSTR(bb.CONSIGNID,LENGTH(trim（bb.CONSIGNID）)-1,1)='I' THEN
你的意思是不是取CONSIGNID的倒是第二位是否为‘I’？