情景:目前有100多个表,每个表都要创建两个触发器并测试。一个个写估计要写一个星期,但是我用编程半天搞定。
第一步 收集表信息
收集所有表的表名,字段名
--查询所有表名:
select t.table_name from user_tables t;
--查询指定表的所有字段名:
select t.column_name from user_col_comments t where t.table_name = 'T_XXX';
格式如:表名,主键名,字段名1,字段名2,字段名3,字段名4,字段名5,字段名6,字段名7,字段名8,...
用逗号分隔
保存在名为 "表信息.txt" 的文件下。
第二步 写Java程序,生成创建触发器sql
package util;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;
public class 生成触发器代码 {
public static void main(String[] args) {
//读取的文件路径
String filePath = "C:\\表信息.txt";
try {
//输出的文件路径
FileWriter fw = new FileWriter("C:\\创建触发器.sql", true);
BufferedWriter bw = new BufferedWriter(fw);
try (BufferedReader bufReader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF-8")))//数据流读取文件
{
String temp = null;
while ((temp = bufReader.readLine()) != null) {
String[] daima = temp.split(",");
if(daima.length>1){
System.out.print(1);
bw.append(generate(daima));// 往已有的文件上添加字符串
}
}
bufReader.close();
bw.close();
fw.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 生成创建一个表的触发器的sql
* @param daima 表名,字段名
* @return
*/
public static String generate(String[] daima){
StringBuilder sb = new StringBuilder();
sb.append("create or replace trigger tri_"+daima[0]+"_add\n"
+ " after insert on " + daima[0] + "\n"
+ " for each row\n"
+ "begin\n"
+ " insert into " + daima[0] + "@wer\n"
+ "(" + daima[1]);
for(int i = 2 ; i < daima.length ; i++){
if(!"QZKRKSJ".equals(daima[i])&& !"QZKXGSJ".equals(daima[i]) && !"".equals(daima[i])){
sb.append(", "+ daima[i]);
}
}
sb.append(")\n"
+ " values\n(:new." + daima[1]);
for(int i = 2 ; i < daima.length ; i++){
if(!"QZKRKSJ".equals(daima[i])&& !"QZKXGSJ".equals(daima[i]) && !"".equals(daima[i])){
sb.append(",:new."+ daima[i]);
}
}
sb.append(");\nend tri_" + daima[0] + "_add;\n"
+ "/\n"
+ "alter trigger tri_" + daima[0] + "_add enable;\n");
sb.append("create or replace trigger tri_"+daima[0]+"_upd\n"
+ " after update on " + daima[0] + "\n"
+ " for each row\n"
+ "declare\n v_time varchar2(20);\n"
+ "begin\nselect to_char(sysdate,'yyyymmddhh24miss') into v_time from dual@wer;\n"
+ " update " + daima[0] + "@wer set\n");
for(int i = 2 ; i < daima.length ; i++){
if(!"QZKRKSJ".equals(daima[i])&& !"QZKXGSJ".equals(daima[i]) && !"".equals(daima[i])){
sb.append( daima[i] + "=:new."+ daima[i] + ",");
}
}
sb.append("\nSTXGSJ=v_time\n"
+ " where " + daima[1] + "=:new."+ daima[1] + ";\n"
+ "end tri_" + daima[0] + "_upd;\n"
+ "/\n"
+ "alter trigger tri_" + daima[0] + "_upd enable;\n\n");
return sb.toString();
}
}
生成sql的格式如下:
每个创建触发器语句后面都有一个斜杠 '/ ',是为了能批量创建触发器。
没有斜杠的话需要一个个手动选中sql执行。
第三步 生成测试数据insert,update的sql
测试insert触发器需要insert数据,我这里除了日期字段插入to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),其他都插入 '5'。
package util;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;
public class 生成insert语句 {
public static void main(String[] args) {
String filePath = "C:\\表信息.txt"; //读取的文件夹路径
try {
//输出的文件夹路径
FileWriter fw = new FileWriter("C:\\insert.sql", true);
BufferedWriter bw = new BufferedWriter(fw);
try (BufferedReader bufReader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF-8")))//数据流读取文件
{
String temp = null;
while ((temp = bufReader.readLine()) != null) {
String[] daima = temp.split(",");
if(daima.length>1){
System.out.print(1);
bw.append(gen(daima));// 往已有的文件上添加字符串
}
}
bufReader.close();
bw.close();
fw.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static String gen(String[] daima){
StringBuilder sb = new StringBuilder();
sb.append("insert into "+daima[0]+"\n"
+ " ( " + daima[1]);
for(int i = 2 ; i < daima.length ; i++){
if(!"".equals(daima[i])){
sb.append(", "+ daima[i]);
}
}
sb.append(")\n"
+ " values\n( '5'");
for(int i = 2 ; i < daima.length ; i++){
if(!"".equals(daima[i]) && (daima[i].endsWith("SJ") || daima[i].endsWith("RQ"))){
sb.append(",to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss')");
}else if(!"".equals(daima[i]) && (daima[i].endsWith("SJLY_DSDM")) ){
sb.append(",'12345'");
}else if (!"".equals(daima[i])){
sb.append(",'5'");
}
}
sb.append(");\n\n");
return sb.toString();
}
}
生成的sql语句截图:
测试update触发器同理,把非主键,非日期的字段值改成 '6' ,稍微改一下就行。
package util;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;
public class 生成update语句 {
public static void main(String[] args) {
String filePath = "C:\\表信息.txt"; //读取的文件夹路径
try {
//输出的文件夹路径
FileWriter fw = new FileWriter("C:\\update.sql", true);
BufferedWriter bw = new BufferedWriter(fw);
try (BufferedReader bufReader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF-8")))//数据流读取文件
{
String temp = null;
while ((temp = bufReader.readLine()) != null) {
String[] daima = temp.split(",");
if(daima.length>1){
System.out.print(1);
bw.append(gen(daima));// 往已有的文件上添加字符串
}
}
bufReader.close();
bw.close();
fw.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static String gen(String[] daima){
StringBuilder sb = new StringBuilder();
sb.append("UPDATE "+daima[0]+"\n"
+ "SET \n" + daima[2] + "='6'");
for(int i = 3 ; i < 5 ; i++){
if(!"".equals(daima[i]) && daima[i].endsWith("SJ")){
sb.append("," + daima[i] + "= to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss')");
}else if (!"".equals(daima[i])){
sb.append("," + daima[i] + " = '6'\n");
}
}
sb.append("where "+ daima[1] +" = '5';\n\n");
return sb.toString();
}
}
生成的sql截图:
第四步 删除测试数据
删除主键值为 '5' 的数据。
package util;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;
public class 生成delete语句 {
public static void main(String[] args) {
String filePath = "C:\\表信息.txt"; //读取的文件夹路径
try {
//输出的文件夹路径
FileWriter fw = new FileWriter("C:\\delete.sql", true);
BufferedWriter bw = new BufferedWriter(fw);
try (BufferedReader bufReader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF-8")))//数据流读取文件
{
String temp = null;
while ((temp = bufReader.readLine()) != null) {
String[] daima = temp.split(",");
if(daima.length>1){
System.out.print(1);
bw.append(gen(daima));// 往已有的文件上添加字符串
}
}
bufReader.close();
bw.close();
fw.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
// DELETE FROM Person WHERE LastName = 'Wilson'
public static String gen(String[] daima){
StringBuilder sb = new StringBuilder();
sb.append("DELETE FROM "+daima[0]+" WHERE " + daima[1] + "='5' ; \n");
return sb.toString();
}
}
生成sql语句截图:
总结:重点是收集表信息,表信息有了什么都好说,可以用表信息做任何事情。
拒绝机械式工作。