其它行级别的函数
下面是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, expression1, value1 [[,expression2, value2] […]] [,default_value] )
CREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND(AVG(DECODE(grade ,'A', 4 ,'A+', 4.3 ,'A-', 3.7 ,'B', 3 ,'B+', 3.3 ,'B-', 2.7 ,'C', 2 ,'C+', 2.3 ,'C-', 1.7 ,'D', 1 ,'D+', 1.3 ,'D-', 0.7 ,0)),2) FROM STUDENT_ADMIN.GRADE GROUP BY SSN
|
CASE
input_expression
WHEN
when_expression
THEN result_expression [[WHENwhen_expressionTHEN result_expression] [...]] [ELSEelse_result_expression] END
CREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND(AVG(CASE grade WHEN 'A' THEN 4 WHEN 'A+' THEN 4.3 WHEN 'A-' THEN 3.7 WHEN 'B' THEN 3 WHEN 'B+' THEN 3.3 WHEN 'B-' THEN 2.7 WHEN 'C' THEN 2 WHEN 'C+' THEN 2.3 WHEN 'C-' THEN 1.7 WHEN 'D' THEN 1 WHEN 'D+' THEN 1.3 WHEN 'D-' THEN 0.7 ELSE 0 END),2) FROM STUDENT_ADMIN.GRADE GROUP BY SSN
|
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') TO_DATE('04-JUL-1997', 'dd-mon-yyyy') TO_DATE('July 4, 1997', 'Month dd, yyyy')
|
CONVERT(datetime, '04-JUL-97') CONVERT(datetime, '04-JUL-1997') CONVERT(datetime, 'July 4, 1997')
|
日期到字符
|
TO_CHAR(sysdate) TO_CHAR(sysdate, 'dd mon yyyy') TO_CHAR(sysdate, 'mm/dd/yyyy')
|
CONVERT(char, GETDATE()) CONVERT(char, GETDATE(), 106) CONVERT(char, GETDATE(), 101)
|
16进制到2进制
|
HEXTORAW('1F')
|
CONVERT(binary, '1F')
|
2进制到16进制
|
RAWTOHEX (binary_column)
|
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, TUITION_PAID/GET_SUM_ MAJOR(MAJOR) AS PERCENT_MAJOR FROM STUDENT_ADMIN.STUDENT
|
SELECT SSN, FNAME, LNAME, TUITION_PAID, TUITION_PAID/SUM_MAJOR AS PERCENT_MAJOR FROM STUDENT_ADMIN.STUDENT, (SELECT MAJOR, SUM(TUITION_PAID)
SUM_MAJOR FROM STUDENT_ADMIN.STUDENT GROUP BY MAJOR) SUM_STUDENT WHERE STUDENT.MAJOR = SUM_STUDENT.MAJOR
|
CREATE OR REPLACE FUNCTION GET_SUM_MAJOR (INMAJOR VARCHAR2) RETURN NUMBER AS SUM_PAID NUMBER; BEGIN SELECT SUM(TUITION_PAID) INTO SUM_PAID FROM STUDENT_ADMIN.STUDENT WHERE MAJOR = INMAJOR; RETURN(SUM_PAID); END 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, != SOME, <> SOME, < SOME, > SOME, <= SOME, >= SOME
|
!= ALL, <> ALL, < ALL, > ALL, <= ALL, >= ALL, != SOME, <> SOME, < SOME, > SOME, <= SOME, >= SOME
|
像模式(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, != NULL for backward compatibility (not recommended).
|
模式匹配
SQL Server的LIKE关键字提供了有用的通配符搜索功能,这个功能在Oracle中不支持。除了所有的RDBMS都支持的(%)和(_)通配符以外,SQL Server还支持([ ])和([^])通配符。
([ ])字符用来查询在一个范围内的所有单个字符。例如,如果你需要查询包含一个从a到f的字符的数据,你可以这样写:“LIKE '[a-f]'”或者“LIKE '[abcdef]'”。这些附加的通配符的有效性在下表中给出。
Oracle
|
Microsoft SQ
|
SELECT * FROM STUDENT_ADMIN.STUDENT WHERE LNAME LIKE 'A%' OR LNAME LIKE 'B%' OR LNAME LIKE 'C%'
|
SELECT * FROM STUDENT_ADMIN.STUDENT WHERE LNAME LIKE '[ABC]%'
|
[^]通配符用来标记那些不在特定范围内的字符。例如,如果除了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 FROM STUDENT_ADMIN.STUDENT
|
SELECT FNAME +' '+ LNAME AS NAME FROM STUDENT_ADMIN.STUDENT
|
流控制(
Control-of-Flow
)语言
流控制语言控制SQL语句执行流,语句块以及存储过程。PL/SQL和Transact-SQL提供了多数相同的结构,但是还是有一些语法差别。
关键字
这是两个RDBMS支持的关键字。
语句
|
Oracle PL/SQL
|
Microsoft SQServer Transact-SQL
|
声明变量
|
DECLARE
|
DECLARE
|
语句块
|
BEGIN...END;
|
BEGIN...END
|
条件处理
|
IF…THEN, ELSIF…THEN, ELSE ENDIF;
|
IF…[BEGIN…END] ELSE <condition> [BEGIN…END] ELSE IF <condition> CASE expression
|
无条件结束
|
RETURN
|
RETURN
|
无条件结束当前程序块后面的语句
|
EXIT
|
BREAK
|
重新开始一个WHILE循环
|
N/A
|
CONTINUE
|
等待指定间隔
|
N/A (dbms_lock.sleep)
|
WAITFOR
|
循环控制
|
WHILE LOOP…END LOOP;
LABEL…GOTO LABEL; FOR…END LOOP; LOOP…END LOOP;
|
WHILE <condition> BEGIN… END
LABEL…GOTO LABEL
|
程序注释
|
/* … */, --
|
/* … */, --
|
打印输出
|
RDBMS_OUTPUT.PUT_ LINE
|
PRINT
|
引发程序错误(Raise program error)
|
RAISE_APPLICATION_ ERROR
|
RAISERROR
|
执行程序
|
EXECUTE
|
EXECUTE
|
语句终止符
|
Semicolon (;)
|
N/A
|
声明变量
Transact-SQL和PL/SQL的变量是用DECLARE关键字创建的。Transact-SQL变量用@标记,并且就像PL/SQL一样,在第一次创建时,用空值初始化。
Oracle
|
Microsoft SQ
|
DECLARE VSSN CHAR(9); VFNAME VARCHAR2(12); VLNAME VARCHAR2(20); VBIRTH_DATE DATE; VLOAN_AMOUNT NUMBER(12,2);
|
DECLARE @VSSN CHAR(9), @VFNAME VARCHAR2(12), @VLNAME VARCHAR2(20), @VBIRTH_DATE DATETIME, @VLOAN_AMOUNT NUMERIC(12,2)
|
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); VFNAME VARCHAR2(12); VLNAME VARCHAR2(20); BEGIN VSSN := '123448887'; SELECT FNAME, LNAME INTO VFNAME, VLNAME FROM STUDENTS WHERE SSN=VSSN; END;
|
DECLARE @VSSN CHAR(9), @VFNAME VARCHAR(12), @VLNAME VARCHAR(20) SET @VSSN = '12355887' SELECT @VFNAME=FNAME, @VLNAME=LNAME FROM STUDENTS WHERE SSN = @VSSN
|
语句块
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 VARIABLES ... BEGIN -- THIS IS REQUIRED SYNTAX PROGRAM_STATEMENTS ... IF ...THEN STATEMENT1; STATEMENT2; STATEMENTN; END IF; WHILE ... LOOP STATEMENT1; STATEMENT2; STATEMENTN; END LOOP; END; -- THIS IS REQUIRED SYNTAX
|
DECLARE DECLARE VARIABLES ... BEGIN -- THIS IS OPTIONAL SYNTAX PROGRAM_STATEMENTS ... IF ...
BEGIN
STATEMENT1 STATEMENT2 STATEMENTN END WHILE ...
BEGIN
STATEMENT1 STATEMENT2 STATEMENTN
END END -- THIS IS REQUIRED SYNTAX
|
条件处理
Microsoft SQL Server Transact-SQL的条件语句包括IF和ELSE,但不包括Oracle PL/SQL中的ELSEIF语句。可以用嵌套多重IF语句来到达同样的效果。对于广泛的条件测试,用CASE表达式也许更容易和可读一些。
Oracle
|
Microsoft SQ
|
DECLARE VDEGREE_PROGRAM CHAR(1); VDEGREE_PROGRAM_NAME VARCHAR2(20); BEGIN VDEGREE_PROGRAM := 'U'; IF VDEGREE_PROGRAM = 'U' THEN VDEGREE_PROGRAM_NAME := 'Undergraduate'; ELSIF VDEGREE_PROGRAM = 'M' THEN VDEGREE_PROGRAM_ NAME := 'Masters'; ELSIF VDEGREE_PROGRAM = 'P' THEN VDEGREE_PROGRAM_ NAME := 'PhD'; ELSE VDEGREE_PROGRAM_ NAME := 'Unknown'; END IF; END;
|
DECLARE @VDEGREE_PROGRAM CHAR(1), @VDEGREE_PROGRAM_NAME VARCHAR(20) SELECT @VDEGREE_PROGRAM = 'U' SELECT @VDEGREE_PROGRAM_ NAME = CASE @VDEGREE_PROGRAM WHEN 'U' THEN 'Undergraduate' WHEN 'M' THEN 'Masters' WHEN 'P' THEN 'PhD'. ELSE 'Unknown' END
|
重复执行语句(循环)
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 COUNTER NUMBER; BEGIN COUNTER := 0 WHILE (COUNTER <5) LOOP COUNTER := COUNTER + 1; END LOOP; END;
|
DECLARE @COUNTER NUMERIC SELECT@COUNTER = 1 WHILE (@COUNTER <5) BEGIN SELECT @COUNTER = @COUNTER +1 END
|
语句的执行可以在循环的内部用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; <<label name here>>
|
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 DEPT_ADMIN.DELETE_DEPT (VDEPT IN VARCHAR2) RETURN NUMBER AS BEGIN DELETE FROM DEPT_ADMIN.DEPT WHERE DEPT = VDEPT; RETURN(SQL%ROWCOUNT); EXCEPTION WHEN OTHER THEN RAISE_APPLICATION_ERROR (-20001,SQLERRM); END DELETE_DEPT; /
|
CREATE PROCEDURE DEPT_ADMIN.DELETE_DEPT @VDEPT VARCHAR(4) AS DELETE FROM DEPT_DB.DBO.DEPT WHERE DEPT = @VDEPT RETURN @@ROWCOUNT GO
|
|