在实际项目开发过程中，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
