Java读取数据库中的xml格式内容，解析后修改属性节点内容并写回数据库
直接附代码：
1、测试用的xml内容
<mxGraphModel> <root> <mxCell id="-1" /> <mxCell id="0" parent="-1" /> <mxCell id="16" value="删除目标表" parent="0" script="{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID" fn="13" vertex="1"> <mxGeometry x="570" y="120" width="100" height="40" as="geometry" /> </mxCell> <mxCell id="2" value="创建目标表" parent="0" script="{DWD}.DWD_ACC_GRP_ADJUST_YYYYMMDD " fn="14" vertex="1"> <mxGeometry x="580" y="250" width="100" height="40" as="geometry" /> </mxCell> <mxCell id="3" value="向目标表插入数据" parent="0" script="{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID;
INSERT INTO {DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID
 (SO_NBR
 ,ITEM_CODE
 ,SPEC_ID
 ,BILL_NO
 ,ACCT_ID
 ,GROUP_ID
 ,BILL_MONTH
 ,PHONE_NO
 ,USER_ID
 ,ADJUST_REASON
 ,SRC_BILL_MONTH
 ,ADJUST_FEE
 ,KPI_ADJUST_FEE
 ,KPI_ADJUST_RATE
 ,ADJUST_ASSET_FEE
 ,ADJUST_BILLFEE
 ,ADJUST_EXTFEE
 ,OP_ID
 ,ORG_ID
 ,SO_DATE
 ,BEGIN_DATE
 ,END_DATE
 ,ADJUST_TYPE
 ,REASON_ONE
 ,REASON_TWO
 ,REASON_THREE
 ,REASON_FOUR
 ,REASON_FIVE
 ,DEAL_REASON
 ,REMARKS
 
 ,EXC_ADJUST_FEE 
 ,ADJUST_TAX_FEE 
 ,ADJUST_TAX_RATE 
 ,KPI_EXC_ADJUST_FEE
 ,KPI_ADJUST_TAX_FEE
 ,KPI_ADJUST_TAX_RATE
 ,EXC_ADJUST_ASSET_FEE
 ,ADJUST_ASSET_TAX_FEE
 ,ADJUST_ASSET_TAX_RATE
 ,EXC_ADJUST_BILLFEE
 ,ADJUST_BILL_TAX_FEE
 ,ADJUST_BILL_TAX_RATE
 ,EXC_ADJUST_EXTFEE
 ,ADJUST_EXT_TAX_FEE
 ,ADJUST_EXT_TAX_RATE)
SELECT 
 A.SO_NBR
 ,A.ITEM_CODE
 ,A.SPEC_ID
 ,A.BILL_NO
 ,A.ACCT_ID
 ,A.GROUP_ID
 ,A.BILL_MONTH
 ,A.PHONE_NO
 ,A.USER_ID
 ,A.ADJUST_REASON
 ,A.SRC_BILL_MONTH
 ,A.ADJUST_FEE
 ,A.KPI_ADJUST_FEE
 ,A.KPI_ADJUST_RATE
 ,A.ADJUST_ASSET_FEE
 ,A.ADJUST_BILLFEE
 ,A.ADJUST_EXTFEE
 ,A.OP_ID
 ,A.ORG_ID
 ,A.SO_DATE
 ,A.BEGIN_DATE
 ,A.END_DATE
 ,A.ADJUST_TYPE
 ,A.REASON_ONE
 ,A.REASON_TWO
 ,A.REASON_THREE
 ,A.REASON_FOUR
 ,A.REASON_FIVE
 ,A.DEAL_REASON
 ,A.REMARKS
 
,A.ADJUST_FEE-(A.ADJUST_FEE*1.00*T5.ADJUST_TAX_RATE/10000) AS EXC_ADJUST_FEE
,A.ADJUST_FEE*1.00*T5.ADJUST_TAX_RATE/10000 AS ADJUST_TAX_FEE
,T5.ADJUST_TAX_RATE AS ADJUST_TAX_RATE
,A.KPI_ADJUST_FEE-(A.KPI_ADJUST_FEE*1.00*T5.KPI_ADJUST_TAX_RATE/10000) AS KPI_EXC_ADJUST_FEE
,A.KPI_ADJUST_FEE*1.00*T5.KPI_ADJUST_TAX_RATE/10000 AS KPI_EXC_ADJUST_FEE
,T5.KPI_ADJUST_TAX_RATE AS KPI_ADJUST_TAX_RATE
,A.ADJUST_ASSET_FEE-(A.ADJUST_ASSET_FEE*1.00*T5.ADJUST_ASSET_TAX_RATE/10000) AS EXC_ADJUST_ASSET_FEE
,A.ADJUST_ASSET_FEE*1.00*T5.ADJUST_ASSET_TAX_RATE/10000 AS ADJUST_ASSET_TAX_FEE
,T5.ADJUST_ASSET_TAX_RATE AS ADJUST_ASSET_TAX_RATE
,A.ADJUST_BILLFEE-(A.ADJUST_BILLFEE*1.00*T5.ADJUST_BILL_TAX_RATE/10000) AS EXC_ADJUST_BILLFEE
,A.ADJUST_BILLFEE*1.00*T5.ADJUST_BILL_TAX_RATE/10000 AS ADJUST_BILL_TAX_FEE
,T5.ADJUST_BILL_TAX_RATE AS ADJUST_BILL_TAX_RATE
,A.ADJUST_EXTFEE-(A.ADJUST_EXTFEE*1.00*T5.ADJUST_EXT_TAX_RATE/10000) AS EXC_ADJUST_EXTFEE
,A.ADJUST_EXTFEE*1.00*T5.ADJUST_EXT_TAX_RATE/10000 AS ADJUST_EXT_TAX_FEE
,T5.ADJUST_EXT_TAX_RATE AS ADJUST_EXT_TAX_RATE
FROM {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID A
 LEFT JOIN {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID T5
 ON A.ITEM_CODE=T5.ITEM_CODE
WITH UR" fn="2" vertex="1" remark="向目标表插入数据，取集团客户对应的账户调账信息。"> <mxGeometry x="580" y="340" width="100" height="40" as="geometry" /> </mxCell> <mxCell id="5" value="完成时" style="strokeColor=#000000" parent="0" source="16" target="2" edge="1"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="6" value="成功时" style="strokeColor=#00FF00" parent="0" source="2" target="3" edge="1" linkType="0"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="8" value="结束" parent="0" script="TARGET_TABLE:{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID


DATA_DATE:&TASK_ID

--SUCCESS--
" fn="23" vertex="1"> <mxGeometry x="790" y="390" width="110" height="40" as="geometry" /> </mxCell> <mxCell id="1" value="开始" parent="0" script="TARGET_TABLE:{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID


DATA_DATE:&TASK_ID

--BEGIN—
" fn="23" vertex="1"> <mxGeometry x="150" y="40" width="100" height="40" as="geometry" /> </mxCell> <mxCell id="11" value="RUNSTATS" parent="0" script="{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID" fn="11" vertex="1"> <mxGeometry x="790" y="50" width="110" height="40" as="geometry" /> </mxCell> <mxCell id="12" value="成功时" style="strokeColor=#00FF00" parent="0" source="3" target="11" edge="1" linkType="0"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="17" value="表赋权限" parent="0" script="{DWD}.DWD_ACC_GRP_ADJUST_YYYYMMDD" fn="45" vertex="1"> <mxGeometry x="790" y="140" width="110" height="40" as="geometry" /> </mxCell> <mxCell id="18" value="成功时" style="strokeColor=#00FF00" parent="0" source="11" target="17" edge="1" linkType="0"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="27" value="删除一张表tmp01" parent="0" vertex="1" remark="删除一张表tmp01" fn="13" script="{TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID"> <mxGeometry x="150" y="130" width="100" height="40" as="geometry" /> </mxCell> <mxCell id="28" value="创建临时表TMP01" parent="0" vertex="1" remark="创建临时表TMP01" fn="9" script="CREATE TABLE {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID LIKE
{TEMPLATE}.DWD_ACC_GRP_ADJUST_YYYYMMDD;ITEM_CODE;{TbsTemp};{TbsIdx}"> <mxGeometry x="150" y="230" width="100" height="40" as="geometry" /> </mxCell> <mxCell id="29" value="插入tmp01数据" parent="0" vertex="1" remark="插入tmp01数据" fn="2" script="{TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID;
INSERT INTO {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID
 (SO_NBR
 ,ITEM_CODE
 ,SPEC_ID
 ,BILL_NO
 ,ACCT_ID
 ,GROUP_ID
 ,BILL_MONTH
 ,PHONE_NO
 ,USER_ID
 ,ADJUST_REASON
 ,SRC_BILL_MONTH
 ,ADJUST_FEE
 ,KPI_ADJUST_FEE
 ,KPI_ADJUST_RATE
 ,ADJUST_ASSET_FEE
 ,ADJUST_BILLFEE
 ,ADJUST_EXTFEE
 ,OP_ID
 ,ORG_ID
 ,SO_DATE
 ,BEGIN_DATE
 ,END_DATE
 ,ADJUST_TYPE
 ,REASON_ONE
 ,REASON_TWO
 ,REASON_THREE
 ,REASON_FOUR
 ,REASON_FIVE
 ,DEAL_REASON
 ,REMARKS)
SELECT
 T3.SO_NBR
,T3.ITEM_CODE
,T3.SPEC_ID
,T3.BILL_NO
,T3.ACCT_ID
,T1.GROUP_ID
,T3.BILL_MONTH
,T3.IDENTITY AS PHONE_NO
,T3.RESOURCE_ID AS USER_ID
,T3.ADJUST_REASON
,T3.SRC_BILL_MONTH
,VALUE(T3.ADJUST_TOTAL, 0)*1.00/100 AS ADJUST_FEE
,CASE
 WHEN t4.item_name like ''%上期%违约金%'' or t4.item_name like ''%本期%违约金%'' THEN (VALUE(T3.ADJUST_TOTAL,0)*1.00/100)*1.0
 WHEN T3.SRC_BILL_MONTH<''201401'' OR T4.LOGIN_NAME IN (''H0106'',''I9829'') THEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100
 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -3 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE)-1 MONTHS)),6) THEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100
 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -6 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE) -4 MONTHS)),6) THEN (VALUE(T3.ADJUST_TOTAL,0)*1.00/100)*0.5
 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH <= LEFT(CHAR(INTEGER(DATE(SO_DATE) -7 MONTHS)),6) THEN 0
 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -3 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE)-1 MONTHS)),6) THEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100
 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -6 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE) -4 MONTHS)),6) THEN (VALUE(T3.ADJUST_TOTAL,0)*1.00/100)*1.5
 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH <= LEFT(CHAR(INTEGER(DATE(SO_DATE) -7 MONTHS)),6) THEN (VALUE(T3.ADJUST_TOTAL,0)*1.00/100)*2
 END KPI_ADJUST_FEE
,CASE
 WHEN t4.item_name like ''%上期%违约金%'' or t4.item_name like ''%本期%违约金%'' THEN 100
 WHEN T3.SRC_BILL_MONTH<''201401'' OR T4.LOGIN_NAME IN (''H0106'',''I9829'') THEN 100
 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -3 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE)-1 MONTHS)),6) THEN 100
 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -6 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE) -4 MONTHS)),6) THEN 50
 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH <= LEFT(CHAR(INTEGER(DATE(SO_DATE) -7 MONTHS)),6) THEN 0
 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -3 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE)-1 MONTHS)),6) THEN 100
 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -6 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE) -4 MONTHS)),6) THEN 150
 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH <= LEFT(CHAR(INTEGER(DATE(SO_DATE) -7 MONTHS)),6) THEN 200
 END KPI_ADJUST_RATE
,VALUE(T3.ADJUST_ASSETFEE, 0)*1.00/100 AS ADJUST_ASSETFEE
,VALUE(T3.ADJUST_BILLFEE, 0)*1.00/100 AS ADJUST_BILLFEE
,VALUE(T3.ADJUST_EXTFEE, 0)*1.00/100 AS ADJUST_EXTFEE
,T3.OP_ID
,T3.ORG_ID
,T3.SO_DATE
,T3.BEGIN_DATE
,T3.END_DATE
,T3.ADJUST_TYPE
,T3.REASON_ONE
,T3.REASON_TWO
,T3.REASON_THREE
,T3.REASON_FOUR
,T3.REASON_FIVE
,T3.DEAL_REASON
,T3.REMARK AS REMARKS
FROM {DWD}.DWD_PRTY_GRP_INFO_&TASK_ID T1
INNER JOIN {ODS}.ODS_CM_CUST_ACCT_REL_&TASK_ID T2 ON T1.GRP_CUST_ID=T2.REL_CUST_ID
INNER JOIN {ODS}.ODS_CA_BILL_BUSI_REC_&TASK_ID T3 ON T2.ACCT_ID=T3.ACCT_ID
LEFT JOIN 
(select op_id,login_name from {DIM}.DIM_PRTY_OPER_INFO where start_date<''&TASK_DATE'' and end_date>''&TASK_DATE'' )T4 ON T3.OP_ID=T4.OP_ID
 left join (
 select item_code,item_name 
 from {DIM}.DIM_ACC_ITEM_CODE 
 WHERE start_date<=''&TASK_DATE'' and end_date>''&TASK_DATE''
 ) t4
 on t3.ITEM_CODE=t4.ITEM_CODE
WHERE T2.STATE=''U'';"> <mxGeometry x="150" y="340" width="100" height="40" as="geometry" /> </mxCell> <mxCell id="31" value="删除一张表tmp02" parent="0" vertex="1" remark="删除一张表tmp01" fn="13" script="{TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID"> <mxGeometry x="350" y="130" width="100" height="40" as="geometry" /> </mxCell> <mxCell id="32" value="创建临时表TMP02" parent="0" vertex="1" remark="创建临时表TMP02" fn="9" script="CREATE TABLE {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID LIKE
{TEMPLATE}.DWD_ACC_GRP_ADJUST_YYYYMMDD;ITEM_CODE;{TbsTemp};{TbsIdx}"> <mxGeometry x="350" y="230" width="100" height="40" as="geometry" /> </mxCell> <mxCell id="33" value="插入tmp02数据" parent="0" vertex="1" remark="插入tmp02数据--得到税率" fn="2" script="{TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID;
INSERT INTO {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID
 (
 ITEM_CODE
 ,ADJUST_TAX_RATE 
 ,KPI_ADJUST_TAX_RATE
 ,ADJUST_ASSET_TAX_RATE
 ,ADJUST_BILL_TAX_RATE
 ,ADJUST_EXT_TAX_RATE
)
SELECT DISTINCT 
a.ITEM_CODE
,T5.TAX_RATE AS ADJUST_TAX_RATE
,T5.TAX_RATE AS KPI_ADJUST_TAX_RATE
,T5.TAX_RATE AS ADJUST_ASSET_TAX_RATE
,T5.TAX_RATE AS ADJUST_BILL_TAX_RATE
,T5.TAX_RATE AS ADJUST_EXT_TAX_RATE
FROM {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID A
 left join {ODS}.ODS_PM_ITEM_TAX_RATE_&TASK_ID T5
 on a.ITEM_CODE=T5.ITEM_CODE
WITH UR"> <mxGeometry x="350" y="340" width="100" height="40" as="geometry" /> </mxCell> <mxCell id="34" value="成功时" style="strokeColor=#00FF00" parent="0" source="1" target="27" edge="1" linkType="0"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="35" value="完成时" style="strokeColor=#000000" parent="0" source="27" target="28" edge="1"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="36" value="成功时" style="strokeColor=#00FF00" parent="0" source="28" target="29" edge="1" linkType="0"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="40" value="成功时" style="strokeColor=#00FF00" parent="0" source="29" target="31" edge="1" linkType="0"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="41" value="完成时" style="strokeColor=#000000" parent="0" source="31" target="32" edge="1"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="42" value="成功时" style="strokeColor=#00FF00" parent="0" source="32" target="33" edge="1" linkType="0"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="43" value="成功时" style="strokeColor=#00FF00" parent="0" source="33" target="16" edge="1" linkType="0"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="44" value="删除一张表tmp01" parent="0" vertex="1" remark="删除一张表tmp01" fn="13" script="{TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID"> <mxGeometry x="790" y="220" width="100" height="40" as="geometry" /> </mxCell> <mxCell id="45" value="删除一张表tmp02" parent="0" vertex="1" remark="删除一张表tmp01" fn="13" script="{TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID"> <mxGeometry x="790" y="290" width="100" height="40" as="geometry" /> </mxCell> <mxCell id="46" value="成功时" style="strokeColor=#00FF00" parent="0" source="17" target="44" edge="1" linkType="0"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="47" value="完成时" style="strokeColor=#000000" parent="0" source="44" target="45" edge="1"> <mxGeometry relative="1" as="geometry" /> </mxCell> <mxCell id="48" value="完成时" style="strokeColor=#000000" parent="0" source="45" target="8" edge="1"> <mxGeometry relative="1" as="geometry" /> </mxCell> </root> </mxGraphModel>
解析xml文件，转换通过parseXml将script属性值中的DB2数据库语法转换成Oracle语法，再写回数据库。
2、Java实现
package com.asiainfo.db.parserXml; import java.io.BufferedReader; import java.io.ByteArrayOutputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.Reader; import java.io.StringReader; import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.ParserConfigurationException; import javax.xml.transform.Transformer; import javax.xml.transform.TransformerConfigurationException; import javax.xml.transform.TransformerException; import javax.xml.transform.TransformerFactory; import javax.xml.transform.dom.DOMSource; import javax.xml.transform.stream.StreamResult; import org.apache.log4j.Logger; import org.w3c.dom.Document; import org.w3c.dom.Element; import org.w3c.dom.Node; import org.w3c.dom.NodeList; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import com.asiainfo.db.others.Xml; import oracle.jdbc.driver.OracleResultSet; import oracle.sql.CLOB; /** * @ClassName: OracleConversion * @Description: TODO(这里用一句话描述这个类的作用) */ public class OracleConversion { private static Logger logger = Logger.getLogger(OracleConversion.class); private static Connection conn2; private static Statement st2; private static ResultSet rt2; private static Config config = Config.getInstance(); private static Parser parserXml = new Parser(); /** * @Title: dbConn * @Description: TODO(数据库连接) */ public static Connection dbConn(String driverClass, String url, String username, String password) { Connection conn = null; try { Class.forName(driverClass); conn = DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e1) { logger.error("驱动类没找到：" + e1.getMessage()); } catch (SQLException e2) { logger.error("数据库连接失败： " + e2.getMessage()); } return conn; } public static void main(String[] args) { String second_driverclass = config.secondDriverclass; String second_url = config.secondUrl; String second_username = config.secondUsername; String second_password = config.secondPassword; String second_tablename = config.secondTablename; String second_column = config.secondCloumn; conn2 = dbConn(second_driverclass, second_url, second_username, second_password); if (conn2 != null) { logger.info("数据库连接成功。"); try { st2 = conn2.createStatement(); rt2 = st2.executeQuery("select proc_name, xml from md." + second_tablename + " for update"); while (rt2.next()) { String proc_name = rt2.getString("proc_name"); String rt_xml = rt2.getString("xml"); System.out.println("proc_name: " + proc_name); System.out.println("xml before convertion: " + rt_xml); String xml_script = null; String xml_new = null; if ((rt_xml != null) && rt_xml.contains("script")) { CLOB clob = ((OracleResultSet) rt2).getCLOB(2); String xml = null; try { xml = ClobToString(clob); String parse_Xml = parseXML(xml); System.out.println("xml after convertion: " + parse_Xml); clob.putString(2, parse_Xml); String updateSQL = "update MD.PROC set XML = ? where PROC_NAME = '" + proc_name + "'"; PreparedStatement pstmt = conn2.prepareStatement(updateSQL); pstmt.setClob(1, clob); int result = pstmt.executeUpdate(); if (result > 0) { System.out.println("数据插入成功：" + proc_name + ". \n"); pstmt.close(); } } catch (IOException e) { logger.error(e.getMessage()); } } else { System.out.println(); continue; } } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { rt2.close(); st2.close(); conn2.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } /** * @Title: parseXML * @Description: TODO(获取xml中script属性的值，并转换) */ public static String parseXML(String xml) { StringReader sr = new StringReader(xml); InputSource is = new InputSource(sr); String parseXmlStr = null; int count = 0; try { // step 1: 获得dom解析器工厂（工作的作用是用于创建具体的解析器） DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); // step 2:获得具体的dom解析器 DocumentBuilder builder = factory.newDocumentBuilder(); // step3: 解析一个xml文档，获得Document对象（根结点） String to document //Document document = builder.parse(new File("D://candidate.xml")); Document document = builder.parse(is); //获取mxGraphModel属性 Element mxGraphModel = document.getDocumentElement(); System.out.println("mxGraphModel元素: " + mxGraphModel.getNodeName()); //获取root节点 NodeList root = mxGraphModel.getChildNodes(); for(int i = 0; i < root.getLength(); i++) { Node node_root = root.item(i); //获取mxCell节点 NodeList mxCell = node_root.getChildNodes(); for(int j = 0; j < mxCell.getLength(); j++) { String script = null; String scriptNew = null; // Node node_mxcell = mxCell.item(j); // if("mxCell".equals(node_mxcell.getNodeName()) && (null != node_mxcell.getAttributes().getNamedItem("script"))) { // script = node_mxcell.getAttributes().getNamedItem("script").getNodeValue().toString(); // System.out.println("【script[" + ++count + "] before】\n" + script); // // //数据库语法转换操作 // scriptNew = parserXml.parse(script).replaceAll("'", "''"); // System.out.println("【script[" + count + "] after】\n" + scriptNew + "\n"); // // //更新值 // node_mxcell.getFirstChild().setNodeValue(scriptNew); // } Element element = (Element)mxCell.item(j); if("mxCell".equals(element.getNodeName()) && (null != element.getAttributes().getNamedItem("script"))) { script = element.getAttribute("script"); System.out.println("【script[" + ++count + "] before】\n" + script); //数据库语法转换操作 scriptNew = parserXml.parse(script); // scriptNew = parserXml.parse(script).replaceAll("'", "''"); System.out.println("【script[" + count + "] after】\n" + scriptNew + "\n"); //更新值 element.setAttribute("script", scriptNew); } } } saveXml("D://xml.xml", document); TransformerFactory transformerFactory = TransformerFactory.newInstance(); Transformer transformer = transformerFactory.newTransformer(); transformer.setOutputProperty("encoding","UTF8");//解决中文问题 ByteArrayOutputStream stream = new ByteArrayOutputStream(); transformer.transform(new DOMSource(document), new StreamResult(stream)); parseXmlStr = stream.toString(); } catch (ParserConfigurationException pce) { // TODO Auto-generated catch block logger.error(pce.getMessage()); } catch (SAXException saxe) { // TODO Auto-generated catch block logger.error(saxe.getMessage()); } catch (IOException ioe) { // TODO Auto-generated catch block logger.error(ioe.getMessage()); } catch (TransformerConfigurationException transe) { // TODO Auto-generated catch block logger.error(transe.getMessage()); } catch (TransformerException te) { // TODO Auto-generated catch block logger.error(te.getMessage()); } return parseXmlStr; } /** * @Title: ClobToString * @Description: TODO(将clob类型转换成string) */ public static String ClobToString(CLOB clob) throws SQLException, IOException { String reString = ""; Reader is = clob.getCharacterStream();// 得到流 BufferedReader br = new BufferedReader(is); String s = br.readLine(); StringBuffer sb = new StringBuffer(); while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING sb.append(s); s = br.readLine(); }
br.close(); reString = sb.toString(); return reString; } /** * @Title: output * @Description: TODO(将node的XML字符串输出到控制台) */ public static void output(Node node) { TransformerFactory transFactory=TransformerFactory.newInstance(); try { Transformer transformer = transFactory.newTransformer(); transformer.setOutputProperty("encoding", "UTF8"); transformer.setOutputProperty("indent", "yes"); DOMSource source=new DOMSource(); source.setNode(node); StreamResult result=new StreamResult(); result.setOutputStream(System.out); transformer.transform(source, result); } catch (TransformerConfigurationException e) { e.printStackTrace(); } catch (TransformerException e) { e.printStackTrace(); } } /** * @Title: saveXml * @Description: TODO(将Document输出到文件) */ public static void saveXml(String fileName, Document doc) { TransformerFactory transFactory=TransformerFactory.newInstance(); try { Transformer transformer = transFactory.newTransformer(); transformer.setOutputProperty("indent", "yes"); DOMSource source=new DOMSource(); source.setNode(doc); StreamResult result=new StreamResult(); result.setOutputStream(new FileOutputStream(fileName)); transformer.transform(source, result); } catch (TransformerConfigurationException e) { e.printStackTrace(); } catch (TransformerException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); } } }
3、转换过程
【script[1] before】 {DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID 【script[1] after】 {DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID 【script[2] before】 {DWD}.DWD_ACC_GRP_ADJUST_YYYYMMDD 【script[2] after】 {DWD}.DWD_ACC_GRP_ADJUST_YYYYMMDD 【script[3] before】 {DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID; INSERT INTO {DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID (SO_NBR ,ITEM_CODE ,SPEC_ID ,BILL_NO ,ACCT_ID ,GROUP_ID ,BILL_MONTH ,PHONE_NO ,USER_ID ,ADJUST_REASON ,SRC_BILL_MONTH ,ADJUST_FEE ,KPI_ADJUST_FEE ,KPI_ADJUST_RATE ,ADJUST_ASSET_FEE ,ADJUST_BILLFEE ,ADJUST_EXTFEE ,OP_ID ,ORG_ID ,SO_DATE ,BEGIN_DATE ,END_DATE ,ADJUST_TYPE ,REASON_ONE ,REASON_TWO ,REASON_THREE ,REASON_FOUR ,REASON_FIVE ,DEAL_REASON ,REMARKS ,EXC_ADJUST_FEE ,ADJUST_TAX_FEE ,ADJUST_TAX_RATE ,KPI_EXC_ADJUST_FEE ,KPI_ADJUST_TAX_FEE ,KPI_ADJUST_TAX_RATE ,EXC_ADJUST_ASSET_FEE ,ADJUST_ASSET_TAX_FEE ,ADJUST_ASSET_TAX_RATE ,EXC_ADJUST_BILLFEE ,ADJUST_BILL_TAX_FEE ,ADJUST_BILL_TAX_RATE ,EXC_ADJUST_EXTFEE ,ADJUST_EXT_TAX_FEE ,ADJUST_EXT_TAX_RATE) SELECT A.SO_NBR ,A.ITEM_CODE ,A.SPEC_ID ,A.BILL_NO ,A.ACCT_ID ,A.GROUP_ID ,A.BILL_MONTH ,A.PHONE_NO ,A.USER_ID ,A.ADJUST_REASON ,A.SRC_BILL_MONTH ,A.ADJUST_FEE ,A.KPI_ADJUST_FEE ,A.KPI_ADJUST_RATE ,A.ADJUST_ASSET_FEE ,A.ADJUST_BILLFEE ,A.ADJUST_EXTFEE ,A.OP_ID ,A.ORG_ID ,A.SO_DATE ,A.BEGIN_DATE ,A.END_DATE ,A.ADJUST_TYPE ,A.REASON_ONE ,A.REASON_TWO ,A.REASON_THREE ,A.REASON_FOUR ,A.REASON_FIVE ,A.DEAL_REASON ,A.REMARKS ,A.ADJUST_FEE-(A.ADJUST_FEE*1.00*T5.ADJUST_TAX_RATE/10000) AS EXC_ADJUST_FEE ,A.ADJUST_FEE*1.00*T5.ADJUST_TAX_RATE/10000 AS ADJUST_TAX_FEE ,T5.ADJUST_TAX_RATE AS ADJUST_TAX_RATE ,A.KPI_ADJUST_FEE-(A.KPI_ADJUST_FEE*1.00*T5.KPI_ADJUST_TAX_RATE/10000) AS KPI_EXC_ADJUST_FEE ,A.KPI_ADJUST_FEE*1.00*T5.KPI_ADJUST_TAX_RATE/10000 AS KPI_EXC_ADJUST_FEE ,T5.KPI_ADJUST_TAX_RATE AS KPI_ADJUST_TAX_RATE ,A.ADJUST_ASSET_FEE-(A.ADJUST_ASSET_FEE*1.00*T5.ADJUST_ASSET_TAX_RATE/10000) AS EXC_ADJUST_ASSET_FEE ,A.ADJUST_ASSET_FEE*1.00*T5.ADJUST_ASSET_TAX_RATE/10000 AS ADJUST_ASSET_TAX_FEE ,T5.ADJUST_ASSET_TAX_RATE AS ADJUST_ASSET_TAX_RATE ,A.ADJUST_BILLFEE-(A.ADJUST_BILLFEE*1.00*T5.ADJUST_BILL_TAX_RATE/10000) AS EXC_ADJUST_BILLFEE ,A.ADJUST_BILLFEE*1.00*T5.ADJUST_BILL_TAX_RATE/10000 AS ADJUST_BILL_TAX_FEE ,T5.ADJUST_BILL_TAX_RATE AS ADJUST_BILL_TAX_RATE ,A.ADJUST_EXTFEE-(A.ADJUST_EXTFEE*1.00*T5.ADJUST_EXT_TAX_RATE/10000) AS EXC_ADJUST_EXTFEE ,A.ADJUST_EXTFEE*1.00*T5.ADJUST_EXT_TAX_RATE/10000 AS ADJUST_EXT_TAX_FEE ,T5.ADJUST_EXT_TAX_RATE AS ADJUST_EXT_TAX_RATE FROM {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID A LEFT JOIN {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID T5 ON A.ITEM_CODE=T5.ITEM_CODE WITH UR yes insert into 【script[3] after】 {DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID; INSERT INTO {DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID (SO_NBR ,ITEM_CODE ,SPEC_ID ,BILL_NO ,ACCT_ID ,GROUP_ID ,BILL_MONTH ,PHONE_NO ,USER_ID ,ADJUST_REASON ,SRC_BILL_MONTH ,ADJUST_FEE ,KPI_ADJUST_FEE ,KPI_ADJUST_RATE ,ADJUST_ASSET_FEE ,ADJUST_BILLFEE ,ADJUST_EXTFEE ,OP_ID ,ORG_ID ,SO_DATE ,BEGIN_DATE ,END_DATE ,ADJUST_TYPE ,REASON_ONE ,REASON_TWO ,REASON_THREE ,REASON_FOUR ,REASON_FIVE ,DEAL_REASON ,REMARKS ,EXC_ADJUST_FEE ,ADJUST_TAX_FEE ,ADJUST_TAX_RATE ,KPI_EXC_ADJUST_FEE ,KPI_ADJUST_TAX_FEE ,KPI_ADJUST_TAX_RATE ,EXC_ADJUST_ASSET_FEE ,ADJUST_ASSET_TAX_FEE ,ADJUST_ASSET_TAX_RATE ,EXC_ADJUST_BILLFEE ,ADJUST_BILL_TAX_FEE ,ADJUST_BILL_TAX_RATE ,EXC_ADJUST_EXTFEE ,ADJUST_EXT_TAX_FEE ,ADJUST_EXT_TAX_RATE) SELECT A.SO_NBR ,A.ITEM_CODE ,A.SPEC_ID ,A.BILL_NO ,A.ACCT_ID ,A.GROUP_ID ,A.BILL_MONTH ,A.PHONE_NO ,A.USER_ID ,A.ADJUST_REASON ,A.SRC_BILL_MONTH ,A.ADJUST_FEE ,A.KPI_ADJUST_FEE ,A.KPI_ADJUST_RATE ,A.ADJUST_ASSET_FEE ,A.ADJUST_BILLFEE ,A.ADJUST_EXTFEE ,A.OP_ID ,A.ORG_ID ,A.SO_DATE ,A.BEGIN_DATE ,A.END_DATE ,A.ADJUST_TYPE ,A.REASON_ONE ,A.REASON_TWO ,A.REASON_THREE ,A.REASON_FOUR ,A.REASON_FIVE ,A.DEAL_REASON ,A.REMARKS ,A.ADJUST_FEE-(A.ADJUST_FEE*1.00*T5.ADJUST_TAX_RATE/10000) AS EXC_ADJUST_FEE ,A.ADJUST_FEE*1.00*T5.ADJUST_TAX_RATE/10000 AS ADJUST_TAX_FEE ,T5.ADJUST_TAX_RATE AS ADJUST_TAX_RATE ,A.KPI_ADJUST_FEE-(A.KPI_ADJUST_FEE*1.00*T5.KPI_ADJUST_TAX_RATE/10000) AS KPI_EXC_ADJUST_FEE ,A.KPI_ADJUST_FEE*1.00*T5.KPI_ADJUST_TAX_RATE/10000 AS KPI_EXC_ADJUST_FEE ,T5.KPI_ADJUST_TAX_RATE AS KPI_ADJUST_TAX_RATE ,A.ADJUST_ASSET_FEE-(A.ADJUST_ASSET_FEE*1.00*T5.ADJUST_ASSET_TAX_RATE/10000) AS EXC_ADJUST_ASSET_FEE ,A.ADJUST_ASSET_FEE*1.00*T5.ADJUST_ASSET_TAX_RATE/10000 AS ADJUST_ASSET_TAX_FEE ,T5.ADJUST_ASSET_TAX_RATE AS ADJUST_ASSET_TAX_RATE ,A.ADJUST_BILLFEE-(A.ADJUST_BILLFEE*1.00*T5.ADJUST_BILL_TAX_RATE/10000) AS EXC_ADJUST_BILLFEE ,A.ADJUST_BILLFEE*1.00*T5.ADJUST_BILL_TAX_RATE/10000 AS ADJUST_BILL_TAX_FEE ,T5.ADJUST_BILL_TAX_RATE AS ADJUST_BILL_TAX_RATE ,A.ADJUST_EXTFEE-(A.ADJUST_EXTFEE*1.00*T5.ADJUST_EXT_TAX_RATE/10000) AS EXC_ADJUST_EXTFEE ,A.ADJUST_EXTFEE*1.00*T5.ADJUST_EXT_TAX_RATE/10000 AS ADJUST_EXT_TAX_FEE ,T5.ADJUST_EXT_TAX_RATE AS ADJUST_EXT_TAX_RATE FROM {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID A LEFT JOIN {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID T5 ON A.ITEM_CODE=T5.ITEM_CODE ;{sql_compress} 【script[4] before】 TARGET_TABLE:{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID DATA_DATE:&TASK_ID --SUCCESS-- 【script[4] after】 TARGET_TABLE:{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID DATA_DATE:&TASK_ID --SUCCESS-- 【script[5] before】 TARGET_TABLE:{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID DATA_DATE:&TASK_ID --BEGIN— 【script[5] after】 TARGET_TABLE:{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID DATA_DATE:&TASK_ID --BEGIN— 【script[6] before】 {DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID 【script[6] after】 {DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID 【script[7] before】 {DWD}.DWD_ACC_GRP_ADJUST_YYYYMMDD 【script[7] after】 {DWD}.DWD_ACC_GRP_ADJUST_YYYYMMDD 【script[8] before】 {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID 【script[8] after】 {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID 【script[9] before】 CREATE TABLE {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID LIKE {TEMPLATE}.DWD_ACC_GRP_ADJUST_YYYYMMDD;ITEM_CODE;{TbsTemp};{TbsIdx} yes create table 【script[9] after】 create table {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID as select * from {TEMPLATE}.DWD_ACC_GRP_ADJUST_YYYYMMDD where 1 <> 1;ITEM_CODE;{TbsTemp};{TbsIdx};{ddl_compress} 【script[10] before】 {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID; INSERT INTO {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID (SO_NBR ,ITEM_CODE ,SPEC_ID ,BILL_NO ,ACCT_ID ,GROUP_ID ,BILL_MONTH ,PHONE_NO ,USER_ID ,ADJUST_REASON ,SRC_BILL_MONTH ,ADJUST_FEE ,KPI_ADJUST_FEE ,KPI_ADJUST_RATE ,ADJUST_ASSET_FEE ,ADJUST_BILLFEE ,ADJUST_EXTFEE ,OP_ID ,ORG_ID ,SO_DATE ,BEGIN_DATE ,END_DATE ,ADJUST_TYPE ,REASON_ONE ,REASON_TWO ,REASON_THREE ,REASON_FOUR ,REASON_FIVE ,DEAL_REASON ,REMARKS) SELECT T3.SO_NBR ,T3.ITEM_CODE ,T3.SPEC_ID ,T3.BILL_NO ,T3.ACCT_ID ,T1.GROUP_ID ,T3.BILL_MONTH ,T3.IDENTITY AS PHONE_NO ,T3.RESOURCE_ID AS USER_ID ,T3.ADJUST_REASON ,T3.SRC_BILL_MONTH ,VALUE(T3.ADJUST_TOTAL, 0)*1.00/100 AS ADJUST_FEE ,CASE WHEN t4.item_name like ''%上期%违约金%'' or t4.item_name like ''%本期%违约金%'' THEN (VALUE(T3.ADJUST_TOTAL,0)*1.00/100)*1.0 WHEN T3.SRC_BILL_MONTH<''201401'' OR T4.LOGIN_NAME IN (''H0106'',''I9829'') THEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -3 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE)-1 MONTHS)),6) THEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -6 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE) -4 MONTHS)),6) THEN (VALUE(T3.ADJUST_TOTAL,0)*1.00/100)*0.5 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH <= LEFT(CHAR(INTEGER(DATE(SO_DATE) -7 MONTHS)),6) THEN 0 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -3 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE)-1 MONTHS)),6) THEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -6 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE) -4 MONTHS)),6) THEN (VALUE(T3.ADJUST_TOTAL,0)*1.00/100)*1.5 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH <= LEFT(CHAR(INTEGER(DATE(SO_DATE) -7 MONTHS)),6) THEN (VALUE(T3.ADJUST_TOTAL,0)*1.00/100)*2 END KPI_ADJUST_FEE ,CASE WHEN t4.item_name like ''%上期%违约金%'' or t4.item_name like ''%本期%违约金%'' THEN 100 WHEN T3.SRC_BILL_MONTH<''201401'' OR T4.LOGIN_NAME IN (''H0106'',''I9829'') THEN 100 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -3 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE)-1 MONTHS)),6) THEN 100 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -6 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE) -4 MONTHS)),6) THEN 50 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH <= LEFT(CHAR(INTEGER(DATE(SO_DATE) -7 MONTHS)),6) THEN 0 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -3 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE)-1 MONTHS)),6) THEN 100 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN LEFT(CHAR(INTEGER(DATE(SO_DATE) -6 MONTHS)),6) AND LEFT(CHAR(INTEGER(DATE(SO_DATE) -4 MONTHS)),6) THEN 150 WHEN VALUE(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH <= LEFT(CHAR(INTEGER(DATE(SO_DATE) -7 MONTHS)),6) THEN 200 END KPI_ADJUST_RATE ,VALUE(T3.ADJUST_ASSETFEE, 0)*1.00/100 AS ADJUST_ASSETFEE ,VALUE(T3.ADJUST_BILLFEE, 0)*1.00/100 AS ADJUST_BILLFEE ,VALUE(T3.ADJUST_EXTFEE, 0)*1.00/100 AS ADJUST_EXTFEE ,T3.OP_ID ,T3.ORG_ID ,T3.SO_DATE ,T3.BEGIN_DATE ,T3.END_DATE ,T3.ADJUST_TYPE ,T3.REASON_ONE ,T3.REASON_TWO ,T3.REASON_THREE ,T3.REASON_FOUR ,T3.REASON_FIVE ,T3.DEAL_REASON ,T3.REMARK AS REMARKS FROM {DWD}.DWD_PRTY_GRP_INFO_&TASK_ID T1 INNER JOIN {ODS}.ODS_CM_CUST_ACCT_REL_&TASK_ID T2 ON T1.GRP_CUST_ID=T2.REL_CUST_ID INNER JOIN {ODS}.ODS_CA_BILL_BUSI_REC_&TASK_ID T3 ON T2.ACCT_ID=T3.ACCT_ID LEFT JOIN (select op_id,login_name from {DIM}.DIM_PRTY_OPER_INFO where start_date<''&TASK_DATE'' and end_date>''&TASK_DATE'' )T4 ON T3.OP_ID=T4.OP_ID left join ( select item_code,item_name from {DIM}.DIM_ACC_ITEM_CODE WHERE start_date<=''&TASK_DATE'' and end_date>''&TASK_DATE'' ) t4 on t3.ITEM_CODE=t4.ITEM_CODE WHERE T2.STATE=''U''; yes insert into 【script[10] after】 {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID; INSERT INTO {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID (SO_NBR ,ITEM_CODE ,SPEC_ID ,BILL_NO ,ACCT_ID ,GROUP_ID ,BILL_MONTH ,PHONE_NO ,USER_ID ,ADJUST_REASON ,SRC_BILL_MONTH ,ADJUST_FEE ,KPI_ADJUST_FEE ,KPI_ADJUST_RATE ,ADJUST_ASSET_FEE ,ADJUST_BILLFEE ,ADJUST_EXTFEE ,OP_ID ,ORG_ID ,SO_DATE ,BEGIN_DATE ,END_DATE ,ADJUST_TYPE ,REASON_ONE ,REASON_TWO ,REASON_THREE ,REASON_FOUR ,REASON_FIVE ,DEAL_REASON ,REMARKS) SELECT T3.SO_NBR ,T3.ITEM_CODE ,T3.SPEC_ID ,T3.BILL_NO ,T3.ACCT_ID ,T1.GROUP_ID ,T3.BILL_MONTH ,T3.IDENTITY AS PHONE_NO ,T3.RESOURCE_ID AS USER_ID ,T3.ADJUST_REASON ,T3.SRC_BILL_MONTH ,nvl(T3.ADJUST_TOTAL, 0)*1.00/100 AS ADJUST_FEE ,CASE WHEN t4.item_name like ''%上期%违约金%'' or t4.item_name like ''%本期%违约金%'' THEN (nvl(T3.ADJUST_TOTAL,0)*1.00/100)*1.0 WHEN T3.SRC_BILL_MONTH<''201401'' OR T4.LOGIN_NAME IN (''H0106'',''I9829'') THEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -3))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -1))), 6) THEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -6))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -4))), 6) THEN (nvl(T3.ADJUST_TOTAL,0)*1.00/100)*0.5 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH <= substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -7))), 6) THEN 0 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -3))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -1))), 6) THEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -6))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -4))), 6) THEN (nvl(T3.ADJUST_TOTAL,0)*1.00/100)*1.5 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH <= substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -7))), 6) THEN (nvl(T3.ADJUST_TOTAL,0)*1.00/100)*2 END KPI_ADJUST_FEE ,CASE WHEN t4.item_name like ''%上期%违约金%'' or t4.item_name like ''%本期%违约金%'' THEN 100 WHEN T3.SRC_BILL_MONTH<''201401'' OR T4.LOGIN_NAME IN (''H0106'',''I9829'') THEN 100 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -3))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -1))), 6) THEN 100 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -6))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -4))), 6) THEN 50 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH <= substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -7))), 6) THEN 0 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -3))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -1))), 6) THEN 100 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -6))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -4))), 6) THEN 150 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH <= substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -7))), 6) THEN 200 END KPI_ADJUST_RATE ,nvl(T3.ADJUST_ASSETFEE, 0)*1.00/100 AS ADJUST_ASSETFEE ,nvl(T3.ADJUST_BILLFEE, 0)*1.00/100 AS ADJUST_BILLFEE ,nvl(T3.ADJUST_EXTFEE, 0)*1.00/100 AS ADJUST_EXTFEE ,T3.OP_ID ,T3.ORG_ID ,T3.SO_DATE ,T3.BEGIN_DATE ,T3.END_DATE ,T3.ADJUST_TYPE ,T3.REASON_ONE ,T3.REASON_TWO ,T3.REASON_THREE ,T3.REASON_FOUR ,T3.REASON_FIVE ,T3.DEAL_REASON ,T3.REMARK AS REMARKS FROM {DWD}.DWD_PRTY_GRP_INFO_&TASK_ID T1 INNER JOIN {ODS}.ODS_CM_CUST_ACCT_REL_&TASK_ID T2 ON T1.GRP_CUST_ID=T2.REL_CUST_ID INNER JOIN {ODS}.ODS_CA_BILL_BUSI_REC_&TASK_ID T3 ON T2.ACCT_ID=T3.ACCT_ID LEFT JOIN (select op_id,login_name from {DIM}.DIM_PRTY_OPER_INFO where start_date<''&TASK_DATE'' and end_date>''&TASK_DATE'' )T4 ON T3.OP_ID=T4.OP_ID left join ( select item_code,item_name from {DIM}.DIM_ACC_ITEM_CODE WHERE start_date<=''&TASK_DATE'' and end_date>''&TASK_DATE'' ) t4 on t3.ITEM_CODE=t4.ITEM_CODE WHERE T2.STATE=''U'';{sql_compress} 【script[11] before】 {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID 【script[11] after】 {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID 【script[12] before】 CREATE TABLE {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID LIKE {TEMPLATE}.DWD_ACC_GRP_ADJUST_YYYYMMDD;ITEM_CODE;{TbsTemp};{TbsIdx} yes create table 【script[12] after】 create table {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID as select * from {TEMPLATE}.DWD_ACC_GRP_ADJUST_YYYYMMDD where 1 <> 1;ITEM_CODE;{TbsTemp};{TbsIdx};{ddl_compress} 【script[13] before】 {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID; INSERT INTO {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID ( ITEM_CODE ,ADJUST_TAX_RATE ,KPI_ADJUST_TAX_RATE ,ADJUST_ASSET_TAX_RATE ,ADJUST_BILL_TAX_RATE ,ADJUST_EXT_TAX_RATE ) SELECT DISTINCT a.ITEM_CODE ,T5.TAX_RATE AS ADJUST_TAX_RATE ,T5.TAX_RATE AS KPI_ADJUST_TAX_RATE ,T5.TAX_RATE AS ADJUST_ASSET_TAX_RATE ,T5.TAX_RATE AS ADJUST_BILL_TAX_RATE ,T5.TAX_RATE AS ADJUST_EXT_TAX_RATE FROM {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID A left join {ODS}.ODS_PM_ITEM_TAX_RATE_&TASK_ID T5 on a.ITEM_CODE=T5.ITEM_CODE WITH UR yes insert into 【script[13] after】 {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID; INSERT INTO {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID ( ITEM_CODE ,ADJUST_TAX_RATE ,KPI_ADJUST_TAX_RATE ,ADJUST_ASSET_TAX_RATE ,ADJUST_BILL_TAX_RATE ,ADJUST_EXT_TAX_RATE ) SELECT DISTINCT a.ITEM_CODE ,T5.TAX_RATE AS ADJUST_TAX_RATE ,T5.TAX_RATE AS KPI_ADJUST_TAX_RATE ,T5.TAX_RATE AS ADJUST_ASSET_TAX_RATE ,T5.TAX_RATE AS ADJUST_BILL_TAX_RATE ,T5.TAX_RATE AS ADJUST_EXT_TAX_RATE FROM {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID A left join {ODS}.ODS_PM_ITEM_TAX_RATE_&TASK_ID T5 on a.ITEM_CODE=T5.ITEM_CODE ;{sql_compress} 【script[14] before】 {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID 【script[14] after】 {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID 【script[15] before】 {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID 【script[15] after】 {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID
4、转换之后
<mxGraphModel> <root> <mxCell id="-1" /> <mxCell id="0" parent="-1" /> <mxCell fn="13" id="16" parent="0" script="{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID" value="删除目标表" vertex="1"> <mxGeometry as="geometry" height="40" width="100" x="570" y="120" /> </mxCell> <mxCell fn="14" id="2" parent="0" script="{DWD}.DWD_ACC_GRP_ADJUST_YYYYMMDD " value="创建目标表" vertex="1"> <mxGeometry as="geometry" height="40" width="100" x="580" y="250" /> </mxCell> <mxCell fn="2" id="3" parent="0" remark="向目标表插入数据，取集团客户对应的账户调账信息。" script="{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID;
INSERT INTO {DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID
 (SO_NBR
 ,ITEM_CODE
 ,SPEC_ID
 ,BILL_NO
 ,ACCT_ID
 ,GROUP_ID
 ,BILL_MONTH
 ,PHONE_NO
 ,USER_ID
 ,ADJUST_REASON
 ,SRC_BILL_MONTH
 ,ADJUST_FEE
 ,KPI_ADJUST_FEE
 ,KPI_ADJUST_RATE
 ,ADJUST_ASSET_FEE
 ,ADJUST_BILLFEE
 ,ADJUST_EXTFEE
 ,OP_ID
 ,ORG_ID
 ,SO_DATE
 ,BEGIN_DATE
 ,END_DATE
 ,ADJUST_TYPE
 ,REASON_ONE
 ,REASON_TWO
 ,REASON_THREE
 ,REASON_FOUR
 ,REASON_FIVE
 ,DEAL_REASON
 ,REMARKS
 
 ,EXC_ADJUST_FEE 
 ,ADJUST_TAX_FEE 
 ,ADJUST_TAX_RATE 
 ,KPI_EXC_ADJUST_FEE
 ,KPI_ADJUST_TAX_FEE
 ,KPI_ADJUST_TAX_RATE
 ,EXC_ADJUST_ASSET_FEE
 ,ADJUST_ASSET_TAX_FEE
 ,ADJUST_ASSET_TAX_RATE
 ,EXC_ADJUST_BILLFEE
 ,ADJUST_BILL_TAX_FEE
 ,ADJUST_BILL_TAX_RATE
 ,EXC_ADJUST_EXTFEE
 ,ADJUST_EXT_TAX_FEE
 ,ADJUST_EXT_TAX_RATE)
SELECT 
 A.SO_NBR
 ,A.ITEM_CODE
 ,A.SPEC_ID
 ,A.BILL_NO
 ,A.ACCT_ID
 ,A.GROUP_ID
 ,A.BILL_MONTH
 ,A.PHONE_NO
 ,A.USER_ID
 ,A.ADJUST_REASON
 ,A.SRC_BILL_MONTH
 ,A.ADJUST_FEE
 ,A.KPI_ADJUST_FEE
 ,A.KPI_ADJUST_RATE
 ,A.ADJUST_ASSET_FEE
 ,A.ADJUST_BILLFEE
 ,A.ADJUST_EXTFEE
 ,A.OP_ID
 ,A.ORG_ID
 ,A.SO_DATE
 ,A.BEGIN_DATE
 ,A.END_DATE
 ,A.ADJUST_TYPE
 ,A.REASON_ONE
 ,A.REASON_TWO
 ,A.REASON_THREE
 ,A.REASON_FOUR
 ,A.REASON_FIVE
 ,A.DEAL_REASON
 ,A.REMARKS
 
,A.ADJUST_FEE-(A.ADJUST_FEE*1.00*T5.ADJUST_TAX_RATE/10000) AS EXC_ADJUST_FEE
,A.ADJUST_FEE*1.00*T5.ADJUST_TAX_RATE/10000 AS ADJUST_TAX_FEE
,T5.ADJUST_TAX_RATE AS ADJUST_TAX_RATE
,A.KPI_ADJUST_FEE-(A.KPI_ADJUST_FEE*1.00*T5.KPI_ADJUST_TAX_RATE/10000) AS KPI_EXC_ADJUST_FEE
,A.KPI_ADJUST_FEE*1.00*T5.KPI_ADJUST_TAX_RATE/10000 AS KPI_EXC_ADJUST_FEE
,T5.KPI_ADJUST_TAX_RATE AS KPI_ADJUST_TAX_RATE
,A.ADJUST_ASSET_FEE-(A.ADJUST_ASSET_FEE*1.00*T5.ADJUST_ASSET_TAX_RATE/10000) AS EXC_ADJUST_ASSET_FEE
,A.ADJUST_ASSET_FEE*1.00*T5.ADJUST_ASSET_TAX_RATE/10000 AS ADJUST_ASSET_TAX_FEE
,T5.ADJUST_ASSET_TAX_RATE AS ADJUST_ASSET_TAX_RATE
,A.ADJUST_BILLFEE-(A.ADJUST_BILLFEE*1.00*T5.ADJUST_BILL_TAX_RATE/10000) AS EXC_ADJUST_BILLFEE
,A.ADJUST_BILLFEE*1.00*T5.ADJUST_BILL_TAX_RATE/10000 AS ADJUST_BILL_TAX_FEE
,T5.ADJUST_BILL_TAX_RATE AS ADJUST_BILL_TAX_RATE
,A.ADJUST_EXTFEE-(A.ADJUST_EXTFEE*1.00*T5.ADJUST_EXT_TAX_RATE/10000) AS EXC_ADJUST_EXTFEE
,A.ADJUST_EXTFEE*1.00*T5.ADJUST_EXT_TAX_RATE/10000 AS ADJUST_EXT_TAX_FEE
,T5.ADJUST_EXT_TAX_RATE AS ADJUST_EXT_TAX_RATE
FROM {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID A
 LEFT JOIN {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID T5
 ON A.ITEM_CODE=T5.ITEM_CODE ;{sql_compress}" value="向目标表插入数据" vertex="1"> <mxGeometry as="geometry" height="40" width="100" x="580" y="340" /> </mxCell> <mxCell edge="1" id="5" parent="0" source="16" style="strokeColor=#000000" target="2" value="完成时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell edge="1" id="6" linkType="0" parent="0" source="2" style="strokeColor=#00FF00" target="3" value="成功时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell fn="23" id="8" parent="0" script="TARGET_TABLE:{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID


DATA_DATE:&TASK_ID

--SUCCESS--
" value="结束" vertex="1"> <mxGeometry as="geometry" height="40" width="110" x="790" y="390" /> </mxCell> <mxCell fn="23" id="1" parent="0" script="TARGET_TABLE:{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID


DATA_DATE:&TASK_ID

--BEGIN—
" value="开始" vertex="1"> <mxGeometry as="geometry" height="40" width="100" x="150" y="40" /> </mxCell> <mxCell fn="11" id="11" parent="0" script="{DWD}.DWD_ACC_GRP_ADJUST_&TASK_ID" value="RUNSTATS" vertex="1"> <mxGeometry as="geometry" height="40" width="110" x="790" y="50" /> </mxCell> <mxCell edge="1" id="12" linkType="0" parent="0" source="3" style="strokeColor=#00FF00" target="11" value="成功时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell fn="45" id="17" parent="0" script="{DWD}.DWD_ACC_GRP_ADJUST_YYYYMMDD" value="表赋权限" vertex="1"> <mxGeometry as="geometry" height="40" width="110" x="790" y="140" /> </mxCell> <mxCell edge="1" id="18" linkType="0" parent="0" source="11" style="strokeColor=#00FF00" target="17" value="成功时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell fn="13" id="27" parent="0" remark="删除一张表tmp01" script="{TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID" value="删除一张表tmp01" vertex="1"> <mxGeometry as="geometry" height="40" width="100" x="150" y="130" /> </mxCell> <mxCell fn="9" id="28" parent="0" remark="创建临时表TMP01" script="create table {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID as select * from {TEMPLATE}.DWD_ACC_GRP_ADJUST_YYYYMMDD where 1 <> 1;ITEM_CODE;{TbsTemp};{TbsIdx};{ddl_compress}" value="创建临时表TMP01" vertex="1"> <mxGeometry as="geometry" height="40" width="100" x="150" y="230" /> </mxCell> <mxCell fn="2" id="29" parent="0" remark="插入tmp01数据" script="{TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID;
INSERT INTO {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID
 (SO_NBR
 ,ITEM_CODE
 ,SPEC_ID
 ,BILL_NO
 ,ACCT_ID
 ,GROUP_ID
 ,BILL_MONTH
 ,PHONE_NO
 ,USER_ID
 ,ADJUST_REASON
 ,SRC_BILL_MONTH
 ,ADJUST_FEE
 ,KPI_ADJUST_FEE
 ,KPI_ADJUST_RATE
 ,ADJUST_ASSET_FEE
 ,ADJUST_BILLFEE
 ,ADJUST_EXTFEE
 ,OP_ID
 ,ORG_ID
 ,SO_DATE
 ,BEGIN_DATE
 ,END_DATE
 ,ADJUST_TYPE
 ,REASON_ONE
 ,REASON_TWO
 ,REASON_THREE
 ,REASON_FOUR
 ,REASON_FIVE
 ,DEAL_REASON
 ,REMARKS)
SELECT
 T3.SO_NBR
,T3.ITEM_CODE
,T3.SPEC_ID
,T3.BILL_NO
,T3.ACCT_ID
,T1.GROUP_ID
,T3.BILL_MONTH
,T3.IDENTITY AS PHONE_NO
,T3.RESOURCE_ID AS USER_ID
,T3.ADJUST_REASON
,T3.SRC_BILL_MONTH
,nvl(T3.ADJUST_TOTAL, 0)*1.00/100 AS ADJUST_FEE
,CASE
 WHEN t4.item_name like ''%上期%违约金%'' or t4.item_name like ''%本期%违约金%'' THEN (nvl(T3.ADJUST_TOTAL,0)*1.00/100)*1.0
 WHEN T3.SRC_BILL_MONTH<''201401'' OR T4.LOGIN_NAME IN (''H0106'',''I9829'') THEN nvl(T3.ADJUST_TOTAL,0)*1.00/100
 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -3))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -1))), 6) THEN nvl(T3.ADJUST_TOTAL,0)*1.00/100
 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -6))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -4))), 6) THEN (nvl(T3.ADJUST_TOTAL,0)*1.00/100)*0.5
 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH <= substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -7))), 6) THEN 0
 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -3))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -1))), 6) THEN nvl(T3.ADJUST_TOTAL,0)*1.00/100
 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -6))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -4))), 6) THEN (nvl(T3.ADJUST_TOTAL,0)*1.00/100)*1.5
 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH <= substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -7))), 6) THEN (nvl(T3.ADJUST_TOTAL,0)*1.00/100)*2
 END KPI_ADJUST_FEE
,CASE
 WHEN t4.item_name like ''%上期%违约金%'' or t4.item_name like ''%本期%违约金%'' THEN 100
 WHEN T3.SRC_BILL_MONTH<''201401'' OR T4.LOGIN_NAME IN (''H0106'',''I9829'') THEN 100
 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -3))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -1))), 6) THEN 100
 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -6))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -4))), 6) THEN 50
 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 > 0 AND T3.SRC_BILL_MONTH <= substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -7))), 6) THEN 0
 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -3))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -1))), 6) THEN 100
 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH BETWEEN substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -6))), 6) AND substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -4))), 6) THEN 150
 WHEN nvl(T3.ADJUST_TOTAL,0)*1.00/100 < 0 AND T3.SRC_BILL_MONTH <= substr( to_char( to_number(add_months(to_to_date(SO_DATE) , -7))), 6) THEN 200
 END KPI_ADJUST_RATE
,nvl(T3.ADJUST_ASSETFEE, 0)*1.00/100 AS ADJUST_ASSETFEE
,nvl(T3.ADJUST_BILLFEE, 0)*1.00/100 AS ADJUST_BILLFEE
,nvl(T3.ADJUST_EXTFEE, 0)*1.00/100 AS ADJUST_EXTFEE
,T3.OP_ID
,T3.ORG_ID
,T3.SO_DATE
,T3.BEGIN_DATE
,T3.END_DATE
,T3.ADJUST_TYPE
,T3.REASON_ONE
,T3.REASON_TWO
,T3.REASON_THREE
,T3.REASON_FOUR
,T3.REASON_FIVE
,T3.DEAL_REASON
,T3.REMARK AS REMARKS
FROM {DWD}.DWD_PRTY_GRP_INFO_&TASK_ID T1
INNER JOIN {ODS}.ODS_CM_CUST_ACCT_REL_&TASK_ID T2 ON T1.GRP_CUST_ID=T2.REL_CUST_ID
INNER JOIN {ODS}.ODS_CA_BILL_BUSI_REC_&TASK_ID T3 ON T2.ACCT_ID=T3.ACCT_ID
LEFT JOIN 
(select op_id,login_name from {DIM}.DIM_PRTY_OPER_INFO where start_date<''&TASK_DATE'' and end_date>''&TASK_DATE'' )T4 ON T3.OP_ID=T4.OP_ID
 left join (
 select item_code,item_name 
 from {DIM}.DIM_ACC_ITEM_CODE 
 WHERE start_date<=''&TASK_DATE'' and end_date>''&TASK_DATE''
 ) t4
 on t3.ITEM_CODE=t4.ITEM_CODE
WHERE T2.STATE=''U'';{sql_compress}" value="插入tmp01数据" vertex="1"> <mxGeometry as="geometry" height="40" width="100" x="150" y="340" /> </mxCell> <mxCell fn="13" id="31" parent="0" remark="删除一张表tmp01" script="{TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID" value="删除一张表tmp02" vertex="1"> <mxGeometry as="geometry" height="40" width="100" x="350" y="130" /> </mxCell> <mxCell fn="9" id="32" parent="0" remark="创建临时表TMP02" script="create table {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID as select * from {TEMPLATE}.DWD_ACC_GRP_ADJUST_YYYYMMDD where 1 <> 1;ITEM_CODE;{TbsTemp};{TbsIdx};{ddl_compress}" value="创建临时表TMP02" vertex="1"> <mxGeometry as="geometry" height="40" width="100" x="350" y="230" /> </mxCell> <mxCell fn="2" id="33" parent="0" remark="插入tmp02数据--得到税率" script="{TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID;
INSERT INTO {TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID
 (
 ITEM_CODE
 ,ADJUST_TAX_RATE 
 ,KPI_ADJUST_TAX_RATE
 ,ADJUST_ASSET_TAX_RATE
 ,ADJUST_BILL_TAX_RATE
 ,ADJUST_EXT_TAX_RATE
)
SELECT DISTINCT 
a.ITEM_CODE
,T5.TAX_RATE AS ADJUST_TAX_RATE
,T5.TAX_RATE AS KPI_ADJUST_TAX_RATE
,T5.TAX_RATE AS ADJUST_ASSET_TAX_RATE
,T5.TAX_RATE AS ADJUST_BILL_TAX_RATE
,T5.TAX_RATE AS ADJUST_EXT_TAX_RATE
FROM {TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID A
 left join {ODS}.ODS_PM_ITEM_TAX_RATE_&TASK_ID T5
 on a.ITEM_CODE=T5.ITEM_CODE ;{sql_compress}" value="插入tmp02数据" vertex="1"> <mxGeometry as="geometry" height="40" width="100" x="350" y="340" /> </mxCell> <mxCell edge="1" id="34" linkType="0" parent="0" source="1" style="strokeColor=#00FF00" target="27" value="成功时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell edge="1" id="35" parent="0" source="27" style="strokeColor=#000000" target="28" value="完成时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell edge="1" id="36" linkType="0" parent="0" source="28" style="strokeColor=#00FF00" target="29" value="成功时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell edge="1" id="40" linkType="0" parent="0" source="29" style="strokeColor=#00FF00" target="31" value="成功时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell edge="1" id="41" parent="0" source="31" style="strokeColor=#000000" target="32" value="完成时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell edge="1" id="42" linkType="0" parent="0" source="32" style="strokeColor=#00FF00" target="33" value="成功时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell edge="1" id="43" linkType="0" parent="0" source="33" style="strokeColor=#00FF00" target="16" value="成功时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell fn="13" id="44" parent="0" remark="删除一张表tmp01" script="{TEMP}.TMP01_DWD_ACC_GRP_ADJUST_&TASK_ID" value="删除一张表tmp01" vertex="1"> <mxGeometry as="geometry" height="40" width="100" x="790" y="220" /> </mxCell> <mxCell fn="13" id="45" parent="0" remark="删除一张表tmp01" script="{TEMP}.TMP02_DWD_ACC_GRP_ADJUST_&TASK_ID" value="删除一张表tmp02" vertex="1"> <mxGeometry as="geometry" height="40" width="100" x="790" y="290" /> </mxCell> <mxCell edge="1" id="46" linkType="0" parent="0" source="17" style="strokeColor=#00FF00" target="44" value="成功时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell edge="1" id="47" parent="0" source="44" style="strokeColor=#000000" target="45" value="完成时"> <mxGeometry as="geometry" relative="1" /> </mxCell> <mxCell edge="1" id="48" parent="0" source="45" style="strokeColor=#000000" target="8" value="完成时"> <mxGeometry as="geometry" relative="1" /> </mxCell> </root> </mxGraphModel>
|
博客地址： http://www.cnblogs.com/dwf07223，本文以学习、研究和分享为主，欢迎转载，转载请务必保留此出处。若本博文中有不妥或者错误处请不吝赐教。 |
