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

OracleÖÐdecode()º¯數ʹÓü¼ÇÉ

decode()º¯數ʹÓü¼ÇÉ
·Èí¼þ»·¾³£º
1¡¢Windows NT4.0+ORACLE 8.0.4
2¡¢ORACLE°²×°Â·¾¶Îª£ºC:\ORANT
·º¬Òå½âÊÍ£º
decode(Ìõ¼þ,Öµ1,·­ÒëÖµ1,Öµ2,·­ÒëÖµ2,...Öµn,·­ÒëÖµn,ȱʡֵ)
¸Ãº¯ÊýµÄº¬ÒåÈçÏ£º
IF Ìõ¼þ=Öµ1 THEN
¡¡¡¡¡¡¡¡RETURN(·­ÒëÖµ1)
ELSIF Ìõ¼þ=Öµ2 THEN
¡¡¡¡¡¡¡¡RETURN(·­ÒëÖµ2)
¡¡¡¡¡¡¡¡......
ELSIF Ìõ¼þ=Öµn THEN
¡¡¡¡¡¡¡¡RETURN(·­ÒëÖµn)
ELSE
¡¡¡¡¡¡¡¡RETURN(ȱʡֵ)
END IF
·        ʹÓ÷½·¨£º
1¡¢±È½Ï´óС
select decode(sign(±äÁ¿1-±äÁ¿2),-1,±äÁ¿1,±äÁ¿2) from dual; --È¡½ÏСֵ
sign()º¯Êý¸ù¾Ýij¸öÖµÊÇ0¡¢ÕýÊý»¹ÊǸºÊý£¬·Ö±ð·µ»Ø0¡¢1¡¢-1
ÀýÈ磺
±äÁ¿1=10£¬±äÁ¿2=20
Ôòsign(±äÁ¿1-±äÁ¿2)·µ»Ø-1£¬decode½âÂë½á¹ûΪ“±äÁ¿1”£¬´ïµ½ÁËÈ¡½ÏСֵµÄÄ¿µÄ¡£
2¡¢±í¡¢ÊÓͼ½á¹¹×ª»¯
ÏÖÓÐÒ»¸öÉÌÆ·ÏúÊÛ±ísale£¬±í½á¹¹Îª£º
month¡¡¡¡¡¡ char(6)¡¡¡¡¡¡¡¡¡¡ --Ô·Ý
sell¡¡¡¡¡¡¡¡number(10,2)¡¡¡¡¡¡--ÔÂÏúÊÛ½ð¶î
ÏÖÓÐÊý¾ÝΪ£º
200001¡¡¡¡1000
200002¡¡¡¡1100
200003¡¡¡¡1200
200004¡¡¡¡1300
200005¡¡¡¡1400
200006¡¡¡¡1500
200007¡¡¡¡1600
200101¡¡¡¡1100
200202¡¡¡¡1200
200301¡¡¡¡1300
ÏëҪת»¯ÎªÒÔϽṹµÄÊý¾Ý£º
year¡¡¡¡¡¡char(4)¡¡¡¡¡¡¡¡¡¡ --Äê·Ý
month1¡¡¡¡number(10,2)¡¡¡¡¡¡--1ÔÂÏúÊÛ½ð¶î
month2¡¡¡¡number(10,2)¡¡¡¡¡¡--2ÔÂÏúÊÛ½ð¶î
month3¡¡¡¡number(10,2)¡¡¡¡¡¡--3ÔÂÏúÊÛ½ð¶î
month4¡¡¡¡number(10,2)¡¡¡¡¡¡--4ÔÂÏúÊÛ½ð¶î
month5¡¡¡¡number(10,2)¡¡¡¡¡¡--5ÔÂÏúÊÛ½ð¶î
month6¡¡¡¡number(10,2)¡¡¡¡¡¡--6ÔÂÏúÊÛ½ð¶î
month7¡¡¡¡number(10,2)¡¡¡¡¡¡--7ÔÂÏúÊÛ½ð¶î
month8¡¡¡¡number(10,2)¡¡¡¡¡¡--8ÔÂÏúÊÛ½ð¶î
month9¡¡¡¡number(10,2)¡¡¡¡¡¡--9ÔÂÏúÊÛ½ð¶î
month10¡¡¡¡number(10,2)¡¡¡¡¡¡--10ÔÂÏúÊÛ½ð¶î
month11¡¡¡¡number(10,2)¡¡¡¡¡¡--11ÔÂÏúÊÛ½ð¶î
month12¡¡¡¡number(10,2)¡¡¡¡¡¡--12ÔÂÏúÊÛ½ð¶î
½á¹¹×ª»¯µÄSQLÓï¾äΪ£º
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
¡¡¡¡¡¡¡¡select
¡¡¡¡¡¡¡¡substrb(month,1,4),
¡¡¡¡¡¡¡¡sum(decode(substrb(month,5,2),'01',sell,0)),
¡¡¡¡¡¡¡¡sum(decode(substrb(month,5,2),'02',sell,0)),
¡¡¡¡¡¡¡¡sum(decode(substrb(month,5,2),'03',sell,0)),
¡¡¡¡¡¡¡¡sum(decode(substrb(month,5


Ïà¹ØÎĵµ£º

linux oracle °²×°

linuxÏÂoracle°²×°£º
Oracle¹«Ë¾Ðû³ÆÔÚLinuxϰ²×°Oracle9iÊý¾Ý¿âÖÁÉÙÒªÓÐ512MBµÄÄÚ´æºÍÖÁÉÙ1GB»òÕßÁ½±¶     
ÄÚ´æ´óСµÄ½»»»¿Õ¼ä£¬¶ÔÓÚϵͳÄÚ´æ´óÓÚ2GBµÄ·þÎñÆ÷£¬½»»»¿Õ¼ä¿ÉÒÔ½éÓÚ2GB—4GBÖ®¼ä¡£     
Èç¹ûÊÇΪÁËÔÚһ̨½öÓÐ256MÄÚ´æµÄÆÕͨPC»úÉÏÊÔÓÃOracle9 ......

Oracle µÄÊý¾Ýµ¼Èëµ¼³ö¼° Sql Loader (sqlldr) µÄÓ÷¨

Ô­ÎÄ:http://www.blogjava.net/Unmi/archive/2009/01/05/249956.html
ÔÚ Oracle Êý¾Ý¿âÖУ¬ÎÒÃÇͨ³£ÔÚ²»Í¬Êý¾Ý¿âµÄ±í¼ä¼Ç¼½øÐи´ÖÆ»òÇ¨ÒÆÊ±»áÓÃÒÔϼ¸ÖÖ·½·¨£º
1. A ±íµÄ¼Ç¼µ¼³öΪһÌõÌõ·ÖºÅ¸ô¿ªµÄ insert Óï¾ä£¬È»ºóÖ´ÐвåÈëµ½ B ±íÖÐ
2. ½¨Á¢Êý¾Ý¿â¼äµÄ dblink£¬È»ºóÓà create table B as select * from A ......

oracleÊý¾Ý×ÖµäС½á


ÏÂÃæ°´Àà±ðÁгöһЩORACLEÓû§³£ÓÃÊý¾Ý×ÖµäµÄ²éѯʹÓ÷½·¨¡£
   
    Ò»¡¢Óû§
    ²é¿´µ±Ç°Óû§µÄȱʡ±í¿Õ¼ä
    SQL>select username,default_tablespace from user_users;
    ²é¿´µ±Ç°Óû§µÄ½ÇÉ«
    SQL ......

oracleÊé¼®


ºÜ¶àÅóÓÑÒªÎÒ°ïÃ¦ÍÆ¼öÒ»ÏÂOracleµÄÈëÃÅÊé¼®£¬Äܹ»Á˽âOracleµÄ»ù±¾¸ÅÄî¡¢»ù±¾ÖªÊ¶µÄÄÇÖÖ¡£
ÎÒ¾ÍÃâΪÆäÄÑ£¬ÍƼö¼¸±¾¡£
Ê×ÏÈÎÒÏëÇ¿µ÷µÄÒ»µãÊÇ£¬ÈκÎÒ»±¾ÏµÍ³µÄOracleÊé¼®Ö»ÒªÈÏÕæ¶ÁÏÂÀ´£¬¶¼»áÓв»´íµÄÊÕ»ñ£¬¶ÁÊé×î¼É»äµÄÊÇ»¢Í·Éßβ£¬Ç³³¢ÔòÖ¹¡£
1.µÚÒ»±¾ÒªÍƼö¸ø´ó¼ÒµÄÊÇOracleµÄ¸ÅÄîÊֲᣬÕâ±¾ÊÖ²áÊÇÎÞÊýDBAѧϰµÄÆðµã£ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ