存储过程的例子(一)

最新推荐文章于 2022-04-27 09:18:15 发布

missccy

最新推荐文章于 2022-04-27 09:18:15 发布

阅读量305

点赞数

分类专栏： ORACLE ORACLE，存储过程

本文链接：https://blog.csdn.net/lhw244/article/details/75578374

版权

ORACLE 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

ORACLE，存储过程

5 篇文章 0 订阅

订阅专栏

1.这里用到了游标循环，字符串拼接赋值给游标，列转行，string分割

/**
 1.t_whxzp_plan_supplies_demand为表D，t_PROTOCOL_INVENTORY_INFO为表I
 2.先根据传来的值(可能为空)查询D表并根据(报装编号+大类)分组，找到每个组的序号
 3.I表根据(批次+大类+包号)分组，每个分组也有一些序号
 4.如果I里分组的序号>=D分组的序号，这个I组就是需要的数据
 5.把得到可用的I组数据插入到表t_PROTOCOL_INVENTORY_FPLSB
*/

CREATE OR REPLACE PROCEDURE proc_data_fetch(p_admin            VARCHAR2,
                                            p_planCode         VARCHAR2,
                                            p_materialTypes    VARCHAR2,
                                            p_contractCxdwms   VARCHAR2,
                                            p_createDate_begin VARCHAR2,
                                            p_createDate_end   VARCHAR2,
                                            p_tenderBatch      VARCHAR2) is
  --定义动态游标
  TYPE ref_cur_type IS REF CURSOR;
  cur_query  ref_cur_type;
  cur_query2 ref_cur_type;
  cur_query3 ref_cur_type;
  cur_query4 ref_cur_type;
  --定义存放语句的变量
  v_sqlStmt  string(10000);
  v_sqlStmt2 string(10000);
  v_sqlStmt3 string(10000);
  v_sqlStmt4 string(10000);
  --定义变量用来接收游标的值
  V_XH              VARCHAR2(18); --序号,用逗号分开,格式为'1.1,1.2'
  V_XH2             VARCHAR2(18); --序号,用逗号分开,格式为'1.1,1.2'
  V_XHTEMP          VARCHAR2(18); --序号
  V_BZBH            VARCHAR2(10); --报装编号
  V_MATERIAL_TYPES  VARCHAR2(30); --物料大类
  V_TENDER_BATCH    VARCHAR2(255); --招标批次
  V_PACKET_NUMBER   VARCHAR2(255); --包号
  V_MATERIAL_TYPES2 VARCHAR2(30); --物料大类
  V_JE_SUM          NUMBER(16, 6); --包总金额
  V_ALLOT_THRESHODL VARCHAR2(255); --分配阀值
  V_NUM             VARCHAR2(30); --物临时变量记录是否有值
  V_FLAG            boolean;

  --插入t_PROTOCOL_INVENTORY_FPLSB表所需变量
  F_ZXH                   VARCHAR2(50); --标识
  F_PROTOCOL_KCH          VARCHAR2(10); --协议库存编号
  F_PROTOCOL_LINE_KCH     NUMBER(5); --协议库存行号
  F_TENDER_BATCH          VARCHAR2(255); --招标批次
  F_XH                    VARCHAR2(255); --序号
  F_MATERIAL_CODE         VARCHAR2(255); --物料编码
  F_PACKET_NUMBER         VARCHAR2(255); --包号
  F_EXTENSION_DESCRIPTION VARCHAR2(255); --扩展描述
  F_REMARKS               VARCHAR2(255); --备注
  F_BID_NUMBER            NUMBER(13, 3); --中标数量
  F_SUPPLIER_CODE         VARCHAR2(255); --供应单位编码
  F_TAX_UNIT_PRICE        NUMBER(16, 6); --含税单价
  F_TAX_SUM_PRICE         NUMBER(16, 6); --含税总价
  F_ALLOT_THRESHODL       VARCHAR2(255); --分配阈值
  F_VALID_DATE            DATE; --有效期
  F_ALLOT_Q               VARCHAR2(255); --已使用数量
  F_JE_SUM                NUMBER(16, 6); --包总金额
  F_YSYZJE                NUMBER(16, 6); --已使用总金额
  F_STOP_MARK             VARCHAR2(255); --'Y'或'N'
  F_MATERIAL_TYPES        VARCHAR2(30); --物料大类
  F_RATE                  NUMBER(8, 2); --比例
  F_SUPPLIER_NAME         VARCHAR2(200); --供应商名称

BEGIN
  --找到没有被分配的物资需求计划
  v_sqlStmt := 'select wm_concat(distinct xh) xh,t.bzbh, t.material_types
          from t_whxzp_plan_supplies_demand t
           where t.alflag=''N'' and delete_mark=''N''';
  if p_planCode is not null then
    v_sqlStmt := v_sqlStmt || ' and  t.plan_code  = ' || chr(39) ||
                 p_planCode || chr(39);
  end if;

  if p_materialTypes is not null then
    v_sqlStmt := v_sqlStmt || ' and t.material_types  = ' || chr(39) ||
                 p_materialTypes || chr(39); --存储过程中单引号处理用chr(39)
  end if;

  if p_contractCxdwms is not null then
    v_sqlStmt := v_sqlStmt || ' and  t.contract_cxdwms  = ' || chr(39) ||
                 p_contractCxdwms || chr(39);
  end if;

  if p_createDate_begin is not null then
    v_sqlStmt := v_sqlStmt || ' and  t.create_date>=to_date(' || chr(39) ||
                 p_createDate_begin || chr(39) || ',''yyyy-mm-dd'')';
  end if;

  if p_createDate_end is not null then
    v_sqlStmt := v_sqlStmt || ' and  t.create_date<=to_date(' || chr(39) ||
                 p_createDate_end || chr(39) || ',''yyyy-mm-dd'')';
  end if;

  v_sqlStmt := v_sqlStmt || ' group by t.bzbh, t.material_types ';
  DBMS_OUTPUT.put_line('SQL:' || v_sqlStmt);
  --取未停分，已使用比例小于阀值的
  v_sqlStmt2 := 'select i.TENDER_BATCH, --招标批次
       i.PACKET_NUMBER, --包号
       i.MATERIAL_TYPES, --物料大类
       wm_concat(distinct i.xh) xh,
       i.je_sum,
       i.allot_threshodl
  from t_PROTOCOL_INVENTORY_INFO i
 where i.valid_date>sysdate and i.stop_mark=''N''';

  if p_tenderBatch is not null then
    v_sqlStmt2 := v_sqlStmt2 || ' and i.tender_batch  = ' || chr(39) ||
                  p_tenderBatch || chr(39);
  end if;

  v_sqlStmt2 := v_sqlStmt2 ||
                ' group by i.TENDER_BATCH, i.PACKET_NUMBER, i.MATERIAL_TYPES,i.je_sum,i.allot_threshodl 
                  having sum(TAX_UNIT_PRICE*ALLOT_Q)/je_sum<i.allot_threshodl';
  --将语句赋值给游标
  open cur_query for v_sqlStmt;

  LOOP
    --将游标的值给变量
    FETCH cur_query
      INTO V_XH, V_BZBH, V_MATERIAL_TYPES;
    exit when cur_query%notfound;
  
    open cur_query2 for v_sqlStmt2;
    LOOP
      --将游标的值给变量
      FETCH cur_query2
        INTO V_TENDER_BATCH, V_PACKET_NUMBER, V_MATERIAL_TYPES2, V_XH2,V_JE_SUM,V_ALLOT_THRESHODL;
      exit when cur_query2%notfound;
    
      V_FLAG := true; --初始值为true
      --将t_whxzp_plan_supplies_demand的序号分隔
      v_sqlStmt3 := 'SELECT REGEXP_SUBSTR(' || chr(39) || V_XH || chr(39) ||
                    ', ''[^,]+'',1,rownum)
       FROM DUAL
CONNECT BY ROWNUM <= LENGTH(' || chr(39) || V_XH ||
                    chr(39) || ') -
           LENGTH(REPLACE(' || chr(39) || V_XH ||
                    chr(39) || ', '','', '''')) + 1';
    
      open cur_query3 for v_sqlStmt3;
      LOOP
        --将游标的值给变量
        FETCH cur_query3
          INTO V_XHTEMP;
        exit when cur_query3%notfound;
      
        --分隔后和t_PROTOCOL_INVENTORY_INFO的序号比较，如果都匹配就将数据插入到t_PROTOCOL_INVENTORY_FPLSB
        V_NUM := instr(V_XH2, V_XHTEMP);
        --有一个没找到说明不匹配，标识符为False
        if V_NUM = 0 THEN
          V_FLAG := false;
        END IF;
      END LOOP;
      CLOSE cur_query3;
    
      if V_FLAG = true then
        --有匹配的数据则重新查找需要的变量，插入数据到t_PROTOCOL_INVENTORY_FPLSB
        V_FLAG     := false;
        v_sqlStmt4 := 'select to_char(i.protocol_kch) as protocol_kch,
       i.protocol_line_kch,
       i.tender_batch,
       i.xh,
       i.material_code,
       i.packet_number,
       i.extension_description,
       i.remarks,
       i.bid_number,
       i.supplier_code,
       i.tax_unit_price,
       i.tax_sum_price,
       i.valid_date,
       i.allot_threshodl,
       i.allot_q,
       i.stop_mark,
       i.material_types,
       i.supplier_name
  from t_PROTOCOL_INVENTORY_INFO i
 where  i.stop_mark=''N'' and i.tender_batch = ' ||
                      chr(39) || V_TENDER_BATCH || chr(39) ||
                      'and i.packet_number = ' || chr(39) ||
                      V_PACKET_NUMBER || chr(39) ||
                      'and i.material_types = ' || chr(39) ||
                      V_MATERIAL_TYPES2 || chr(39);
      
        select JE_SUM
          into F_JE_SUM --包总金额
          from t_PROTOCOL_INVENTORY_INFO i
         where i.tender_batch = V_TENDER_BATCH
           and i.packet_number = V_PACKET_NUMBER
           and i.material_types = V_MATERIAL_TYPES2
           and rownum = 1;
        select sum(i.tax_unit_price * i.allot_q)
          into F_YSYZJE --已使用总金额
          from t_PROTOCOL_INVENTORY_INFO i
         where i.tender_batch = V_TENDER_BATCH
           and i.packet_number = V_PACKET_NUMBER
           and i.material_types = V_MATERIAL_TYPES2;
        select sum(i.tax_unit_price * i.allot_q) / F_JE_SUM
          into F_RATE --比例
          from t_PROTOCOL_INVENTORY_INFO i
         where i.tender_batch = V_TENDER_BATCH
           and i.packet_number = V_PACKET_NUMBER
           and i.material_types = V_MATERIAL_TYPES2;
        open cur_query4 for v_sqlStmt4;
        LOOP
          --将游标的值给变量
          FETCH cur_query4
            INTO F_protocol_kch, F_PROTOCOL_LINE_KCH, F_TENDER_BATCH, F_XH, F_MATERIAL_CODE, F_PACKET_NUMBER, F_EXTENSION_DESCRIPTION, F_REMARKS, F_BID_NUMBER, F_SUPPLIER_CODE, F_TAX_UNIT_PRICE, F_TAX_SUM_PRICE, F_VALID_DATE, F_ALLOT_THRESHODL, F_ALLOT_Q, F_STOP_MARK, F_MATERIAL_TYPES, F_SUPPLIER_NAME;
          --主键为当前登陆者账号+招标批次+物料大类+包号+序号+日期（YYYYMMDD）
          F_ZXH := p_admin || '|' || F_TENDER_BATCH || '|' ||
                   F_MATERIAL_TYPES || '|' || F_PACKET_NUMBER || '|' || F_XH || '|' ||
                   to_char(sysdate, 'yyyyMMdd');
          exit when cur_query4%notfound;
        
          --一天内多次查询将删除之前的内容插入新的
          delete from t_PROTOCOL_INVENTORY_FPLSB where zxh = F_ZXH;
          insert into t_PROTOCOL_INVENTORY_FPLSB
          values
            (F_ZXH,
             F_PROTOCOL_KCH,
             F_PROTOCOL_LINE_KCH,
             F_TENDER_BATCH,
             F_XH,
             F_MATERIAL_CODE,
             F_PACKET_NUMBER,
             F_EXTENSION_DESCRIPTION,
             F_REMARKS,
             F_BID_NUMBER,
             F_SUPPLIER_CODE,
             F_TAX_UNIT_PRICE,
             F_TAX_SUM_PRICE,
             F_ALLOT_THRESHODL,
             F_VALID_DATE,
             F_ALLOT_Q,
             F_JE_SUM,
             F_YSYZJE,
             F_STOP_MARK,
             F_RATE,
             F_MATERIAL_TYPES,
             F_SUPPLIER_NAME);
          commit;
        END LOOP;
      END IF;
    END LOOP;
    CLOSE cur_query2;
  END LOOP;
  CLOSE cur_query;
END;