Oracle函数详解(经典)
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
SELECT LPAD('Page 1',15,'*.') "LPAD example"
FROM DUAL;
LPAD example
---------------
*.*.*.*.*Page 1
* LTRIM
Syntax
LTRIM(char [,set])
Purpose 左截取
Removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. Oracle begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.
Example
SELECT LTRIM('xyxXxyLAST WORD','xyXLA') "LTRIM example"
FROM DUAL;
LTRIM exampl
------------
* REPLACE
Syntax
REPLACE(char,search_string[,replacement_string])
Purpose 替换
Returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, char is returned. This function provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE allows you to substitute one string for another as well as to remove character strings.
Example
SELECT REPLACE('JACK and JUE','J','BL') "Changes"
FROM DUAL;
Changes
--------------
BLACK and BLUE
Syntax
RPAD(char1, n [,char2])
Purpose 右填充
Returns char1, right-padded to length n with char2, replicated as many times as necessary; 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
SELECT RPAD('MORRISON',12,'ab') "RPAD example"
FROM DUAL;
RPAD example
-----------------
MORRISONabab
* RTRIM
Syntax
RTRIM(char [,set]
Returns char, with all the rightmost characters that appear in set removed; set defaults to a single blank. RTRIM works similarly to LTRIM.
Example
SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."
FROM DUAL;
RTRIM e.g
-------------
BROWNINGyxX
* SUBSTR
Syntax
SUBSTR(char, m [,n])
Purpose 截取字符串
Returns a portion of char, beginning at character m, n characters long. If m is 0, it is treated as 1. If m is positive, Oracle counts from the beginning of char to find the first character. If m is negative, Oracle counts backwards from the end of char. If n is omitted, Oracle returns all characters to the end of char. If n is less than 1, a null is returned.
Floating-point numbers passed as arguments to substr are automatically converted to integers.
Example 1
SELECT SUBSTR('ABCDEFG',3,4) "Subs"
FROM DUAL;
Subs
----
CDEF
Example 2
SELECT SUBSTR('ABCDEFG',-5,4) "Subs"
FROM DUAL;
Subs
----
CDEF
* TRANSLATE
Syntax
TRANSLATE(char, from, to)
Purpose 在一定范围内转换字符
Returns char with all occurrences of each character in from replaced
by its corresponding character in to. Characters in char that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value. You cannot use an empty string for to to remove all characters in from from the return value. Oracle interprets the empty string as null, and if this function has a null argument, it returns null.
Example 1
The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012 . . . 9' are translated to '9':
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
FROM DUAL;
License
--------
9XXX999
Example 2
The following statement returns a license number with the characters removed and the digits remaining:
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789')
"Translate example"
FROM DUAL;
Translate example
-----------------
2229
* UPPER
Syntax
UPPER(char)
Purpose 大写
Returns char, with all letters uppercase. The return value has the same datatype as the argument char.
Example
SELECT UPPER('Large') "Uppercase"
FROM DUAL;
Upper
-----
LARGE
* ASCII
Syntax
ASCII(char)
Purpose 取字符的ASCII值
Returns the decimal representation in the database character set of the first character of char. If your database character set is 7-bit ASCII, this function returns an ASCII value. If your database character set is EBCDIC Code Page 500, this function returns an EBCDIC value. Note that there is no similar EBCDIC character function.
Example
SELECT ASCII('Q')
FROM DUAL;
ASCII('Q')
----------
81
* INSTR
Syntax
INSTR (char1,char2 [,n[,m]])
Purpose 从char1中第n个字符开始char2第m次出现的位置Searches char1 beginning with its nth character for the mth occurrence of char2 and returns the position of the character in char1 that is the first character of this occurrence. If n is negative, Oracle counts and searches backward from the end of char1. The value of m must be positive. The default values of both n and m are 1, meaning Oracle begins searching at the first character of char1 for the first occurrence of char2. The return value is relative to the beginning of char1, regardless of the value of n, and is expressed in characters. If the search is unsuccessful (if char2 does not appear m times after the nth character of char1) the return value is 0.
Example 1
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL;
Instring
----------
14
Example 2
SELECT INSTR('CORPORATE FLOOR','OR', -3, 2)
"Reversed Instring"
FROM DUAL;
Reversed Instring
-----------------
2
* LENGTH
Syntax
LENGTH(char)
Purpose 取字符串的长度
Returns the length of char in characters. If char has datatype CHAR, the length includes all trailing blanks. If char is null, this function returns null.
Example
SELECT LENGTH('CANDIDE') "Length in characters"
FROM DUAL;
Length in characters
--------------------
7
* ADD_MONTHS
Syntax
ADD_MONTHS(d,n)
Purpose 取N个月后的日期
Returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d.
Example
SELECT TO_CHAR(
ADD_MONTHS(hiredate,1),
'DD-MON-YYYY') "Next month"
FROM emp
WHERE ename = 'SMITH';
Next Month
-----------
17-JAN-1981
* LAST_DAY
Syntax
LAST_DAY(d)
Purpose 取D所在月份的最后一天
Returns the date of the last day of the month that contains d. You might use this function to determine how many days are left in the current month.
Example 1
SELECT SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;
SYSDATE Last Days Left
--------- --------- ----------
23-OCT-97 31-OCT-97 8
Example 2
SELECT TO_CHAR(
ADD_MONTHS(
LAST_DAY(hiredate),5),
'DD-MON-YYYY') "Five months"
FROM emp
WHERE ename = 'MARTIN';
Five months
-----------
28-FEB-1982
* MONTHS_BETWEEN
Syntax
MONTHS_BETWEEN(d1, d2)
Purpose 取两个日期间相隔的月数
Returns number of months between dates d1 and d2. If d1 is later than d2, result is positive; if earlier, negative. If d1 and d2 are either the same days of the month or both last days of months, the result is always an integer; otherwise Oracle calculates the fractional portion of the result based on a 31-day month and considers the difference in time components of d1 and d2.
Example
SELECT MONTHS_BETWEEN
(TO_DATE('02-02-1995','MM-DD-YYYY'),
TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"
FROM DUAL;
Months
----------
1.03225806
* NEXT_DAY
Syntax
* NEXT_DAY(d, char)
Purpose 取D下一个weekday的日期
Returns the date of the first weekday named by char that is later than the date d. The argument char must be a day of the week in your session's date language-either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version; any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument d.
Example
This example returns the date of the next Tuesday after March 15,
1992.
SELECT NEXT_DAY('15-MAR-92','TUESDAY') "NEXT DAY"
FROM DUAL;
NEXT DAY
---------
17-MAR-92
* SYSDATE
Syntax
SYSDATE
Purpose 取系统日期
Returns the current date and time. Requires no arguments. In distributed SQL statements, this function returns the date and time on your local database. You cannot use this function in the condition of a CHECK constraint.
Example
Oracle中分析函数用法小结
Oracle中分析函数用法小结 一.分析函数适用场景: ○1需要对同样的数据进行不同级别的聚合操作 ○2需要在表内将多条数据和同一条数据进行多次的比较 ○3需要在排序完的结果集上进行额外的过滤操作 二.分析函数语法: FUNCTION_NAME(
Oracle 常见函数(一)——数值函数
Oracle常见数值函数 ----***特别说明***： x 可以是纯的数值，也可以是数值型表达式/* ABS(x)返回x绝对值 eg. */ selectabs(100),abs(-100) from dual; /* sign(x)判断x的正负，正数返回1，负数返回-1,0返回0； eg. */ selectsign(100),sign(-100),sign(0) from dual;
/* round(x[,n])对x进行四舍五入，保留n位小数，其中n采用其整数部分； 没有n时默认四舍五入到整数位，n为负数时，四舍五入保留小数点左边n位(补零)， eg. */ selectround(5555.6666, 2.1), round(5555.6666, -2.6), round(5555.6666) from dual; /* trunc(x)对x进行直接截取，保留n位小数，其中n采用其整数部分； 没有n时默认截取到整数位，n为负数时，截取保留小数点左边n位(补零)， eg. */ selecttrunc(5555.66666,2.1), trunc(5555.66666,-2.6), trunc(5555.033333) from dual; /* ceil(x)对x进行向上取整，返回不小于x的最小整数(可以是整数x本身)。
eg. */ selectceil(3.1), ceil(2.8+1.3), ceil(0) from dual; /* floor(x)对x进行向下取整，返回不大于x的最大整数(可以是整数x本身)。eg. */ selectfloor(3.1), floor(2.8+1.3), floor(0) from dual; /* mod(x,y)求x除以y的余数，x，y为数字型表达式。 eg. */ selectmod(23,8),mod(24,8) from dual;
oracle中常用函数大全
oracle中常用函数大全 1、数值型常用函数 函数返回值样例显示 ceil(n) 大于或等于数值n的最小整数select ceil(10.6) from dual; 11 floor(n) 小于等于数值n的最大整数select ceil(10.6) from dual; 10 mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2 power(m,n) m的n次方select power(3,2) from dual; 9 round(n,m) 将n四舍五入,保留小数点后m位select round(1234.5678,2) from dual; 1234.57 sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1 sqrt(n) n的平方根select sqrt(25) from dual ; 5 2、常用字符函数 initcap(char) 把每个字符串的第一个字符换成大写select initicap('mr.ecop') from dual; Mr.Ecop lower(char) 整个字符串换成小写select lower('MR.ecop') from dual; mr.ecop replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott substr(char,m,n) 取出从m字符开始的n个字符的子串select substr('ABCDEF',2,2) from dual; CD length(char) 求字符串的长度select length('ACD') from dual; 3 || 并置运算符select 'ABCD'||'EFGH' from dual; ABCDEFGH 3、日期型函数 sysdate当前日期和时间select sysdate from dual;
Oracle 分析函数(Analytic Functions) 说明
Oracle 分析函数(Analytic Functions)说明一. Analytic Functions 说明 分析函数是oracle 8中引入的一个概念,为我们分析数据提供了一种简单高效的处理方式. 官方对分析函数的说明如下： Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The groupof rows is called a window and is defined bythe analytic_clause. For each row, a sliding window of rows is defined.The window determines the range of rows used to perform the calculations forthe current row. Window sizes can be based on either a physical number of rowsor a logical interval such as time. Analytic functions are the last set of operations performed in a query except for thefinal ORDER BY clause. All joins and all WHERE, GROUP BY,and HAVING clauses are completed before the analytic functions areprocessed. Therefore, analytic functions can appear only in the select listor ORDER BY clause. Analytic functions are commonly used to compute cumulative, moving, centered, andreporting aggregates. From：Analytic Functions https://www.360docs.net/doc/1c4269214.html,/cd/E11882_01/server.112/e26088/functions004.htm#S QLRF06174 分析函数是对一组查询结果进行运算，然后获得结果，从这个意义上，分析函数非常类似于聚合函数（Aggregate Function）。区别是在调用分析函数时，后面加上了开窗子句over()。 聚合函数是对一个查询结果中的每个分组进行运算，并且对每个分组产生一个运算结果。分析函数也是对一个查询结果中的每个分组进行运算，但每个分组对应的结果可以有多个。产生这个不同的原因是分析函数中有一个窗口的概念，一个窗口对应于一个分组中的若干行，分析函数每次对一个窗口进行运算。运算时窗口在查询结果或分组中从顶到底移动，对每一行数据生成一个窗口。 Oracle 聚合函数（Aggregate Functions）说明 https://www.360docs.net/doc/1c4269214.html,/tianlesoftware/article/details/7057249
ORACLE 内置函数大全
SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; A A ZERO SPACE --------- --------- --------- --------- 65 97 48 32 2.CHR 给出整数,返回对应的字符; SQL> select chr(54740) zhao,chr(65) chr65 from dual; ZH C -- - 赵 A 3.CONCAT 连接两个字符串; SQL> select concat('010-','')||'转23' 高乾竞电话from dual; 高乾竞电话 ---------------- 010-转23 4.INITCAP 返回字符串并将字符串的第一个字母变为大写; SQL> select initcap('smith') upp from dual; UPP ----- Smith 5.INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置; C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的开始位置,默认为1 J 出现的位置,默认为1 SQL> select instr('oracle traning','ra',1,2) instring from dual; INSTRING --------- 9 6.LENGTH 返回字符串的长度; SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst; NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL
ORACLE排序与分析函数
--已知：两种排名方式(分区和不分区):使用和不使用partition --两种计算方式(连续,不连续),对应函数：dense_rank,rank 语法： rank()over(order by排序字段顺序) rank()over(partition by分组字段order by排序字段顺序) 1.顺序：asc|desc名次与业务相关： 示例：找求优秀学员：成绩：降序迟到次数：升序 2.分区字段：根据什么字段进行分区。 问题：分区与分组有什么区别? ·分区只是将原始数据进行名次排列(记录数不变), ·分组是对原始数据进行聚合统计(记录数变少,每组返回一条),注意：聚合。rank()与dense_rank()：非连续排名与连续排名(都是简单排名) ·查询原始数据：学号,姓名,科目名,成绩 select*from t_score; S_ID S_NAME SUB_NAME SCORE 1张三语文80.00 2李四数学80.00 1张三数学0.00 2李四语文50.00 3张三丰语文10.00 3张三丰数学 3张三丰体育120.00 4杨过JAVA90.00 5mike c++80.00 3张三丰Oracle0.00 4杨过Oracle77.00 2李四Oracle77.00 ·查询各学生科目为Oracle排名(简单排名) select sc.s_id,sc.s_name,sub_name,sc.score,rank()over(order by score desc)名次from t_score sc where sub_name='Oracle'; S_ID S_NAME SUB_NAME SCORE名次 4杨过Oracle77.001 2李四Oracle77.001 3张三丰Oracle0.003 ·对比：rank()与dense_rank()：非连续排名与连续排名(都是简单排名) S_ID S_NAME SUB_NAME SCORE名次
Oracle分析函数sum over介绍
分析函数sum over() 介绍 报送单位：审核人： 类型：业务应用 关键字：分析函数 1、引言 运维中，常常需要通过SQL语句对单行数据进行查询，同时又需要对结果集进行汇总，通常的方法是通过两个SQL语句分别进行查询汇总，这样效率低下。 2、现象描述 本节介绍一种ORACLE提供的全新的函数sum over()，该类函数称为分析函数，这类函数功能强大，可以通过一个SQL语句对数据进行遍历的同时又进行汇总，而且一张表只进行一次扫描，极大地提高SQL的执行效率。 3、处理过程 语法： FUNCTION_NAME(
执行计划： 下图说明分析函数只对表进行一次扫描 4、举例说明 下面分别举例来说明分析函数的使用，原始数据如下表
示例1：查询单行数据同时对所有数据工资进行汇总求和 示例2：查询所有数据，同时对第各部门工资进行汇总，汇总范围取值为第一行所在部门至当前部门所有数据。
示例3：查询所有数据，并且每一行汇总值按ENAME排序后取第一行至当前行ENAME所在行。 示例4：查询所有数据，并且每一行汇总值按EMPNO排序后取第一行至当前行EMPNO所在行
示例5：查询所有数据，同时每行对各部门分别进行按EMPNO排序后从各组第一行至当前行汇总求和。 示例6：查询所有数据，同时每行对从当前上两行数据范围的工资进行汇总求和。 示例7：查询所有数据，同时每行对从当前向前一行数据向后两行数据范围的工资进行汇总求和。
Oracle常用函数及使用案例(珍藏版)
Oracle常用函数及使用案例（珍藏版） 一：sql函数： lower（char）:将字符串转化为小写的格式。 upper（char）:将字符串转化为大写的格式。 length（char）：返回字符串的长度。 substr（char，m，n）：取字符串的字串。 案例1.将所有员工的名字按小写的方式显示 select lower(ename),sal from emp; 案例2.将所有员工的名字按大写的方式显示。 select upper(ename),sal from emp; 案例3.显示正好为五个字符的的员工的姓名。 select * from emp where length(ename)=5; 案例4.显示所有员工姓名的前三个字符。 select substr(ename,1,3) from emp;//从名字的第一个字符开始取，向后取三个字符。 案例5.以首字母为大写的方式显示所有员工的姓名。 （1）首字母大写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母小写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp; 案例6.以首字母为小写的方式显示所有员工的姓名。（需要有较高的灵活度，细心分析和清晰思路） （1）首字母小写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母大写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1)) from emp; 案例7.函数（替换）：replace(char1,search_string,replace_string); 显示所有员工的姓名，用“我要替换A”替代所有“A”。 select replace(ename,'A','我是老鼠')from emp; 案例8.以首字母为小写的方式显示所有员工的姓名。 select replace(ename,substr(ename,1,1),lower(substr(ename,1,1)))from emp; 案例9.以首字母为大写的方式显示所有员工的姓名。 Select replace(ename,substr(ename,2,length(ename)-1),lower(substr(ename,2,length(ename) -1)))from emp; 二：数学函数：(在财务中用的比较多) ronud（sal）用于四舍五默认取整； ronud（sal，1）用于四舍五留一位小数。 trunc（sal）取整，忽略小数。截去小数部分。 trunc（sal，1）截取；小数点留一位，之后的右边的省去。 trunc（sal，-1）截取；只留整数，个位数取零。 floor（sal）向下最接近取整；比如1.1值为1.
Oracle函数大全
附录Ⅱ Oracle11g SQL函数 函数名 返回 类型 说明 字符串函数 ASCII(s) 数值 返回s首位字母的ASCII码 CHR(i) 字符 返回数值i的ASCII字符 CONCAT(s1,s2) 字符 将s2连接到字符串s1的后面 INITCAP(s) 字符 将每个单词首位字母大写其它字母小写 INSTR(s1,s2[,i[,j]]) 数值 返回s2在s1中第i位开始第j次出现的位置 INSTRB(s1,s2[,i[,j]]) 数值 与INSTR(s)函数相同，但按字节计算 LENGTH(s) 数值 返回s的长度。 LENGTHb(s) 数值 与LENGTH(s)相同，但按字节计算。 lower(s) 字符 返回s的小写字符 LPAD(s1,i[,s2]) 字符 在s1的左侧用s2字符串补足到总长度i LTRIM(s1,s2) 字符 循环去掉在s2中存在的s1左边字符 RPAD(s1,i[,s2]) 字符 在s1的右侧用s2字符串补足到总长度i RTRIM(s1,s2) 字符 循环去掉在s2中存在的s1右边字符 REPLACE(s1,s2[,s3]) 字符 用s3替换出现在s1中的s2 REVERSE(s) 字符 返回s倒排的字符串 SUBSTR(s,i[,j]) 字符 从s的第i位开始截得长度j的子字符串 SUBSTRB(s,i[,j]) 字符 与SUBSTR相同，但i,j按字节计算。 SOUNDEX(s) 返回与s发音相似的词 TRANSLATE(s1,s2,s3) 字符 将s1中与s2相同的字符以s3代替 TRIM(s) 字符 删除s的首部和尾部空格 UPPER(s) 字符 返回s的大写 正则表达式函数 REGEXP_LIKE() 布尔 功能与LIKE的功能相似 REGEXP_INSTR() 数值 功能与INSTR的功能相似 REGEXP_SUBSTR() 字符 功能与SUBSTR的功能相似 REGEXP_REPLACE() 字符 功能与REPLACE的功能相似 数字函数 ABS(i) 数值 返回i的绝对值 ACOS(i) 数值 反余玄函数，返回-1到1之间的数 ASIN(i) 数值 反正玄函数，返回-1到1之间的数 ATAN(i) 数值 反正切函数，返回i的反正切值
Oracle分析函数
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是 对于每个组返回多行，而聚合函数对于每个组只返回一行。 一、开窗函数 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。 例如： 1)over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数 2)over（partition by department_id）按照部门分区 3)over（order by salary range between 50 preceding and 150 following） 每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150 4)over（order by salary rows between 50 preceding and 150 following） 每行对应的数据窗口是之前50行，之后150行 5)over（order by salary rows between unbounded preceding and unbounded following） 每行对应的数据窗口是从第一行到最后一行，等效： 6) over（order by salary range between unbounded preceding and unbounded following） 其中： 第一行是unbounded preceding 当前行是current row 最后一行是unbounded following 二、分析函数的概念 分析函数是在整个SQL查询结束后(SQL语句中的ORDER BY的执行比较特殊)再进行的操作, 也就是说 SQL语句中的ORDER BY也会影响分析函数的执行结果。 分析函数中包含三个分析子句:分组(Partition By), 排序(Order By), 窗口(Window) 当省略窗口子句时: 1) 如果存在Order By则默认的窗口是unbounded preceding and current row 2) 如果同时省略Order By则默认的窗口是unbounded preceding and unbounded following 如果省略分组,则把全部记录当成一个组 a) 如果SQL语句中的Order By满足分析函数分析时要求的排序,那么SQL语句中的排序将先执行,分析 函数分析时就不必再排序 b) 如果SQL语句中的Order By不满足分析函数分析时要求的排序，那么SQL语句中的排序将最后在分 析函数分析结束后执行排序 三、分析函数 1）AVG
oracle函数列表
PL/SQL单行函数和组函数详解 函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类: 单行函数; 组函数 本文将讨论如何利用单行函数以及使用规则。SQL中的单行函数 SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。 SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename) 单行函数也可以在其他语句中使用，如update的SET子句，INSERT的V ALUES子句，DELET的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。 NULL和单行函数 在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCA T,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数:NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。 下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿 column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2 不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子: update empset salary=(salary+bonus)*1.1 这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。 所以正确的语句是: update empset salary=(salary+nvl(bonus,0)*1.1 单行字符串函数 单行字符串函数用于操作字符串数据，他们大多数有一个或多个参数，其中绝大多数返回字符串 ASCII()
Oracle自定义聚合函数-分析函数
自定义聚合函数，分析函数 --from GTA Aaron 最近做一数据项目要用到连乘的功能，而Oracle数据库里没有这样的预定义聚合函数，当然利用数据库已有的函数进行数学运算也可以达到这个功能，如： selectexp(sum(ln(field_name))) from table_name; 不过今天的重点不是讲这个数学公式，而是如何自己创建聚合函数，实现自己想要的功能。很幸运Oracle 允许用户自定义聚合函数，提供了相关接口，LZ研究了下，留贴共享。 首先介绍聚合函数接口： 用户可以通过实现Oracle的Extensibility Framework中的ODCIAggregate interface 来创建自定义聚合函数，而且自定义的聚合函数跟内建的聚合函数用法上没有差别。 通过实现ODCIAggregaterountines来创建自定义的聚合函数。可以通过定义一个对象类型（Object Type），然后在这个类型内部实现ODCIAggregate 接口函数(routines)，可以用任何一种Oracle支持的语言来实现这些接口函数，比如C/C++, JAVA, PL/SQL等。在这个Object Type定义之后，相应的接口函数也都在该Object Type Body内部实现之后，就可以通过CREATE FUNCTION语句来创建自定义的聚合函数了。 每个自定义的聚合函数需要实现4个ODCIAggregate 接口函数，这些函数定义了任何一个聚合函数内部需要实现的操作： 1. 自定义聚合函数初始化操作,从这儿开始一个聚合函数。初始化的聚合环境(aggregation context)会以对象实例(object type instance)传回给oracle. static function ODCIAggregateInitialize(varIN OUTagg_type ) return number 2. 自定义聚合函数,最主要的步骤,这个函数定义我们的聚合函数具体做什么操作，self 为 当前聚合函数的指针,用来与前面的计算结果进行关联。这个函数用来遍历需要处理的
oracle数据库之常用的函数练习
/*此文章可以作为sql脚本直接运行，某些函数限于oracle数据库！前面建表和数据插入可以不看，直接看后面红色部分的代码，前面的表只是提供一些数据供大家练习，不用自己再去建表了，真正的内容在后面*/ --人员表 create table person( personID number , pname varchar2(20) constraint NN_pname not null, psex char(2) , pbirth date , constraint PK_personID primary key (personID) , constraint CK_psex check (psex='男' or psex='女') ); --增加数据 create sequence seq_personID ; insert into person values (seq_personID.Nextval,'david','男',to_date('1990-09-10','yyyy-mm- dd')); insert into person values (seq_personID.Nextval,'ggyy','男',to_date('1991-10-10','yyyy-mm-dd')); insert into person values (seq_personID.Nextval,'朱 刀','男',to_date('1970-10-10','yy-mm-dd'));
insert into person values (seq_personID.Nextval,'泥 巴','女',to_date('1991-10-10','yy-mm-dd')); insert into person values (seq_personID.Nextval,'憨 坨','女',to_date('1991-10-10','yy-mm-dd')); insert into person values (seq_personID.Nextval,'李静 芳','女',to_date('1990-09-10','yyyy-mm-dd')); insert into person (personID,pname,psex) values (seq_personID.Nextval,'胖子','男'); insert into person (personID,pname,psex) values (seq_personID.Nextval,'小成成','男'); insert into person (personID,pname,psex) values (seq_personID.Nextval,'乐姐','女'); insert into person (personID,pname,psex) values (seq_personID.Nextval,'靓崽波','男'); --登录账号表 create table loginzh( userID number , personID number , zhanghao varchar2(20) , passwd varchar2(20) , logintime date , loginIP varchar2(15) , constraint PK_userID primary key (userID) , constraint FK_personID foreign key (personID) references person (personID) ); --为这个表加点数据 create sequence seq_userID ; insert into loginzh values (seq_userID.Nextval,1,'111111','123456',sysdate,'127.0.0.1'); --sysdate为系统当前时间
Oracle常用函数
数值函数
日期函数 SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual; SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual; 其他函数
5.高级查询（多表联合查询） 例子： SELECT job 职务 , SUM(sal) 工资总和 FROM emp WHERE job != 'PRESIDENT' GROUP BY job HAVING SUM(sal)>4500 ORDER BY SUM(sal); 分析函数 以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始 ROW_NUMBER返回连续的排位，不论值是否相等 RANK具有相等值的行排位相同，序数随后跳跃 DENSE_RANK 具有相等值的行排位相同，序号是连续的 例子： SELECT deptno, ename, sal, comm, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) RANK FROM emp; SELECT ename, job, deptno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS SAL_RANK FROM SCOTT.EMP; SELECT d.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS DENRANK FROM emp e, dept d WHERE e.deptno = d.deptno;
oracle SQL里常用的时间函数
oracle SQL里常用的时间函数,经典推荐 相信很多人都有过统计某些数据的经历，比如，要统计财务的情况，可能要按每年，每季度，每月，甚至每个星期来分别统计。那在oracle 中应该怎么来写sql语句呢，这个时候Oracle的日期函数会给我们很多帮助。 常用日期型函数 1。Sysdate 当前日期和时间 SQL> Select sysdate from dual; SYSDATE ---------- 21-6月-05 2。Last_day 本月最后一天 SQL> Select last_day(sysdate) from dual; LAST_DAY(S ---------- 30-6月-05 3。Add_months(d,n) 当前日期d后推n个月
用于从一个日期值增加或减少一些月份 date_value:=add_months(date_value,number_of_months) SQL> Select add_months(sysdate,2) from dual; ADD_MONTHS ---------- 21-8月-05 4。Months_between(f,s) 日期f和s间相差月数 SQL> select months_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))fro m dual; MONTHS_BETWEEN(SYSDATE,TO_DATE('2005-11-12','YYYY-MM-D D')) ---------------------------------------------------------- -4.6966741 5。NEXT_DAY(d, day_of_week) 返回由"day_of_week"命名的，在变量"d"指定的日期之后的第一个工作日的日期。参数"day_of_week"必须为该星期中的某一天。
oracle高级分析函数使用实例
oracle高级分析函数使用实例 2014年11月26日10:26:55 ?标签： ?oracle ?1744 ORACLE的分析函数，发现大家写SQL的时候有些功能写的比较麻烦或者不知道复杂的功能怎么通过SQL实现，ORACLE自带的分析函数有很多相应的功能： 它是Oracle分析函数专门针对类似于"经营总额"、"找出一组中的百分之多少" 或"计算排名前几位"等问题设计的。 分析函数运行效率高，使用方便。 分析函数是基于一组行来计算的。这不同于聚集函数且广泛应用于OLAP环境中。 Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是 对于每个组返回多行，而聚合函数对于每个组只返回一行。 语法：
组。这里的"分区partition"和"组group" 都是同义词。 5 排序子句order-by-clause指定数据是如何存在分区内的。其格式为：order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last] 其中： (1)asc|desc:指定了排列顺序。 (2)nulls first|nulls last:指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。 6窗口子句windowing-clause 给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。在一组基于任意变化或固定的窗口中， 可用该子句让分析函数计算出它的值。 格式： {rows|range} {between {unbounded preceding|current row |
oracle常用函数习题及答案
--1、选择部门30中的雇员 select * from emp where deptno=30; --2、列出所有办事员的姓名、编号和部门 select ename,empno,dname from emp e inner join dept d on e.deptno = d.deptno where job=upper('clerk?); --3、找出佣金高于薪金的雇员 select * from emp where comm>sal; --4、找出佣金高于薪金60%的雇员 select * from emp where comm>sal*0.6 --5、找出部门10中所有经理和部门20中的所有办事员的详细资料 select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')); --6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料 select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')) or (job<>upper(…manager?) and job<>upper(…clerk?) and sal>=2000) --7、找出收取佣金的雇员的不同工作 select distinct job from emp where comm>0; --8、找出不收取佣金或收取的佣金低于100的雇员 select * from emp where nvl(comm,0)<100; --9、找出各月最后一天受雇的所有雇员 select * from emp where hiredate= last_day(hiredate); --10、找出早于25年之前受雇的雇员