sybase isql 导出文件 sybase数据库转oracle

转载

hochie 2023-10-27 00:46:32

文章标签 sybase isql 导出文件数据库 SQL 字段 文章分类 Sybase 数据库

操作步骤如下，供参考。

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

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

(一)在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 
GOset ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GOCREATE 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 
 SET @fieldlist = @FIELDNAME 
 END
 ELSE 
 BEGIN
 SET @fieldlist = @fieldlist + ',' + @FIELDNAME 
 END 
 FETCH NEXT FROM field_cursor INTO @FIELDNAME,@DATATYPE
 end
 SET @fieldlist = UPPER('CREATE VIEW V_' + @TABLENAME +' AS SELECT ' + @fieldlist + ' FROM ' + @TABLENAME) 
 CLOSE field_cursor
 DEALLOCATE field_cursor
 RETURN @fieldlist
END
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[GETORASQL](@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 = 'text' 
 begin
 SET @FIELDNAME = 'TO_CLOB('+@FIELDNAME+') AS ' + @FIELDNAME
 end
 IF @fieldlist = '' 
 BEGIN 
 SET @fieldlist = @FIELDNAME 
 END
 ELSE 
 BEGIN
 SET @fieldlist = @fieldlist + ',' + @FIELDNAME 
 END 
 FETCH NEXT FROM field_cursor INTO @FIELDNAME,@DATATYPE
 end
 SET @fieldlist = UPPER('CREATE TABLE ' + @TABLENAME +' AS SELECT ' + @fieldlist + ' FROM V_' + @TABLENAME+'@DBSQL;') 
 CLOSE field_cursor
 DEALLOCATE field_cursor
 RETURN @fieldlist
END

3.执行下段代码，快速获得创建视图的脚本。此处只创建含有TEXT、BIGINT类型字段的表的视图。
DECLARE @tablename VARCHAR(36)
DECLARE TABLE_cursor CURSOR FOR SELECT * FROM V_MYTABLE2 OPEN TABLE_cursor
FETCH NEXT FROM TABLE_cursor INTO @tablename
PRINT '-- 请在SQL SERVER服务器创建下列视图:'
WHILE (@@FETCH_STATUS = 0)
BEGIN
 PRINT '-- V_'+@tablename 
 PRINT [dbo].GETSELECTSQL(@tablename)
 PRINT 'GO '
 PRINT ''
 FETCH NEXT FROM TABLE_cursor INTO @tablename
ENDPRINT ''
PRINT ''
PRINT ''
PRINT ''CLOSE TABLE_cursor
OPEN TABLE_cursor
FETCH NEXT FROM TABLE_cursor INTO @tablename
PRINT '-- 请在ORACLE服务器中执行下面脚本，迁移表和数据:'
WHILE (@@FETCH_STATUS = 0)
BEGIN
 PRINT '-- '+@tablename 
 PRINT [dbo].GETORASQL(@tablename) 
 PRINT ' '
 FETCH NEXT FROM TABLE_cursor INTO @tablename
END
CLOSE TABLE_cursor
DEALLOCATE TABLE_cursor
GO把输出的创建视图的代码，保存到文件“输出的脚本.SQL”中。

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

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

（二）ORALCE服务器端
1.用ORACLE的企业管理器，创建ORACLE表空间和用户，我创建的数据表空间名称是CFWX，索引表空间名称CFWX_INDX，这里数据表空间和索引表空间分开，可提升系统日后的运行效率；创建的用户是EJIA，创建用户后记得授权。
在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.配置好透明网关，方便在ORACLE后台访问MS SQL SERVER数据库。关于如何配置从ORACLE访问SQL SERVER数据库的链路方法，请阅读http://user.qzone.qq.com/56430204 空间的技术文档日志“从Oracle 9i连接SQL Server数据库 ”。配置好透明网关后，创建一个访问SQL SERVER数据库的链路，我的链路名称命名为：DBSQL。
在PL/SQL DEVELOPER 7.0工具中执行代码创建链路例子：

create PUBLIC database link DBSQL connect to SA identified by "1" using 'MSTNSNAME';

注释：MSTNSNAME 是我用的访问SQL SERVER数据库的服务名。

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

为减少手工处理工作量，本人以视图的方式导入数据。
例如：

SQL SERVER中先创建视图：
CREATE VIEW V_CZRJB AS SELECT CAST(GUID AS NUMERIC(19)) AS GUID,RJLX, KHDM FROM CZRJBGO
ORACLE中执行：
CREATE TABLE AS CZRJB SELECT GUID，RJLX, KHDM FROM CZRJB@DBSQL;

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

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

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

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

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

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

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

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

--PL/SQL Developer Test script 3.0
declare
 -- Local variables here
 i integer;
 EXESTR VARCHAR2(2000);
 FIELDSTR VARCHAR2(2000);
begin
 I := 0; --1. 先删除再创建。
 FOR TMP_TB IN (SELECT upper(table_name) as TABLE_NAME
 FROM User_Tab_Comments
 where TABLE_TYPE = 'TABLE'
 AND TABLE_NAME <> 'MYINDEX'
 AND TABLE_NAME <> 'MYTABLE'
 AND TABLE_NAME <> 'MYVIEW'
 AND TABLE_NAME <> 'MYVALUE'
 AND TABLE_NAME <> 'MYPK'
 AND TABLE_NAME <> 'MYFIELD') LOOP
 begin
 EXESTR := 'DROP TABLE ' || TMP_TB.table_name;
 execute immediate EXESTR;
 I := I + 1;
 EXCEPTION
 WHEN others THEN
 DBMS_OUTPUT.put_line('删除表异常: ' || EXESTR);
 END;
 END LOOP; DBMS_OUTPUT.put_line('删除表个数：' || to_char(i));
 I := 0;
 FIELDSTR := '';
 FOR Tmp_TABLE IN (SELECT * from MYTABLE) LOOP
 EXESTR := 'CREATE TABLE ' || UPPER(Tmp_TABLE.NAME) ||
 ' AS SELECT #FIELDLISTS FROM ' || Tmp_TABLE.NAME || '@DBSQL';
 --创建表是，把字段名全部大写 
 FOR Tmp_CREATE IN (SELECT COLNAME
 from MYFIELD
 WHERE TABLENAME = Tmp_TABLE.NAME
 ORDER BY COLID) LOOP
 IF (FIELDSTR = '' OR FIELDSTR IS NULL) THEN
 FIELDSTR := '"' || Tmp_CREATE.COLNAME || '" AS ' ||
 UPPER(Tmp_CREATE.COLNAME);
 ELSE
 FIELDSTR := FIELDSTR || ',"' || Tmp_CREATE.COLNAME || '" AS ' ||
 UPPER(Tmp_CREATE.COLNAME);
 END IF;
 END LOOP;
 begin
 EXESTR := REPLACE(EXESTR, '#FIELDLISTS', FIELDSTR);
 FIELDSTR := '';
 --DBMS_OUTPUT.put_line(SUBSTR(EXESTR, 1, 250));
 execute immediate EXESTR;
 I := I + 1;
 EXCEPTION
 WHEN others THEN
 DBMS_OUTPUT.put_line('创建表异常:' || SUBSTR(EXESTR, 1, 240));
 end;
 END LOOP; DBMS_OUTPUT.put_line('正常创建表个数：' || to_char(i));
end;

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

7.执行脚本创建所有主键。
declare
 -- Local variables here
 i integer;
 EXESTR VARCHAR2(1000);
 INDEXSPACENAME VARCHAR2(30);
 FIELDKEYS VARCHAR2(1000);
begin
 -- 索引表空间名（要留意改写表索引空间名为你设定的名称） 
 INDEXSPACENAME := 'CFWX_INDX';
 I := 0;
 --1. 检查，先删除主键 ，再创建。
 FOR TMP_INDX IN (SELECT A.index_name, A.table_name
 FROM USER_INDEXES A, USER_CONSTRAINTS B
 WHERE A.index_name = B.index_name
 AND B.constraint_type = 'P'
 AND A.uniqueness = 'UNIQUE') LOOP
 EXESTR := 'Alter TABLE ' || TMP_INDX.TABLE_NAME ||
 ' DROP CONSTRAINT ' || TMP_INDX.INDEX_NAME || ' Cascade';
 execute immediate EXESTR;
 I := I + 1;
 END LOOP; DBMS_OUTPUT.put_line('删除主键数：' || to_char(i));
 I := 0;
 FIELDKEYS := '';
 FOR TMP_PKNAME IN (SELECT DISTINCT TABLENAME, INDEXNAME FROM MYPK) LOOP
 EXESTR := 'ALTER TABLE ' || TMP_PKNAME.TABLENAME ||
 ' ADD CONSTRAINT PK_' || TMP_PKNAME.TABLENAME ||
 ' PRIMARY KEY(@FIELDLISTS) USING INDEX TABLESPACE ' ||
 INDEXSPACENAME;
 FOR Tmp_PK IN (SELECT TABLENAME, INDEXNAME, COLNAME
 from MYPK
 WHERE TABLENAME = TMP_PKNAME.TABLENAME
 AND INDEXNAME = TMP_PKNAME.INDEXNAME) LOOP
 IF (FIELDKEYS = '' OR FIELDKEYS IS NULL) THEN
 FIELDKEYS := Tmp_PK.COLNAME;
 ELSE
 FIELDKEYS := FIELDKEYS || ',' || Tmp_PK.COLNAME;
 END IF;
 END LOOP;
 begin
 EXESTR := REPLACE(EXESTR, '@FIELDLISTS', FIELDKEYS);
 EXESTR := UPPER(EXESTR);
 FIELDKEYS := '';
 execute immediate EXESTR;
 I := I + 1;
 EXCEPTION
 WHEN others THEN
 DBMS_OUTPUT.put_line('创建主键异常: ' || EXESTR);
 end;
 END LOOP;
 DBMS_OUTPUT.put_line('正常创建主键数：' || 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 := 'CFWX_INDX';
 I := 0;
 FIELDSTR := ''; --删除索引后再创建 
 FOR TMP_DEL IN (SELECT A.TABLE_NAME, A.INDEX_NAME
 FROM USER_INDEXES A
 WHERE A.INDEX_TYPE = 'NORMAL'
 AND NOT EXISTS (SELECT *
 FROM USER_CONSTRAINTS B
 WHERE CONSTRAINT_TYPE = 'P'
 AND B.index_name = A.index_name
 AND B.table_name = A.table_name
 AND ROWNUM = 1)) LOOP
 -- DROP INDEX INDEXNAME;
 EXESTR := 'DROP INDEX ' || TMP_DEL.INDEX_NAME;
 Execute immediate EXESTR;
 I := I + 1;
 END LOOP;
 DBMS_OUTPUT.put_line('正常删除索引数：' || to_char(i)); I := 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 := 'CREATE INDEX ' || TMP_IND.INDNAME || ' ON ' ||
 TMP_IND.TABLENAME || ' (@FIELDLISTS) TABLESPACE ' ||
 INDEXSPACENAME;
 FOR TMP_Field IN (SELECT TABLENAME, INDNAME, COLNAME, FIELDCNT
 from MYINDEX
 WHERE TABLENAME = TMP_IND.TABLENAME
 AND INDNAME = TMP_IND.INDNAME
 ORDER BY COLID) LOOP
 IF (FIELDSTR = '' OR FIELDSTR IS NULL) THEN
 FIELDSTR := TMP_Field.COLNAME;
 ELSE
 FIELDSTR := FIELDSTR || ',' || TMP_Field.COLNAME;
 END IF;
 END LOOP;
 EXESTR := REPLACE(EXESTR, '@FIELDLISTS', FIELDSTR);
 EXESTR := UPPER(EXESTR);
 FIELDSTR := '';
 BEGIN
 Execute immediate EXESTR;
 I := I + 1;
 EXCEPTION
 WHEN others THEN
 DBMS_OUTPUT.put_line('创建索引异常: ' || EXESTR);
 end;
 END LOOP;
 DBMS_OUTPUT.put_line('正常创建索引数：' || to_char(i));
end;9.创建字段的默认值约束。此段代码不能通用，需要根据情况改写。
declare
 -- Local variables here
 i integer;
 EXESTR VARCHAR2(1000);
begin
 I := 0;
 FOR TMP_VALUE IN (SELECT DISTINCT *
 from MYVALUE
 WHERE TEXT = '(getdate())') LOOP
 --alter table CZRJB modify RJLX default 0; 
 EXESTR := 'alter table ' || TMP_VALUE.TABLENAME || ' MODIFY ' ||
 TMP_VALUE.COLNAME || ' default SYSDATE';
 BEGIN
 Execute immediate EXESTR;
 I := I + 1;
 EXCEPTION
 WHEN others THEN
 DBMS_OUTPUT.put_line('创建SYSDATE默认值异常: ' || EXESTR);
 end;
 END LOOP;
 DBMS_OUTPUT.put_line('创建SYSDATE默认值个数：' || to_char(i));
 I := 0;
 FOR TMP_VALUE IN (SELECT DISTINCT *
 from MYVALUE
 WHERE TEXT = '(0)') LOOP
 --alter table CZRJB modify RJLX default 0; 
 EXESTR := 'alter table ' || TMP_VALUE.TABLENAME || ' MODIFY ' ||
 TMP_VALUE.COLNAME || ' default 0';
 BEGIN
 Execute immediate EXESTR;
 I := I + 1;
 EXCEPTION
 WHEN others THEN
 DBMS_OUTPUT.put_line('创建0默认值异常: ' || EXESTR);
 end;
 END LOOP;
 DBMS_OUTPUT.put_line('创建0默认值个数：' || to_char(i));
 I := 0;
 FOR TMP_VALUE IN (SELECT DISTINCT *
 from MYVALUE
 WHERE TEXT = '(1)') LOOP
 --alter table CZRJB modify RJLX default 0; 
 EXESTR := 'alter table ' || TMP_VALUE.TABLENAME || ' MODIFY ' ||
 TMP_VALUE.COLNAME || ' default 1';
 BEGIN
 Execute immediate EXESTR;
 I := I + 1;
 EXCEPTION
 WHEN others THEN
 DBMS_OUTPUT.put_line('创建1默认值异常: ' || EXESTR);
 end;
 END LOOP;
 DBMS_OUTPUT.put_line('创建1默认值个数：' || to_char(i));
 I := 0;
 FOR TMP_VALUE IN (SELECT DISTINCT *
 from MYVALUE
 WHERE TEXT = '('''')') LOOP
 --alter table CZRJB modify RJLX default 0; 
 EXESTR := 'alter table ' || TMP_VALUE.TABLENAME || ' MODIFY ' ||
 TMP_VALUE.COLNAME || ' default ''''';
 BEGIN
 Execute immediate EXESTR;
 I := I + 1;
 EXCEPTION
 WHEN others THEN
 DBMS_OUTPUT.put_line('创建""默认值异常: ' || EXESTR);
 end;
 END LOOP;
 DBMS_OUTPUT.put_line('创建""默认值个数：' || to_char(i));
 I := 0;
 FOR TMP_VALUE IN (SELECT DISTINCT *
 from MYVALUE
 WHERE TEXT = '(3)') LOOP
 --alter table CZRJB modify RJLX default 0; 
 EXESTR := 'alter table ' || TMP_VALUE.TABLENAME || ' MODIFY ' ||
 TMP_VALUE.COLNAME || ' default 3';
 BEGIN
 Execute immediate EXESTR;
 I := I + 1;
 EXCEPTION
 WHEN others THEN
 DBMS_OUTPUT.put_line('创建3默认值异常: ' || EXESTR);
 end;
 END LOOP;
 DBMS_OUTPUT.put_line('创建3默认值个数：' || to_char(i));
 I := 0;
 FOR TMP_VALUE IN (SELECT DISTINCT *
 from MYVALUE
 WHERE TEXT = '(70)') LOOP
 --alter table CZRJB modify RJLX default 0; 
 EXESTR := 'alter table ' || TMP_VALUE.TABLENAME || ' MODIFY ' ||
 TMP_VALUE.COLNAME || ' default 70';
 BEGIN
 Execute immediate EXESTR;
 I := I + 1;
 EXCEPTION
 WHEN others THEN
 DBMS_OUTPUT.put_line('创建70默认值异常: ' || EXESTR);
 end;
 END LOOP;
 DBMS_OUTPUT.put_line('创建70默认值个数：' || to_char(i));
 I := 0;
 FOR TMP_VALUE IN (SELECT DISTINCT *
 from MYVALUE
 WHERE TEXT = '(''未知'')') LOOP
 --alter table CZRJB modify RJLX default 0; 
 EXESTR := 'alter table ' || TMP_VALUE.TABLENAME || ' MODIFY ' ||
 TMP_VALUE.COLNAME || ' default ''未知''';
 BEGIN
 Execute immediate EXESTR;
 I := I + 1;
 EXCEPTION
 WHEN others THEN
 DBMS_OUTPUT.put_line('创建(''未知'')默认值异常: ' || EXESTR);
 end;
 END LOOP;
 DBMS_OUTPUT.put_line('创建(''未知'')默认值个数：' || to_char(i));
end;
10.创建外键约束。先在MS SQL SERVER服务中，查询出库中的所有外键约束。
select (CASE a.xtype
 WHEN 'F' THEN
 '外键'
 WHEN 'C' THEN
 '约束'
 WHEN 'D' THEN
 '默认值'
 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 = b.id
where (a.xtype in ('F','C','D') AND OBJECTPROPERTY(a.id, N'IsMSShipped') = 0)
ORDER BY parent_obj参照查询结果，在ORACLE中创建外键约束。此处自动创建约束的脚本请自行完成。

11.创建触发器，模拟实现MS SERVER自增长字段功能。需要用ORACLE序列和触发器配合实现。先在MS SQL SERVER服务中，执行如下SQL语句：
select name,object_name(id) as tablename from syscolumns where COLUMNPROPERTY(id,name,'IsIdentity')=1;
查看一下那些表有自增型字段，并参照如下脚本创建每个表的触发器。本人建议不用触发器实现，应该直接去修改源码的INSERT语句。
建触发器：
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 = 0 THEN
 :NEW.GUID := GETSEQ();
 END IF;
 END;

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

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

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。