求oracle存储过程报错“读取违反顺序”解决办法

xuranyao0416 2010-03-04 05:13:57



create or replace procedure P_Search_CRM_Barcode_State

as

  

  var_barcode varchar2(50);--条码使用

  cur_refcursor sys_refcursor;--结果集使用



  var_DEV_SUBTYPE            VARCHAR2(20);

  var_RESOURCE_INSTANCE_CODE VARCHAR2(50);

  var_SALES_RESOURCE_NAME    VARCHAR2(250);

  var_STORAGE_NAME           VARCHAR2(100);

  var_CURR_STATE             VARCHAR2(60);

  var_DC_DEVICE_SCODE        VARCHAR2(20);

  var_NC_SALES_RESOURCE_ID   VARCHAR2(50);

  var_SALES_RESOURCE_ID      NUMBER(9);

  var_STORAGE_CODE           VARCHAR2(21);

  var_ATTR_ID                number(9);

  var_DN_NO                  VARCHAR2(20);

  var_LAN_NAME               VARCHAR2(40);

  var_curr_state_temp        varchar2(60);

  

  --建立游标

  --1

  cursor cur_barcode is

  select * from xry_temp_barcode2;

  

  --2

  cursor cur_crm_barcode is

  select * from xry_crm_barcode where dr=0

  and state='000';

  

  var_cur_temp1 xry_crm_barcode%rowtype;

  

begin



     --将要处理的条码存入临时表里

     /*insert into xry_temp_barcode2

     select distinct(vbarcode) from infl_nc_out_b 

     where dbizdate=to_char(to_date(sysdate-1),'yyyy-mm-dd');*/

     --从临时表里取出条码，再调用copy库的zczx_bss_ljj.get_device_inf

     open cur_barcode;

     loop           

           fetch  cur_barcode  into var_barcode;

           exit when cur_barcode %NOTFOUND;

           zczx_bss_ljj.get_device_inf@CSSS_COPY(var_barcode,cur_refcursor);

           --

              fetch cur_refcursor into var_DEV_SUBTYPE,var_RESOURCE_INSTANCE_CODE,

                                       var_SALES_RESOURCE_NAME,var_STORAGE_NAME,

                                       var_CURR_STATE,var_DC_DEVICE_SCODE,

                                       var_NC_SALES_RESOURCE_ID,var_SALES_RESOURCE_ID,

                                       var_STORAGE_CODE,var_ATTR_ID,var_DN_NO,

                                       var_LAN_NAME;

              exit when cur_refcursor %NOTFOUND;

               --将查询的结果插入xry_crm_barcode

               insert into xry_crm_barcode

                    values(var_DEV_SUBTYPE,var_RESOURCE_INSTANCE_CODE,

                           var_SALES_RESOURCE_NAME,var_STORAGE_NAME,

                           var_CURR_STATE,var_DC_DEVICE_SCODE,

                           var_NC_SALES_RESOURCE_ID,var_SALES_RESOURCE_ID,

                           var_STORAGE_CODE,var_ATTR_ID,var_DN_NO,

                           var_LAN_NAME,var_curr_state_temp,sysdate,'0','');

               

     end loop;

     close cur_barcode;

end P_Search_CRM_Barcode_State;