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
Ïà¹ØÎĵµ£º
SQLÖеĵ¥¼Ç¼º¯Êý
1.ASCII
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
¸ø³öÕûÊý,·µ»Ø¶ÔÓ¦µÄ×Ö·û;
SQL> select chr(54740) zhao,chr(65) chr65 from ......
Ò».Ëø
Êý¾Ý¿âʹÓÃËø£¨lock£© À´±£Ö¤Èκθø¶¨Ê±¿Ì×î¶àÖ»ÓÐÒ»¸öÊÂÎñÔÚÐ޸ĸø¶¨µÄÒ»¶ÎÊý¾Ý¡£ÊµÖÊÉϽ²£¬
ÕýÊÇËø»úÖÆ²Åʹ²¢·¢¿ØÖƳÉΪ¿ÉÄÜ¡£ÀýÈ磬Èç¹ûûÓÐijÖÖËø¶¨Ä£ÐÍÀ´×èÖ¹¶ÔͬһÐеIJ¢·¢¸üУ¬Êý¾Ý¿â¾Í
²»¿ÉÄÜÌṩ¶àÓû§·ÃÎÊ¡£²»¹ý£¬Èç¹ûÀÄÓûòÕßʹÓò»µ±£¬Ëø·´µ¹»á×è°²¢·¢ÐÔ¡£Èç¹ûÄã»òÊý¾Ý¿â±¾Éí²»±Ø
ÒªµØ¶ÔÊý¾Ý ......
SQL*Loader ÓÃÓÚ½«´óÁ¿Êý¾Ý×°ÈëÊý¾Ý¿â¡£
¢Å¡¢¶¨¿íÊý¾Ý
´´½¨Êý¾ÝÎļþcontrol.txt£º
aaa,bbb
ccc,ddd
eee,fff
´´½¨¿ØÖÆÎļþcontrol.ctl£º
load data
infile 'c:\loader.txt'
append
into table tester.mm(
m1 position(1:3) char,
m2 position(5:7) char)
ÅúÁ¿¼ÓÔØÊý¾Ý£º
sqlldr tester/test control=c:\loade ......
Ubuntu9.04ÏÂOracleÊÖ¶¯Æô¶¯Óë×ÔÆô¶¯½Å±¾
Ò»¡¢Æô¶¯Êý¾Ý¿âʵÀý
Java´úÂë
gengzhi
@gengzhi
-desktop:~$ su oracle
oracle@gengzhi
-desktop:~$ sqlplus
" scott/tiger as sysdba"
gengzhi@gengzhi-desktop:~$ s ......
1.¶à°æ±¾£º¶ÁÒ»Ö²éѯ ·Ç×èÈû²éѯ
Oracle ²ÉÓÃÁËÒ»ÖÖ
¶à°æ±¾¡¢¶ÁÒ»Ö£¨read-consistent£©µÄ²¢·¢Ä£ÐÍ¡£ÔÙ´Î˵Ã÷£¬ÎÒÃǽ«ÔÚµÚ7 Õ¸üÏêϸµØ½éÉÜÓйصļ¼Êõ¡£
²»¹ý£¬ÊµÖÊÉϽ²£¬Oracle ÀûÓÃÕâÖÖ»úÖÆÌṩÁËÒÔÏÂÌØÐÔ£º
¶ÁÒ»Ö²éѯ£º¶ÔÓÚÒ»¸öʱ¼äµã£¨point in time£©£¬²éѯ»á²úÉúÒ»ÖµĽá¹û¡£
·Ç×èÈû²éѯ£º²éѯ²»» ......