oracle 与 sql server语法函数比较

异种数据库函数和标准SQL语法比较
0 引言

  在实际工作当中，常常会遇到同种功能在异种数据库中函数的实现方法和SQL语法的实现方法，时间的累积效应显示，提高这部分的空缺，无疑对使用数据库的人员来说，都有非常现实的意义。

   从另外一翼来说，异种数据库函数和标准SQL语法等的比较，对于开发二次平台提供一个有力的参照，将尽快形成属于自己的一套语法和函数体系。最终使应用开发端和后台数据库实现无关联性，即不论后台数据库系统如何变化，通过二次开发平台转换之后，应用程序不作改动，或尽量少地改动。

1.异种数据库函数比较
1.1字符串函数比较

下面是Oracle支持的字符函数和它们的Microsoft SQL Server等价函数。

函数

Oracle

Microsoft SQL Server

把字符转换为ASCII

ASCII

ASCII

字串连接

CONCAT

(expression + expression)

把ASCII转换为字符

CHR

CHAR

返回字符串中的开始字符（左起）

INSTR

CHARINDEX

把字符转换为小写

LOWER

LOWER

把字符转换为大写

UPPER

UPPER

填充字符串的左边

LPAD

N/A

清除开始的空白

LTRIM

LTRIM

清除尾部的空白

RTRIM

RTRIM

字符串中的起始模式（pattern）

INSTR

PATINDEX

多次重复字符串

RPAD

REPLICATE

字符串的语音表示

SOUNDEX

SOUNDEX

重复空格的字串

RPAD

SPACE

从数字数据转换为字符数据

TO_CHAR

STR

子串

SUBSTR

SUBSTRING

替换字符

REPLACE

STUFF

将字符串中的每个词首字母大写

INITCAP

N/A

翻译字符串

TRANSLATE

N/A

字符串长度

LENGTH

DATELENGTH or LEN

列表中最大的字符串

GREATEST

N/A

列表中最小的字符串

LEAST

N/A

如果为NULL则转换为相应的字符串

NVL

ISNULL

获取行号作为流水号

Rownum as cindex

Identity(int,1,1) as cindex

 

1.2 数字函数比较

下面是Oracle支持的数字／数学函数以及它们的Microsoft SQL Server等价函数。

函数功能介绍

Oracle

Microsoft SQL Server

绝对值

ABS

ABS

求余炫弧度

ACOS

ACOS

求正炫弧度

ASIN

ASIN

求N的弧度

ATAN

ATAN

求n和m的弧度

ATAN2

ATN2

最小整数 >= value

CEIL

CEILING

余炫

COS

COS

数的指数

EXP

EXP

最大整数值 <= value

FLOOR

FLOOR

求自然对数

LN

LOG

求对数

LOG(N)

N/A

求以10为底的对数

LOG(10)

LOG10

求模

MOD

USE MODULO (%) OPERATOR

求幂

POWER

POWER

求随机数

N/A

RAND

窃取范围

ROUND

ROUND

取符号

SIGN

SIGN

求正炫

SIN

SIN

求双曲线正炫

SINH

N/A

截取函数

TRUNC

N/A

转变函数

NVL

ISNULL

说明：

   本表中，设计的函数，其功能请参照文档《异种数据库初探》。

1.3 转换函数比较

下面是Oracle支持的转换函数和它们的Microsoft SQL Server等价函数。

函数

Oracle

Microsoft SQL Server

数字转换为字符

TO_CHAR

CONVERT

字符转换为数字

TO_NUMBER

CONVERT

日期转换为字符

TO_CHAR

CONVERT

字符转换为日期

TO_DATE

CONVERT

16进制转换为2进制

HEX_TO_RAW

CONVERT

2进制转换为16进制

RAW_TO_HEX

CONVERT

1.4 其它函数比较

    日期函数

下面是Oracle支持的日期函数和它们的Microsoft SQL Server等价函数。

函数

Oracle

Microsoft SQL Server

日期相加

(date column +/- value) or
ADD_MONTHS

DATEADD

两个日期的差

(date column +/- value) or
MONTHS_BETWEEN

DATEDIFF

当前日期和时间

SYSDATE

GETDATE()

一个月的最后一天

LAST_DAY

N/A

时区转换

NEW_TIME

N/A

日期后的第一个周日

NEXT_DAY

N/A

代表日期的字符串

TO_CHAR

DATENAME

代表日期的整数

TO_NUMBER
(TO_CHAR))

DATEPART

日期舍入

ROUND

CONVERT

日期截断

TRUNC

CONVERT

字符串转换为日期

TO_DATE

CONVERT

如果为NULL则转换为日期

NVL

ISNULL

2. 异种数据库SQL语法比较
2.1 查询语句语法比较

SQL Server不支持面向Oracle的基于开销的优化器，必须把这些清除掉。建议使用SQL Server的基于开销的优化器。

  2.1.1 ORACLE中查询语句的语法：

      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 … CONNECT BY]

[{UNION | UNION ALL | INTERSECT |

MINUS} SELECT …]

[ORDER BY clause]

[FOR UPDATE]

  2.1.2 SQL SERVER中查询语句的语法：

SELECT select_list

[INTO new_table_]

FROM table_source

[WHERE search_condition]

[ GROUP BY [ALL] group_by_expression [,…n]

 [ 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的START WITH…CONNECT 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)

 

2.2 插入语句语法比较

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

2.2.1 ORACLE的插入语句语法比较

INSERT INTO

{table_name | view_name | select_statement} [(column_list)]

{values_list | select_statement}

2.2.2 SQL SERVER的插入语句语法比较

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

INSERT [INTO]

    {

     table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n])

     | view_name [ [AS] table_alias]

     | rowset_function_limited

    }

 

    {    [(column_list)]

 { VALUES ( {    DEFAULT

   |    NULL

   |    expression

   }[,…n]

     )

 | derived_table

 | execute_statement   

 }

    }

    | DEFAULT VALUES

   说明：

Transact-SQL语言支持插入表和视图，但是不支持SELECT语句中的INSERT操作。如果你的Oracle程序这么做了，则必须修改。

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，则返回一个错误消息。

关键字DEFAULT不能用于标识列。要产生下一个连续值，则有IDENTITY属性的列一定不能列入column_list或者values_clause。你不一定非要用DEFAUL关键字来获得一列的缺省值。在Oracle中，如果该列没有被column_list引用并且它有一个缺省值，则缺省值将放入列中。这是执行移植时最兼容的方法。

一个有用的Transact-SQL选项（Execute procedure_name）是执行一个过程并且用管道把它的输出值输出到一个目标表或者视图。Oracle不允许你这样做。

2.3 修改语句语法比较

因为Transact-SQL支持Oracle的UPDATE绝大多数语法，所以只需要很小的修改。

2.3.1 ORACLE修改语句语法说明

  UPDATE{table_name | view_name |    select_statement}

SET [column_name(s) = {constant_value |

     expression | select_statement | column_list |

   variable_list}

{where_statement}

2.3.2 SQL SERVER修改语句语法说明

  UPDATE { table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n])view_name [ [AS] table_alias]| rowset_function_limited }

SET {column_name = {expression | DEFAULT | NULL}| @variable = expression | @variable = column = expression } [,…n]{{[

FROM {<table_source>} [,…n] ][

WHERE <search_condition>] }|

[WHERE CURRENT OF { { [GLOBAL] cursor_name } | cursor_variable_name} ] }[OPTION (<query_hint> [,…n] )]

说明：

   Transact-SQL的UPDATE语句不支持依赖SELECT语句的更新操作。如果你的Sql Server程序这样做了，你可以把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'

 

2.4 删除语句语法比较

在大多数情况下，不需要修改DELETE语句。但是如果你在Oracle中执行依赖SELECT语句的删除操作，你就必须进行修改，因为在Transact-SQL不支持这种功能。

Transact-SQL支持在WHERE子句中使用子查询，FROM子句也一样。后者可以产生更有效的语句。

2.4.1 ORACLE删除语句语法分析

DELETE

[FROM]{table_name | view_name |    select_statement}

[WHERE clause]

   2.4.2 SQL SERVER删除语句分析。

DELETE

[FROM ] { table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n])| view_name [ [AS] table_alias]| rowset_function_limited }[ FROM {<table_source>} [,…n] ]

[WHERE { <search_condition> |    { [ CURRENT OF { { [ GLOBAL ] cursor_name } cursor_variable_name }] }}[OPTION (<query_hint> [,…n])]

2.5 truncate语句语法分析

在Oracle和Microsoft SQL Server中TRUNCATE TABLE语句的语法是相似的。TRUNCATE TABLE语句用来把一个表中的所有行清除掉，并且这个操作是不能后滚的。该表的结构和索引仍然存在。DELETE触发器不会被执行。如果该表被一个FOREIGN KEY引用，则该表不能被砍掉。

2.5.1 ORACLE数据库中使用TRUNCATE语句

      TRUNCATE TABLE table_name
[{DROP | REUSE} STORAGE]

2.5.2 SQL SERVER数据库中使用TRUNCATE语句

  TRUNCATE TABLE table_name

说明：

在SQL Server中，这个语句只能由表的所有者给出。在Oracle中，只有当你是表的所有者或者有DELETE TABLE系统特权时才能使用该语句。

Oracle的TRUNCATE TABLE命令可以随意的释放被表中的行占据的存储空间。SQL Server的 TRUNCATE TABLE则总是回收被表中的数据和与之关联的索引所占据的空间。

2.6在IDENTITY和TIMESTAMP列中操作数据

Oracle序列是一种和任何给定的表或者列都不直接相关的数据库对象。一列和一个序列的关系是在应用程序中实现的，方法是把一个序列的值分配给一个列。因此在同序列一起工作的时候，Oracle并没有强化任何规则。但是在Microsoft SQL Server的标识列中，值是不能被更新的并且也不能使用DEFAULT关键字。

缺省情况下，数据不能直接插入到一个标识列中。标识列为新插入表的每一行自动产生一个唯一的、顺序的数字。这个缺省设置可以用下面的SET语句覆盖。

SET IDENTITY_INSERT table_name ON

当IDENTITY_INSERT设置为ON时，用户就可以在新行的标识列中插入任何值。为了防止输入重复的值，必须在该列上创建一个唯一的索引。这个语句的目的是允许用户为一行重新创建一个偶然被删除的值。@@IDENTITY可以用来获取最后一个标识值。

TRUNCATE TABLE语句把一个标识列重新设置为它原来的SEED值。如果你不想为一列重新设置标识值，可以用不带WHERE子句的DELETE子句来代替TRUNCATE TABLE语句。你必须估计这会给你的Oracle移植带来什么样的影响，因为ORACLE SEQUENCES不会跟着TRUNCATE TABLE命令重新设置。

对时间信息（timestamp）列，你只能执行插入或者删除操作。如果你试图更新一个时间信息列，你将收到这样的错误消息。

Msg 272, Level 16, State 1 Can't update a TIMESTAMP column.

2.7 锁定被请求的行

⑴ Oracle用FOR UPDATE子句来锁定在SELECT命令中指定的行。

⑵ 在Microsoft SQL Server中，你不需要使用它的等价子句，因为这是一个缺省行为。

2.8行合计和计算子句

SQL Server的COMPUTE子句用来生成行合计函数（SUM、AVG、MIN、MAX、以及COUNT），这些函数看起来好像是查询结果的附加行。这允许你看到一组结果的详情和汇总。你可以为一个子群（subgroups）计算汇总值，还可以为同一组计算更多的合计函数。

Oracle的SELECT命令语法不支持COMPUTE子句。但是，SQL Server的COMPUTE子句就像在Oracle的SQL*Plus查询工具中能找到的COMPUTE命令一样的工作。

2.9 SQL语法在转换过程中遵循的标准

⑴ 检查所有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语句来调试你的查询以获得高的性能。

 3 应用对象：

   本文档主要对ORACLE和SQL SERVER在语法和函数方面进行了分析和比较，对形成和完善KMSQL将会起者促进作用。