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
Ïà¹ØÎĵµ£º
ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½ÓOracle£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
Ê×ÏÈ£¬Ô ......
1.λͼË÷Òý
×î½üÎÒ²ÎÓëÁËÒ»¸öÏîÄ¿¡£¿ª·¢ÈËÔ±Õý±¥ÊÜÐÔÄÜÎÊÌâÖ®¿à£¬¿´ÉÏÈ¥ËûÃǵÄϵͳÖÐÐí¶àÊÂÎñÔÚ´®ÐÐ
½øÐС£ËûÃǵÄ×ö·¨²»ÊÇ´ó¼Ò²¢·¢µØ¹¤×÷£¬¶øÊÇÿ¸öÈ˶¼ÒªÅÅÒ»¸ö³¤³¤µÄ¶Ó£¬¿à¿àµÈ×ÅÇ°ÃæµÄÈËÍê³Éºó²ÅÄÜ
¼ÌÐø¡£Ó¦Óüܹ¹Ê¦ÏòÎÒչʾÁËϵͳµÄÌåϵ½á¹¹£¬ÕâÊǾµäµÄÈý²ã·½·¨¡£ËûÃÇÏëÈÃWeb ä¯ÀÀÆ÷ÓëÒ»¸öÔËÐÐJSP
£¨JavaServer Pa ......
1.¶à°æ±¾£º¶ÁÒ»Ö²éѯ ·Ç×èÈû²éѯ
Oracle ²ÉÓÃÁËÒ»ÖÖ
¶à°æ±¾¡¢¶ÁÒ»Ö£¨read-consistent£©µÄ²¢·¢Ä£ÐÍ¡£ÔÙ´Î˵Ã÷£¬ÎÒÃǽ«ÔÚµÚ7 Õ¸üÏêϸµØ½éÉÜÓйصļ¼Êõ¡£
²»¹ý£¬ÊµÖÊÉϽ²£¬Oracle ÀûÓÃÕâÖÖ»úÖÆÌṩÁËÒÔÏÂÌØÐÔ£º
¶ÁÒ»Ö²éѯ£º¶ÔÓÚÒ»¸öʱ¼äµã£¨point in time£©£¬²éѯ»á²úÉúÒ»ÖµĽá¹û¡£
·Ç×èÈû²éѯ£º²éѯ²»» ......
ÍêÈ«Ð¶ÔØOracle
Èí¼þ»·¾³£º
1¡¢Windows 2000+ORACLE 8.1.7
2¡¢ORACLE°²×°Â·¾¶Îª£ºC:\ORACLE
ʵÏÖ·½·¨£º
1¡¢ ¿ªÊ¼££¾ÉèÖ㣾¿ØÖÆÃæ°å££¾¹ÜÀí¹¤¾ß££¾·þÎñ
Í£Ö¹ËùÓÐOracle·þÎñ¡£
2¡¢ ¿ªÊ¼££¾³ÌÐò££¾Oracle - OraHome81££¾Oracle Installation Products££¾
Universal Installer
жװËùÓÐOracle²úÆ·£¬ ......