Oracle Decodeº¯ÊýʹÓü¼ÇÉ
decodeº¯Êý
Óï·¨£º
decode(expr,search,result[,search,result]..[,search,result][,default])
½âÊÍ£º
±È½ÏexprÓëÿ¸ösearchµÄÖµ£¬Èç¹ûexprµÈÓÚij¸ösearch£¬Ôò·µ»ØÏàÓ¦µÄresult£»Èç¹ûûÓÐÆ¥ÅäµÄÖµ£¬Ôò·µ»ØdefaultÖµ£»Èç¹ûûÓÐÖ¸¶¨defaultÖµ£¬Ôò·µ»Ønull
×¢Ò⣺
±È½ÏÇ°£¬Oracle×Ô¶¯½«exprµÄÊý¾ÝÀàÐÍת»»³ÉµÚÒ»¸ösearchµÄÀàÐÍ£»Oracle×Ô¶¯½«·µ»ØÖµµÄÀàÐÍת»»³ÉµÚÒ»¸öresultµÄÀàÐÍ£»Èç¹ûµÚÒ»¸öresultµÄÀàÐÍΪChar»ònull£¬ÔòOracle½«·µ»Ø½á¹ûת»»³ÉvVarchar2ÀàÐÍ¡£decodeº¯ÊýÖУ¬OracleÈÏΪÁ½¸önullÏàµÈ¡£
ʾÀý£º
SQL> select decode('0','0','true','1','false',null) from dual; --·µ»Øtrue
SQL> select decode('1','0','true','1','false',null) from dual; --·µ»Øfalse
SQL> select decode('2','0','true','1','false',null) from dual; --·µ»Ønull
Ó¦ÓãºÐÐÁÐת»»
±íexample1ÓÐÈý¸ö×Ö¶Î
Ãû³Æ ÊÇ·ñΪ¿Õ? ÀàÐÍ
----------------------------------------- -------- ----------------------------
SEQ NOT NULL NUMBER(3)
JCXM NUMBER(3)
ZHI NUMBER(3,2)
Êý¾Ý·Ö±ðÈçÏ£º
SEQ JCXM ZHI
---------- ---------- ----------
11 1 .5
11 2 .21
11 3 .25
12  
Ïà¹ØÎĵµ£º
OracleÖ´Ðмƻ®µÄÏà¹Ø¸ÅÄ
rowid£ºÏµÍ³¸øoracleÊý¾ÝµÄÿÐи½¼ÓµÄÒ»¸öαÁУ¬°üº¬Êý¾Ý±íÃû³Æ£¬Êý¾Ý¿âid£¬´æ´¢Êý¾Ý¿âidÒÔ¼°Ò»¸öÁ÷Ë®ºÅµÈÐÅÏ¢£¬rowidÔÚÐеÄÉúÃüÖÜÆÚÄÚΨһ¡£
recursive sql£ºÎªÁËÖ´ÐÐÓû§Óï¾ä£¬ÏµÍ³¸½¼ÓÖ´ÐеĶîÍâ²Ù×÷Óï¾ä£¬Æ©Èç¶ÔÊý¾Ý×ÖµäµÄά»¤µÈ¡£
row source£¨ÐÐÔ´£©£ºoracleÖ´Ðв½Öè¹ý³ÌÖУ¬ÓÉÉÏÒ» ......
Êý¾ÝÀàÐÍʹÓÃ
1.ÉùÃ÷£º
a.¸ñʽ£ºVariable_name [constant] databyte [not null] [:=default expression]
b.±äÁ¿Óë³£Á¿ÉùÃ÷»ù±¾Ò»Ö£¬Ê¹ÓÃconstantÉùÃ÷µÄΪ³£Á¿£¬²»Ê¹ÓÃΪ±äÁ¿£¬²¢ÇÒ¸³ÖµÖ»ÄÜÔÚDECLAREÇøÓò¸³Öµ£»
c.ʹÓÃ%TYPEºÍ%ROWTYPEÉùÃ÷¿ÉÒÔʹ±äÁ¿µÄÀàÐÍÓë±íÖÐ×Ö¶ÎÀàÐÍ»òÕû¸ö¼Ç¼ÀàÐͱ ......
Òì³£
Ô¤¶¨ÒåÒì³£
oracleΪ³£¼û´íÎóÔ¤¶¨Òå
ÔÚDBMS_STANDARD³ÌÐò°üÖÐÌṩÁËÕâЩ¶¨Òå
²»ÐèÒªÏÔʾÉùÃ÷
declare
sex student.stu_sex%type;
begin
select stu_sex into sex from student;
dbms_output.put_line('sex:'||sex);
exception
&nbs ......
--sqlplus
sqlplus;
sys /password as sysdba;
--²éÕÒÓû§ID£¨user#£©,Êý¾Ý¿âÀïÃæ´æ·ÅµÄÓû§¶¼ÊÇ´óд!
select user#,name,password from user$ where name ='FACT';
USER# NAME   ......
±¾ÊµÀýÒÑÍêȫͨ¹ý²âÊÔ,µ¥Ïò,ÓÖÏòͬ²½¶¼¿ÉʹÓÃ.
--Ãû´Ê˵Ã÷£ºÔ´——±»Í¬²½µÄÊý¾Ý¿â
Ä¿µÄ——Ҫͬ²½µ½µÄÊý¾Ý¿â
Ç°6²½±ØÐëÖ´ÐÐ,µÚ6ÒÔºóÊÇһЩ¸¨ÖúÐÅÏ¢.
--1¡¢ÔÚÄ¿µÄÊý¾Ý¿âÉÏ£¬´´½¨dblink
drop public database link dblink_orc9 ......