Compare the function: SQL server vs ORACLE

6jhoucov17

　　数学函数
　　1.绝对值
　　S:selectabs(-1)value
　　O:selectabs(-1)valuefromdual
　　2.取整(大)
　　S:selectceiling(-1.001)value
　　O:selectceil(-1.001)valuefromdual
　　3.取整（小）
　　S:selectfloor(-1.001)value
　　O:selectfloor(-1.001)valuefromdual
　　4.取整（截取）
　　S:selectcast(-1.002asint)value
　　O:selecttrunc(-1.002)valuefromdual
　　5.四舍五入
　　S:selectround(1.23456,4)value1.23460
　　O:selectround(1.23456,4)valuefromdual1.2346
　　6.e为底的幂
　　S:selectExp(1)value2.7182818284590451
　　O:selectExp(1)valuefromdual2.71828182
　　7.取e为底的对数
　　S:selectlog(2.7182818284590451)value1
　　O:selectln(2.7182818284590451)valuefromdual;1
　　8.取10为底对数
　　S:selectlog10(10)value1
　　O:selectlog(10,10)valuefromdual;1
　　9.取平方
　　S:selectSQUARE(4)value16
　　O:selectpower(4,2)valuefromdual16
　　10.取平方根
　　S:selectSQRT(4)value2
　　O:selectSQRT(4)valuefromdual2
　　11.求任意数为底的幂
　　S:selectpower(3,4)value81
　　O:selectpower(3,4)valuefromdual81
　　12.取随机数
　　S:selectrand()value
　　O:selectsys.dbms_random.value(0,1)valuefromdual;
　　13.取符号
　　S:selectsign(-8)value-1
　　O:selectsign(-8)valuefromdual-1
　　14.圆周率
　　S:SELECTPI()value3.1415926535897931
　　O:不知道
　　15.sin,cos,tan参数都以弧度为单位
　　例如：selectsin(PI()/2)value得到1（SQLServer）
　　16.Asin,Acos,Atan,Atan2返回弧度
　　17.弧度角度互换(SQLServer，Oracle不知道)
　　DEGREES：弧度-〉角度
　　RADIANS：角度-〉弧度
　　数值间比较
　　18.求集合最大值
　　S:select max(value) value from
　　(select 1 value
　　union
　　select -2 value
　　union
　　select 4 value
　　union
　　select 3 value ) a
　　O:selectgreatest(1,-2,4,3)valuefromdual
　　19.求集合最小值
　　S:selectmin(value)valuefrom
　　(select1value
　　union
　　select-2value
　　union
　　select4value
　　union
　　select3value)a
　　O:selectleast(1,-2,4,3)valuefromdual
　　20.如何处理null值(F2中的null以10代替)
　　S:selectF1,IsNull(F2,10)valuefromTbl
　　O:selectF1,nvl(F2,10)valuefromTbl
　　21.求字符序号
　　S:selectascii('a')value
　　O:selectascii('a')valuefromdual
　　22.从序号求字符
　　S:selectchar(97)value
　　O:selectchr(97)valuefromdual
　　23.连接
　　S:select'11'+'22'+'33'value
　　O:selectCONCAT('11','22')33valuefromdual
　　23.子串位置--返回3
　　S:selectCHARINDEX('s','sdsq',2)value
　　O:selectINSTR('sdsq','s',2)valuefromdual
　　23.模糊子串的位置--返回2,参数去掉中间%则返回7
　　S:selectpatindex('%d%q%','sdsfasdqe')value
　　O:oracle没发现，但是instr可以通过第四个参数控制出现次数
　　selectINSTR('sdsfasdqe','sd',1,2)valuefromdual返回6
　　24.求子串
　　S:select substring('abcd',2,2)value
　　O:selectsubstr('abcd',2,2)valuefromdual
　　25.子串代替返回aijklmnef
　　S:SELECT STUFF('abcdef',2,3,'ijklmn')value
　　O:SELECTReplace('abcdef','bcd','ijklmn')valuefromdual
　　26.子串全部替换
　　S:没发现
　　O:selectTranslate('fasdbfasegas','fa','我')valuefromdual
　　27.长度
　　S:len,datalength
SELECT LEN('baiwentao')
SELECT datalength('baiwentao')
　　O:length
　　28.大小写转换lower,upper
SELECT upper('baiwentao')
29.单词首字母大写
　　S:没发现
　　O:selectINITCAP('abcddsafdf')valuefromdual
　　30.左补空格（LPAD的第一个参数为空格则同space函数）
　　S:select space(10)+'abcd' value
　　O:selectLPAD('abcd',14)valuefromdual
　　31.右补空格（RPAD的第一个参数为空格则同space函数）
　　S:select'abcd'+space(10)value
　　O:selectRPAD('abcd',14)valuefromdual
　　32.删除空格
　　S:ltrim,rtrim
select LTRIM(' bai ')
select rTRIM(' bai ')
select LTRIM(RTRIM(' bai '))
O:ltrim,rtrim,trim
　　33.重复字符串
　　S:select REPLICATE('abcd',2) value
　　O:没发现
　　34.发音相似性比较(这两个单词返回值一样，发音相同)
　　S:SELECT SOUNDEX('Smith'),SOUNDEX('Smythe')
　　O:SELECTSOUNDEX('Smith'),SOUNDEX('Smythe')fromdual
　　SQLServer中用SELECT DIFFERENCE('Smithers','Smythers')比较soundex的差
　　返回0-4，4为同音，1最高
　　日期函数
　　35.系统时间
　　S:select getdate()value
　　O:selectsysdatevaluefromdual
　　36.前后几日
　　直接与整数相加减
　　37.求日期
　　S:select convert(char(10),getdate(),20 )value
2011-07-12
O:selecttrunc(sysdate)valuefromdual
　　select to_char(sysdate,'yyyy-mm-dd')valuefromdual
　　38.求时间
　　S:select convert(char(8),getdate(),108) value
　　O:selectto_char(sysdate,'hh24:mm:ss')valuefromdual
　　39.取日期时间的其他部分
　　S:DATEPART和DATENAME函数（第一个参数决定）
　　O:to_char函数第二个参数决定
　　参数---------------------------------下表需要补充
　　yearyy,yyyy
　　quarterqq,q(季度)
　　monthmm,m(mO无效)
　　dayofyeardy,y(O表星期)
　　daydd,d(dO无效)
　　weekwk,ww(wkO无效)
　　weekdaydw(O不清楚)
　　Hourhh,hh12,hh24(hh12,hh24S无效)
　　minutemi,n(nO无效)
　　secondss,s(sO无效)
　　millisecondms(O无效)
　　----------------------------------------------
　　40.当月最后一天
　　S:不知道
　　O:selectLAST_DAY(sysdate)valuefromdual
　　41.本星期的某一天（比如星期日）
　　S:不知道
　　O:SELECTNext_day(sysdate,7)vauleFROMDUAL;
　　42.字符串转时间
　　S:可以直接转或者select cast('2004-09-08' as datetime) value
　　O:SELECTTo_date('2004-01-0522:09:38','yyyy-mm-ddhh24-mi-ss')vauleFROMDUAL;
　　43.求两日期某一部分的差（比如秒）
　　S:select datediff(ss,getdate(),getdate()+12.3) value
　　O:直接用两个日期相减（比如d1-d2=12.3）
　　SELECT(d1-d2)*24*60*60vauleFROMDUAL;
　　44.根据差值求新的日期（比如分钟）
　　S:select dateadd(mi,8,getdate()) value
　　O:SELECTsysdate+8/60/24vauleFROMDUAL;
　　45.求不同时区时间
　　S:不知道
　　O:SELECTNew_time(sysdate,'ydt','gmt')vauleFROMDUAL;
　　-----时区参数,北京在东8区应该是Ydt-------
　　ASTADT大西洋标准时间
　　BSTBDT白令海标准时间
　　CSTCDT中部标准时间
　　ESTEDT东部标准时间
　　GMT格林尼治标准时间
　　HSTHDT阿拉斯加?夏威夷标准时间
　　MSTMDT山区标准时间
　　NST纽芬兰标准时间
　　PSTPDT太平洋标准时间
　　YSTYDTYUKON标准时间
　　总结：
字符函数
下面是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
　
日期函数
下面是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
　
转换函数
下面是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
　
其它行级别的函数
下面是Oracle支持的其它行级别的函数以及它们的Microsoft SQL Server等价函数。
函数 Oracle --------------------Microsoft SQL　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
用户环境(audit trail) USERENV --------------------N/A
在CONNECT BY子句中的级别 LEVEL --------------------N/A
　
合计函数
下面是Oracle支持的合计函数和它们的Microsoft SQL Server等价函数。
函数 Oracle --------------------Microsoft SQL　Server
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 SQL　
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
[ELSE else_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 SQL　Server
字符到数字 TO_NUMBER(F') --------------------CONVERT(numeric, F')
数字到字符 TO_CHAR(10) --------------------CONVERT(char, 10)
字符到日期 TO_DATE(@-JUL-97')
TO_DATE(@-JUL-1997','dd-mon-yyyy')
TO_DATE('July 4, 1997', 'Month dd, yyyy') --------------------CONVERT(datetime, @-JUL-97')
CONVERT(datetime, @-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(嘑')-------------------- CONVERT(binary, 嘑')
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 SQL　Server
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 SQL　Server
等于 (=) (=)
大于 (>) (>)
小于 (<) (<)
大于或等于 (>=) (>=)
小于或等于 (<=) (<=)
不等于 (!=, <>, ^=) (!=, <>, ^=)
不大于，不小于 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 SQL　
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 SQL　
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 SQL　Server 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