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

　　在实际项目开发过程中，sql脚本需要多次执行。而一般的DML和DDL语句一般只能执行一次，再次执行执行时就会报错（操作对应已存在/不存在），所以必须将sql脚本生成可重复执行的。本文共分为4部分：1.什么是DDL和DML；2.DDL可重复执行脚本；3.DML可重复执行脚本。

1.什么是DDL和DML

　　DDL: Data Defination Language,即数据定义语言。主要是是对表进行操作（DROP, CREATE,ALTER...）

　　DML: Data Management Language,即数据控制语句。主要是对记录进行操作（INSERT,DELETE,UPDATE....）

2.DDL可重复执行脚本

　　2.1 通用格式：

-- 定义便变量并赋值
DECLARE
    varName SqlType := value;
     V_SQL VARCHAR2(1000) := SqlStatement;
-- 执行逻辑
BEGIN
    IF IfCondition THEN
       EXECUTE IMMEDIATE V_SQL;
    END IF;
END;
-- ‘/’可看作结束符
/

　　2.2 创建一张新的表单以及序列

DECLARE
    V_TABLE VARCHAR2(100):='T_DEFINE';
    V_CREATE_SQL VARCHAR2(2000):='CREATE TABLE T_GRI_TREATY_DEFINE(
                                  ID NUMBER(10) PRIMARY KEY,
                                  TREATY_ID NUMBER(10) not null,
                                  RISK_CAT NUMBER(10) not null,
                                  ANNUAL_AGG_LIMIT_FLAG NUMBER(1) DEFAULT 2,
                                  ANNUAL_AGG_LIMIT_AMOUNT NUMBER(24,4),
                                  INSERT_BY NUMBER(10),
                                  INSERT_TIME DATE,
                                  UPDATE_BY NUMBER(10),
                                  UPDATE_TIME DATE
                            )';
    V_SEQUENCE_SQL VARCHAR2(2000):='CREATE SEQUENCE S_GRI_TREATY_DEFINE_ID';
    V_CNT NUMBER:=0;
BEGIN
　　-- 查询表是否存在（0：不存在，1：存在）    SELECT COUNT(1) INTO V_CNT FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME=V_TABLE;
    IF V_CNT=0 THEN
       EXECUTE IMMEDIATE V_CREATE_SQL;
       EXECUTE IMMEDIATE V_SEQUENCE_SQL;
    END IF;
END;
/

　　2.2 给表中的列添加注释

DECLARE
    V_COLUMN VARCHAR2(100):='ID';
    V_TABLE VARCHAR2(100):='T_DEFINE';
    -- 注释的内容必须用两个单引号括起来    V_SQL VARCHAR2(2000):='COMMENT ON COLUMN T_DEFINE.ID IS ''PK S_DEFINE_ID''';
    V_CNT NUMBER:=0;
BEGIN
　　-- 查询列是否存在    SELECT COUNT(1) INTO V_CNT FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME=V_TABLE AND T.COLUMN_NAME=V_COLUMN ;
    IF V_CNT>0 THEN
       EXECUTE IMMEDIATE V_SQL;
    END IF;
END;
/

　　2.3 给表添加一列（alter其他语句例子基本相同，只需要修改V_SQL的值和IF的条件）

DECLARE
    V_COLUMN VARCHAR2(100):='LIMIT_AMOUNT';
    V_TABLE VARCHAR2(100):='T_TREATY';
    V_SQL VARCHAR2(2000):='ALTER TABLE T_TREATY ADD LIMIT_AMOUNT NUMBER(24,4) ';
    V_CNT NUMBER:=0;
BEGIN
    SELECT COUNT(1) INTO V_CNT FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME=V_TABLE AND T.COLUMN_NAME=V_COLUMN ;
    IF V_CNT=0 THEN
       EXECUTE IMMEDIATE V_SQL;
    END IF;
END;
/

3. DML可重复执行脚本

　　3.1 update语句本身就可以多次执行，因此不用进行特殊处理

　　3.2 insert语句只需要在insert之前追加delete语句即可。

delete from t_vehicle_info where VEHICLE_ID = 1763154645;

insert into t_th_int_redbook_info (VEHICLE_ID,MAKE_NAME, FAMILY_NAME, DESCRIPTION, UPDATE_TIME)
values (1763154645, 'BMW', '430i', 'BMW 430i Coupe M Sport', '11-JUL-17');

4. 用Java生成DDL可重复执行脚本

package com.ebao.th.gs.integration.util;

import java.io.File;
import java.io.FileWriter;
import java.text.SimpleDateFormat;
import java.util.Date;

public class GeneraterPKGUtils {

  public static final String FILE_SPLIT="_";
    
  public static void main(String[] args) {
       
    //调用函数
       
    //生成文件
    
  }
  
  public static String generaterModifyColType(String column,String tableName,String type){
    StringBuffer pkg = new StringBuffer();
    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");
    return pkg.toString().toUpperCase();
  }
  
  
  public static String generaterRenameColumn(String column,String tableName){
    StringBuffer pkg = new StringBuffer();
    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");
    return pkg.toString().toUpperCase();
  }
  
  public static String generateCreateTable(String tableStr,String tableName){
    StringBuffer pkg = new StringBuffer();
    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");
    return pkg.toString().toUpperCase();
  }
  
  public static String renameColumn(String column,String tableName,String type){
    StringBuffer pkg = new StringBuffer();
    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");
    return pkg.toString().toUpperCase();
  }
  
   public static String generatePrimaryKey(String column,String tableName,String type,String constraintName){
     StringBuffer pkg = new StringBuffer();
     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");
     return pkg.toString().toUpperCase();
   }
  
  public static String createIndex(String column,String tableName,String type,String indexName){
    StringBuffer pkg = new StringBuffer();
    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");
    return pkg.toString().toUpperCase();
  }
  

  public static String dropColumn(String column,String tableName){
    StringBuffer pkg = new StringBuffer();
    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");
    return pkg.toString().toUpperCase();
  }
  
  public static String addComment(String column,String tableName,String comments){
    StringBuffer pkg = new StringBuffer();
    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");
    return pkg.toString().toUpperCase();
  } 
  
  public static String addCommentOnTable(String tableName,String comments){
    StringBuffer pkg = new StringBuffer();
    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");
    return pkg.toString().toUpperCase();
  } 
  
  public static void generateFile(String sql,String name) throws Exception{
    String path="D:"+File.separator+"dbscript"+File.separator;
    File f = new File(path);
    if(!f.exists()){
      f.mkdirs();
    }
    FileWriter writer=null;
    String dateStr ="";
    SimpleDateFormat sim = new SimpleDateFormat("yyyyMMddHHmmss");
    dateStr = sim.format(new Date());
    String fileName = path+dateStr+FILE_SPLIT+name.trim().toUpperCase()+"_ddl.sql";
    try {
      writer = new FileWriter(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();
      }
    }
    
  }
}

　　更多内容，请访问：http://www.cnblogs.com/BlueStarWei

更多相关文章

随机推荐