`
唐辉999
  • 浏览: 969 次
最近访客 更多访客>>
社区版块
存档分类
最新评论

使用DBLINK比较2个数据库之间的表结构差异并生成DML语句

阅读更多

String deleteSql = " DROP  database link linkfwq ";
commConstantDao.executeSQLUpdate(deleteSql);

String linkfwqSql = " create database link linkfwq connect to test identified by test using '(DESCRIPTION =  (ADDRESS_LIST =  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )'";

commConstantDao.executeSQLUpdate(linkfwqSql);



// 少表字段的表

String sql = " select table_name,  column_name  , DATA_TYPE,DATA_LENGTH,DATA_PRECISION , DATA_SCALE ,NULLABLE,DATA_DEFAULT ,COLUMN_ID  from user_tab_columns  ,";

sql+= " (select table_name a,  column_name  b  from user_tab_columns  where table_name not in (SELECT TABLE_NAME FROM USER_TABLES minus SELECT TABLE_NAME FROM USER_TABLES@linkfwq) ";
sql+= " minus "; 
sql+=  "select table_name ,column_name  from user_tab_columns@linkfwq where table_name not in (SELECT TABLE_NAME FROM USER_TABLES minus SELECT TABLE_NAME FROM USER_TABLES@linkfwq)) t " +
" where t.a = table_name and t.b = column_name order by TABLE_NAME , COLUMN_ID ";

List<Object[]> list =  commConstantDao.executeSQLQuery(sql);
StringBuffer addColumnBuf = new StringBuffer(""); // 增加字段的SQL集合
String curSql = "";
for (Object[] obj : list) {
String TABLE_NAME = (String) obj[0];
String COLUMN_NAME = (String) obj[1];
String DATA_TYPE = (String) obj[2];// 数据类型
BigDecimal DATA_LENGTH_BIG = (BigDecimal) obj[3];
Long DATA_LENGTH = DATA_LENGTH_BIG.longValue();

Long DATA_PRECISION = null;
if(obj[4]!= null){
BigDecimal DATA_PRECISION_BIG = (BigDecimal) obj[4];
    DATA_PRECISION = DATA_PRECISION_BIG.longValue();
}
Long DATA_SCALE = null;
if( obj[5]!=null){
BigDecimal DATA_SCALE_BIG = (BigDecimal) obj[5];
DATA_SCALE = DATA_SCALE_BIG.longValue();
}

String NULLABLE = (String) obj[6]; // 是否为NULL
String DATA_DEFAULT = (String) obj[7]; // 默认值

// ALTER TABLE APS_PLAN_SCHEDULE_TIME  ADD (ARRIVE_PROCESS_TIME  NUMBER   DEFAULT 1  NOT NULL);
curSql = "ALTER TABLE " + TABLE_NAME + " ADD ( "+ COLUMN_NAME ;
if(DATA_TYPE.equals("NUMBER")){
// int
if(DATA_PRECISION == null){
curSql += " INTEGER ";
}else{// double, float 等
curSql += " NUMBER( " + DATA_PRECISION +" ,"+DATA_SCALE +" )";
}
}else if(DATA_TYPE.equals("VARCHAR2")){// VARCHAR2(15 BYTE)
curSql += " VARCHAR2 ( "+ DATA_LENGTH +")" ;
}else if(DATA_TYPE.equals("DATE")){
curSql += " DATE " ;
}else if(DATA_TYPE.equals("FLOAT")){
curSql += " FLOAT " ;
}

// 默认值 和 是否为空
if(!StringUtil.isNullOrEmpty(DATA_DEFAULT)){
curSql += " DEFAULT "+ DATA_DEFAULT;
}
if(NULLABLE.equals("N")){
curSql += " NOT NULL ";
}
curSql += " );";

addColumnBuf.append(curSql);
addColumnBuf.append("\n");
curSql = "";
}

 

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics