oracle 函数

字符串函数
LENGTH()    字符长度
LENTTHB()    字节长度；一个汉字内存中占用 2字节
LTRIM、RTRIM、TRIM

截串
SUBSTR(表达式，位置，长度)
Oracle 无左右取串函数，但可以使用变通方式完成。
左取串：    SUBSTR('abcdefg', 1, 3)
右取串：    SUBSTR('abcedfg', LENGTH('abcdefg')-3+1, 3)

  字符函数接受字符输入，并返回字符或数值。
INITCAP(char)        首字母大写
LOWER(char)        转换为小写
UPPER(char)        转换为大写
LTRIM(char, set)    左裁切
RTRIM(char, set)    右裁切
TRANSLATE(char, from, to)        按字母翻译
REPLACE(char, search_str, replace_str)    字符串替换
INSTR(char, substr[,pos])        查找子串位置
SUBSTR(char, pos, len)            取子字符串
CONCAT(char1, char2)            连接字符串

CHR(ascii)        根据 ASCII 码返回对应字符串
LPAD / RPAD        左 / 右 填充
    LPAD ('function', 15 , '=') 返回    '=======function'
TRAM            开头或结尾(或 开头和结尾)裁剪特定的字符，默认裁剪空格。
    TRIM ([LEADING | TRAILING] trim_char)
LENGTH(char)        返回字符串长度
DECODE            逐个值进行字符串替换
    DECODE (expr, search1, result1, search2, result2, [ ,default])
    DECODE (ostalus, 'p', '准备处理', 'c', '已完成')


时间函数
sysdate、current_day
设置时间格式：    ALERT SESSION SET NLS_DATE_FORMAT = 'dd-mon-yyyy HH:mi:ss'
求时间：        NEXT_DAY(sysdate, '星期三')
ADD_MONTHS(d, n)    返回 指定日期加上月数后的 日期值
MONTHS_BETWEEN(d1, d2)    返回 两个日期间的 月数
LAST_DAY(d)        返回 指定日期当前的最后一天的 日期值
RONUD(d,[fmt])        返回 指定日期四舍五入格式(YEAR、MONTH、DAY)后的 日期值
NEXT_DAY(d,day)        返回 指定日期下一个星期几的 日期值
TRUNC(d,[fnt])        返回 指定日期截断为格式后的 日期值
EXTRACT(fmt FROM d)    返回 指定日期提取的格式的 值 


转换函数
TO_CHAR(sysdate, 'yyyy-mm-dd hh24:mi:ss')
TO_DATE('12-3月-04')
TO_NUMBER('333')        必须是能转换
TO_TIMESTAMP('2007-10-10 00:00:00.0', 'yyyy-mm-dd hh24:mi:ssxff')    转换为时间戳格式

聚合函数
count(*)    ：查询表行数
count(column)    :查询列行数，会忽略空值，注意
ps.聚合函数不能做为 where 里查询条件出现（因为聚合是对所有查询结果的运算？）


其他函数
USER：当前用户
SUM(DECODE(SEX, '男', 1, 0))    筛选出行被为男的记录 并加1
SUM(DECODE(SEX, '女', 1, 0))    筛选出行被为女的记录 并加1
NVL (exp, exp2)        如果 exp 为空返回 exp2；如果非空返回 exp
NVL2 (exp, exp2, exp3)    如果 exp 为空返回 exp3；如果非空返回 exp2
NULLIF (exp1, exp2)    比较两表达式，相等返回空值，不等则返回 exp1

 分析函数
    分析函数根据一组行来计算聚合值。这些函数通常用来完成对聚集的累积排名、移动平均数和报表计算。
    分析函数与聚合函数不同的是他们为每组记录返回多个行。

ROW_NUMBER () OVER ([PARTITION BY colum] ORDER BY colum)
    为有序组中的每一行返回一个唯一的排序值，序号由 ORDER BY 子句指定，从 1 开始，即使具有相等的值，排位也不同。
    PARTITION BY colum 按列值进行区分，各分组内在进行排序。

RANK () OVER ([PARTITION BY colum] ORDER BY colum)
    计算一个值在一个组中的地位，由 1 开头，具有相等值得行排位相同，序数随后跳跃相应的数值。

DENSE_RANK () OVER ([PARTITION BY colum] ORDER BY colum)

 

abs:求绝对值函数,如:abs(−5) 5
sqrt:求平方根函数,如:sqrt(2) 1.41421356
power:求幂函数,如:power(2,3) 8
cos:求余弦三角函数,如:cos(3.14159) −1
mod:求除法余数,如:mod(1600, 300) 100
ceil:求大于等于某数的最小整数,如:ceil(2.35) 3
floor:求小于等于某数的最大整数,如:floor(2.35) 2
round:按指定精度对十进制数四舍五入,如:round(45.923, 1),结果,45.9
round(45.923, 0),结果,46
round(45.923, −1),结果,50
trunc:按指定精度截断十进制数,如:trunc(45.923, 1),结果,45.9
trunc(45.923),结果,45
trunc(45.923, −1),结果, 40

使用数值型函数练习。
使用求绝对值函数abs。

SELECT abs(?5) FROM dual;

SELECT abs(?5) FROM dual;

执行结果：
ABS(?5)
---------------
5
说明：求?5的绝对值，结果为5。

字符型函数

ascii:获得字符的ASCII码,如:Ascii('A') 65
chr:返回与ASCII码相应的字符,如:Chr(65) A
lower:将字符串转换成小写,如:lower ('SQL Course') sql course
upper:将字符串转换成大写,如:upper('SQL Course') SQL COURSE
initcap:将字符串转换成每个单词以大写开头,如:initcap('SQL course') Sql Course
concat:连接两个字符串 concat('SQL', ' Course') SQL Course
substr:给出起始位置和长度，返回子字符串,如:substr('String',1,3) Str
length:求字符串的长度 length('Wellcom') 7
instr:给出起始位置和出现的次数，求子字符串在字符串中出现的位置,如:instr('String', 'r',1,1) 3
lpad:用字符填充字符串左侧到指定长度,如:lpad('Hi',10,'-') --------Hi
rpad:用字符填充字符串右侧到指定长度,如:rpad('Hi',10,'-') Hi--------
trim:在一个字符串中去除另一个字符串,如:trim('S' FROM 'SSMITH') MITH
replace:用一个字符串替换另一个字符串中的子字符串,如:replace('ABC', 'B', 'D') ADC

如果不知道表的字段内容是大写还是小写，可以转换后比较。
输入并执行查询：

SELECT empno, ename, deptno FROM emp
WHERE lower(ename) ='blake';

SELECT  empno, ename, deptno FROM emp   WHERE  lower(ename) ='blake';

结果为：

EMPNO ENAME DEPTNO
--------------- ---------- ------------------------
7698 BLAKE 30

EMPNO ENAME          DEPTNO   --------------- ---------- ------------------------         7698 BLAKE              30

说明：该查询将表中的雇员名转换成小写，与小写的blake进行比较。

日期函数

months_between:返回两个日期间的
月份,如:months_between ('04-11月-05','11-1月-01'),结果,57.7741935
add_months:返回把月份数加到日期上的新日期,如:add_months('06-2月-03',1),结果,06-3月-03
add_months('06-2月-03',-1),结果,06-1月-03
next_day:返回指定日期后的星期对应的新日期,如:next_day('06-2月-03','星期一'),结果,10-2月-03
last_day:返回指定日期所在的月的最后一天,如:last_day('06-2月-03'),结果,28-2月-03
round:按指定格式对日期进行四舍五入,如:round(to_date('13-2月-03'),'YEAR'),结果,01-1月-03
round(to_date('13-2月-03'),'MONTH'),结果,01-2月-03
round(to_date('13-2月-03'),'DAY'),结果,16-2月-03

(按周四舍五入)
trunc:对日期按指定方式进行截断,如:trunc(to_date('06-2月-03'),'YEAR'),结果,01-1月-03
trunc(to_date('06-2月-03'),'MONTH'),结果,01-2月-03
trunc(to_date('06-2月-03'),'DAY'),结果,02-2月-03

(按周截断)

返回2003年2月的最后一天。
输入并执行查询：

SELECT last_day('08-2月-03') FROM dual;

SELECT last_day('08-2月-03') FROM dual;

返回结果为：

LAST_DAY('
----------------
28-2月-03

LAST_DAY('   ----------------   28-2月-03

说明：该函数给定参数为某月份的任意一天，返回时间为该月份的最后一天。本例中，参数为03年2月8号，返回日期为03年2月28日，是该月的最后一天。

假定当前的系统日期是2003年2月6日，求再过1000天的日期。
输入并执行查询：

SELECT sysdate+1000 AS "NEW DATE" FROM dual;

SELECT sysdate+1000 AS "NEW DATE" FROM dual;

返回结果为：

NEW DATE
----------------
04-11月-05

NEW DATE   ----------------   04-11月-05

说明：该查询使用到了日期的加法运算，求经过一定天数后的新日期。

假定当前的系统日期是2003年2月6日，显示部门10雇员的雇佣天数。
输入并执行查询：

SELECT ename, round(sysdate-hiredate) DAYS
FROM emp
WHERE deptno = 10;

SELECT ename, round(sysdate-hiredate) DAYS   FROM   emp   WHERE  deptno = 10;

返回结果为：
ENAME DAYS
--------------- ---------------------
CLARK 7913
KING 7752
MILLER 7685
说明：该查询使用日期的减法运算求两个日期的相差天数。用round函数对天数进行四舍五入。

转换函数
Oracle的类型转换分为自动类型转换和强制类型转换。常用的类型转换函数有TO_CHAR、TO_DATE或TO_NUMBER

To_char:转换成字符串类型,如:To_char(1234.5, '$9999.9'),结果:$1234.5
To_date:转换成日期类型,如:To_date('1980-01-01', 'yyyy-mm-dd'),结果:01-1月-80
To_number:转换成数值类型,如:To_number('1234.5'),结果:1234.5

自动类型转换
Oracle可以自动根据具体情况进行如下的转换：
* 字符串到数值。
* 字符串到日期。
* 数值到字符串。
* 日期到字符串。
以下是自动转换的训练。
自动转换字符型数据到数值型。
输入并执行查询：

SELECT '12.5'+11 FROM dual;

SELECT '12.5'+11 FROM dual;

执行结果为：

'12.5'+11
------------
23.5

'12.5'+11   ------------       23.5

说明：在本训练中，因为出现+运算符，说明进行的是算术运算，所以字符串'12.5'被自动转换成数值12.5，然后参加运算。

自动转换数值型数据到字符型。
执行以下查询：

SELECT '12.5'||11 FROM dual;

SELECT '12.5'||11 FROM dual;

结果为：

'12.5'
------
12.511

'12.5'   ------   12.511

说明：在本训练中，因为出现||运算符，说明进行的是字符串连接运算，数值11被自动转换成字符串'11'，然后参加运算。

日期类型转换
将日期型转换成字符串时，可以按新的格式显示。
如格式YYYY-MM-DD HH24:MI:SS表示“年-月-日 小时:分钟:秒”。Oracle的日期类型是包含时间在内的。
主要的日期格式字符的含义

AM、PM:上午、下午,如:08 AM
D:数字表示的星期(1～7),如:1,2,3,4,5,6,7
DD:数字表示月中的日期(1～31),如:1,2,3,…,31
MM:两位数的月份,如:01,02,…,12
Y、YY、YYY、YYYY 年份的后几位,如:3,03,003,2003
RR:解决Y2K问题的年度转换
DY:简写的星期名,如:MON,TUE,FRI,…
DAY:全拼的星期名,如:MONDAY,TUESDAY,…
MON:简写的月份名,如:JAN,FEB,MAR,…
MONTH:全拼的月份名,如:JANUARY,FEBRUARY,…
HH、HH12:12小时制的小时(1～12),如:1,2,3,…,12
HH24:24小时制的小时(0～23),如:0,1,2,…,23
MI:分(0～59),如:0,1,2,…,59
SS:秒(0～59),如:0,1,2,…,59
,./-;: 原样显示的标点符号
'TEXT':引号中的文本原样显示,如:TEXT

将日期转换成带时间和星期的字符串并显示。
执行以下查询：

SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual;

SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual;

结果为：

TO_CHAR(SYSDATE,'YYYY-MM-DD HH24
----------------------------------------------------------
2004-02-07 15:44:48 下午 星期六

TO_CHAR(SYSDATE,'YYYY-MM-DD HH24   ----------------------------------------------------------   2004-02-07 15:44:48 下午 星期六

说明：该语句中的第一个参数表示要转换的日期，第二个参数是格式字符串，表示转换后的格式，结果类型为字符串。“YYYY”为4位的年份，“MM”为两位 的月份，“DD”为两位的日期，“HH24”表示显示24小时制的小时,“MI”表示显示分钟，“SS”表示显示秒，“AM”表示显示上午或下午(本例中 为下午)，“DY”表示显示星期。“-”、“:”和空格原样显示，用于分割日期和时间。转换出来的系统时间为：2004年2月7日(星期六)下午15点 44分48秒。
还可以按其他的格式显示。以下查询中插入中文的年月日，其中原样显示部分区别于外层的单引号，需要用双引号引起。

将日期显示转换成中文的年月日。
输入并执行查询：

SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual;

SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual;

执行结果为：

TO_CHAR(SYSDAT
-------------------------
2003年11月18日

TO_CHAR(SYSDAT   -------------------------   2003年11月18日

说明：双引号中的中文字“年”、“月”、“日”原样显示，单引号为字符串的界定标记，区别于双引号，不能混淆。

时间显示的大小写。
步骤1：执行以下查询：

SELECT SYSDATE,to_char(SYSDATE,'yyyysp') FROM dual;

SELECT SYSDATE,to_char(SYSDATE,'yyyysp') FROM dual;

结果为：

SYSDATE TO_CHAR(SYSDATE,'YYYYSP')
------------- ----------------------------------------------
07-2月 -04 two thousand four

SYSDATE   TO_CHAR(SYSDATE,'YYYYSP')   ------------- ----------------------------------------------   07-2月 -04  two thousand four

步骤2：执行以下查询：

SELECT to_char(SYSDATE,'Yyyysp') FROM dual;

SELECT to_char(SYSDATE,'Yyyysp') FROM dual;

结果为：

SYSDATE TO_CHAR(SYSDATE,'YYYYSP')
-------------- -----------------------------------------------
Two Thousand Four

SYSDATE    TO_CHAR(SYSDATE,'YYYYSP')   -------------- -----------------------------------------------   Two Thousand Four

数值转换符:

9:代表一位数字，如果是正数，前面是空格，如果是负数，前面是−号,如:9999
0:代表一位数字，在相应的位置上如果没有数字则出现0 0000
,:逗号，用作组分隔符,如:99,999
.:小数点，分隔整数和小数,如:999.9
$:$货币符号,如:$999.9
L:本地货币符号,如:L999.99
FM:去掉前后的空格,如:FM999.99
EEEE:科学计数法,如:9.9EEEE
S:负数符号−放在开头,如:S999.9

将数值转换成字符串并按新格式显示。
执行以下查询：

SELECT TO_CHAR(sal,'$99,999') SALARY FROM emp
WHERE ename = 'SCOTT';

SELECT TO_CHAR(sal,'$99,999') SALARY FROM emp   WHERE ename = 'SCOTT';

结果为：

SALARY
------------
$4,000

SALARY   ------------     $4,000

说明：格式字符串中“$”表示转换结果前面添加$。“9”表示一位数字，“99，999”表示结果可以显示为5位的数字。“,”表示在相应的位置上添加逗 号。如果实际数值位数不足5位，则只显示实际位数，如4000实际位数为4位，则只显示4位。如果实际位数超过5位，则会填充为#号。

其他函数
nvl:空值转换函数,如:nvl(null, '空'),结果:空
decode:实现分支功能,如:decode(1,1, '男', 2, '女'),结果:男
userenv:返回环境信息,如:userenv('LANGUAGE'),结果:SIMPLIFIED CHINESE_CHINA.ZHS16GBK
greatest:返回参数的最大值,如:greatest(20,35,18,9),结果:35
least:返回参数的最小值,如:least(20,35,18,9),结果:9

空值的转换
如果对空值NULL不能很好的处理，就会在查询中出现一些问题。在一个空值上进行算术运算的结果都是NULL。最典型的例子是，在查询雇员表时，将工资sal字段和津贴字段comm进行相加，如果津贴为空，则相加结果也为空，这样容易引起误解。
使用nvl函数，可以转换NULL为实际值。该函数判断字段的内容，如果不为空，返回原值；为空，则返回给定的值。

如下3个函数，分别用新内容代替字段的空值：
nvl(comm, 0)：用0代替空的Comm值。
nvl(hiredate, '01-1月-97')：用1997年1月1日代替空的雇佣日期。
nvl(job, '无')：用“无”代替空的职务。
使用nvl函数转换空值。
执行以下查询：

SELECT ename,nvl(job,'无'),nvl(hiredate,'01-1月-97'),nvl(comm,0) FROM emp;

SELECT ename,nvl(job,'无'),nvl(hiredate,'01-1月-97'),nvl(comm,0) FROM  emp;

结果为：

ENAME NVL(JOB,'N NVL(HIREDA NVL(COMM,0)
---------------- ------------------- ------------------ -------------------
SMITH CLERK 17-12月-80 0
ALLEN SALESMAN 20-2月 -81 300

ENAME      NVL(JOB,'N NVL(HIREDA NVL(COMM,0)   ---------------- ------------------- ------------------ -------------------   SMITH      CLERK        17-12月-80            0   ALLEN      SALESMAN    20-2月 -81          300

说明：本例中，空日期将显示为“01-1月-97”，空职务显示为“无”，空津贴将显示为0。

最大、最小值函数
greatest返回参数列表中的最大值，least返回参数列表中的最小值。
这两个函数的参数是一个表达式列表，按表达式列表中的第一个表达式的类型对求值后的表达式求得最大或最小值。对字符的比较按ASCII码的顺序进行。如果表达式中有NULL，则返回NULL。

比较字符串的大小，返回最大值。
执行以下查询：

SELECT greatest('ABC','ABD','abc', 'abd') FROM dual;

SELECT greatest('ABC','ABD','abc', 'abd') FROM dual;

执行结果为：

GRE
------
abd

GRE   ------   abd

说明：在上述四个字符串中，大小关系为abd>abc>ABD>ABC。在ASCII码表中，排在后边的字符大，小写字母排在大写字母之后。字符串的比较原则是，先比较第一位，如果相同，则继续比较第二位，依此类推，直到出现大小关系。