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;