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

ORACLE ÔÚnot inÖÐʹÓÃnullµÄÎÊÌâ

ÒÔǰ»¹×¨ÃÅС×ܽá¹ýÒ»ÏÂORACLEÖйØÓÚNULLµÄһЩÎÊÌ⣬ÅöÇɽñÌìÔÚ¿´ÊéµÄ¹ý³ÌÖÐÓÖ¿´µ½ÁËÁíÍâÒ»¸öÒÔǰû·¢ÏÖµÄÐèҪעÒâµÄµØ·½£¬ÄǾÍÊÇÔÚnot inÖÐʹÓÃnullµÄÎÊÌâ¡£
SQL> select * from dept;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
SQL> select deptno
  2  from dept
  3  where deptno in (10,50,null);
    DEPTNO
----------
        10
//¿´µ½Ê¹ÓÃinµÄʱºò¼´±ãÓÐnull Ò²ÊÇÕý³£µÄ ÏÂÃæ¿´Ò»ÏÂnot in
SQL> select deptno
  2  from dept
  3  where deptno not in (10,50);
    DEPTNO
----------
        20
        30
        40
//ÕâÀï¿´ÆðÀ´ºÍÎÒÃǵÄÔ¤ÆÚͦ·ûºÏµÄŶ
SQL> select deptno
  2  from dept
  3  where deptno not in (10,50,null);
no rows selected
//Ôõô»ØÊ Ϊʲô¼ÓÁ˸önull Ç°ÃæµÄ20¡¢30¡¢40ÈýÌõÊý¾Ý¾Í²»ÏÔʾ³öÀ´ÁË
INºÍNOT IN±¾ÖÊÉ϶¼ÊÇORÔËË㣬Òò¶ø¼ÆËãÂß¼­ORʱ´¦ÀíNULLµÄ·½Ê½²»Í¬£¬²úÉúµÄ½á¹ûÒ²²»Í¬¡£
ÏÂÃæÎÒÃÇ·ÖÎöÒ»ÏÂÇ°ÃæµÄÈýÌõÓï¾ä
SQL> select deptno
  2  from dept
  3  where deptno in (10,50,null);
ÕâÀï¿ÉÒԵȼÛÓÚwhere deptno=10 or deptno=50 or deptno=null£¬ÓÉÓÚÊÇorÏàÁ¬½Ó£¬ÄÇôֻҪÓÐÒ»¸öÌõ¼þΪTRUE£¬Õû¸ö¾ÍιTRUEÁË¡£ËùÒÔdeptnoΪ10µÄ¼Ç¼ÏÔʾ³öÀ´ÁË¡£
SQL> select deptno
  2  from dept
  3  where deptno not in (10,50,null);
ÕâÀïµÈ¼ÛÓÚwhere not (deptno=10 or deptno=50 or deptno=null)£¬ÄÃdeptno=20µÄ¼Ç¼À´¾ÙÀý°É¡£
not (20=10


Ïà¹ØÎĵµ£º

ÈçºÎ²é¿´ºÍÇå³ýoracleÎÞÓõÄÁ¬½Ó½ø³Ì


DBAÒª¶¨Ê±¶ÔÊý¾Ý¿âµÄÁ¬½ÓÇé¿ö½øÐмì²é£¬¿´ÓëÊý¾Ý¿â½¨Á¢µÄ»á»°ÊýÄ¿ÊDz»ÊÇÕý³££¬Èç¹û½¨Á¢Á˹ý¶àµÄÁ¬½Ó£¬»áÏûºÄÊý¾Ý¿âµÄ×ÊÔ´¡£Í¬Ê±£¬¶ÔһЩ“¹ÒËÀ”µÄÁ¬½Ó£¬¿ÉÄÜ»áÐèÒªDBAÊÖ¹¤½øÐÐÇåÀí¡£
ÒÔϵÄSQLÓï¾äÁгöµ±Ç°Êý¾Ý¿â½¨Á¢µÄ»á»°Çé¿ö:
select sid,serial#,username,program,machine,status
from v$session; ......

ORACLE to_char() º¯Êý»ñÈ¡×ÔÈ»ÖÜÊý

 
×î¼òµ¥µÄÊÇʹÓÃ
 
/* Formatted on 2009/12/02 16:01 (Formatter Plus v4.8.8) */
SELECT TO_CHAR (TO_DATE ('2009-12-02', 'YYYY-MM-DD'), 'WW')
  from DUAL;
²»¹ýto_char()º¯ÊýÔÚ¼ÆËãÒ»ÄêÖеڼ¸ÖÜÊÇ´Ó¸ÃÄêµÄ1ÔÂ1ÈÕ¿ªÊ¼µÄ¡£
 
 ÒÔÏÂSQL Äܵõ½ÕýÈ·µÄ×ÔÈ»ÖÜÊý.
 
/* Formatted ......

OracleÄÚÖú¯ÊýSQLCODEºÍSQLERRMµÄʹÓÃ

      OracleÄÚÖú¯ÊýSQLCODEºÍSQLERRMÊÇÌØ±ðÓÃÔÚOTHERS´¦ÀíÆ÷ÖУ¬·Ö±ðÓÃÀ´·µ»ØOracleµÄ´íÎó´úÂëºÍ´íÎóÏûÏ¢¡£
¡¡¡¡
¡¡¡¡OTHERS´¦ÀíÆ÷Ó¦¸ÃÊÇÒì³£´¦Àí¿éÖеÄ×îºóµÄÒì³£´¦ÀíÆ÷£¬ÒòΪËüÊÇÓÃÀ´²¶»ñ³ýÁ˱ðµÄÒì³£´¦ÀíÆ÷´¦ÀíÒÔÍâµÄËùÓеÄOracleÒì³££¬ËùÒÔÔÚ³ÌÐòµÄ×îÍâ²ãʹÓÃÒ»¸öOTHERS´¦ÀíÆ÷µÄ»°£¬ ......

ORACLE ÔÚWHERE×Ó¾äÖÐÒýÓÃÁбðÃûµÄÎÊÌâ

SQL> select sal,comm from emp
2 where sal<2000;
SAL COMM
---------- ----------
800
1600 300
1250 500
1250 1400
1500 0
1100
950
1300
8 rows selected.
SQL> select sal as salary,comm as ......

ROLLUPºÍCUBEÓï¾ä¡£ ORACLE·Ö×éͳ¼Æ

ROLLUPºÍCUBEÓï¾ä¡£
OracleµÄGROUP
BYÓï¾ä³ýÁË×î»ù±¾µÄÓï·¨Í⣬»¹Ö§³ÖROLLUPºÍCUBEÓï¾ä¡£Èç¹ûÊÇROLLUP(A, B, C)µÄ»°£¬Ê×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP
BY£¬È»ºó¶Ô(A¡¢B)½øÐÐGROUP BY£¬È»ºóÊÇ(A)½øÐÐGROUP BY£¬×îºó¶ÔÈ«±í½øÐÐGROUP BY²Ù×÷¡£Èç¹ûÊÇGROUP BY
CUBE(A, B, C)£¬ÔòÊ×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP
BY£¬È»ºóÒÀ´ÎÊÇ(A¡ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ