Oracle SQL函數(shù)大全

1、-SQL函數(shù)-數(shù)字函數(shù)-返回?cái)?shù)字n的絕對(duì)值A(chǔ)BS(n-三角函數(shù)ACOS(nASIN(nATAN(nSIN(n-正弦COS(n-余弦-返回大于或等于n的最小整數(shù)CEIL(n-返回e的n次冪EXP(n-返回小于或等于n的最大整數(shù)FLOOR(n-返回n的自然對(duì)數(shù),n 必須大于0LN(n-返回 m 為底的 n 的對(duì)數(shù)LOG(m , n-返回兩個(gè)數(shù)取模MOD(m , n-返回四舍五入ROUND(n , mSQL>select round(12635.714265,0,round(12635.714265,-3,round(12635.714265,3from dual;-檢測(cè)數(shù)字的正負(fù),正返回 1

2、,負(fù)返回 -1,0 返回 0;GN(n-返回n的平方根SQRT(n-截取數(shù)字TRUNC(n , mSQL>select trunc(1245.567,trunc(1245.567,2,trunc(1245.567,-2from dual;TRUNC(1245.567 TRUNC(1245.567,2 TRUNC(1245.567,-2- - -12451245.561200-字符函數(shù)-連接字符串CONNECT(char1 , char2-將字符char中的首字符大寫,其他字符小寫INITCAP(char-將字符串轉(zhuǎn)換為小寫LOWER(char-將字符串轉(zhuǎn)換為大寫UPPER(char-取子

3、串在字符串中的位置INSTR(char1, char2 ,n, mSQL>select instr('this is apper','is',1,1from dual;INSTR('THISISAPPER','IS',1,1-3SQL>select instr('is this is apper','is',1,1from dual;INSTR('ISTHISISAPPER','IS',1,1-1SQL>select instr('is th

4、is is apper','is',1,3from dual;INSTR('ISTHISISAPPER','IS',1,3-9-取字符換的長(zhǎng)度LENGTH(char-在字符串1 左端和右端填充字符串2 直到長(zhǎng)度達(dá)到n 為止,若char1 的長(zhǎng)度> n 均返回char1 左端的n 個(gè)字符LPAD(char1 , n, char2RPAD(char1 , n, char2-去除char1 左端包含的set 中的任何字符LTRIM(char1 ,set-去除char1 兩端特定的字符TRIM(char from char1SQL>s

5、elect trim('o'from'i love you !'from dual;TRIM('O'FROM'ILOVEYOU!'-i love you !SQL>select trim('o'from'oi love youo !'from dual;TRIM('O'FROM'OILOVEYOUO!'-i love youo !SQL>select trim('o'from'oi love youo'from dual;T

6、RIM('O'FROM'OILOVEYOUO'-i love you-替換子串REPLACE(char, search_string , replacement_stringSQL>select replace('this is apple','apple','orange'from dual;REPLACE('THISISAPPLE','APPLE',-this is orange-日期時(shí)間函數(shù)-返回特定日期時(shí)間d 之后或之前的 n 個(gè)月對(duì)應(yīng)的日期時(shí)間ADD_MONTHS(s

7、ysdate, n-返回當(dāng)前回話所對(duì)應(yīng)的日期時(shí)間CURRENT_DATESQL>select current_date from dual;CURRENT_DATE-2012/5/1518CURRENT_TIMESTAMPSQL>select current_timestamp from dual;CURRENT_TIMESTAMP-取出日期中的特定數(shù)據(jù)EXTRACTSQL>select extract(year from sysdatefrom dual;EXTRACT(YEARFROMSYSDATE-2012-返回日期所在月份的最后一天LAST_DAY(dSQL>s

8、elect LAST_DAY(sysdatefrom dual;LAST_DAY(SYSDATE-2012/5/3119:00:3-返回日期相差的月數(shù)MONTHS_BETWEEN(d1 , d2SQL>select months_between(sysdate,'15-3月-12'from dual;MONTHS_BETWEEN(SYSDATE,'15-3月-12'-2-截?cái)嗳掌跁r(shí)間數(shù)據(jù)TRUNC(d , fmtSQL>select trunc(sysdate,'year'from dual;TRUNC(SYSDATE,'YE

9、AR'-2012/1/1-轉(zhuǎn)換函數(shù)-返回匹配特定表達(dá)式的結(jié)果DECODE(value, if1, then1, if2,then2, if3,then3, . . . default-將特定數(shù)字格式的字符串轉(zhuǎn)變成數(shù)值型TO_NUMBER(CHAR, fmt , nls_paramTO_NUMBER('1000.22','99999D99'-1000.22-將日期型轉(zhuǎn)化為字符串 TO_CHAR(date ,fmt , nls_param -將數(shù)值型轉(zhuǎn)變?yōu)関archar2 數(shù)據(jù)類型 TO_CHAR(n ,fmt , nls_param -將字符串轉(zhuǎn)變?yōu)镃LOB 類型 TO_CLOB(char SQL> select to_clob(n'華東交通大學(xué)' from dual; TO_CLOB(N'華東交通大學(xué)' -華東交通大學(xué) -轉(zhuǎn)變?yōu)槿掌谛?to_date(CHAR , fmt , nls_param SQL> select to_date('05-15-12' , 'MM-DD-YY' from dual; TO_DATE('05-15-12','MM-DD-YY