把Oracle数据库移植到Microsoft SQL Server 7.0(2)

安装和配置Microsoft SQL Server
　　了解了Oracle和SQL Server之间基本结构上的差异以后，你就可以开始进行移植过程的第
　　 Query Analyzer将用来运行下面的脚本：

　　使用Windows NT基于软件的RAID或者基于硬件的RAID第五级来创建一个足够放下你的所有
　　对空间的估算可以通过计算被Oracle系统、临时文件以及应用程序表空间占用的文件空间
　　使用Windows NT基于软件的RAID或者基于硬件的RAID第一级创建一个第二逻辑驱动器来放
　　器的大小起码应该和在线恢复以及后滚表空间的总的大小一致。
　　使用SQL Server Enterprise Manager创建一个和Oracle应用程序表空间名字一样的数据
　　序使用的数据库名字叫做USER_DB）标明文件位置，使它们分别和你在第一步以及第二步
　　建的磁盘位置一致。如果你使用多个Oracle表空间，不需要也建议你不要创建多个SQL
　　会自动为你分配的。
　　创建SQL Server登录账号： USE MASTER
　　EXEC SP_ADDLOGIN STUDENT_ADMIN, STUDENT_ADMIN

　　EXEC SP_ADDLOGIN DEPT_ADMIN, DEPT_ADMIN
　　EXEC SP_ADDLOGIN ENDUSER1, ENDUSER1
　　GO
　　为数据库添加角色： USE USER_DB
　　EXEC SP_ADDROLE DATA_ADMIN
　　EXEC SP_ADDROLE USER_LOGON
　　GO
　　为角色授予许可： GRANT CREATE TABLE, CREATE TRIGGER, CREATE VIEW,
　　CREATE PROCEDURE TO DATA_ADMIN
　　GO
　　增加作为数据库用户账号的登录账号：
　　EXEC SP_ADDUSER ENDUSER1, ENDUSER1, USER_LOGON

　　EXEC SP_ADDUSER DEPT_ADMIN, DEPT_ADMIN, DATA_ADMIN
　　EXEC SP_ADDUSER STUDENT_ADMIN, STUDENT_ADMIN, DATA_ADMIN
　　GO

　　定义数据库对象
　　Oracle数据库对象（表、视图和索引）可以很容易的移植到Microsoft SQL Server上，这
　　库都基本遵循SQL-92标准，该标准承认对象定义。把Oracle SQL的表、索引和视图的定义
　　的表、索引和视图的定义只需要做相对简单的语法改变。下表指出了Oracle和Microsoft
　　数据库对象的某些不同之处。
　　类别 Microsoft SQL　Server Oracle
　　列数 1024 254
　　行尺寸 8060 byte, 外加16 byte用来指向每一个text或者image列 无限制 (每行只允许
　　raw)
　　最大行数 无限制 无限制
　　BLOB类型存储 行中存储一个16-byte 指针。数据存储在其他数据页。 每表一个long或
　　行的结尾。数据存储在行的同一个块里。
　　分簇表索引 每表一个 每表一个(index-organized tables)
　　未分簇的表索引 每表249 无限制
　　在单一索引中的最大索引列数 16 16
　　索引中列值的最大长度 900 bytes ? block
　　表名约定 [[[server.]database.]owner.]
　　table_name [schema.]table_name
　　视图名约定 [[[server.]database.]owner.]
　　table_name [schema.]table_name
　　索引名约定 [[[server.]database.]owner.]
　　table_name [schema.]table_name

　　假设你是从一个Oracle脚本或者程序开始的，该脚本或者程序用来创建你的数据库对象。
　　者程序并且进行如下修改。这些修改将在本部分的其他地方加以讨论。该例子是从示例应
　　Oratable.sql和Sstable.sql中截取的：
　　确保数据库对象标识遵循Microsoft SQL Server命名法则。你可能只需要修改索引的名字
　　修改数据存储参数使之能在SQL Server下工作。如果你使用RAID，就不需要任何存储参数
　　修改Oracle约束定义使之能在SQL Server中工作。如果需要的话，创建一个触发器以支持
　　 CASCADE语句。如果表跨数据库的话，使用触发器来增强外部键的关系。
　　修改CREATE INDEX语句以利用分簇的索引。
　　使用数据转换服务来创建新的CREATE TABLE语句。回顾该语句，注意Oracle数据类型是如
　　Server数据类型上的。
　　清除所有的CREATE SEQUENCE语句。在CREATE TABLE或者ALTER TABLE语句中使用同等列
　　如果需要的话，修改CREATE VIEW语句。
　　清除所有对同义字的引用。
　　评估对Microsoft SQL Server临时表的使用和其在你的应用程序中的用处。
　　把所有的Oracle的CREATE TABLE…AS SELECT命令改为SQL Server的SELECT…INTO语句。
　　评估潜在的对用户定义规则、数据类型和缺省的使用。

　　数据对象标识符
　　下表比较了Oracle和Microsoft SQL Server是如何处理对象标识符的。在许多情况下，当
　　Server上时，你不需要改变对象的名字。
　　Oracle Microsoft SQL　
　　1-30 字符长度。
　　数据库名称：最多8个字符长度。
　　数据库连接名称：最多128个字符长度。 1-128 Unicode字符长度。
　　临时表名称：最多116个字符。
　　标识符的名称必须用：字母、包含文字数字的字符、或者字符_, $, 和 #开头 标识符名
　　数字字符、或者_开头，实际上可以用任何字符开头。
　　如果标识符用空格开头，或者包含了不是_、@、#、或者$的字符，你必须用[]（定界符）
　　如果一个对象用下面这些字符开头：
　　@ 则表明该对象是一个本地变量。
　　# 则该对象是一个本地临时对象。
　　## 则该对象是一个全局临时对象
　　表空间名必须唯一. 数据库名必须唯一
　　标识符名在用户账号（计划，Schema）范围内必须唯一。 标识符名在数据库用户账号范
　　列名在表和视图范围内必须唯一。 列名在表和视图范围内必须唯一。
　　索引名在用户账号（Schema）范围内必须唯一。 索引名在数据库表名范围内必须唯一

　　修饰表名
　　当访问存在于你的用户账号中的表时，该表可以简单的通过未经限制的表名来选中。访问
　　中的表就需要把该计划的名字作为前缀加到表名上，两者之间用点号（.）隔开。Oracle
　　更高的位置透明度。
　　涉及到表时，Microsoft SQL Server采用一种不同的方法。因为一个SQL Server登录账号
　　库中用同一个名字创建一个表，所以采用下面的方法来访问表和视图：［［数据库名字］
　　用……访问一个表 Oracle Microsoft SQL　Server
　　你的用户账号 SELECT *
　　FROM STUDENT SELECT * FROM USER_DB.STUDENT_

　　ADMIN.STUDENT
　　其他模式（schema） SELECT * FROM STUDENT_ADMIN.STUDENT SELECT * FROM
　　ADMIN.STUDENT

　　这是一些为Microsoft SQL Server表和视图命名的指导方针：
　　使用数据库名字和用户名字是可选的。如果一个表只通过名字加以引用（例如，STUDENT
　　当前数据库中以当前用户帐号搜索该表。如果没有找到，就在数据库中寻找由dbo的保留
　　同样名字的对象。表名在同一个数据库中的同一个用户帐号下必须是唯一的。
　　同一个SQL Server登录账号可以在多个数据库中拥有同样名字的表。例如，ENDUSER1账号
　　象：USER_DB.ENDUSER1.STUDENT和OTHER_DB.ENDUSER1.STUDENT。这里所加的限制是数据
　　 Server登录名，因为两者不一定要一样。
　　同时，这些数据库的其他用户可以有同样名字的对象：
　　USER_DB.DBO.STUDENT
　　USER_DB.DEPT_ADMIN.STUDENT
　　USER_DB.STUDENT_ADMIN.STUDENT
　　OTHER_DB.DBO.STUDENT

　　因此，建议你在引用数据库对象时包含所有者的名字。如果应用程序有多个数据库，建议
　　也包含在引用中。如果查询跨越多个服务器，还要包括服务器名。
　　SQL Server的每个连接都有一个当前数据库上下文，这是在登录时用USE语句设置的。例
　　景：
　　一个用户，使用ENDUSER1账号，登录到USER_DB数据库上。用户请求STUDENT表。SQL
　　STUDENT表。如果找到，SQL Server就在USER_DB.ENDUSER1.STUDENT表上做要求的数据库
　　ENDUSER1数据库账号下没有找到该表，SQL Server就为该数据库以dbo账号搜寻
　　还是找不到该表，SQL Server就返回一个错误消息，指出该表不存在。
　　如果另一个用户，例如DEPT_ADMIN拥有该表，则该表必须以数据库用户名作为前缀（
　　另外，数据库名字缺省为在当前上下文中的数据库名字。
　　如果被引用的表在另一个数据库中，则数据库名必须作为引用的一部分。例如，要访问在
　　ENDUSER1拥有的表STUDENT，就需要用OTHER_DB.ENDUSER1.STUDENT来引用。
　　可以在数据库和表名之间加两个点号来省略对象的所有者名。例如，如果应用程序引用
　　，SQL Server就做如下搜寻：
　　STUDENT_DB.current_user.STUDENT
　　STUDENT_DB.DBO.STUDENT

　　如果应用程序一次只使用一个数据库，在做对象引用时省略数据库名字，这样的话，该应
　　用于其他数据库。所有的对象引用都隐含的访问当前所用的数据库。这对于你要想在同一
　　个测试数据库和一个产品数据库时很有用

　　创建表
　　因为Oracle和SQL Server都支持SQL-92条目级（entry-level）的关于标识RDBMS对象的协
　　的语法是相似的。
　　Oracle Microsoft SQL　
　　CREATE TABLE
　　[schema.]table_name
　　(
　　{col_name column_properties
　　[default_expression] [constraint [constraint

　　[...constraint]]]| [[,] constraint]}
　　[[,] {next_col_name | next_constraint}...]
　　)
　　[Oracle Specific Data Storage Parameters] CREATE TABLE [
server.][database.][owner.] table_name
　　(
　　{col_name column_properties[constraint
　　[constraint [...constraint]]]| [[,] constraint]}
　　[[,] {next_col_name | next_constraint}...]
　　)
　　[ON filegroup_name]

　　Oracle数据库对象名字是不分大小写的。在Microsoft SQL Server中，数据库对象的名字
　　的，这要看安装时的设置。
　　当SQL Server第一次设置的时候，缺省的排序顺序是字典顺序，区分大小写。（可以用
　　不同的设置）因为Oracle对象的名字总是唯一的，你在把数据库对象移植到SQL Server上
　　烦。建议你把Oracle和SQL Server中的所有的表和列的名字都写成大写的以避免万一有用
　　的SQL Server时出问题。

　　表和索引存储参数
　　对于Microsoft SQL Server，使用RAID通常可以简化数据库对象的放置。在表的结构中集
　　的分簇的索引，就像一个Oracle索引组织表一样。
　　Oracle Microsoft SQL　
　　CREATE TABLE DEPT_ADMIN.DEPT (
　　DEPTVARCHAR2(4) NOT NULL,
　　DNAMEVARCHAR2(30) NOT NULL,
　　CONSTRAINT DEPT_DEPT_PK
　　PRIMARY KEY (DEPT)
　　USING INDEX TABLESPACE USER_DATA
　　PCTFREE 0 STORAGE (INITIAL 10K NEXT 10K
　　MINEXTENTS 1 MAXEXTENTS UNLIMITED),
　　CONSTRAINT DEPT_DNAME_UNIQUE
　　UNIQUE (DNAME)
　　USING INDEX TABLESPACE USER_DATA
　　PCTFREE 0 STORAGE (INITIAL 10K NEXT 10K
　　MINEXTENTS 1 MAXEXTENTS UNLIMITED)
　　)
　　PCTFREE 10PCTUSED 40
　　TABLESPACE USER_DATA
　　STORAGE (INITIAL 10K NEXT 10K
　　MINEXTENTS 1 MAXEXTENTS UNLIMITED
　　FREELISTS 1) CREATE TABLE USER_DB.DEPT_ADMIN.DEPT (
　　DEPTVARCHAR(4) NOT NULL,
　　DNAMEVARCHAR(30) NOT NULL,
　　CONSTRAINT DEPT_DEPT_PK
　　PRIMARY KEY CLUSTERED (DEPT),
　　CONSTRAINT DEPT_DNAME_UNIQUE
　　UNIQUE NONCLUSTERED (DNAME)
　　)

　　用SELECT语句创建表
　　使用Oracle，一个表可以用任何有效的SELECT命令创建。Microsoft SQL Server提供了同
　　法不一样。
　　Oracle Microsoft SQL　
　　CREATE TABLE STUDENTBACKUP AS SELECT * FROM STUDENT SELECT * INTO
　　FROM STUDENT

　　要SELECT…INTO能够起作用，必须将使用该程序的数据库的选项select into/bulkcopy设
　　库所有者可以用SQL Server Enterprise Manager或者Transact-SQL的sp_dboption系统存
　　项）。用sp_helpdb系统存储过程来检查数据库的状态。如果select into/bulkcopy未设
　　以用SELECT语句拷贝到临时表中，就像下面这样：
　　SELECT * INTO #student_backup FROM user_db.student_admin.student
　　当用SELECT.. INTO语句来创建新的表时，其参考的完整性定义不会转换到新的表中。
　　将select into/bulkcopy设定为true的要求可能会使移植的过程变得复杂。如果你必须用
　　据到表中，请首先创建表，然后再用INSERT INTO…SELECT语句来载入该表。对于Oracle
　　语法是一样的，也不需要设置任何数据库选项。

　　视图
　　在Microsoft SQL Server中创建视图的语法同Oracle一样。
　　Oracle Microsoft SQL　
　　CREATE [OR REPLACE] [FORCE |
　　NOFORCE] VIEW [schema.]view_name
　　[(column_name [, column_name]...)]
　　AS select_statement
　　[WITH CHECK OPTION [CONSTRAINT
　　name]]
　　[WITH READ ONLY] CREATE VIEW [owner.]view_name
　　[(column_name [, column_name]...)]
　　[WITH ENCRYPTION]
　　AS select_statement [WITH CHECK OPTION]


　　SQL Server视图要求表必须存在，并且视图的所有者必须有访问在SELECT语句中标明的数
　　Oracle中的FORCE选项相似）。
　　缺省情况下，不会检查视图上的数据修改语句来判定受影响的行是否在视图的范围内。要
　　请使用WITH CHECK OPTION。对于WITH CHECK OPTION主要的不同之处在于，Oracle将其作
　　SQL Server不是。此外，两者的功能是一样的。
　　在定义视图的时候，Oracle提供了一个WITH READ ONLY选项。SQL Server应用程序可以用
　　SELECT权限的方法来达到同样的结果。
　　SQL Server和Oracle视图都支持派生列、使用数学表达式、函数以及常量表达式。SQL
　　不同之处是：
　　如果数据修改只影响一个基本表，则数据修改语句（INSERT或者UPDATE）可以存在于多个
　　中的数据修改语句不能用在超过一个表上。
　　READTEXT或者WRITETEXT不能用于视图中的列。
　　不能使用ORDER BY、COMPUTE、FOR BROWSE、或者COMPUTE BY子句。
　　在视图中不能使用INTO关键字。
　　当一个视图是和一个外部连接一起定义的，并且查询限定在外部接合点的内部表上时，
　　的结果会有所不同。在大多数情况下，Oracle视图很容易转化为SQL Server视图。
　　Oracle Microsoft SQL　
　　CREATE VIEW STUDENT_ADMIN.STUDENT_GPA
　　(SSN, GPA)
　　AS SELECT SSN, ROUND(AVG(DECODE(grade

　　,'A', 4
　　,'A+', 4.3
　　,'A-', 3.7
　　,'B', 3
　　,'B+', 3.3
　　,'B-', 2.7
　　,'C', 2
　　,'C+', 2.3
　　,'C-', 1.7
　　,'D', 1
　　,'D+', 1.3
　　,'D-', 0.7
　　,0)),2)
　　FROM STUDENT_ADMIN.GRADE
　　GROUP BY SSN CREATE VIEW STUDENT_ADMIN.STUDENT_GPA
　　(SSN, GPA)
　　AS SELECT SSN, ROUND(AVG(CASE grade
　　WHEN 'A' THEN 4
　　WHEN 'A+' THEN 4.3
　　WHEN 'A-' THEN 3.7
　　WHEN 'B' THEN 3
　　WHEN 'B+' THEN 3.3
　　WHEN 'B-' THEN 2.7
　　WHEN 'C' THEN 2
　　WHEN 'C+' THEN 2.3
　　WHEN 'C-' THEN 1.7
　　WHEN 'D' THEN 1
　　WHEN 'D+' THEN 1.3
　　WHEN 'D-' THEN 0.7
　　ELSE 0
　　END),2)
　　FROM STUDENT_ADMIN.GRADE
　　GROUP BY SSN