oracle函数
函数的分类：
1.单行函数：对每一行输入值进行计算，得到相应的计算结果，返回给用户。比如：length
2.多行函数：对多行输入值进行计算，得到多行对应的单个结果。比如max,min
Number Functions:
ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET
Character Functions Returning Character Values:
CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NCHRSingle-Row Functions
Functions 5-5
NLS_INITCAP
NLS_LOWER
NLS_UPPER
NLSSORT
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER
Character Functions Returning Number Value:
ASCII
INSTR
LENGTH
REGEXP_COUNT
REGEXP_INSTR
NLS Character Functions:
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
Datetime Functions:
ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ORA_DST_AFFECTED
ORA_DST_CONVERT
ORA_DST_ERROR
ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_DSINTERVAL
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET
General Comparison Functions:
GREATEST
LEAST
Conversion Functions:
ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEXSingle-Row Functions
Functions 5-7
ROWIDTOCHAR
ROWIDTONCHAR
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_BLOB
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR (character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR
Large Object Functions
Large Object Functions
The large object functions operate on LOBs. The large object functions are:
BFILENAME
EMPTY_BLOB, EMPTY_CLOB
Collection Functions
The collection functions operate on nested tables and varrays. The SQL collection
functions are:
CARDINALITY
COLLECT
POWERMULTISET
POWERMULTISET_BY_CARDINALITY
SET
Hierarchical Functions
Hierarchical functions applies hierarchical path information to a result set. The
hierarchical function is:
SYS_CONNECT_BY_PATH
Data Mining Functions
The data mining functions operate on models that have been built using the DBMS_
DATA_MINING package or the Oracle Data Mining Java API. The SQL data mining
functions are:Single-Row Functions
5-8 Oracle Database SQL Language Reference
CLUSTER_ID
CLUSTER_PROBABILITY
CLUSTER_SET
FEATURE_ID
FEATURE_SET
FEATURE_VALUE
PREDICTION
PREDICTION_BOUNDS
PREDICTION_COST
PREDICTION_DETAILS
PREDICTION_PROBABILITY
PREDICTION_SET
XML Functions
The XML fu
nctions operate on or return XML documents or fragments. These
functions use arguments that are not defined as part of the ANSI/ISO/IEC SQL
Standard but are defined as part of the World Wide Web Consortium (W3C) standards.
The processing and operations that the functions perform are defined by the relevant
W3C standards. The table below provides a link to the appropriate section of the W3C
standard for the rules and guidelines that apply to each of these XML-related
arguments. A SQL statement that uses one of these XML functions, where any of the
arguments does not conform to the relevant W3C syntax, will result in an error. Of
special note is the fact that not every character that is allowed in the value of a
database column is considered legal in XML.
For more information about selecting and querying XML data using these functions,
including information on formatting output, refer to Oracle XML DB Developer's Guide.
The SQL XML functions are:
APPENDCHILDXML
DELETEXML
DEPTH
EXISTSNODE
EXTRACT (XML)
EXTRACTVALUE
INSERTCHILDXML
INSERTCHILDXMLAFTER
INSERTCHILDXMLBEFORE
INSERTXMLAFTER
INSERTXMLBEFORE
PATH
SYS_DBURIGEN
See Also: Oracle Data Mining Application Developer's Guide for
information on SQL data mining functions
Syntax Element W3C Standard URL
value_expr https://www.doczj.com/doc/3c7233418.html,/TR/2006/REC-xml-20060816
Xpath_string https://www.doczj.com/doc/3c7233418.html,/TR/1999/REC-xpath-19991116
XQuery_string https://www.doczj.com/doc/3c7233418.html,/TR/2007/REC-xquery-semantics-20070123/
namespace_string https://www.doczj.com/doc/3c7233418.html,/TR/2006/REC-xml-names-20060816/
identifier https://www.doczj.com/doc/3c7233418.html,/TR/2006/REC-xml-20060816/#NT-NmtokenSingle-Row Functions
Functions 5-9
SYS_XMLAGG
SYS_XMLGEN
UPDATEXML
XMLAGG
XMLCAST
XMLCDATA
XMLCOLATTVAL
XMLCOMMENT
XMLCONCAT
XMLDIFF
XMLELEMENT
XMLEXISTS
XMLFOREST
XMLISVALID
XMLPARSE
XMLPATCH
XMLPI
XMLQUERY
XMLROOT
XMLSEQUENCE
XMLSERIALIZE
XMLTABLE
XMLTRANSFORM
Encoding and Decoding Functions
The encoding and decoding functions let you inspect and decode data in the database.
The encoding and decoding functions are:
DECODE
DUMP
ORA_HASH
VSIZE
NULL-Related Functions
The NULL-related functions facilitate null handling. The NULL-related functions are:
COALESCE
LNNVL
NANVL
NULLIF
NVL
NVL2
Environment and Identifier Functions
The environment and identifier functions provide information about the instance and
session. The environment and identifier functions are:
SYS_CONTEXT
SYS_GUID
SYS_TYPEID
UID
USER
USERENVAggregate Functions
5-10 Oracle Database SQL Language Reference
Aggregate Functions
Aggregate functions return a single result row based on groups of rows, rather than on
single rows. Aggregate functions can appear in select lists and in ORDER BY and
HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT
statement, where Oracle Database divides the rows of a queried table or view into
groups. In a query containing a GROUP BY clause, the elements of the select list
can be
aggregate functions, GROUP BY expressions, constants, or expressions involving one of
these. Oracle applies the aggregate functions to each group of rows and returns a
single result row for each group.
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select
list to all the rows in the queried table or view. You use aggregate functions in the
HAVING clause to eliminate groups from the output based on the results of the
aggregate functions, rather than on the values of the individual rows of the queried
table or view.
Many (but not all) aggregate functions that take a single argument accept these
clauses:
■ DISTINCT and UNIQUE, which are synonymous, cause an aggregate function to
consider only distinct values of the argument expression. The syntax diagrams for
aggregate functions in this chapter use the keyword DISTINCT for simplicity.
■ ALL causes an aggregate function to consider all values, including all duplicates.
For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you
specify neither, then the default is ALL.
Some aggregate functions allow the windowing_clause, which is part of the syntax
of analytic functions. Refer to windowing_clause on page 5-15 for information about this
clause. In the listing of aggregate functions at the end of this section, the functions that
allow the windowing_clause are followed by an asterisk (*)
All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls.
You can use the NVL function in the argument to an aggregate function to substitute a
value for a null. COUNT and REGR_COUNT never return null, but return either a
number or zero. For all the remaining aggregate functions, if the data set contains no
rows, or contains only rows with nulls as arguments to the aggregate function, then
the function returns null.
The aggregate functions MIN, MAX, SUM, AVG, COUNT, VARIANCE, and STDDEV, when
followed by the KEEP keyword, can be used in conjunction with the FIRST or LAST
function to operate on a set of values from a set of rows that rank as the FIRST or
LAST with respect to a given sorting specification. Refer to FIRST on page 5-101 for
more information.
You can nest aggregate functions. For example, the following example calculates the
average of the maximum salaries of all the departments in the sample schema hr:
SELECT AVG(MAX(salary))
FROM employees
GROUP BY department_id;
AVG(MAX(SALARY))
----------------
10926.3333
See Also: "Using the GROUP BY Clause: Examples" on page 19-42
and the "HAVING Clause" on page 19-28 for more information on the
GROUP BY clause and HAVING clauses in queries and subqueriesAnalytic Functions
Functions 5-11
This calculation evaluates the inner aggregate (MAX(salary)) for each group defined
by the GROUP BY clause (department_id), and aggregates the results again.
In the list of aggregate functions that follows
, functions followed by an asterisk (*)
allow the windowing_clause.
AVG
COLLECT
CORR
CORR_*
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
LISTAGG
MAX
MEDIAN
MIN
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
REGR_ (Linear Regression) Functions
STATS_BINOMIAL_TEST
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST_*
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
SYS_XMLAGG
VAR_ POP
VAR_ S AMP
VARI ANCE
XMLAGG
单行函数包括：字符函数、系统函数、数字函数、日期函数、转换函数。
字符函数：
1.replace(char1,searchStr,replaceStr)
替换函数，将char1中的searchStr字符替换成replaceStr。例如：select replace('abcda','a','A') from dual;返回结果‘AbcdA'。
2.instr(C1,C2,I,J)
在C1字符串中搜索C2，返回发现指定字符的位置；I表示搜索开始位置，默认为1，J表示出现的次数，默认为1。例如：select instr('a0bcde0f000','0',3,2) from dual;返回结果：9。
3.ASCII(char)
返回与指定字符对应的ASCII码。列如：select ascii('a'),ascii('A') from dual;返回结果：97 65.
4.CHR
给出整数，返回对应的字符。例如：select chr(65),chr(97),chr(54740) from dual;返回结果：a A 赵5.CONCAT
连接两个字符串。例如：select concat('020-','887694933') from dual;返回结果：'020-887694933'.
6.INITCAP
返回字符串并将字符串的第一个字母变为大写,其他字母小写。例如：select initcap('smith') from dual;返回结果：'Smith'.
7.UPPER
将所有字母都转为大写。例如：select upper('abc') from dual;返回结果：'ABC'.
8.LOWER
将所有字母都转为小写。例如：select lower('ABC') from dual;返回结果：'abc'.
9.LENGTH
返回字符串的长度。例如：select length('你好abc') from dual;返回结果：5.
10.SUBSTR(string,start,count);
截取字符串string,从start开始，截取count个。
例子：select substr('abcdef',2,3) from dual;返回结果：bcd.
综合运用：
将ename第一个字母小写，其他字母大写。
select concat(lower(ename,1,1),upper(ename,2,length(ename)-1))) from emp;
11.粘贴字符LPAD和RPAD
LPAD(char,len,c1) 在列左边粘贴字符
RPAD(char,len,c1) 在列右边粘贴字符
char表示要处理的字符串
len表示填充后的长度
c1表示填充的字符
例如：select lpad('abc',5,'*') from dual;返回结果：'**abc'.
12.LTRIM和RTRIM
ltrim(string,'char') 删除string中左边的字符char.
rtrim(string,'char')删除string中右边的字符char.
例如：select ltrim('ffabcdefeff','ff') from dual;返回结果：'abcdefeff'.
13.SOUNDEX(char)
14.TRIM(C1 FROM string)
将string中的前后‘C1'字符去掉。
例如：select trim('a' from 'aabcdefa') from dual;返回结果：'bcdef'.
15.ABS(number)取绝对值
例如：select abs(-100) from dual;返回结果：100.
16.CEI
L
向上取整，返回大于或等于给出数字的最大整数
例如：select ceil(56.345562) from dual;返回结果：57.
select ceil(-56.345562) from dual;返回结果：-56.
17.FLOOR
向下取整，返回小于或等于给出数字的最小整数
例如：select floor(56.345562) from dual;返回结果：56.
select floor(-56.345562) from dual;返回结果：-57.
18.EXP
返回一个数字e的n次方根
例如：select exp(2),exp(1) from dual;返回结果：7.3890561 2.71828183
19.LN
返回一个以10为底数字的对数值
20.LOG(n1,n2)
返回一个以n1为底，n2的对数值
21.MOD(n1,n2) 取模
返回一个n1除以n2的余数
select mod(5,2) from dual;返回结果：1
22.ROUND(N1，N2)四舍五入计算
select round(-45.6) from dual;返回结果：-46；
23.trunc函数，截取数字，截取日期
trunc(date,[format]):为指定元素而截去的日期值。
select sysdate, --返回当前日期时间 2012/8/29 10:20:33
trunc(sysdate), --返回当前日期，截去“时分秒” 2012/8/29
trunc(sysdate, 'yyyy'), --返回当年第一天 2012/1/1
trunc(sysdate, 'yy'), --返回当年第一天 2012/1/1
trunc(sysdate, 'mm'), --返回当月第一天 2012/8/1
trunc(sysdate, 'd'), --返回本周第一天日期，即返回本周星期天日期 2012/8/26(start day of the week)
trunc(sysdate, 'day'), --返回本周第一天日期，即返回本周星期天日期 2012/8/26
trunc(sysdate, 'ww'),
trunc(sysdate, 'w'),
trunc(sysdate, 'dd'), --返回当天 2012/8/29
trunc(sysdate, 'hh'), --返回当前时间，精确到小时 2012/8/29 10:00:00
trunc(sysdate, 'mi') --返回当前时间，精确到分钟 2012/8/29 10:20:00
from dual;
TRUNC（number[,decimals]:按照指定精度，截取数字，与Round区别在于它不会进行四舍五入。
select trunc(10.98145),--返回整数部分 10
trunc(10.98145, 0),--返回整数部分，与上面相同 10
trunc(10.98145, 1),--返回保留小数点右边1位 10.9
trunc(10.98145, 3),--返回保留小数点右边3位 10.981
trunc(10.98145, -1),--返回保留小数点左边1位，负数代表从小数点左边截取，截取部分以0填充 10
trunc(10.98145, -2)--返回保留小数点左边2位 0
from dual;