ORACLE内部函数大全[2]

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 grea