SQL Server與oracle兩者區(qū)別之函數(shù)區(qū)別

kiss丶筱魔 2010-10-27

展開全文

SQL Server與oracle兩者區(qū)別之函數(shù)區(qū)別

Oracle和SQL Server的常用函數(shù)對(duì)比

1．?dāng)?shù)學(xué)函數(shù)

①絕對(duì)值
S：select abs(-1) value
O：select abs(-1) value from dual
②取整(大)
S：select ceiling(-001) value
O：select ceil(-001) value from dual
③取整（?。?
S：select floor(-001) value
O：select floor(-001) value from dual
④取整（截?。?
S：select cast(-002 as int) value
O：select trunc(-002) value from dual
⑤四舍五入
S：select round(23456,4) value 23460
O：select round(23456,4) value from dual 2346
⑥e為底的冪
S：select Exp(1) value
O：select Exp(1) value from dual
⑦取e為底的對(duì)數(shù)
S：select log(7182818284590451) value
O：select ln(7182818284590451) value from dual;
⑧取10為底對(duì)數(shù)
S：select log10(10) value
O：select log(10,10) value from dual;
⑨取平方
S：select SQUARE(4) value
O：select power(4,2) value from dual
⑩取平方根
S：select SQRT(4) value
O：select SQRT(4) value from dual
求任意數(shù)為底的冪
S：select power(3,4) value
O：select power(3,4) value from dual
取隨機(jī)數(shù)
S：select rand() value
O：select sys.dbms_random.value(0,1) value from dual;
取符號(hào)
S：select sign(-8) value -1
O：select sign(-8) value from dual -1

2．?dāng)?shù)值比較

①求集合最大值
S：select max(value) value from
(select 1 value union
select -2 value union
select 4 value union
select 3 value)a
O：select greatest(1,-2,4,3) value from dual
②求集合最小值
S：select min(value) value from
(select 1 value union
select -2 value union
select 4 value union
select 3 value)a
O：select least(1,-2,4,3) value from dual
③如何處理null值（F2字段中的null以10代替）
S：select F1,IsNull(F2,10) value from Tbl
O：select F1,nvl(F2,10) value from Tbl

3．字符串函數(shù)

①求字符序號(hào)
S：select ascii('a') value
O：select ascii('a') value from dual
②從序號(hào)求字符
S：select char(97) value
O：select chr(97) value from dual
③連接
S：select '11'+'22'+'33' value
O：select CONCAT('11','22')||33 value from dual
④子串位置--返回3
S：select CHARINDEX('s','sdsq',2) value
O：select INSTR('sdsq','s',2) value from dual
⑤模糊子串的位置--返回2，參數(shù)去掉中間%則返回7
S：select patindex('%d%q%','sdsfasdqe') value
O：oracle沒發(fā)現(xiàn)，但是instr可以通過第四個(gè)參數(shù)控制出現(xiàn)次數(shù)
select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6
⑥求子串
S：select substring('abcd',2,2) value
O：select substr('abcd',2,2) value from dual
⑦子串代替--返回aijklmnef
S：SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
O：SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual
⑧左補(bǔ)空格（LPAD的第1個(gè)參數(shù)為空格則同space函數(shù)）
S：select space(10)+'abcd' value
O：select LPAD('abcd',14) value from dual
⑨右補(bǔ)空格（RPAD的第1個(gè)參數(shù)為空格則同space函數(shù)）
S：select 'abcd'+space(10) value
O：select RPAD('abcd',14) value from dual
⑩發(fā)音相似性比較（這2個(gè)單詞返回值一樣，發(fā)音相同）
S：select soundex ('Smith'), soundex ('Smythe')
O：SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual
SQL Server中用SELECT DIFFERENCE('Smithers', 'Smythers')

比較soundex的差返回0～4，4為同音，1最高

4．日期函數(shù)

①系統(tǒng)時(shí)間
S：select getdate() value
O：select sysdate value from dual
②求日期
S：select convert(char(10),getdate(),20) value
O：select trunc(sysdate) value from dual
select to_char(sysdate,'yyyy-mm-dd') value from dual
③求時(shí)間
S：select convert(char(8),getdate(),108) value
O：select to_char(sysdate,'hh24:mm:ss') value from dual
④當(dāng)月最后一天
S：不知道
O：select LAST_DAY(sysdate) value from dual
⑤本星期的某一天（比如星期日）
S：不知道
O：SELECT Next_day(sysdate,7) vaule FROM DUAL;
⑥字符串轉(zhuǎn)時(shí)間
S：select cast('2004-09-08'as datetime) value
O：SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss')

vaule FROM DUAL;
⑦求2日期某一部分的差（比如秒）
S：select datediff(ss,getdate(),getdate()+3) value
O：直接用兩個(gè)日期相減（比如d1-d2=3）
SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
⑧根據(jù)差值求新的日期（比如分鐘）
S：select dateadd(mi,8,getdate()) value
O：SELECT sysdate+8/60/24 vaule FROM DUAL;

ORACLE內(nèi)部函數(shù)大全以及與SQLSERVER的區(qū)別：
下面是Oracle支持的字符函數(shù)和它們的Microsoft SQL Server等價(jià)函數(shù)。

函數(shù) Oracle Microsoft SQL　Server
把字符轉(zhuǎn)換為ASCII :ASCII ASCII
字串連接: CONCAT --------------(expression + expression)
把ASCII轉(zhuǎn)換為字符 CHR, CHAR
返回字符串中的開始字符（左起） INSTR ,---------------CHARINDEX
把字符轉(zhuǎn)換為小寫 LOWER ---------------------LOWER
把字符轉(zhuǎn)換為大寫 UPPER-------------------- UPPER
填充字符串的左邊 LPAD --------------------N/A
清除開始的空白 LTRIM--------------------LTRIM
清除尾部的空白 RTRIM --------------------RTRIM
字符串中的起始模式（pattern） INSTR --------------------PATINDEX
多次重復(fù)字符串 RPAD --------------------REPLICATE
字符串的語音表示 SOUNDEX --------------------SOUNDEX
重復(fù)空格的字串 RPAD --------------------SPACE
從數(shù)字?jǐn)?shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù) TO_CHAR --------------------STR
子串 SUBSTR --------------------SUBSTRING
替換字符 REPLACE --------------------STUFF
將字串中的每個(gè)詞首字母大寫 INITCAP --------------------N/A
翻譯字符串 TRANSLATE --------------------N/A
字符串長度 LENGTH-------------------- DATELENGTH or LEN
列表中最大的字符串 GREATEST-------------------- N/A
列表中最小的字符串 LEAST --------------------N/A
如果為NULL則轉(zhuǎn)換字串 NVL-------------------- ISNULL

　

日期函數(shù)

下面是Oracle支持的日期函數(shù)和它們的Microsoft SQL Server等價(jià)函數(shù)。

函數(shù) Oracle --------------------Microsoft SQL　Server
日期相加 (date column +/- value) or ADD_MONTHS   --------------------DATEADD

兩個(gè)日期的差 (date column +/- value) or MONTHS_BETWEEN   --------------------DATEDIFF
當(dāng)前日期和時(shí)間 SYSDATE --------------------GETDATE()
一個(gè)月的最后一天 LAST_DAY   --------------------N/A
時(shí)區(qū)轉(zhuǎn)換 NEW_TIME   --------------------N/A
日期后的第一個(gè)周日 NEXT_DAY   --------------------N/A
代表日期的字符串 TO_CHAR --------------------DATENAME
代表日期的整數(shù) TO_NUMBER (TO_CHAR))   --------------------DATEPART
日期舍入 ROUND   --------------------CONVERT
日期截?cái)?TRUNC   --------------------CONVERT
字符串轉(zhuǎn)換為日期 TO_DATE   --------------------CONVERT
如果為NULL則轉(zhuǎn)換日期 NVL -------------------- ISNULL

　

轉(zhuǎn)換函數(shù)

下面是Oracle支持的轉(zhuǎn)換函數(shù)和它們的Microsoft SQL Server等價(jià)函數(shù)。

函數(shù) Oracle   --------------------Microsoft SQL　Server
數(shù)字轉(zhuǎn)換為字符 TO_CHAR   --------------------CONVERT
字符轉(zhuǎn)換為數(shù)字 TO_NUMBER   --------------------CONVERT
日期轉(zhuǎn)換為字符 TO_CHAR   --------------------CONVERT
字符轉(zhuǎn)換為日期 TO_DATE CONVERT
16進(jìn)制轉(zhuǎn)換為2進(jìn)制 HEX_TO_RAW   --------------------CONVERT
2進(jìn)制轉(zhuǎn)換為16進(jìn)制 RAW_TO_HEX   --------------------CONVERT

　

其它行級(jí)別的函數(shù)

下面是Oracle支持的其它行級(jí)別的函數(shù)以及它們的Microsoft SQL Server等價(jià)函數(shù)。

函數(shù) Oracle   --------------------Microsoft SQL　Server
返回第一個(gè)非空表達(dá)式 DECODE -------------------------------------COALESCE
當(dāng)前序列值 CURRVAL   --------------------N/A
下一個(gè)序列值 NEXTVAL   --------------------N/A

用戶登錄賬號(hào)ID數(shù)字 UID --------------------SUSER_ID
用戶登錄名 USER   --------------------SUSER_NAME
用戶數(shù)據(jù)庫ID數(shù)字 UID   --------------------USER_ID
用戶數(shù)據(jù)庫名 USER   --------------------USER_NAME
當(dāng)前用戶 CURRENT_USER -------------------- CURRENT_USER
用戶環(huán)境(audit trail) USERENV   --------------------N/A
在CONNECT BY子句中的級(jí)別 LEVEL   --------------------N/A

　

合計(jì)函數(shù)

下面是Oracle支持的合計(jì)函數(shù)和它們的Microsoft SQL Server等價(jià)函數(shù)。

函數(shù) 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

　

條件測(cè)試

Oracle的DECODE語句和Microsoft SQL Server的CASE表達(dá)式都執(zhí)行條件測(cè)試。
當(dāng)test_value中的值和后面的任何表達(dá)式匹配的時(shí)候，相關(guān)的值就返回。如果沒有找到任何匹配的值，就返回default_value。
如果沒有指定default_value，在沒有匹配的時(shí)候，DECODE和CASE都返回一個(gè)NULL。下表顯示了該語句的語法，
同時(shí)給出了轉(zhuǎn)換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表達(dá)式可以支持用SELECT語句執(zhí)行布爾測(cè)試，這是DECODE命令所不允許的。欲了解關(guān)于CASE表達(dá)式的詳細(xì)信息，
請(qǐng)參閱SQL Server聯(lián)機(jī)手冊(cè)。

　

把值轉(zhuǎn)換為不同的數(shù)據(jù)類型

Microsoft SQL Server的CONVERT和CAST函數(shù)都是多目標(biāo)轉(zhuǎn)換函數(shù)。它們提供了相似的功能，
把一種數(shù)據(jù)類型的表達(dá)式轉(zhuǎn)換為另一種數(shù)據(jù)類型的表達(dá)式，并且支持多種專門數(shù)據(jù)的格式。

CAST(expression AS data_type)
CONVERT (data type[(length)], expression [, style])
CAST是一個(gè)SQL-92標(biāo)準(zhǔn)的函數(shù)。這些函數(shù)執(zhí)行同Oracle的TO_CHAR、TO_NUMBER、TO_DATE、HEXTORAW以及RAWTOTEXT函數(shù)相同的功能。

這里所指的數(shù)據(jù)類型是任何表達(dá)式將被轉(zhuǎn)換成為的系統(tǒng)數(shù)據(jù)類型。不能使用用戶定義的數(shù)據(jù)類型。長度參數(shù)是可選的，
該參數(shù)用于char、varchar、binary以及varbinary數(shù)據(jù)類型。允許的最大長度是8000。

轉(zhuǎn)換 Oracle Microsoft SQL　Server
字符到數(shù)字 TO_NUMBER(?')   --------------------CONVERT(numeric, ?')
數(shù)字到字符 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進(jìn)制到2進(jìn)制 HEXTORAW(?F')-------------------- CONVERT(binary, ?F')
2進(jìn)制到16進(jìn)制 RAWTOHEX(binary_column) --------------------CONVERT(char, binary_column)

　

請(qǐng)注意字符串是怎樣轉(zhuǎn)換為日期的。在Oracle中，缺省的日期格式模型是“DD-MON-YY”如果你使用任何其它格式，
你必須提供一個(gè)合適的日期格式模型。CONVERT函數(shù)自動(dòng)轉(zhuǎn)換標(biāo)準(zhǔn)日期格式，不需要任何格式模型。

從日期轉(zhuǎn)換到字符串時(shí)，CONVERT函數(shù)的缺省輸出是“dd mon yyyy hh:mm:ss:mmm(24h)”。
用一個(gè)數(shù)字風(fēng)格代碼來格式化輸出，使它能輸出為其它類型的日期格式模型。欲了解CONVERT函數(shù)的詳細(xì)信息，請(qǐng)參閱SQL Server聯(lián)機(jī)手冊(cè)。

下表顯示了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)

　

用戶定義函數(shù)

Oracle PL/SQL函數(shù)可以在Oracle SQL語句中使用。在Microsoft SQL Server中一般可以通過其它方式來實(shí)現(xiàn)同樣的功能。

在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.

函數(shù)

1.轉(zhuǎn)換函數(shù)

Oracle 中有to_char(),to_date(),to_number()

Ms sql 中有cast,convert

2.系統(tǒng)日期：

Oracle: sysdate

Ms sql: getdate()

如

Select sysdate from dual 一定要from子句

Select getdate() 不一定要from子句

3.Decode函數(shù)相當(dāng)if else，或者ms 中的case語句

語法是decode(value,if1,then1,if2,then2....)

如decode(col1,'1','true','0','fase')

4.常規(guī)函數(shù)

Nvl 語法是NVL(EXP1,EXP2) 表示如果ex1為空則返回ex2

Nvl2 語法是nvl(exp1,exp2,exp3)表示如果ex1為空，則返回ex3,否則返回ex2

Nullif 語法是nullif(ex1,ex2) 如果這兩個(gè)表達(dá)式相等則返回空

coalesce語法是coalesce（ex1,ex2,....exn）返回第一個(gè)非空表達(dá)式

5.分組函數(shù)

Oracle 中的分組函數(shù)Rollup,cube

Rollup返回的結(jié)果集包含分組行和小計(jì)行，cube產(chǎn)生交叉報(bào)表

如:

Select a,b,sum(c) from tabname group by rollup(a,b)

Select a,b,sum(c) from tabname group by cube(a,b)

Ms sql中的分組函數(shù) compute 和 compute by

Compute子句為行聚集函數(shù)生成匯總值，該匯總值做為一個(gè)附加的行顯示

在結(jié)果集中。沒有GROUP BY 子句的情況下，也可以使用關(guān)鍵字COMPUTE.

此關(guān)鍵字使用MAX,MIN,SUM,COUNT,AVG等函數(shù)生成匯總值，而compute by

則在控制中斷時(shí)給出該匯總值,compute by 必須包括在order by 子句中。

還有很多函數(shù)，諸如日期函數(shù)，字符串函數(shù)等等，就不一一作比較了。

Oracle中還有很多比較好的分析函數(shù)，也不列舉了。

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： kiss丶筱魔 > 《學(xué)習(xí)天地》

舉報(bào)/認(rèn)領(lǐng)