Oracle函数详解(经典)
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Oracle常用函数/过程说明主要介绍Oracle的系统函数、过程和包。
SQL常用函数：
数值函数：
ABS
Purpose 返回绝对值
Returns the absolute value of n.
Example
SELECT ABS(-15) "Absolute" FROM DUAL;
Absolute
----------
15
CEIL
Purpose 取最小整数
Returns smallest integer greater than or equal to n. Example
SELECT CEIL(15.7) "Ceiling" FROM DUAL;
Ceiling
----------
16
* MOD
Syntax
MOD(m,n)
Purpose 取余
Returns remainder of m divided by n. Returns m if n is 0. Example
SELECT MOD(11,4) "Modulus" FROM DUAL;
Modulus
----------
3
* ROUND
Syntax
ROUND(n[,m])
Purpose 取四舍五入信息
Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.
Example 1
SELECT ROUND(15.193,1) "Round" FROM DUAL;
Round
----------
15.2
Example 2
SELECT ROUND(15.193,-1) "Round" FROM DUAL;
Round
----------
20
* TRUNC
Purpose 取截取后的信息
Returns n truncated to m decimal places; if m is omitted, to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.
Examples
SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;
Truncate
----------
15.7
SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;
Truncate
----------
10
字符函数：
* CONCAT
Syntax
CONCAT(char1, char2)
Purpose 合并字符串，相当于“||”
Returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||). For information on this operator, see "Concatenation Operator".
Example
This example uses nesting to concatenate three character strings: SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job"
FROM emp
WHERE empno = 7900;
Job
-----------------
JAMES is a CLERK
* LOWER
Purpose 变为小写
Returns char, with all letters lowercase. The return value has the same datatype as the argument char (CHAR or V ARCHAR2). Example
SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase"
FROM DUAL;
Lowercase
--------------------
mr. scott mcmillan
* LPAD
Purpose 左填充
Returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.
The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.
Example