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 | DATEADD |
两个日期的差 | (date column +/- value) or | DATEDIFF |
当前日期和时间 | SYSDATE | GETDATE() |
一个月的最后一天 | LAST_DAY | N/A |
时区转换 | NEW_TIME | N/A |
日期后的第一个周日 | NEXT_DAY | N/A |
代表日期的字符串 | TO_CHAR | DATENAME |
代表日期的整数 | TO_NUMBER | 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 | SELECT CCODE, CNAME |
下例使用MINUS操作符来找出那些没有学生的班级。
Oracle | Microsoft SQL Server |
SELECT CCODE, CNAME | SELECT CCODE, CNAME |
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) | INSERT INTO GRADE (SSN, CCODE, GRADE) |
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 | DECLARE |
在SQL Server中,关键字DEFAULT可以用来把一列设置为它的缺省值。你不能用Oracle的UPDATE命令来设置一列为它的缺省值。
Transact-SQL和Oracle SQL都支持在一个UPDATE语句中使用子查询。但是Transact-SQL的FROM子句可以用来创建一个基于节点的UPDATE。这个能力是你的UPDATE语法更加可读并且在某种意义上提高了性能。
Oracle | Microsoft SQL Server |
UPDATE | Subquery: UPDATE FROM clause: UPDATE |
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将会起者促进作用。