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

oracleÖеÄNVL,NVL2,NULLIF,COALESCE¼¸¸öͨÓú¯Êý

OracleÖк¯ÊýÒÔǰ½éÉܵÄ×Ö·û´®´¦Àí£¬ÈÕÆÚº¯Êý£¬Êýѧº¯Êý,ÒÔ¼°×ª»»º¯ÊýµÈµÈ£¬»¹ÓÐÒ»ÀຯÊýÊÇͨÓú¯Êý¡£Ö÷ÒªÓУºNVL,NVL2,NULLIF,COALESCE£¬Õ⼸¸öº¯ÊýÓÃÔÚ¸÷¸öÀàÐÍÉ϶¼¿ÉÒÔ¡£
ÏÂÃæ¼òµ¥½éÉÜһϼ¸¸öº¯ÊýµÄÓ÷¨¡£
ÔÚ½éÉÜÕâ¸ö֮ǰÄã±ØÐëÃ÷°×ʲôÊÇoracleÖеĿÕÖµnull
1.NVLº¯Êý
NVLº¯ÊýµÄ¸ñʽÈçÏ£ºNVL(expr1,expr2)
º¬ÒåÊÇ£ºÈç¹ûoracleµÚÒ»¸ö²ÎÊýΪ¿ÕÄÇôÏÔʾµÚ¶þ¸ö²ÎÊýµÄÖµ£¬Èç¹ûµÚÒ»¸ö²ÎÊýµÄÖµ²»Îª¿Õ£¬ÔòÏÔʾµÚÒ»¸ö²ÎÊý±¾À´µÄÖµ¡£
ÀýÈ磺
SQL> select ename,NVL(comm, -1) from emp;
 
ENAME NVL(COMM,-1)
——————– ————
SMITH -1
ALLEN 300
WARD 500
JONES -1
MARTIN 1400
BLAKE -1
FORD -1
MILLER -1
ÆäÖÐÏÔʾ-1µÄ±¾À´µÄֵȫ²¿¶¼ÊÇ¿ÕÖµµÄ
 
2 NVL2º¯Êý
NVL2º¯ÊýµÄ¸ñʽÈçÏ£ºNVL2(expr1,expr2, expr3)
º¬ÒåÊÇ£ºÈç¹û¸Ãº¯ÊýµÄµÚÒ»¸ö²ÎÊýΪ¿ÕÄÇôÏÔʾµÚ¶þ¸ö²ÎÊýµÄÖµ£¬Èç¹ûµÚÒ»¸ö²ÎÊýµÄÖµ²»Îª¿Õ£¬ÔòÏÔʾµÚÈý¸ö²ÎÊýµÄÖµ¡£
SQL> select ename,NVL2(comm,-1,1) from emp;
 
ENAME NVL2(COMM,-1,1)
——————– —————
SMITH 1
ALLEN -1
WARD -1
JONES 1
MARTIN -1
BLAKE 1
CLARK 1
SCOTT 1
ÉÏÃæµÄÀý×ÓÖС£·²Êǽá¹ûÊÇ1µÄÔ­À´¶¼²»Îª¿Õ£¬¶ø½á¹ûÊÇ-1µÄÔ­À´µÄÖµ¾ÍÊǿա£
 
3. NULLIFº¯Êý
NULLIF(exp1,expr2)º¯ÊýµÄ×÷ÓÃÊÇÈç¹ûexp1ºÍexp2ÏàµÈÔò·µ»Ø¿Õ(NULL)£¬·ñÔò·µ»ØµÚÒ»¸öÖµ¡£
ÏÂÃæÊÇÒ»¸öÀý×Ó¡£Ê¹ÓõÄÊÇoracleÖÐHR schema£¬Èç¹ûHR´¦ÓÚËø¶¨£¬ÇëÆôÓÃ
ÕâÀïµÄ×÷ÓÃÊÇÏÔʾ³öÄÇЩ»»¹ý¹¤×÷µÄÈËÔ±Ô­¹¤×÷£¬ÏÖ¹¤×÷¡£
SQL> SELECT e.last_name, e.job_id,j.job_id,NULLIF(e.job_id, j.job_id) “Old Job ID”
from employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name;
 
LAST_NAME JOB_ID JOB_ID Old Job ID
————————————————– ——————– ——————– ——————–
De Haan AD_VP IT_PROG AD_VP
Hartstein MK_MAN MK_REP MK_MAN
Kaufling ST_MAN ST_CLERK ST_MAN


Ïà¹ØÎĵµ£º

oracleÖÐÈçºÎʵÏÖ¸øµ±Ç°Ê±¼ä¼ÓÒ»·ÖÖÓ£¿

 select   to_char(sysdate   +   1   /   (24   *   60),'yyyy/mm/dd   hh24:mi:ss')   from   dual
SQL>   ;  
      1     select   to_char(sysdate,   'hh:mi:ss')  
      2 &nbs ......

ѧϰ¡¶Oracle 9i10g±à³ÌÒÕÊõ¡·µÄ±Ê¼Ç (¶þ)

 Ò».Ëø
Êý¾Ý¿âʹÓÃËø£¨lock£© À´±£Ö¤Èκθø¶¨Ê±¿Ì×î¶àÖ»ÓÐÒ»¸öÊÂÎñÔÚÐ޸ĸø¶¨µÄÒ»¶ÎÊý¾Ý¡£ÊµÖÊÉϽ²£¬
ÕýÊÇËø»úÖÆ²Åʹ²¢·¢¿ØÖƳÉΪ¿ÉÄÜ¡£ÀýÈ磬Èç¹ûûÓÐijÖÖËø¶¨Ä£ÐÍÀ´×èÖ¹¶ÔͬһÐеIJ¢·¢¸üУ¬Êý¾Ý¿â¾Í
²»¿ÉÄÜÌṩ¶àÓû§·ÃÎÊ¡£²»¹ý£¬Èç¹ûÀÄÓûòÕßʹÓò»µ±£¬Ëø·´µ¹»á×è°­²¢·¢ÐÔ¡£Èç¹ûÄã»òÊý¾Ý¿â±¾Éí²»±Ø
ÒªµØ¶ÔÊý¾Ý ......

oracleÖµµÃ̽ÌÖµÄÎÊÌâ

¾­³£×öһЩÊý¾Ý¿â²Ù×÷£¬ÔÚ½¨Á¢±íµÄʱºò£¬Ñ¡Ôñ×Ö¶ÎÀàÐÍʱ£¬ÓйØ×Ö·û´®ÀàÐ͵ÄÑ¡Ôñ×ÜÊÇÓÐЩģºý¡£
¡£char
¡£varchar2
¡£nvarchar2
¡£nchar
¹ØÓÚÉÏÃæÁ½¸öÎÒÓеãÓ¡Ïó¡£CHARµÄ³¤¶ÈÊǹ̶¨µÄ£¬¶øVARCHAR2µÄ³¤¶ÈÊÇ¿ÉÒԱ仯µÄ£¬±ÈÈ磬´æ´¢×Ö·û´®“abc"£¬¶ÔÓÚCHAR (20)£¬±íʾÄã´æ´¢µÄ×Ö·û½«Õ¼20¸ö×Ö½Ú(°üÀ¨17¸ö¿Õ×Ö·û)£ ......

ORACLEÆô¶¯

 
ORACLEÖ÷ÒªÓÉÁ½²¿·Ö×é³É£ºInstanceºÍDatabase¡£InstanceÊÇÖ¸Ò»×éºǫ́½ø³Ì/Ï̺߳ÍÒ»¿é¹²ÏíÄÚ´æÇøÓò£¬¶øDatabaseÊÇÖ¸´æ´¢ÔÚ´ÅÅÌÉϵÄÒ»×éÎïÀíÎļþ¡£
ORACLEÊý¾Ý¿âµÄÆô¶¯Ö÷Òª·Ö3¸ö²½Ö裺
1¡¢Æô¶¯µ½nomount״̬£»
2¡¢Æô¶¯µ½mount״̬£»
3¡¢Æô¶¯µ½open״̬¡£
ÏÂÃæ·ÖÎöÒ»ÏÂÆô¶¯µÄ¸÷¸ö²½Ö裺
Ò»¡¢Æô¶¯µ½nomount״̬
......

ѧϰ¡¶Oracle 9i10g±à³ÌÒÕÊõ¡·µÄ±Ê¼Ç (Èý)

 1.¶à°æ±¾£º¶ÁÒ»Ö²éѯ  ·Ç×èÈû²éѯ
Oracle ²ÉÓÃÁËÒ»ÖÖ
¶à°æ±¾¡¢¶ÁÒ»Ö£¨read-consistent£©µÄ²¢·¢Ä£ÐÍ¡£ÔÙ´Î˵Ã÷£¬ÎÒÃǽ«ÔÚµÚ7 Õ¸üÏêϸµØ½éÉÜÓйصļ¼Êõ¡£
²»¹ý£¬ÊµÖÊÉϽ²£¬Oracle ÀûÓÃÕâÖÖ»úÖÆÌṩÁËÒÔÏÂÌØÐÔ£º
¶ÁÒ»Ö²éѯ£º¶ÔÓÚÒ»¸öʱ¼äµã£¨point in time£©£¬²éѯ»á²úÉúÒ»ÖµĽá¹û¡£
·Ç×èÈû²éѯ£º²éѯ²»» ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ