SQLSERVER数据库快速转ORACLE实战

操作步骤如下&＃xff0c;供参考。

数据库转型工作涉及的工作事项分析&＃xff1a;表&＃xff0c;表数据&＃xff0c;索引&＃xff0c;外键约束&＃xff0c;字段默认值。

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

(一)在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 &＃61; &＃39;U&＃39; AND NAME<>&＃39;dtproperties&＃39;
AND NOT EXISTS(SELECT TOP 1 B.NAME FROM SYSCOLUMNS B,SYSTYPES C WHERE B.XTYPE&＃61;C.XTYPE AND B.ID&＃61;A.ID
AND (C.NAME &＃61; &＃39;text&＃39; OR C.NAME&＃61;&＃39;bigint&＃39;))
GO

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

--3.所有主键视图&＃xff0c;构建主键时使用
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 &＃61; B.ID
AND INDID &＃61; B.INDID
ORDER BY KEYNO DESC) AS KEYCNT
FROM sysobjects A,
sysindexes B,
sysindexkeys C,
syscolumns D,
sysobjects E
WHERE (A.xtype &＃61; &＃39;PK&＃39;)
AND (A.parent_obj &＃61; B.ID AND A.NAME &＃61; B.NAME)
AND (B.ID &＃61; C.ID AND B.INDID &＃61; C.INDID)
AND (C.ID &＃61; D.ID AND C.COLID &＃61; D.COLID)
AND (A.parent_obj &＃61; E.ID AND E.XTYPE &＃61; &＃39;U&＃39; AND E.NAME <> &＃39;dtproperties&＃39;)
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,&＃39;IsUnique&＃39;) as ISUNIQUE
FROM sysindexes A, sysindexkeys B, syscolumns C, sysobjects D
where (A.indid > 0 and A.indid <255 and (A.status &64) &＃61; 0)
AND (A.ID &＃61; B.ID AND A.INDID &＃61; B.INDID)
AND (B.ID &＃61; C.ID AND B.COLID &＃61; C.COLID)
AND (C.ID &＃61; D.ID AND D.XTYPE &＃61; &＃39;U&＃39; AND D.PARENT_OBJ &＃61; 0 AND
D.NAME <> &＃39;dtproperties&＃39;)
AND NOT EXISTS (SELECT 1
FROM sysobjects
WHERE XTYPE &＃61; &＃39;PK&＃39;
AND PARENT_OBJ > 0
AND NAME &＃61; A.NAME)) X,
(SELECT ID, INDID, MAX(KEYNO) AS FIELDCNT
FROM sysindexkeys
GROUP BY ID, INDID) Y
WHERE X.tableid &＃61; Y.ID
AND X.INDID &＃61; 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 &＃61; &＃39;D&＃39; AND OBJECTPROPERTY(a.id, N&＃39;IsMSShipped&＃39;) &＃61; 0)
AND (A.id &＃61; B.id)
AND (A.ID&＃61;C.CONSTID AND A.parent_obj&＃61;C.ID AND C.status &＃61; 2069)
AND (C.ID&＃61;D.ID AND C.COLID&＃61;D.COLID)
AND (D.XTYPE&＃61;E.XTYPE)
--and a.parent_obj &＃61; object_id(&＃39;表名&＃39;)
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&＃61;B.ID AND B.XTYPE&＃61;&＃39;U&＃39; AND B.NAME <> &＃39;dtproperties&＃39;)
AND (A.XTYPE&＃61;C.XTYPE )
GO

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

USE EJ_ZSZQ
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GETSELECTSQL](&＃64;TABLENAME VARCHAR(30)) RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE &＃64;fieldlist VARCHAR(2000)
DECLARE &＃64;FIELDNAME VARCHAR(100)
DECLARE &＃64;DATATYPE VARCHAR(30)
DECLARE field_cursor CURSOR FOR SELECT COLNAME,DATATYPE FROM V_MYFIELD where TABLENAME &＃61; &＃64;TABLENAME ORDER BY COLID
SET &＃64;fieldlist &＃61; &＃39;&＃39;
OPEN field_cursor

FETCH NEXT FROM field_cursor INTO &＃64;FIELDNAME,&＃64;DATATYPE
while (&＃64;&＃64;FETCH_STATUS &＃61; 0)
begin
IF &＃64;DATATYPE &＃61; &＃39;bigint&＃39;
begin
SET &＃64;FIELDNAME &＃61; &＃39;CAST(&＃39;&＃43;&＃64;FIELDNAME &＃43;&＃39; AS NUMERIC(19)) AS &＃39; &＃43; &＃64;FIELDNAME
end
if &＃64;DATATYPE &＃61; &＃39;text&＃39;
begin
SET &＃64;FIELDNAME &＃61; &＃39;CAST(&＃39;&＃43;&＃64;FIELDNAME &＃43;&＃39; AS VARCHAR(4000)) AS &＃39; &＃43; &＃64;FIELDNAME
end
IF &＃64;fieldlist &＃61; &＃39;&＃39;
BEGIN
SET &＃64;fieldlist &＃61; &＃64;FIELDNAME
END
ELSE
BEGIN
SET &＃64;fieldlist &＃61; &＃64;fieldlist &＃43; &＃39;,&＃39; &＃43; &＃64;FIELDNAME
END
FETCH NEXT FROM field_cursor INTO &＃64;FIELDNAME,&＃64;DATATYPE
end
SET &＃64;fieldlist &＃61; UPPER(&＃39;CREATE VIEW V_&＃39; &＃43; &＃64;TABLENAME &＃43;&＃39; AS SELECT &＃39; &＃43; &＃64;fieldlist &＃43; &＃39; FROM &＃39; &＃43; &＃64;TABLENAME)
CLOSE field_cursor
DEALLOCATE field_cursor
RETURN &＃64;fieldlist
END

-- &＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
-- Author:
-- Create date:
-- Description:
-- &＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
CREATE FUNCTION [dbo].[GETORASQL](&＃64;TABLENAME VARCHAR(30)) RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE &＃64;fieldlist VARCHAR(2000)
DECLARE &＃64;FIELDNAME VARCHAR(100)
DECLARE &＃64;DATATYPE VARCHAR(30)
DECLARE field_cursor CURSOR FOR SELECT COLNAME,DATATYPE FROM V_MYFIELD where TABLENAME &＃61; &＃64;TABLENAME ORDER BY COLID
SET &＃64;fieldlist &＃61; &＃39;&＃39;
OPEN field_cursor

FETCH NEXT FROM field_cursor INTO &＃64;FIELDNAME,&＃64;DATATYPE
while (&＃64;&＃64;FETCH_STATUS &＃61; 0)
begin
if &＃64;DATATYPE &＃61; &＃39;text&＃39;
begin
SET &＃64;FIELDNAME &＃61; &＃39;TO_CLOB(&＃39;&＃43;&＃64;FIELDNAME&＃43;&＃39;) AS &＃39; &＃43; &＃64;FIELDNAME
end
IF &＃64;fieldlist &＃61; &＃39;&＃39;
BEGIN
SET &＃64;fieldlist &＃61; &＃64;FIELDNAME
END
ELSE
BEGIN
SET &＃64;fieldlist &＃61; &＃64;fieldlist &＃43; &＃39;,&＃39; &＃43; &＃64;FIELDNAME
END
FETCH NEXT FROM field_cursor INTO &＃64;FIELDNAME,&＃64;DATATYPE
end
SET &＃64;fieldlist &＃61; UPPER(&＃39;CREATE TABLE &＃39; &＃43; &＃64;TABLENAME &＃43;&＃39; AS SELECT &＃39; &＃43; &＃64;fieldlist &＃43; &＃39; FROM V_&＃39; &＃43; &＃64;TABLENAME&＃43;&＃39;&＃64;DBSQL;&＃39;)
CLOSE field_cursor
DEALLOCATE field_cursor
RETURN &＃64;fieldlist
END

3.执行下段代码&＃xff0c;快速获得创建视图的脚本。此处只创建含有TEXT、BIGINT类型字段的表的视图。
DECLARE &＃64;tablename VARCHAR(36)
DECLARE TABLE_cursor CURSOR FOR SELECT * FROM V_MYTABLE2

OPEN TABLE_cursor

FETCH NEXT FROM TABLE_cursor INTO &＃64;tablename
PRINT &＃39;-- 请在SQL SERVER服务器创建下列视图:&＃39;
WHILE (&＃64;&＃64;FETCH_STATUS &＃61; 0)
BEGIN
PRINT &＃39;-- V_&＃39;&＃43;&＃64;tablename
PRINT [dbo].GETSELECTSQL(&＃64;tablename)
PRINT &＃39;GO &＃39;
PRINT &＃39;&＃39;
FETCH NEXT FROM TABLE_cursor INTO &＃64;tablename
END

PRINT &＃39;&＃39;
PRINT &＃39;&＃39;
PRINT &＃39;&＃39;
PRINT &＃39;&＃39;

CLOSE TABLE_cursor
OPEN TABLE_cursor
FETCH NEXT FROM TABLE_cursor INTO &＃64;tablename
PRINT &＃39;-- 请在ORACLE服务器中执行下面脚本&＃xff0c;迁移表和数据:&＃39;
WHILE (&＃64;&＃64;FETCH_STATUS &＃61; 0)
BEGIN
PRINT &＃39;-- &＃39;&＃43;&＃64;tablename
PRINT [dbo].GETORASQL(&＃64;tablename)
PRINT &＃39; &＃39;
FETCH NEXT FROM TABLE_cursor INTO &＃64;tablename
END
CLOSE TABLE_cursor
DEALLOCATE TABLE_cursor
GO

把输出的创建视图的代码&＃xff0c;保存到文件“输出的脚本.SQL”中。

4.执行“输出的脚本.SQL ””文件中前半部分 SQL SERVER脚本&＃xff0c;创建视图。

5.在SQL SERVER服务器端的准备工作结束。

&＃xff08;二&＃xff09;ORALCE服务器端
1.用ORACLE的企业管理器&＃xff0c;创建ORACLE表空间和用户&＃xff0c;我创建的数据表空间名称是CFWX&＃xff0c;索引表空间名称CFWX_INDX&＃xff0c;这里数据表空间和索引表空间分开&＃xff0c;可提升系统日后的运行效率&＃xff1b;创建的用户是EJIA&＃xff0c;创建用户后记得授权。
在PL/SQL DEVELOPER 7.0工具中执行创建用户。
create user EJIA identified by EJIA default tablespace CFWX;
grant connect,resource,dba to EJIA;
revoke unlimited tablespace from EJIA;
alter user EJIA quota 0 on Users;
alter user EJIA　Quota unlimited on CFWX;

2.配置好透明网关&＃xff0c;方便在ORACLE后台访问MS SQL SERVER数据库。关于如何配置从ORACLE访问SQL SERVER数据库的链路方法&＃xff0c;请阅读http://user.qzone.qq.com/56430204 空间的技术文档日志“从Oracle 9i连接SQL Server数据库 ”。配置好透明网关后&＃xff0c;创建一个访问SQL SERVER数据库的链路&＃xff0c;我的链路名称命名为&＃xff1a;DBSQL。
在PL/SQL DEVELOPER 7.0工具中执行代码创建链路例子&＃xff1a;

create PUBLIC database link DBSQL connect to SA identified by "1" using &＃39;MSTNSNAME&＃39;;

注释&＃xff1a;MSTNSNAME 是我用的访问SQL SERVER数据库的服务名。

3.需要注意&＃xff0c;通过配置透明网关&＃xff08;Oracle transparent gateway&＃xff09;访问SQL Server 2000数据库&＃xff0c;如果Oracle transparent gateway 安装的是10.2以前的版本&＃xff0c;在ORACLE的PL/SQL工具中&＃xff0c;你会发现bigint类型字段的数据&＃xff0c;查询出来的结果都是0。所以凡是含有这种字段类型的表&＃xff0c;其数据类型需要先在视图中转换为NUMERIC(19)类型&＃xff0c;然后查询它的视图导入&＃xff0c;或者用工具PL/SQL DEVELOPER 7.0>>TOOLS>>ODBC IMPORTER功能导入数据。通过透明网关&＃xff0c;text类型字段&＃xff0c;也无法直接查询并插入ORACLE表中&＃xff0c;也需要先在视图中转换为VARCHAR(4000)类型&＃xff0c;然后通过视图操作。这也就是我在前面第四步需要对所有包含text或bigint类型字段的表都创建视图的原因。

为减少手工处理工作量&＃xff0c;本人以视图的方式导入数据。
例如&＃xff1a;

SQL SERVER中先创建视图&＃xff1a;
CREATE VIEW V_CZRJB AS SELECT CAST(GUID AS NUMERIC(19)) AS GUID,RJLX, KHDM FROM CZRJB

GO

ORACLE中执行&＃xff1a;

CREATE TABLE AS CZRJB SELECT GUID&＃xff0c;RJLX, KHDM FROM CZRJB&＃64;DBSQL;

4.执行脚本&＃xff0c;导出SQL SERVER对象信息保存在ORALCE库中&＃xff0c;方便下一步处理。

--把需要创建的主键信息&＃xff0c;保存到本地ORACLE库(用到前面创建的视图V_MYPK)
CREATE TABLE MYPK AS SELECT * from V_MYPK&＃64;DBSQL;

--把需要创建的索引&＃xff0c;保存到本地ORACLE库
CREATE TABLE MYINDEX AS SELECT * from V_MYINDEX&＃64;DBSQL;
--需要创建的表,保存到本地ORACLE库 &＃xff08;不包括含有TEXT或bigint字段的表&＃xff09;
CREATE TABLE MYTABLE AS SELECT * from V_MYTABLE&＃64;DBSQL ;

--需要创建的表,保存到本地ORACLE库 &＃xff08;包含有TEXT或bigint字段的表&＃xff09;
CREATE TABLE MYTABLE AS SELECT * from V_MYTABLE2&＃64;DBSQL ;

--字段的默认值约束
CREATE TABLE MYVALUE AS SELECT * from V_MYVALUE&＃64;DBSQL ;

--表字段视图
--DROP TABLE MYFIELD;
CREATE TABLE MYFIELD AS SELECT * from V_MYFIELD&＃64;DBSQL ;

下面&＃xff0c;就可以完全根据这些信息&＃xff0c;用脚本动态的完成数据库对象创建&＃xff08;包括表结构、索引、约束等&＃xff09;。

5.工具PL/SQL DEVELOPER 7.0中&＃xff0c;open>>Test script 窗口执行下面脚本&＃xff0c;创建并一并迁移不含TEXT类型或bigint类型字段的表结构与数据。

--PL/SQL Developer Test script 3.0
declare
-- Local variables here
i integer;
EXESTR VARCHAR2(2000);
FIELDSTR VARCHAR2(2000);
begin
I :&＃61; 0;

--1. 先删除再创建。
FOR TMP_TB IN (SELECT upper(table_name) as TABLE_NAME
FROM User_Tab_Comments
where TABLE_TYPE &＃61; &＃39;TABLE&＃39;
AND TABLE_NAME <> &＃39;MYINDEX&＃39;
AND TABLE_NAME <> &＃39;MYTABLE&＃39;
AND TABLE_NAME <> &＃39;MYVIEW&＃39;
AND TABLE_NAME <> &＃39;MYVALUE&＃39;
AND TABLE_NAME <> &＃39;MYPK&＃39;
AND TABLE_NAME <> &＃39;MYFIELD&＃39;) LOOP
begin
EXESTR :&＃61; &＃39;DROP TABLE &＃39; || TMP_TB.table_name;
execute immediate EXESTR;
I :&＃61; I &＃43; 1;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(&＃39;删除表异常: &＃39; || EXESTR);
END;
END LOOP;

DBMS_OUTPUT.put_line(&＃39;删除表个数&＃xff1a;&＃39; || to_char(i));

I :&＃61; 0;
FIELDSTR :&＃61; &＃39;&＃39;;
FOR Tmp_TABLE IN (SELECT * from MYTABLE) LOOP
EXESTR :&＃61; &＃39;CREATE TABLE &＃39; || UPPER(Tmp_TABLE.NAME) ||
&＃39; AS SELECT #FIELDLISTS FROM &＃39; || Tmp_TABLE.NAME || &＃39;&＃64;DBSQL&＃39;;
--创建表是&＃xff0c;把字段名全部大写
FOR Tmp_CREATE IN (SELECT COLNAME
from MYFIELD
WHERE TABLENAME &＃61; Tmp_TABLE.NAME
ORDER BY COLID) LOOP
IF (FIELDSTR &＃61; &＃39;&＃39; OR FIELDSTR IS NULL) THEN
FIELDSTR :&＃61; &＃39;"&＃39; || Tmp_CREATE.COLNAME || &＃39;" AS &＃39; ||
UPPER(Tmp_CREATE.COLNAME);
ELSE
FIELDSTR :&＃61; FIELDSTR || &＃39;,"&＃39; || Tmp_CREATE.COLNAME || &＃39;" AS &＃39; ||
UPPER(Tmp_CREATE.COLNAME);
END IF;
END LOOP;
begin
EXESTR :&＃61; REPLACE(EXESTR, &＃39;#FIELDLISTS&＃39;, FIELDSTR);
FIELDSTR :&＃61; &＃39;&＃39;;
--DBMS_OUTPUT.put_line(SUBSTR(EXESTR, 1, 250));
execute immediate EXESTR;
I :&＃61; I &＃43; 1;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(&＃39;创建表异常:&＃39; || SUBSTR(EXESTR, 1, 240));
end;
END LOOP;

DBMS_OUTPUT.put_line(&＃39;正常创建表个数&＃xff1a;&＃39; || to_char(i));
end;

6.执行前面第三步骤中保存到文件“输出的脚本.SQL ””中的后半部分 ORACLE脚本&＃xff0c;通过查询视图的方式创建并迁移含TEXT类型、bigint类型的表结构与数据。此处需要注意&＃xff0c;如果某张表的TEXT类型字段超长&＃xff08;转为字符型后长度大于4000&＃xff09;&＃xff0c;或者text类型字段保存的不是字符集&＃xff0c;而是图片、文件等二进制数据&＃xff0c;那么&＃xff0c;需要用工具PL/SQL DEVELOPER 7.0>>TOOLS>>ODBC IMPORTER功能重新导入数据&＃xff0c;避免TEXT类型数据缺失。

7.执行脚本创建所有主键。

declare
-- Local variables here
i integer;
EXESTR VARCHAR2(1000);
INDEXSPACENAME VARCHAR2(30);
FIELDKEYS VARCHAR2(1000);
begin
-- 索引表空间名&＃xff08;要留意改写表索引空间名为你设定的名称&＃xff09;
INDEXSPACENAME :&＃61; &＃39;CFWX_INDX&＃39;;
I :&＃61; 0;
--1. 检查&＃xff0c;先删除主键 &＃xff0c;再创建。
FOR TMP_INDX IN (SELECT A.index_name, A.table_name
FROM USER_INDEXES A, USER_CONSTRAINTS B
WHERE A.index_name &＃61; B.index_name
AND B.constraint_type &＃61; &＃39;P&＃39;
AND A.uniqueness &＃61; &＃39;UNIQUE&＃39;) LOOP
EXESTR :&＃61; &＃39;Alter TABLE &＃39; || TMP_INDX.TABLE_NAME ||
&＃39; DROP CONSTRAINT &＃39; || TMP_INDX.INDEX_NAME || &＃39; Cascade&＃39;;
execute immediate EXESTR;
I :&＃61; I &＃43; 1;
END LOOP;

DBMS_OUTPUT.put_line(&＃39;删除主键数&＃xff1a;&＃39; || to_char(i));

I :&＃61; 0;
FIELDKEYS :&＃61; &＃39;&＃39;;
FOR TMP_PKNAME IN (SELECT DISTINCT TABLENAME, INDEXNAME FROM MYPK) LOOP
EXESTR :&＃61; &＃39;ALTER TABLE &＃39; || TMP_PKNAME.TABLENAME ||
&＃39; ADD CONSTRAINT PK_&＃39; || TMP_PKNAME.TABLENAME ||
&＃39; PRIMARY KEY(&＃64;FIELDLISTS) USING INDEX TABLESPACE &＃39; ||
INDEXSPACENAME;
FOR Tmp_PK IN (SELECT TABLENAME, INDEXNAME, COLNAME
from MYPK
WHERE TABLENAME &＃61; TMP_PKNAME.TABLENAME
AND INDEXNAME &＃61; TMP_PKNAME.INDEXNAME) LOOP
IF (FIELDKEYS &＃61; &＃39;&＃39; OR FIELDKEYS IS NULL) THEN
FIELDKEYS :&＃61; Tmp_PK.COLNAME;
ELSE
FIELDKEYS :&＃61; FIELDKEYS || &＃39;,&＃39; || Tmp_PK.COLNAME;
END IF;
END LOOP;
begin
EXESTR :&＃61; REPLACE(EXESTR, &＃39;&＃64;FIELDLISTS&＃39;, FIELDKEYS);
EXESTR :&＃61; UPPER(EXESTR);
FIELDKEYS :&＃61; &＃39;&＃39;;
execute immediate EXESTR;
I :&＃61; I &＃43; 1;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(&＃39;创建主键异常: &＃39; || EXESTR);
end;
END LOOP;
DBMS_OUTPUT.put_line(&＃39;正常创建主键数&＃xff1a;&＃39; || to_char(i));
end;

8.执行脚本创建所有的索引。

-- Created on 2010-01-22 by ADMINISTRATOR
declare
-- Local variables here
i integer;
EXESTR VARCHAR2(1000);
INDEXSPACENAME VARCHAR2(20);
FIELDSTR VARCHAR2(1000);
begin
-- 索引表空间名(要预先创建好表空间)
INDEXSPACENAME :&＃61; &＃39;CFWX_INDX&＃39;;
I :&＃61; 0;
FIELDSTR :&＃61; &＃39;&＃39;;

--删除索引后再创建
FOR TMP_DEL IN (SELECT A.TABLE_NAME, A.INDEX_NAME
FROM USER_INDEXES A
WHERE A.INDEX_TYPE &＃61; &＃39;NORMAL&＃39;
AND NOT EXISTS (SELECT *
FROM USER_CONSTRAINTS B
WHERE CONSTRAINT_TYPE &＃61; &＃39;P&＃39;
AND B.index_name &＃61; A.index_name
AND B.table_name &＃61; A.table_name
AND ROWNUM &＃61; 1)) LOOP
-- DROP INDEX INDEXNAME;
EXESTR :&＃61; &＃39;DROP INDEX &＃39; || TMP_DEL.INDEX_NAME;
Execute immediate EXESTR;
I :&＃61; I &＃43; 1;
END LOOP;
DBMS_OUTPUT.put_line(&＃39;正常删除索引数&＃xff1a;&＃39; || to_char(i));

I :&＃61; 0;
FOR TMP_IND IN (SELECT DISTINCT TABLENAME, INDNAME from MYINDEX) LOOP
--create index IX_FSHZ on FSHZ (yhdm, fsrq) tablespace EJIA
--create unique index IX_FSHZ on FSHZ (yhdm, fsrq) tablespace EJIA
EXESTR :&＃61; &＃39;CREATE INDEX &＃39; || TMP_IND.INDNAME || &＃39; ON &＃39; ||
TMP_IND.TABLENAME || &＃39; (&＃64;FIELDLISTS) TABLESPACE &＃39; ||
INDEXSPACENAME;
FOR TMP_Field IN (SELECT TABLENAME, INDNAME, COLNAME, FIELDCNT
from MYINDEX
WHERE TABLENAME &＃61; TMP_IND.TABLENAME
AND INDNAME &＃61; TMP_IND.INDNAME
ORDER BY COLID) LOOP
IF (FIELDSTR &＃61; &＃39;&＃39; OR FIELDSTR IS NULL) THEN
FIELDSTR :&＃61; TMP_Field.COLNAME;
ELSE
FIELDSTR :&＃61; FIELDSTR || &＃39;,&＃39; || TMP_Field.COLNAME;
END IF;
END LOOP;
EXESTR :&＃61; REPLACE(EXESTR, &＃39;&＃64;FIELDLISTS&＃39;, FIELDSTR);
EXESTR :&＃61; UPPER(EXESTR);
FIELDSTR :&＃61; &＃39;&＃39;;
BEGIN
Execute immediate EXESTR;
I :&＃61; I &＃43; 1;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(&＃39;创建索引异常: &＃39; || EXESTR);
end;
END LOOP;
DBMS_OUTPUT.put_line(&＃39;正常创建索引数&＃xff1a;&＃39; || to_char(i));
end;

9.创建字段的默认值约束。此段代码不能通用&＃xff0c;需要根据情况改写。

declare
-- Local variables here
i integer;
EXESTR VARCHAR2(1000);
begin
I :&＃61; 0;
FOR TMP_VALUE IN (SELECT DISTINCT *
from MYVALUE
WHERE TEXT &＃61; &＃39;(getdate())&＃39;) LOOP
--alter table CZRJB modify RJLX default 0;
EXESTR :&＃61; &＃39;alter table &＃39; || TMP_VALUE.TABLENAME || &＃39; MODIFY &＃39; ||
TMP_VALUE.COLNAME || &＃39; default SYSDATE&＃39;;
BEGIN
Execute immediate EXESTR;
I :&＃61; I &＃43; 1;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(&＃39;创建SYSDATE默认值异常: &＃39; || EXESTR);
end;
END LOOP;
DBMS_OUTPUT.put_line(&＃39;创建SYSDATE默认值个数&＃xff1a;&＃39; || to_char(i));
I :&＃61; 0;
FOR TMP_VALUE IN (SELECT DISTINCT *
from MYVALUE
WHERE TEXT &＃61; &＃39;(0)&＃39;) LOOP
--alter table CZRJB modify RJLX default 0;
EXESTR :&＃61; &＃39;alter table &＃39; || TMP_VALUE.TABLENAME || &＃39; MODIFY &＃39; ||
TMP_VALUE.COLNAME || &＃39; default 0&＃39;;
BEGIN
Execute immediate EXESTR;
I :&＃61; I &＃43; 1;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(&＃39;创建0默认值异常: &＃39; || EXESTR);
end;
END LOOP;
DBMS_OUTPUT.put_line(&＃39;创建0默认值个数&＃xff1a;&＃39; || to_char(i));
I :&＃61; 0;
FOR TMP_VALUE IN (SELECT DISTINCT *
from MYVALUE
WHERE TEXT &＃61; &＃39;(1)&＃39;) LOOP
--alter table CZRJB modify RJLX default 0;
EXESTR :&＃61; &＃39;alter table &＃39; || TMP_VALUE.TABLENAME || &＃39; MODIFY &＃39; ||
TMP_VALUE.COLNAME || &＃39; default 1&＃39;;
BEGIN
Execute immediate EXESTR;
I :&＃61; I &＃43; 1;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(&＃39;创建1默认值异常: &＃39; || EXESTR);
end;
END LOOP;
DBMS_OUTPUT.put_line(&＃39;创建1默认值个数&＃xff1a;&＃39; || to_char(i));
I :&＃61; 0;
FOR TMP_VALUE IN (SELECT DISTINCT *
from MYVALUE
WHERE TEXT &＃61; &＃39;(&＃39;&＃39;&＃39;&＃39;)&＃39;) LOOP
--alter table CZRJB modify RJLX default 0;
EXESTR :&＃61; &＃39;alter table &＃39; || TMP_VALUE.TABLENAME || &＃39; MODIFY &＃39; ||
TMP_VALUE.COLNAME || &＃39; default &＃39;&＃39;&＃39;&＃39;&＃39;;
BEGIN
Execute immediate EXESTR;
I :&＃61; I &＃43; 1;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(&＃39;创建""默认值异常: &＃39; || EXESTR);
end;
END LOOP;
DBMS_OUTPUT.put_line(&＃39;创建""默认值个数&＃xff1a;&＃39; || to_char(i));
I :&＃61; 0;
FOR TMP_VALUE IN (SELECT DISTINCT *
from MYVALUE
WHERE TEXT &＃61; &＃39;(3)&＃39;) LOOP
--alter table CZRJB modify RJLX default 0;
EXESTR :&＃61; &＃39;alter table &＃39; || TMP_VALUE.TABLENAME || &＃39; MODIFY &＃39; ||
TMP_VALUE.COLNAME || &＃39; default 3&＃39;;
BEGIN
Execute immediate EXESTR;
I :&＃61; I &＃43; 1;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(&＃39;创建3默认值异常: &＃39; || EXESTR);
end;
END LOOP;
DBMS_OUTPUT.put_line(&＃39;创建3默认值个数&＃xff1a;&＃39; || to_char(i));
I :&＃61; 0;
FOR TMP_VALUE IN (SELECT DISTINCT *
from MYVALUE
WHERE TEXT &＃61; &＃39;(70)&＃39;) LOOP
--alter table CZRJB modify RJLX default 0;
EXESTR :&＃61; &＃39;alter table &＃39; || TMP_VALUE.TABLENAME || &＃39; MODIFY &＃39; ||
TMP_VALUE.COLNAME || &＃39; default 70&＃39;;
BEGIN
Execute immediate EXESTR;
I :&＃61; I &＃43; 1;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(&＃39;创建70默认值异常: &＃39; || EXESTR);
end;
END LOOP;
DBMS_OUTPUT.put_line(&＃39;创建70默认值个数&＃xff1a;&＃39; || to_char(i));
I :&＃61; 0;
FOR TMP_VALUE IN (SELECT DISTINCT *
from MYVALUE
WHERE TEXT &＃61; &＃39;(&＃39;&＃39;未知&＃39;&＃39;)&＃39;) LOOP
--alter table CZRJB modify RJLX default 0;
EXESTR :&＃61; &＃39;alter table &＃39; || TMP_VALUE.TABLENAME || &＃39; MODIFY &＃39; ||
TMP_VALUE.COLNAME || &＃39; default &＃39;&＃39;未知&＃39;&＃39;&＃39;;
BEGIN
Execute immediate EXESTR;
I :&＃61; I &＃43; 1;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(&＃39;创建(&＃39;&＃39;未知&＃39;&＃39;)默认值异常: &＃39; || EXESTR);
end;
END LOOP;
DBMS_OUTPUT.put_line(&＃39;创建(&＃39;&＃39;未知&＃39;&＃39;)默认值个数&＃xff1a;&＃39; || to_char(i));
end;

10.创建外键约束。先在MS SQL SERVER服务中&＃xff0c;查询出库中的所有外键约束。

select (CASE a.xtype
WHEN &＃39;F&＃39; THEN
&＃39;外键&＃39;
WHEN &＃39;C&＃39; THEN
&＃39;约束&＃39;
WHEN &＃39;D&＃39; THEN
&＃39;默认值&＃39;
END) AS LX, a.name AS NAME,OBJECT_NAME(parent_obj) AS TABLENAME,
b.TEXT
from sysobjects a
left outer join syscomments b on a.id &＃61; b.id
where (a.xtype in (&＃39;F&＃39;,&＃39;C&＃39;,&＃39;D&＃39;) AND OBJECTPROPERTY(a.id, N&＃39;IsMSShipped&＃39;) &＃61; 0)
ORDER BY parent_obj

参照查询结果&＃xff0c;在ORACLE中创建外键约束。此处自动创建约束的脚本请自行完成。

11.创建触发器&＃xff0c;模拟实现MS SERVER自增长字段功能。需要用ORACLE序列和触发器配合实现。先在MS SQL SERVER服务中&＃xff0c;执行如下SQL语句&＃xff1a;
select name,object_name(id) as tablename from syscolumns where COLUMNPROPERTY(id,name,&＃39;IsIdentity&＃39;)&＃61;1;
查看一下那些表有自增型字段&＃xff0c;并参照如下脚本创建每个表的触发器。

本人建议不用触发器实现&＃xff0c;应该直接去修改源码的INSERT语句。

建触发器&＃xff1a;

CREATE OR REPLACE TRIGGER TRG_INSERT_CSSZ
BEFORE INSERT ON CSSZ FOR EACH ROW
DECLARE
NEXTID NUMBER;
BEGIN
IF :NEW.GUID IS NULL OR :NEW.GUID &＃61; 0 THEN
:NEW.GUID :&＃61; GETSEQ();
END IF;
END;

12.参照ORACLE语法规范&＃xff0c;修改SQL SERVER的存储过程和函数、视图。

13.客户端源码和服务端源码修改。含有自增长型字段的表&＃xff0c;代码中所有未列明插入字段列表的SQL语句&＃xff0c;都要改写。
如&＃xff1a;INSERT INTO CSSZ VALUES (&＃39;TEST&＃39;,&＃39;TEST&＃39;,NULL,&＃39;&＃39;,0,GETDATE())
所有函数、存储过程调用的地方&＃xff0c;也都要改写。

14.完工。清理在SQL SERVER库中创建的视图和函数。