/* * Copyright 1999-2017 Alibaba Group Holding Ltd. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.alibaba.druid.bvt.sql.oracle.select; import java.util.List; import org.junit.Assert; import com.alibaba.druid.sql.OracleTest; import com.alibaba.druid.sql.SQLUtils; import com.alibaba.druid.sql.ast.SQLStatement; import com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser; import com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor; import com.alibaba.druid.stat.TableStat; public class OracleSelectTest38 extends OracleTest { public void test_0() throws Exception { String sql = // "select * from " + "(with vw_kreis_statics_t as" + " (select substr(xzqh,1,6) xzqh,swrslx,sum(swrs_count) acd_totle from" + " (select xzqh,sglx,case when (swrs7 < 3) then '1'" + " when (swrs7 < 5) then '2' when (swrs7 <= 9) then '3' else '4' end swrslx,1 swrs_count" + " from acduser.vw_acd_info where sglx='1' " + " " + " and sgfssj >= ?" + " " + " " + " )" + " group by substr(xzqh,1,6),swrslx)" + "" + " select e.\"XZQH\",e.\"LESS3\",e.\"F3TO5\",e.\"F5TO9\",e.\"MORE9\",kreis_code, kreis_name,px1,py1,px2,py2 from" + " ( select" + " xzqh," + " nvl(max(decode(swrslx,'1',acd_totle)),0) less3," + " nvl(max(decode(swrslx,'2',acd_totle)),0) f3to5," + " nvl(max(decode(swrslx,'3',acd_totle)),0) f5to9," + " nvl(max(decode(swrslx,'4',acd_totle)),0) more9" + " from( select * from acduser.vw_kreis_statics_t) group by xzqh " + " ) e" + "" + " left join" + " acduser.vw_sc_kreis_code_lv2 f on e.xzqh = f.short_kreis_code) " + " where kreis_code in" + "(select * from " + " (select tbek_code from acduser.vw_kreis_code start with tbek_code = ? connect by prior tbek_pk=tbek_parent ) " + "where tbek_code != ?)"; // OracleStatementParser parser = new OracleStatementParser(sql); List<SQLStatement> statementList = parser.parseStatementList(); SQLStatement stmt = statementList.get(0); { String result = SQLUtils.toOracleString(stmt); Assert.assertEquals("SELECT *" + "\nFROM (" + "\n\tWITH" + "\n\t\tvw_kreis_statics_t" + "\n\t\tAS" + "\n\t\t(" + "\n\t\t\tSELECT substr(xzqh, 1, 6) AS xzqh, swrslx, SUM(swrs_count) AS acd_totle" + "\n\t\t\tFROM (" + "\n\t\t\t\tSELECT xzqh, sglx, CASE WHEN swrs7 < 3 THEN '1' WHEN swrs7 < 5 THEN '2' WHEN swrs7 <= 9 THEN '3' ELSE '4' END AS swrslx, 1 AS swrs_count" + "\n\t\t\t\tFROM acduser.vw_acd_info" + "\n\t\t\t\tWHERE sglx = '1'" + "\n\t\t\t\t\tAND sgfssj >= ?" + "\n\t\t\t)" + "\n\t\t\tGROUP BY substr(xzqh, 1, 6), swrslx" + "\n\t\t)" + "\n\tSELECT e.\"XZQH\", e.\"LESS3\", e.\"F3TO5\", e.\"F5TO9\", e.\"MORE9\"" + "\n\t\t, kreis_code, kreis_name, px1, py1, px2" + "\n\t\t, py2" + "\n\tFROM (" + "\n\t\tSELECT xzqh, nvl(MAX(decode(swrslx, '1', acd_totle)), 0) AS less3, nvl(MAX(decode(swrslx, '2', acd_totle)), 0) AS f3to5, nvl(MAX(decode(swrslx, '3', acd_totle)), 0) AS f5to9, nvl(MAX(decode(swrslx, '4', acd_totle)), 0) AS more9" + "\n\t\tFROM (" + "\n\t\t\tSELECT *" + "\n\t\t\tFROM acduser.vw_kreis_statics_t" + "\n\t\t)" + "\n\t\tGROUP BY xzqh" + "\n\t) e" + "\n\t\tLEFT JOIN acduser.vw_sc_kreis_code_lv2 f ON e.xzqh = f.short_kreis_code " + "\n)" + "\nWHERE kreis_code IN (SELECT *" + "\n\tFROM (" + "\n\t\tSELECT tbek_code" + "\n\t\tFROM acduser.vw_kreis_code" + "\n\t\tSTART WITH tbek_code = ?" + "\n\t\tCONNECT BY PRIOR tbek_pk = tbek_parent" + "\n\t)" + "\n\tWHERE tbek_code != ?)", result); } { String result = SQLUtils.toOracleString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION); Assert.assertEquals("select *" + "\nfrom (" + "\n\twith" + "\n\t\tvw_kreis_statics_t" + "\n\t\tas" + "\n\t\t(" + "\n\t\t\tselect substr(xzqh, 1, 6) as xzqh, swrslx, sum(swrs_count) as acd_totle" + "\n\t\t\tfrom (" + "\n\t\t\t\tselect xzqh, sglx, case when swrs7 < 3 then '1' when swrs7 < 5 then '2' when swrs7 <= 9 then '3' else '4' end as swrslx, 1 as swrs_count" + "\n\t\t\t\tfrom acduser.vw_acd_info" + "\n\t\t\t\twhere sglx = '1'" + "\n\t\t\t\t\tand sgfssj >= ?" + "\n\t\t\t)" + "\n\t\t\tgroup by substr(xzqh, 1, 6), swrslx" + "\n\t\t)" + "\n\tselect e.\"XZQH\", e.\"LESS3\", e.\"F3TO5\", e.\"F5TO9\", e.\"MORE9\"" + "\n\t\t, kreis_code, kreis_name, px1, py1, px2" + "\n\t\t, py2" + "\n\tfrom (" + "\n\t\tselect xzqh, nvl(max(decode(swrslx, '1', acd_totle)), 0) as less3, nvl(max(decode(swrslx, '2', acd_totle)), 0) as f3to5, nvl(max(decode(swrslx, '3', acd_totle)), 0) as f5to9, nvl(max(decode(swrslx, '4', acd_totle)), 0) as more9" + "\n\t\tfrom (" + "\n\t\t\tselect *" + "\n\t\t\tfrom acduser.vw_kreis_statics_t" + "\n\t\t)" + "\n\t\tgroup by xzqh" + "\n\t) e" + "\n\t\tleft join acduser.vw_sc_kreis_code_lv2 f on e.xzqh = f.short_kreis_code " + "\n)" + "\nwhere kreis_code in (select *" + "\n\tfrom (" + "\n\t\tselect tbek_code" + "\n\t\tfrom acduser.vw_kreis_code" + "\n\t\tstart with tbek_code = ?" + "\n\t\tconnect by prior tbek_pk = tbek_parent" + "\n\t)" + "\n\twhere tbek_code != ?)", result); } Assert.assertEquals(1, statementList.size()); OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor(); stmt.accept(visitor); System.out.println("Tables : " + visitor.getTables()); System.out.println("fields : " + visitor.getColumns()); System.out.println("coditions : " + visitor.getConditions()); System.out.println("relationships : " + visitor.getRelationships()); System.out.println("orderBy : " + visitor.getOrderByColumns()); Assert.assertEquals(4, visitor.getTables().size()); Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("acduser.vw_acd_info"))); Assert.assertEquals(12, visitor.getColumns().size()); Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("acduser.vw_acd_info", "xzqh"))); Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("acduser.vw_acd_info", "sglx"))); // Assert.assertTrue(visitor.getOrderByColumns().contains(new TableStat.Column("employees", "last_name"))); } }