SQL Server向Oracle迁移的解决方案

1．  表结构调整

a. 表结构必须符合oracle的要求，即不能有自增长字段、不能有bit型字段，日期字段只有一种date。自增字段建议的解决方法是：在oracle中建立一个“序列”，用序列的nextval值代替。虽然oracle中数据类型比较多，但建议只使用:varchar2、number、date三种数据类型，方便处理。

b. 所有数据库的对象建立都写成SQL语句，不要用手工操作的方法。

2．  数据导出导入

a. 数据的导出导入的方式有多种，（1）写一个程序连接两个数据库对导，（2）可以先导出数据到数据文件中，再通过oracle的sql*loader导入，（3）通过SQL Server的DTS，（4）通过第三方工具导，如PL/SQL Developer。

b. 注意oracle的字符集问题，建议使用: NLS_LANG = AMERICAN_AMERICA.ZHS16GBK。

c. 注意Oracle日期格式问题，建议采用：NLS_DATE_FORMAT = “YYYY-MM-DD HH24:MI:SS”

d. 字符集和日期格式的设置，在windows中是注册表，在Linux中是环境变量。

3．  Sql语句的异同

a. oracle中没有top的语法，解决方案见附录1。

b. oracle中没有left/right join 语句，用表的左右连接代替。

c. oracle中没有case语法，用decode解决，见附录2。

d. oracle中没有NEWID函数，代替的函数为：SYS_GUID() 函数，返回数据类型为char，长度为32，长度不同（sql server中为36）。

e. oracle中null的处理方法，见附录3。

f. dual伪列的应用，见附录4。

g. oracle中数据同步可以通过job来实现，见附录5。

h.

 

附录1：
如何正确利用Rownum来限制查询所返回的行数？

软件环境：

1、Windows NT4.0+ORACLE 8.0.4

2、ORACLE安装路径为：C:/ORANT

含义解释：

1、rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，

　 依此类推，这个伪字段可以用于限制查询返回的总行数。

2、rownum不能以任何基表的名称作为前缀。

使用方法：

现有一个商品销售表sale，表结构为：

month　　　 char(6)　　　　　 --月份

sell　　　　number(10,2)　　　--月销售金额

create table sale (month char(6),sell number);

insert into sale values('200001',1000);

insert into sale values('200002',1100);

insert into sale values('200003',1200);

insert into sale values('200004',1300);

insert into sale values('200005',1400);

insert into sale values('200006',1500);

insert into sale values('200007',1600);

insert into sale values('200101',1100);

insert into sale values('200202',1200);

insert into sale values('200301',1300);

insert into sale values('200008',1000);

commit;

SQL> select rownum,month,sell from sale where rownum=1;（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标）

ROWNUM MONTH SELL

--------- ------ ---------

1 200001  1000

SQL> select rownum,month,sell from sale where rownum=2;（1以上都查不到记录）

没有查到记录

SQL> select rownum,month,sell from sale where rownum>5;

（由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件不成立，查不到记录）

 

没有查到记录

只返回前3条纪录

SQL> select rownum,month,sell from sale where rownum<4;

ROWNUM MONTH SELL

--------- ------ ---------

1 200001  1000

2 200002  1100

3 200003  1200

 

如何用rownum实现大于、小于逻辑？（返回rownum在4—10之间的数据）（minus操作，速度会受影响）

SQL> select rownum,month,sell from sale where rownum<10

  2  minus

  3  select rownum,month,sell from sale where rownum<5;

ROWNUM MONTH SELL

--------- ------ ---------

5 200005  1400

6 200006  1500

7 200007  1600

8 200101  1100

9 200202  1200

想按日期排序，并且用rownum标出正确序号（有小到大）

SQL> select rownum,month,sell from sale order by month;

ROWNUM MONTH SELL

--------- ------ ---------

1 200001  1000

2 200002  1100

3 200003  1200

4 200004  1300

5 200005  1400

6 200006  1500

7 200007  1600

11 200008  1000

8 200101  1100

9 200202  1200

10 200301  1300

查询到11记录.

可以发现，rownum并没有实现我们的意图，系统是按照记录入库时的顺序给记录排的号，rowid也是顺序分配的

SQL> select rowid,rownum,month,sell from sale order by rowid;

ROWID ROWNUM MONTH SELL

------------------ --------- ------ ---------

000000E4.0000.0002 1 200001  1000

000000E4.0001.0002 2 200002  1100

000000E4.0002.0002 3 200003  1200

000000E4.0003.0002 4 200004  1300

000000E4.0004.0002 5 200005  1400

000000E4.0005.0002 6 200006  1500

000000E4.0006.0002 7 200007  1600

000000E4.0007.0002 8 200101  1100

000000E4.0008.0002 9 200202  1200

000000E4.0009.000210 200301  1300

000000E4.000A.000211 200008  1000

查询到11记录.

正确用法，使用子查询

SQL> select rownum,month,sell from (select month,sell from sale group by month,sell) where rownum<13;

ROWNUM MONTH SELL

--------- ------ ---------

1 200001  1000

2 200002  1100

3 200003  1200

4 200004  1300

5 200005  1400

6 200006  1500

7 200007  1600

8 200008  1000

9 200101  1100

10 200202  1200

11 200301  1300

按销售金额排序，并且用rownum标出正确序号（有小到大）

SQL> select rownum,month,sell from (select sell,month from sale group by sell,month) where rownum<13;

ROWNUM MONTH SELL

--------- ------ ---------

1 200001  1000

2 200008  1000

3 200002  1100

4 200101  1100

5 200003  1200

6 200202  1200

7 200004  1300

8 200301  1300

9 200005  1400

10 200006  1500

11 200007  1600

查询到11记录.

利用以上方法，如在打印报表时，想在查出的数据中自动加上行号，就可以利用rownum。

返回第5—9条纪录，按月份排序

SQL> select * from (select rownum row_id ,month,sell

  2  from (select month,sell from sale group by month,sell))

  3  where row_id between 5 and 9;

ROW_ID MONTHSELL

---------- ------ ----------

5 200005 1400

6 200006 1500

7 200007 1600

8 200008 1000

9 200101 1100

附录2
Decode()函数使用技巧

软件环境：

1、Windows NT4.0+ORACLE 8.0.4

2、ORACLE安装路径为：C:/ORANT

含义解释：

DECODE(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

该函数的含义如下：

IF 条件=值1 THEN

　　　　RETURN(翻译值1)

ELSIF 条件=值2 THEN

　　　　RETURN(翻译值2)

　　　　......

ELSIF 条件=值n THEN

　　　　RETURN(翻译值n)

ELSE

　　　　RETURN(缺省值)

END IF

使用方法：

1、比较大小

select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

例如：

变量1=10，变量2=20

则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

 

2、表、视图结构转化

现有一个商品销售表sale，表结构为：

month　　　 char(6)　　　　　 --月份

sell　　　　number(10,2)　　　--月销售金额

现有数据为：

200001　　1000

200002　　1100

200003　　1200

200004　　1300

200005　　1400

200006　　1500

200007　　1600

200101　　1100

200202　　1200

200301　　1300

想要转化为以下结构的数据：

year　　　char(4)　　　　　 --年份

month1　　number(10,2)　　　--1月销售金额

month2　　number(10,2)　　　--2月销售金额

month3　　number(10,2)　　　--3月销售金额

month4　　number(10,2)　　　--4月销售金额

month5　　number(10,2)　　　--5月销售金额

month6　　number(10,2)　　　--6月销售金额

month7　　number(10,2)　　　--7月销售金额

month8　　number(10,2)　　　--8月销售金额

month9　　number(10,2)　　　--9月销售金额

month10　　number(10,2)　　　--10月销售金额

month11　　number(10,2)　　　--11月销售金额

month12　　number(10,2)　　　--12月销售金额

结构转化的SQL语句为：

create or replace view

v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)

as

　　　　select

　　　　substrb(month,1,4),

　　　　sum(decode(substrb(month,5,2),'01',sell,0)),

　　　　sum(decode(substrb(month,5,2),'02',sell,0)),

　　　　sum(decode(substrb(month,5,2),'03',sell,0)),

　　　　sum(decode(substrb(month,5,2),'04',sell,0)),

　　　　sum(decode(substrb(month,5,2),'05',sell,0)),

　　　　sum(decode(substrb(month,5,2),'06',sell,0)),

　　　　sum(decode(substrb(month,5,2),'07',sell,0)),

　　　　sum(decode(substrb(month,5,2),'08',sell,0)),

　　　　sum(decode(substrb(month,5,2),'09',sell,0)),

　　　　sum(decode(substrb(month,5,2),'10',sell,0)),

　　　　sum(decode(substrb(month,5,2),'11',sell,0)),

　　　　sum(decode(substrb(month,5,2),'12',sell,0))

　　　　from sale

　　　　group by substrb(month,1,4);

附录3
NULL 使用详解

软件环境：

1、Windows NT4.0+ORACLE 8.0.4

2、ORACLE安装路径为：C:/ORANT

含义解释：

问：什么是NULL？

答：在我们不知道具体有什么数据的时候，也即未知，可以用NULL，我们称它为空，ORACLE中，含有空值的表列长度为零。

ORACLE允许任何一种数据类型的字段为空，除了以下两种情况：

1、主键字段（primary key），

2、定义时已经加了NOT NULL限制条件的字段

说明：

1、等价于没有任何值、是未知数。

2、NULL与0、空字符串、空格都不同。

3、对空值做加、减、乘、除等运算操作，结果仍为空。

4、NULL的处理使用NVL函数。

5、比较时使用关键字用“is null”和“is not null”。

6、空值不能被索引，所以查询时有些符合条件的数据可能查不出来，count(*)中，用nvl(列名,0)处理后再查。

7、排序时比其他数据都大（索引默认是降序排列，小→大），所以NULL值总是排在最后。

使用方法：

SQL> select 1 from dual where null=null;

没有查到记录

SQL> select 1 from dual where null='';

没有查到记录

SQL> select 1 from dual where ''='';

没有查到记录

SQL> select 1 from dual where null is null;

1

---------

1

SQL> select 1 from dual where nvl(null,0)=nvl(null,0);

1

---------

1

对空值做加、减、乘、除等运算操作，结果仍为空。

SQL> select 1+null from dual;

SQL> select 1-null from dual;

SQL> select 1*null from dual;

SQL> select 1/null from dual;

 

查询到一个记录.

注：这个记录就是SQL语句中的那个null

设置某些列为空值

update table1 set 列1=NULL where 列1 is not null;

 

现有一个商品销售表sale，表结构为：

month　　　 char(6)　　　　　 --月份

sell　　　　number(10,2)　　　--月销售金额

create table sale (month char(6),sell number);

insert into sale values('200001',1000);

insert into sale values('200002',1100);

insert into sale values('200003',1200);

insert into sale values('200004',1300);

insert into sale values('200005',1400);

insert into sale values('200006',1500);

insert into sale values('200007',1600);

insert into sale values('200101',1100);

insert into sale values('200202',1200);

insert into sale values('200301',1300);

insert into sale values('200008',1000);

insert into sale(month) values('200009');（注意：这条记录的sell值为空）

commit;

共输入12条记录

SQL> select * from sale where sell like '%';

MONTH SELL

------ ---------

200001  1000

200002  1100

200003  1200

200004  1300

200005  1400

200006  1500

200007  1600

200101  1100

200202  1200

200301  1300

200008  1000

查询到11记录.

结果说明：

查询结果说明此SQL语句查询不出列值为NULL的字段

此时需对字段为NULL的情况另外处理。

SQL> select * from sale where sell like '%' or sell is null;

SQL> select * from sale where nvl(sell,0) like '%';

MONTH SELL

------ ---------

200001  1000

200002  1100

200003  1200

200004  1300

200005  1400

200006  1500

200007  1600

200101  1100

200202  1200

200301  1300

200008  1000

200009

查询到12记录.

Oracle的空值就是这么的用法，我们最好熟悉它的约定，以防查出的结果不正确。

 

附录4
Dual伪列

软件环境：

1、操作系统：Windows 2000 Server

2、数 据 库：Oracle 8i R2 (8.1.6) for NT 企业版

3、安装路径：C:/ORACLE

含义解释：

Dual 是 Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select中。

使用方法：

查看当前连接用户

SQL> select user from dual;

USER

------------------------------

SYSTEM

 

查看当前日期、时间

SQL> select sysdate from dual;

SYSDATE

----------

18-3月 -01

 

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2001-03-18 22:37:56

 

当作计算器用

SQL> select 1+2 from dual;

1+2

----------

3

 

查看序列值

SQL> create sequence aaa increment by 1 start with 1;

SQL> select aaa.nextval from dual;

SQL> select aaa.nextval from dual;

NEXTVAL

----------

1
SQL> select aaa.currval from dual;
CURRVAL