SQL 语言支持
这一部分概述了 Transact-SQL 和 PL/SQL 语言语法之间的相同点和不同点,并给出转换策略。
要将 Oracle DML 语句和 PL/SQL 程序迁移到 SQL Server 时,请按下列步骤执行:
- 验证所有 SELECT、INSERT、UPDATE 和 DELETE 语句的语法是有效的。进行任何必要的修改。
- 把所有外部联接改为 SQL-92 标准外部联接语法。
- 用相应 SQL Server 函数替代 Oracle 函数。
- 检查所有的比较运算符。
- 用“+”字符串串联运算符代替“||”字符串串联运算符。
- 用 Transact-SQL 程序代替 PL/SQL 程序。
- 把所有 PL/SQL 游标改为非游标 SELECT 语句或 Transact-SQL 游标。
- 用 Transact-SQL 过程代替 PL/SQL 过程、函数和包。
- 把 PL/SQL 触发器转换为 Transact-SQL 触发器。
- 使用 SET SHOWPLAN 语句,优化查询性能。
SELECT 语句
Oracle 和 Microsoft SQL Server 使用的 SELECT 语句语法类似。
Oracle |
Microsoft SQL Server |
SELECT [/*+ optimizer_hints*/] |
SELECT select_list |
SQL Server 不支持 Oracle 特定的基于开销的优化程序提示,它必须被删除。建议使用的技术是,使用 SQL Server 基于开销的优化程序。有关详细信息,请参见本章后面的“SQL 语句优化”。
SQL Server 不支持 Oracle 的 START WITHUCONNECT BY 子句。在 SQL Server 中,可以创建完成相同任务的存储过程替代它。
SQL Server 不支持 Oracle 的 INTERSECT 和 MINUS 集合运算符。可使用 SQL Server EXISTS 和 NOT EXISTS 子句,实现相同的结果。
在下面示例中,使用 INTERSECT 运算符,用于查找学生登记的所有课程的代码和名称。注意,EXISTS 运算符是如何代替 INTERSECT 运算符的。返回的数据是相同的。
Oracle |
Microsoft SQL Server |
SELECT CCODE, CNAME |
SELECT CCODE, CNAME |
在此例中,使用 MINUS 运算符,查找那些没有任何学生登记的课程。
Oracle |
Microsoft SQL Server |
SELECT CCODE, CNAME |
SELECT CCODE, CNAME |
INSERT 语句
Oracle 和 Microsoft SQL Server 使用的 INSERT 语句语法类似。
Oracle |
Microsoft SQL Server |
INSERT INTO |
INSERT [INTO] |
Transact-SQL 语言支持对表和视图的插入,但不支持对 SELECT 语句的 INSERT 操作。如果 Oracle 应用程序代码执行对 SELECT 语句的插入操作,则必须对它进行修改。
Oracle |
Microsoft SQL Server |
INSERT INTO (SELECT SSN, CCODE, GRADE FROM GRADE) |
INSERT INTO GRADE (SSN, CCODE, GRADE) |
Transact-SQL values_list 参数提供了 SQL-92 标准关键字 DEFAULT,但 Oracle 不支持。此关键字指定了,执行插入操作时使用列的默认值。如果指定列的默认值不存在,则插入 NULL。如果该列不允许 NULL,则返回一个错误消息。如果该列数据类型定义为 timestamp,则插入下一个有序值。
标识符列不能使用 DEFAULT 关键字。要生成下一个序列号,拥有 IDENTITY 属性的列不能列在 column_list 或 values_clause 中。不需使用 DEFAULT 关键字,来获取列的默认值。正如在 Oracle 中,如果列没有在 column_list 中引用,并且它有默认值,则默认值存放在列中。这是迁移时可使用的最兼容的方法。
一个有用的 Transact_SQL 选项 (EXECute procedure_name) 是,执行一个过程并将其结果用管道输出到目标表或视图中。Oracle 不允许这样做。
UPDATE 语句
因为 Transact SQL 支持 Oracle UPDATE 命令使用的绝大多数语法,所以只需要极少的修改。
Oracle |
Microsoft SQL Server |
UPDATE |
UPDATE |
Transact-SQL UPDATE 语句不支持对 SELECT 语句的更新操作。如果 Oracle 应用程序代码对 SELECT 语句进行更新,则可以把 SELECT 语句转换成一个视图,然后在 SQL Server UPDATE 语句中使用该视图名称。请参见前面“INSERT 语句”中的示例。
Oracle UPDATE 命令只能使用一个 PL/SQL 块中的程序变量。要使用变量,Transact-SQL 语言并不需要使用块。
Oracle |
Microsoft SQL Server |
DECLARE |
DECLARE |
在 SQL Server 中,DEFAULT 关键字可用于将一列设为其默认值。但不能使用 Oracle UPDATE 命令,将一列设为默认值。
Transact-SQL 和 Oracle SQL 均支持在 UPDATE 语句中使用子查询。但是,Transact-SQL FROM 子句可用来创建一个基于联接的 UPDATE。这一功能使 UPDATE 语法可读性更好,在某些情况下还能改善性能。
Oracle |
Microsoft SQL Server |
UPDATE |
Subquery: |
DELETE 语句
在大多数情况下,不需要修改 DELETE 语句。如果要对 Oracle 中的 SELECT 语句执行删除操作,则必须修改 SQL Server 语法,因为 Transact-SQL 不支持这一功能。
Transact-SQL 支持在 WHERE 子句中使用子查询,以及在 FROM 子句中使用联接。后者可产生更有效的语句。请参见前面“UPDATE 语句”中的示例。
Oracle |
Microsoft SQL Server |
DELETE [FROM] |
DELETE |
TRUNCATE TABLE 语句
Oracle 和 Microsoft SQL Server 使用的 TRUNCATE TABLE 语句语法类似。TRUNCATE TABLE 用于从表中删除所有的行,并且不能回滚。表结构及其所有索引继续存在。DELETE 触发器不执行。如果表被一个 FOREIGN KEY 约束引用,则它不能被截断。
Oracle |
Microsoft SQL Server |
TRUNCATE TABLE table_name |
TRUNCATE TABLE table_name |
在 SQL Server 中,此语句只能由表的所有者执行。在 Oracle 中,如果是表的所有者或拥有 DELETE TABLE 系统权限,就可以执行此命令。
Oracle TRUNCATE TABLE 命令可以有选择地释放表中行所占用的存储空间。SQL Server TRUNCATE TABLE 语句总是收回表数据及其相关索引所占用的空间。
标识符列和时间戳列中数据的处理
Oracle 序列是与任何给定的表或列均不直接相关的数据库对象。列和序列之间的关系是在应用程序中实现的,即通过编程的方法将序列值赋给列。因此,Oracle 使用序列时,并不实施任何规则。但是,在 Microsoft SQL Server 标识符列中,值不能被更新,并且不能使用 DEFAULT 关键字。
默认情况下,数据不能直接插入到标识符列。标识符列自动给表中插入的每个新行生成一个唯一的序列号。可以使用下列 SET 语句改写这种默认设置:
SET IDENTITY_INSERT table_name ON
将 IDENTUTY_INSERT 设为 ON,用户就可以向新行的标识符列插入任何值。要防止出现有重复号码的条目,必须为该列创建唯一索引。这条语句的目的是,允许用户给无意中删除的行重新创建一个值。@@IDENTITY 函数可用来获取上一个标识值。
TRUNCATE TABLE 语句将标识符列重置为其起始 SEED 值。如果不想重置列的标识值,则不使用 TRUNCATE TABLE 语句,而使用不带 WHERE 子句的 DELETE 语句。必须评估它对 Oracle 迁移造成的影响,因为 ORACLE SEQUENCE 在 TRUNCATE TABLE 命令之后不被重置。
处理 timestamp 列时,只能执行插入和删除。如果要更新一个 timestamp 列,会收到以下的错误信息:
Msg 272, Level 16, State 1 Can't update a TIMESTAMP column.
锁定请求的行
Oracle 使用 FOR UPDATE 子句来锁定 SELECT 命令中指定的行。不需要在 Microsoft SQL Server 中使用对等的子句,因为这是默认行为。
行合计和 COMPUTE 子句
SQL Server COMPUTE 子句用于生成行合计函数(SUM、AVG、MIN、MAX 和 COUNT),它们在查询结果中作为附加行出现。它允许查看一组结果的详细和汇总信息行。可以计算子组的汇总值,以及计算同一组的多个合计函数。
Oracle SELECT 命令语法不支持 COMPUTE 子句。但是,SQL Server COMPUTE 子句与 Oracle SQL*Plus 查询工具中的 COMPUTE 命令作用相似。
联接子句
Microsoft SQL Server 7.0 允许在一个联接子句中最多可联接 256 个表,包括临时表和永久表。在 Oracle 中,则没有联接限制。
在 Oracle 中使用外部联接时,外部联接运算符 (+) 通常放在该联接的子列(外键)旁边。(+) 标识了具有较少唯一值的列。情况一般是这样,除非外键允许空值,在这种情况下,(+) 被放在父(PRIMARY KEY 或 UNIQUE 约束)列上。(+) 不能放在等号 (=) 两边。
在 SQL Server 中,可以使用 *= 和 =* 外部联接运算符。* 用于标识有较多唯一值的列。如果子(外键)列不允许空值,则 * 放在等号的父(PRIMARY KEY 或 UNIQUE 约束)列一边。* 的位置和 Oracle 完全相反。* 不能放在等号 (=) 两边。
*= 和 =* 被认为是旧式联接运算符。SQL Server 也支持下面列出的 SQL-92 标准联接运算符。建议使用这种语法。SQL-92 标准语法功能更强大,并且比 * 运算符的限制要少。
联接操作 |
说明 |
CROSS JOIN |
这是两个表的交叉乘积。它与旧式联接中未指定 WHERE 子句而返回的行相同。在 Oracle 中,这种类型联接称为笛卡尔联接。 |
INNER |
这种联接指定,所有内部行均要返回。丢弃任何不匹配的行。这和标准的 Oracle 表联接相同。 |
LEFT[OUTER] |
这种类型的联接指定,所有左表的外部行均要返回,即使没找到匹配的列,也就如此。这和 Oracle 外部联接 (+) 的操作类似。 |
RIGHT[OUTER] |
这种类型的联接指定,所有右表的外部行均要返回,即使没找到匹配的列,也是如此。这和 Oracle 外部联接 (+) 的操作类似。 |
FULL [OUTER] |
如果两个表中的一行不符合选择标准,则指定将这一行加到结果集中,并且将其对应于另一表的输出列设为 NULL。这和将 Oracle 外部联接运算符放在“=”号两边 (col1(+)=col2(+)) 的效果是一样的,但后者在 Oracle 中是不允许的。 |
下面的代码示例返回所有学生登记的课程列表。外部联接定义在学生 (student) 和成绩 (grade) 表之间,成绩表允许所有的学生出现在上面,甚至那些没有登记任何课程的学生也是如此。外部联接也加到课程表上,以返回课程名称。如果外部联接不加到课程表上,就不会返回那些没有登记任何课程的学生,因为他们的课程代码 (CCODE) 为空。
Oracle |
Microsoft SQL Server |
SELECT S.SSN AS SSN, |
SELECT S.SSN AS SSN, |
将 SELECT 语句做为表名使用
Microsoft SQL Server 和 Oracle 均支持在执行查询时,把 SELECT 语句作为表的来源使用。SQL Server 需要一个别名;对 Oracle,别名的使用是可选的。
Oracle |
Microsoft SQL Server |
SELECT SSN, LNAME, FNAME, |
SELECT SSN, LNAME, FNAME, |
读取和修改 BLOB
Microsoft SQL Server 使用 text 和 image 列,来实现二进制大型对象 (BLOB)。Oracle 使用 LONG 和 LONG RAW 列实现 BLOB。在 Oracle 中,SELECT 命令可以查询 LONG 和 LONG RAW 列中的值。
在 SQL Server 中,可以使用标准的 Transact-SQL 语句或专门的 READTEXT 语句读取 text 和 image 列中的数据。READTEXT 语句允许读取 text 或 image 列的部分片段。Oracle 没有提供处理 LONG 和 LONG RAW 的对等语句。
READTEXT 语句使用 text_pointer,它可以用 TEXTPTR 函数获得。TEXTPTR 函数返回一个指针,它指向指定行中的 text 或 image 列,或如果返回不止一行,则它指向查询返回的最后一行的 text 或 image 列。因为 TEXTPTR 函数返回一个 16 字节的二进制字符串,最好声明一个局部变量来存放文本指针,然后由 READTEXT 使用该变量。
READTEXT 语句指定要返回的字节数。@@TEXTSIZE 函数中的值是要返回的字符或字节数的限度,如果它小于 READTEXT 指定的大小,就会替代 READTEXT 语句指定值。
可使用带 TEXTSIZE 参数的 SET 语句,指定 SELECT 语句返回的文本数据的大小(字节数)。如果指定 TEXTSIZE 为 0,其大小被重置为默认值 (4 KB)。设置 TEXTSIZE 参数,会影响 @@TEXTSIZE 函数。当 SQL_MAX_LENGTH 语句选项更改时,SQL Server ODBC 驱动程序就会自动设置 TEXTSIZE 参数。
在 Oracle 中,UPDATE 和 INSERT 命令用于更改 LONG 和 LONG RAW 列中的值。在 SQL Server 中,可以使用标准的 UPDATE 和 INSERT 语句,也可以使用 UPDATETEXT 和 WRITETEXT 语句。UPDATETEXT 和 WRITETEXT 均允许无日志记录的选项,并且 UPDATETEXT 允许对 text 或 image 列进行部分更新。
UPDATETEXT 语句可用于替换现有数据、删除现有数据或插入新数据。新插入的数据可以是常量、表名、列名或文本指针。
WRITETEXT 语句可完全覆盖受其影响的列中的任何现有数据。使用 WRITETEXT 可替换文本数据;使用 UPDATETEXT 可修改文本数据。UPDATETEXT 语句更加灵活,因为它只更改一部分文本或图像值,而不是更改全部。
有关详细信息,请参见 SQL Server Books Online。
本节中的表给出了 Oracle 和 SQL Server 标量值函数和合计函数之间的关系。尽管名称看起来是相同的,但要注意,函数参数的数量和类型是不同的,这一点非常重要。此外,在这个列表中,没有给出仅由 Microsoft SQL Server 提供的函数,因为本章仅限于讲述,如何方便地实现从现有 Oracle 应用程序的迁移。Oracle 不支持函数的例子有:角度 (DEGREES)、圆周率 (PI) 和随机数 (RAND)。
数字/数学函数
下面是 Oracle 支持的数字/数学函数及其 Microsoft SQL Server 对等函数。
函数 |
Oracle |
Microsoft SQL Server |
绝对值 |
ABS |
ABS |
反余弦 |
ACOS |
ACOS |
反正弦 |
ASIN |
ASIN |
n 的反正切 |
ATAN |
ATAN |
m/n 的反正切 |
ATAN2 |
ATN2 |
>=值的最小整数 |
CEIL |
CEILING |
余弦 |
COS |
COS |
双曲余弦 |
COSH |
COT |
指数值 |
EXP |
EXP |
<=值的最大整数 |
FLOOR |
FLOOR |
自然对数 |
LN |
LOG |
以任何为底的对数 |
LOG(N) |
暂缺 |
以 10 为底的对数 |
LOG(10) |
LOG10 |
模数(余数) |
MOD |
USE MODULO (%) OPERATOR |
幂 |
POWER |
POWER |
随机数 |
暂缺 |
RAND |
舍入 |
ROUND |
ROUND |
数的符号 |
SIGN |
SIGN |
正弦 |
SIN |
SIN |
双曲正弦 |
SINH |
暂缺 |
平方根 |
SQRT |
SQRT |
正切 |
TAN |
TAN |
双曲正切 |
TANH |
暂缺 |
截尾 |
TRUNC |
暂缺 |
列表中的最大数 |
GREATEST |
暂缺 |
列表中的最小数 |
LEAST |
暂缺 |
如果为 NULL,转换成数字 |
NVL |
ISNULL |
字符函数
下面是 Oracle 支持的字符函数及其 Microsoft SQL Server 对等函数。
函数 |
Oracle |
Microsoft SQL Server |
把字符转换成 ASCII |
ASCII |
ASCII |
字符串串联 |
CONCAT |
(表达式 + 表达式) |
把 ASCII 转换成字符 |
CHR |
CHAR |
返回字符串中的起始字符(从左) |
INSTR |
CHARINDEX |
将字符转换成小写 |
LOWER |
LOWER |
将字符转换成大写 |
UPPER |
UPPER |
在字符串的左边填充字符 |
LPAD |
暂缺 |
删除前导空格 |
LTRIM |
LTRIM |
删除尾空格 |
RTRIM |
RTRIM |
字符串中模式的起始点 |
INSTR |
PATINDEX |
多次重复字符串 |
RPAD |
REPLICATE |
字符串的语音表示 |
SOUNDEX |
SOUNDEX |
重复空格的字符串 |
RPAD |
SPACE |
从数字数据转换而来的字符数据 |
TO_CHAR |
STR |
子串 |
SUBSTR |
SUBSTRING |
字符替换 |
REPLACE |
STUFF |
字符串中每个词的第一个字母大写 |