ORACLE向SQL转换参考（转)

SQL 语言支持

这一部分概述了 Transact-SQL 和 PL/SQL 语言语法之间的相同点和不同点，并给出转换策略。

要将 Oracle DML 语句和 PL/SQL 程序迁移到 SQL Server 时，请按下列步骤执行：

1. 验证所有 SELECT、INSERT、UPDATE 和 DELETE 语句的语法是有效的。进行任何必要的修改。
2. 把所有外部联接改为 SQL-92 标准外部联接语法。
3. 用相应 SQL Server 函数替代 Oracle 函数。
4. 检查所有的比较运算符。
5. 用“+”字符串串联运算符代替“||”字符串串联运算符。
6. 用 Transact-SQL 程序代替 PL/SQL 程序。
7. 把所有 PL/SQL 游标改为非游标 SELECT 语句或 Transact-SQL 游标。
8. 用 Transact-SQL 过程代替 PL/SQL 过程、函数和包。
9. 把 PL/SQL 触发器转换为 Transact-SQL 触发器。
10. 使用 SET SHOWPLAN 语句，优化查询性能。

SELECT 语句

Oracle 和 Microsoft SQL Server 使用的 SELECT 语句语法类似。

Oracle	Microsoft SQL Server
SELECT [/*+ optimizer_hints*/] [ALL | DISTINCT] select_list [FROM { table_name | view_name | select_statement}] [WHERE clause] [GROUP BY group_by_expression] [HAVING search_condition] [START WITH U CONNECT BY] [{ UNION | UNION ALL | INTERSECT | MINUS} SELECT U] [ORDER BY clause] [FOR UPDATE]	SELECT select_list [INTO new_table_] FROM table_source [WHERE search_condition] [ GROUP BY [ALL] group_by_expression [,Un] [ WITH { CUBE | ROLLUP } ] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC] ] In addition: UNION Operator COMPUTE Clause FOR BROWSE Clause OPTION Clause

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 FROM DEPT_ADMIN.CLASS INTERSECT SELECT C.CCODE, C.CNAME FROM STUDENT_ADMIN.GRADE G, DEPT_ADMIN.CLASS C WHERE C.CCODE = G.CCODE	SELECT CCODE, CNAME FROM DEPT_ADMIN.CLASS C WHERE EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE G WHERE C.CCODE = G.CCODE)

在此例中，使用 MINUS 运算符，查找那些没有任何学生登记的课程。

Oracle	Microsoft SQL Server
SELECT CCODE, CNAME FROM DEPT_ADMIN.CLASS MINUS SELECT C.CCODE, C.CNAME FROM STUDENT_ADMIN.GRADE G, DEPT_ADMIN.CLASS C WHERE C.CCODE = G.CCODE	SELECT CCODE, CNAME FROM DEPT_ADMIN.CLASSC WHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE G WHERE C.CCODE = G.CCODE)

INSERT 语句

Oracle 和 Microsoft SQL Server 使用的 INSERT 语句语法类似。

Oracle	Microsoft SQL Server
INSERT INTO {table_name | view_name | select_statement} [(column_list)] { values_list | select_statement}	INSERT [INTO] { table_name [ [AS] table_alias] WITH ( <table_hint_limited> [Un]) | view_name [ [AS] table_alias] | rowset_function_limited } { [(column_list)] { VALUES ( { DEFAULT | NULL | expression }[,Un] ) | derived_table | execute_statement } } | DEFAULT VALUES

Transact-SQL 语言支持对表和视图的插入，但不支持对 SELECT 语句的 INSERT 操作。如果 Oracle 应用程序代码执行对 SELECT 语句的插入操作，则必须对它进行修改。

Oracle	Microsoft SQL Server
INSERT INTO (SELECT SSN, CCODE, GRADE FROM GRADE) VALUES ('111111111', '1111',NULL)	INSERT INTO GRADE (SSN, CCODE, GRADE) VALUES ('111111111', '1111',NULL)

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 {table_name | view_name | select_statement} SET [column_name(s) = {constant_value | expression | select_statement | column_list | variable_list] {where_statement}

UPDATE { table_name [ [AS] table_alias] WITH ( <table_hint_limited> [Un]) view_name [ [AS] table_alias] | rowset_function_limited } SET { column_name = { expression | DEFAULT | NULL} | @variable = expression | @variable = column = expression } [,Un] { {[FROM {<table_source>} [,Un] ] [WHERE <search_condition>] } | [WHERE CURRENT OF { { [GLOBAL] cursor_name } | cursor_variable_name} ] } [OPTION (<query_hint> [,Un] )]

Transact-SQL UPDATE 语句不支持对 SELECT 语句的更新操作。如果 Oracle 应用程序代码对 SELECT 语句进行更新，则可以把 SELECT 语句转换成一个视图，然后在 SQL Server UPDATE 语句中使用该视图名称。请参见前面“INSERT 语句”中的示例。

Oracle UPDATE 命令只能使用一个 PL/SQL 块中的程序变量。要使用变量，Transact-SQL 语言并不需要使用块。

Oracle	Microsoft SQL Server
DECLARE VAR1 NUMBER(10,2); BEGIN VAR1 := 2500; UPDATE STUDENT_ADMIN.STUDENT SET TUITION_TOTAL = VAR1; END;	DECLARE @VAR1 NUMERIC(10,2) SELECT @VAR1 = 2500 UPDATE STUDENT_ADMIN.STUDENT SET TUITION_TOTAL=@VAR1

在 SQL Server 中，DEFAULT 关键字可用于将一列设为其默认值。但不能使用 Oracle UPDATE 命令，将一列设为默认值。

Transact-SQL 和 Oracle SQL 均支持在 UPDATE 语句中使用子查询。但是，Transact-SQL FROM 子句可用来创建一个基于联接的 UPDATE。这一功能使 UPDATE 语法可读性更好，在某些情况下还能改善性能。

Oracle	Microsoft SQL Server
UPDATE STUDENT_ADMIN.STUDENT S SET TUITION_TOTAL = 1500 WHERE SSN IN (SELECT SSN FROM GRADE G WHERE G.SSN = S.SSN AND G.CCODE = '1234')	Subquery: UPDATE STUDENT_ADMIN.STUDENT S SET TUITION_TOTAL = 1500 WHERE SSN IN (SELECT SSN FROM GRADE G WHERE G.SSN = S.SSN AND G.CCODE = '1234') FROM clause: UPDATE STUDENT_ADMIN.STUDENT S SET TUITION_TOTAL = 1500 FROM GRADE G WHERE S.SSN = G.SSN AND G.CCODE = '1234'

DELETE 语句

在大多数情况下，不需要修改 DELETE 语句。如果要对 Oracle 中的 SELECT 语句执行删除操作，则必须修改 SQL Server 语法，因为 Transact-SQL 不支持这一功能。

Transact-SQL 支持在 WHERE 子句中使用子查询，以及在 FROM 子句中使用联接。后者可产生更有效的语句。请参见前面“UPDATE 语句”中的示例。

Oracle	Microsoft SQL Server
DELETE [FROM] { table_name | view_name | select_statement} [WHERE clause]	DELETE [FROM ] { table_name [ [AS] table_alias] WITH ( <table_hint_limited> [Un]) | view_name [ [AS] table_alias] | rowset_function_limited } [ FROM {<table_source>} [,Un] ] [WHERE { <search_condition> | { [ CURRENT OF { { [ GLOBAL ] cursor_name } cursor_variable_name } ] } ] [OPTION (<query_hint> [,Un])]

TRUNCATE TABLE 语句

Oracle 和 Microsoft SQL Server 使用的 TRUNCATE TABLE 语句语法类似。TRUNCATE TABLE 用于从表中删除所有的行，并且不能回滚。表结构及其所有索引继续存在。DELETE 触发器不执行。如果表被一个 FOREIGN KEY 约束引用，则它不能被截断。

Oracle	Microsoft SQL Server
TRUNCATE TABLE table_name [{DROP | REUSE} STORAGE]	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, FNAME, LNAME FROM STUDENT_ADMIN.STUDENT S, DEPT_ADMIN.CLASS C, STUDENT_ADMIN.GRADE G WHERE S.SSN = G.SSN(+) AND G.CCODE = C.CCODE(+)	SELECT S.SSN AS SSN, FNAME, LNAME FROM STUDENT_ADMIN.GRADE G RIGHT OUTER JOIN STUDENT_ADMIN.STUDENT S ON G.SSN = S.SSN LEFT OUTER JOIN DEPT_ADMIN.CLASS C ON G.CCODE = C.CCODE

将 SELECT 语句做为表名使用

Microsoft SQL Server 和 Oracle 均支持在执行查询时，把 SELECT 语句作为表的来源使用。SQL Server 需要一个别名；对 Oracle，别名的使用是可选的。

Oracle	Microsoft SQL Server
SELECT SSN, LNAME, FNAME, TUITION_PAID, SUM_PAID FROM STUDENT_ADMIN.STUDENT, (SELECT SUM(TUITION_PAID) SUM_PAID FROM STUDENT_ADMIN.STUDENT)	SELECT SSN, LNAME, FNAME, TUITION_PAID, SUM_PAID FROM STUDENT_ADMIN.STUDENT, (SELECT SUM(TUITION_PAID) SUM_PAID FROM STUDENT_ADMIN.STUDENT) SUM_STUDENT

读取和修改 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
字符串中每个词的第一个字母大写