Examples with SQLMergeStatement com.alibaba.druid.sql.ast.statement.SQLMergeStatement used on opensource projects

Example 1 with SQLMergeStatement

use of com.alibaba.druid.sql.ast.statement.SQLMergeStatement in project druid by alibaba.

the class OracleMergeTest7 method test_0.

public void test_0() throws Exception {
    String sql = //
    "MERGE INTO copy_emp c " + //
    "USING employees e " + //
    "ON (c.employee_id=e.employee_id) " + //
    "WHEN MATCHED THEN " + //
    "UPDATE SET " + //
    "c.first_name=e.first_name, " + //
    "c.last_name=e.last_name, " + //
    "c.department_id=e.department_id " + //
    "WHEN NOT MATCHED THEN " + //
    "INSERT VALUES(e.employee_id,e.first_name,e.last_name," + //
    "e.email,e.phone_number,e.hire_date,e.job_id, " + "e.salary,e.commission_pct,e.manager_id,e.department_id)";
    SQLStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> stmtList = parser.parseStatementList();
    SQLMergeStatement mergeStatement = (SQLMergeStatement) stmtList.get(0);
    String result = SQLUtils.toOracleString(mergeStatement);
    Assert.assertEquals(//
    "MERGE INTO copy_emp c" + //
    "\nUSING employees e ON (c.employee_id = e.employee_id) " + //
    "\nWHEN MATCHED THEN UPDATE SET c.first_name = e.first_name, c.last_name = e.last_name, c.department_id = e.department_id" + "\nWHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id)", result);
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "employee_id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "salary")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "department_id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("bonuses", "employee_id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("bonuses", "bonus")));
}

Example 2 with SQLMergeStatement

use of com.alibaba.druid.sql.ast.statement.SQLMergeStatement in project druid by alibaba.

the class OracleMergeTest8 method test_0.

public void test_0() throws Exception {
    String sql = //
    "merge into (select * from T09_RULE_CAL_COUNT where data_dt = '20160328' and rule_type = '2') t " + "using (" + //
    "     select cust_no,organ_key " + "     from (select t1.cust_no, t1.organ_key from t08_cust_result_c_mid t1 " + "         union " + "         (select t2.cust_no, t2.organ_key from t08_cust_result_i_mid t2)" + "     )" + ") t3 on(t3.cust_no =t.cust_no) when matched then update set t.organ_key=t3.organ_key";
    SQLStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> stmtList = parser.parseStatementList();
    SQLMergeStatement mergeStatement = (SQLMergeStatement) stmtList.get(0);
    String result = SQLUtils.toOracleString(mergeStatement);
    Assert.assertEquals("MERGE INTO (SELECT *" + "\n\tFROM T09_RULE_CAL_COUNT" + "\n\tWHERE data_dt = '20160328'" + "\n\t\tAND rule_type = '2'" + "\n\t) t" + "\nUSING (" + "\n\tSELECT cust_no, organ_key" + "\n\tFROM (" + "\n\t\tSELECT t1.cust_no, t1.organ_key" + "\n\t\tFROM t08_cust_result_c_mid t1" + "\n\t\tUNION" + "\n\t\tSELECT t2.cust_no, t2.organ_key" + "\n\t\tFROM t08_cust_result_i_mid t2" + "\n\t)" + "\n) t3 ON (t3.cust_no = t.cust_no) " + "\nWHEN MATCHED THEN UPDATE SET t.organ_key = t3.organ_key", result);
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "employee_id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "salary")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "department_id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("bonuses", "employee_id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("bonuses", "bonus")));
}