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) {                }            }        }    }}

 

oracle資料庫指令碼轉為sqlServer、DB2