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-------- 65aabbccdd 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