oracle 函数 英文,oracle函数大全-英文分类列表

*******************************************************************************

* 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

*******************************************************************************

* 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 [,expr2 [, 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, num [,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, num2 [,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, 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])