ora-01403 未找到数据 在线等

Question

为什么我的过程总报错ora-01403呀
select  pt.productid,             pt.productcategoryid,
             pt.memberid,
             pt.productname,
             pt.standard,
             pt.unit,
             pt.postdate,
             pt.lastamenddate,
             pt.createdate,
             pt.status,
             pt.realproductno,
             pt.realproductno2 
             into 
             v_s_productid,
              v_s_productcategoryid,
             v_s_memberid,
             v_s_productname,
              v_s_standard ,
              v_s_unit,
              v_s_postdate,
              v_s_lastamenddate,
              v_s_createdate,
              v_s_status,
              v_s_realproductno ,
              v_s_realproductno2
      from product_com_l pt
      where pt.realproductno=v_s_nativeno or  pt.realproductno2=v_s_foreignno;
就是这个SQL报错，v_s_nativeno 和 v_s_foreignno 是得到的两个参数，且都有值，为什么总报找不到数据？
单独运行SQL能得到结果，就是放在过程里不行。在线等

Answer 1

create or replace procedure ctmc_product_standard_l_proc as  v_s_nativeno                                  char(35);
  v_s_foreignno                                 char(35);
  v_s_productid                                 char(35);
  v_s_memberid                                   char(35);
  v_s_productcategoryid                         char(35);
  v_s_productname                               VARCHAR2(100);
  v_s_standard                                  VARCHAR2(80);
  v_s_unit                                      VARCHAR2(50);
  v_s_postdate                                  DATE;
  v_s_createdate                                DATE;
  v_s_lastamenddate                             DATE;
  v_s_status                                    CHAR(1);
  v_s_realproductno                             VARCHAR2(100);
  v_s_realproductno2                            VARCHAR2(100);
  v_s_pt_m_l_count                              number;
  v_s_pt_s_l_count                              number;  cursor c_sel_nno_fno is
    select ctpt.nativeno,ctpt.foreignno  
    from contract ct,contractproduct ctpt,product pt
    where ct.status='C' 
    and ctpt.contractid=ct.contractid 
    and ctpt.versionid=ct.versionid
      and ctpt.productid=pt.productid group by ctpt.nativeno,ctpt.foreignno;
begin  for r_sel_nno_fno in c_sel_nno_fno  loop    v_s_nativeno:=r_sel_nno_fno.nativeno;
    v_s_foreignno:=r_sel_nno_fno.foreignno;    
    select count(*) into v_s_pt_s_l_count from product_standard_l where nativeno=v_s_nativeno and  foreignno=v_s_foreignno;
 
    dbms_output.put_line(v_s_pt_s_l_count);  
   if (v_s_pt_s_l_count = 0) then 
  
   select  count(*) into v_s_pt_m_l_count 
   from product_com_l pt
   where pt.realproductno=r_sel_nno_fno.nativeno or pt.realproductno2=r_sel_nno_fno.foreignno;
   
   dbms_output.put_line(v_s_pt_m_l_count);    if (v_s_pt_m_l_count>=1) then
    dbms_output.put_line(v_s_nativeno);
    dbms_output.put_line(v_s_foreignno);
   
    
     select  pt.productid,
             pt.productcategoryid,
             pt.memberid,
             pt.productname,
             pt.standard,
             pt.unit,
             pt.postdate,
             pt.lastamenddate,
             pt.createdate,
             pt.status,
             pt.realproductno,
             pt.realproductno2 
             into 
             v_s_productid,
              v_s_productcategoryid,
             v_s_memberid,
             v_s_productname,
              v_s_standard ,
              v_s_unit,
              v_s_postdate,
              v_s_lastamenddate,
              v_s_createdate,
              v_s_status,
              v_s_realproductno ,
              v_s_realproductno2
      from product_com_l pt
      where pt.realproductno=r_sel_nno_fno.nativeno or  pt.realproductno2=r_sel_nno_fno.foreignno;
   
      insert into product_standard_l (
             memberid,
             productid,
             productcategoryid,
             productname,
             standard,
             unit,
             postdate,
             lastamenddate,
             createdate,
             status,
             nativeno,
             foreignno 
             )
             values
             (v_s_memberid,
              v_s_productid,
              v_s_productcategoryid,
              v_s_productname,
              v_s_standard ,
              v_s_unit,
              v_s_postdate,
              v_s_lastamenddate ,
              v_s_createdate,
              v_s_status,
              v_s_realproductno ,
              v_s_realproductno2
           
           );
      commit;
     
end if;
end if;
end loop; end ctmc_product_standard_l_proc;