SQL SERVER数据库快速转ORACLE实战

 

操作步骤如下，供参考。

 数据库转型工作涉及的工作事项分析：表，表数据，索引，外键约束，字段默认值。

存储过程、函数、触发器、视图等由于语法存在差异，只能自行改写处理。

 

(一)在MS SQL SERVER服务器端的准备工作。
1).创建关于表、视图、主键、索引、字段字典、默认值约束的对象视图。以方便下一步使用。

USE  EJ_ZSZQ   ---USE   [数据库名]
GO

--.1不含有TEXT类型、BIGINT类型字段的表视图
CREATE VIEW V_MYTABLE AS
 Select UPPER(NAME) AS NAME from sysobjects A
 where xtype = 'U' AND NAME<>'dtproperties'
  AND NOT EXISTS(SELECT TOP 1 B.NAME FROM SYSCOLUMNS B,SYSTYPES C WHERE B.XTYPE=C.XTYPE AND B.ID=A.ID
   AND (C.NAME = 'text' OR C.NAME='bigint'))
GO

--2.含有TEXT类型、BIGINT类型字段的表视图
CREATE VIEW V_MYTABLE2 AS
 Select UPPER(NAME) AS NAME from sysobjects A
 where xtype = 'U' AND NAME<>'dtproperties'
  AND EXISTS(SELECT TOP 1 B.NAME FROM SYSCOLUMNS B,SYSTYPES C WHERE B.XTYPE=C.XTYPE AND B.ID=A.ID
   AND (C.NAME = 'text' OR C.NAME='bigint'))
GO

--3.所有主键视图，构建主键时使用
CREATE VIEW V_MYPK AS
SELECT A.parent_obj AS TABLEID,
       UPPER(E.NAME) AS TABLENAME,
       UPPER(A.NAME) AS INDEXNAME,
       UPPER(D.NAME) AS COLNAME,
       C.KEYNO AS COLNO,
       (SELECT TOP 1 KEYNO
          FROM sysindexkeys
         WHERE ID = B.ID
           AND INDID = B.INDID
         ORDER BY KEYNO DESC) AS KEYCNT
  FROM sysobjects   A,
       sysindexes   B,
       sysindexkeys C,
       syscolumns   D,
       sysobjects   E
 WHERE (A.xtype = 'PK')
   AND (A.parent_obj = B.ID AND A.NAME = B.NAME)
   AND (B.ID = C.ID AND B.INDID = C.INDID)
   AND (C.ID = D.ID AND C.COLID = D.COLID)
   AND (A.parent_obj = E.ID AND E.XTYPE = 'U' AND E.NAME <> 'dtproperties')
GO
 --4.所有索引名称及索引字段,不包含主键。构建索引时使用

CREATE VIEW V_MYINDEX AS
SELECT X.*, Y.FIELDCNT
  FROM (SELECT A.id as TABLEID,
               object_name(A.id) as TABLENAME,
               A.name AS INDNAME,
               B.INDID,
               C.COLID,
               C.NAME AS COLNAME,INDEXPROPERTY(A.id,A.name,'IsUnique') as ISUNIQUE
          FROM sysindexes A, sysindexkeys B, syscolumns C, sysobjects D
         where (A.indid > 0 and A.indid < 255 and (A.status &64) = 0)
           AND (A.ID = B.ID AND A.INDID = B.INDID)
           AND (B.ID = C.ID AND B.COLID = C.COLID)
           AND (C.ID = D.ID AND D.XTYPE = 'U' AND D.PARENT_OBJ = 0 AND
               D.NAME <> 'dtproperties')
           AND NOT EXISTS (SELECT 1
                  FROM sysobjects
                 WHERE XTYPE = 'PK'
                   AND PARENT_OBJ > 0
                   AND NAME = A.NAME)) X,
       (SELECT ID, INDID, MAX(KEYNO) AS FIELDCNT
          FROM sysindexkeys
         GROUP BY ID, INDID) Y
 WHERE X.tableid = Y.ID
   AND X.INDID = Y.INDID

GO

 --5字段默认值约束视图
CREATE VIEW V_MYVALUE AS
select   OBJECT_NAME(parent_obj) AS TABLENAME,C.COLID,D.NAME AS COLNAME,E.NAME AS DATATYPE, b.TEXT,a.XTYPE
  from sysobjects a , syscomments B, sysconstraints C ,SYSCOLUMNS D ,systypes E
 where (a.xtype = 'D' AND OBJECTPROPERTY(a.id, N'IsMSShipped') = 0)
   AND (A.id = B.id)
   AND (A.ID=C.CONSTID AND A.parent_obj=C.ID AND C.status  = 2069)
   AND (C.ID=D.ID AND C.COLID=D.COLID)
   AND (D.XTYPE=E.XTYPE)
   --and a.parent_obj = object_id('表名')
 GO

--6表字段字典视图
CREATE VIEW V_MYFIELD AS
SELECT B.NAME AS TABLENAME, A.NAME AS COLNAME,A.COLID,C.NAME AS DATATYPE
  FROM  syscolumns A, SYSOBJECTS B ,SYSTYPES C
 WHERE (A.ID=B.ID AND B.XTYPE='U' AND B.NAME <> 'dtproperties')
  AND (A.XTYPE=C.XTYPE )
GO
 

 

2).创建两个SQL SERVER函数。函数GETSELECTSQL和函数GETORASQL，函数用于输出包含有bigint和text类型字段的表的创建视图的脚本。

USE EJ_ZSZQ
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE   FUNCTION  [dbo].[GETSELECTSQL](@TABLENAME VARCHAR(30)) RETURNS VARCHAR(2000)
AS
BEGIN
  DECLARE @fieldlist VARCHAR(2000)
  DECLARE @FIELDNAME VARCHAR(100)
  DECLARE @DATATYPE VARCHAR(30)
  DECLARE field_cursor CURSOR FOR SELECT COLNAME,DATATYPE  FROM V_MYFIELD where TABLENAME = @TABLENAME ORDER BY COLID
  SET @fieldlist = ''
  OPEN field_cursor

  FETCH NEXT FROM field_cursor INTO @FIELDNAME,@DATATYPE
  while (@@FETCH_STATUS = 0)
    begin  
    IF @DATATYPE = 'bigint'
      begin
      SET @FIELDNAME = 'CAST('+@FIELDNAME +' AS NUMERIC(19)) AS ' + @FIELDNAME
      end
    if @DATATYPE = 'text'
      begin
      SET @FIELDNAME = 'CAST('+@FIELDNAME +' AS VARCHAR(4000)) AS ' + @FIELDNAME
      end
    IF @fieldlist = ''
      BEGIN