Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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)),
¡¡¡¡¡¡¡¡s


Ïà¹ØÎĵµ£º

jdbcÁ¬½ÓOracle

      ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½ÓOracle£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
    ½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
    ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
    Ê×ÏÈ£¬Ô ......

oracleµÄTX lockÐÅÏ¢ÔÚÄÄÀ

ÎÒÃǶ¼ÖªµÀORACLE²»»á·¢ÉúËøÉý¼¶£¬Ëø¶ÔÓÚORACLEÀ´Ëµ²¢²»ÊÇϡȱ×ÊÔ´£¬ÎªÊ²Ã´DML lock¶ÔÓÚORACLEÀ´Ëµ²»ÊÇϡȱ×ÊÔ´£¬ÏÂÃæÀ´Ñ°ÕҴ𰸡£
SQL> select * from emp where sal>2999;
EMPNO ENAME      JOB         MGR HIREDATE     ......

ѧϰOracleÖÐSequencesµÄʹÓÃ

1. ´´½¨ Sequence
ʹÓÃÈçÏÂÃüÁîн¨sequence(Óû§ÐèÒªÓÐCREATE SEQUENCE »òÕßCREATE ANY SEQUENCEȨÏÞ):
CREATE  SEQUENCE test_sequence
INCREMENT  BY   1   --  ÿ´Î¼ÓµÄ¸öÊý¾Ý 
START  WITH   1   --  ´Ó1¿ªÊ¼¼ÆÊý 
NOMAXVALUE& ......

oracle job²»ÔËÐÐ ½â¾ö·½°¸

1) Instance in RESTRICTED SESSIONS mode?
¡¡¡¡Check if the instance is in restricted sessions mode:
¡¡¡¡select instance_name,logins from v$instance;
¡¡¡¡If logins=RESTRICTED, then:
¡¡¡¡alter system disable restricted session;
¡¡¡¡^-- Checked!
¡¡¡¡2) JOB_QUEUE_PROCESSES=0
¡¡¡¡Make sure th ......

oracle job²»ÔËÐÐ ½â¾ö·½°¸

1) Instance in RESTRICTED SESSIONS mode?
¡¡¡¡Check if the instance is in restricted sessions mode:
¡¡¡¡select instance_name,logins from v$instance;
¡¡¡¡If logins=RESTRICTED, then:
¡¡¡¡alter system disable restricted session;
¡¡¡¡^-- Checked!
¡¡¡¡2) JOB_QUEUE_PROCESSES=0
¡¡¡¡Make sure th ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ