Oracle SQL 內(nèi)置函數(shù)大全

1、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 32 2.CHR 給出整數(shù),返回對(duì)應(yīng)的字符; SQL> select chr(54740 zhao,chr(65 chr65 from dual; ZH C - - 趙 A 3.CONCAT 連接兩個(gè)字符串; SQL> sel

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

3、ect instr('oracle traning','ra',1,2 instring from dual; INSTRING - 9 6.LENGTH 返回字符串的長(zhǎng)度; SQL> select name,length(name,addr,length(addr,sal,length(to_char(sal from .nchar_tst; NAME LENGTH(NAME ADDR LENGTH(ADDR SAL LENGTH(TO_CHAR(SAL - - - - - - 高乾競(jìng) 3 北京市海錠區(qū) 6 9999.99 7 7.LOWER 返回字符串

4、,并將所有的字符小寫 SQL> select lower('AaBbCcDd'AaBbCcDd from dual; AABBCCDD - aabbccdd 8.UPPER 返回字符串,并將所有的字符大寫 SQL> select upper('AaBbCcDd' upper from dual; UPPER - AABBCCDD 9.RPAD和LPAD(粘貼字符 RPAD 在列的右邊粘貼字符 LPAD 在列的左邊粘貼字符 SQL> select lpad(rpad('gao',10,'*',17,'*&#

5、39;from dual; LPAD(RPAD('GAO',1 - *gao* 不夠字符則用*來(lái)填滿 10.LTRIM和RTRIM LTRIM 刪除左邊出現(xiàn)的字符串 RTRIM 刪除右邊出現(xiàn)的字符串 SQL> select ltrim(rtrim(' gao qian jing ',' ',' ' from dual; LTRIM(RTRIM(' - gao qian jing 11.SUBSTR(string,start,count 取子字符串,從start開(kāi)始,取count個(gè) SQL> select sub

6、str('130*',3,8 from dual; SUBSTR(' - 08888888 12.REPLACE('string','s1','s2' string 希望被替換的字符或變量 s1 被替換的字符串 s2 要替換的字符串 SQL> select replace('he love you','he','i' from dual; REPLACE('HELOVEYOU','HE','I' - i love you

7、13.SOUNDEX 返回一個(gè)與給定的字符串讀音相同的字符串 SQL> create table table1(xm varchar(8; SQL> insert into table1 values('weather' SQL> insert into table1 values('wether' SQL> insert into table1 values('gao' SQL> select xm from table1 where soundex(xm=soundex('weather' XM

8、- weather wether 14.TRIM('s' from 'string' LEADING 剪掉前面的字符 TRAILING 剪掉后面的字符 如果不指定,默認(rèn)為空格符 15.ABS 返回指定值的絕對(duì)值 SQL> select abs(100,abs(-100 from dual; ABS(100 ABS(-100 - - 100 100 16.ACOS 給出反余弦的值 SQL> select acos(-1 from dual; ACOS(-1 - 3.1415927 17.ASIN 給出反正弦的值 SQL> select asin(

9、0.5 from dual; ASIN(0.5 - .52359878 18.ATAN 返回一個(gè)數(shù)字的反正切值 SQL> select atan(1 from dual; ATAN(1 - .78539816 19.CEIL 返回大于或等于給出數(shù)字的最小整數(shù) SQL> select ceil(3.1415927 from dual; CEIL(3.1415927 - 4 20.COS 返回一個(gè)給定數(shù)字的余弦 SQL> select cos(-3.1415927 from dual; COS(-3.1415927 - -1 21.COSH 返回一個(gè)數(shù)字反余弦值 SQL>

10、select cosh(20 from dual; COSH(20 - 242582598 22.EXP 返回一個(gè)數(shù)字e的n次方根 SQL> select exp(2,exp(1 from dual; EXP(2 EXP(1 - - 7.3890561 2.7182818 23.FLOOR 對(duì)給定的數(shù)字取整數(shù) SQL> select floor(2345.67 from dual; FLOOR(2345.67 - 2345 24.LN 返回一個(gè)數(shù)字的對(duì)數(shù)值 SQL> select ln(1,ln(2,ln(2.7182818 from dual; LN(1 LN(2 LN(2

11、.7182818 - - - 0 .69314718 .99999999 25.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 2 26.MOD(n1,n2 返回一個(gè)n1除以n2的余數(shù) SQL> select mod(10,3,mod(3,3,mod(2,3 from dual; MOD(10,3 MOD(3,3 MOD(2,3 - - - 1 0 2 27.POWER 返回n1的n2次方根 SQL> select power(2,10,power(3,

12、3 from dual; POWER(2,10 POWER(3,3 - - 1024 27 28.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 - - - - 56 -55 55 -55 29.SIGN 取數(shù)字n的符號(hào),大于0返回1,小于0返回-1,等于0返回0 SQL> select sign(123,sign(-100,sign(0 from dua

13、l; SIGN(123 SIGN(-100 SIGN(0 - - - 1 -1 0 30.SIN 返回一個(gè)數(shù)字的正弦值 SQL> select sin(1.57079 from dual; SIN(1.57079 - 1 31.SIGH 返回雙曲正弦的值 SQL> select sin(20,sinh(20 from dual; SIN(20 SINH(20 - - .91294525 242582598 32.SQRT 返回?cái)?shù)字n的根 SQL> select sqrt(64,sqrt(10 from dual; SQRT(64 SQRT(10 - - 8 3.1622777

14、 33.TAN 返回?cái)?shù)字的正切值 SQL> select tan(20,tan(10 from dual; TAN(20 TAN(10 - - 2.2371609 .64836083 34.TANH 返回?cái)?shù)字n的雙曲正切值 SQL> select tanh(20, 46.ROWIDTOCHAR 將ROWID數(shù)據(jù)類型轉(zhuǎn)換為字符類型 47.TO_CHAR(date,'format' SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss' from dual; TO_CHAR(SYSDATE,'

15、YY - 2004/05/09 21:14:41 48.TO_DATE(string,'format' 將字符串轉(zhuǎn)化為ORACLE中的一個(gè)日期 49.TO_MULTI_BYTE 將字符串中的單字節(jié)字符轉(zhuǎn)化為多字節(jié)字符 SQL> select to_multi_byte('高' from dual; TO - 高 50.TO_NUMBER 將給出的字符轉(zhuǎn)換為數(shù)字 SQL> select to_number('1999' year from dual; YEAR - 1999 51.BFILENAME(dir,file 指定一個(gè)外部二進(jìn)制

16、文件 SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif' 52.CONVERT('x','desc','source' 將x字段或變量的源source轉(zhuǎn)換為desc SQL> select sid,serial#,username,decode(command, 2 0,'none', 3 2,'insert', 4 3, 5 'select', 6 6,'update

17、', 7 7,'delete', 8 8,'drop', 9 'other' cmd from v$session where type!='background' SID SERIAL# USERNAME CMD - - - - 1 1 none 2 1 none 3 1 none 4 1 none 5 1 none 6 1 none 7 1275 none 8 1275 none 9 20 GAO select 10 40 GAO none 53.DUMP(s,fmt,start,length DUMP函數(shù)以fmt指定

18、的內(nèi)部數(shù)字格式返回一個(gè)VARCHAR2類型的值 SQL> col global_name for a30 SQL> col dump_string for a50 SQL> set lin 200 SQL> select global_name,dump(global_name,1017,8,5 dump_string from global_name; GLOBAL_NAME DUMP_STRING - - ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D 54.EMPTY_BLOB(和EMPTY_C

19、LOB( 這兩個(gè)函數(shù)都是用來(lái)對(duì)大數(shù)據(jù)類型字段進(jìn)行初始化操作的函數(shù) 55.GREATEST 返回一組表達(dá)式中的最大值,即比較字符的編碼大小. SQL> select greatest('AA','AB','AC' from dual; GR - AC SQL> select greatest('啊','安','天' from dual; GR - 天 56.LEAST 返回一組表達(dá)式中的最小值 SQL> select least('啊','安',

20、9;天' from dual; LE - 啊 57.UID 返回標(biāo)識(shí)當(dāng)前用戶的唯一整數(shù) SQL> show user USER 為"GAO" SQL> select username,user_id from dba_users where user_id=uid; USERNAME USER_ID - - GAO 25 58.USER 返回當(dāng)前用戶的名字 SQL> select user from dual; USE R - GAO 59.USEREVN 返回當(dāng)前用戶環(huán)境的信息,opt可以是: ENTRYID,SESSIONID,TERMINAL

21、,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE ISDBA 查看當(dāng)前用戶是否是DBA如果是則返回true SQL> select userenv('isdba' from dual; USEREN - FALSE SQL> select userenv('isdba' from dual; USEREN - TRUE SESSION 返回會(huì)話標(biāo)志 SQL> select userenv('sessionid' from dual; USERENV('SESSIONID'

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

23、NGUAGE' - SIMPLIFIED CHINESE_CHINA.ZHS16GBK LANG 返回當(dāng)前環(huán)境的語(yǔ)言的縮寫 SQL> select userenv('lang' from dual; USERENV('LANG' - ZHS TERMINAL 返回用戶的終端或機(jī)器的標(biāo)志 SQL> select userenv('terminal' from dual; USERENV('TERMINA - GAO VSIZE(X 返回X的大小(字節(jié)數(shù) SQL> select vsize(user,user fro

24、m dual; VSIZE(USER USER - - 6 SYSTEM 60.AVG(DISTINCT|ALL all表示對(duì)所有的值求平均值,distinct只對(duì)不同的值求平均值 SQLWKS> create table table3(xm varchar(8,sal number(7,2; 語(yǔ)句已處理。 SQLWKS> insert into table3 values('gao',1111.11; SQLWKS> insert into table3 values('gao',1111.11; SQLWKS> insert into

25、 table3 values('zhu',5555.55; SQLWKS> commit; SQL> select avg(distinct sal from gao.table3; AVG(DISTINCTSAL - 3333.33 SQL> select avg(all sal from gao.table3; AVG(ALLSAL - 2592.59 61.MAX(DISTINCT|ALL 求最大值,ALL表示對(duì)所有的值求最大值,DISTINCT表示對(duì)不同的值求最大值,相同的只取一次 SQL> select max(distinct sal fro

26、m scott.emp; MAX(DISTINCTSAL - 5000 62.MIN(DISTINCT|ALL 求最小值,ALL表示對(duì)所有的值求最小值,DISTINCT表示對(duì)不同的值求最小值,相同的只取一次 SQL> select min(all sal from gao.table3; MIN(ALLSAL - 1111.11 63.STDDEV(distinct|all 求標(biāo)準(zhǔn)差,ALL表示對(duì)所有的值求標(biāo)準(zhǔn)差,DISTINCT表示只對(duì)不同的值求標(biāo)準(zhǔn)差 SQL> select stddev(sal from scott.emp; STDDEV(SAL - 1182.5032 SQL> select stddev(distinct sal from scott.emp; STDDEV(DISTINCTSAL - 1229.