ITPub博客

首页 > Linux操作系统 > Linux操作系统 > (转)PLS_INTEGER类型与timestamp类型、date、及时间函数

(转)PLS_INTEGER类型与timestamp类型、date、及时间函数

原创 Linux操作系统 作者:mahanso 时间:2011-09-01 12:00:03 0 删除 编辑
一、 PLS_INTEGER类型

   今天看到了这个 PLS_INTEGER,觉得生疏,网上搜了下,原来这个类型的好处多多啊。因此将这些转了过来,收藏了。

PLS_INTEGER可以存储一个有符号的整形值,其精度范围和BINARY_INTEGER一样,是:-2^31~2^31。
PLS_INTEGER和NUMBER比较起来,其优点是:
1).占有较少的存储空间;
2).可以直接进行算术运算(在NUMBER上不能直接进行算术运算,如果要计算,NUMBER必须先被转换成二进制)。所以在进行算术的时候PLS_INTEGER比NUMBER和BINARY_INTEGER快一些。

PLS_INTEGER和BINARY_INTEGER区别:
PLS_INTEGER进行的运算发生溢出的时候,会触发异常。但是当BINARY_INTEGER运算发生溢出时,如果可以指派给一个NUMBER变量(没有超出NUMBER的精度范围)的话就不会触发异常。

二、date类型使用

而date类型相减只得到天数
SQL> SELECT sysdate-(sysdate-1) FROM dual;
SYSDATE-(SYSDATE-1)
-------------------
                  1 
 做个试验:
create table t2 (d1 date,d2 date) ;--创建一个表
INSERT INTO T2 VALUES (SYSDATE, SYSDATE + 1.1234); --插入一条数据

SQL> SELECT D1, D2, D2 - D1, CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP) FROM T2;
D1          D2               D2-D1                  CAST(D2ASTIMESTAMP)-CAST(D1AST
----------- ----------- ---------- ---------------------------------------
2010-12-2 1 2010-12-3 1 1.12340277    +000000001 02:57:42.000000 

date类型相减转换为天、时分秒
SQL> SELECT TO_NUMBER(SUBSTR(D21, 1, INSTR(D21, ' '))) D,
  2             SUBSTR(D21, INSTR(D21, ' ') + 1, 2) H,
  3             SUBSTR(D21, INSTR(D21, ' ') + 4, 2) M,
  4             SUBSTR(D21, INSTR(D21, ' ') + 7, 2) S
  5      FROM (SELECT CAST(D2 AS TIMESTAMP) - CAST(D1 AS TIMESTAMP) D21
  6             FROM T2) ;
         D H    M    S
---------- ---- ---- ----
         1 02   57   42 

--两个date日期相减
select dt2 - dt1,
       months_between(dt2, dt1) months_btwn,
       numtodsinterval(dt2 - dt1, 'day') days,
       numtoyminterval(months_between(dt2, dt1), 'month') years_months
  from (select to_date('29-2-2000 01:02:03',
                            'dd-mm-yyyy hh24:mi:ss') dt1,
               to_date('15-3-2001 11:22:33',
                            'dd-mm-yyyy hh24:mi:ss') dt2
          from dual);

--转换为年,天时分秒
select numtoyminterval(months_between(dt2, dt1), 'month') years_months,
       numtodsinterval(dt2 -
                       add_months(dt1, trunc(months_between(dt2, dt1))),
                       'day') days_hours
  from (select to_date('29-2-2000 01:02:03', 'dd-mm-yyyy hh24:mi:ss') dt1,
               to_date('15-3-2001 11:22:33', 'dd-mm-yyyy hh24:mi:ss') dt2
          from dual);

三、timestamp类型

timestamp 语法:timestamp(n)
--当n=0时,与date长度一样
create table t (dt date,ts timestamp(0));
insert into t values (sysdate,systimestamp);
SQL> select dump(dt,10) dump,dump(ts,10) dump from t; 
DUMP                                             DUMP
------------------------------------------   ---------------------------------------
Typ=12 Len=7: 120,110,12,4,14,10,10          Typ=180 Len=7: 120,110,12,4,14,10,10

--n为9
create table t (dt date,ts timestamp(9));
insert into t values (sysdate,systimestamp);
SQL> select dump(dt,10) dump,dump(ts,10) dump from t; 
DUMP                                       DUMP
-----------------------------------  --------------------------------------------------------
Typ=12 Len=7: 120,110,12,4,14,7,41        Typ=180 Len=11: 120,110,12,4,14,7,41,11,37,100,192

SQL> select * from t;
DT          TS
----------- -----------------------------------
2010-12-4 1 04-12月-10 01.54.09.625000000 下午  --n为9即秒后面保留的位数(625000000)

select dump(ts,16) dump from t;
Typ=180 Len=11: 78,6e,c,4,e,37,a,25,40,be,40
SQL> select to_number('2540be40','xxxxxxxx') from dual;
 
TO_NUMBER('2540BE40','XXXXXXXX')--将后四个字节转换为十进制
------------------------------
       625000000                --后面四个字节就是秒小数点后面的数字
       
--timestamp 类型包括了所有DATE数据类型的年月日时分秒的信息,而且包括了小数秒的信息
SQL> SELECT sysdate,systimestamp FROM dual;

SYSDATE     SYSTIMESTAMP
----------- ---------------------------------------
2011-3-4 15 04-3月 -11 03.18.16.625000 下午 +08:00

timestamp直接进行加减时会隐式的转为date型
SQL> select systimestamp ts,systimestamp+1 dt from dual;
 
TS                                                 DT
--------------------------------------------  -----------
04-12月-10 10.40.00.265000 上午 +08:00         2010-12-5 1

解决隐式转换问题(仍保持timestamp类型)
SQL> select systimestamp ts,systimestamp+numtodsinterval(1,'day') dt from dual;
 
TS                                                       DT
-------------------------------------------- ----------------------------------------------
04-12月-10 10.42.46.515000 上午 +08:00             05-12月-10 10.42.46.515000000 上午 +08:00

两个timestamp相减
SQL> select dt2 - dt1
  2    from (select to_timestamp('29-2-2000 01:02:03.122000',
  3                              'dd-mm-yyyy hh24:mi:ss.ff') dt1,
  4                 to_timestamp('15-3-2001 11:22:33.000000',
  5                              'dd-mm-yyyy hh24:mi:ss.ff') dt2
  6            from dual);
 
DT2-DT1
---------------------------------------
+000000380 10:20:29.878000000


将其转换为具体的年、天时分秒
SQL> select numtoyminterval(months_between(dt2, dt1), 'month') years_months,
  2         dt2 - add_months(dt1, trunc(months_between(dt2, dt1))) days_hours
  3    from (select to_timestamp('29-2-2000 01:02:03.122000',
  4                              'dd-mm-yyyy hh24:mi:ss.ff') dt1,
  5                 to_timestamp('15-3-2001 11:22:33.000000',
  6                              'dd-mm-yyyy hh24:mi:ss.ff') dt2
  7            from dual);
 
YEARS_MONTHS                            DAYS_HOURS
--------------------------------------- ---------------------------------------
+000000001-00                           +000000015 10:20:30.000000000


关于numtodsinterval 参考文献########################################

Oracle/PLSQL: NumToDSInterval Function


In Oracle/PLSQL, the numtodsinterval function converts a number to an INTERVAL DAY TO SECOND literal.

The syntax for the numtodsinterval function is:

numtodsinterval( number, expression )

number is the number to convert to an interval.

expression is the unit. It must be one of the following values: DAY, HOUR, MINUTE, or SECOND.


Applies To:

  • Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

numtodsinterval(150, 'DAY')would return '+000000150'
numtodsinterval(1500, 'HOUR')would return '+000000062'
numtodsinterval(15000, 'MINUTE')would return '+000000010'
numtodsinterval(150000, 'SECOND')would return '+000000001'

###############################################
 注:to_char函数支持date和timestamp,但是trunc却不支持TIMESTAMP数据类型 
 SQL> select trunc(to_date('20100908','yyyymmdd') - to_date('20100907','yyyymmdd')) m from dual; 
         M
----------
         1
--两个相减的timestamp类型不能用trunc
SQL> select trunc(to_timestamp('20100908','yyyymmdd') - to_timestamp('20100907','yyyymmdd')) m from dual;
 
select trunc(to_timestamp('20100908','yyyymmdd') - to_timestamp('20100907','yyyymmdd')) m from dual
 
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 INTERVAL DAY TO SECOND


 to_char的使用:
 SQL> SELECT sysdate,systimestamp,to_char(systimestamp,'yyyymmdd hh24:mi:ssxff3') FROM dual;
 
SYSDATE     SYSTIMESTAMP                                             TO_CHAR(SYSTIMESTAMP,'YYYYMMDD'
----------- -------------------------------------------------------- ------------------------------
2010-12-2 1 02-12月-10 02.06.34.046000 下午 +08:00                       20101202 14:06:34.046

对于 timestamp类型的数据直接相减可以精确到秒以后
SQL> select systimestamp-systimestamp from dual;
SYSTIMESTAMP-SYSTIMESTAMP
---------------------------------------
+000000000 00:00:00.000000
        
四、interval year to month 类型的使用

interval year to month 可以用numtoyminterval或to_yminterval函数替代
SQL> select numtoyminterval(5,'year') + numtoyminterval(2,'month') from dual;
 
NUMTOYMINTERVAL(5,'YEAR')+NUMT
---------------------------------------
+000000005-02

SQL> select numtoyminterval(5*12+2,'month') from dual;
 
NUMTOYMINTERVAL(5*12+2,'MONTH'
---------------------------------------
+000000005-02

SQL> select to_yminterval('5-2') from dual;
 
TO_YMINTERVAL('5-2')
---------------------------------------
+000000005-02

SQL> select interval '5-2' year to month from dual;
 
INTERVAL'5-2'YEARTOMONTH
---------------------------------------
+05-02


五、interval day to second 类型的使用

interval day to second 可以用numtodsinterval或to_dsinterval替换使用
SQL> select numtodsinterval(10, 'day') + numtodsinterval(2, 'hour') +
  2         numtodsinterval(3, 'minute') + numtodsinterval(2.3312, 'second')

  3    from dual;
 
NUMTODSINTERVAL(10,'DAY')+NUMT
---------------------------------------
+000000010 02:03:02.331200000

SQL> select numtodsinterval(10*24*60*60+2*3600+3*60+2.3312, 'second') from dual;
 
NUMTODSINTERVAL(10*24*60*60+2*
---------------------------------------
+000000010 02:03:02.331200000

SQL> select to_dsinterval('10 02:03:02.3312') from dual;
 
TO_DSINTERVAL('1002:03:02.3312'
---------------------------------------
+000000010 02:03:02.331200000

SQL> select interval '10 02:03:02.3312' day to second from dual;
 
INTERVAL'1002:03:02.3312'DAYTO
---------------------------------------
+10 02:03:02.331200

六、时间函数

ADD_MONTHS 函数
ADD_MONTHS函数将一个日期上加上一指定的月份数,所以,日期中的日将是不变的。然而,如果开始日期是某月的最后一天,那么,结果将会调整以使
返回值仍对应新的一月的最后一天。如果,结果月份的天数比开始月份的天数少,那么,也会向回调整以适应有效日期
select ADD_MONTHS(TO_DATE('15-11-1961','dd-mm-yyyy'),1) from dual; --1961-12-15
select ADD_MONTHS(TO_DATE('30-11-1961','dd-mm-yyyy'),1) from dual; --1961-12-31
select ADD_MONTHS(TO_DATE('31-1-1999','dd-mm-yyyy'),1) from dual;--1999-2-28

MONTHS_BETWEEN 函数
MONTHS_BETWEEN (x, y)用于计算x和y之间有几个月。如果x在日历中比y早,那么MONTHS_BETWEEN()就返回一个负数。
                   当x 和 y 之间的月份之差不是整月的时候,可以采用小数表示

NUMTODSINTERVAL()函数
NUMTODSINTERVAL(x, interval_unit)用于将以interval_unit指定的值为单位的数字x转换为一个INTERVAL DAY TO SECOND类型,
                                 interval_unit参数可以设置为DAY、 HOUR、MINUTE或SECOND。

NUMTOYMINTERVAL()函数
NUMTOYMINTERVAL(x, interval_unit)用于将以interval_unit指定的值为单位的数字x转换为一个INTERVAL YEAR TO MONTH类型,
                                 interval_unit参数可以设置为YEAR或MONTH。




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12272958/viewspace-706415/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-10-18

  • 博文量
    160
  • 访问量
    600569