postgre转oracle数据迁移，表名称和字段名成转⼤写，序列触发
器创建
1.表迁移
推荐使⽤Navicat 从postgre迁移oracle ，基本没有报错
迁移完成后
执⾏ 表明转⼤写和字段转⼤写语句
表明转⼤写
Begin
for c in (select table_name tn from user_tables where table_name <> upper(table_name)) loop
begin
execute immediate 'alter table "'||c.tn||'" rename to '||c.tn;
exception
when others then
dbms_output.put_'已存在');
end;
end loop;
end;
-- oracle 批量修改列名为⼤写(当前登录⽤户)
begin
for cl in (SELECT table_name,column_name from user_tab_columns WHERE column_name<>upper(column_name) and upper(column_name) not in('SIZE','CHECK')) loop
begin
execute immediate 'alter table '||cl.table_name||' rename column "'|| cl.column_name ||'" to '||lumn_name); exception
when others then
dbms_output.put_line(cl.table_name||'.'||cl.column_name||'已存在');
end;
end loop;
end;
3. 下⾯使⽤java代码批量⽣成触发器和序列
需要⽤到 hutool 的数据库连接⼯具
import io.file.FileWriter;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.DSFactory;
import cn.hutool.db.sql.SqlExecutor;
import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
* @author zz
* @version 1.0
* 序列
* DROP SEQUENCE "WQJCXXPT"."SEQ_TABLE_ZY";
* CREATE SEQUENCE "WQJCXXPT"."SEQ_TABLE_ZY" MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 CACHE 10;
* <p>
* 触发器
* CREATE TRIGGER "WQJCXXPT"."SEQ_TABLE_ZY" BEFORE INSERT ON "WQJCXXPT"."TABLE_ZY" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR E * begin
* select SEQ_val into :new.sid from dual;
* if :new.PXBJ is null then
* select SEQ_TABLE_ZY.CURRVAL into :new.PXBJ from dual;
* end if;
* end;
* <p>
* <p>
* <p>
* <p>
* <p>
* <p>
* <p>
* 表明转⼤写
* Begin
* for c in (select table_name tn from user_tables where table_name <> upper(table_name)) loop
* begin
* execute immediate 'alter table "'||c.tn||'" rename to '||c.tn;
* exception
* when others then
* dbms_output.put_'已存在');
* end;
* end loop;
* end;
* <p>
* <p>
* <p>
* -- oracle 批量修改列名为⼤写(当前登录⽤户)
* begin
* for cl in (SELECT table_name,column_name from user_tab_columns WHERE column_name<>upper(column_name) and upper(column_name) not in('SIZE','C * begin
* execute immediate 'alter table '||cl.table_name||' rename column "'|| cl.column_name ||'" to '||lumn_name);
* exception
* when others then
* dbms_output.put_line(cl.table_name||'.'||cl.column_name||'已存在');
* end;
* end loop;
* end;
* <p>
* <p>
* <p>
* <p>
* <p>
* <p>
* <p>
* <p>
* <p>
* ----删除前先解除 id 对该序列的依赖
* ALTER TABLE zzgtkjjcxxpt_dev.TABLE_wjsjgl ALTER COLUMN sid SET DEFAULT null;
* DROP SEQUENCE IF EXISTS TABLE_wjsjgl_id_seq;
* SELECT MAX(sid) FROM zzgtkjjcxxpt_dev.TABLE_wjsjgl;
* ---- id_max 即 id ⽬前的最⼤值，可写为1，可通过 “ SELECT MAX(id) FROM tablename” 得到
* CREATE SEQUENCE TABLE_wjsjgl_id_seq START WITH 2271;
* ALTER TABLE zzgtkjjcxxpt_dev.TABLE_wjsjgl ALTER COLUMN sid SET DEFAULT nextval('TABLE_wjsjgl_id_seq'::regclass);
* <p>
* <p>
* <p>
* <p>
* <p>
*
*
-- 删除主键
ALTER TABLE TABLE_SR_SXWEBYZT_YYXX DROP CONSTRAINT "TABLE_SR_SXWEBYZT_YYXX_PKEY"
bigdecimal格式化两位小数-- 添加主键
ALTER TABLE TABLE_SR_SXWEBYZT_YYXX ADD CONSTRAINT TABLE_SR_SXWEBYZT_YYXX_PKEY23 PRIMARY KEY ("SID")
*
* <p>
* /
* @description: 批量⽣产 oracle 序列和触发器
* @date 2021/2/7
*/
public class CrateSenquence {
private static String DATABASE_NAME = "SHANGRAO_JCXXPT";
private static Connection conn = null;
public static void main(String[] args) throws SQLException {
create(DATABASE_NAME);
}
public static void create(String DATABASE_NAME) throws SQLException {
DataSource ds = ();
conn = ds.getConnection();
List<Entity> query = Db.use().query("SELECT TABLE_NAME from all_tables where OWNER = '" + DATABASE_NAME + "' ");
List<String> tables = new ArrayList<String>();
// FileWriter wr= new FileWriter("sequense.sql");
for (Entity entity : query) {
String tname = ("TABLE_NAME").toString();
tables.add(tname);
System.out.println("========================================>>>" + tname);
crateSZEQZUENCEZ(tname);
}
for (int i = 0; i < tables.size(); i++) {
System.out.println(i + "--------" + (i));
try {
(i));
(i));
} catch (Exception e) {
System.out.println(e);
}
}
}
//创建触发器
private static void creteTRIGGER(String tname) {
try {
Db.use().query("SELECT PXBJ from " + DATABASE_NAME + "." + tname);
dropTrigger(tname);
String sql = "CREATE OR REPLACE TRIGGER " + DATABASE_NAME + ".SEQ_" + tname + " BEFORE INSERT ON " + DATABASE_NAME + "." + tname + "begin\n" +
" select SEQ_" + tname + ".nextval into :new.sid from dual ;\n" +
" if :new.PXBJ is null then\n" +
" select SEQ_" + tname + ".CURRVAL into :new.PXBJ from dual ; \n" +
" end if;\n" +
"end;\n";
// wr.append(sql+"\n");
System.out.println("-------------->" + sql);
//Db.use().execute(sql, " ", " ", " ");
System.out.println("-------------->" + sql);
} catch (Exception e) {
System.out.println(e);
//pxbj 不存在就使⽤下⾯的序列⽣成器
String().indexOf("PXBJ")<0){
return;
}
try {
String sql = "CREATE OR REPLACE TRIGGER " + DATABASE_NAME + ".SEQ_" + tname + " BEFORE INSERT ON " + DATABASE_NAME + "." + tname "begin\n" +
" select SEQ_" + tname + ".nextval into :new.sid from dual ;\n" +
"end;\n";
System.out.println("-------------->" + sql);
Db.use().execute(sql," ");
} catch (Exception e2) {
System.out.println(e2);
}
}
}
//TODO 创建序列先查询当前表中最⼤的id ，防⽌序列跟id冲突了
private static void crateSZEQZUENCEZ(String tname) {
//
// ----删除前先解除 id 对该序列的依赖
// ALTER TABLE zzgtkjjcxxpt_dev.TABLE_wjsjgl ALTER COLUMN sid SET DEFAULT null;
/
/ DROP SEQUENCE IF EXISTS TABLE_wjsjgl_id_seq;
// SELECT MAX(sid) FROM zzgtkjjcxxpt_dev.TABLE_wjsjgl;
// ---- id_max 即 id ⽬前的最⼤值，可写为1，可通过 “ SELECT MAX(id) FROM tablename” 得到
// CREATE SEQUENCE TABLE_wjsjgl_id_seq START WITH 2271;
// ALTER TABLE zzgtkjjcxxpt_dev.TABLE_wjsjgl ALTER COLUMN sid SET DEFAULT nextval('TABLE_wjsjgl_id_seq'::regclass);
//SELECT
dropSequense(tname);
createSequense(tname);
}
private static void createSequense(String tname) {
try {
BigDecimal minValue = BigDecimal.valueOf(1);
String sql = " SELECT sid FROM " + tname + " where ROWNUM <= 1 ORDER BY sid desc ";
List<Entity> query = Db.use().query(sql);
if (Empty(query) && query.size() > 0) {
minValue = (BigDecimal) (0).get("sid");
minValue= minValue.add(BigDecimal.valueOf(1));
}
Db.use().execute(" CREATE SEQUENCE " + DATABASE_NAME + ".\"SEQ_" + tname + "\"\n" +
" MINVALUE 1 \n" +
" MAXVALUE 99999999999\n" +
" INCREMENT BY 1 \n" +
" START WITH "+minValue+" \n " +
" CACHE 10");
} catch (Exception e) {
System.out.println(e);
}
}
private static void dropSequense(String tname) {
try {
String dropSql = " DROP SEQUENCE SEQ_" + tname + " ";
try {
Db.use().execute(dropSql);
} catch (Exception e) {
System.out.println(e);
}
} catch (Exception exception) {
System.out.println(exception);
}
}
//删除触发器
private static void dropTrigger(String tname) {
try {
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论