Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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¸´Ï°£¨Ò»£© Ö®OracleÌåϵ½á¹¹

      ѧϰOracle DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬Ô­À´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·­Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
      µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......

[×ªÔØ]oracleÖв鿴Óû§È¨ÏÞ

1.²é¿´ËùÓÐÓû§£º
  select * from dba_users;
  select * from all_users;
  select * from user_users;
2.²é¿´Óû§»ò½ÇɫϵͳȨÏÞ(Ö±½Ó¸³Öµ¸øÓû§»ò½ÇÉ«µÄϵͳȨÏÞ)£º
  select * from dba_sys_privs;
  select * from user_sys_privs;
3.²é¿´½ÇÉ«(Ö»Äܲ鿴µÇ½Óû§ÓµÓеĽÇÉ«)Ëù°üº¬µÄÈ ......

oracle ÐÞ¸Ä×Ö¶ÎÀàÐ͵ķ½·¨£¨×ª£©

ÒòΪҵÎñÐèÒª,ÐÞÒªÐÞ¸Äij¸ö×Ö¶ÎÊý¾ÝÀàÐÍÓÐnumber(5),±äΪnumber(5,2)ÐÍ
ÒªÊÇûÓÐÊý¾ÝµÄ»°Ö±½ÓÓÃÒÔÏÂÓï¾ä¼´¿É
alter   table tb_test modify permile number(5,2);
µ«ÊÇÓÐÊý¾ÝµÄ»° ¾Í²»ÄÜÓÃÉÏÃæ·½·¨ÁË,
alter table tb_test add permile_temp number(5,2)
update tb_test set permile_temp=permile;
alter t ......

oracle job µãµÎ


³õʼ»¯Ïà¹Ø²ÎÊýjob_queue_processes
alter system set job_queue_processes=39 scope=spfile;//×î´óÖµ²»Äܳ¬¹ý1000 ;job_queue_interval = 10 //µ÷¶È×÷ҵˢÐÂÆµÂÊÃëΪµ¥Î»
job_queue_process ±íʾoracleÄܹ»²¢·¢µÄjobµÄÊýÁ¿£¬¿ÉÒÔͨ¹ýÓï¾ä¡¡¡¡
show parameter job_queue_process;
À´²é¿´oracleÖÐjob_queue_processµÄ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ