java可以反复执行_java之生成可重复执行的sql脚本

packagecom.ebao.th.gs.integration.util;importjava.io.File;importjava.io.FileWriter;importjava.text.SimpleDateFormat;importjava.util.Date;public classGeneraterPKGUtils {public static final String FILE_SPLIT="_";public static voidmain(String[] args) {//调用函数//生成文件}public staticString generaterModifyColType(String column,String tableName,String type){

StringBuffer pkg= newStringBuffer();

pkg.append("declare\r\n");

pkg.append(" v_column varchar2(100):='"+column+"';\r\n");

pkg.append(" v_table varchar2(100):='"+tableName+"';\r\n");

pkg.append(" v_sql varchar2(2000):='ALTER TABLE "+tableName+" MODIFY "+column+type+"';\r\n");

pkg.append(" v_cnt number:=0;\r\n");

pkg.append("begin\r\n");

pkg.append(" select count(1) into v_cnt from user_tab_columns t where t.TABLE_NAME=v_table and t.COLUMN_NAME=v_column;\r\n");

pkg.append(" if v_cnt>0 then\r\n");

pkg.append(" execute immediate v_sql;\r\n");

pkg.append(" end if;\r\n");

pkg.append("end;\r\n");

pkg.append("/\r\n");returnpkg.toString().toUpperCase();

}public staticString generaterRenameColumn(String column,String tableName){

StringBuffer pkg= newStringBuffer();

pkg.append("declare\r\n");

pkg.append(" v_column varchar2(100):='"+column+"';\r\n");

pkg.append(" v_table varchar2(100):='"+tableName+"';\r\n");

pkg.append(" v_sql varchar2(2000):='alter table "+tableName.toUpperCase()+" rename column "+column+" to "+column.replaceAll("Insured", "Holder").toUpperCase()+"';\r\n");

pkg.append(" v_cnt number:=0;\r\n");

pkg.append("begin\r\n");

pkg.append(" select count(1) into v_cnt from user_tab_columns t where t.TABLE_NAME=v_table and t.COLUMN_NAME=v_column ;\r\n");

pkg.append(" if v_cnt>0 then\r\n");

pkg.append(" execute immediate v_sql;\r\n");

pkg.append(" end if;\r\n");

pkg.append("end;\r\n");

pkg.append("/\r\n");returnpkg.toString().toUpperCase();

}public staticString generateCreateTable(String tableStr,String tableName){

StringBuffer pkg= newStringBuffer();

pkg.append("declare\r\n");

pkg.append(" v_table varchar2(100):='"+tableName+"';\r\n");

pkg.append(" v_sql varchar2(2000):='"+tableStr.trim()+"';\r\n");

pkg.append(" v_cnt number:=0;\r\n");

pkg.append("begin\r\n");

pkg.append(" select count(1) into v_cnt from user_tables t where t.TABLE_NAME=v_table ;\r\n");

pkg.append(" if v_cnt=0 then\r\n");

pkg.append(" execute immediate v_sql;\r\n");

pkg.append(" end if;\r\n");

pkg.append("end;\r\n");

pkg.append("/\r\n");returnpkg.toString().toUpperCase();

}public staticString renameColumn(String column,String tableName,String type){

StringBuffer pkg= newStringBuffer();

pkg.append("declare\r\n");

pkg.append(" v_column varchar2(100):='"+column.trim()+"';\r\n");

pkg.append(" v_table varchar2(100):='"+tableName.trim()+"';\r\n");

pkg.append(" v_sql varchar2(2000):='ALTER TABLE "+tableName+" rename column "+column+" to "+type+"';\r\n");

pkg.append(" v_cnt number:=0;\r\n");

pkg.append("begin\r\n");

pkg.append(" select count(1) into v_cnt from user_tab_columns t where t.TABLE_NAME=v_table and t.COLUMN_NAME=v_column ;\r\n");

pkg.append(" if v_cnt>0 then\r\n");

pkg.append(" execute immediate v_sql;\r\n");

pkg.append(" end if;\r\n");

pkg.append("end;\r\n");

pkg.append("/\r\n");returnpkg.toString().toUpperCase();

}public staticString generatePrimaryKey(String column,String tableName,String type,String constraintName){

StringBuffer pkg= newStringBuffer();

pkg.append("declare\r\n");

pkg.append(" v_column varchar2(100):='"+column.toUpperCase()+"';\r\n");

pkg.append(" v_table varchar2(100):='"+tableName.toUpperCase()+"';\r\n");

pkg.append(" v_sql varchar2(2000):='alter table "+tableName.toUpperCase()+" add constraint "+constraintName.toUpperCase()+" primary key ("+column.toUpperCase()+") ';\r\n");

pkg.append(" v_cnt number:=0;\r\n");

pkg.append("begin\r\n");

pkg.append(" select count(1) into v_cnt from user_constraints t where t.CONSTRAINT_NAME='"+constraintName.toUpperCase()+"' and constraint_type='"+type.toUpperCase()+"' and table_name=v_table ;\r\n");

pkg.append(" if v_cnt=0 then\r\n");

pkg.append(" execute immediate v_sql;\r\n");

pkg.append(" end if;\r\n");

pkg.append("end;\r\n");

pkg.append("/\r\n");returnpkg.toString().toUpperCase();

}public staticString createIndex(String column,String tableName,String type,String indexName){

StringBuffer pkg= newStringBuffer();

pkg.append("declare\r\n");

pkg.append(" v_column varchar2(100):='"+column.toUpperCase()+"';\r\n");

pkg.append(" v_table varchar2(100):='"+tableName.toUpperCase()+"';\r\n");

pkg.append(" v_sql varchar2(2000):='create index "+indexName.toUpperCase()+" on "+tableName.toUpperCase()+"("+column.toUpperCase()+")';\r\n");

pkg.append(" v_cnt number:=0;\r\n");

pkg.append("begin\r\n");

pkg.append(" select count(1) into v_cnt from user_indexes t where t.INDEX_NAME='"+indexName.toUpperCase()+"' and index_type='"+type.toUpperCase()+"' and table_name=v_table ;\r\n");

pkg.append(" if v_cnt=0 then\r\n");

pkg.append(" execute immediate v_sql;\r\n");

pkg.append(" end if;\r\n");

pkg.append("end;\r\n");

pkg.append("/\r\n");returnpkg.toString().toUpperCase();

}public staticString dropColumn(String column,String tableName){

StringBuffer pkg= newStringBuffer();

pkg.append("declare\r\n");

pkg.append(" v_column varchar2(100):='"+column+"';\r\n");

pkg.append(" v_table varchar2(100):='"+tableName+"';\r\n");

pkg.append(" v_sql varchar2(2000):='alter table "+tableName+" drop column "+column+"';\r\n");

pkg.append(" v_cnt number:=0;\r\n");

pkg.append("begin\r\n");

pkg.append(" select count(1) into v_cnt from user_tab_columns t where t.TABLE_NAME=v_table and t.COLUMN_NAME=v_column ;\r\n");

pkg.append(" if v_cnt>0 then\r\n");

pkg.append(" execute immediate v_sql;\r\n");

pkg.append(" end if;\r\n");

pkg.append("end;\r\n");

pkg.append("/\r\n");returnpkg.toString().toUpperCase();

}public staticString addComment(String column,String tableName,String comments){

StringBuffer pkg= newStringBuffer();

pkg.append("declare\r\n");

pkg.append(" v_column varchar2(100):='"+column+"';\r\n");

pkg.append(" v_table varchar2(100):='"+tableName.toUpperCase()+"';\r\n");

pkg.append(" v_sql varchar2(2000):='COMMENT ON COLUMN "+tableName+"."+column+" IS ''"+comments+"'''\r\n;");

pkg.append(" v_cnt number:=0;\r\n");

pkg.append("begin\r\n");

pkg.append(" select count(1) into v_cnt from user_tab_columns t where t.TABLE_NAME=v_table and t.COLUMN_NAME=v_column ;\r\n");

pkg.append(" if v_cnt>0 then\r\n");

pkg.append(" execute immediate v_sql;\r\n");

pkg.append(" end if;\r\n");

pkg.append("end;\r\n");

pkg.append("/\r\n");returnpkg.toString().toUpperCase();

}public staticString addCommentOnTable(String tableName,String comments){

StringBuffer pkg= newStringBuffer();

pkg.append("declare\r\n");

pkg.append(" v_table varchar2(100):='"+tableName.toUpperCase()+"';\r\n");

pkg.append(" v_sql varchar2(2000):='comment on table "+tableName.toUpperCase()+" IS ''"+comments+"'''");

pkg.append(" v_cnt number:=0;\r\n");

pkg.append("begin\r\n");

pkg.append(" select count(1) into v_cnt from user_tab_columns t where t.TABLE_NAME=v_table ;\r\n");

pkg.append(" if v_cnt>0 then\r\n");

pkg.append(" execute immediate v_sql;\r\n");

pkg.append(" end if;\r\n");

pkg.append("end;\r\n");

pkg.append("/\r\n");returnpkg.toString().toUpperCase();

}public static void generateFile(String sql,String name) throwsException{

String path="D:"+File.separator+"dbscript"+File.separator;

File f= newFile(path);if(!f.exists()){

f.mkdirs();

}

FileWriter writer=null;

String dateStr="";

SimpleDateFormat sim= new SimpleDateFormat("yyyyMMddHHmmss");

dateStr= sim.format(newDate());

String fileName= path+dateStr+FILE_SPLIT+name.trim().toUpperCase()+"_ddl.sql";try{

writer= newFileWriter(fileName);

writer.write(sql);

writer.flush();

Runtime.getRuntime().exec("notepad.exe "+fileName);

}catch(Exception e) {//e.printStackTrace();

}finally{

writer.flush();if(writer!=null){

writer.close();

}

}

}

}