存储过程

Question

大家看看下面的存储过程 红字的地方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);
BEGIN
  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;

Answer 1

for bb in (SELECT * FROM T_FEE_ACCOUNT where BUSITYPEID='1') 
你后面查出来有值SELECT * FROM T_FEE_ACCOUNT where BUSITYPEID='1'?

Answer 2

SELECT * FROM T_FEE_ACCOUNT where BUSITYPEID='1'这条语句单独执行有结果集返回吗

Answer 3

查询出来是有值的 ，但是当用if判断的时候 用bb.xxx全为null值

Answer 4

你的CONSIGNID最后面是不是有空格？

Answer 5

建议把一个大sql拆成几个小的去执行 看看到底哪个没有返回结果