postgre 转oracle 数据迁移, 表名称和字段名成转大写, 序列触发器创建

最新推荐文章于 2024-05-18 08:00:00 发布

学海无涯苦做java

最新推荐文章于 2024-05-18 08:00:00 发布

阅读量795

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/chenzhenguo123/article/details/118729346

版权

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_line(c.tn||'已存在');
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 '||upper(cl.column_name);
exception
when others then
dbms_output.put_line(cl.table_name||'.'||cl.column_name||'已存在');
end;
end loop;
end;

3. 下面使用java代码批量生成触发器和序列

需要用到 hutool 的数据库连接工具

import cn.hutool.core.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 EACH ROW WHEN (new.sid is null)
 * begin
 * select SEQ_TABLE_ZY.nextval 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_line(c.tn||'已存在');
 * 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','CHECK')) loop
 * begin
 * execute immediate 'alter table '||cl.table_name||' rename column "'|| cl.column_name ||'" to '||upper(cl.column_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>
 * <p>
 *
 *
-- 删除主键
ALTER TABLE TABLE_SR_SXWEBYZT_YYXX DROP CONSTRAINT "TABLE_SR_SXWEBYZT_YYXX_PKEY"
-- 添加主键
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 = DSFactory.get();
        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 = entity.get("TABLE_NAME").toString();
            tables.add(tname);
            System.out.println("========================================>>>" + tname);
            crateSZEQZUENCEZ(tname);
        }
        for (int i = 0; i < tables.size(); i++) {
            System.out.println(i + "--------" + tables.get(i));
            try {
                defaultSCBJ(tables.get(i));
                creteTRIGGER(tables.get(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 + " REFERENCING OLD AS \"OLD\" NEW AS \"NEW\" FOR EACH ROW WHEN (new.sid is null) \n" +
                    "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");
            SqlExecutor.executeForGeneratedKey(conn, sql, " ", " ", " ");
            System.out.println("-------------->" + sql);
//Db.use().execute(sql, " ", " ", " ");
            System.out.println("-------------->" + sql);
        } catch (Exception e) {
            System.out.println(e);
//pxbj 不存在就使用下面的序列生成器
            if(e.toString().indexOf("PXBJ")<0){
                return;
            }
            try {
                String sql = "CREATE OR REPLACE TRIGGER " + DATABASE_NAME + ".SEQ_" + tname + " BEFORE INSERT ON " + DATABASE_NAME + "." + tname + " REFERENCING OLD AS \"OLD\" NEW AS \"NEW\" FOR EACH ROW WHEN (new.sid is null) \n" +
                        "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 (Utils.notEmpty(query) && query.size() > 0) {
                minValue = (BigDecimal) query.get(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 {
            String dropSql = "DROP TRIGGER SEQ_" + tname + " ";
            try {
                Db.use().execute(dropSql);
            } catch (Exception e) {
                System.out.println(e);
            }
        } catch (Exception exception) {
            System.out.println(exception);
        }
    }
    //删除标记默认值
    private static void defaultSCBJ(String tname) {
        try {
// String sql = " ALTER TABLE " + DATABASE_NAME + "."+tname+" MODIFY SCBJ DEFAULT 0 ";
            String sql = " ALTER TABLE " + tname + " MODIFY SCBJ DEFAULT 0";
// SqlExecutor.executeForGeneratedKey(conn, sql,DATABASE_NAME,tname);
            SqlExecutor.execute(conn, sql);
// Db.use().query(sql);
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

##db.setting文件创建

# CrateSenquence 批量创建序列需要的配置
url = jdbc:oracle:thin:@1.0.10.1:1521/orcl
user = CZG_test
pass = test
## 可选配置
# 是否在日志中显示执行的SQL
showSql = true
# 是否格式化显示的SQL
formatSql = false
# 是否显示SQL参数
showParams = true
# 打印SQL的日志等级，默认debug，可以是info、warn、error
sqlLevel = debug

oracle 转 postgre 大写转小写

[转][postgresql]转换表名为小写
转自：https://www.cnblogs.com/heibai-ma/p/13675068.html
复制代码
-- 创建exec(sqlstring)函数方便执行
CREATE OR REPLACE FUNCTION "public"."exec"("sqlstring" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$
    declare
        res varchar(50);
    BEGIN
        EXECUTE sqlstring;
        RETURN 'ok';
    END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
    
    
-- 通过下列语句转换column_name中的大写字母
SELECT
    exec('alter table "' || table_name || '" rename column  "' || column_name || '" to ' || lower( column_name ) || ';')
FROM
    information_schema.COLUMNS 
WHERE
    table_schema = 'public' 
    AND column_name <> lower(column_name)
    --AND table_name like '%';
    
    
-- 查询表名中的大写
SELECT * FROM information_schema.TABLES 
WHERE table_schema = 'public' AND table_catalog = '表名' AND table_name <> lower( table_name );

-- 修改表名中的大写为小写
SELECT
    exec ( 'alter table "' || table_name || '" rename to ' || lower( table_name ) || ';' ) 
FROM
    information_schema.tables 
WHERE
    table_schema='public' 
    and table_catalog = '表名' 
    and table_name <> lower(table_name);