oracle ÖеÄINTERVAL º¯ÊýÏê½â
INTERVAL YEAR TO MONTHÊý¾ÝÀàÐÍ
OracleÓï·¨:
INTERVAL 'integer [- integer]' {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]
¸ÃÊý¾ÝÀàÐͳ£ÓÃÀ´±íʾһ¶Îʱ¼ä²î, ×¢Òâʱ¼ä²îÖ»¾«È·µ½ÄêºÍÔÂ. precisionΪÄê»òÔµľ«È·Óò, ÓÐЧ·¶Î§ÊÇ0µ½9, ĬÈÏֵΪ2.
eg:
INTERVAL '123-2' YEAR(3) TO MONTH
±íʾ: 123Äê2¸öÔÂ, "YEAR(3)" ±íʾÄêµÄ¾«¶ÈΪ3, ¿É¼û"123"¸ÕºÃΪ3ΪÓÐЧÊýÖµ, Èç¹û¸Ã´¦YEAR(n), n<3¾Í»á³ö´í, ×¢ÒâĬÈÏÊÇ2.
INTERVAL '123' YEAR(3)
±íʾ: 123Äê0¸öÔÂ
INTERVAL '300' MONTH(3)
±íʾ: 300¸öÔÂ, ×¢Òâ¸Ã´¦MONTHµÄ¾«¶ÈÊÇ3°¡.
INTERVAL '4' YEAR
±íʾ: 4Äê, ͬ INTERVAL '4-0' YEAR TO MONTH ÊÇÒ»ÑùµÄ
INTERVAL '50' MONTH
±íʾ: 50¸öÔÂ, ͬ INTERVAL '4-2' YEAR TO MONTH ÊÇÒ»Ñù
INTERVAL '123' YEAR
±íʾ: ¸Ã´¦±íʾÓдíÎó, 123¾«¶ÈÊÇ3ÁË, µ«ÏµÍ³Ä¬ÈÏÊÇ2, ËùÒԸô¦Ó¦¸Ãд³É INTERVAL '123' YEAR(3) »ò"3"¸Ä³É´óÓÚ3СÓÚµÈÓÚ9µÄÊýÖµ¶¼¿ÉÒÔµÄ
INTERVAL '5-3' YEAR TO MONTH + INTERVAL '20' MONTH =
INTERVAL '6-11' YEAR TO MONTH
±íʾ: 5Äê3¸öÔ + 20¸öÔ = 6Äê11¸öÔÂ
Óë¸ÃÀàÐÍÏà¹ØµÄº¯Êý:
NUMTODSINTERVAL(n, 'interval_unit')
½«nת»»³Éinterval_unitËùÖ¸¶¨µÄÖµ, interval_unit¿ÉÒÔΪ: DAY, HOUR, MINUTE, SECOND
×¢Òâ¸Ãº¯Êý²»¿ÉÒÔת»»³ÉYEARºÍMONTHµÄ.
NUMTOYMINTERVAL(n, 'interval_unit')
interval_unit¿ÉÒÔΪ: YEAR, MONTH
eg: (Oracle Version 9204, RedHat Linux 9.0)
SQL> select numtodsinterval(100,'DAY') from dual;
NUMTODSINTERVAL(100,'DAY')
---------------------------------------------------------------------------
+000000100 00:00:00.000000000 &nb
Ïà¹ØÎĵµ£º
oracle´´½¨±í¿Õ¼ä£¬´´½¨Óû§(ת)
¹Ø¼ü×Ö: oracle ±í¿Õ¼ä Óû§
//´´½¨ÁÙʱ±í¿Õ¼ä
create temporary tablespace test_temp
tempfile 'E:\oracle\product\10.2.0\oradata\testserver\test_temp01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
//´´½¨Êý¾Ý±í¿Õ¼ä
create ta ......
³éÏóÊý¾ÝÀàÐÍ£¬ÈÃOracleÊý¾Ý¿âÊý¾Ý¹ÜÀí¸ü¼ÓÈÝÒ×
×÷Õß: Victor, ¡¡³ö´¦:ITר¼ÒÍø,¡¡ÔðÈαà¼: °ü´ºÁÖ,¡¡
2009-02-04 10:10
¡¡¡¡ÐÕÃû£¬Æäʵ°üÀ¨Á½¸ö²¿·Ö£¬·Ö±ðΪÐÕÓëÃû¡£ÔÚûÓгéÏóÊý¾ÝÀàÐÍ֮ǰ£¬ÔÚÊý¾Ý¿âÉè¼ÆµÄʱºò£¬ÍùÍùÐèÒªÀûÓÃÁ½¸ö×Ö¶ÎÀ´±£´æÕâ¸öÐÕÃûÄÚÈÝ£¬ÈçFirst_nameÓëLast_nameµÈµÈ¡£ÕâÖ÷ÒªÊÇÒòΪÔںܶàʱºò£¬Ç°Ì¨ ......
2¡¢ ³éÏóÊý¾ÝµÄÒýÓá£
¡¡¡¡µ±½¨Á¢ºÃ³éÏóÊý¾ÝÀàÐ͵Äʱºò£¬Êý¾Ý¿â¹ÜÀíÔ±¾Í¿ÉÒÔÏò»ù±¾Êý¾ÝÀàÐÍÄÇÑù£¬ÒýÓóéÏóÊý¾ÝÀàÐÍ£¬°üÀ¨ÔÚ½¨Á¢±í»òÕß¶ÔÏà¹Ø×ֶνøÐи³ÖµµÄʱºò£¬¶¼Õâô·½±ã¡£
¡¡¡¡ÈçÔÚ½¨Á¢±íµÄʱºò£¬¿ÉÒÔ°ÑÕâ¸ö³éÏóÊý¾ÝÀàÐÍÖ±½Ó¸úij¸ö×ֶιØÁªÆðÀ´¡£Èç
¡¡¡¡Create Table USERS
¡¡¡¡(F_Adress Adress );
¡¡¡ ......
·ÖÒ³²éѯ¸ñʽ£º
SELECT * from
(
SELECT A.*, ROWNUM RN
from (SELECT * from TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
ÆäÖÐ×îÄÚ²ãµÄ²éѯSELECT * from TABLE_NAME±íʾ²»½øÐзҳµÄÔʼ²éѯÓï¾ä¡£ROWNUM <= 40ºÍRN >= 21¿ØÖÆ·ÖÒ³²éѯµÄÿҳµÄ·¶Î§¡£
ÉÏÃæ¸ø³öµÄÕâ¸ö·ÖÒ³²éѯÓï¾ä£¬ÔÚ´ó¶à ......
Ò»¡¢ ³£ÓÃÈÕÆÚÊý¾Ý¸ñʽ
1.Y»òYY»òYYY ÄêµÄ×îºóһ룬Á½Î»»òÈýλ
SQL> Select to_char(sysdate,'Y') from dual;
TO_CHAR(SYSDATE,'Y')
--------------------
7
SQL> Select to_char(sysdate,'YY') from dual;
TO_CHAR(SYSDATE,'YY')
---------------------
07
SQL> Select to_char(sysdate,'YYY') from ......