ORACLE内部函数大全以及与SQLSERVER的区别.docx
《ORACLE内部函数大全以及与SQLSERVER的区别.docx》由会员分享，可在线阅读，更多相关《ORACLE内部函数大全以及与SQLSERVER的区别.docx（24页珍藏版）》请在冰豆网上搜索。
ORACLE内部函数大全以及与SQLSERVER的区别
ORACLE内部函数大全以及与SQLSERVER的区别
下面是Oracle支持的字符函数和它们的MicrosoftSQLServer等价函数。
函数OracleMicrosoftSQL Server
把字符转换为ASCII:
ASCIIASCII
字串连接:
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--------------------DATELENGTHorLEN
列表中最大的字符串GREATEST--------------------N/A
列表中最小的字符串LEAST--------------------N/A
如果为NULL则转换字串NVL--------------------ISNULL
日期函数
下面是Oracle支持的日期函数和它们的MicrosoftSQLServer等价函数。
函数Oracle--------------------MicrosoftSQL Server
日期相加（datecolumn+/-value）orADD_MONTHS --------------------DATEADD
两个日期的差（datecolumn+/-value）orMONTHS_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
转换函数
下面是Oracle支持的转换函数和它们的MicrosoftSQLServer等价函数。
函数Oracle --------------------MicrosoftSQL Server
数字转换为字符TO_CHAR --------------------CONVERT
字符转换为数字TO_NUMBER --------------------CONVERT
日期转换为字符TO_CHAR --------------------CONVERT
字符转换为日期TO_DATECONVERT
16进制转换为2进制HEX_TO_RAW --------------------CONVERT
2进制转换为16进制RAW_TO_HEX --------------------CONVERT
其它行级别的函数
下面是Oracle支持的其它行级别的函数以及它们的MicrosoftSQLServer等价函数。
函数Oracle --------------------MicrosoftSQL Server
返回第一个非空表达式DECODE-------------------------------------COALESCE
当前序列值CURRVAL --------------------N/A
下一个序列值NEXTVAL --------------------N/A
用户登录账号ID数字UID--------------------SUSER_ID
用户登录名USER --------------------SUSER_NAME
用户数据库ID数字UID --------------------USER_ID
用户数据库名USER --------------------USER_NAME
当前用户CURRENT_USER--------------------CURRENT_USER
用户环境（audittrail）USERENV --------------------N/A
在CONNECTBY子句中的级别LEVEL --------------------N/A
合计函数
下面是Oracle支持的合计函数和它们的MicrosoftSQLServer等价函数。
函数Oracle --------------------MicrosoftSQL Server
AverageAVG--------------------AVG
CountCOUNT --------------------COUNT
MaximumMAX--------------------MAX
MinimumMIN --------------------MIN
StandarddeviationSTDDEV --------------------STDEVorSTDEVP
SummationSUM--------------------SUM
VarianceVARIANCE --------------------VARorVARP
条件测试
Oracle的DECODE语句和MicrosoftSQLServer的CASE表达式都执行条件测试。
当test_value中的值和后面的任何表达式匹配的时候，相关的值就返回。
如果没有找到任何匹配的值，就返回default_value。
如果没有指定default_value，在没有匹配的时候，DECODE和CASE都返回一个NULL。
下表显示了该语句的语法，
同时给出了转换DECODE命令的示例。
OracleMicrosoftSQL
DECODE（test_value,
expression1,value1
<,expression2,value2][…>
[,default_value]
）
CREATEVIEWSTUDENT_ADMIN.STUDENT_GPA
（SSN,GPA）
ASSELECTSSN,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）
FROMSTUDENT_ADMIN.GRADE
GROUPBYSSN
CASEinput_expression
WHENwhen_expressionTHEN result_expression
[ELSEelse_result_expression]
END
CREATEVIEWSTUDENT_ADMIN.STUDENT_GPA
（SSN,GPA）
ASSELECTSSN,ROUND（AVG（CASEgrade
WHEN'A'THEN4
WHEN'A+'THEN4.3
WHEN'A-'THEN3.7
WHEN'B'THEN3
WHEN'B+'THEN3.3
WHEN'B-'THEN2.7
WHEN'C'THEN2
WHEN'C+'THEN2.3
WHEN'C-'THEN1.7
WHEN'D'THEN1
WHEN'D+'THEN1.3
WHEN'D-'THEN0.7
ELSE0
END）,2）
FROMSTUDENT_ADMIN.GRADE
GROUPBYSSN
CASE表达式可以支持用SELECT语句执行布尔测试，这是DECODE命令所不允许的。
欲了解关于CASE表达式的详细信息，
请参阅SQLServer联机手册。
把值转换为不同的数据类型
MicrosoftSQLServer的CONVERT和CAST函数都是多目标转换函数。
它们提供了相似的功能，
把一种数据类型的表达式转换为另一种数据类型的表达式，并且支持多种专门数据的格式。
CAST（expressionASdata_type）
CONVERT（datatype[（length）],expression[,style]）
CAST是一个SQL-92标准的函数。
这些函数执行同Oracle的TO_CHAR、TO_NUMBER、TO_DATE、HEXTORAW以及RAWTOTEXT函数相同的功能。
这里所指的数据类型是任何表达式将被转换成为的系统数据类型。
不能使用用户定义的数据类型。
长度参数是可选的，
该参数用于char、varchar、binary以及varbinary数据类型。
允许的最大长度是8000。
转换OracleMicrosoftSQL Server
字符到数字TO_NUMBER（ཆ'） --------------------CONVERT（numeric,ཆ'）
数字到字符TO_CHAR（10） --------------------CONVERT（char,10）
字符到日期TO_DATE（ཀ-JUL-97'）
TO_DATE（ཀ-JUL-1997','dd-mon-yyyy'）
TO_DATE（'July4,1997','Monthdd,yyyy'）--------------------CONVERT（datetime,ཀ-JUL-97'）
CONVERT（datetime,ཀ-JUL-1997'）
CONVERT（datetime,'July4,1997'）
日期到字符TO_CHAR（sysdate）
TO_CHAR（sysdate,'ddmonyyyy'）
TO_CHAR（sysdate,'mm/dd/yyyy'）--------------------CONVERT（char,GETDATE（））
CONVERT（char,GETDATE（）,106）
CONVERT（char,GETDATE（）,101）
16进制到2进制HEXTORAW（ƇF'）--------------------CONVERT（binary,ƇF'）
2进制到16进制RAWTOHEX（binary_column）--------------------CONVERT（char,binary_column）
请注意字符串是怎样转换为日期的。
在Oracle中，缺省的日期格式模型是“DD-MON-YY”如果你使用任何其它格式，
你必须提供一个合适的日期格式模型。
CONVERT函数自动转换标准日期格式，不需要任何格式模型。
从日期转换到字符串时，CONVERT函数的缺省输出是“ddmonyyyyhh:
mm:
ss:
mmm（24h）”。
用一个数字风格代码来格式化输出，使它能输出为其它类型的日期格式模型。
欲了解CONVERT函数的详细信息，请参阅SQLServer联机手册。
下表显示了MicrosoftSQLServer日期的缺省输出。
WithoutCenturyWithCenturyStandardOutput
-0or100（*）Defaultmonddyyyyhh:
miAM（orPM）
1101USAmm/dd/yy
2102ANSIyy.mm.dd
3103British/Frenchdd/mm/yy
4104Germandd.mm.yy
5105Italiandd-mm-yy
6106-ddmonyy
7107-mondd,yy
8108-hh:
mm:
ss
-9or109（*）Defaultmillisecondsmonddyyyyhh:
mi:
ss:
mmm（AMorPM）
10110USAmm-dd-yy
11111Japanyy/mm/dd
12112ISOyymmdd
-13or113（*）Europedefaultddmonyyyyhh:
mm:
ss:
mmm（24h）
14114-hh:
mi:
ss:
mmm（24h）
用户定义函数
OraclePL/SQL函数可以在OracleSQL语句中使用。
在MicrosoftSQLServer中一般可以通过其它方式来实现同样的功能。
在SQLServer中可以用表中给出的查询来代替。
OracleMicrosoftSQL Server
SELECTSSN,FNAME,LNAME,） TUITION_PAID,
TUITION_PAID/GET_SUM_
MAJOR（MAJOR）
ASPERCENT_MAJOR
FROMSTUDENT_ADMIN.STUDENTSELECTSSN,FNAME,LNAME,TUITION_PAID,TUITION_PAID/SUM_MAJORASPERCENT_MAJOR
FROMSTUDENT_ADMIN.STUDENT,
（SELECTMAJOR, SUM（TUITION_PAID）SUM_MAJOR
FROMSTUDENT_ADMIN.STUDENT
GROUPBYMAJOR）SUM_STUDENT
WHERESTUDENT.MAJOR= SUM_STUDENT.MAJOR
CREATEORREPLACEFUNCTIONGET_SUM_MAJOR
（INMAJORVARCHAR2）RETURNNUMBER
ASSUM_PAIDNUMBER;
BEGIN
SELECTSUM（TUITION_PAID）INTO SUM_PAID
FROMSTUDENT_ADMIN.STUDENT
WHEREMAJOR=INMAJOR;
RETURN（SUM_PAID）;
ENDGET_SUM_MAJOR;NoCREATEFUNCTIONsyntaxisrequired;useCREATEPROCEDUREsyntax.
比较操作符
Oracle和MicrosoftSQLServer的比较操作符几乎是一样的。
算符OracleMicrosoftSQL Server
等于（=）（=）
大于（>）（>）
小于（<）（<）
大于或等于（>=）（>=）
小于或等于（<=）（<=）
不等于（!
=,<>,^=）（!
=,<>,^=）
不大于，不小于N/A!
>,!
<
在集合中任意成员中ININ
不在集合中的任何成员中NOTINNOTIN
集合中的任意值ANY,SOMEANY,SOME
提交集合中的所有值!
=ALL,<>ALL,>ALL,<=ALL,>=ALL,!
= SOME,<> SOME,
< SOME,> SOME,
<= SOME,>= SOME!
=ALL,<>ALL,>ALL,<=ALL,>=ALL,!
= SOME,<> SOME,
< SOME,> SOME,
<= SOME,>= SOME
像模式（Likepattern）LIKELIKE
不像模式（Notlikepattern）NOTLIKENOTLIKE
X和y之间的值BETWEENxANDyBETWEENxANDy
不在x和y之间的值NOTBETWEENNOTBETWEEN
值存在EXISTSEXISTS
值不存在NOTEXISTSNOTEXISTS
值{为|不为}空ISNULL,ISNOTNULLSame.Also=NULL,
!
= NULLforbackwardcompatibility（notrecommended）.
模式匹配
SQLServer的LIKE关键字提供了有用的通配符搜索功能，这个功能在Oracle中不支持
除了所有的RDBMS都支持的（%）和（_）通配符以外，SQLServer还支持（[]）和（[^]）通配符。
（[]）字符用来查询在一个范围内的所有单个字符。
例如，如果你需要查询包含一个从a到f的字符的数据，
你可以这样写：
“LIKE'[a-f]'”或者“LIKE'[abcdef]'”。
这些附加的通配符的有效性在下表中给出。
OracleMicrosoftSQL
SELECT*FROMSTUDENT_ADMIN.STUDENT
WHERELNAMELIKE'A%'
ORLNAMELIKE'B%'
ORLNAMELIKE'C%'SELECT*FROMSTUDENT_ADMIN.STUDENT
WHERELNAMELIKE'[ABC]%'
[^]通配符用来标记那些不在特定范围内的字符。
例如，如果除了a到f以外的所有字符都是可以接受的，你可以这样书写：
LIKE'[^a-f]'或者LIKE'[^abcdef]'。
欲了解关于LIKE关键字的详细信息，请参阅SQLServer联机手册。
在比较中使用NULL
尽管MicrosoftSQLServer传统上支持SQL-92标准的和一些非标准的NULL行为，但是它还是支持Oracle中的NULL的用法。
为了支持分布式查询，SETANSI_NULLS必须设定为ON。
在进行连接的时候，SQLServer的SQLServerODBC驱动程序和OLEDB提供者自动把SETANSI_NULLS设定为ON。
这个设置可以在ODBC数据源、ODBC连接属性、或者是在连接到SQLServer之前在应用程序中设置的OLEDB连接属性中进行配置。
在从DB-Library应用程序中连接时，SETANSI_NULLS缺省为OFF。
当SETANSI_DEFAULTS为ON时，SETANSI_NULLS被允许。
欲了解关于NULL用法的详细信息，请参阅SQLServer联机手册。
字串连接
Oracle使用两个管道符号（||）来作为字串连接操作符，SQLServer则使用加号（+）。
这个差别要求你在应用程序中做小小的修改。
OracleMicrosoftSQL
SELECTFNAME||''||LNAMEASNAME
FROMSTUDENT_ADMIN.STUDENT
-----------------------------------------------
SELECTFNAME+''+LNAMEAS NAME
FROMSTUDENT_ADMIN.STUDENT
流控制（Control-of-Flow）语言
流控制语言控制SQL语句执行流，语句块以及存储过程。
PL/SQL和Transact-SQL提供了多数相同的结构，但是还是有一些语法差别。
关键字
这是两个RDBMS支持的关键字。
语句OraclePL/SQL---------------------MicrosoftSQL ServerTransact-SQL
声明变量DECLAREDECLARE
语句块BEGIN...END;BEGIN...END
条件处理IF…THEN,
ELSIF…THEN,
ELSE
ENDIF;
--------------------------------------------------------
IF…[BEGIN…END]
ELSE
[BEGIN…END]
ELSEIF
CASEexpression
无条件结束RETURN------------RETURN
无条件结束当前程序块后面的语句EXITBREAK
重新开始一个WHILE循环N/ACONTINUE
等待指定间隔N/A（dbms_lock.sleep）WAITFOR
循环控制WHILELOOP…ENDLOOP;
------------
LABEL…GOTOLABEL;
FOR…ENDLOOP;
LOOP…ENDLOOP;
WHILE
BEGIN…END
LABEL…GOTOLABEL
程序注释/*…*/,--/*…*/,--
打印输出RDBMS_OUT
升级会员 