其它行级别的函数
下面是Oracle支持的其它行级别的函数以及它们的Microsoft SQL Server等价函数。
函数 | Oracle | Microsoft SQServer |
返回第一个非空表达式 | DECODE | COALESCE |
当前序列值 | CURRVAL | N/A |
下一个序列值 | NEXTVAL | N/A |
如果exp1 = exp2, 返回null | DECODE | NULLIF |
用户登录账号ID数字 | UID | SUSER_ID |
用户登录名 | USER | SUSER_NAME |
用户数据库ID数字 | UID | USER_ID |
用户数据库名 | USER | USER_NAME |
当前用户 | CURRENT_USER | CURRENT_USER |
用户环境(audit trail) | USERENV | N/A |
在CONNECT BY子句中的级别 | LEVEL | N/A |
聚合函数
下面是Oracle支持的合计函数和它们的Microsoft SQL Server等价函数。
函数 | Oracle | Microsoft SQServer |
Average | AVG | AVG |
Count | COUNT | COUNT |
Maximum | MAX | MAX |
Minimum | MIN | MIN |
Standard deviation | STDDEV | STDEV or STDEVP |
Summation | SUM | SUM |
Variance | VARIANCE | VAR or VARP |
条件测试
Oracle 的DECODE语句和Microsoft SQL Server的CASE表达式都执行条件测试。当test_value中的值和后面的任何表达式匹配的时候,相关的值就返回。如果没有找到任何匹配的值, 就返回default_value。如果没有指定default_value,在没有匹配的时候,DECODE和CASE都返回一个NULL。下表显示了 该语句的语法,同时给出了转换DECODE命令的示例。
Oracle | Microsoft SQ |
DECODE (test_value, CREATE VIEW STUDENT_ADMIN.STUDENT_GPA | CASE input_expression CREATE VIEW STUDENT_ADMIN.STUDENT_GPA |
CASE表达式可以支持用SELECT语句执行布尔测试,这是DECODE命令所不允许的。欲了解关于CASE表达式的详细信息,请参阅SQL Server联机手册。
把值转换为不同的数据类型
Microsoft SQL Server的CONVERT和CAST函数都是多目标转换函数。它们提供了相似的功能,把一种数据类型的表达式转换为另一种数据类型的表达式,并且支持多种专门数据的格式。
- CAST(expression AS data_type)
- CONVERT (data type[(length)], expression [, style])
CAST是一个SQL-92标准的函数。这些函数执行同Oracle的TO_CHAR、TO_NUMBER、TO_DATE、HEXTORAW以及RAWTOTEXT函数相同的功能。
这里所指的数据类型是任何表达式将被转换成为的系统数据类型。不能使用用户定义的数据类型。长度参数是可选的,该参数用于char、varchar、binary以及varbinary数据类型。允许的最大长度是8000。
转换 | Oracle | Microsoft SQServer |
字符到数字 | TO_NUMBER('10') | CONVERT(numeric, '10') |
数字到字符 | TO_CHAR(10) | CONVERT(char, 10) |
字符到日期 | TO_DATE('04-JUL-97') | CONVERT(datetime, '04-JUL-97') |
日期到字符 | TO_CHAR(sysdate) | CONVERT(char, GETDATE()) |
16进制到2进制 | HEXTORAW('1F') | CONVERT(binary, '1F') |
2进制到16进制 | RAWTOHEX | CONVERT(char, binary_column) |
请注意字符串是怎样转换为日期的。在Oracle中,缺省的日期格式模型是“DD-MON-YY”如果你使用任何其它格式,你必须提供一个合适的日期格式模型。CONVERT函数自动转换标准日期格式,不需要任何格式模型。
从日期转换到字符串时,CONVERT函数的缺省输出是“dd mon yyyy hh:mm:ss:mmm(24h)”。用一个数字风格代码来格式化输出,使它能输出为其它类型的日期格式模型。欲了解CONVERT函数的详细信息,请参阅SQL Server联机手册。
下表显示了Microsoft SQL Server日期的缺省输出。
Without Century | With Century | Standard | Output |
- | 0 or 100 (*) | Default | mon dd yyyy hh:miAM (or PM) |
1 | 101 | USA | mm/dd/yy |
2 | 102 | ANSI | yy.mm.dd |
3 | 103 | British/French | dd/mm/yy |
4 | 104 | German | dd.mm.yy |
5 | 105 | Italian | dd-mm-yy |
6 | 106 | - | dd mon yy |
7 | 107 | - | mon dd, yy |
8 | 108 | - | hh:mm:ss |
- | 9 or 109 (*) | Default milliseconds | mon dd yyyy hh:mi:ss:mmm (AM or PM) |
10 | 110 | USA | mm-dd-yy |
11 | 111 | Japan | yy/mm/dd |
12 | 112 | ISO | yymmdd |
- | 13 or 113 (*) | Europe default | dd mon yyyy hh:mm:ss:mmm(24h) |
14 | 114 | - | hh:mi:ss:mmm(24h) |
用户定义函数
Oracle PL/SQL函数可以在Oracle SQL语句中使用。在Microsoft SQL Server中一般可以通过其它方式来实现同样的功能。
在SQL Server中可以用表中给出的查询来代替。
Oracle | Microsoft SQServer |
SELECT SSN, FNAME, LNAME, ) TUITION_PAID, | SELECT SSN, FNAME, LNAME, TUITION_PAID, TUITION_PAID/SUM_MAJOR AS PERCENT_MAJOR |
CREATE OR REPLACE FUNCTION GET_SUM_MAJOR | No CREATE FUNCTION syntax is required; use CREATE PROCEDURE syntax. |
比较操作符
Oracle和Microsoft SQL Server的比较操作符几乎是一样的。
算符 | Oracle | Microsoft SQServer |
等于 | (=) | (=) |
大于 | (>) | (>) |
小于 | (<) | (<) |
大于或等于 | (>=) | (>=) |
小于或等于 | (<=) | (<=) |
不等于 | (!=, <>, ^=) | (!=, <>, ^=) |
不大于,不小于 | N/A | !> , !< |
在集合中任意成员中 | IN | IN |
不在集合中的任何成员中 | NOT IN | NOT IN |
集合中的任意值 | ANY, SOME | ANY, SOME |
提交集合中的所有值 | != ALL, <> ALL, < ALL, | != ALL, <> ALL, < ALL, |
像模式(Like pattern) | LIKE | LIKE |
不像模式(Not like pattern) | NOT LIKE | NOT LIKE |
X和y之间的值 | BETWEEN x AND y | BETWEEN x AND y |
不在x和y之间的值 | NOT BETWEEN | NOT BETWEEN |
值存在 | EXISTS | EXISTS |
值不存在 | NOT EXISTS | NOT EXISTS |
值{为|不为}空 | IS NULL, IS NOT NULL | Same. Also = NULL, |
模式匹配
SQL Server的LIKE关键字提供了有用的通配符搜索功能,这个功能在Oracle中不支持。除了所有的RDBMS都支持的(%)和(_)通配符以外,SQL Server还支持([ ])和([^])通配符。
([ ])字符用来查询在一个范围内的所有单个字符。例如,如果你需要查询包含一个从a到f的字符的数据,你可以这样写:“LIKE '[a-f]'”或者“LIKE '[abcdef]'”。这些附加的通配符的有效性在下表中给出。
Oracle | Microsoft SQ |
SELECT * FROM STUDENT_ADMIN.STUDENT | SELECT * FROM STUDENT_ADMIN.STUDENT |
[^]通配符用来标记那些不在特定范围内的字符。例如,如果除了a到f以外的所有字符都是可以接受的,你可以这样书写:LIKE '[^a - f]'或者LIKE '[^abcdef]'。
欲了解关于LIKE关键字的详细信息,请参阅SQL Server联机手册。
在比较中使用 NULL
尽管Microsoft SQL Server传统上支持SQL-92标准的和一些非标准的NULL行为,但是它还是支持Oracle中的NULL的用法。
为了支持分布式查询,SET ANSI_NULLS必须设定为ON。
在 进行连接的时候,SQL Server的SQL Server ODBC驱动程序和OLE DB提供者自动把SET ANSI_NULLS设定为ON。这个设置可以在ODBC数据源、ODBC连接属性、或者是在连接到SQL Server之前在应用程序中设置的OLE DB连接属性中进行配置。在从DB-Library应用程序中连接时,SET ANSI_NULLS缺省为OFF。
当SET ANSI_DEFAULTS为ON时,SET ANSI_NULLS被允许。
欲了解关于NULL用法的详细信息,请参阅SQL Server联机手册。
字串连接
Oracle使用两个管道符号(||)来作为字串连接操作符,SQL Server则使用加号(+)。这个差别要求你在应用程序中做小小的修改。
Oracle | Microsoft SQ |
SELECT FNAME||' '||LNAME AS NAME | SELECT FNAME +' '+ LNAME AS NAME |
流控制( Control-of-Flow )语言
流控制语言控制SQL语句执行流,语句块以及存储过程。PL/SQL和Transact-SQL提供了多数相同的结构,但是还是有一些语法差别。
关键字
这是两个RDBMS支持的关键字。
语句 | Oracle PL/SQL | Microsoft SQServer |
声明变量 | DECLARE | DECLARE |
语句块 | BEGIN...END; | BEGIN...END |
条件处理 | IF…THEN, | IF…[BEGIN…END] |
无条件结束 | RETURN | RETURN |
无条件结束当前程序块后面的语句 | EXIT | BREAK |
重新开始一个WHILE循环 | N/A | CONTINUE |
等待指定间隔 | N/A (dbms_lock.sleep) | WAITFOR |
循环控制 | WHILE LOOP…END LOOP; | WHILE <condition> LABEL…GOTO LABEL |
程序注释 | /* … */, -- | /* … */, -- |
打印输出 | RDBMS_OUTPUT.PUT_ | |
引发程序错误(Raise program error) | RAISE_APPLICATION_ | RAISERROR |
执行程序 | EXECUTE | EXECUTE |
语句终止符 | Semicolon (;) | N/A |
声明变量
Transact-SQL和PL/SQL的变量是用DECLARE关键字创建的。Transact-SQL变量用@标记,并且就像PL/SQL一样,在第一次创建时,用空值初始化。
Oracle | Microsoft SQ |
DECLARE | DECLARE |
Transact -SQL不支持%TYPE和%ROWTYPE变量数据类型定义。一个Transact-SQL变量不能在DECLARE命令中初始化。在 Microsoft SQL Server数据类型定义中也不能使用Oracle的NOT NULL和CONSTANT关键字。
像Oracle的LONG和LONG RAW数据类型一样。文本和图形数据类型不能被用做变量定义。此外,Transact-SQL不支持PL/SQL风格的记录和表的定义。
给变量赋值
Oracle和Microsoft SQL Server提供了下列方法来为本地变量赋值。
Oracle | Microsoft SQ |
Assignment operator (:=) | SET @local_variable = value |
SELECT...INTO syntax for selecting column values from a single row | SELECT @local_variable = expression [FROM…] for assigning a literal value, an expression involving other local variables, or a column value from a single row |
FETCH…INTO syntax | FETCH…INTO syntax |
这里有一些语法示例
Oracle | Microsoft SQ |
DECLARE VSSN CHAR(9); | DECLARE @VSSN CHAR(9), |
语句块
Oracle PL/SQL和Microsoft SQL Server Transact-SQL都支持用BEGIN…END术语来标记语句块。Transact-SQL不需要在DECLARE语句后使用一个语句块。如果在 Microsoft SQL Server 中的IF语句和WHILE循环中有多于一个语句被执行,则需要使用BEGIN…END语句块。
Oracle | Microsoft SQ |
DECLARE | DECLARE |
条件处理
Microsoft SQL Server Transact-SQL的条件语句包括IF和ELSE,但不包括Oracle PL/SQL中的ELSEIF语句。可以用嵌套多重IF语句来到达同样的效果。对于广泛的条件测试,用CASE表达式也许更容易和可读一些。
Oracle | Microsoft SQ |
DECLARE | DECLARE |
重复执行语句(循环)
Oracle PL/SQL提供了无条件的LOOP和FOR LOOP。Transact-SQL则提供了WHILE循环和GOTO语句。
WHILE Boolean_expression {sql_statement | statement_block} [BREAK] [CONTINUE] |
WHILE循环需要测试一个布尔表达式来决定一个或者多个语句的重复执行。只要给定的表达式结果为真,这个(些)语句就一直重复执行下去。如果有多个语句需要执行,则这些语句必须放在一个BEGIN…END块中。
Oracle | Microsoft SQ |
DECLARE | DECLARE |
语 句的执行可以在循环的内部用BREAK和CONTINUE关键字控制。BREAK关键字使WHILE循环无条件的结束,而CONTINUE关键字使 WHILE循环跳过后面的语句重新开始。BREAK关键字同Oracle PL/SQL中的EXIT关键字是等价的。而在Oracle中没有和CONTINUE等价的关键字
GOTO 语句
Oracle和Microsoft SQL Server都有GOTO语句,但是语法不同。GOTO语句使Transact-SQL跳到指定的标号处运行,在GOTO语句后指定标号之间的任何语句都不会被执行。
Oracle | Microsoft SQ |
GOTO label; | GOTO label |
PRINT 语句
Transact-SQL的PRINT语句执行同PL/SQL的RDBMS_OUTPUT.put_line过程同样的操作。该语句用来打印用户给定的消息。
用PRINT语句打印的消息上限是8,000个字符。定义为char或者varchar数据类型的变量可以嵌入打印语句。如果使用其它数据类型的变量,则必须使用CONVERT或者CAST函数。本地变量、全局变量可以被打印。可以用单引号或者双引号来封闭文本。
从存储过程返回
Microsoft SQL Server和Oracle都有RETURN语句。RETURN使你的程序从查询或者过程中无条件的跳出。RETURN是立即的、完全的、并且可以用于从过程、批处理或者语句块的任意部分跳出。在REUTRN后面的语句将不会被执行。
Oracle | Microsoft SQ |
RETURN expression: | RETURN [integer_expression] |
引发程序错误( Raising program errors )
Transact-SQL的RAISERROR返回一个用户定义的错误消息,并且设置一个系统标志来记录发生了一个错误。这个功能同PL/SQL的raise_application_error异常处理器的功能是相似的。
RAISERROR语句允许客户重新取得sysmessages表的一个入口,或者用用户指定的严重性和状态信息动态的建立一条消息。在被定义后,消息被送回客户端作为系统错误消息。
RAISERROR ({msg_id | msg_str}, severity, state [, argument1 [, argument2]]) [WITH options] |
在 转换你的PL/SQL程序时,也许用不着使用RAISERROR语句。在下面的示例代码中。PL/SQL程序使用 raise_application_error异常处理器,但是Transact-SQL程序则什么也没用。包括 raise_application_error异常处理器是为了防止PL/SQL返回不明确的未经处理的异常错误消息。作为代替,当一个不可预见的问题 发生的时候,异常处理器总是返回Oracle错误消息。
当一个Transact-SQL失败时,它总是返回一个详细的错误消息给客户程序。因此,除非需要某些特定的错误处理,一般是不需要RAISERROR语句的。
Oracle | Microsoft SQ |
CREATE OR REPLACE FUNCTION | CREATE PROCEDURE |