ORACLE內部函數大全

1SQL中的單記錄函數 21.ASCII 3返回與指定的字符對應的十進制數; 4SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; 5 6 A A ZERO SPACE 7--------- --------- --------- --------- 8 65 97 48 32 9 10 112.CHR 12給出整數,返回對應的字符; 13SQL> select chr(54740) zhao,chr(65) chr65 from dual; 14 15ZH C 16-- - 17趙 A 18 193.CONCAT 20連接兩個字符串; 21SQL> select concat('010-','88888888')||'轉23' 高乾競電話 from dual; 22 23高乾競電話 24---------------- 25010-88888888轉23 26 274.INITCAP 28返回字符串并將字符串的第一個字母變為大寫; 29SQL> select initcap('smith') upp from dual; 30 31UPP 32----- 33Smith 34 35 365.INSTR(C1,C2,I,J) 37在一個字符串中搜索指定的字符,返回發現指定的字符的位置; 38C1 被搜索的字符串 39C2 希望搜索的字符串 40I 搜索的開始位置,默認為1 41J 出現的位置,默認為1 42SQL> select instr('oracle traning','ra',1,2) instring from dual; 43 44 INSTRING 45--------- 46 9 47 48 496.LENGTH 50返回字符串的長度; 51SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst; 52 53NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)) 54------ ------------ ---------------- ------------ --------- -------------------- 55高乾競 3 北京市海錠區 6 9999.99 7 56 57 58 597.LOWER 60返回字符串,并將所有的字符小寫 61SQL> select lower('AaBbCcDd')AaBbCcDd from dual; 62 63AABBCCDD 64-------- 65aabbclearcase/" target="_blank" >ccdd 66 67 688.UPPER 69返回字符串,并將所有的字符大寫 70SQL> select upper('AaBbCcDd') upper from dual; 71 72UPPER 73-------- 74AABBCCDD 75 76 77 789.RPAD和LPAD(粘貼字符) 79RPAD 在列的右邊粘貼字符 80LPAD 在列的左邊粘貼字符 81SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual; 82 83LPAD(RPAD('GAO',1 84----------------- 85*******gao******* 86不夠字符則用*來填滿 87 88 8910.LTRIM和RTRIM 90LTRIM 刪除左邊出現的字符串 91RTRIM 刪除右邊出現的字符串 92SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual; 93 94LTRIM(RTRIM(' 95------------- 96gao qian jing 97 98 9911.SUBSTR(string,start,count) 100取子字符串,從start開始,取count個 101SQL> select substr('13088888888',3,8) from dual; 102 103SUBSTR(' 104-------- 10508888888 106 107 10812.REPLACE('string','s1','s2') 109string 希望被替換的字符或變量 110s1 被替換的字符串 111s2 要替換的字符串 112SQL> select replace('he love you','he','i') from dual; 113 114REPLACE('H 115---------- 116i love you 117 118 11913.SOUNDEX 120返回一個與給定的字符串讀音相同的字符串 121SQL> create table table1(xm varchar(8)); 122SQL> insert into table1 values('weather'); 123SQL> insert into table1 values('wether'); 124SQL> insert into table1 values('gao'); 125 126SQL> select xm from table1 where soundex(xm)=soundex('weather'); 127 128XM 129-------- 130weather 131wether 132 133 13414.TRIM('s' from 'string') 135LEADING 剪掉前面的字符 136TRAILING 剪掉后面的字符 137如果不指定,默認為空格符 138 13915.ABS 140返回指定值的絕對值 141SQL> select abs(100),abs(-100) from dual; 142 143 ABS(100) ABS(-100) 144--------- --------- 145 100 100 146 147 14816.ACOS 149給出反余弦的值 150SQL> select acos(-1) from dual; 151 152 ACOS(-1) 153--------- 1543.1415927 155 156 15717.ASIN 158給出反正弦的值 159SQL> select asin(0.5) from dual; 160 161ASIN(0.5) 162--------- 163.52359878 164 165 16618.ATAN 167返回一個數字的反正切值 168SQL> select atan(1) from dual; 169 170 ATAN(1) 171--------- 172.78539816 173 174 17519.CEIL 176返回大于或等于給出數字的最小整數 177SQL> select ceil(3.1415927) from dual; 178 179CEIL(3.1415927) 180--------------- 181 4 182 183 18420.COS 185返回一個給定數字的余弦 186SQL> select cos(-3.1415927) from dual; 187 188COS(-3.1415927) 189--------------- 190 -1 191 192 19321.COSH 194返回一個數字反余弦值 195SQL> select cosh(20) from dual; 196 197 COSH(20) 198--------- 199242582598 200 201 20222.EXP 203返回一個數字e的n次方根 204SQL> select exp(2),exp(1) from dual; 205 206 EXP(2) EXP(1) 207--------- --------- 2087.3890561 2.7182818 209 210 21123.FLOOR 212對給定的數字取整數 213SQL> select floor(2345.67) from dual; 214 215FLOOR(2345.67) 216-------------- 217 2345 218 219 22024.LN 221返回一個數字的對數值 222SQL> select ln(1),ln(2),ln(2.7182818) from dual; 223 224 LN(1) LN(2) LN(2.7182818) 225--------- --------- ------------- 226 0 .69314718 .99999999 227 228 22925.LOG(n1,n2) 230返回一個以n1為底n2的對數 231SQL> select log(2,1),log(2,4) from dual; 232 233 LOG(2,1) LOG(2,4) 234--------- --------- 235 0 2 236 237 23826.MOD(n1,n2) 239返回一個n1除以n2的余數 240SQL> select mod(10,3),mod(3,3),mod(2,3) from dual; 241 242MOD(10,3) MOD(3,3) MOD(2,3) 243--------- --------- --------- 244 1 0 2 245 246 24727.POWER 248返回n1的n2次方根 249SQL> select power(2,10),power(3,3) from dual; 250 251POWER(2,10) POWER(3,3) 252----------- ---------- 253 1024 27 254 255 25628.ROUND和TRUNC 257按照指定的精度進行舍入 258SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual; 259 260ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5) 261----------- ------------ ----------- ------------ 262 56 -55 55 -55 263 264 26529.SIGN 266取數字n的符號,大于0返回1,小于0返回-1,等于0返回0 267SQL> select sign(123),sign(-100),sign(0) from dual; 268 269SIGN(123) SIGN(-100) SIGN(0) 270--------- ---------- --------- 271 1 -1 0 272 273 27430.SIN 275返回一個數字的正弦值 276SQL> select sin(1.57079) from dual; 277 278SIN(1.57079) 279------------ 280 1 281 282 28331.SIGH 284返回雙曲正弦的值 285SQL> select sin(20),sinh(20) from dual; 286 287 SIN(20) SINH(20) 288--------- --------- 289.91294525 242582598 290 291 29232.SQRT 293返回數字n的根 294SQL> select sqrt(64),sqrt(10) from dual; 295 296 SQRT(64) SQRT(10) 297--------- --------- 298 8 3.1622777 299 300 30133.TAN 302返回數字的正切值 303SQL> select tan(20),tan(10) from dual; 304 305 TAN(20) TAN(10) 306--------- --------- 3072.2371609 .64836083 308 309 31034.TANH 311返回數字n的雙曲正切值 312SQL> select tanh(20),tan(20) from dual; 313 314 TANH(20) TAN(20) 315--------- --------- 316 1 2.2371609 317 318 319 32035.TRUNC 321按照指定的精度截取一個數 322SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual; 323 324 TRUNC1 TRUNC(124.16666,2) 325--------- ------------------ 326 100 124.16 327 328 329 33036.ADD_MONTHS 331增加或減去月份 332SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual; 333 334TO_CHA 335------ 336200002 337SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual; 338 339TO_CHA 340------ 341199910 342 343 34437.LAST_DAY 345返回日期的最后一天 346SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual; 347 348TO_CHAR(SY TO_CHAR((S 349---------- ---------- 3502004.05.09 2004.05.10 351SQL> select last_day(sysdate) from dual; 352 353LAST_DAY(S 354---------- 35531-5月 -04 356 357 35838.MONTHS_BETWEEN(date2,date1) 359給出date2-date1的月份 360SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual; 361 362MON_BETWEEN 363----------- 364 9 365SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual; 366 367 MON_BETW 368--------- 369 -60 370 371 37239.NEW_TIME(date,'this','that') 373給出在this時區=other時區的日期和時間 374SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time 375 2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual; 376 377BJ_TIME LOS_ANGLES 378------------------- ------------------- 3792004.05.09 11:05:32 2004.05.09 18:05:32 380 381 38240.NEXT_DAY(date,'day') 383給出日期date和星期x之后計算下一個星期的日期 384SQL> select next_day('18-5月-2001','星期五') next_day from dual; 385 386NEXT_DAY 387---------- 38825-5月 -01 389 390 391 39241.SYSDATE 393用來得到系統的當前日期 394SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual; 395 396TO_CHAR(SYSDATE,' 397----------------- 39809-05-2004 星期日 399trunc(date,fmt)按照給出的要求將日期截斷,如果fmt='mi'表示保留分,截斷秒 400SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh, 401 2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual; 402 403HH HHMM 404------------------- ------------------- 4052004.05.09 11:00:00 2004.05.09 11:17:00 406 407 408 40942.CHARTOROWID 410將字符數據類型轉換為ROWID類型 411SQL> select rowid,rowidtochar(rowid),ename from scott.emp; 412 413ROWID ROWIDTOCHAR(ROWID) ENAME 414------------------ ------------------ ---------- 415AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH 416AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN 417AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD 418AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES 419 420 42143.CONVERT(c,dset,sset) 422將源字符串 sset從一個語言字符集轉換到另一個目的dset字符集 423SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual; 424 425conver 426------ 427strutz 428 429 43044.HEXTORAW 431將一個十六進制構成的字符串轉換為二進制 432 433 43445.RAWTOHEXT 435將一個二進制構成的字符串轉換為十六進制 436 437 438 43946.ROWIDTOCHAR 440將ROWID數據類型轉換為字符類型 441 442 443 44447.TO_CHAR(date,'format') 445SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual; 446 447TO_CHAR(SYSDATE,'YY 448------------------- 4492004/05/09 21:14:41 450 451 452 45348.TO_DATE(string,'format') 454將字符串轉化為ORACLE中的一個日期 455 456 45749.TO_MULTI_BYTE 458將字符串中的單字節字符轉化為多字節字符 459SQL> select to_multi_byte('高') from dual; 460 461TO 462-- 463高 464 465 46650.TO_NUMBER 467將給出的字符轉換為數字 468SQL> select to_number('1999') year from dual; 469 470 YEAR 471--------- 472 1999 473 474 47551.BFILENAME(dir,file) 476指定一個外部二進制文件 477SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif')); 478 479 48052.CONVERT('x','desc','source') 481將x字段或變量的源source轉換為desc 482SQL> select sid,serial#,username,decode(command, 483 2 0,'none', 484 3 2,'insert', 485 4 3, 486 5 'select', 487 6 6,'update', 488 7 7,'delete', 489 8 8,'drop', 490 9 'other') cmd from v$session where type!='background'; 491 492 SID SERIAL# USERNAME CMD 493--------- --------- ------------------------------ ------ 494 1 1 none 495 2 1 none 496 3 1 none 497 4 1 none 498 5 1 none 499 6 1 none 500 7 1275 none 501 8 1275 none 502 9 20 GAO select 503 10 40 GAO none 504 505 50653.DUMP(s,fmt,start,length) 507DUMP函數以fmt指定的內部數字格式返回一個VARCHAR2類型的值 508SQL> col global_name for a30 509SQL> col dump_string for a50 510SQL> set lin 200 511SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name; 512 513GLOBAL_NAME DUMP_STRING 514------------------------------ -------------------------------------------------- 515ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D 516 517 51854.EMPTY_BLOB()和EMPTY_CLOB() 519這兩個函數都是用來對大數據類型字段進行初始化操作的函數 520 521 52255.GREATEST 523返回一組表達式中的最大值,即比較字符的編碼大小. 524SQL> select greatest('AA','AB','AC') from dual; 525 526GR 527-- 528AC 529SQL> select greatest('啊','安','天') from dual; 530 531GR 532-- 533天 534 535 53656.LEAST 537返回一組表達式中的最小值 538SQL> select least('啊','安','天') from dual; 539 540LE 541-- 542啊 543 544 54557.UID 546返回標識當前用戶的唯一整數 547SQL> show user 548USER 為"GAO" 549SQL> select username,user_id from dba_users where user_id=uid; 550 551USERNAME USER_ID 552------------------------------ --------- 553GAO 25 554 555 556 55758.USER 558返回當前用戶的名字 559SQL> select user from dual; 560 561USER 562------------------------------ 563GAO 564 565 56659.USEREVN 567返回當前用戶環境的信息,opt可以是: 568ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE 569ISDBA 查看當前用戶是否是DBA如果是則返回true 570SQL> select userenv('isdba') from dual; 571 572USEREN 573------ 574FALSE 575SQL> select userenv('isdba') from dual; 576 577USEREN 578------ 579TRUE 580SESSION 581返回會話標志 582SQL> select userenv('sessionid') from dual; 583 584USERENV('SESSIONID') 585-------------------- 586 152 587ENTRYID 588返回會話人口標志 589SQL> select userenv('entryid') from dual; 590 591USERENV('ENTRYID') 592------------------ 593 0 594INSTANCE 595返回當前INSTANCE的標志 596SQL> select userenv('instance') from dual; 597 598USERENV('INSTANCE') 599------------------- 600 1 601LANGUAGE 602返回當前環境變量 603SQL> select userenv('language') from dual; 604 605USERENV('LANGUAGE') 606---------------------------------------------------- 607SIMPLIFIED CHINESE_CHINA.ZHS16GBK 608LANG 609返回當前環境的語言的縮寫 610SQL> select userenv('lang') from dual; 611 612USERENV('LANG') 613---------------------------------------------------- 614ZHS 615TERMINAL 616返回用戶的終端或機器的標志 617SQL> select userenv('terminal') from dual; 618 619USERENV('TERMINA 620---------------- 621GAO 622VSIZE(X) 623返回X的大小(字節)數 624SQL> select vsize(user),user from dual; 625 626VSIZE(USER) USER 627----------- ------------------------------ 628 6 SYSTEM 629 630 631 63260.AVG(DISTINCT|ALL) 633all表示對所有的值求平均值,distinct只對不同的值求平均值 634SQLWKS> create table table3(xm varchar(8),sal number(7,2)); 635語句已處理。 636SQLWKS> insert into table3 values('gao',1111.11); 637SQLWKS> insert into table3 values('gao',1111.11); 638SQLWKS> insert into table3 values('zhu',5555.55); 639SQLWKS> commit; 640 641SQL> select avg(distinct sal) from gao.table3; 642 643AVG(DISTINCTSAL) 644---------------- 645 3333.33 646 647SQL> select avg(all sal) from gao.table3; 648 649AVG(ALLSAL) 650----------- 651 2592.59 652 653 65461.MAX(DISTINCT|ALL) 655求最大值,ALL表示對所有的值求最大值,DISTINCT表示對不同的值求最大值,相同的只取一次 656SQL> select max(distinct sal) from scott.emp; 657 658MAX(DISTINCTSAL) 659---------------- 660 5000 661 662 66362.MIN(DISTINCT|ALL) 664求最小值,ALL表示對所有的值求最小值,DISTINCT表示對不同的值求最小值,相同的只取一次 665SQL> select min(all sal) from gao.table3; 666 667MIN(ALLSAL) 668----------- 669 1111.11 670 671 67263.STDDEV(distinct|all) 673求標準差,ALL表示對所有的值求標準差,DISTINCT表示只對不同的值求標準差 674SQL> select stddev(sal) from scott.emp; 675 676STDDEV(SAL) 677----------- 678 1182.5032 679 680SQL> select stddev(distinct sal) from scott.emp; 681 682STDDEV(DISTINCTSAL) 683------------------- 684 1229.951 685 686 687 68864.VARIANCE(DISTINCT|ALL) 689求協方差 690 691SQL> select variance(sal) from scott.emp; 692 693VARIANCE(SAL) 694------------- 695 1398313.9 696 697 69865.GROUP BY 699主要用來對一組數進行統計 700SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno; 701 702 DEPTNO COUNT(*) SUM(SAL) 703--------- --------- --------- 704 10 3 8750 705 20 5 10875 706 30 6 9400 707 708 709 71066.HAVING 711對分組統計再加限制條件 712SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5; 713 714 DEPTNO COUNT(*) SUM(SAL) 715--------- --------- --------- 716 20 5 10875 717 30 6 9400 718SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ; 719 720 DEPTNO COUNT(*) SUM(SAL) 721--------- --------- --------- 722 20 5 10875 723 30 6 9400 724 725 72667.ORDER BY 727用于對查詢到的結果進行排序輸出 728SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc; 729 730 DEPTNO ENAME SAL 731--------- ---------- --------- 732 10 KING 5000 733 10 CLARK 2450 734 10 MILLER 1300 735 20 SCOTT 3000 736 20 FORD 3000 737 20 JONES 2975 738 20 ADAMS 1100 739 20 SMITH 800 740 30 BLAKE 2850 741 30 ALLEN 1600 742 30 TURNER 1500 743 30 WARD 1250 744 30 MARTIN 1250 745 30 JAMES 950

原文轉自：http://www.kjueaiud.com