www.2527.com_澳门新葡8455手机版_新京葡娱乐场网址_
做最好的网站

新京葡娱乐场网址Oracle内置函数大全,SQL内置函

2019-08-02 02:40 来源:未知

日期格式元素

SQL内置函数

【**函数】Oracle函数系列(2)--数学函数**及日期函数

一般地,日期格式说明符是不太敏感的。然而,当为了显示而说明日期格式、对于文本数据中的说明符等情况下,它就变得比较务实、具体了。以月份的名字为例,通过下面引用的结果解释一下该情况的效果:
 TO_CHAR(SYSDATE,’MONTH’)=NOVEMBER
 TO_CHAR(SYSDATE,’Month’)=November
 TO_CHAR(SYSDATE,’month’)=november
 当你键入的单词MONTH(月份)是大写字母时,月份名字的结果就全部说明为大写。否则,如果串说明时的首字母是大写,则返回的月份名首字母也是大写字母。同样的规则也适用于下面的格式说明符:AD、AM、PM、BC、DAY、DY、MON、RM和YEAR

•      ABS

 

ABS函数返回一数值的绝对值。负数将舍去其负号。下面是它的语法格式:
ABS(number)
其中,number是希望得到其绝对值的数值
ABS函数截去数值的符号。如下例所示:
ABS(5)=5
ABS(5)=5
不管是5还是-5,其绝对值均为5。

  ABS函数返回一数值的绝对值。负数将舍去其负号。下面是它的语法格式:

1  BLOG文档结构图

新京葡娱乐场网址 1 

ACOS函数返回一数值的反余弦值。结果以弧度表示,且在0到PI之间。
其语法格式如下:
ACOS(number)
其中,number必须是-1与1之间的值
下面的例子显示了1和-1 的反余弦值:
ACOS(1)=0
ACOS(-1)=3.1415927(PI)

  ABS(number)

2  前言部分

ADD_MONTHS函数将一个日期上加上一指定的月份数,所以,日期中的日将是不变的。然而,如果开始日期是某月的最后一天,那么,结果将会调整以使返回值仍对应新的一月的最后一天。如果,结果月份的天数比开始月份的天数少,那么,也会向回调整以适应有效日期。其语法格式如下:
 ADD_MONTHS(date,months)
 其中:
 date 一个日期数值
 months 要加上的月份数。要减去的月份数用负数
 下面的例子解释了该函数的使用:
 ADD_MONTHS(TO_DATE(’15-Nov-1961’,’d-mon-yyyy’),1) =’15-Dec-1961
 ADD_MONTHS(TO_DATE(’30-Nov-1961’,’d-mon-yyyy’),1) =’31-Dec-1961
 ADD_MONTHS(TO_DATE(’31-Jan-1999’,’d-mon-yyyy’),1) =’28-Feb-1999
 注意,在上面的第三个例子中,函数将不得不将31日往回调整为28日,已使结果对应新一月的最后一天。因为,1999年的2月份只有28天。在第二个例子中,则是从30往后调整为31,也同样是为了保持对应的最后一天。

  其中,number是希望得到其绝对值的数值

2.1  导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:

① 数学函数

② trunc和round函数

③ 常用日期函数

Tips:

① 本文在itpub(

② 文章中用到的所有代码、相关软件、相关资料及本文的pdf版本都请前往小麦苗的云盘下载,小麦苗的云盘地址见:

③ 若网页文章代码格式有错乱,请下载pdf格式的文档来阅读。

④ 在本篇BLOG中,代码输出部分一般放在一行一列的表格中。其中,需要特别关注的地方我都用灰色背景和粉红色字体来表示,比如在下边的例子中,thread 1的最大归档日志号为33,thread 2的最大归档日志号为43是需要特别关注的地方;而命令一般使用黄色背景和红色字体标注;对代码或代码输出部分的注释一般采用蓝色字体表示。

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

[ZHLHRDB1:root]:/>lsvg -o

T_XLHRD_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

====》2097152*512/1024/1024/1024=1G

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

 

新京葡娱乐场网址 2

  ABS函数截去数值的符号。如下例所示:

2.2  本文简介

最近一段时间比较忙,就给大家分享一些简单的函数吧。本部分分享完后就给大家分享有关锁的内容,请大家持续关注小麦苗,谢谢。

之前发布了Oracle函数系列(1)--字符函数(

ASCII函数返回你指向的字符串首字母的十进制表示代表码值。该返回值或许是ASCII码值,也或许不是。如果用户的数据库字符集是7位的ASCII值,那就得到一ASCII码值。该返回值总是以用户使用的字符集为基础的。
 ASCII(string)
 其中:string一个字符串。更多的时候就是一字符
 下面的例子解释了该函数的使用:
 ASCII(‘j’)=74
 ASCII(‘jeff’)=74
 从上面的第二个例子,我们不难看出,如果指定了一个多字符串,该函数将忽略除首字母外的所有字符。

  ABS(5)=5

3  数学函数

数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括 cos, cosh,exp,ln, log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round,如下所示:

l round(n,[m]) 

该函数用于执行四舍五入,如果省掉 m,则四舍五入到整数, 如果 m 是正数,则四舍五入到小数点的 m 位后。如果 m 是负数,则四舍五入到小数点的 m 位前。

l trunc(n,[m])  ---可以对数值和date类型的数据截取,具体见下面的解析。  

l floor(n)  返回小于或是等于 n 的最大整数

l ceil(n)  返回大于或是等于 n 的最小整数

l mod(m,n):取余(ANSI标准中规定取模运算的符号为%在一些解释器中被函数MOD所取代)

l sign(m): 根据参数m的值是0,正数还是负数依次返回0,1,-1

l abs(n):  返回数字 n 的绝对值

l acos(n):  返回数字的反余弦值

l asin(n):  返回数字的反正弦值

l atan(n):  返回数字的反正切值

l cosh(): 双曲余弦

l sinh(): 双曲正弦

l tanh(); 双曲正切

l sin(n); 正弦

l cos(n):余弦

l exp(n):  返回 e 的 n 次幂

l power(m,n):返回 m 的 n 次幂

l log(m,n):  返回对数值

l ln(m): 返回m(m>0)的自然对数(以常数e为底数的对数叫做自然对数,记作ln N(N>0).)

l sqrt(m): 返回参数的平方根 由于负数是不能开平方的 ,所以m不能为负数

 

注意:三角函数默认的参数认定为弧度制

对数字的处理,在财务系统或银行系统中用的最多,不同的处理方法,对财务报表有不同的结果。

Oracle内置函数大全 PPT版下载

  ABS(5)=5

3.1   **trunc(n,[m])**

免费下载地址在

  不管是5还是-5,其绝对值均为5。

3.1.1  对日期型数据截去取整

TRUNC:表示对日期进行截取

    下面是该函数的使用情况:

 SELECT trunc(SYSDATE),

       TRUNC(SYSDATE - 1**),**

       SYSDATE - 1**,**

       trunc(SYSDATE, 'dd'), --返回当前年月日

       trunc(SYSDATE, 'd'), --返回当前星期的第一天。 

       trunc(SYSDATE, 'mm'), --返回当月第一天。

       trunc(SYSDATE, 'yyyy'), --返回当年第一天。

       trunc(sysdate,'mi'),  --截取到分钟,即秒是以00来显示的,可以和 (SYSDATE - 1)的时间做比较

       TRUNC(TO_DATE('24-12月-1999 08:00 ','dd-mon-yyyy hh:mi AM')),

       TRUNC(TO_DATE('24-12月-1999 08:37 ','dd-mon-yyyy hh:mi AM'),'hh')

FROM   dual;

 

转换为列模式后的结果:

新京葡娱乐场网址 3 

新京葡娱乐场网址 4 

 

用户名与密码都是www.linuxidc.com

SQL内置函数

3.1.2  对数值截去取整返回类型为数值

SELECT TRUNC(456.873),TRUNC(456.873,2),trunc(456.873,-2),trunc(456.873,-3) FROM dual ;

新京葡娱乐场网址 5 

 

TRUNC(for number)

    TRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,只是该函数不对指定小数前或后的部分做相应舍入选择处理,而统统截去。

    下面是该函数的使用情况:

    TRUNC(89.985,2)=89.98

    TRUNC(89.985)=89

    TRUNC(89.985,-1)=80

    注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截去,即均以0记。与取整类似,比如参数为1即取整到十分位,如果是-1,则是取整到十位,以此类推。

 

具体下载目录在 /2012年资料/8月/15日/Oracle内置函数大全 PPT

•      ACOS

3.2  Round

新京葡娱乐场网址 6

  ACOS函数返回一数值的反余弦值。结果以弧度表示,且在0到PI之间。

3.2.1  日期

ROUND:对日期进行四舍五入

如果按月进行四舍五入,15日算上半月,会舍去;16日算下半月,会进位

如果按日进行四舍五入,星期三算上半周,星期四算下半周,周日不变

select round(to_date('15/2/2011','DD/MM/YYYY'),'month') from dual

Select ROUND(Sysdate,'YEAR') From DUAL

 

例如:Assume SYSDATE = '25-JUL-95':

ROUND(SYSDATE,'MONTH')    则     01-AUG-95

ROUND(SYSDATE ,'YEAR')     则    01-JAN-96

  其语法格式如下:

3.2.2  数值

 

SELECT round(156.00,-2),round(156.00,-1),round(156.00,-3),trunc(round(156.00,-1),-1) FROM dual ;

新京葡娱乐场网址 7

 

  ACOS(number)

4  日期函数 

1. 日期函数用于处理 date 类型的数据。

2. 在日期上加上或减去一个数字结果仍为日期。

3. 两个日期相减返回日期之间相差的天数。

4. 可以用数字除24来向日期中加上或减去小时。

5. 默认情况下日期格式是 DD-MON-RR  即 12-7 月-78,这个可以用如下语句来查询:

select sys_context('userenv','nls_date_format') from dual;

 

既然数据库以数字方式存储日期,你就可以用算术运算符进行计算,例如,加或减。你可以加或减数字常数以及日期。从日期加或者减一个数,结果是一个日期值,两个日期相减,得到两个日期之间的天数,用小时数除以24,可以加小时到日期上。

 

运算             结果      说明

date number  日期  加一个天数到一个日期上

date - number  日期  从一个日期上减一个天数

date - date  天数  用一个日期减另一个日期

date number/24  日期  加一个小时数到一个日期上

 

(1)sysdate:  该函数返回系统时间

(2)add_months(d,n) 该函数将给定的日期增加n 个月

(3)last_day(d):返回指定日期所在月份的最后一天

 

注意:

sysdate:年月日时分秒

日期 -1,都代表一天的时间,比如:

Select TRUNC(Sysdate-365) From DUAL

Select Sysdate-1/24/60 From DUAL

 

求月份:

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

新京葡娱乐场网址 8 

 

--上一个月

SELECT to_char(add_months(trunc(SYSDATE), -1), 'yyyy-mm') FROM dual;

新京葡娱乐场网址 9 

 

--下一个月

SELECT to_char(add_months(trunc(SYSDATE), 1), 'yyyy-mm') FROM dual;

新京葡娱乐场网址 10 

 

--去年

SELECT SYSDATE,

       add_months(SYSDATE, -12**)**

FROM   dual;

新京葡娱乐场网址 11 

 

技巧:

如果某一个需求需要求在某一个月中的情况(比如:2012-1-1到2012-1-31),此时可以换种思维,就是用年份和月份来表示,将他们截取出来,如 createdate这个列就可以表示成:to_char(createdate,'yyyy-mm')=to_char(sysdate,'yyyy-mm')

  其中,number必须是-1与1之间的值

4.1   MONTHS_BETWEEN

MONTHS_BETWEEN:表示两个日期的月份之差,即在给定的两个日期之间有多少个月

select months_between(sysdate,'16-9月-2001') from dual;

Select EMPNO,HIREDATE,MONTHS_BETWEEN(Sysdate,HIREDATE)/12 From EMP;

SELECT SYSDATE,

       MONTHS_BETWEEN(SYSDATE, TO_DATE('2016-12-20', 'YYYY-MM-DD')),

       MONTHS_BETWEEN(SYSDATE, TO_DATE('2016-10-20', 'YYYY-MM-DD'))

  FROM DUAL;

新京葡娱乐场网址 12 

  下面的例子显示了1和-1 的反余弦值:

4.2   ADD_MONTHS(d,n)

ADD_MONTHS(d,n)**:**当n为正数时,该函数将给定的日期增加n 个月,为负数时减去n个月,该函数很常用,可以用来表示上个月、下个月,去年和下一年等等。

select add_months(sysdate,4) from dual

Select HIREDATE,ADD_MONTHS(HIREDATE,3) From EMP

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 1), ADD_MONTHS(SYSDATE, -1) FROM DUAL;

新京葡娱乐场网址 13 

 

ADD_MONTHS(x, y)用于计算x加上y个月的结果。如果y是负数,就从x中减去y个月。下面这个例子在2007年1月1日上加上13个月:

SELECT ADD_MONTHS('01-JAN-2007', 13)FROM dual;ADD_MONTH---------01-FEB-08

下面这个例子从2008年1月1日中减去13个月;注意本例实际上是使用ADD_MONTHS 函数在这个日期上加上-13个月:

SELECT ADD_MONTHS('01-JAN-2008', -13)FROM dual;ADD_MONTH---------01-DEC-06

ADD_MONTHS函数可以用于时间和日期。例如,下面这个查询在时间值2007年1月1日下午7点15分26秒上增加两个月:

SELECT ADD_MONTHS(TO_DATE('01-JAN-2007 19:15:26','DD-MON-YYYY HH24:MI:SS'), 2)FROM dual;ADD_MONTH---------01-MAR-07

下面这个查询重写了上面这个例子:它使用TO_CHAR函数将从ADD_MONTHS函数中返回的时间值转换为字符串,并指定格式为DD-MON-YYYY HH24:MI:SS:

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01-JAN-2007 19:15:26','DD-MON-YYYY HH24:MI:SS'), 2), 'DD-MON-YYYY HH24:MI:SS')FROM dual;

TO_CHAR(ADD_MONTHS(T


01-MAR-2007 19:15:26

 

  ACOS(1)=0

4.3   NEXT_DAY(d,n)

NEXT_DAY(d,n)**:返回以时间点d为基准(开始),下一个"目标日n**"的日期

SELECT SYSDATE,NEXT_DAY(SYSDATE,'星期二') FROM DUAL;   表示下周二的日期

 

补充:修改当地语言:ALTER SESSION SET NLS_LANGUAGE='AMERICAN';

      修改为中文:ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE';

 

在英语的环境中,执行星期几时要用英文:

select next_day(sysdate,'Friday') from dual;

SQL> SELECT SYSDATE,NEXT_DAY(SYSDATE,'星期二') FROM DUAL;

SELECT SYSDATE,NEXT_DAY(SYSDATE,'星期二') FROM DUAL

                                *

ERROR at line 1:

ORA-01846: not a valid day of the week

 

 

SQL> ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE';

 

会话已更改。

 

SQL>  SELECT SYSDATE,NEXT_DAY(SYSDATE,'星期二') FROM DUAL;

 

SYSDATE             NEXT_DAY(SYSDATE,'

------------------- -------------------

2016-11-20 19:50:07 2016-11-22 19:50:07

 

SQL> select next_day(sysdate,'Friday') from dual;

select next_day(sysdate,'Friday') from dual

                        *

第 1 行出现错误:

ORA-01846: 周中的日无效

 

 

SQL> ALTER SESSION SET NLS_LANGUAGE='AMERICAN';

 

Session altered.

 

SQL>  select next_day(sysdate,'Friday') from dual;

 

NEXT_DAY(SYSDATE,'F

-------------------

2016-11-25 19:50:55

 

SQL> select next_day(sysdate,1) from dual;

 

NEXT_DAY(SYSDATE,1)

-------------------

2016-11-27 19:52:50

 

SQL>  ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE';

 

会话已更改。

 

SQL> select next_day(sysdate,1) from dual;

 

NEXT_DAY(SYSDATE,1)

-------------------

2016-11-27 19:52:59

 

SQL>

 

 

在生活中,为了避免因为语言问题导致的命令错误,常用下面的方法:

select next_day(sysdate,1) from dual;--1 表示星期日, 7表示星期六,依此类推,不过在用数字作为日期的时候,next_day函数不能够直接赋值给变量,只能够通过select into方式.

 

SELECT to_char(next_day(last_day(trunc(SYSDATE)), 7) - 7, 'YYYY-MM-DD')

INTO   v_sdate

FROM   dual;

 

--- 或者 下边

v_sdate := to_char(next_day(last_day(trunc(SYSDATE)), '星期六') - 7**,**

                   'YYYY-MM-DD');

--这种错误,next_day 使用数字不能直接赋值  ,困扰我很久 2013/1/20 16:40 躺床上突然想到才解决              

v_sdate := to_char(next_day(last_day(trunc(SYSDATE)), 7) - 7, 'YYYY-MM-DD');

 

 

-----  判断今天是否月份最后一天

WITH T AS

(SELECT   sysdate DAT FROM DUAL)

SELECT DECODE(DAT, LAST_DAY(DAT), 'yes', 'no') FROM T;

 

--- 判断 '2014-04-30'是否所在月份最后一天

WITH T AS

(SELECT DATE '2014-04-30' DAT FROM DUAL)

SELECT DECODE(DAT, LAST_DAY(DAT), 'yes', 'no') FROM T;

  ACOS(-1)=3.1415927(PI)

4.4   LAST_DAY

LAST_DAY  :计算给定日期的最后一天,当月的最后一天

Select HIREDATE,LAST_DAY(HIREDATE)-HIREDATE From EMP;

SELECT last_day(TO_DATE('201302', 'yyyymm')),

       last_day(trunc(SYSDATE))

FROM   dual;

新京葡娱乐场网址 14 

SQL内置函数

一、 next_day 和last_day 联合使用

SELECT trunc(SYSDATE, 'MM') 本月第一天,

       add_months(trunc(SYSDATE, 'MM'), 1) 下个月第一天,

       add_months(trunc(SYSDATE, 'MM'), 1) - 1 本月最后一天,

       next_day(add_months(trunc(SYSDATE, 'MM'), 1) - 1, 7) 本月最后一天的下一个周六,

       next_day(add_months(trunc(SYSDATE, 'MM'), 1) - 1, 7) - 7 本月最后一个周六,

       next_day(last_day(trunc(SYSDATE)), 7) - 7 本月最后一个周六

FROM   dual;

新京葡娱乐场网址 15 

•       ADD_MONTHS

4.5   NEW_TIME

NEW_TIME: 调整时区

下边给出了所有的时区

简写时区

AST or ADT 大西洋标准时间

HST or HDT 阿拉斯加_夏威夷时间

BST or BDT 英国夏令时

MST or MDT 美国山区时间

CST or CDT 美国中央时区

NST 新大陆标准时间

EST or EDT 美国东部时区

PST or PDT 太平洋标准时间

GMT 格伦威治标准时间

YST or YDT Yukon标准时间

 

  ADD_MONTHS函数将一个日期上加上一指定的月份数,所以,日期中的日将是不变的。然而,如果开始日期是某月的最后一天,那么,结果将会调整以使返回值仍对应新的一月的最后一天。如果,结果月份的天数比开始月份的天数少,那么,也会向回调整以适应有效日期。其语法格式如下:

4.6  求2个日期之间的时间差

在ORACLE里日期类型是可以直接进行比较的。举个例子

SQL> SELECT to_date('2006-12-05', 'yyyy-mm-dd') - to_date('2006-12-07', 'yyyy-mm-dd') FROM dual ;

 

TO_DATE('2006-12-05','YYYY-MM-DD')-TO_DATE('2006-12-07','YYYY-MM-DD')


                                                               -2

这说明2006-12-05要比7号早2天。

SQL> SELECT to_date('2006-12-07 14:23:24' , 'yyyy-mm-dd hh24:mi:ss') 1/24 FROM dual ;

 

TO_DATE('2006-12-07


2006-12-07 15:23:24

 

SQL>

这是说明在某一时间上加一小时,1是代表一天,1/24就是一小时,同理1/24/60就是一分钟

 

select trunc(sysdate) - trunc(to_date('2006-05-28 10:20','YYYY-MM-DD HH24:MI')) from dual

 

Oracle中计算时间差是经常用到的。可以使用“日期1-日期2”并加以运算,来获得你要想的时间差:天、小时、分钟或者秒。

例如:

 SELECT TO_DATE('2012-02-20 17:45:04', 'yyyy-mm-dd hh24:mi:ss') -

       TO_DATE('2012-02-19 08:34:04', 'yyyy-mm-dd hh24:mi:ss') AS DAY

FROM   dual;

  结果:

新京葡娱乐场网址 16

这里的TO_DATE很有用,它决定你的时间存储格式。

那么如果要获取相应的时间单位,下面:

   ADD_MONTHS(date,months)

一、 以天为单位

round(to_number(end-date-start_date))

例如:

 SELECT round(to_number(TO_DATE('2012-02-20 17:45:04',

                               'yyyy-mm-dd hh24:mi:ss') -

                       TO_DATE('2012-02-19 08:34:04',

                               'yyyy-mm-dd hh24:mi:ss'))) AS DAY

FROM   dual;

 结果:

新京葡娱乐场网址 17

   其中:

二、 以小时为单位

 

round(to_number(end-date-start_date)*24)

例如:

 SELECT round(to_number(TO_DATE('2012-02-20 17:45:04',

                               'yyyy-mm-dd hh24:mi:ss') -

                       TO_DATE('2012-02-19 08:34:04',

                               'yyyy-mm-dd hh24:mi:ss')) * 24) AS Hour

FROM   dual;

 结果:

新京葡娱乐场网址 18

   date 一个日期数值

三、 以分钟为单位

round(to_number(end-date-start_date)*1440)

例如:

SELECT round(to_number(TO_DATE('2012-02-20 17:45:04',

                               'yyyy-mm-dd hh24:mi:ss') -

                       TO_DATE('2012-02-19 08:34:04',

                               'yyyy-mm-dd hh24:mi:ss')) * 1440) AS Minite

FROM   dual; 

结果:

新京葡娱乐场网址 19

 

   months   要加上的月份数。要减去的月份数用负数

四、 

ROUND(TO_NUMBER(END_DATE

  • START_DATE) * 24 * 60 * 60)

   下面的例子解释了该函数的使用:

五、 毫秒:

ROUND(TO_NUMBER(END_DATE

  • START_DATE) * 24 * 60 * 60 * 1000)

   ADD_MONTHS(TO_DATE(’15-Nov-1961’,’d-mon-yyyy’),1)   =’15-Dec-1961

4.7  常用之日期格式

  

 日期格式                 說明  

------------------------------------------------------------------------  

YYYY/MM/DD              -- 年/月/日   

YYYY                    -- 年(4位)  

YYY                     -- 年(3位)  

YY                      -- 年(2位)  

MM                      -- 月份  

DD                      -- 日期  

D                       -- 星期  

                             -- 星期日 = 1  星期一 = 2 星期二 = 3  

                         -- 星期三 = 4  星期四 = 5 星期五 = 6 星期六 = 7  

                               

DDD                     -- 一年之第幾天  

WW                      -- 一年之第幾週  

W                       -- 一月之第幾週  

YYYY/MM/DD HH24:MI:SS   -- 年/月/日 時(24小時制):分:秒  

YYYY/MM/DD HH:MI:SS     -- 年/月/日 時(非24小時制):分:秒  

 J                       -- Julian day, julian date指的是公元前4712年1月1日起经过的天数.,104235,1指21世纪,04指第四年,235指这年的第几天!

RR/MM/DD                -- 公元2000問題   

    -- 00-49 = 下世紀;50-99 = 本世紀  

ex.  

select to_char(sysdate,''YYYY/MM/DD'') FROM DUAL;             -- 2007/09/20  

select to_char(sysdate,''YYYY'') FROM DUAL;                   -- 2007  

select to_char(sysdate,''YYY'') FROM DUAL;                    -- 007  

select to_char(sysdate,''YY'') FROM DUAL;                     -- 07  

select to_char(sysdate,''MM'') FROM DUAL;                     -- 09  

select to_char(sysdate,''DD'') FROM DUAL;                     -- 20  

select to_char(sysdate,''D'') FROM DUAL;                      -- 5  

select to_char(sysdate,''DDD'') FROM DUAL;                    -- 263  

select to_char(sysdate,''WW'') FROM DUAL;                     -- 38  

select to_char(sysdate,''W'') FROM DUAL;                      -- 3  

select to_char(sysdate,''YYYY/MM/DD HH24:MI:SS'') FROM DUAL;  -- 2007/09/20 15:24:13  

select to_char(sysdate,''YYYY/MM/DD HH:MI:SS'') FROM DUAL;    -- 2007/09/20 03:25:23  

select to_char(sysdate,''J'') FROM DUAL;                      -- 2454364

select to_char(sysdate-365,'j') from dual; 

select to_date (to_char(sysdate-365,'j')   trunc(dbms_random.value(0,365)),'j') from dual; 

select to_char(sysdate,''RR/MM/DD'') FROM DUAL;               -- 07/09/20 

   ADD_MONTHS(TO_DATE(’30-Nov-1961’,’d-mon-yyyy’),1)   =’31-Dec-1961

4.8  时区

如何判断数据库的时区?

select dbtimezone from dual;

select sessiontimezone from dual;

select systimestamp from dual;

select current_date from dual;

select current_timestamp from dual;

   ADD_MONTHS(TO_DATE(’31-Jan-1999’,’d-mon-yyyy’),1)   =’28-Feb-1999

5  常用日期查询

1.获取当前月份的第一天

运行这个命令能快速返回当前月份的第一天。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"

FROM DUAL;

 

2.获取当前月份的最后一天

这个查询语句类似于上面那个语句,而且充分照顾到了闰年,所以当二月份有 29 号,那么就会返回 29/2 。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"

FROM DUAL;

 

3.获取当前年份的第一天

每年的第一天都是1 月1日,这个查询语句可以使用在存储过程中,需要对当前年份第一天做一些计算的时候。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;

 

4.获取当前年份的最后一天

类似于上面的查询语句。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL

 

5.获取当前月份的天数

这个语句非常有用,可以计算出当前月份的天数。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days

FROM DUAL;

 

6.获取当前月份剩下的天数

下面的语句用来计算当前月份剩下的天数。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

SELECT SYSDATE,

LAST_DAY (SYSDATE) "Last",

LAST_DAY (SYSDATE) - SYSDATE "Days left"

FROM DUAL;

 

7.获取两个日期之间的天数

使用这个语句来获取两个不同日期自检的天数。

SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0)

   num_of_days

FROM DUAL;

 

OR

 

SELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees;

如果你需要查询一些特定日期的天数,可以使用第二个查询语句。这个例子是计算员工入职的天数。

 

8.显示当前年份截止到上个月每个月份开始和结束的日期

这个是个很聪明的查询语句,用来显示当前年份每个月的开始和结束的日期,你可以使用这个进行一些类型的计算。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date,

       TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date

  FROM XMLTABLE (

          'for $i in 0 to xs:int(D) return $i'

          PASSING XMLELEMENT (

                     d,

                     FLOOR (

                        MONTHS_BETWEEN (

                           ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12),

                           SYSDATE)))

          COLUMNS i INTEGER PATH '.');

 

9.获取直到目前为止今天过去的秒数(从 00:00 开始算)

SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning

FROM DUAL;

 

10.获取今天剩下的秒数(直到 23:59:59 结束)

SELECT (TRUNC (SYSDATE 1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left

  FROM DUAL;

 

About Me

...............................................................................................................................

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/viewspace-2128764/

● 本文博客园地址:http://www.cnblogs.com/lhrbest/p/6083478.html

● 本文pdf版及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群:230161599     微信群:私聊

● 联系我请加QQ好友(642808185),注明添加缘由

● 于 2016-11-20 18:00 ~ 2016-11-20 21:00 在泰兴公寓完成

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

...............................................................................................................................

手机长按下图识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号:xiaomaimiaolhr,免费学习最实用的数据库技术。

 

   注意,在上面的第三个例子中,函数将不得不将31日往回调整为28日,已使结果对应新一月的最后一天。因为,1999年的2月份只有28天。在第二个例子中,则是从30往后调整为31,也同样是为了保持对应的最后一天。

SQL内置函数

•      ASCII

  ASCII函数返回你指向的字符串首字母的十进制表示代表码值。该返回值或许是ASCII码值,也或许不是。如果用户的数据库字符集是7位的ASCII值,那就得到一ASCII码值。该返回值总是以用户使用的字符集为基础的。

  ASCII(string)

  其中:string一个字符串。更多的时候就是一字符

  下面的例子解释了该函数的使用:

  ASCII(‘j’)=74

  ASCII(‘jeff’)=74

  从上面的第二个例子,我们不难看出,如果指定了一个多字符串,该函数将忽略除首字母外的所有字符。

SQL内置函数

•      ASIN

  ASIN函数返回一数值的反正弦值。结果以弧度表示,且在-PI/2到PI/2之间。

  其语法格式如下:

  ASIN(number)

  其中:number必须是-1与1之间的值

  下面的例子显示了1和-1的反正弦值:

  ASIN(1)=1.57

  ASIN(-1)=-1.57

SQL内置函数 

•      ATAN

  ATAN函数返回一数值的反正切值。结果以弧度表示,且在-PI/2到PI/2之间。

  其语法格式如下:

  ATAN(number)

  其中:number必须是-1与1之间的值

  下面的例子显示了1和-1 的反正切值:

  ATAN(1)=0.7854

  ATAN(-1)=-0.7854

SQL内置函数

•      ATAN2

  ATAN2函数返回两个数值的反正切值。结果以弧度表示。

  其语法格式如下:

  ATAN2(first,second)

  其中:

  first   第一个数

  second  第二个数

  函数ATAN2(first,second)与函数TAN(first/second)的功能是相同的。

  下面的例子显示了1和-1的反正切值:

  ATAN2(1,.5)=1.107

  TAN(1/.5)=1.107

SQL内置函数

•      BFILENAME

  BFILENAME函数返回一指向磁盘的物理文件的BFILE地址。

  其语法格式如下:

  BFILENAME(directory,filename)

  其中:

  directory     一个包括指定文件的目录路径字符串。        该目录通过命令CREATE DIRECTORY产生。         但它不是一个操作系统目录

  filename    指定目录中用户试图指定的文件名字。      该文件可以是预先不存在的文件

  下面的例子解释了BFILENAME函数的使用情况:

  BFILENAME(‘gif_dir’,’book_photo.gif’)

SQL内置函数

•       CEIL

   CEIL函数根据输入值返回一个数值。输入参数可以是非整数值,但返回结果则是大于等于输入参数的最小整数。

   其语法格式如下:

   CEIL(number)

   其中:

   number   任意数值,十进制数

   下面的例子解释了CEIL函数的使用情况:

   CEIL(5.1)=6

   CEIL(-5.1)=-5

   应注意,对于负数使用该函数的结果。大于等于-5。1的最小整数精确地讲应该是-5。这是数学上的情况,但首先也是记数中知觉上的感觉。

SQL内置函数

•       CHARTOROWID

   CHARTOROWID函数将一个字符串转换成rowid类型。

   其语法格式如下:

   CHARTOROWID(string)

   其中:

   string   待处理的字符串。该字符串应该能表示一个有效的        rowid型数据

   下面的例子解释了CHARTOROWID函数的使用情况:

   SQL>SELECT ROWID FORM dual;

   ROWID

   ----------

   AAAADCAABAAAAVUAAA

   SQL>SELECT * FORM dual

    2 WHERE ROWID=CHARTOROWID(‘AAAADCAABAAAAVUAAA’);

   D

   -

   X

SQL内置函数

•       CHR

  CHR函数就相应的数据库字符集,返回一个指定数值相应的字符。例如,给定一个ASCII值,使用CHR函数返回该数值表示的字符。

   其语法格式如下:

   CHR(integer[USING NCHAR_CS])

   其中:

   integer      在相应的字符集中,代表对应字符          的整数类型

   USING NCHAR_CS   指明相应的字符集

   下面的例子解释了CHR函数的使用情况:

   CHR(10)=a tab character

   CHR(65)=‘A’

   这些例子假设基于ASCII字符集

SQL内置函数

•      CONCAT

  CONCAT函数将两个输入字符串,组合成一个,并返回结果。

  其语法格式如下:

  CONCAT(string_1,string_2)

  其中:

  string_1 第一个字符串

  string_2 第二个字符串。该字符串将被拼接在第一      个字符串的尾部

  下面的例子解释了CONCAT函数的使用情况:

  CONCAT(‘This is’,’a test’)=‘This is a test’

SQL内置函数

•       CONVERT

  CONVERT函数把一字符串从一个字符集转换到另一字符集。

   其语法格式如下:

   CONVERT(string,dest_char_set[,source_char_set])

   其中:

   string       待转换的字符串

   dest_char_set 目标字符串

   source_char_set  源字符集。省略默认数据库字符集

   下面的例子解释了CONVERT函数的使用情况:

   CONVERT(‘Jonathan’,’WE8EBDIC37C’,’US7ASCII’)

   CONVERT(‘Jonathan’,’WE8ROMAN8’)

   第一个例子从US7ASCII字符集转换。第二个例子从默认数据库字符集转换。

SQL内置函数

•      COS

  COS函数返回一角度的余弦值。结果为弧度值。

  其语法格式如下:

  COS(angle)

  其中:

  angle   角度值,用弧度表示

  下面的例子解释了COS函数的使用情况:

  COS(90*3.14/180)=.000796

  COS(0)=1

SQL内置函数

•      COSH

  COSH函数返回一角度的双曲余弦值。

  其语法格式如下:

  COSH(angle)

  其中:

  angle   角度值,以弧度表示

  下面的例子解释了COSH函数的使用情况:

  COSH(0)=1

  COSH(90*3.14/180)=2.507

  将角度值转化成弧度值,可以参考COS函数的有关部门信息

SQL内置函数

•       DECODE(一)

   DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然,如果未能与任何一个实参序偶匹配成功,则函数也有默认的返回值。区别于SQL的其它函数,DECODE函数还能识别和操作空值。

   其具体的语法格式如下:

   DECODE(input_value,value,result[,value,result…][,default_result]);

   其中:

   input_value         试图处理的数值。DECODE函数将该数值与一系列的序偶相比             较,以决定最后的返回结果

   value      是一组成序偶的数值。如果输入数值与之匹配成功,则相应             的结果将被返回。对应一个空的返回值,可以使用关键字            NULL于之对应

   result     是一组成序偶的结果值

   default_result  未能与任何一序偶匹配成功时,函数返回的默认值

   下面的例子说明了,人们如何读取用户CHECKUP表SEAPARK中的BLOOD_TEST_FLAG列下的项目,作为DECODE函数的实参支持值。

   SELECT checkup_type,

     DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,

     ’Invalid’)

   FROM     checkup;

SQL内置函数

•       DECODE(二)

  这个SQL语句展示了DECODE函数的左右基础功能特征。函数的输入值时BLOOD_TEST_FLAG列的数据,如果该列的值是‘Y’,那么,函数返回‘YES’。如果该列的值是NULL,那么,函数返回‘None’。如果没有与任何一个序偶匹配成功,则表示该列当前值无效,函数返回‘Invalid’。

   在SQL疑难问题中,DECODE函数常常发挥非常灵活的作用。其中的一个技术就是为了某种目的可以将一个表的行转换成列。例如:

   SQL>SELECT TO_CHAR(TRUNC(BIRTH_DATE,’YEAR’),’YYYY’),

   2    COUNT(*)

   3 FROM AQUATIC_ANIMAL

   4 WHERE TO_CHAR(TRUNC(BIRTH_DATE,’YEAR’),’YYYY’)

   5    IN(‘1995’,’1996’,’1997’)

   6 GROUP BY TO_CHAR(TRUNC(BIRTH_DATE,’YEAR’),’YYYY’);

   TO_C COUNT(*)

   --- -------

   1995     1

   1996     3

   1997     1

  

SQL内置函数

  该例告诉我们,1995、1996和1997年各出生了多少动物。每一行显示不同的年。一旦希望将这些数值显示成3列,该如何处理?可以通过如表B-1所示的方法处理之。

SQL内置函数

•      EMPTY_BLOB

  EMPTY_BLOB函数返回一空的BLOB指针,该指针可以用在INSERT或UPDATE命令中,以初始化BLOB列项。

  其具体的语法格式如下:

  EMPTY_BLOB()

  该函数没有参数。下面的例子说明了如何使用该函数初始化BLOB列,并作为一个新行插入到表中。

  INSERT INTO some_table

  (blob_column)

  VALUES(EMPTY_BLOB());

SQL内置函数

•      EMPTY_CLOB

  EMPTY_CLOB函数与EMPTY_BLOB函数的功能类似,只不过对应CLOB而已。它返回一空的CLOB指针,该指针可以用在INSERT或UPDATE命令中,以初始化CLOB列。

  其具体的语法格式如下:

  EMPTY_CLOB()

  该函数没有参数。下面的例子说明了如何使用该函数初始化CLOB列,并作为一个新行插入到表中。

  INSERT INTO some_table

  (clob_column)

  VALUE(EMPTY_CLOB());


 

SQL内置函数

•      EXP

  EXP函数返回e的一个幂。在数学上e经常被用来表示一指定的特殊数值(一个十进制的无限数)2.178…,它是数学的基础。其具体的语法格式如下:

  EXP(exponent)

  其中:

  exponent 返回e的少次(exponent)幂

  下面是该函数的使用情况:

  EXP(1)=2.7182818

  EXP(3)=20.085537(2.7182818*2.7182818*2.7182818)

SQL内置函数

•       FLOOR

   FLOOR函数返回一个小于或等于给定十进制数的最大整数。该函数的工作机制与函数CEIL的情况极为相似,但却正好相反。

   其具体的语法格式如下:

   FLOOR(number)

   其中:

   number   任意数,包括十进制数

   下面是该函数的使用情况:

    FLOOR(5.1)=5

    FLOOR(-5.1)=-6

   为了理解函数FLOOR和函数CEIL在处理负数时的不同机制,可以比较函数FLOOR(5.1)和函数CEIL(-5.1)的执行结果。

SQL内置函数

•       GREATEST

   GREATEST函数返回一数值列表中的最高数值。可使用GREATEST函数处理数字数据和字符数据。

   其具体的语法格式如下:

   GREATEST(value,value,value,…)

   其中:

   value 数字数据或文本数据。一般地,所有数值的类型应该一致。        如果给定的数据类型相混,返回值将匹配第一个参数类型,       其余的所有参数自然被转换成对应的类型

   下面是该函数的使用情况:

   GREATEST(1,3,9,45,93,2,-100)=93

   GREATEST(’Jenny’,’Jeff’,’Ashley’)= ’Jenny’

   GREATEST(’11’,101)=‘11’

   第三个例子反映了参数类型相混时的函数处理情况。因为第一的数据类型是字符串,所以,Oracle将数字1转换成字符串‘1’,以匹配对应的数据类型。虽然在数值上101大于11,但如果都转换成字符串,则结果正好相反。这种情况下,‘11’大于‘101’,并且,也是函数的返回结果。


 

SQL内置函数

•       HEXTORAW

   函数HEXTORAW将一个由十六进制字符组成的字符串转换成一个raw数值。

   其具体的语法格式如下:

   HEXTORAW(string)

   其中:

   string   十六进制字符组成的字符串数据

SQL内置函数

•      INITCAP

  INITCAP函数接收一字符串,并转换该字符串中的所有单词,以使各单词均以大写字母开头。

  其的语法格式如下:

  INITCAP(string)

  其中:

  string  任意一VARCHAR2或CHAR型数据

  下面是该函数的使用情况。

  INITCAP(‘This is a test’=‘This is a test’

SQL内置函数

•       INSTR

   函数INSTR会告诉用户,其中的一个字符串(参数)是否在另一个字符串(也是参数)当中。

   其具体的语法格式如下:

   INSTR(string,substring[,start[,occurrence]])

   其中:

   string   待查询的字符串

   substring 正在搜索的字符串

   start 说明开始搜索的字符位置。默认值是1,就是说,搜       索将从字符串的第一个字符开始。如果,参数为负   则表示搜索的位置从右边开始计算,而不是默认的        从左边开始

   occurrence   指定试图搜索的子串的第几次出现,默认值是1,意       味着希望其首次出现

   该函数反馈一索引顺序值,在该位置发现了要搜索的子串。下面的例子指出了INSTR函数的使用情况:

   INSTR(‘AAABAABA’,’B’)=4

   INSTR(‘AAABAABA’,’B’,1,2)=7

SQL内置函数

•       INSTRB

   该函数与INSTR的功能极为类似,也将反馈其中的一个字符串(参数)是否在另一个字符串(也是参数)当中。只是该函数返回的是一字节索引位置,而不是一字符索引位置。其实,仅仅在多个字符集同时被使用时,这种不同才有意义。

   其具体的语法格式如下:

   INSTRB(string,substring[,start[,occurrence]])

   其中:

   string   待查询的字符串

   substring 正在搜索的字符串

   start 说明开始搜索的字符位置。默认值是1,就是说,搜索将从字       符串的第一个字符开始。如果,参数为负则表示搜索的位置从     右边开始计算,而不是默认的从左边开始

   occurrence   指定试图搜索的子串的第几次出现,默认值是1,意味着希望       其首次出现

   该函数反馈一索引顺序值,在该位置发现了要搜索的子串。下面的例子指出了INSTRB函数的使用情况:

   INSTRB(‘AAABAABA’,’B’)=4

   INSTRB(‘AAABAABA’,’B’,1,2)=7

SQL内置函数

•      LAST_DAY

  函数LAST_DAY返回实参数指定日期对应月份的最后一天。其具体的语法格式如下:

  LAST_DAY(date)

  其中:

  date 一日期数值

  下面是该函数的使用情况:

  LAST_DAY(TO_DATE(’29-Dec-1988’,’dd-mon-yyyy’))

    =’31-Dec-1988’

  LAST_DAY(TO_DATE(’1-Feb-2000’,’dd-mon-yyyy’))

    =’29-Feb-2000

SQL内置函数

•       LEAST

   LEAST函数返回参数给定的数值列表中的最小值。对数值数据或字符串数据均有效。

   其具体的语法格式如下:

   LEAST(value,value,value,…)

   其中:

   value 一数字或文本数值。尤其要注意的是所有的数据类        型应该一致。如果类型相混,返回值将匹配第一个   参数的数据类型,而且,其它所有的参数均被转换        成该参数的数据类型

   下面是该函数的使用情况:

   LEAST(1,3,9,45,93,2,-100)=-100

   LEAST(’Jenny’,’Jeff’,’Ashley’)=‘Ashley’

   LEAST(’110’,12)=‘110’

   第三个例子说明了,当参数的数据类型相混时函数的处理机制。因为第一个参数实字符串,所以,将数字1转换成字符串‘1’,一匹配对应的数据类型。虽然在数值上110大于12,但如果都转换成字符串,则结果正好相反。这种情况下,‘12’大于‘110’(一顺序为基础),这就是函数的返回结果。

SQL内置函数

•      LENGTH

  LENGTH函数返回字符串的长度。

  其具体的语法格式如下:

  LENGTH(string)

  其中:

  string  任意字符串

  下面是该函数的使用情况:

  LENGTH(’This is short’)=13

  LENGTH(’This is a bit longer’)=20

SQL内置函数

•      LENGTHB

  LENGTHB函数返回字符串的长度。但在多字符集同时使用时会有所区别。

  其具体的语法格式如下:

  LENGTHB(string)

  其中:

  string  任意字符串

  下面是该函数的使用情况:

  LENGTHB(’This is short’)=13

  LENGTHB(’This is a bit longer’)=20


 

SQL内置函数

•      LN

  函数LN返回一数值的自然对数。

  其具体的语法格式如下:

  LN(number)

  其中:

  number  大于0的任意数值

  下面是该函数的使用情况:

  LN(10)=2.3025851

  EXP(2.3025851)=10

  可见,将函数LN的结果作为参数,通过再调用函数EXP可得到函数LN的原参数值。

SQL内置函数

•      LOG

  LOG函数返回数值的非自然对数。

  其具体的语法格式如下:

  LOG(logbase,number)

  其中:

  logbase 除1和0以外的任意数值,是对数的底

  number  及对数的真数

  下面是该函数的使用情况:

  LOG(10,100)=2

  LOG(EXP(1),10)=2.3025851=LN(10)

  因为102等于100,所以,以10为底时100的对数是2。注意:LOG(e,x)=LN(x).可以使用EXP(1)得到e的值。

SQL内置函数

•      LOWER

  函数LOWER返回参数指定字符串的小写形式。

  其具体的语法格式如下:

  LOWER(atring)

  其中:

  string  任意VARCHAR2型或CHAR型的数值   

  下面是该函数的使用情况:

  LOWER(’This IS a Test’)=‘This is a test’

SQL内置函数

•      LPAD

  LPAD函数从左面填充指定的字符串。

  其具体的语法格式如下:

  LPAD(string,numchars[,padding])

  其中:

  string  任意VARCHAR2型或CHAR型的字符串

  numchars 返回字符串的字符个数

  padding 填充字符。是一可选项,默认为单个空格

  下面是该函数的使用情况:

  LPAD(’Jenny’,10)=‘Jenny’(5 leading spaces)

  LPAD(’Jenny’,10,’*’)=‘*****Jenny’

  LPAD(’Jenny’,10,’*!’)=‘*!*!*Jenny’

SQL内置函数

•      LTRIM

  LTRIM函数删除字符串左边的前缀字符。一般前缀字符总是空格。

  其具体的语法格式如下:

  LTRIM(string[,trimchars])

  其中:

  string  任意VARCHAR2型或CHAR型的数值

  trimchars   待删除的字符串

  下面是该函数的使用情况:

  LTRIM(’     Jeff’)=‘Jeff’(leading spaces removed)

  LTRIM(’*****Jeff’,’*’)=‘Jeff’

  LTRIM(’*!*!*Jeff’,’*!’)=‘Jeff’

SQL内置函数

•      MOD

  MOD函数返回一个数除以另一数的余数。

  其具体的语法格式如下:

  MOD(number,divisor)

  其中:

  number  任意数值

  divisor 任意数值。该函数计算number/divisor的余数

  下面是该函数的使用情况:

  MOD(14,12)=2(1400 hours=2:00 O’Clock)

  MOD(10,10)=0

  MOD(10,0)=10

  注意:如果除数为0则返回原来的数

SQL内置函数

•       MONTHS_BETWEEN

   MONTHS_BETWEEN函数返回两个日期之间的月份数

   其具体的语法格式如下:

   MONTHS_BETWEEN(date_1,date_2)

   其中:

   date_1   日期型数据

   date_2   一日期型数据

   如果两个参数代表的是某月中的同一日期,或它们军代表的某月中的最后一天,则该函数返回一整型值。否则,将返回一分数。另外,如果第一个参数代表的日期小于第二个参数代表的日期,则返回一负值。

   下面是该函数的使用情况:

   MONTHS_BETWEEN(TO_DATE(’29-Dec-1999’,’dd-mon-yyyy’),

    TO_DATE(’29-Dec-1988’,’dd-mon-yyyy’))=132

   MONTHS_BETWEEN(TO_DATE(’29-Dec-1999’,’dd-mon-yyyy’),

    TO_DATE(’24-Nov-1988’,’dd-mon-yyyy’))=133.16129

   在第一个例子中,两个参数均为某一月中的29号,所以返回一整型值(其间相差的月份数),在第二个例子中,因两个参数表示的日期不是同一天,所以返回一分数(以31天为基础计算处理)

SQL内置函数

•       NEW_TIME

   NEW_TIME函数将在时区间转换日期/时间数据。

   其具体的语法格式如下:

   NEW_TIME(date,oldzone,newzone)

   其中:

   date     一日期数值。在Oracle中,除其自己以外,所有日        期数值都有时间和记号这样的成分

   oldzone  代表时区的字符串。有效的时区如表所示。被推算        出的日期数值就在该时区当中

   newzone  也是代表时区的字符串。被从老时区到新时区转换的日期数值

   下面是该函数的使用情况:

   NEW_TIME(TO_DATE(’25-Dec-1999 8:00’,’dd-mom-yyyy HH:MI’),’EST’,’PST’)=’25-DEC-1999 05:00’

SQL内置函数

•       NEXT_DAY

   NEXT_DAY函数返回从实参日期开始,紧随其后的指定星期对应的日期。

   其具体的语法格式如下:

   NEXT_DAY(date,weekday)

   其中:

   date 一日期数据

   weekday  一字符串,说明某一天的星期几的名称。可以使用全称,也可以使用缩写,但对于用户的编程语言做的日期设置来说必须是有效的日期

   下面是该函数的使用情况:

   NEXT_DAY(TO_DATE(’24-Nov-1999’),’dd-mon-yyyy’,’FRIDAY’)

                =’26-Nov-1999’

   NEXT_DAY(TO_DATE(’24-Nov-1999’),’dd-mon-yyyy’,’WED’)

                =’01-Dec-1999’

   注意:在第二个例子当中,24-Nov-1999正好落在星期三:Wednesday。下一个’WED’自然就是1-Dec-1999,所以,函数NEXT-DAY返回的结果就是

   ’01-Dec-1999’

SQL内置函数

•       NVL

   NVL函数需要两个参数。如果第一个参数为空,则返回第二个参数。第二个参数表示是第一个参数的替换对象。

   其具体的语法格式如下:

   NVL(value,alternative)

   其中:

   value    一个可为空的值。它不为空的时候将作为返回值

   alternative      只要它不是空的就是将被返回的值

   下面是该函数处理数据库的某些列包括空值时的情况:

   SELECT emp_id,NVL(emp_name,’Name Missing!’)

   FROM emp_table;

   在该例中,如果雇员有名字,将通过该函数返回其名字。如果雇员的名字为空,将返回‘Name Missing!’

SQL内置函数

•       POWER

   函数POWER返回一个数值的指定次幂。

   其具体的语法格式如下:

   POWER(number,power)

   其中:

   number   任意数值

   power 幂次数。函数的结果其实即:number幂次。如果第        一个参数为负数,则该幂次数务必是一整数。其它   情况可为任意数值

   下面是该函数的使用情况:

   POWER(10,2)=100(10*10)

   POWER(10,3)=1000

   POWER(-10,3)=-1000

SQL内置函数

•       RAWTOHEX

   RAWTOHEX函数将一raw转换成十六进制字符表示的串值。

   其具体的语法格式如下:

   RAWTOHEX(raw_value)

   其中:

   raw_value    一RAW型数值

   下面是该函数的使用情况:

   SQL>CREATE TABLE xx(y raw(10));

   Table created.

   SQL>INSERT INTO xx VALUES(HEXTORAW(‘414243’));

   1 row created.

   SQL>SELECT RAWTOHEX(y) FORM xx;

   RAWTOHEX(Y)

   ------

   414243

SQL内置函数

•       REPLACE

   REPLACE函数在一字符串中搜索一指定的子串,并将其替换成另外一指定串。

   其具体的语法格式如下:

   REPLACE(string,substring[,replace_string])

   其中:

   string       被搜索字符串。在其中搜索待替换串

   substring    被搜索子串。所有找到的子串均被替换掉

   replace_string   替换结果子串。是一可选项。如果该参数被忽略,            则所有被搜索到的子串实质上均被删除

   下面是该函数的使用情况:

   REPLACE(’This is a test’,’is’,’was’)=‘Thwas was a test’

   REPLACE(’This is a test’,’is’)=‘Th a test’

SQL内置函数

•       ROUND(for dates)

  ROUND函数将一日期处理(舍入)成第二个参数指定的形式。

   其具体的语法格式如下:

   ROUND(date[,fmt])

   其中:

   date     一个日期数值

   fmt      是一个日期格式说明符。指定日期将被处理成该说明符指定的      形式。省略该参数,则指定日期将被处理到最近的一天

   下面是该函数的使用情况:

   ROUND(TO_DATE(’24-Nov-1999 08:00 pm’,’dd-mon-yyyy hh:mi am’))

        =’25-Nov-1999 12:00:00 am’

   ROUND(TO_DATE(’24-Nov-1999 08:37 pm’,’dd-mon-yyyy hh:mi am’),’hh’)

        =’24-Nov-1999 09:00:00 am’

   注意:这种处理过程可能意味着日期数值的改变。在第一个例子中,因为8:00 PM是紧邻第二天的一精确值,所以处理结果被转到下一天(25th)而不是当天(24th).

SQL内置函数


 

•       ROUND(for number)

  ROUND函数将一数值处理(舍入)成第二个参数指定的形式的十进制数。

   其具体的语法格式如下:

   ROUND(value,places)

   其中:

   value 试图转换的数值

   places   结果中的十进制数位置。必须是一正整数,但可能是一负数。如果是负数,数字就被精确地处理在从小数点其左数的十进制数

   下面是该函数的使用情况:

   ROUND(89,985,2)=89.90(Note that .005 is rounded up.)

   ROUND(89,985,-1)=90

   ROUND(89,985,-2)=100

SQL内置函数

•      ROWIDTOCHAR

  ROWIDTOCHAR函数将一行标志码(ROWID)转换成一字符串。

  其具体的语法格式如下:

  ROWIDTOCHAR(rowid)

  其中:

  rowid   一ROWID型数据

  下面是该函数的使用情况:

  SQL>SELECT ROWIDTOCHAR(ROWID) FROM dual;

  ROWIDTOCHAR(ROWID)

  ----------

  AAAADCAABAAAAVUAAA

SQL内置函数

•      RPAD

  RPAD函数在字符串的后面追加若干字符。

  其具体的语法格式如下:

  RPAD(string,numchars[,padding])

  其中:

  string  任意VARCHAR2型或CHAR型的数据

  numchars 函数结果串的长度

  padding 追加字符,单个的。是可选项,默认为空格

  下面是该函数的使用情况:

  RPAD(‘Jenny’,10)=‘Jenny     ’(5 trailing spaces)

  RPAD(‘Jenny’,10,’*’)=‘Jenny*****’

  RPAD(‘Jenny’,10,’*!’)=‘Jenny*!*!*’

SQL内置函数


 

•       RTRIM

   RTRIM函数压缩掉串右面的尾随字符,常常是从指定字符串(第一参数)中压缩空格。

   其具体的语法格式如下:

   RTRIM(string[,trimchars])

   其中:

   string   任意VARCHAR2型或CHAR型的数据

   trimchars 准备压缩掉的字符(串)

   下面是该函数的使用情况:

   RTRIM(‘Jeff     ’)= ‘Jeff’(trailing spaces removed)

   RTRIM(‘Jenny*****’)= ‘Jeff’

   RTRIM(‘Jenny*!*!*’)= ‘Jeff’

SQL内置函数

•       SIGN

  SIGN函数返回实参的符号。对于负数返回-1,0返回0,正数返回 1。

   其具体的语法格式如下:

   SIGN(value)

   其中:

   value 输入的实参,你试图返回它的符号

   下面是该函数的使用情况:

   SIGN(-100)=-1

   SIGN(0)=0

   SIGN(100)=1

   可以使用该函数和DECODE函数,根据一个数值的正、负或0,指明表达式间的不同。

SQL内置函数

•      SIN

  SIN函数返回一个角度的正弦值。

  其具体的语法格式如下:

  SIN(angle)

  其中:

  angle   待求解其正弦的角度。是一角度表达式。可参考函数COS了解从弧度到角度的转换

  下面是该函数的使用情况:

  SIN(90*3.1415926/180)=1

  SIN(0)=0

SQL内置函数

•       SOUNDEX

   SOUNDEX函数根据以下规则返回一个字符串的发音描述。

   1、保留字符串的首字母

   2、删除下面的所有字母:a、e、h、I、o、u、w和y。

   3、首字母后面的任一字母均按如下规则匹配于某一数字:

    b,f,p,v=1

    c,g,j,k,q,s,x,z=2

    d,t=3

    I=4

    m,n=5

    r=6

   4、结果被缩写并转换成一个4位的数字

   其具体的语法格式如下:

   SOUNDEX(string)

   其中:

   string   任意字符串

   下面是该函数的使用情况:

   SOUNDEX(‘Gennick’)=520 SOUNDEX(‘Genick’)=520 SOUNDEX(‘Genyk’)=520


 

SQL内置函数

•      SORT

  SORT函数返回一数字的算术平方根。

  其具体的语法格式如下:

  SORT(number)

  其中:

  number  试图求解其平方根的数值,非负数

  下面是该函数的使用情况:

  SORT(100)=10(because 10*10-100)

  SORT(100)=3.1622777

SQL内置函数

•       SUBSTR

   SUBSTR返回一字符串得指定字串。

   其具体的语法格式如下:

   SUBSTR(string,start,length)

   其中:

   string   任意字符串

   start 字串的开始位置。第一个位置总匹配为1。该参数可以为           负数,这时从给定字符串的右面数起

   length   待匹配字串的长度。是一可选项,忽略它则匹配整个字符串

   下面是该函数的使用情况:

   SUBSTR(’JennyJeffJonathan’,6,4)=‘Jeff’

   SUBSTR(’JennyJeffJonathan’,-12,4)=‘Jeff’

   SUBSTR(’JennyJeffJonathan’,-8)=‘Jonathan’

SQL内置函数


 

•       SUBSTRB

   SUBSTRB返回一字符串的指定部分。该函数与SUBSTR极为类似,只是该函数的start和length两参数均以字节为单位,而不是根据字符。这种区别只有在使用多种不同的字符集时才有意义。

   其具体的语法格式如下:

   SUBSTRB(string,start[,length])

   其中:

   string   任意字符串

   start 字串的开始位置。第一个位置总匹配为1。该参数可       以为负数,这时从给定字符串的右面数起

   length   待匹配字串的长度。是一可选项,忽略它则匹配整        个字符串

   下面是该函数的使用情况:

   SUBSTRB(’JennyJeffJonathan’,6,4)=‘Jeff’

   SUBSTRB(’JennyJeffJonathan’,-12,4)=‘Jeff’

   SUBSTRB(’JennyJeffJonathan’,-8)=‘Jonathan’

SQL内置函数

•      SYSDATE

  SYSDATE函数返回当前日期时间,一直到秒。

  其具体的语法格式如下:

  SYSDATE

  SYSDATE没有参数。

  下面是该函数的使用情况:

  SQL>SELECT SYSDATE FROM DUAL;

  SYSDATE

  -------

  24-Nov-1999 06:45:00 pm

  当然,结果会由于用户调用该函数的时机不同而不同。

SQL内置函数


 

•      TAN

  TAN函数返回一个角度的正切值。

  其具体的语法格式如下:

  TAN(angle)

  其中:

  angle   用弧度表示。可参考函数COS了解从弧度到     角度的转换

  下面是该函数的使用情况:

  TAN(225*3.1415926535/180)=1

  TAN(0)=0

SQL内置函数


 

•      TANH

  TANH函数返回一个角度的十六进制正切值。

  其具体的语法格式如下:

  TANH(angle)

  其中:

  angle   用弧度表示。可参考函数COS了解从弧度到     角度的转换

  下面是该函数的使用情况:

  TANH(225*3.1415926535/180)=.99922389

  TANH(0)=0

SQL内置函数

•       TO_CHAR(for dates)

   TO_CHAR函数将一个日期型数据转换成一个字符型数据。

   其具体的语法格式如下:

   TO_CHAR(date[,fmt[,’NLS_DATE_LANGUAGE=language’]])

   其中:

   date 任一DATE型数据

   fmt      一个日期格式说明符,它控制了代表日期型数据的字符型结果

   language   使用的语言,它对日期拼读有影响,如在使用月份、日子、星期      等时

   下面是该函数的使用情况:

   SQL>SELECT TO_CHAR(SYSDATE,’dd-Mon-yyyy’)

    FROM dual;

   TO_CHAR(SYS

   -----

   24-Nov-1999

   SQL>SELECT TO_CHAR(SYSDATE,’Month’,’NLS_DATE_LANGUAGE=Spanish’)

    FROM dual;

   TO_CHAR(SY

   -----

   November

  

SQL内置函数

•       TO_CHAR(for numbers)

   TO_CHAR函数将一个数值型数据转换成一个字符型数据。

   其具体的语法格式如下:

   TO_CHAR(date[,fmt[,nlsparams]])

   其中:

   date     任一数值型数据

   fmt      一个数字格式说明符,它控制了代表日期型数据的字符型结果

   nlsparams 数字的语言特征。该参数是由若干下列内容组成的字符串  :      NLS_NUMERIC_CHARACTERS=“dg”,NLS_CURRENCY=“currchar”,及        NLS_TERRITORY=territory

        d和g表示十进制符号和分隔符(在美国为逗号)。货币数值(在        美国的一个($)符号)常用来表示金钱数值。这两种数值均必     须放在引号中

   下面是该函数的使用情况:

   TO_CHAR(123.45)=‘123.45’

   TO_CHAR(123456.78,’$999,999.99’)=‘$123,456.78’

   TO_CHAR(123456.78,’L999G999D999’,

     ’NLS_NUMERIC_CHARACTERS=“,.”NLS_CURRENCY=“!”’)

   =‘!123.456,78’

   注意:NLS参数没有什么效果,必须使用L、G和D通行符号、分隔符、小数点等等。

SQL内置函数


 

•       TO_DATE

   TO_DATE函数将一个字符型数据转换成一个日期型数据。

   其具体的语法格式如下:

   TO_DATE(string [,fmt[,’NLS_DATE_LANGUAGE=language’]])

   其中:

   string   待转换的字符串

   fmt      一个日期格式说明符,它控制了代表了转换字符时的处理方式,也是一可选项,忽略它,则使用数据库的默认方式

   language   使用的语言,也是可选项,对日期拼读有影响。

   下面是该函数的使用情况:

   第一个TO_DATE函数将给定字符串解释成8-Nov-1915;

   下列显示了TO_DATE 函数的用法:

   TO_DATE(’11/08/1915’,’MM/DD/YY’)

   下一次调用则将给定字符串解释成11-Nov-1915;

   TO_DATE(’11/08/1915’,’DD/MM/YY’)

   最后一个例子将给定字符串解释成11-Nov-1915;

   TO_DATE(’ 11-Nov-1915’,’MM/DD/YYYY’)

SQL内置函数

•       TO_LOB

   TO_LOB函数将一个LONG或LONG RAW型数据转换成下列类型:CLOB、BLOB或NCLOB。当仅仅需要在INSERT语句中用LONG型数据代替LOB型数据时,可以使用该函数。

   其具体的语法格式如下:

   TO_LOB(long_value)

   其中:

   long_value       待转换为CLOB或NCLOB等类型数据的一个             LONG或LONG RAW型数据。LONG RAW型数据转      换成BLOB型数据

   下面是该函数的使用情况:

   INSERT INTO new_table(clob_value)

    SELECT TO_LOB(long_value)

    FROM old_table

SQL内置函数

•       TO_NUMBER

   TO_NUMBER函数将一字符串转换成一数字。

   其具体的语法格式如下:

   TO_NUMBER(string[,fmt[,’nlsparams’]])

   其中:

   string   待转换的字符串

   fmt      一数字格式说明符号。它将控制转换过程中的处理方式

   nlsparams 语言特征值。该字符串参数的组成部分有以下内容:      NLS_NUMERIC_CHARACTERS=“dg”,NLS_CURRENCY=“currchar”,      及NLS_TERRITORY=territory

        d和g表示十进制符号和分隔符(在美国为逗号)。货币数值        (在美国的一个($)符号)常用来表示金钱数值。这两种     数值均必须放在引号中

   下面是该函数的使用情况:

   第一个TO_NUMBER函数将给定字符串解释成123.45;

   TO_NUMBER(‘123.45’)   

   下一次调用则将给定字符串解释成123,456.78

   TO_NUMBER(‘$123,456.78’,’$999,999.99’)

SQL内置函数

•       TRIM

   TRIM函数将字符串的前缀(或尾随)字符删除。

   其具体的语法格式如下:

   TRIM([LEADING|TRAILING|BOTH][trimchar FROM] string)

   其中:

   LEADING  指明仅仅将字符串的前缀字符删除

   TRAILING 指明仅仅将字符串的尾随字符删除

   BOTH     指明既删除前缀字符,也删除尾随字符。这也是默认方式

   string   任意一待处理字符串

   trimchar 可选项。指明试图删除什么字符,默认被删除的字符是空格

   下面是该函数的使用情况:

   TRIM(’   Ashley   ’)=‘Ashley’

   TRIM(LEADING ’*’ FROM’***Ashley***’)=‘Ashley***’

SQL内置函数

•      TRUNC(for dates)

  TRUNC函数为指定元素而截去的日期值。

  其具体的语法格式如下:

  TRUNC(date[,fmt])

  其中:

  date 一个日期值

  fmt     日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去

  下面是该函数的使用情况:

  TRUNC(TO_DATE(’24-Nov-1999 08:00 pm’,’dd-mon-yyyy hh:mi am’))

    =’24-Nov-1999 12:00:00 am’

   TRUNC(TO_DATE(’24-Nov-1999 08:37 pm’,’dd-mon-yyyy hh:mi am’,’hh’)) =’24-Nov-1999 08:00:00 am’

SQL内置函数

•       TRUNC(for number)

   TRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,只是该函数不对指定小数前或后的部分做相应舍入选择处理,而统统截去。

   其具体的语法格式如下

   TRUNC(number[,decimals])

   其中:

   number   待做截取处理的数值

   decimals 指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分

   下面是该函数的使用情况:

   TRUNC(89.985,2)=89.98

   TRUNC(89.985)=89

   TRUNC(89.985,-1)=80

   注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截去,即均以0记。

SQL内置函数

•      UID

  UID函数返回一个整数值。对于当前数据库用户来它是唯一的。

  其具体的语法格式如下:

  UID

  该函数没有参数

  下面是该函数的使用情况:

  SQL>SELECT UID FROM dual;

     UID

  ------

      5

  该数值来源于V$SESSION中的USER#列。

SQL内置函数

•      UPPER

  UPPER函数间返回字符串的大写形式。

  其具体的语法格式如下:

  UPPER(string)

  其中:

  string  任意VARCHAR2或CHAR型字符串

  下面是该函数的使用情况:

  UPPER(’THIS IS a Test’)=‘THIS IS A TEST’

SQL内置函数

•      USER

  USER返回当前用户名,没有参数。

  其具体的语法格式如下:

  USER

  下面是该函数的使用情况:

  SQL>SELECT USER FROM dual;

  USER

  --------

  SYSTEM

    当任一内嵌的函数或程序调用该函数时,该函数总返回该调用者的名字。

TAG标签:
版权声明:本文由澳门新葡8455手机版发布于新京葡娱乐场网址,转载请注明出处:新京葡娱乐场网址Oracle内置函数大全,SQL内置函