Oracle中的常用函数详解(2)-木庄网络博客

其中DBTIMEZONE是数据库的，session是针对当前会话的，因为时区在会话级可以改变

SELECT SESSIONTIMEZONE FROM DUAL;  
ALTER SESSION SET TIME_ZONE = '8:00';
SELECT SESSIONTIMEZONE FROM DUAL;

12、常用时间查询：

----  上月最后一天
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYY/MM/DD') FROM DUAL;
----: 上各月的今天
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL;
---- 上个月第一天
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-2),'YYYY-MM-DD') FirstDay FROM DUAL;
---  要找到某月中所有周五的具体日期 
SELECT TO_CHAR(T.D, 'YY-MM-DD')
FROM (SELECT TRUNC(SYSDATE, 'MM') + ROWNUM -1 AS D FROM DBA_OBJECTS WHERE ROWNUM < 32) T
WHERE TO_CHAR(T.D, 'MM') = TO_CHAR(SYSDATE, 'MM') AND TRIM(TO_CHAR(T.D, 'DAY')) = '星期五'

四、转换函数

1： to_char(date,'format') ：把对应的数据转换为字符串类型

TO_CHAR的fmt:格式字符串，不分大小写。

select to_char(sysdate,'yyyy-mm-dd hh24:mi;ss') from dual

Y或YY或YYY 年的最后一位，两位或三位
SYEAR或YEAR: SYEAR使公元前的年份前加一负号 --TWENTY ELEVEN
Q：季度，1～3月为第一季度 -- 2表示第二季度
MM：月份数 --04表示4月
RM：月份的罗马表示 --IV表示4月
MON：月份 --4月
Month：用9个字符长度表示的月份名 -- 4月
WW：当年第几周 -- 24表示2002年6月13日为第24周
W：本月第几周 -- 2011年04月26日为第4周
DDD：当年第几天. 1月1日为001，2月1日为032
DD：当月第几天
D：周内第几天
DY：周内第几天缩写
HH或HH12： 12进制小时数
HH24： 24小时制
MI：分钟数(0～59) ：提示注意不要将MM格式用于分钟(分钟应该使用MI)。MM是用于月份的格式，将它用于分钟也能工作，但结果是错误的。
SS：秒数(0～59)

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

2： to_char(n,'format')：把对应的数字为字符串类型

FORMAT格式符：

9：带有指定位数的值
0：前导零的值
. （句点）：小数点
, （逗号）：分组（千）分隔符
PR：尖括号内负值
S：带负号的负值（使用本地化）
L：货币符号（使用本地化）
D：小数点（使用本地化）
G：分组分隔符（使用本地化）
MI：在指明的位置的负号（如果数字 < 0）
PL：在指明的位置的正号（如果数字 > 0）
SG：在指明的位置的正/负号
RN：罗马数字（输入在 1 和 3999 之间）
TH or th：转换成序数

SELECT TO_CHAR(122323.45, '$99999999.99') FROM DUAL;

3： to_date(string,'format')：将字符串转化为日期

主要用于比较和修改日期。

SELECT TO_DATE('2011/03/24', 'YYYY-MM-DD') FROM DUAL;

4： to_number：将给出的字符转换为数字

SELECT TO_NUMBER('￥2008.00'，'L9999D99') AS Year FROM DUAL;

五、辅助函数

1、decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值):根据条件返回相应值

值1……n 不能为条件表达式,这种情况只能用case when then end解决。

select decode(xqn,1,'星期一',2,'星期二',3,'星期三','星期三以后') 星期FROM xqb

2、greatest(exp1,exp2,exp3,……,expn):返回表达式列表中值最大的一个。 ; least(exp1,exp2,exp3,……,expn):返回表达式列表中值最小的一个。

如果表达式类型不同，会隐含转换为第一个表达式类型。

SELECT greatest(10,32,'123','2006') FROM dual;

3、nullif (expr1, expr2)：expr1和expr2相等返回NULL，不相等返回expr1。

SELECT NULLIF( 'a', 'b' ) ;--返回值 a
SELECT NULLIF( 'a', 'a');--返回 NULL

实际应用：

--添加函数查询结果，要求（将日期类型默认'0001/1/1',改成null,不相等，返回本身日期）--
select NULLIF(RECEIVEDATE,TO_DATE('0001/1/1','yyyy-mm-dd hh24:mi:ss')) 收货日期,NAME 单据名称 from tab

4、nvl (expr1, expr2):若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。

select NVL(null,'未知') SexType from dual;--expr1为空，返回expr2，结果：'未知'
select NVL('1','2') SexType from dual;--expr1不为空，返回expr1， 结果：1

实际应用：

--加函数时情况，要求（field 为null，返回0 ；field 不为null,返回本身
select NVL(UseFlag,'0') 使用标志,NAME 单据名称 from tab

5、nvl2(expr1, expr2, expr3) :expr1不为NULL，返回expr2；expr2为NULL，返回expr3。

expr2和expr3类型不同的话，expr3会转换为expr2的类型

select NVL2(0,1,2) from dual;--不为null时，返回expr2 ，结果：1
select NVL2(null,1,2) from dual;--为null时，返回expr3 ，结果：2

实际应用：

--应用到实际查询中，要求（field 为null,返回0; field 不为null,返回本身）
 select NVL2(UseFlag,UseFlag,'0') 使用标志,A.NAME 单据名称 from tab

6、coalesce(c1, c2, ...,cn)：返回列表中第一个非空的表达式，如果所有表达式都为空值则返回1个空值。

select COALESCE(null,3*5,44) hz from dual; --返回15

7、sys_context('USERENV',c2)：返回系统'USERENV'变量中c2对应的的值。

SYS_CONTEXT('USERENV','LANGUAGE') language,

8、sys_connect_by_path(column_name,'分隔符')：把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示

详见 Oracle递归查询connect by

第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符
伪列CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLE

结构化查询：START WITH ...CONNNECT BY PRIOR基本语法是：

SELECT ...FROM
WHERE （过滤返回记录，仅过滤被限定节点，其根节点和子节点均不受影响）
START WITH (根节点，可以指定多个节点)
CONNECT BY PRIOR= （连接条件，PRIOR置于等号前，则从根节点到叶节点开始检索；置于等号后，则从叶节点到根节点开始检索）

该查询访问路径如下：从根节点开始，向下扫描子节点，该子节点已被访问则转向其最左侧未被访问的子节点，否则判断该节点是否为根节点，是则访问完毕，否则返回父节点重新执行判断。

SELECT ename   FROM scott.emp    START WITH ename = 'KING'    CONNECT BY PRIOR empno = mgr;

--得到结果为：

KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES

SELECT SYS_CONNECT_BY_PATH(ename, </'>') "Path"    FROM scott.emp    START WITH ename = 'KING'    CONNECT BY PRIOR empno = mgr;

--得到结果为：

KING
KING>JONES
KING>JONES>SCOTT
KING>JONES>SCOTT>ADAMS
KING>JONES>FORD
KING>JONES>FORD>SMITH
KING>BLAKE
KING>BLAKE>ALLEN
KING>BLAKE>WARD
KING>BLAKE>MARTIN
KING>BLAKE>TURNER
KING>BLAKE>JAMES
KING>CLARK
KING>CLARK>MILLER

六、聚合函数

AVG(DISTINCT|ALL)：平均值，DISTINCT表示对不同的值求平均值,重复值的列的只取一次。

SELECT AVG(DISTINCT SAL) FROM SCOTT.EMP;

MAX(DISTINCT|ALL)：最大值
MIN(DISTINCT|ALL)：最小值
SUM(DISTINCT|ALL)：求和
COUNT(DISTINCT|ALL)：求记录数
wmsys.wm_concat(DISTINCT|ALL)：合并列。
将一列的多行记录合并到一列，用逗号隔开。例如表的有两个字段，要按airport_id合并成两行可用sql语句

select airport_id,   wmsys.wm_concat(distinct  account) from AIRPORT_MODIFY group by airport_id

七、分析函数

见专题：Oracle分析函数

到此这篇关于Oracle常用函数的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持。

标签：Oracle

返回前面的内容

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » Oracle中的常用函数详解

Oracle中的常用函数详解

四、转换函数

五、辅助函数

六、聚合函数

七、分析函数

数据库系统概念第6版

相关推荐

评论