在实际项目开发过程中，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(); } } }}
更多内容，请访问：