由SQL SERVER 2008向ORACLE 11G迁移过程记录之一-表
最近在将项目由SQL SERVER 2008向ORACLE 11G迁移，以下简述迁移数据表过程。
- 产生数据库的脚本。在脚本生成向导中选择脚本选项中，设置ANSI填充FALSE、编写USE DATABASE脚本FALSE、编写扩展属性脚本FALSE、架构限定对象名称FALSE，为服务器版本编写脚本选择SQL SERVER 2008
- 生成到新文件或到新窗口后保存到文件，然后使用支持正则表达式的文本编辑器打开（建议使用EmEditor，否则可能下面某些正则表达式会稍有不同），使用查找替换功能依次如下操作（以下如未说明则都为使用正则表达式查找）：
- 由于ORACLE的名称限定符是引号而非方括号，因此要将[和]替换成空字符串以去除。假如有使用关键字作为名称的，需要再单独处理了
查找：\[|\]
替换为：空字符串
- 去除SET ANSI_NULLS ON和SET QUOTED_IDENTIFIER ON，因为ORACLE中不存在这些属性或不是这个名称，使用正则可以很方便的完成这个工作：
查找：SET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n
替换为：空字符串。
- 替换所有的GO为;同样使用正则表达式来做：
查找：\nGO\n
替换为：;\n
- 注释掉IDENTITY(1,1)，因为ORACLE中不支持这个关键字，相应的，为了实现自增数字的功能，需要建立一个SEQUENCE，然后在表上建立一个INSERT触发器，触发时往对应字段赋值为代替。后面我会写一个PYTHON脚本，根据这些注释生成所有的SEQUENCE和触发器脚本。
查找：(IDENTITY\(\d+,\d+\))
替换为：/*\1*/
- 去除所有的“ ON PRIMARY”，“ TEXTIMAGE_ON PRIMARY”
查找：TEXTIMAGE_ON PRIMARY|ON PRIMARY
替换为：空字符串
- 去除所有的“ CLUSTERED ”、“ NONCLUSTERED ”、“WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)”
查找：((NON)*CLUSTERED)|(WITH \(PAD[^\)]+\))
替换为：空字符串
- 主键语句中，去除ASC和DESC，注意别误伤视图语句中的相应字符串。
查找：( ASC| DESC) 注意字符前的空格，以免误伤标识符中的对应字符
替换为：空字符串
- 替换字段类型。因为SQL SERVER中的类型和ORACLE中大部分都不一样，因此基本上都需要替换掉。具体替换时建议按如下步骤(注意如果在标识符中没有关键字，那么在替换时勾选全字匹配即可，如果可能存在使用关键字当标识符的时候，那么，查找时使用正则表达式，在查找字符串前加上[ ]^1，表示查找字符串前存在并只存在1个空格，如原类型后必然有括号的，例如char/varchar之类，那么在查找字符串后加上\(，必然没有括号的加上[ ]^1，然后在替换字符串中根据前后是否有空格或括号，要依样加上，如int替换成number(10,0)，使用正则时，查找字符串内填[ ]^1int[ ]^1，替换字符串内填 NUMBER(10,0) ，注意前后的空格，再如varchar替换成varchar2时，查找字符串填[ ]^1varhcar\(，替换字符串写 varchar2(，注意前面的空格和后面的括号。)：
- varchar替换成varchar2
- nvarchar也是替换成varchar2，但是长度要乘2，所以这个要单独每种长度都独立替换
- 同样要乘2的还包括所有n开头的类型
- int替换成NUMBER(10,0)
- NUMERIC和DECIMAL替换成NUMBER
- smalldatetime和datetime替换成DATE
- text替换成CLOB
- image替换成BLOB
- 其它类型对应表如下：
SQL SERVER ORACLE 数字类型 DECIMAL[(P[,S])] NUMBER[(P[,S])] NUMERIC[(P[,S])] NUMBER[(P[,S])] FLOAT[(N)] NUMBER[(N)] FLOAT[(N)] NUMBER[(N)] INT NUMBER SMALLINT NUMBER TINYINT NUMBER MONEY NUMBER[19,4] SMALLMONEY NUMBER[19,4] 字符类型 CHAR[(N)] CHAR[(N)] VARCHAR[(N)] VARCHAR2[(N)] NCHAR[(N)] CHAR[(N*2)] NVARCHAR[(N)] VARCHAR2[(N*2)] 日期时间类型 DATETIME DATE SMALLDATETIME DATE 其它 TEXT CLOB IMAGE BLOB BIT NUMBER(1) UNIQUEIDENTIFIER LONG RAW
- 替换默认值规则：
- 替换默认日期：
查找（普通非正则）：(getdate())
替换为：SYSDATE
- 数字默认值语法替换，把数字外的括号去除：
查找：( DEFAULT )\((\d+)\)
替换为：\1\2
- 替换默认值语法：
查找：ALTER TABLE (\w+) ADD\s+CONSTRAINT.+DEFAULT (.+) FOR (\w+);
替换为：alter table \1 modify \3 default \2;
- 替换默认日期：
- 去除外键语句中一些不支持的选项（在我的数据库中碰到的。应该不完整），以及，SQL SERVER生成的约束有时会先使用WITH NOCHECK创建约束后紧跟着将之启用，虽然可以用ORACLE对应的DISALBE和ENABLE选项替代，但感觉没有什么用，于是干脆把WITH NOCHECK去掉，同时却掉紧跟着的启用约束语句。具体如下：
查找：ON UPDATE CASCADE\n|\nNOT FOR REPLICATION|ALTER TABLE.+CHECK CONSTRAINT.+\n|WITH NOCHECK|WITH CHECK
替换为：空字符串
- 由于ORACLE的名称限定符是引号而非方括号，因此要将[和]替换成空字符串以去除。假如有使用关键字作为名称的，需要再单独处理了
- 转换的过程中，应注意以下问题：
- 标识符长度不能超过30（包括表、约束、视图等等所有的标识标）
- varchar2的长度不能超过4000
- 好的，保存修改后的SQL脚本文件，现在可以给ORACLE执行了吗？等等，还有。。我们要生成IDENTITY的替代脚本！下面是PYTHON脚本代码，把它保存为文本文件，例如c:\convert.py中，然后，在CMD中执行
c:\>%path_to_python.exe% c:\convert.py [sql原文件路径] [生成的触发器和seqence的sql文件保存路径]
不过，这个脚本只支持一个表中存在一个IDENTITY字段的情况。：（
#coding=utf-8
import sys
import re
#sys.setdefaultencoding('utf8')
orgfile=sys.argv[1]
newfile=sys.argv[2]
f=open(orgfile,'r')
fidentity=open(newfile,"w")
line=f.read()
p=re.compile('CREATE TABLE ([^\(\n]+)[^;]+?(\S+) NUMBER\(10,0\)/\* IDENTITY\((\d+),(\d+)\)\*/',re.I|re.S|re.M)
mat=p.findall(line)
for item in mat:
seqname="SEQ_"+item[0]
triggername="TRI_"+item[0]+"_"+item[1]
if len(seqname)>30:
seqname=seqname[0:29]
if len(triggername)>30:
triggername=triggername[0:29]
createseq="create sequence "+seqname+" increment by "+item[3]+" start with "+item[2]+";\n"
createseq=createseq+"CREATE OR REPLACE TRIGGER " + triggername +"\n\
BEFORE INSERT ON "+item[0]+"\n\
FOR EACH ROW\n\
WHEN (new."+item[1]+" IS NULL)\n\
BEGIN\n\
Select "+seqname+".NEXTVAL INTO :NEW."+item[1]+"\n\
FROM DUAL;\n\
END;\n/\n"
fidentity.write(createseq)
fidentity.flush()
fidentity.close()
f.close()
- 最后，依次执行两个SQL脚本文件。
- 如果有错，那么真是太糟糕啦。根据错误提示，排错吧。 ：（
