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
Ïà¹ØÎĵµ£º
²éѯ±íempÖÐËùÓÐÊý¾Ý
select emp_id,rownum from emp
µÚÒ»²½£¬²éѯ½á¹û,rownum´ý¶¨
emp_id rownum
1 ? 1
2 ? 2
3 ? 3
4&n ......
Ç°ÃæÐ´Á˶à·ÝºìÆìDC 4.1ºÍ5.0Éϰ²×°Oracle 9i/10gµÄÎĵµ£¬µ«Ò»Ö±Ã»ÓÐÕûÀíDC 5.0Éϰ²×°Oracle 10gµ¥»ú·þÎñÆ÷µÄ×ÊÁÏ£¬ÔÒòÊǸùý³Ì·Ç³£¼òµ¥¡£µ«×î½üÏîÄ¿ÖУ¬»¹ÊÇÓÐÓû§Óöµ½Ð©ÎÊÌ⣬½ñÌì¾Í°ÑһЩÐèҪעÒâµÄµØ·½ÕûÀíһϣ¬ÏêϸµÄ¹ý³Ì¾Í²»ÃèÊöÁË¡£
Ò»¡¢ÏµÍ³»·¾³
²Ù×÷ϵͳ£ººìÆì DC 5.0 for x86 »ò x86_64
Ó²¼þ»·¾ ......
1¡¢±àдĿµÄ
ʹÓÃͳһµÄÃüÃûºÍ±àÂë¹æ·¶£¬Ê¹Êý¾Ý¿âÃüÃû¼°±àÂë·ç¸ñ±ê×¼»¯£¬ÒÔ±ãÓÚÔĶÁ¡¢Àí½âºÍ¼Ì³Ð¡£
2¡¢ÊÊÓ÷¶Î§
±¾¹æ·¶ÊÊÓÃÓÚ¹«Ë¾·¶Î§ÄÚËùÓÐÒÔORACLE×÷Ϊºǫ́Êý¾Ý¿âµÄÓ¦ÓÃϵͳºÍÏîÄ¿¿ª·¢¹¤×÷¡£
3¡¢¶ÔÏóÃüÃû¹æ·¶
3.1 Êý¾Ý¿âºÍSID
Êý¾Ý¿âÃû¶¨ÒåΪϵͳÃû+Ä£¿éÃû
¡ï È«¾ÖÊý¾Ý¿âÃûºÍÀý³ÌSID ÃûÒªÇóÒ»ÖÂ
¡ï ÒòSID ......
1.³õʼ»¯ÊµÑ黵¾³
1£©´´½¨²âÊÔ±ígroup_test
sec@ora10g> create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);
¡¡
Table created.
¡¡
2£©³õʼ»¯Êý¾Ý
insert into group_test values (10,'Coding', 'Bruce',1000);
insert into group_test val ......
¡¡¡¡±¾ÎĽéÉÜÁËÔÚOracleÊý¾Ý¿âÖУ¬¶ÔÈÕÆÚ¡¢Ê±¼äµÄ¸÷ÖÖ²Ù×÷£¬°üÀ¨£ºÈÕÆÚʱ¼ä¼ä¸ô²Ù×÷¡¢ÈÕÆÚµ½×Ö·û²Ù×÷¡¢×Ö·ûµ½ÈÕÆÚ²Ù×÷¡¢trunk / ROUNDº¯ÊýµÄʹÓᢺÁÃë¼¶µÄÊý¾ÝÀàÐ͵ȡ£
1.ÈÕÆÚʱ¼ä¼ä¸ô²Ù×÷
¡¡¡¡µ±Ç°Ê±¼ä¼õÈ¥7·ÖÖÓµÄʱ¼ä
¡¡¡¡select sysdate,sysdate - interval '7' MINUTE from dual
¡¡¡¡µ±Ç°Ê±¼ä¼õÈ¥7СʱµÄʱ¼ä
¡¡¡ ......