SQL SERVER数据库快速转ORACLE实战
来源：互联网 发布：防p2p限速软件 编辑：程序博客网 时间：2024/05/21 21:43
操作步骤如下，供参考。
数据库转型工作涉及的工作事项分析：表，表数据，索引，外键约束，字段默认值。
存储过程、函数、触发器、视图等由于语法存在差异，只能自行改写处理。
(一)在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
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
END
PRINT ''
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 CZRJB
GO
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库中创建的视图和函数。
- SQL SERVER数据库快速转ORACLE实战
- SQL Server还原数据库实战
- SQL Server访问ORACLE数据库
- 【数据库】Oracle vs SQL Server
- Oracle 访问 SQL SERVER实战全过程
- SQL Server数据库优化实战（一）
- SQL Server数据库优化实战（二）
- SQL Server数据库优化实战（三）
- SQL Server开发者Oracle快速入门
- SQL Server开发者Oracle快速入门
- 迁移 SQL Server 数据库到 Azure SQL 实战
- 迁移 SQL Server 数据库到 Azure SQL 实战
- 如何连接SQL Server与Oracle数据库
- Oracle与SQL Server数据库对比
- Oracle,MySQL,SQL Server数据库的数据类型
- 如何连接SQL Server与Oracle数据库
- 认识Oracle/SQL Server的免费数据库
- 认识Oracle/SQL Server的免费数据库
- 10000的阶乘
- 第二章 COBOL程序概述----笔记
- 讨论WPF单位的设备无关性
- Mac OS X のキーボードショートカット
- SQL SERVER常用日期函数
- SQL SERVER数据库快速转ORACLE实战
- JUnit的简单例子
- 使用闪回从人为错误中恢复
- 面向对象JavaScript
- Windows下安装最新的Apache+PHP+MySQL
- C头文件相互包含的问题
- Spring JDBC事务管理
- 如何创建高性能的索引
- 伪静态URL重写技术实现方法 php asp
