ZT: Oracle 最常用功能函数经典汇总

Oracle 最常用功能函数经典汇总

来源：ChinaITLab 收集整理

2004-6-14 10:58:00

* SQL Group Function * s (num can be a column or ex pression)　　　　　　　　　　 　　(null values are ign * ored, default between distin ct and all is all)　　　　　　 　　******************** *************** **************************** **************** 　　AVG([distinct or all] num)　　　 -- average value 　　COUNT(distinct or all] num)　　 -- number of values 　　MAX([distinct or all ] num)　　　-- maximum value 　　MAX([distinct or all] num)　　　 -- minimum value 　　STDDEV([distinct or all] num)　 -- standard devi ation 　　SUM([distinct or all ] num)　　　-- sum of values 　　VARIANCE([distinct o r all] num) -- variance of v alues 　　******************************** *********************** ************************ 　　* Miscellaneaous Functions :　　 * 　　　　　　　　　　　　　　　　　　　　　　　 　　******************** *************** **************************** **************** 　　DECODE(expr, srch1, return1 [,srch2, return2...] , default] 　　　　　 -- if no search matches t he expression then the default is returned, 　　　　　 -- otherwise, the first search that match es will cause 　　　　　 -- the corres ponding return value to be r eturned 　　DUMP(column_name [,fmt [,start_p os [, length]]]) 　　　　　-- returns an column internal oracle format, used for getting info about a 　　　　　-- format options : 8 = oc tal, 10 = decimel, 16 = hex, 17 = characters 　　　　　-- return type codes : 1 = varchar2, 2 = n umber, 8 = long, 12 = date, 　　　　　--　 23 = raw, 24 = long raw, 69 = rowid, 96 = char, 106 = mlslabel 　　GREATEST(expr [,expr2 [, expr3...]]        　　　　　-- returns the largest val ue of all expressions 　　LEAST(expr [,expr2 [, expr3...]]              　　　　　-- returns the smallest value of all expre ssions 　　NVL(expr1 ,expr2                                              　　　　　-- if expr1 is not null, i t is returned, otherwise expr2 is returned 　　SQLCODE                                                                　　　　　-- returns sql error code query, of last error.　Can not be used directly in 　　　　　-- value must be set to local variable fir st 　　SQLERRM                                                                　　　　　-- returns sql in query, error message of last error .　Can not be used directly 　　　　　-- value must be set to lo cal variable first 　　UID                                                                        　　　　　-- returns the user id of the user you are logged on as 　　　　　-- useful in s electing information from lo w level sys tables 　　USER                                                                      　　　　　-- returns the user name of the user you a re logged on as 　　USERENV('option')                                            　　　　　-- returns inf ormation about the user you are logged on as 　　　　　-- options : E NTRYID, SESSIONID, TERMINAL, LANGUAGE, LABEL, OSDBA 　　　　　--　　　　　 ( all options not available in all Oracle versions) 　　VSIZE(expr)                                                        　　　　　-- returns the number of b ytes used by the expression 　　　　　-- useful in s electing information about t able space requirements 　　******************** *************** **************************** **************** 　　* SQL Date Functions (dt represe * nts oracle date and time)　　　　　　　　　　 　　* (functions return * an oracle date unless otherw ise specified)　　　　　　　　 　　******************************** *********************** ************************ 　　ADD_MONTHS(dt, num) 　　　-- adds num months to dt (num can be negative) 　　LAST_DAY(dt)　　　　 　　 -- last day of month in month containing dt 　　MONTHS_BETWEEN(dt1, dt2) -- retu dt2 rns fractional value of months between dt1, 　　NEW_TIME(dt, tz1, tz zone 2 2)　 -- dt = date in time zo ne 1, returns date in time 　　NEXT_DAY(dt, str)　　　　-- date etc..) of first (str) after dt (str = 'Monday', 　　SYSDATE　　　　　　　　　-- present system date 　　ROUND(dt [,fmt]　　　　　-- roun ds dt as specified by format fmt 　　TRUNC(dt [,fmt]　　 　　　-- truncates dt as spe cified by format fmt 　　******************************** *********************** ************************ 　　* Number Functions :　　　　　　 * 　　　　　　　　　　　　　　　　　　　　　　　 　　******************************** *********************** ************************ 　　ABS(num)　　　　　　 -- absolute value of num 　　CEIL(num)　　　　　　-- smallest integer > or = num 　　COS(num)　　　　　　 -- cosine(n um), num in radians 　　COSH(num)　　　　　 　-- hyperbolic cosine(num) 　　EXP(num)　　　　　　 -- e raised to the num powe r 　　FLOOR(num)　　　　　 -- largest integer < or = num 　　LN(num)　　　　　　　-- natural logarithm of num 　　LOG(num2, num1)　　　-- logarith m base num2 of num1 　　MOD(num2, num1)　　　-- remainde r of num2 / num1 　　POWER(num2, num1)　 　-- num2 raised to the num1 power 　　ROUND(num1 [,num2]　 -- num1 rou nded to num2 decimel places (default 0) 　　SIGN(num)　　　　　　-- sign of num * 1, 0 if num = 0 　　SIN(num)　　　　　　 -- sin(num), num in radians 　　SINH(num)　　　　　　-- hyperbolic sine(num) 　　SQRT(num)　　　　　　-- square root of num      　　TAN(num)　　　　　　 -- tangent( num), num in radians 　　TANH(num)　　　　　 　-- hyperbolic tangent(num) 　　TRUNC(num1 [,num2]　 -- truncate num1 to num2 decimel places (default 0) 　　******************************** *********************** ************************ 　　* String Functions, * String Result :　　　　　　 　　　　　　　　　　　　　　　 　　******************************** *********************** ************************ 　　(num)　　　　　　　　　 -- ASCII character for num 　　CHR(num)　　　　　　 　　-- ASCII character for n um 　　CONCAT(str1, str2)　　　-- str1 concatenated with str2 (same as str1||str2) 　　INITCAP(str)　　　　 　　-- capitalize first lett er of each word in str 　　LOWER(str)　　　　　　　-- str w ith all letters in lowercase 　　LPAD(str1, num [,str2]) -- left spaces) pad str1 to length num with str2 (default 　　LTRIM(str [,set])　 　　 -- remove set from left side of str (default spaces) 　　NLS_INITCAP(str [,nl s_val]) -- same as initcap f or different languages 　　NLS_LOWER(str [,nls_ val])　 -- same as lower for different languages 　　REPLACE(str1, str2 [,str3]) -- r eplaces str2 with str3 in str1 　　　　　　　　　　　　　　　　 -- deletes str2 from str1 if str3 is omitted 　　RPAD(str1, num [,str (default spaces) 2])　　 -- right pad str1 to length num with str2 　　RTRIM(str [,set])　 spaces) 　　　　 -- remove set from right side of str (default 　　SOUNDEX(str)　　　　 　　　　-- phonetic represen tation of str 　　SUBSTR(str, num2 [,n um1])　 -- substring of str, starting with num2, 　　　　　　　　　　　　　　　　 -- omitted) num1 characters (to end of str if num1 is 　　SUBSTRB(str, num2 [, bytes num1])　-- same as substr bu t num1, num2 expressed in 　　TRANSLATE(str, set1, set2)　-- replaces set1 in str with set2 　　　　　　　　　　　　　　　　 -- truncated if set2 is longer than set1, it will be 　　UPPER(str)　　　　　 　　　　-- str with all lett ers in uppercase 　　******************** *************** **************************** **************** 　　* String Functions, * Numeric Result :　　　　　　 　　　　　　　　　　　　　　　 　　******************************** *********************** ************************ 　　ASCII(str)　　　　　　　　　　　 　-- ASCII value of str 　　INSTR(str1, str2 [,num1 [,num2]] ) -- position of num2th occurrence of 　　　　　　　　　　　　　　　　　　 　 -- str2 in str1, starting at num1 　　　　　　　　　　　　　　　　　　 　 -- (num1, num2 default to 1) 　　INSTRB(str1, str2 [,num1 [num2]] ) -- same as instr, byte values for num1, num2 　　LENGTH(str)　　　　 　　　　　　　 -- number of characters in str 　　LENGTHB(str)　　　　　　　　　　 　-- number of bytes in str 　　NLSSORT(str [,nls_val])　　　　 　 -- nls_val byte value of str 　　******************************** *********************** ************************ 　　* SQL Conversion Functions　　　 * 　　　　　　　　　　　　　　　　　　　　　　　 　　******************************** *********************** ************************ 　　CHARTOROWID(str)　　　　　　　　 　 -- converts str to ROWID 　　CONVERT(str, chr_set2 [,chr_set1 ]) -- converts str to chr_set2 　　　　　　　　　　　　 character set 　　　　　　　　-- chr_set1 default is the datbase 　　HEXTORAW(str)　　　 　 -- converts hex string va lue to internal raw values 　　RAWTOHEX(raw_val)　　 -- convert s raw hex value to hex string value 　　ROWIDTOCHAR(rowid)　 　-- converts rowid to 18 ch aracter string format 　　TO_CHAR(expr [,fmt]) fmt 　-- converts expr(date or n umber) to format specified by 　　TO_DATE(str [,fmt]) 　 -- converts string to dat e 　　TO_MULTI_BYTE(str)　　-- convert s single byte string to multi byte string 　　TO_NUMBER(str [,fmt]) -- convert s str to a number formatted by fmt 　　TO_SINGLE_BYTE(str) 　 -- converts multi byte st ring to single byte string 　　******************************** *********************** ************************ 　　* SQL Date Formats　 * 　　　　　　　　　　　　　　 　　　　　　　　　　　　　　　 　　******************** *************** **************************** **************** 　　BC, B.C.　　　　BC indicator                              　　AD, A.D.　　　　AD indicator                              　　CC, SCC　　　　 Cent ury Code (SCC includes space or - sign) 　　YYYY, SYYYY　　 4 digit year (SY YYY includes space or - sign) 　　IYYY　　　　　　4 digit ISO year                          　　Y,YYY　　　　　 4 digit year with comma          　　YYY, YY, or Y　 last 3, 2, or 1 digit of year 　　YEAR, SYEAR　　 year spelled out (SYEAR includes space or - sign) 　　RR　　　　　　　last 2 digits of year in prior or next century 　　Q　　　　　　　 quarter or year, 1 to 4              　　MM　　　　　　　month - from 01 to 12                  　　MONTH　　　　　 month spelled out                      　　MON　　　　　　 month 3 letter abbreviation    　　RM　　　　　　　roman numeral for month              　　WW　　　　　　　week of year, 1 to 53                  　　IW　　　　　　　ISO week of year , 1 to 52 or 1 to 53 　　W　　　　　　　 week of month, 1 to 5 (week 1 begins 1st day of the month) 　　D　　　　　　　 day of week, 1 to 7                      　　DD　　　　　　　day of month, 1 to 31                  　　DDD　　　　　　 day of year, 1 to 366                　　DAY　　　　　　 day of week spel led out, nine characters right padded 　　DY　　　　　　　day abbreviation                            　　J　　　　　　　 # of days since Jan 1, 4712 BC 　　HH, HH12　　　　hour of day, 1 to 12              　　HH24　　　　　　hour of day, 0 to 23                  　　MI　　　　　　　minute of hour, 0 to 59              　　SS　　　　　　　second of minute, 0 to 59          　　SSSSS　　　　　 seco nds past midnight, 0 to 8639 9 　　AM, A.M.　　　　am indicator                              　　PM, P.M.　　　　pm indicator                              　　any puctuation　punc tuation between format items , as in 'DD/MM/YY' 　　any text　　　　text between format items    　　TH　　　　　　　conv erts 1 to '1st', 2 to '2nd', and so on 　　SP　　　　　　　converts 1 to 'o ne', 2 to 'two', and so on 　　SPTH　　　　　　converts 1 to 'F IRST', 2 to 'SECOND', and so on 　　FX　　　　　　　fill exact : uses exact pattern matching 　　FM　　　　　　　fill mode　: tog gles suppression of blanks in output