用Java实现SQL Server到Oracle数据迁移.doc

用JAVA实现SQL Server到Oracle的数据迁移中国科学院西安网络中心 陈拓2005年9月25日以下的操作以SQL Server2000和Oracle 10g为例。开发工具使用JDveloper 10.1.3。一、 在Oracle数据库中建立一个新帐户在“用SQL DTS实现SQL Server到Oracle的数据迁移”一文中我们介绍过怎样创建一个新帐户，如果已有可用用户，跳过这一部分。1用SQL*plus创建新用户l 建立一个新帐户ct，PL/SQL脚本如下：SET ECHO OFFPROMPT PROMPT specify password for ct as parameter 1 (ct):DEFINE pass = &1PROMPT PROMPT specify default tablespeace for ct as parameter 2 (USERS):DEFINE tbs = &2PROMPT PROMPT specify temporary tablespace for CT as parameter 3 (TEMP):DEFINE ttbs = &3PROMPT PROMPT specify password for SYS as parameter 4 (sys):DEFINE pass_sys = &4PROMPT PROMPT specify log path as parameter 5 (D:oracleproduct10.1.0):DEFINE log_path = &5PROMPT- The first dot in the spool command below is - the SQL*Plus concatenation characterDEFINE spool_file = &log_path.ct_main.logSPOOL &spool_fileREM =REM 删除用户及其方案 REM =DROP USER CT CASCADE;REM =REM 创建新用户，指定默认表空间和临时表空间REM =CREATE USER CT IDENTIFIED BY &pass;ALTER USER CT DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs;ALTER USER CT TEMPORARY TABLESPACE &ttbs;REM =REM 授权 CONNCET AND RESOURCE 角色REM =GRANT CONNECT TO CT;GRANT RESOURCE TO CT;REM =REM 从sys方案授权（方案SYS源类型程序包dbms_stats）REM =CONNECT sys/&pass_sys AS SYSDBA;GRANT execute ON sys.dbms_stats TO ct;REM =REM 设置本次会话的语言、区域和字符集REM =CONNECT ct/&passALTER SESSION SET NLS_LANGUAGE=SIMPLIFIED CHINESE;ALTER SESSION SET NLS_TERRITORY=CHINA;ALTER SESSION SET NLS_CHARSET=ZHS16GBK;REM =REM 解锁REM =SQLalter user hr identified by hr account unlock;spool offl 查看相关信息用DBA_ROLES视图查看角色信息：SQL conn sys/sysoract as sysdbaSQL select * from DBA_ROLES;用DBA_ROLE_PRIVS 视图查看授予授予用户的角色：SQL select * from DBA_ROLE_PRIVS where GRANTEE=CT;用ROLE_SYS_PRIVS 视图查看授予角色的系统权限：SQL select * from ROLE_SYS_PRIVS where ROLE=CONNECT;SQL select * from ROLE_SYS_PRIVS where ROLE=RESOURCE;用SESSION_ROLES 视图查看用户当前已启用的角色：SQL conn ct/ctoractSQL select * from SESSION_ROLES;l 将此脚本存为文件ct_main.sqll 以SYS或SYSTEM登录C:sqlplus sys/sys as sysdbal 运行脚本ct_main.sqlSQL D:lectureoraclect_main.sqll 查看操作日志文件：D:oracleproduct10.1.0 ct_main.log2用JDeveloper创建新用户你也可以用JDeveloper创建新用户。用于创建一个连接的用户名必须有足够的权限来创建用户，该用户既可以被授予CREATE USER权限，也可以被授予一个象管理员那样包含该权限的角色。1) 选择 View | Connection Navigator。2) 展开 Database 并且选择一个 database connection。3) 在connection中，右击schema (与用户名相同) 或者其中的任一项，选择New。4) 从New Gallery窗口的Filter By组合框中选择Available Items。5) 从New Gallery窗口的Categories树型目录中展开Database Tier并且选择Database Objects。6) 从 Items窗口选择User，并且单击确定打开Create Database User窗口，见图1。图1 New Gallery窗口7) 在“创建数据库用户”窗口中输入用户名和口令参数，并选择默认表空间和临时表空间选项，见图2。图2 创建数据库用户窗口8) 单击确定创建新用户。二、安装Microsoft SQL Server数据库JDBC驱动程序1下载JDBC驱动程序l 从下面的微软网站下载JDBC驱动程序，支持JDK 1.3。/china/sql/downloads/2000/jdbc.asp执行setup.exe安装SQL Server JDBC驱动程序。在安装目录下有帮助文件。l 下载SQL Server 2000 Driver for JDBC Service Pack 3，支持JDK 1.4。/downloads/details.aspx?FamilyID=07287b11-0502-461a-b138-2aa54bfdc03a&displaylang=en执行setup.exe安装SQL Server JDBC驱动程序。在安装目录下有帮助文件。2设置驱动程序库 Libraries Tools - Manage Libraries打开Manage Libraries 窗口，如图3。图3 Manage Libraries 窗口 在“管理库”窗口中选择Libraries 标签。 选择User。 单击下面的 New 按钮，打开Create Library窗口，见图4。图4 Create Library窗口 在“创建库”窗口中设置 Library Name 为 SQLServer JDBC。 选择Class Path:，单击Add Entry按钮，打开Select Path Entry对话框，图5。图5 Select Path Entry窗口 在“选择路径入口”窗口中选择安装的驱动程序路径下的lib文件夹中的文件msbase.jar、mssqlserver.jar和msutil.jar，单击Select回到Create Library窗口，单击确定回到Manage Libraries 窗口，单击确定。三、测试SQL Server JDBC驱动程序1创建一个到SQL Server的新连接1) 选择 View | Connection Navigator2) 右击 Database 并且选择一个New database connection，打开创建数据库连接向导，见图6。图6 创建数据库连接向导1/4步3) 在“创建数据库连接向导1/4步”的Connection Name文本框中输入SQLServer_conn；Connection type，选择Third Party JDBC Driver，下一步，见图7。图7 创建数据库连接向导2/4步4) 在“创建数据库连接向导2/4步”的窗口中输入Username：sa；Password：*，单击下一步，图8。图8 创建数据库连接向导3/4步5) 在“创建数据库连接向导3/4步”的窗口中单击New按钮打开Register JDBC Driver对话框，见图9。图9 Register JDBC Driver对话框6) 注册JDBC驱动l 在Driver Class文本中输入：com.microsoft.jdbc.sqlserver.SQLServerDriver。 l 单击Browse按钮，找到前面设置的SQLServer JDBC驱动程序库，单击确定回到创建数据库连接向导3/4步，见图8。7) 在URL文本中输入：jdbc:microsoft:sqlserver:/:1433;SelectMethod=cursor;DatabaseName=在这里，为localhost；为ckgl。单击下一步，见图10。图10 创建数据库连接向导4/4步8) 在“创建数据库连接向导4/4步”的窗口中单击Test Connection按钮进行测试,单击下一步，完成。在“连接导航”窗口中展开database节点，再展开SQLServer_conn连接，在dbo文件夹中查看数据库对象。2用JAVA程序测试l 在JDeveloper 10.1.3中导入 SQL Server JDBC 驱动程序：n 右击要使用SQL Server JDBC 的项目。n 选择 Project Properties，打开项目属性窗口，图11。图11 项目属性窗口n 在 Project Properties 窗口左侧选择 Libraries，在右侧单击Add Library按钮打开Add Library窗口，图12。图12 项目属性窗口n 在Add Libraries窗口中找到前面设置的 SQLServer JSBC 驱动程序库，单击确定。回到Project Properties 窗口，图13。图13 Add Libraries窗口n 单击确定。l 重复上面的步骤添加Oracle JDBC驱动程序。图14 添加Oracle JDBC驱动程序l 在JDeveloper 10.1.3中写连接SQL Server的JDBC JAVA测试程序：n 用JDeveloper添加一个新的Java Class，命名为SQLServerTest.java，测试程序代码：package mypackage;public class SQLServerTest private java.sql.Connection con = null; private final String url = jdbc:microsoft:sqlserver:/; private final String serverName= localhost; private final String portNumber = 1433; private final String databaseName= pubs; private final String userName = sa; private final String password = *; private final String selectMethod = cursor; public SQLServerTest() private java.sql.Connection getConnection() try / A. 注册 SQL Server JDBC 驱动程序 Class.forName(com.microsoft.jdbc.sqlserver.SQLServerDriver); / B. 创建新数据库连接 con= java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password); if(con!=null) System.out.println(Connection Successful!); catch(Exception e) e.printStackTrace(); System.out.println(Error Trace in getConnection() : + e.getMessage(); return con; private String getConnectionUrl() return url+serverName+:+portNumber+;databaseName=+databaseName+;selectMethod=+selectMethod+; private void closeConnection() try if(con!=null) con.close(); con=null; catch(Exception e) e.printStackTrace(); public void displayDbProperties() java.sql.DatabaseMetaData dm = null; java.sql.ResultSet rs = null; try con= this.getConnection(); if(con!=null) dm = con.getMetaData(); System.out.println(驱动器信息：); System.out.println(t驱动器名字: + dm.getDriverName(); System.out.println(t驱动器版本: + dm.getDriverVersion (); System.out.println(n数据库信息： ); System.out.println(t数据库名字: + dm.getDatabaseProductName(); System.out.println(t数据库版本: + dm.getDatabaseProductVersion(); System.out.println(显示可用的数据库目录：); rs = dm.getCatalogs(); while(rs.next() System.out.println(tcatalog: + rs.getString(1); rs.close(); rs = null; closeConnection(); else System.out.println(Error: No active Connection); catch(Exception e) e.printStackTrace(); dm=null; public static void main(String args) SQLServerTest sQLServerTest = new SQLServerTest(); sQLServerTest.displayDbProperties(); n 编译运行，结果如下图15 SQL Server JDBC连接测试程序运行结果l 在JDeveloper 10.1.3中写连接Oracle的JDBC JAVA测试程序：n 用JDeveloper添加一个新的Java Class，命名为OracleTest.java，测试程序代码：package mypackage;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.DriverManager;import java.sql.SQLException;public class OracleTest public OracleTest() public static void main(String args) throws SQLException / A. 注册 Oracle JDBC 驱动程序 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver(); / B. 创新新数据库连接 Connection conn = DriverManager.getConnection(jdbc:oracle:thin:localhost:1521:oract, ct, ct); / C. 取得连接数据 DatabaseMetaData md = conn.getMetaData(); System.out.println(数据库版本：); System.out.println(-); System.out.println(md.getDatabaseProductVersion(); System.out.println(); System.out.println(驱动程序名称与版本：); System.out.println(-); System.out.print(md.getDriverName() + + md.getDriverVersion(); / D. 关闭数据库连接 conn.close(); n 编译运行，结果如下图16 Oracle测试程序运行结果四、SQL Server和Oracle数据类型的对应关系1SQL SERVER与ORACLE的数据类型不同当数据从SQL SERVER2000向ORACLE 10g迁移时，可以做如下调整：SQL SERVERORACLE数字类型DECIMAL(P, S)NUMBER(P, S)NUMERIC(P, S)NUMBER(P, S)FLOAT(N)NUMBER(N)INTNUMBERSMALLINTNUMBERTINYINTNUMBERMONEYNUMBER19,4SMALLMONEYNUMBER19,4字符类型CHAR(N)CHAR(N)VARCHAR(N)VARCHAR2(N)日期时间类型DATETIMETIMESTAMP(6)SMALLDATETIMETIMESTAMP(6)其它TEXTCLOBIMAGEBLOBBITNUMBER（1）2ID列向SEQUENCE迁移在SQL SERVER中，可以将数据库中的某一字段定义为IDENTITY列以做主键识别，如：ItemID int identity(1,1) /*记录编号字段*/在这里，ItemID是一个ID列，在向具有该列的表插入记录时，系统将从1开始以1的步长自动对ItemID的值进行维护。但在ORACLE中，没有这样的ID列定义，而是采用另一种方法，即创建SEQUENCE。例如，创建SEQUENCE SEQ_ITEMID用于表Portal_Discussion的ITEMID列：create sequence SEQ_DISC_ITEMID increment by 1 /*该SEQUENCE以1为步长递增*/start with 1 maxvalue 99999; /*从1开始，最大增长到99999*/实际操作时引用SEQUENCE的下一个值，例如：insert into Portal_Discussion (ITEMID, MODULEID, TITLE) values(SEQ_DISC_ITENID.NEXTVAL, 18, Oracle 10g的新特性);除了上面的引用方法，还可以在ORACLE中采用“插入前触发器”实现自动增长字段，例如：CREATE OR REPLACE TRIGGER GenaerateITEMIDBEFORE INSERT ON Portal_DiscussionFOR EACH ROW Select SEQ_DISC_ITEMID.NEXTVAL INTO :NEW.ID FROM DUAL;BEGINEND GenaerateITEMID;GenaerateITEMID实际上修改了伪记录:new的area_id值。 :new最有用的一个特性-当该语句真正被执行时，:new中的存储内容就会被使用。所以系统每次都能自动生成新的号码。3SQL Server中的数据库表例子我们用仓库管理程序的入库单及其明细表作为例子l 入库单表结构l 入库单明细表结构l 表间关系4在Oracle数据库中创建相应的对象l 创建与SQLServer中“入库单”与“入库单明细”表对应的表InputVoucher和InputVoucherDetail。SQL脚本程序StoreTable.sqlREM $ 无触发器，不用序列生成主键，用于 SQL Server 到 Oracle 的数据迁移 $spool Store.logDROP TABLE InputVoucherDetail;DROP TABLE InputVoucher;CREATE TABLE InputVoucher ( InputVoucher_id NUMBER NOT NULL, AccountSetCode VARCHAR2(10), VoucherNumber VARCHAR2(15), InputDate DATE, ProviderCode VARCHAR2(10), Operator VARCHAR2(10), Checker VARCHAR2(10) DEFAULT , CarryForwardSign NUMBER(1,0) DEFAULT 0, CONSTRAINT PK_InputVoucher PRIMARY KEY (InputVoucher_id);CREATE TABLE InputVoucherDetail ( InputVoucherDetail_id NUMBER NOT NULL, InputVoucher_id NUMBER, AccountSetCode VARCHAR2(10), VoucherNumber VARCHAR2(15), MerchCode CHAR(8), MerchName VARCHAR2(100), Unit VARCHAR2(8), Quantity NUMBER(13,6), Price NUMBER(13,6), Note VARCHAR2(100), KeepBookSign NUMBER(1,0) DEFAULT 0, ReturnSign NUMBER(1,0) DEFAULT 0, CarryFforwardSign NUMBER(1,0) DEFAULT 0, Storage_id NUMBER DEFAULT 0, CONSTRAINT PK_InputVoucherDetail PRIMARY KEY (InputVoucherDetail_id), CONSTRAINT INPUT_DETAIL FOREIGN KEY (InputVoucher_id) REFERENCES InputVoucher (InputVoucher_id);show errorspurge recyclebin;spool offl 创建序列和触发器的SQL脚本SEQUENCETigger.sqlREM $ 用触发器、序列生成主键，用于插入新记录 $spool Store.logDROP SEQUENCE ORDERNUM_INPUT;DROP SEQUENCE ORDERNUM_INPUT_DETAIL;CREATE SEQUENCE ORDERNUM_INPUT START WITH 13577;CREATE SEQUENCE ORDERNUM_INPUT_DETAIL START WITH 33002;CREATE OR REPLACE TRIGGER input_before_insert BEFORE INSERT ON InputVoucher FOR EACH ROWBEGIN IF ( :new.InputVoucher_id 0 OR :new.InputVoucher_id IS NULL) THEN SELECT ordernum_input.nextval INTO :new.InputVoucher_id FROM dual; END IF;END;/CREATE OR REPLACE TRIGGER input_detail_before_insert BEFORE INSERT ON InputVoucherDetail FOR EACH ROWBEGIN IF ( :new.InputVoucherDetail_id sqlplus /nologSQL conn ct/ct已连接。SQL D:softOracleSoftjdevstudio1013jdevmyworkckglDatabaseSetupStoreTable.sql2入库单数据迁移程序SQLToOracleIV.javapackage mypackage;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.Statement;public class SQLToOracleIV private java.sql.Connection con = null; private final String url = jdbc:microsoft:sqlserver:/; private final String serverName= localhost; private final String portNumber = 1433; private final String databaseName= ckgl; private final String userName = sa; private final String password = *; private final String selectMethod = cursor; private java.sql.Connection conn = null; public SQLToOracleIV() private String getConnectionUrl() return url+serverName+:+portNumber+;databaseName=+databaseName+;selectMethod=+selectMethod+; private java.sql.Connection getConnection() try / A. 注册 SQL Server JDBC 驱动程序 Class.forName(com.microsoft.jdbc.sqlserver.SQLServerDriver); / B. 创建新数据库连接 con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password); if(con!=null) System.out.println(SQL Server Connection Successful!); catch(Exception e) e.printStackTrace(); System.out.println(Error SQL Server Trace in getConnection() : + e.getMessage(); return con; private java.sql.Connection getOracleConnection() try / A. 注册 Oracle JDBC 驱动程序 Class.forName(oracle.jdbc.driver.OracleDriver); / B. 创建新数据库连接 conn = java.sql.DriverManager.getConnection(jdbc:oracle:thin:localhost:1521:oract, ct ,ct); if(conn!=null) System.out.println(Oracle Connection Successful!); catch(Exception e) e.printStackTrace(); System.out.println(Error Oracle Trace in getConnection() : + e.getMessage(); return conn; private void closeSQLServerConnection() try if(con!=null) con.close(); con=null; catch(Exception e) e.printStackTrace(); private void closeOracleConnection() try if(conn!=null) conn.close(); conn=null; catch(Exception e) e.printStackTrace(); public void displayDbProperties() java.sql.DatabaseMetaData dm = null; String InsertStatement =; try con = this.getConnection(); conn = this.getOracleConnection(); if(con!=null & conn!=null) / C. 创建 Statement 对象 Statement stmt = con.createStatement(); Statement stmtOracle = conn.createStatement(); / D. 执行 SELECT 命令，查询结果存放在 ResultSet 对象中 ResultSet rset = stmt.executeQuery(SELECT * FROM 入库单); / E. 取得 ResultSet 结果集相关的信息 ResultSetMetaData md = rset.getMetaData(); / F. 取得 ResultSet 对象中的字段数量 int no_cols = md.getColumnCount(); String col_names = new Stringno_cols; / G. 取得 ResultSet 对象中各字段名称 for (int i = 0; i no_cols; i+) col_namesi = md.getColumnLabel(i+1); / H. 先打印出各字段名称 for (int i = 0; i no_cols; i+) System.out.print(col_namesi+t); System.out.println(n-); / I. 打印每笔数据列中各字段数据 while (rset.next() for (int i = 1; i = no_cols; i+) System.out.print(rset.getString(i) + t); System.out.println(); / J. 数据插入 Oracle 中 InsertStatement=INSERT INTO INPUTVOUCHER (InputVoucher_id,AccountSetCode,VoucherNumber, + InputDate,ProviderCode,Operator,Checker,CarryForwardSign) + VALUES ( + rset.getString(1) + , + rset.getString(2) + , + rset.getString(3) + , + TO_DATE(SUBSTR( + rset.getString(4) + ,1,19),yyyy-mm-dd hh24:mi:ss), + rset.getString(5) + , + rset.getString(6) + , + rset.getString(7) + , + rset.getString(8) + ); System.out.print(InsertStatement+n); stmtOracle.executeUpdate(InsertStatement); rset.close(); rset = null; stmt.clos