oracletextdb2,从Oracle到DB2（一）

在实际的软件项目的开发过程中&＃xff0c;特别是在企业的应用系统集成(EAI)项目中广大开发人员经常遇到不同关系型数据库之间的数据移植问题。笔者根据自己在工作中的不同数据库数据移植的经验经过通用化的java应用程序,演示把数据从Oracle数据库移植到DB2数据库中。

当然也可以应用其它支持JDBC和ODBC的关系型数据库。

本议题分为二部分&＃xff1a;

一、Oracle和DB2数据库对象的异同

二、把数据从Oracle移植到DB2的Java应用程序的详细分析.详见

从Oracle 到DB2(二)

首先让大家快速的熟悉一下Oracle和DB2各个方面的异同

上面的表格比较全面的对比了二者之间的区别&＃xff0c;相信大家应该对Oracle 和DB2在整体上有了比较全面的理解&＃xff0c;

好&＃xff0c;下面就让大家对二个数据库的数据类型作详细的比较。

下面我通过一些具体的实例来详细演示DB2和Oracle的区别&＃xff1a;

一、日期

Oracle的日期用法&＃xff1a;

Create table date_demo

( day_demo varchar2(25),

actualDate DATE,

detaildate DATE

);

插入数据

insert into date_demo values (&＃39;new years&＃39;,to_date(&＃39;01-02-2002&＃39;,&＃39;DD-MON-YYYY&＃39;),

to_date(&＃39;01-02-2002&＃39;,&＃39;DD-MON-YYYY&＃39;),

...;

查看结果&＃xff1a;

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的日期用法&＃xff1a;

Create table date_demo

( day_demo char(25),

actualDate DATE,

detaildate DATE

);

插入数据

insert into date_demo values (&＃39;new years&＃39;,(&＃39;01-02-2002&＃39;),

to_date(&＃39;01-02-2002&＃39;),

......;

查看结果&＃xff1a;

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的序列用法&＃xff1a;

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,&＃39;sales&＃39;,&＃39;smith&＃39;,55,&＃39;downtown&＃39;),

(dept_seq.nextval,&＃39;marketing&＃39;,&＃39;wong&＃39;,12,&＃39;midtown&＃39;),

(dept_seq.nextval,&＃39;accounting&＃39;,&＃39;fisher&＃39;,300,&＃39;uptown&＃39;);

select * from dept;

DB2的序列用法&＃xff1a;

首先建表&＃xff1a;

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));

接下来插入数据

insert into dept values

(default,&＃39;sales&＃39;,&＃39;smith,50,&＃39;downtown&＃39;),

(default,&＃39;marketing&＃39;,&＃39;wong&＃39;,23,&＃39;midtown&＃39;),

(default,&＃39;accounting&＃39;,&＃39;fisher&＃39;200,&＃39;uptown&＃39;);

查询结果:

select * from dept;

结果和Oracle 的一样。

三、截断大的数据表的内容

Oracle在截断大的数据表提供了一个函数truncate&＃xff0c;通过语句truncate table tab_name来清空表中的内容并重组表的空间&＃xff0c;truncate 是DDL语句不能回滚。

DB2不支持truncate语句&＃xff0c;但提供另外二种方法来解决上面的问题。

(1) 在建表时加选项not logged initially &＃xff0c;当清空表时通过alter table [name] activate not logged initially with empty table

(2)首先在操作系统上建一个空文件empty.del&＃xff0c;接着通过import命令import from empty.del of del replace into [table_name]来清空表中的数据并重组表空间。

以上是我在使用Oracle 和DB2数据库的过程中总结的不通点&＃xff0c;更多的不通可以查阅相关的手册。

通过上面的介绍相信大家对Oracle和DB2的区别会有一定程度的了解&＃xff0c;下一篇将在这一篇的基础上介绍如何把Oracle中的数据移植到DB2数据库中来。

-- 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&＃64; -vf "

-- where 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

ORDER BY SALARY DESC;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET medianSalary &＃61; 6666;

-- initialize OUT parameter

SET medianSalary &＃61; 0;

SELECT COUNT(*) INTO v_numRecords FROM STAFF;

OPEN c1;

WHILE v_counter <(v_numRecords / 2 &＃43; 1) DO

FETCH c1 INTO medianSalary;

SET v_counter &＃61; v_counter &＃43; 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 &＃64;