学习oracle存储过程

最新推荐文章于 2023-04-06 15:02:19 发布

奶茶半糖233

最新推荐文章于 2023-04-06 15:02:19 发布

阅读量274

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/i_Truth/article/details/80763716

版权

Oracle 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

需求： 发货通知单审核 ,应收款金额（统计全部币别）+发货通知单已审核未出货+已出货未开票金额+本单审核金额 <= 信用档案金额
1、应收款金额：sum(应收汇总表.期末余额) group by 币别, 通过币别汇总应收汇总表.期末余额
2、发货通知单已审核未发货：发货通知单(审核) 下推销售出库单
条件:
未发货库数量=发货通知单分录.数量-销售出库单分录.数量
发货通知单已审核未发货金额=未发货数量*发货通知单分录.单价
3、已出货未开票金额：发货通知单（审核）下推销售出库单
条件：销售出库单分录.未核销数量！=0 执行销售出库单分录.未核销金额求和
4、本单审核金额：发货通知单（审核）分录.金额求和
5、信用档案金额：信用档案.金额，条件=以【客户】和【审核】检索。

1、整体计算求和，发货通知单已出库未核销、发货通知单已审核未出库


CREATE OR REPLACE 
PROCEDURE PROC_GETOTHERBILLAMOUNT_EAS(
customerIds IN VARCHAR2,
out_return out sys_refcursor
)
AS
    type cur_type is ref cursor; --声明游标类型
    postRequisitionSql VARCHAR2(2000);--发货通知单
    postRequisitionNumber VARCHAR2(100);
    postRequisitionMaterialFid VARCHAR2(200);
    materialNumber VARCHAR2(200);
    postRequisitionQty numeric(18,6);--发货通知单数量
    postRequisitionPrice numeric(18,6);--发货通知单单价
    postReqEntrySeq VARCHAR2(200);
    postReqDeliveryType VARCHAR2(200);
    postReqCurrencyNumber VARCHAR2(200);
    postReqCustomID VARCHAR2(200);
    saleIssueMaterialId VARCHAR2(200);

    surplusPostRequisitionQty numeric(18,6);
    surplusPostRequisitionAmount numeric(18,6);
    --flag VARCHAR2(100);
    saleIssueQtyAdd numeric(18,6);
    unBillsAmount  numeric(18,6);--未核销金额
    amountPostRequisition numeric(18,6);
    cj numeric(18,6);
    flag number;
    flagMore number;
    temptable VARCHAR2(200);
    indexDeclare int;
    --定义销售出库返回行结构
    Type s_rowType is RECORD(
            SaleIssueBillNumber VARCHAR2(100),
            SaleIssueSourceBillTypeID VARCHAR2(100),
            BillTypeName VARCHAR2(100),
            SaleIssueSourceBillNumber VARCHAR2(100),
            SaleIssueMaterialID VARCHAR2(100),
            SaleIssueMaterialName VARCHAR2(100),
            SaleIssueQTY  numeric(18,6),
            SaleIssueUnWriteOffQty  numeric(18,6),
            SaleIssueUnWriteOffAmount  numeric(18,6),
            currencyNumber VARCHAR2(100),
            BalanceCustomerID VARCHAR2(100),
            saleIssueEntrySeq VARCHAR2(100),
            saleIssueEntryOtherSeq VARCHAR2(100)
    );
    --再次查询销售出库单
    Type t_rowType is RECORD(
            SaleIssueBillNumber VARCHAR2(100),
            SaleIssueSourceBillTypeID VARCHAR2(100),
            SaleIssueSourceBillNumber VARCHAR2(100),
            SaleIssueMaterialID VARCHAR2(200),
            SaleIssueQTY numeric(18,6),
            SaleIssueUnWriteOffQty numeric(18,6),
            SaleIssueUnWriteOffAmount numeric(18,6),
            currencyNumberOther VARCHAR2(100),
            balanceCustomerIDOther VARCHAR2(100)
    );

    cursor f_cur IS 
        SELECT PostRequisition.FNUMBER as fnumber,PostRequisitionEntry.FReceiveCustomerID as receiveCustomerID,custom.fname_l2 as receiveCustomerName,
        PostRequisitionEntry.FMaterialID as materialFID,PostRequisitionEntry.FQty as qty,PostRequisitionEntry.FPrice as price,Currency.fnumber as currencyNumber,material.fnumber as materialNumber,
        PostRequisitionEntry.FSourceBillEntrySeq as SourceBillEntrySeq,DeliveryType.fnumber as DeliveryType
        FROM T_SD_PostRequisition PostRequisition
        LEFT JOIN T_SD_PostRequisitionEntry PostRequisitionEntry ON PostRequisitionEntry.FPARENTID=PostRequisition.fid
        LEFT JOIN T_BD_Customer custom ON PostRequisitionEntry.FReceiveCustomerID=custom.fid
        LEFT JOIN T_BD_Material material ON PostRequisitionEntry.FMaterialID=material.fid
        LEFT JOIN T_BD_Currency Currency ON Currency.fid=PostRequisition.FCurrencyID
        LEFT JOIN T_SCM_DeliveryType  DeliveryType ON DeliveryType.fid=PostRequisitionEntry.FDeliveryTypeID;
        --WHERE 
        --PostRequisition.fnumber='SDTZ101000005106'
        --PostRequisitionEntry.FReceiveCustomerID in (SELECT DISTINCT REGEXP_SUBSTR (customerIds,'[^,]+',1,LEVEL) as token FROM   DUAL CONNECT BY REGEXP_SUBSTR (customerIds,'[^,]+',1,LEVEL) IS NOT NULL)
        --AND PostRequisition.FBASESTATUS='4' ORDER BY PostRequisition.FNUMBER DESC;

    f_row f_cur%rowtype;
    s_cur cur_type;--声明游标变量
    t_cur cur_type;--声明游标变量
    s_row s_rowType;--声明行类型
    t_row t_rowType;
BEGIN
    DBMS_OUTPUT.ENABLE(buffer_size => null);    
    FOR f_row IN f_cur loop
         EXIT WHEN f_cur%NOTFOUND;

            indexDeclare:=indexDeclare+1;
         IF f_cur%NOTFOUND OR f_cur%NOTFOUND IS NULL THEN
                dbms_output.put_line('asda');
         ELSE


             --dbms_output.put_line('---asdasdas-----tEIAAAABM+S/DAQO');

             amountPostRequisition:=0;
             --unBillsAmount:=0;
             cj :=0;
             saleIssueQtyAdd:=0;
             postRequisitionNumber:=f_row.fnumber;
             postRequisitionMaterialFid:=f_row.materialFID;
             materialNumber :=f_row.materialNumber;
             postReqEntrySeq:=f_row.SourceBillEntrySeq;
             postReqDeliveryType:=f_row.DeliveryType;
             postReqCurrencyNumber:=f_row.currencyNumber;
             postReqCustomID :=f_row.receiveCustomerID;
             postRequisitionQty :=f_row.qty;
             postRequisitionPrice:=f_row.price;
             flag :=1;
             s_cur :=FN_GETSALEISSUESQL_EAS(postRequisitionNumber,postRequisitionMaterialFid,postReqEntrySeq,postReqDeliveryType) ;
             loop
                fetch s_cur  into s_row;
                    EXIT WHEN s_cur%NOTFOUND;
                    IF s_cur%NOTFOUND AND s_cur%NOTFOUND IS NULL AND s_cur%rowcount=0 THEN

                        flag:=1;
                    END IF;
                    --dbms_output.put_line('flag'||flag||'rowcount'||s_cur%rowcount);
                    IF s_cur%found THEN
                        flag:=0;
                        IF s_row.currencyNumber IS NOT NULL and s_row.BalanceCustomerID IS NOT NULL and postReqCurrencyNumber IS NOT NULL and postReqCustomID IS NOT NULL THEN
                            IF s_row.currencyNumber=postReqCurrencyNumber and s_row.BalanceCustomerID=postReqCustomID THEN
                                --不存在库存调拨
                                saleIssueQtyAdd:=saleIssueQtyAdd+s_row.SaleIssueQTY;
                            --  dbms_output.put_line('【未出库 销售出库不为空】第一次查询存在销售出库单 发货通知单号'||postRequisitionNumber||'【销售出库单】'||s_row.SaleIssueBillNumber||'【物料】'||materialNumber||'【发货通知单数量】'||postRequisitionQty||'【相加之和】'||s_row.SaleIssueQTY||'【销售出库单数量】'||s_row.SaleIssueQTY||'【单价】'||postRequisitionPrice||'【金额】'||amountPostRequisition||chr(13)||chr(10));
                                IF s_row.SaleIssueUnWriteOffQty !=0 THEN
                                    --未核销金额不为0
                                    unBillsAmount:=unBillsAmount+s_row.SaleIssueUnWriteOffAmount;
                                END IF;
                            ELSE
                                --存在库存调拨
                                t_cur :=fn_getSaleIssueSqlMore_Eas(postReqCustomID,s_row.SaleIssueMaterialID,s_row.SaleIssueBillNumber,postReqCurrencyNumber,postReqEntrySeq,s_row.saleIssueEntryOtherSeq) ;
                                flagMore:=1;
                                loop
                                    fetch t_cur into t_row;
                                        EXIT WHEN t_cur%NOTFOUND;
                                        IF t_cur%found then
                                            flagMore:=0;
                                            --计算未出库
                                            saleIssueQtyAdd:=saleIssueQtyAdd+t_row.SaleIssueQTY;
                                            --计算未核销金额  未核销数量不为0
                                            IF t_row.SaleIssueUnWriteOffQty !=0 THEN
                                                unBillsAmount:=unBillsAmount+t_row.SaleIssueUnWriteOffAmount;
                                            END IF;
                                            --dbms_output.put_line('【未出库】第二次查找销售出库单【**xxxxxA】发货通知单： '||postRequisitionNumber||'【销售出库单】'||s_row.SaleIssueBillNumber||'【物料】'||materialNumber||'【发货通知单数量】'||f_row.qty||'【销售出库单数量】'||s_row.SaleIssueQTY||'【相加之和】'||saleIssueQtyAdd||'【单价】'||f_row.price||'【金额】'||amountPostRequisition||'【条件行号】'||f_row.SourceBillEntrySeq||chr(13)||chr(10));
                                        END IF;

                                    end loop;
                                    close t_cur;
                                    IF flagMore=1 THEN
                                            cj :=f_row.qty*f_row.price;
                                            amountPostRequisition :=amountPostRequisition+cj;
                                            --dbms_output.put_line('再次查找销售出库单为空'||postRequisitionNumber||'【销售出库单】'||s_row.SaleIssueBillNumber||'【物料】'||materialNumber||'【flagMore】：'||flagMore||chr(13)||chr(10));
                                    END IF;

                            END IF;

                        ELSE 
                            dbms_output.put_line('单据异常  请查明原因');
                        END IF;

                        --dbms_output.put_line('销售不为销售订单：'||s_row.SaleIssueBillNumber||'物料编码：'||s_row.SaleIssueMaterialName);
                    END IF;

             END loop;
             close s_cur;
             --存在销售出库  销售出库单多张

             IF flag=1 THEN
                        cj :=f_row.qty*f_row.price;
                        amountPostRequisition :=amountPostRequisition+cj;
                        --dbms_output.put_line('不存在销售出库【发货通知单单号】'||postRequisitionNumber||'【物料】'||materialNumber||'【金额=数量*单价】'||amountPostRequisition||'【数量】'||postRequisitionQty||'单价'||postRequisitionPrice||'【flag】:'||flag||chr(13)||chr(10));
             ELSE
                    surplusPostRequisitionQty:=postRequisitionQty-saleIssueQtyAdd;--剩余
                    surplusPostRequisitionAmount:=surplusPostRequisitionQty*postRequisitionPrice;--金额
                    amountPostRequisition:= amountPostRequisition+surplusPostRequisitionAmount;
                    --dbms_output.put_line('【未出库】发货通知单:END======'||postRequisitionNumber||'【物料】'||materialNumber||'【物料ID】'||postRequisitionMaterialFid||'【发货通知单数量】'||postRequisitionQty||'【销售出库单数量】'||saleIssueQtyAdd||'【未出库数量=发货通知单-销售出库单】'||surplusPostRequisitionQty||'【单价】'||postRequisitionPrice||'【金额】'||amountPostRequisition||'【条件行号】'||postReqEntrySeq||chr(13)||chr(10));
             END IF;
        END IF;
        /*更新金额*/
        --dbms_output.put_line('输出'||amountPostRequisition||'unBillsAmount'||unBillsAmount);
        IF amountPostRequisition =0 THEN 
                dbms_output.put_line('');
        ELSE
                temptable:=Fn_saveAmount_Eas(f_row.receiveCustomerID,f_row.currencyNumber,amountPostRequisition,unBillsAmount,'wuyingli','0001');
                dbms_output.put_line('【临时表名】'||temptable);
        END IF;

    END loop;
--  open out_return for SELECT * FROM zp201806190001 WHERE loginID='wuyingli' and posNumber='0001' and customID in (SELECT DISTINCT REGEXP_SUBSTR (customerIds,'[^,]+',1,LEVEL) as token FROM   DUAL CONNECT BY REGEXP_SUBSTR (customerIds,'[^,]+',1,LEVEL) IS NOT NULL);

    --return out_return;
END;

RECORD：定义记录数据类型，方便存储函数返回的多列游标。声明行类型可以用record结构作为类型（s_row s_rowType;–声明行类型）
关于处理返回结果集是否为空，类似rows.size()!=0 这种，在返回0时还存在计算的，我在这边声明的flag标识是否执行过循环。

2、查询销售出库，是否已存在销售出库，送货类型【自提】【送货】
2.1、自提：不存在发运单
2.2、送货：存在发运单，如果存在调拨需要再次查询销售出库。

CREATE OR REPLACE 
function fn_getSaleIssueSql_Eas(postRequisitionNumber in VARCHAR2, postRequisitionMaterialId in VARCHAR2,coreBillEntrySeq in VARCHAR2,billType in VARCHAR2)
return SYS_REFCURSOR 
IS f_cur SYS_REFCURSOR;

BEGIN
--销售出库单
    --存在发运单
    IF billType !='CARRY' THEN 
            open f_cur FOR
            SELECT 
                SaleIssueBill.fnumber as SaleIssueBillNumber,
                SaleIssueEntry.FSourceBillTypeID as SaleIssueSourceBillTypeID,
                BillType.fname_l2 as BillTypeName,
                --CarryBillEntry.FSourceBillNumber as CarryBillSourceBillNumber,
                SaleIssueEntry.FSourceBillNumber as SaleIssueSourceBillNumber,
                SaleIssueEntry.FMaterialID as SaleIssueMaterialID,
                Material.fname_l2 as SaleIssueMaterialName,
                SaleIssueEntry.FQTY as SaleIssueQTY,
                SaleIssueEntry.FUnWriteOffQty as SaleIssueUnWriteOffQty,
                SaleIssueEntry.FUnWriteOffAmount as SaleIssueUnWriteOffAmount,
                Currency.fnumber as currencyNumber,
                SaleIssueEntry.FBalanceCustomerID as BalanceCustomerID,
                SaleIssueEntry.FSaleOrderEntrySeq as saleIssueEntrySeq,
                SaleIssueEntry.FSeq as saleIssueEntryOtherSeq
                FROM T_IM_SaleIssueBill SaleIssueBill
                LEFT JOIN T_IM_SaleIssueEntry SaleIssueEntry ON SaleIssueBill.fid=SaleIssueEntry.fparentid
                LEFT JOIN T_SCM_BillType  BillType ON SaleIssueEntry.FSourceBillTypeID=BillType.fid
                LEFT JOIN T_BD_Material Material ON SaleIssueEntry.FMaterialID=Material.fid
                LEFT JOIN T_DT_CarryBill CarryBill ON CarryBill.FNUMBER=SaleIssueEntry.FSourceBillNumber
                LEFT JOIN T_DT_CarryBillEntry CarryBillEntry ON CarryBill.fid=CarryBillEntry.FPARENTID AND SaleIssueEntry.FSourceBillEntrySeq=CarryBillEntry.fseq       
                LEFT JOIN T_BD_Currency Currency ON Currency.fid=SaleIssueBill.FCurrencyID
                WHERE CarryBillEntry.FSourceBillNumber =postRequisitionNumber and SaleIssueEntry.FMaterialID=postRequisitionMaterialId  AND BillType.fnumber='190'  AND SaleIssueBill.FBaseStatus='4'
                AND SaleIssueEntry.FSaleOrderEntrySeq=coreBillEntrySeq;
    END IF;
    --不存在发运单 自提
    IF billType='CARRY' THEN 
            open f_cur FOR
            SELECT 
                SaleIssueBill.fnumber as SaleIssueBillNumber,
                SaleIssueEntry.FSourceBillTypeID as SaleIssueSourceBillTypeID,
                BillType.fname_l2 as BillTypeName,
                SaleIssueEntry.FSourceBillNumber as SaleIssueSourceBillNumber,
                SaleIssueEntry.FMaterialID as SaleIssueMaterialID,
                Material.fname_l2 as SaleIssueMaterialName,
                SaleIssueEntry.FQTY as SaleIssueQTY,
                SaleIssueEntry.FUnWriteOffQty as SaleIssueUnWriteOffQty,
                SaleIssueEntry.FUnWriteOffAmount as SaleIssueUnWriteOffAmount,
                Currency.fnumber as currencyNumber,
                SaleIssueEntry.FBalanceCustomerID as BalanceCustomerID,
                SaleIssueEntry.FSaleOrderEntrySeq as saleIssueEntrySeq,
                SaleIssueEntry.FSeq as saleIssueEntryOtherSeq
            FROM T_IM_SaleIssueBill SaleIssueBill
            LEFT JOIN T_IM_SaleIssueEntry SaleIssueEntry ON SaleIssueBill.fid=SaleIssueEntry.fparentid
            LEFT JOIN T_SCM_BillType  BillType ON SaleIssueEntry.FSourceBillTypeID=BillType.fid
            LEFT JOIN T_BD_Material Material ON SaleIssueEntry.FMaterialID=Material.fid
            LEFT JOIN T_BD_Currency Currency ON Currency.fid=SaleIssueBill.FCurrencyID
            WHERE SaleIssueEntry.FSourceBillNumber =postRequisitionNumber and SaleIssueEntry.FMaterialID=postRequisitionMaterialId  AND BillType.fnumber='320'  AND SaleIssueBill.FBaseStatus='4'
            AND SaleIssueEntry.FSaleOrderEntrySeq=coreBillEntrySeq;
    END IF;


    return f_cur;
END;

3、再次查询销售出库。根据销售出库单

CREATE OR REPLACE 
function fn_getSaleIssueSqlMore_Eas(balanceCustomerID in VARCHAR2, materialId in VARCHAR2,SaleIssueBillNumber in VARCHAR2,currencyNumber in VARCHAR2,coreBillEntrySeq in VARCHAR2,saleIssueEntryOtherSeq in VARCHAR2)
return SYS_REFCURSOR 
IS f_cur SYS_REFCURSOR;
BEGIN
    open f_cur FOR
        SELECT  
        SaleIssueBill.fnumber as SaleIssueBillNumber,
        SaleIssueEntry.FSourceBillTypeID as SaleIssueSourceBillTypeID,
        SaleIssueEntry.FSourceBillNumber as SaleIssueSourceBillNumber,
        SaleIssueEntry.FMaterialID as SaleIssueMaterialID,
        SaleIssueEntry.FQTY as SaleIssueQTY,
        SaleIssueEntry.FUnWriteOffQty as SaleIssueUnWriteOffQty,
        SaleIssueEntry.FUnWriteOffAmount as SaleIssueUnWriteOffAmount,
        Currency.fnumber as currencyNumberOther,
        SaleIssueEntry.FBalanceCustomerID as balanceCustomerIDOther
        FROM T_IM_SaleIssueBill SaleIssueBill
        LEFT JOIN T_IM_SaleIssueEntry SaleIssueEntry ON SaleIssueBill.fid=SaleIssueEntry.fparentid
        LEFT JOIN T_SCM_BillType  BillType ON SaleIssueEntry.FSourceBillTypeID=BillType.fid
        LEFT JOIN T_BD_Currency Currency ON Currency.fid=SaleIssueBill.FCurrencyID
        WHERE SaleIssueEntry.FBalanceCustomerID =balanceCustomerID and SaleIssueEntry.FMaterialID=materialId
        AND SaleIssueBill.FBaseStatus='4'
        AND SaleIssueBill.fnumber like '%'||SaleIssueBillNumber||'%'
        and Currency.fnumber= currencyNumber    
        AND SaleIssueEntry.FSaleOrderEntrySeq=coreBillEntrySeq
        AND SaleIssueEntry.FSourceBillEntrySeq=saleIssueEntryOtherSeq;
    return f_cur;
END;

helperUtils:币别转换,统一转换为CNY

CREATE OR REPLACE 
function fn_toSwitchCNY(postReqCurrencyNumber in VARCHAR, inputAmount in numeric)
return number  is outAmount number(20,4);
    convertRate numeric(18,6);
BEGIN
    IF postReqCurrencyNumber ='CNY' THEN
        convertRate :=1;
    END IF;
    IF postReqCurrencyNumber !='CNY' THEN
        SELECT rate.FConvertRate INTO convertRate
        FROM T_BD_ExchangeRate rate 
        LEFT JOIN T_BD_ExchangeAux aux ON aux.fid=rate.FExchangeAuxID 
        LEFT JOIN T_BD_Currency ncy ON ncy.fid=aux.FSourceCurrencyID 
        WHERE upper(ncy.fnumber) =upper(postReqCurrencyNumber) AND aux.FTargetCurrencyID='dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC' 
        AND to_char(rate.FInvalidTime,'yyyy-mm-dd')>to_char(sysdate,'yyyy-MM-dd');
    END IF;
    outAmount:=inputAmount*convertRate;
    dbms_output.put_line('输入金额'||inputAmount||'转换绿'||convertRate||'结果'||outAmount);
    RETURN outAmount;
END;

4、把返回的结果集存放在临时表

CREATE OR REPLACE 
function Fn_saveAmount_Eas(inCustomerId IN VARCHAR2,inCurrencyNumber IN VARCHAR2,inAmountPost in numeric,inUnBillsAmount in numeric,inLoginID in VARCHAR2,inPosNumber in VARCHAR2) 
    return VARCHAR2  IS resultTable VARCHAR2(200);
    sqlStr VARCHAR2(2000);
    intoAmountPostReq numeric(18,6);
    intoUnBillsAmount numeric(18,6);

    amountPostReq numeric(18,6);
    unBillsAmount numeric(18,6);
    rowcountOther NUMBER;
begin
      --sqlStr:='create table zp201806190001(loginID varchar(200),posNumber varchar(200),timeOperate varchar(200),customID varchar(300),unBillsAmount numeric(18,6),billsAmount numeric(18,6))';
        --execute immediate sqlStr;
        intoAmountPostReq:=FN_TOSWITCHCNY(inCurrencyNumber,inAmountPost);--汇率转换  
        intoUnBillsAmount:=FN_TOSWITCHCNY(inCurrencyNumber,inUnBillsAmount);--汇率转换  \
        dbms_output.put_line('【为转换】'||inAmountPost||'【币别】'||inCurrencyNumber||'【转换后金额】'||intoAmountPostReq);
        execute immediate  'select count(customID) from zp201806190001 where loginID=:v1 and posNumber=:v2 and customID=:v3 ' into rowcountOther using inLoginID,inPosNumber,inCustomerId;
        dbms_output.put_line('【返回行数】'||rowcountOther);
        IF rowcountOther=0 THEN
            execute immediate 'insert into zp201806190001 (loginID,posNumber,customID,unBillsAmount,billsAmount)values(:data1,:data2,:data3,:data4,:data5)' using inLoginID,inPosNumber,inCustomerId,intoUnBillsAmount,intoAmountPostReq;
            dbms_output.put_line('临时表不存在==》新增');
        ELSE
            execute immediate 'select unBillsAmount,billsAmount from zp201806190001 where loginID=:v1 and posNumber=:v2 and customID=:v3 ' INTO unBillsAmount,amountPostReq using inLoginID,inPosNumber,inCustomerId;
            dbms_output.put_line('true'||'【更新金额】'||amountPostReq||'【+】'||intoAmountPostReq);
            amountPostReq:=amountPostReq+intoAmountPostReq;
            unBillsAmount:=unBillsAmount+intoUnBillsAmount;
            dbms_output.put_line('true'||'【unBillsAmount】'||unBillsAmount||'【amountPostReq】'||amountPostReq);
            execute immediate 'update zp201806190001 set unBillsAmount=:in1 ,billsAmount =:in2 where loginID=:in3 and posNumber=:in4 and customID=:in5' using unBillsAmount,amountPostReq,inLoginID,inPosNumber,inCustomerId;
        END IF;
        resultTable:='zp201806190001';
        return resultTable;
end;

最后处理完结果集把存在临时表的计算结果删除。

1、在计算已审核未出库时需要根据单据物料数量来算是否已完全出库，一张发货通知单可能存在多张销售出库单，
2、未核销数量也要求类似计算，物料数量可能存在未完全核销。

之所以写存储过程计算，一是为了学习存储过程实现计算这样逻辑，二是检查下效率问题，目前测出的速度比服务端稍微快点，不是很明显。关于优化存储过程还需要继续学习。还有就是程序可以批量检查单据问题。比如发货发多了？我也很奇怪，测出数据一张单子发货通知单数量小于出单数量。

奶茶半糖233

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
学习oracle存储过程

需求：发货通知单审核 ,应收款金额（统计全部币别）+发货通知单已审核未出货+已出货未开票金额+本单审核金额 &lt;= 信用档案金额 1、应收款金额：sum(应收汇总表.期末余额) group by 币别, 通过币别汇总应收汇总表.期末余额 2、发货通知单已审核未发货：发货通知单(审核) 下推销售出库单条件: 未发货库数...
复制链接

扫一扫