oracle 中常用的函数
发布时间：2020-12-12 15:26:06 所属栏目：百科 来源：网络整理
导读：转自：http://blog.sina.com.cn/s/blog_578bede90100020t.html ******************************************************************** * SQL Group Functions (num can be a column or expression) * (null values are ignored,default between distinct a
|
转自：http://blog.sina.com.cn/s/blog_578bede90100020t.html ******************************************************************** * SQL Group Functions (num can be a column or expression) * (null values are ignored,default between distinct 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 deviation 给出一组行值的标准差. SUM([distinct or all] num) -- sum of values VARIANCE([distinct or all] num) -- variance of values返回一组行中所有VALUE的方差 VSIZE([distinct or all] num) -- 返回EXPR内部表示的字节数.********************************************************************* Miscellaneaous Functions : ********************************************************************* DECODE(expr,srch1,return1 [,srch2,return2...],default] -- if no search matches the expression then the default is returned,-- otherwise,the first search that matches will cause -- the corresponding return value to be returned DUMP(column_name [,fmt [,start_pos [,length]]]) -- returns an internal oracle format,used for getting info about a column -- format options : 8 = octal,10 = decimel,16 = hex,17 = characters -- return type codes : 1 = varchar2,2 = number,8 = long,12 = date,-- 23 = raw,24 = long raw,69 = rowid,96 = char,106 = mlslabel GREATEST(expr [,expr2 [,expr3...]] -- returns the largest value of all expressions LEAST(expr [,expr3...]] -- returns the smallest value of all expressions NVL(expr1,expr2 -- if expr1 is not null,it is returned,otherwise expr2 is returned SQLCODE -- returns sql error code of last error. Can not be used directly in query,-- value must be set to local variable first SQLERRM -- returns sql error message of last error. Can not be used directly in query,-- value must be set to local variable first UID -- returns the user id of the user you are logged on as -- useful in selecting information from low level sys tables USER -- returns the user name of the user you are logged on as USERENV('option') -- returns information about the user you are logged on as -- options : ENTRYID,SESSIONID,TERMINAL,LANGUAGE,LABEL,OSDBA -- (all options not available in all Oracle versions) VSIZE(expr) -- returns the number of bytes used by the expression -- useful in selecting information about table space requirements ********************************************************************* * SQL Date Functions (dt represents oracle date and time) * * (functions return an oracle date unless otherwise 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) -- returns fractional value of months between dt1,dt2 NEW_TIME(dt,tz1,tz2) -- dt = date in time zone 1,returns date in time zone 2 NEXT_DAY(dt,str) -- date of first (str) after dt (str = 'Monday',etc..) SYSDATE -- present system date ROUND(dt [,fmt] -- rounds dt as specified by format fmt TRUNC(dt [,fmt] -- truncates dt as specified by format fmt ********************************************************************* * Number Functions : * ********************************************************************* ABS(num) -- absolute value of num CEIL(num) -- smallest integer > or = num COS(num) -- cosine(num),num in radians COSH(num) -- hyperbolic cosine(num) EXP(num) -- e raised to the num power FLOOR(num) -- largest integer < or = num LN(num) -- natural logarithm of num LOG(num2,num1) -- logarithm base num2 of num1 MOD(num2,num1) -- remainder of num2 / num1 POWER(num2,num1) -- num2 raised to the num1 power ROUND(num1 [,num2] -- num1 rounded 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 num CONCAT(str1,str2) -- str1 concatenated with str2 (same as str1||str2) INITCAP(str) -- capitalize first letter of each word in str LOWER(str) -- str with all letters in lowercase LPAD(str1,num [,str2]) -- left pad str1 to length num with str2 (default spaces) LTRIM(str [,set]) -- remove set from left side of str (default spaces) NLS_INITCAP(str [,nls_val]) -- same as initcap for different languages NLS_LOWER(str [,nls_val]) -- same as lower for different languages REPLACE(str1,str2 [,str3]) -- replaces str2 with str3 in str1 -- deletes str2 from str1 if str3 is omitted RPAD(str1,str2]) -- right pad str1 to length num with str2 (default spaces) RTRIM(str [,set]) -- remove set from right side of str (default spaces) SOUNDEX(str) -- phonetic representation of str SUBSTR(str,num2 [,num1]) -- substring of str,starting with num2,-- num1 characters (to end of str if num1 is omitted) SUBSTRB(str,num1]) -- same as substr but num1,num2 expressed in bytes TRANSLATE(str,set1,set2) -- replaces set1 in str with set2 -- if set2 is longer than set1,it will be truncated UPPER(str) -- str with all letters in uppercase ********************************************************************* * String Functions,Numeric Result : * ********************************************************************* ASCII(str) -- ASCII value of str INSTR(str1,num1 [,num2]]) -- position of num2th occurrence of -- str2 in str1,starting at num1 -- (num1,num2 default to 1) INSTRB(str1,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 -- chr_set1 default is the datbase character set HEXTORAW(str) -- converts hex string value to internal raw values RAWTOHEX(raw_val) -- converts raw hex value to hex string value ROWIDTOCHAR(rowid) -- converts rowid to 18 character string format TO_CHAR(expr [,fmt]) -- converts expr(date or number) to format specified by fmt TO_DATE(str [,fmt]) -- converts string to date TO_MULTI_BYTE(str) -- converts single byte string to multi byte string TO_NUMBER(str [,fmt]) -- converts str to a number formatted by fmt TO_SINGLE_BYTE(str) -- converts multi byte string to single byte string ********************************************************************* * SQL Date Formats * ********************************************************************* BC,B.C. BC indicator AD,A.D. AD indicator CC,SCC Century Code (SCC includes space or - sign) YYYY,SYYYY 4 digit year (SYYYY 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 spelled 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 seconds past midnight,0 to 86399 AM,A.M. am indicator PM,P.M. pm indicator any puctuation punctuation between format items,as in 'DD/MM/YY' any text text between format items TH converts 1 to '1st',2 to '2nd',and so on SP converts 1 to 'one',2 to 'two',and so on SPTH converts 1 to 'FIRST',2 to 'SECOND',and so on FX fill exact : uses exact pattern matching FM fill mode : toggles suppression of blanks in output（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |