由SQL SERVER 2008向ORACLE 11G迁移过程记录之一-表

最近在将项目由SQL SERVER 2008向ORACLE 11G迁移，以下简述迁移数据表过程。

1. 产生数据库的脚本。在脚本生成向导中选择脚本选项中，设置ANSI填充FALSE、编写USE DATABASE脚本FALSE、编写扩展属性脚本FALSE、架构限定对象名称FALSE，为服务器版本编写脚本选择SQL SERVER 2008
2. 生成到新文件或到新窗口后保存到文件，然后使用支持正则表达式的文本编辑器打开（建议使用EmEditor，否则可能下面某些正则表达式会稍有不同），使用查找替换功能依次如下操作（以下如未说明则都为使用正则表达式查找）：
   1. 由于ORACLE的名称限定符是引号而非方括号，因此要将[和]替换成空字符串以去除。假如有使用关键字作为名称的，需要再单独处理了
      查找：\[|\]
      替换为：空字符串
   2. 去除SET ANSI_NULLS ON和SET QUOTED_IDENTIFIER ON，因为ORACLE中不存在这些属性或不是这个名称，使用正则可以很方便的完成这个工作：
      查找：SET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n
      替换为：空字符串。
   3. 替换所有的GO为;同样使用正则表达式来做：
      查找：\nGO\n
      替换为：;\n
   4. 注释掉IDENTITY(1,1)，因为ORACLE中不支持这个关键字，相应的，为了实现自增数字的功能，需要建立一个SEQUENCE，然后在表上建立一个INSERT触发器，触发时往对应字段赋值为代替。后面我会写一个PYTHON脚本，根据这些注释生成所有的SEQUENCE和触发器脚本。
      查找：(IDENTITY\(\d+,\d+\))
      替换为：/*\1*/
   5. 去除所有的“ ON PRIMARY”，“ TEXTIMAGE_ON PRIMARY”
      查找：TEXTIMAGE_ON PRIMARY|ON PRIMARY
      替换为：空字符串
   6. 去除所有的“ 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[^\)]+\))
      替换为：空字符串
   7. 主键语句中，去除ASC和DESC，注意别误伤视图语句中的相应字符串。
      查找：( ASC| DESC)  注意字符前的空格，以免误伤标识符中的对应字符
      替换为：空字符串
   8. 替换字段类型。因为SQL SERVER中的类型和ORACLE中大部分都不一样，因此基本上都需要替换掉。具体替换时建议按如下步骤(注意如果在标识符中没有关键字，那么在替换时勾选全字匹配即可，如果可能存在使用关键字当标识符的时候，那么，查找时使用正则表达式，在查找字符串前加上[ ]^1，表示查找字符串前存在并只存在1个空格，如原类型后必然有括号的，例如char/varchar之类，那么在查找字符串后加上\(，必然没有括号的加上[ ]^1，然后在替换字符串中根据前后是否有空格或括号，要依样加上，如int替换成number(10,0)，使用正则时，查找字符串内填[ ]^1int[ ]^1，替换字符串内填 NUMBER(10,0) ，注意前后的空格，再如varchar替换成varchar2时，查找字符串填[ ]^1varhcar\(，替换字符串写 varchar2(，注意前面的空格和后面的括号。)：
      1. varchar替换成varchar2
      2. nvarchar也是替换成varchar2，但是长度要乘2，所以这个要单独每种长度都独立替换
      3. 同样要乘2的还包括所有n开头的类型
      4. int替换成NUMBER(10,0)
      5. NUMERIC和DECIMAL替换成NUMBER
      6. smalldatetime和datetime替换成DATE
      7. text替换成CLOB
      8. image替换成BLOB
      9. 其它类型对应表如下：

         	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

   9. 替换默认值规则：
      1. 替换默认日期：
         查找（普通非正则）：(getdate())
         替换为：SYSDATE
      2. 数字默认值语法替换，把数字外的括号去除：
         查找：( DEFAULT )\((\d+)\)
         替换为：\1\2
      3. 替换默认值语法：
         查找：ALTER TABLE (\w+) ADD\s+CONSTRAINT.+DEFAULT (.+) FOR (\w+);
         替换为：alter table \1 modify \3 default \2;
   10. 去除外键语句中一些不支持的选项（在我的数据库中碰到的。应该不完整），以及，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
       替换为：空字符串
3. 转换的过程中，应注意以下问题：
   1. 标识符长度不能超过30（包括表、约束、视图等等所有的标识标）
   2. varchar2的长度不能超过4000
4. 好的，保存修改后的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()

5. 最后，依次执行两个SQL脚本文件。
6. 如果有错，那么真是太糟糕啦。根据错误提示，排错吧。 ：（