在實(shí)際的軟件項(xiàng)目的開發(fā)過程中，特別是在企業(yè)的應(yīng)用系統(tǒng)集成（EAI）項(xiàng)目中廣大開發(fā)人員經(jīng)常遇到不同關(guān)系型數(shù)據(jù)庫之間的數(shù)據(jù)移植問題。筆者根據(jù)自己在工作中的不同數(shù)據(jù)庫數(shù)據(jù)移植的經(jīng)驗(yàn)經(jīng)過通用化的java應(yīng)用程序,演示把數(shù)據(jù)從Oracle數(shù)據(jù)庫移植到DB2數(shù)據(jù)庫中。
當(dāng)然也可以應(yīng)用其它支持JDBC和ODBC的關(guān)系型數(shù)據(jù)庫。
本議題分為二部分：
一、Oracle和DB2數(shù)據(jù)庫對(duì)象的異同
二、把數(shù)據(jù)從Oracle移植到DB2的Java應(yīng)用程序的詳細(xì)分析.詳見從Oracle 到DB2（二）
Oracle和DB2數(shù)據(jù)庫對(duì)象的異同
首先讓大家快速的熟悉一下Oracle和DB2各個(gè)方面的異同
上面的表格比較全面的對(duì)比了二者之間的區(qū)別，相信大家應(yīng)該對(duì)Oracle 和DB2在整體上有了比較全面的理解，
好，下面就讓大家對(duì)二個(gè)數(shù)據(jù)庫的數(shù)據(jù)類型作詳細(xì)的比較。
下面我通過一些具體的實(shí)例來詳細(xì)演示DB2和Oracle的區(qū)別：
一、日期
- Oracle的日期用法：
Create table date_demo ( day_demo varchar2(25), actualDate DATE, detaildate DATE );
插入數(shù)據(jù)
insert into date_demo values ('new years',to_date('01-02-2002','DD-MON-YYYY'), to_date('01-02-2002','DD-MON-YYYY'), ...;
查看結(jié)果：
select * from date_demo; day_demo actualDate detaildate ------------- -------------- ------------ new years 01-02-2002 01-02-2002 new years 01-02-2002 01-02-2002 ......;
- DB2的日期用法：
Create table date_demo ( day_demo char(25), actualDate DATE, detaildate DATE );
插入數(shù)據(jù)
insert into date_demo values ('new years',('01-02-2002'), to_date('01-02-2002'), ......;
查看結(jié)果：
select * from date_demo; day_demo actualDate detaildate --------------- -------------- ------------ new years 01-02-2002 01-02-2002 new years 01-02-2002 01-02-2002 ......;
二、序列
- Oracle的序列用法：
create table dep (deptno smallint not null, deptname varchar2(36) not null, mgrno char(6), admrdept smallint not null, location char(30)); create sequence dept_seq start with 200 increment by 1;
接著
insert into dept values (dept_seq.nextval,'sales','smith',55,'downtown'), (dept_seq.nextval,'marketing','wong',12,'midtown'), (dept_seq.nextval,'accounting','fisher',300,'uptown'); select * from dept;
- DB2的序列用法：
首先建表：
create table dept (deptno smallint not null generated always as identity(start with 200, increment by 1), deptname varchar(36) not null, location char(30));
接下來插入數(shù)據(jù)
insert into dept values (default,'sales','smith,50,'downtown'), (default,'marketing','wong',23,'midtown'), (default,'accounting','fisher'200,'uptown');
查詢結(jié)果:
select * from dept;
結(jié)果和Oracle 的一樣。
三、截?cái)啻蟮臄?shù)據(jù)表的內(nèi)容
- Oracle在截?cái)啻蟮臄?shù)據(jù)表提供了一個(gè)函數(shù)truncate，通過語句truncate table tab_name來清空表中的內(nèi)容并重組表的空間，truncate 是DDL語句不能回滾。
- DB2不支持truncate語句，但提供另外二種方法來解決上面的問題。
（1） 在建表時(shí)加選項(xiàng)not logged initially ，當(dāng)清空表時(shí)通過alter table [name] activate not logged initially with empty table
（2）首先在操作系統(tǒng)上建一個(gè)空文件empty.del，接著通過import命令import from empty.del of del replace into [table_name]來清空表中的數(shù)據(jù)并重組表空間。
以上是我在使用Oracle 和DB2數(shù)據(jù)庫的過程中總結(jié)的不通點(diǎn)，更多的不通可以查閱相關(guān)的手冊(cè)。
通過上面的介紹相信大家對(duì)Oracle和DB2的區(qū)別會(huì)有一定程度的了解，下一篇將在這一篇的基礎(chǔ)上介紹如何把Oracle中的數(shù)據(jù)移植到DB2數(shù)據(jù)庫中來。
-- This is a CLP script that creates an SQL procedure. -- To create the SQL procedure using this script, perform the following steps: -- 1. connect to the database -- 2. issue the command "db2 -td@ -vf <script-name>" -- where <script-name> represents the name of this script -- -- To call this SQL procedure from the command line, perform the following steps: -- 1. connect to the database -- 2. issue the following command: -- db2 "CALL median_result_set (20000)" -- -- The sample "rsultset.c" demonstrates how to call this SQL procedure using -- a CLI client application. CREATE PROCEDURE median_result_set -- Declare medianSalary as INOUT so it can be used in DECLARE CURSOR (INOUT medianSalary DOUBLE) RESULT SETS 2 LANGUAGE SQL BEGIN DECLARE v_numRecords INT DEFAULT 1; DECLARE v_counter INT DEFAULT 0; DECLARE c1 CURSOR FOR SELECT CAST(salary AS DOUBLE) FROM staff ORDER BY salary; -- use WITH RETURN in DECLARE CURSOR to return a result set DECLARE c2 CURSOR WITH RETURN FOR SELECT name, job, CAST(salary AS DOUBLE) FROM staff WHERE salary > medianSalary ORDER BY salary; -- you can return as many result sets as you like, just -- ensure that the exact number is declared in the RESULT SETS -- clause of the CREATE PROCEDURE statement -- use WITH RETURN in DECLARE CURSOR to return another result set DECLARE c3 CURSOR WITH RETURN FOR SELECT name, job, CAST(salary AS DOUBLE) FROM staff WHERE salary < medianSalary ORDER BY SALARY DESC; DECLARE CONTINUE HANDLER FOR NOT FOUND SET medianSalary = 6666; -- initialize OUT parameter SET medianSalary = 0; SELECT COUNT(*) INTO v_numRecords FROM STAFF; OPEN c1; WHILE v_counter < (v_numRecords / 2 + 1) DO FETCH c1 INTO medianSalary; SET v_counter = v_counter + 1; END WHILE; CLOSE c1; -- return 1st result set, do not CLOSE cursor OPEN c2; -- return 2nd result set, do not CLOSE cursor OPEN c3; END @ |
