Oracle ÈÕÆÚʱ¼äº¯ÊýµÄÓ÷¨
TO_DATE¸ñʽ
Day:
dd number 12
dy abbreviated fri
day spelled out friday
ddspth spelled out, ordinal twelfth
Month:
mm number 03
mon abbreviated mar
month spelled out march
Year:
yy two digits 98
yyyy four digits 1998
24Сʱ¸ñʽÏÂʱ¼ä·¶Î§Îª£º 0:00:00 - 23:59:59....
12Сʱ¸ñʽÏÂʱ¼ä·¶Î§Îª£º 1:00:00 - 12:59:59 ....
1.
ÈÕÆÚºÍ×Ö·ûת»»º¯ÊýÓ÷¨£¨to_date,to_char£©
2.
select to_char( to_date(222,'J'),'Jsp') from dual
ÏÔʾTwo Hundred Twenty-Two
3.
ÇóijÌìÊÇÐÇÆÚ¼¸
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
ÐÇÆÚÒ»
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
monday
ÉèÖÃÈÕÆÚÓïÑÔ
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
Ò²¿ÉÒÔÕâÑù
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
4.
Á½¸öÈÕÆÚ¼äµÄÌìÊý
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
5. ʱ¼äΪnullµÄÓ÷¨
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;
×¢ÒâÒªÓÃTO_DATE(null)
6.
a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
ÄÇô12ÔÂ31ºÅÖÐÎç12µãÖ®ºóºÍ12ÔÂ1ºÅµÄ12µã֮ǰÊDz»°üº¬ÔÚÕâ¸ö·¶Î§Ö®Äڵġ£
ËùÒÔ£¬µ±Ê±¼äÐèÒª¾«È·µÄʱºò£¬¾õµÃto_char»¹ÊDZØÒªµÄ
7. ÈÕÆÚ¸ñʽ³åÍ»ÎÊÌâ
ÊäÈëµÄ¸ñʽҪ¿´Äã°²×°µÄORACLE×Ö·û¼¯µÄÀàÐÍ, ±ÈÈç: US7ASCII, date¸ñʽµÄÀàÐ;ÍÊÇ: '01-Jan-01'
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
»òÕßÔÚto_dateÖÐд
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
×¢ÒâÎÒÕâÖ»ÊǾÙÁËNLS_DATE_LANGUAGE£¬µ±È»»¹Óкܶ࣬
¿É²é¿´
select * from nls_sessi
Ïà¹ØÎĵµ£º
ÔÚʵ¼ÊµÄÊý¾Ý¿âÓ¦ÓÃÖУ¬ÎÒÃǾ³£Óöµ½ÕâÑùÒ»¸öÎÊÌ⣬Á¬½Óµ½OracleÊý¾Ý¿âµÄÓû§ÔÚ×÷ÁËÒ»´Î²Ù×÷ºó£¬ÔÙҲûÓкóÐø²Ù×÷£¬µ«È´³¤Ê±¼äûÓкÍÊý¾Ý¿â¶Ï¿ªÁ¬½Ó¡£¶ÔÓÚÒ»¸öСÐ͵ÄÓ¦ÓÃϵͳÀ´½²£¬±¾ÉíµÄÁ¬½ÓÊýÄ¿¾ÍÓÐÏÞ£¬ÕâºÃÏñûÓÐʲôÑÏÖصĺó¹û£¬µ«Èç¹û¶ÔÓÚÒ»¸ö´óÐ͵ÄÊý¾Ý¿âÓ¦Óá£ÈçË°Îñ¡¢¹¤É̵ȣ¬Èç¹ûÊý¾Ý¿âµÄÁ¬½ÓÊýÄ¿ºÜ¶à£¬¶ÔÓÚÊý¾Ý¿â ......
Èç¹û²»ÊÇÒòΪÕâ´Î»»hisµÄ·þÎñÆ÷£¬¹À¼ÆÎÒÒ»Ö±Ò²²»»áÓöµ½Õâ¸öÎÊÌ⣬һֱ¾õµÃoracleºÜ¸ßÉ¶ÔËüҲûÓÐÑо¿£¬¿ÉÕâ´Î»»·þÎñÆ÷£¬Æ«Æ«ÈÃÎÒÓöµ½ÁËÒ»¸öÎÊÌ⣬ÕÛÄ¥ÁËÕûÕûÁ½Ì죬ÖÕÓÚÔÚ×òÌì½â¾öµôÁË£¬¼Ç¼ÏÂÀ´£¬ÒÔ±¸ÒÔºóËùÓá£
ÖÜÎåÍíÉÏ»»ÁË·þÎñÆ÷£¬ÖÜÒ»ÔçÉÏ·¢ÏÖ³¬ÉùµÄ¹¤×÷Õ ......
±¾ÈËÒÔÇ°ÕûÀíµÄÊý¾Ý¿âÎļþǨÒƹý³Ì£¬Ï£ÍûÄܹ»¶Ô´ó¼ÒÓÐËù°ïÖú
1¡¢sqlplus "sys/sys@·þÎñÃû as sysdba"
2¡¢Ð޸ĿØÖÆÎļþ:
alter system set control_files='E:\oracle\oradata\myOracle_1\control01.ctl',
'E:\oracle\oradata\myOracle_1\control02.ctl','E:\oracle\oradata\my ......
1¡¢´æ´¢¹ý³ÌºÍº¯ÊýµÄÇø±ð¡£
£¨1£©¡¢´æ´¢¹ý³ÌÊÇ×÷ΪPL/SQLÓï¾äÖ´ÐУ¬¶øº¯ÊýÊÇ×÷Ϊ±í´ïʽµÄÒ»²¿·Öµ÷Óã»
£¨2£©¡¢´æ´¢¹ý³ÌÔÚ¹æ¸ñ˵Ã÷Öв»°üº¬returnÓï¾ä£¬¶øÔÚº¯ÊýµÄ¹æ¸ñ˵Ã÷Õâ°üº¬return×Ӿ䣻
(3)¡¢´æ´¢¹ý³Ì²»·µ»ØÖµ£¬¶øº¯Êý±ØÐë·µ»ØÖµ£»
£¨4£©¡¢ÔÚ´æ´¢¹ý³ÌÖпÉÒÔ°üº¬returnÓï¾ä£¬µ«²»·µ»ØÈκÎÖ ......
ÔÚÒ»°ãSelectÓï¾äÖÐÎÒÃÇͨ¹ýOrder by ...Asc/DescÀ´½øÐÐÅÅÐò¡£µ«ÊÇÕâÖÖÅÅÐò·½Ê½ÔÚÊä³öʱ£¬²»°üº¬ÅÅÐòºóµÄÐòºÅÐÅÏ¢¡£
ÏÖÔÚ½éÉÜÒ»ÏÂOracleÖпÉÒÔÓÃÓÚÅÅÐòÊä³öµÄ·½·¨¡£
1. RANK ( ) OVER ( [query_partition_clause] order_by_clause )
RANK ( ) OVER ( [query_p ......