常用Oracle的系统函数、过程和包.doc

* 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 VARCHAR2). 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 port