存储过程的例子(一)

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 DUALCONNECT 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;