Oracle 函數(shù)大全(帶例子哦)

1、Oracle 函數(shù)大全SQL中的單記錄函數(shù)1.ASCII返回與指定的字符對(duì)應(yīng)的十進(jìn)制數(shù);SQL> select ascii('A' A,ascii('a' a,ascii('0' zero,ascii(' ' space from dual;A A ZERO SPACE- - - -65 97 48 322.CHR給出整數(shù),返回對(duì)應(yīng)的字符;SQL> select chr(54740 zhao,chr(65 chr65 from dual;ZH C- -趙A3.CONCAT連接兩個(gè)字符串;SQL> select c

2、oncat('010-','88888888'|'轉(zhuǎn)23' 高乾競(jìng)電話from dual; 高乾競(jìng)電話-010-*轉(zhuǎn)234.INITCAP返回字符串并將字符串的第一個(gè)字母變?yōu)榇髮?xiě);SQL> select initcap('smith' upp from dual;UPP-Smith5.INSTR(C1,C2,I,J在一個(gè)字符串中搜索指定的字符,返回發(fā)現(xiàn)指定的字符的位置;C1 被搜索的字符串C2 希望搜索的字符串I 搜索的開(kāi)始位置,默認(rèn)為1J 出現(xiàn)的位置,默認(rèn)為1SQL> select instr('oracle

3、 traning','ra',1,2 instring from dual; INSTRING-96.LENGTH返回字符串的長(zhǎng)度;SQL> select name,length(name,addr,length(addr,sal,length(to_char(salfrom gao.nchar_tst;NAME LENGTH(NAME ADDR LENGTH(ADDR SAL LENGTH(TO_CHAR(SAL- - - - - - 高乾競(jìng) 3 北京市海錠區(qū) 6 9999.99 77.LOWER返回字符串,并將所有的字符小寫(xiě)SQL> select low

4、er('AaBbCcDd'AaBbCcDd from dual;AABBCCDD-aabbccdd8.UPPER返回字符串,并將所有的字符大寫(xiě)SQL> select upper('AaBbCcDd' upper from dual;UPPER-AABBCCDD9.RPAD和LPAD(粘貼字符RPAD 在列的右邊粘貼字符LPAD 在列的左邊粘貼字符SQL> select lpad(rpad('gao',10,'*',17,'*'from dual;LPAD(RPAD('GAO',1-*ga

5、o*不夠字符則用*來(lái)填滿10.LTRIM和RTRIMLTRIM 刪除左邊出現(xiàn)的字符串RTRIM 刪除右邊出現(xiàn)的字符串SQL> select ltrim(rtrim(' gaoqianjing ',' ',' ' from dual; LTRIM(RTRIM('-gaoqianjing11.SUBSTR(string,start,count取子字符串,從start開(kāi)始,取count個(gè)SQL> select substr('130*',3,8 from dual; SUBSTR('-0888888812.R

6、EPLACE('string','s1','s2'string 希望被替換的字符或變量s1 被替換的字符串s2 要替換的字符串SQL> select replace('he love you','he','i' from dual;REPLACE('H-i love you13.SOUNDEX返回一個(gè)與給定的字符串讀音相同的字符串SQL> create table table1(xmvarchar(8;SQL> insert into table1 values('

7、weather'SQL> insert into table1 values('wether'SQL> insert into table1 values('gao'SQL> select xm from table1 where soundex(xm=soundex('weather' XM-weatherwether14.TRIM('s' from 'string'LEADING 剪掉前面的字符TRAILING 剪掉后面的字符如果不指定,默認(rèn)為空格符15.ABS返回指定值的絕對(duì)值SQL

8、> select abs(100,abs(-100 from dual; ABS(100 ABS(-100- -100 10016.ACOS給出反余弦的值SQL> select acos(-1 from dual;ACOS(-1-3.141592717.ASIN給出反正弦的值SQL> select asin(0.5 from dual;ASIN(0.5-.5235987818.ATAN返回一個(gè)數(shù)字的反正切值SQL> select atan(1 from dual;ATAN(1-.7853981619.CEIL返回大于或等于給出數(shù)字的最小整數(shù)SQL> select

9、ceil(3.1415927 from dual; CEIL(3.1415927-420.COS返回一個(gè)給定數(shù)字的余弦SQL> select cos(-3.1415927 from dual; COS(-3.1415927-121.COSH返回一個(gè)數(shù)字反余弦值SQL> select cosh(20 from dual; COSH(20-24258259822.EXP返回一個(gè)數(shù)字e的n次方根SQL> select exp(2,exp(1 from dual; EXP(2 EXP(1- -7.3890561 2.718281823.FLOOR對(duì)給定的數(shù)字取整數(shù)SQL> se

10、lect floor(2345.67 from dual; FLOOR(2345.67-234524.LN返回一個(gè)數(shù)字的對(duì)數(shù)值SQL> select ln(1,ln(2,ln(2.7182818 from dual;LN(1 LN(2 LN(2.7182818- - -0 .69314718 .9999999925.LOG(n1,n2返回一個(gè)以n1為底n2的對(duì)數(shù)SQL> select log(2,1,log(2,4 from dual;LOG(2,1 LOG(2,4- -0 226.MOD(n1,n2返回一個(gè)n1除以n2的余數(shù)SQL> select mod(10,3,mod(

11、3,3,mod(2,3 from dual; MOD(10,3 MOD(3,3 MOD(2,3- - -1 0 227.POWER返回n1的n2次方根SQL> select power(2,10,power(3,3 from dual;POWER(2,10 POWER(3,3- -1024 2728.ROUND和TRUNC按照指定的精度進(jìn)行舍入SQL> select round(55.5,round(-55.4,trunc(55.5,trunc(-55.5 from dual; ROUND(55.5 ROUND(-55.4 TRUNC(55.5 TRUNC(-55.5- - - -

12、56 -55 55 -5529.SIGN取數(shù)字n的符號(hào),大于0返回1,小于0返回-1,等于0返回0SQL> select sign(123,sign(-100,sign(0 from dual;SIGN(123 SIGN(-100 SIGN(0- - -1 -1 030.SIN返回一個(gè)數(shù)字的正弦值SQL> select sin(1.57079 from dual; SIN(1.57079-131.SIGH返回雙曲正弦的值SQL> select sin(20,sinh(20 from dual; SIN(20 SINH(20- -.91294525 24258259832.SQ

13、RT返回?cái)?shù)字n的根SQL> select sqrt(64,sqrt(10 from dual; SQRT(64 SQRT(10- -8 3.162277733.TAN返回?cái)?shù)字的正切值SQL> select tan(20,tan(10 from dual;TAN(20 TAN(10- -2.2371609 .6483608334.TANH返回?cái)?shù)字n的雙曲正切值SQL> select tanh(20,tan(20 from dual;TANH(20 TAN(20- -1 2.237160935.TRUNC按照指定的精度截取一個(gè)數(shù)SQL> select trunc(124.1

14、666,-2 trunc1,trunc(124.16666,2 from dual;TRUNC1 TRUNC(124.16666,2- -100 124.1636.ADD_MONTHS增加或減去月份SQL> selectto_char(add_months(to_date('199912','yyyymm',2,'yyyymm' from dual; TO_CHA-200002SQL> select to_char(add_months(to_date('199912','yyyymm',-2,'

15、;yyyymm' from dual;TO_CHA-19991037.LAST_DAY返回日期的最后一天TO_CHAR(SY TO_CHAR(S- -SQL> select last_day(sysdate from dual;LAST_DAY(S-31-5月-0438.MONTHS_BETWEEN(date2,date1給出date2-date1的月份SQL> select months_between('19-12月-1999','19-3月-1999' mon_between from dual;MON_BETWEEN-9MON_BET

16、W-6039.NEW_TIME(date,'this','that'給出在this時(shí)區(qū)=other時(shí)區(qū)的日期和時(shí)間- -40.NEXT_DAY(date,'day'給出日期date和星期x之后計(jì)算下一個(gè)星期的日期SQL> select next_day('18-5月-2001','星期五' next_day from dual; NEXT_DAY-25-5月-0141.SYSDATE用來(lái)得到系統(tǒng)的當(dāng)前日期SQL> select to_char(sysdate,'dd-mm-yyyy day

17、9; from dual;TO_CHAR(SYSDATE,'-09-05-2004 星期日- -42.CHARTOROWID將字符數(shù)據(jù)類(lèi)型轉(zhuǎn)換為ROWID類(lèi)型SQL> select rowid,rowidtochar(rowid,ename from scott.emp; ROWID ROWIDTOCHAR(ROWID ENAME- - - AAAAfKAACAAAAEqAAAAAAAfKAACAAAAEqAAA SMITH AAAAfKAACAAAAEqAABAAAAfKAACAAAAEqAAB ALLEN AAAAfKAACAAAAEqAACAAAAfKAACAAAAEqAA

18、C WARD AAAAfKAACAAAAEqAADAAAAfKAACAAAAEqAAD JONES43.CONVERT(c,dset,sset將源字符串sset從一個(gè)語(yǔ)言字符集轉(zhuǎn)換到另一個(gè)目的dset字符集SQL> select convert('strutz','we8hp','f7dec' "conversion" from dual; conver-strutz44.HEXTORAW將一個(gè)十六進(jìn)制構(gòu)成的字符串轉(zhuǎn)換為二進(jìn)制45.RAWTOHEXT將一個(gè)二進(jìn)制構(gòu)成的字符串轉(zhuǎn)換為十六進(jìn)制46.ROWIDTOCHAR將RO

19、WID數(shù)據(jù)類(lèi)型轉(zhuǎn)換為字符類(lèi)型47.TO_CHAR(date,'format'SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss' from dual; TO_CHAR(SYSDATE,'YY-2004/05/09 21:14:4148.TO_DATE(string,'format'將字符串轉(zhuǎn)化為ORACLE中的一個(gè)日期49.TO_MULTI_BYTE將字符串中的單字節(jié)字符轉(zhuǎn)化為多字節(jié)字符SQL> select to_multi_byte('高' from dua

20、l;TO-高50.TO_NUMBER將給出的字符轉(zhuǎn)換為數(shù)字SQL> select to_number('1999' year from dual;YEAR-199951.BFILENAME(dir,file指定一個(gè)外部二進(jìn)制文件SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'52.CONVERT('x','desc','source'將x字段或變量的源source轉(zhuǎn)換為descSQL> select

21、sid,serial#,username,decode(command,2 0,'none',3 2,'insert',4 3,5 'select',6 6,'update',7 7,'delete',8 8,'drop',9 'other' cmd from v$session where type!='background'SID SERIAL# USERNAME CMD - - - -1 1 none2 1 none3 1 none4 1 none5 1 non

22、e6 1 none7 1275 none8 1275 none9 20 GAO select10 40 GAO none53.DUMP(s,fmt,start,lengthDUMP函數(shù)以fmt指定的內(nèi)部數(shù)字格式返回一個(gè)VARCHAR2類(lèi)型的值SQL> col global_name for a30SQL> col dump_string for a50SQL> set lin 200SQL> select global_name,dump(global_name,1017,8,5 dump_string from global_name;GLOBAL_NAME DUMP

23、_STRING-ORACLE.WORLD Typ=1 Len=12CharacterSet=ZHS16GBK: W,O,R,L,D54.EMPTY_BLOB(和EMPTY_CLOB(這兩個(gè)函數(shù)都是用來(lái)對(duì)大數(shù)據(jù)類(lèi)型字段進(jìn)行初始化操作的函數(shù)55.GREATEST返回一組表達(dá)式中的最大值,即比較字符的編碼大小. SQL> select greatest('AA','AB','AC' from dual;GR-ACSQL> select greatest('啊','安','天' from dua

24、l;GR-天56.LEAST返回一組表達(dá)式中的最小值SQL> select least('啊','安','天' from dual;LE-啊57.UID返回標(biāo)識(shí)當(dāng)前用戶(hù)的唯一整數(shù)SQL> show userUSER 為"GAO"SQL> select username,user_id from dba_users where user_id=uid; USERNAME USER_ID- -GAO 2558.USER返回當(dāng)前用戶(hù)的名字SQL> select user from dual;USER-GAO5

25、9.USEREVN返回當(dāng)前用戶(hù)環(huán)境的信息,opt可以是:ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,L ANG,VSIZEISDBA 查看當(dāng)前用戶(hù)是否是DBA如果是則返回trueSQL> select userenv('isdba' from dual; USEREN-FALSESQL> select userenv('isdba' from dual; USEREN-TRUESESSION返回會(huì)話標(biāo)志SQL> select userenv('sessionid

26、' from dual; USERENV('SESSIONID'-152ENTRYID返回會(huì)話人口標(biāo)志SQL> select userenv('entryid' from dual; USERENV('ENTRYID'-INSTANCE返回當(dāng)前INSTANCE的標(biāo)志SQL> select userenv('instance' from dual; USERENV('INSTANCE'-1LANGUAGE返回當(dāng)前環(huán)境變量SQL> select userenv('language

27、9; from dual; USERENV('LANGUAGE'- SIMPLIFIED CHINESE_CHINA.ZHS16GBK LANG返回當(dāng)前環(huán)境的語(yǔ)言的縮寫(xiě)SQL> select userenv('lang' from dual; USERENV('LANG'- ZHSTERMINAL返回用戶(hù)的終端或機(jī)器的標(biāo)志SQL> select userenv('terminal' from dual; USERENV('TERMINA-GAOVSIZE(X返回X的大小(字節(jié)數(shù)SQL> select vs

28、ize(user,user from dual;VSIZE(USER USER- -6 SYSTEM60.AVG(DISTINCT|ALLall表示對(duì)所有的值求平均值,distinct只對(duì)不同的值求平均值SQLWKS> create table table3(xmvarchar(8,sal number(7,2; 語(yǔ)句已處理。SQLWKS> insert into table3 values('gao',1111.11; SQLWKS> insert into table3 values('gao',1111.11; SQLWKS> in

29、sert into table3 values('zhu',5555.55; SQLWKS> commit;SQL> select avg(distinct sal from gao.table3;AVG(DISTINCTSAL-3333.33SQL> select avg(all sal from gao.table3;AVG(ALLSAL-2592.5961.MAX(DISTINCT|ALL求最大值,ALL表示對(duì)所有的值求最大值,DISTINCT表示對(duì)不同的值求最大值,相同的只取一次SQL> select max(distinct sal from scott.emp;MAX(DISTINCTSAL-500062.MIN(DISTINCT|ALL求最小值,ALL表示對(duì)所有的值求最小值,DISTINCT表示對(duì)不同的值求最小值,相同的只取一次SQL> select min(all sal from gao.table3;MIN(ALLSAL-1111.1163.STDDEV(distinct|all求標(biāo)準(zhǔn)差,ALL表示對(duì)所有的值求標(biāo)準(zhǔn)差,DISTINCT表示只對(duì)不同的值求標(biāo)準(zhǔn)差SQL> select stdd