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±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
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 ......
¡¡¡¡* SQL Group Function
*
s (num can be a column or ex
pression)¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡
¡¡¡¡(null values are ign
*
ored, default between distin
ct and all is all)¡¡¡¡¡¡¡¡¡¡¡¡
¡¡¡¡********************
***************
****************************
****************
¡¡¡¡AVG([distinct or all ......
ÍêȫжÔØOracle
Èí¼þ»·¾³£º
1¡¢Windows 2000+ORACLE 8.1.7
2¡¢ORACLE°²×°Â·¾¶Îª£ºC:\ORACLE
ʵÏÖ·½·¨£º
1¡¢ ¿ªÊ¼££¾ÉèÖ㣾¿ØÖÆÃæ°å££¾¹ÜÀí¹¤¾ß££¾·þÎñ
Í£Ö¹ËùÓÐOracle·þÎñ¡£
2¡¢ ¿ªÊ¼££¾³ÌÐò££¾Oracle - OraHome81££¾Oracle Installation Products££¾
Universal Installer
жװËùÓÐOracle²úÆ·£¬ ......
dualÊÇÒ»¸öÐéÄâ±í£¬ÓÃÀ´¹¹³ÉselectµÄÓï·¨¹æÔò£¬oracle±£Ö¤dualÀïÃæÓÀÔ¶Ö»ÓÐÒ»Ìõ¼Ç¼¡£ÎÒÃÇ¿ÉÒÔÓÃËüÀ´×öºÜ¶àÊÂÇ飬ÈçÏ£º
1¡¢²é¿´µ±Ç°Óû§£¬¿ÉÒÔÔÚ SQL PlusÖÐÖ´ÐÐÏÂÃæÓï¾ä select user from dual;
2¡¢ÓÃÀ´µ÷ÓÃϵͳº¯Êý
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--»ñµÃµ±Ç°ÏµÍ³Ê±¼ ......