Oracle 存储过程游标学习

需求：看应收单的销售利润 。
单据关系：应收单上推单据是销售订单，销售订单上游单据是销售报价单
普通销售：销售订单上游单据是销售报价单，
委托代销：销售订单上游单据依然是销售订单1，从销售订单1上推是销售报价单。
实现思路：以应收单为主表循环查找销售订单和应收单，
判断点：
应收单核心单据 CoreBillNumber、CoreBillId是销售订单SaleOrderID、SaleOrderNumber
以CoreBillNumber和物料MaterialID为条件查找销售订单，返回销售订单。
返回的销售订单的SourceBillID、SourceBillNumber为销售报价单QuotationID，QuotationNumber（普通销售）
或者是销售订单SaleOrderId、SaleOrderNumber（委托代销）
或者为空（不存在销售报价单特殊单据）。
如果QuotationID/SaleOrderId不为空 ，以QuotationID、MaterialId上查销售报价单，
如果销售报价单返回结果集为空，销售订单类型是委托代销，否则为普通销售
委托代销：销售报价单返回结果集为空，以SaleOrderId、MaterialID再次查询销售订单，返回结果集SaleOrderId、SaleOrderNumber，以SaleOrderId、MaterialID为条件查询销售报价单，返回销售报价单号，把应收单ID ysdFid、ysdFnumber(应收单号)、ysdMaterialID（应收单物料）、xsbjFid（销售报价单id）、xsbjFnumber(销售报价单号)存入临时表，即为销售报价单和应收单的关联关系表供关联查询使用
普通销售：以销售订单的SourceBillID、MaterialID为查询条件查询销售报价单，返回销售报价单存入临时表
临时表结构：

execute immediate 'create table temp_20180515_cus (ysdFid varchar(300),ysdFnumber varchar(300),ysdMaterialID varchar(300),xsbjFid varchar(300),xsbjFnumber varchar(300))'

CREATE OR REPLACE 
PROCEDURE PROC_GETQUOTATION_Test(
inputStartDate IN  VARCHAR2,
inputEndDate IN  VARCHAR2,
inputOtherBillSaleNumber IN VARCHAR2,
inputOtherBillSaleGroup IN VARCHAR2,
out_return out sys_refcursor
)
AS
  --declare
    type cv1_type is ref cursor; --声明游标类型
  strSql varchar(1000);
  strSql1 VARCHAR(1000);
    strSql2 VARCHAR(2000);
  saleSourceBillID VARCHAR(100);--销售订单源单据ID
  saleSourceBillNumber VARCHAR(100);--销售订单源单据number
  saleSourceBillNumberOther VARCHAR(100);--销售订单==销售订单
  saleSourceBillIDOther VARCHAR(100);--销售订单==销售订单
  quotationNumber VARCHAR(100);--销售报价单numbe
  quotationFID VARCHAR(100);--销售报价单ID
  otherMaterialID VARCHAR(100);--应收单物料ID
    saleOrderMaterialID VARCHAR(100);--销售订单物料id
    quotationMaterialID VARCHAR(100);--销售报价单物料id
    flag VARCHAR(100);--临时变量
  cursor f_cur IS 
    select distinct b.FCoreBillNumber as coreBillNumber,b.FCoreBillId as coreBillId,A.FID as otherID,A.Fnumber as otherFnumber,b.FMaterialID as materialID 
    from  T_AR_OtherBill  A inner join T_AR_OtherBillentry B on  B.FParentID =A.Fid  
    inner join T_ORG_Sale C on C.Fid = A.FSaleOrgID  --
    inner join T_BD_SaleGroup E  on E.Fid  = A.FSaleGroupID  
    where to_char(A.Fbilldate,'yyyy-mm-dd') BETWEEN inputStartDate AND inputEndDate  
                AND  A.FBillStatus=3 
                --AND A.fnumber in ('FIYS1010201805100011')
                AND "DECODE"(inputOtherBillSaleGroup,'', E.FNUMBER,inputOtherBillSaleGroup)=E.FNUMBER
                AND C.fnumber in(SELECT DISTINCT REGEXP_SUBSTR (NVL(inputOtherBillSaleNumber, C.fnumber),'[^,]+',1,LEVEL) as token FROM   DUAL CONNECT BY REGEXP_SUBSTR (NVL(inputOtherBillSaleNumber, C.fnumber),'[^,]+',1,LEVEL) IS NOT NULL)--输入组织为多选
  f_row f_cur%rowtype;
    s_cur cv1_type;--定义游标变量
    t_cur cv1_type;--定义游标变量
    fo_cur cv1_type;--定义游标变量
BEGIN
  execute immediate 'DELETE FROM  temp_20180515_cus';
  --execute immediate 'create table temp_20180515_cus (ysdFid varchar(300),ysdFnumber varchar(300),ysdMaterialID varchar(300),xsbjFid varchar(300),xsbjFnumber varchar(300))';
  FOR f_row IN f_cur loop
    EXIT WHEN f_cur%NOTFOUND;
        dbms_output.put_line('应收单 循环次数'||f_row.coreBillNumber);
    --根据应收单 核心单据ID即销售订单ID 查询销售订单 
        strSql:='select  distinct ho1.FSourceBillID as sourceBillID,ho1.FSourceBillNumber from  T_SD_SaleOrder ho left join T_SD_SaleOrderEntry ho1 on ho.fid=ho1.fparentid  where ho.fnumber=:c1 and ho1.FMaterialID=:c2';
    --dbms_output.put_line('销售订单Number:'||f_row.coreBillNumber||'====>'||f_row.coreBillId||'应收单ID NUMBER:'|| f_row.otherID||'====>'||f_row.otherFnumber||'应收单物料ID'||f_row.materialID);
        -- execute immediate strSql into saleSourceBillID,saleSourceBillNumber using f_row.coreBillNumber;
        open t_cur for strSql using f_row.coreBillNumber,f_row.materialID;
        loop 
                fetch t_cur into saleSourceBillID,saleSourceBillNumber;
                    EXIT WHEN t_cur%NOTFOUND OR t_cur%NOTFOUND IS NULL;
                    dbms_output.put_line('销售订单循环次数'||saleSourceBillID);
                    IF saleSourceBillID IS NOT NULL THEN
                        --根据销售订单的源单据ID即销售报价单或者销售订单 查找
                        strSql1:='select distinct q.fnumber,q.fid from T_SD_Quotation q left join T_SD_QuotationEntry q1 on q.fid=q1.FParentID inner join T_BD_Material  N on N.fid = q1.FMaterialID where q.fid=:v1';
                        open s_cur for strSql1 using saleSourceBillID;
                        flag :='1';--除了循环  当查询不存在时仍然执行存在销售报价单
                        loop
                                EXIT WHEN s_cur%NOTFOUND;
                                fetch s_cur into quotationNumber,quotationFID ;
                                        dbms_output.put_line('销售报价单执行次数');
                                        IF s_cur%notfound = false then  
                                                execute immediate 'insert into temp_20180515_cus (ysdFid,ysdFnumber,ysdMaterialID,xsbjFid,xsbjFnumber) values (:1,:2,:3,:4,:5)' using f_row.otherID,f_row.otherFnumber,f_row.materialID,quotationFID,quotationNumber;
                                                flag :='0';
                                                dbms_output.put_line('销售报价单存在'||flag); 
                                        END IF;

                                        IF flag='1' THEN
                                            open fo_cur for strSql using saleSourceBillNumber,f_row.materialID;--再次查询销售订单
                                            loop
                                                fetch fo_cur into saleSourceBillIDOther,saleSourceBillNumberOther;
                                                        EXIT WHEN fo_cur%NOTFOUND;
                                                        dbms_output.put_line('上查销售订单 结果'||saleSourceBillNumberOther||'条件 number'||saleSourceBillNumber||'and material='||f_row.materialID);
                                                        --查询销售报价单  strSql1
                                                        IF saleSourceBillNumberOther IS NOT NULL THEN
                                                            execute immediate strSql1 into quotationNumber,quotationFID  using saleSourceBillIDOther;--查询销售订单核心单据id再次查销售报价单
                                                            IF quotationNumber IS NOT NULL THEN
                                                                execute immediate 'insert into temp_20180515_cus (ysdFid,ysdFnumber,ysdMaterialID,xsbjFid,xsbjFnumber) values (:1,:2,:3,:4,:5)' using f_row.otherID,f_row.otherFnumber,f_row.materialID,quotationFID,quotationNumber;
                                                                dbms_output.put_line('执行新增临时表的次数');
                                                            END IF;

                                                        END IF;

                                                END loop;

                                        END IF;

                            END loop;
                    END IF;
        END loop;
  END loop;
             --游标赋值
            OPEN out_return FOR
       select distinct A.FNumber as ysdnumr,h.FNUMBER  as wlbm, "TO_CHAR"(A.Fbilldate,'yyyy-mm-dd') as djrq ,A.FBillStatus as  djzt, C.FNAME_L2  as xszz  ,
      D.FNAME_L2  as ywmc   , B.FQuantity as ysdsl,B.FCoreBillId,B.FCoreBillNumber,B.FMaterialID as wlid,H.fname_l2,
      currency.fname_l2,q1.Ftaxrate as  shul,q1.FSeq as dlxh ,q1.Cfprofit  as bjdlr,
      q1.CFYEARSALES as ndxl ,q1.CFSALEPROPORTION as xszb,q1.Cftheoryweight as llzl ,q1.Cfmaincostpriceset as  zccb ,
      q1.Cffabricatingcost as zzfy,  q1.Cfpaintfabricatingsum as mqzzzcb ,q1.Cfpainttype as mqzl,q1.Cfpaintcosting  as mqcb,q1.Cfvalveprice as qmz ,
      q1.Cfpackageprice as bzcb ,q1.Cfmanufacturingcost as cnzzcb,q1.Cffactoryprice as ccj ,q1.Cfthreeprice as sbjg ,q1.Cftransportprice  as ysd ,q1.Cffreight as yf ,
      q1.Cftotalcost as zcbj,q1.CFMAINCOSTPRICETON as zccb,q1.Cftotalcostton as zcbd,q1.CFUNITPRICETON as djd,q1.Cfprofitton as lrd,q1.Cfthreecostrate as sxfyb,
      q1.Cfcommission as yjfwf ,q1.Cftransportquantity as yssl , q1.FPrice as sj , q1.FTaxPrice as hsdj,
      q.fnumber as xsbjdnumr
      from   T_AR_OtherBill  A    --应收单
      inner join T_AR_OtherBillentry B on  B.FParentID =A.Fid    -- 应收单分录
      inner join T_ORG_Sale C on C.Fid = A.FSaleOrgID  -- 
      inner join T_SCM_BizType D on D.FID = A.Fbiztypeid  -- 
      inner join T_BD_SaleGroup E  on E.Fid  = A.FSaleGroupID  -- 
      inner join T_BD_Person  F on F.Fid = A.FPersonId  -- 
      inner join T_BD_AsstActType  G on G.FID =A.FAsstActTypeID  ---
      inner join T_BD_Material H ON H.FID=B.FMaterialID
      left join temp_20180515_cus cs on  B.FMaterialID=cs.ysdMaterialID AND A.fid=cs.ysdFid
      left join T_SD_Quotation q on q.fid=cs.xsbjFid
      left join T_SD_QuotationEntry q1 on q.fid=q1.FParentID AND q1.FMaterialID=cs.ysdMaterialID
      LEFT JOIN T_BD_Currency currency on currency.fid=q.FCurrencyID
      where to_char(A.Fbilldate,'yyyy-mm-dd') BETWEEN inputStartDate AND inputEndDate  
            AND A.FBillStatus=3 
            --AND A.fnumber in ('FIYS1010201805100011')--测试特殊单据
            AND "DECODE"(inputOtherBillSaleGroup,'', E.FNUMBER,inputOtherBillSaleGroup)=E.FNUMBER--当输入参数为空
            AND C.fnumber in(SELECT DISTINCT REGEXP_SUBSTR (NVL(inputOtherBillSaleNumber, C.fnumber),'[^,]+',1,LEVEL) as token FROM   DUAL CONNECT BY REGEXP_SUBSTR (NVL(inputOtherBillSaleNumber, C.fnumber),'[^,]+',1,LEVEL) IS NOT NULL)
            AND B.FCoreBillNumber is not null  --AND  A.fnumber in ('FIYS1050201710310002','FIYS1010201805110007')-- AND q.fnumber='SDBJ101000004567' AND h.FNUMBER='F1110005515'
      AND q.fnumber IS NOT NULL
            ORDER BY A.FNumber;
END;

1、关于exit when v_cur%notfound：把查询结果赋值给游标变量，查询结果为空结果集即游标是空游标，exit when v_cur%notfound;仍然执行,v_cur%notfound的三种状态都有判断，依然解决不了。只能用flag变量处理
借鉴一些博客https://www.cnblogs.com/dingyingsi/p/3358682.html加了判断。
2、关于execute immediate:在执行不存在时会出现异常，如果不确定是不是存在查询结果集，尽量把查询结果集赋值给游标处理。
3、execute immediate处理结果集为空：抛出异常no_data_found

DECLARE 
    ysdFid VARCHAR(100);
    ysdNumber VARCHAR(100);
BEGIN
    execute immediate 'select ysdFid,ysdFNumber FROM temp_20180508_cus WHERE rownum <= 10' INTO ysdFid,ysdNumber;
    EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('查找数据集为空');
END;

4、存储过程传入参数为以逗号隔开的字符串处理：

--NVL(P1,P2)当p1为空时，使用p2 ;当p2为空时，使用p1   
--REGEXP_SUBSTR ('1010,1030,1050','[^,]+',1,LEVEL) 字符串转换成table类型的 ，使用IN操作
AND C.fnumber in(SELECT DISTINCT REGEXP_SUBSTR (NVL(inputOtherBillSaleNumber, C.fnumber),'[^,]+',1,LEVEL) as token FROM   DUAL CONNECT BY REGEXP_SUBSTR (NVL(inputOtherBillSaleNumber, C.fnumber),'[^,]+',1,LEVEL) IS NOT NULL)