Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

Oracleʱ¼äÀàÐÍÊý¾ÝΪ0µÄBug

¿ª·¢×éÔÚÊý¾ÝǨÒÆʱ£¬±¨¸æ·¢ÏÖһЩÊýֵΪ'0000/00/00'µÄdateÊý¾Ý£¬µ¼ÖÂÊý¾ÝǨÒÆʧ°Ü¡£
Õâ¸öÎÊÌâÓеãÆæ¹Ö£¬ÒòΪÔÚOracleÖУ¬dateÀàÐ͵ÄÊý¾ÝµÄÈ¡Öµ·¶Î§ÊÇ´Ó-4712/12/31µ½9999/12/31Ö®¼ä£¬²¢ÇÒÄê·Ý²»ÄÜΪ0¡£Ò²¾ÍÊÇ˵'0000/00/00'ÊÇÒ»¸ö·Ç·¨Êý¾Ý£¬²»ÎªOracleËù½ÓÊÜ¡£
 
SQL> select to_date('0000-00-00', 'yyyy-mm-dd') from dual;
 
select to_date('0000-00-00', 'yyyy-mm-dd') from dual
 
ORA-01843: not a valid month
 
SQL> select to_date('0000-01-01', 'yyyy-mm-dd') from dual;
 
select to_date('0000-01-01', 'yyyy-mm-dd') from dual
 
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
 
µ«ÎªÊ²Ã´ÔÚÊý¾ÝÖл¹ÊdzöÏÖÁË'0000/00/00'ÄØ£¿¶Ô´ËÎÊÌâÉÔ΢Ñо¿ÁËһϣ¬·¢ÏÖOracleÔÚdateÀàÐ͵ÄÊý¾ÝÎÊÌâÉÏȷʵ´æÔÚһЩbug£¬Í¨¹ýһЩÌØÊâ·½·¨»¹ÊÇÄÜʹdateÀàÐÍ´æ´¢'0000/00/00'Êý¾Ý¡£ÏÈ¿´ÒÔϲÙ×÷£¬ÕâÊÇbugÖ®Ò»¡£
 
SQL> select date '0000-01-01' from dual;
 
DATE'0000-01-01'
----------------
0/0/0000
 
SQL> select date '0000-11-22' from dual;
 
DATE'0000-11-22'
----------------
0/0/0000
 
ÔÚʹÓÃdate¹Ø¼ü×Öʱ£¬Ê±¼ä¸ñʽÊÇÂÞÂí¸ñʽ¡£´Ëʱ£¬ÎÒÃÇ·¢ÏÖOracleûÓжÔÄê·ÝÊÇ·ñΪ0½øÐÐУÑé¡£²¢ÇÒ£¬Ö»ÒªÄê·ÝΪ0£¬Êý¾Ý¶¼»á±»×ª±äΪ'0000/00/00'¡£
 
ÔÙ¿´ÁíÍâÒ»ÖÖÇé¿ö£¬
 
SQL> select to_date('0001-01-01', 'yyyy-mm-dd')-365 from dual;
 
TO_DATE('0001-01-01','YYYY-MM-
------------------------------
0/0/0000
 
SQL> select to_date('0001-01-01', 'yyyy-mm-dd')-360 from dual;
 
TO_DATE('0001-01-01','YYYY-MM-
------------------------------
0/0/0000
 
¿ÉÒÔ¿´µ½£¬Oracle¶Ôʱ¼ä±í´ïʽµÄ½á¹ûҲûÓÐУÑéÄê·ÝÊÇ·ñΪ0£¬½áºÏÉÏÃæµÄbug£¬Ö»Òª¼ÆËã½á¹ûÄê·ÝΪ0£¬ÎÞÂÛÔ¡¢ÈÕÊýÖµ£¬½á¹û¶¼Îª'0000/00/00'¡£
 
ÔÙ¿´µÚÈýÖÖÇé¿ö£¬¾Í¸ü¼ÓÌØÊâÁË£ºÖ»Òª¶Ô100µ½1500ÄêÖ®ÄÚµÄËùÓÐÕû°ÙÄêµÄÈÕÆÚ½øÐмÆË㣬Èç¹û½á¹ûΪ2ÔÂ29µÄ»°£¬½á¹û¶¼Îª'0000/00/00'¡£
 
SQL> select date '0099-2-28' +1 from dual;
 
DATE'0099-2-28'+1
-----------------
3/1/0099
 
SQL> select date '0100-2-28' +1 from dual;
 
DATE'0100-2-28'+1


Ïà¹ØÎĵµ£º

Oracle 11g R2ÐÂÌØÐÔ¸ÅÊö£¨×ª£©

 µ¼¶Á£º
¡¡¡¡2009Äê9ÔÂOracle¹«Ë¾·¢²¼ÁËÆÚ´ýÒѾõÄOracle 11g R2£¬±¾ÏµÁÐÎÄÕ½«¸ø¶ÁÕßÒ»Ò»½Ò¿ªÐ°汾ÖеÄÐÂÌØÐÔ£¬²¢»á½éÉÜÆóÒµÈçºÎÀûÓÃÕâЩÐÂÌØÐÔ½«ÏÖÓеÄOracle 9i£¬10g£¬11g R1Éý¼¶µ½Oracle 11g R2.
¡¡¡¡¾­ÀúÁËÄÑÒÔÈÌÊܵij¤Ê±¼äµÈ´ý£¬Oracle¹«Ë¾Í»È»ÔÚ9ÔÂ1·¢²¼ÁËOracle 11g R2£¬ÎÒ²»µÃ²»³ÐÈÏOracleµÄ±£Ãܹ¤×÷×öµÃ ......

ORACLE SQLÓï¾äÓÅ»¯×ܽá

ORACLE SQLÓï¾äÓÅ»¯×ܽá
 
1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLEµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£Èç¹ûÓÐ3¸öÒÔÉϵıíÁ¬½Ó²éѯ, Ä ......

ORACLE TRUNC()º¯Êý

 ÒÔÉÏΪתÔØ,¶àÊý²¢Î´ÑéÖ¤.
ÆäÖÐ,select trunc(sysdate,'dd') from dual,µÃµ½µÄÊÇ: 2009-3-23 ÉÏÎç12:00:00 ,ÓëÏÂÎIJ¢²»Ò»Ñù!
oracle trunc()º¯ÊýµÄÓ÷¨
1.TRUNC(for dates)
TRUNCº¯ÊýΪָ¶¨ÔªËضø½ØÈ¥µÄÈÕÆÚÖµ¡£
Æä¾ßÌåµÄÓï·¨¸ñʽÈçÏ£º
TRUNC£¨date[,fmt]£©
ÆäÖУº
date Ò»¸öÈÕÆÚÖµ
fmt ÈÕÆÚ¸ñʽ£¬¸ÃÈÕÆÚ½« ......

Oracleɾ³ýÖظ´Ðд«ÖDz¥¿Í

 ²éѯ¼°É¾³ýÖظ´¼Ç¼µÄSQLÓï¾ä
1¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select   peopleId from   people group by   peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ý ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ