oracle数据库脚本转为sqlServer、DB2

package util;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;

/**
 * @author wyl
 * @Description TODO 
 * @date 2016-5-19
 *
 */

public class OracleToOthers {

    /**
     * @Description: TODO 
     * @param args  
     */

    public static void main(String[] args) {
        /*
         * 原oracle数据库脚本由plSql导出,导出时只选择create tables选项
         * 对于原oracle数据库中的表或者列的注释，如果注释中存在换行符，生成的目标脚本会有错误，请在生成的目标脚本做修改
         */
        //原文件路径
        String oracleFile = "C:\\Users\\admin\\Desktop\\table.sql";
        //目标文件路径
        String sqlServerFile = "C:\\Users\\admin\\Desktop\\table_sqlserver.sql";
        String db2File = "C:\\Users\\admin\\Desktop\\table_db2.sql";

        oracleToSqlserver(oracleFile,sqlServerFile);
        oracleToDB2(oracleFile,db2File);
        

    }
    /**
     * @Description: 由oracle脚本转为sqlserver脚本
     * @param oracleFile    原oracle数据库脚本文件路径
     * @param sqlServerFile    转为sqlserver脚本文件的目标路径
     */
    public static void oracleToSqlserver(String oracleFile,String sqlServerFile) {
        File readFile = new File(oracleFile);
        File writeFile = new File(sqlServerFile);
        BufferedReader reader = null;
        BufferedWriter writer = null;
        try {
            reader = new BufferedReader(new FileReader(readFile));
            writer = new BufferedWriter(new FileWriter(writeFile));
            StringBuffer sb = new StringBuffer();
            String tempString = null;
            String tempStr = null;
            String tempS = null;
            
            String tableName = null;
            String column = null;
            String common = null;
            int endIndex = 0;
            // 一次读入一行，直到读入null为文件结束
            while ((tempString = reader.readLine()) != null) {
                // 显示行号
                //System.out.println("line " + line + ": " + tempString);
                
                //转换数据类型
                tempString=tempString.replaceAll("VARCHAR2","VARCHAR");
                tempString=tempString.replaceAll("NUMBER","DECIMAL");
                tempString=tempString.replaceAll("CLOB","TEXT");
                if(tempString.indexOf("TIMESTAMP")!=-1){
                    tempString=tempString.replaceAll("TIMESTAMP","DATETIME");
                    int str = tempString.indexOf("(");
                    int end = tempString.indexOf(")");
                    System.out.println(tempString.substring(str, end+1));
                    tempString=tempString.replaceAll("\\("+tempString.substring(str+1, end)+"\\)", "");
                }
                
                //添加注释
                if(tempString.startsWith("prompt")||tempString.startsWith("set ")||tempString.startsWith("commit;")||tempString.startsWith("spool")){
                    tempString = "--"+tempString;
                }
                
                //更改sql注释脚本
                if(tempString.startsWith("comment on")){
                    tempStr = tempString;
                    continue;
                }
                if(tempString.startsWith("  is '")){
                    tempString = tempStr + tempString;
                    
                    if(!tempString.endsWith("';")){
                        tempS = tempString;
                        tempStr = "";
                        continue;
                    }
                    
                }
                if(tempString.startsWith("';")){
                    tempString = tempS+tempString;
                    tempS = "";
                }
                //表注释
                if(tempString.startsWith("comment on table ")&&tempString.endsWith("';")){
                    endIndex = tempString.indexOf("  is '");
                    //表名
                    tableName = tempString.substring(17,endIndex);
                    String[] strA = tempString.split("'");
                    common = strA[1];
                    tempString = "EXECUTE sp_addextendedproperty N'MS_Description', N'"+common+"', N'user', N'dbo', N'table', N'"+tableName+"', NULL, NULL";
                }
                //列注释
                if(tempString.startsWith("comment on column ")&&tempString.endsWith("';")){
                    endIndex = tempString.indexOf(".");
                    //表名
                    tableName = tempString.substring(18,endIndex);
                    String[] strA = tempString.split("'");
                    int endIn = strA[0].indexOf("  is");
                    column = strA[0].substring(endIndex+1, endIn);
                    common = strA[1];
                    tempString = "EXECUTE sp_addextendedproperty N'MS_Description', N'"+common+"', N'user', N'dbo', N'table', N'"+tableName+"', N'column', N'"+column+"'";
                }
                // 打印
                System.out.println(tempString);
                sb.append(tempString+"\r\n");
            }
            
            reader.close();
            //写入目标文件
            writer.write(sb.toString());
            writer.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e1) {
                }
            }
        }
    }
    /**
     * @Description: 由oracle脚本转为DB2脚本 
     * @param oracleFile    原oracle数据库脚本文件路径
     * @param db2File    转为DB2脚本的目标文件路径
     */
    public static void oracleToDB2(String oracleFile,String db2File) {
        File readFile = new File(oracleFile);
        File writeFile = new File(db2File);
        BufferedReader reader = null;
        BufferedWriter writer = null;
        try {
            reader = new BufferedReader(new FileReader(readFile));
            writer = new BufferedWriter(new FileWriter(writeFile));
            StringBuffer sb = new StringBuffer();
            String tempString = null;
            // 一次读入一行，直到读入null为文件结束
            while ((tempString = reader.readLine()) != null) {
                // 显示行号
                //System.out.println("line " + line + ": " + tempString);
                
                //转换数据类型
                tempString=tempString.replaceAll("VARCHAR2","VARCHAR");
                tempString=tempString.replaceAll("NUMBER","DECIMAL");
                tempString=tempString.replaceAll("CLOB","CLOB");
                //添加注释
                if(tempString.startsWith("prompt")||tempString.startsWith("set ")||tempString.startsWith("commit;")||tempString.startsWith("spool")){
                    tempString = "--"+tempString;
                }
                
                System.out.println(tempString);
                sb.append(tempString+"\r\n");
            }
            
            reader.close();
            //写入目标文件
            writer.write(sb.toString());
            writer.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e1) {
                }
            }
        }
    }
}

 

转载于:https://www.cnblogs.com/luckydog1/p/5725756.html