oracle decodeʹÓ÷½·¨
Êý¾Ý¿âÖо³£ÓÃ0,1 À´±êʶij×ֶΣ¬×÷Ϊ¿ª·¢ÈËÔ±¿ÉÄÜÖªµÀËüµÄÒâÒ壬µ«ÎÒÃÇÈÃËüÏÔʾÔÚGridÁбíÉϱØÐëÏÔʾËüµÄʵ¼Êº¬Ò壬һ°ãÎÒÃÇ¿ÉÒÔÔÚ´úÂëÖжÁÊý¾ÝԴʱ¿ÉÒÔ×÷´¦Àí£¬Í¬Ê±ORACLEÖÐÓÃdecodeÒ²ÊDz»´í·½·¨¡£
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
Àý:decode(CONTROLLER.Energy_Saving,0,'·ñ','ÊÇ') as IsSaving
½âÊÍÈçÏÂ:ÅжÏCONTROLLER.Energy_Saving×Ö¶Î,Èç¹ûΪ0,¾ÍÈÃ×Ö¶ÎÏÔʾΪ"·ñ"£¬Èç¹û²»Îª0£¬¾ÍÈÃËüÏÔʾΪ“ÊÇ”¡£
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
¸Õ²ÅÔÚEYGLEµÄ²©¿ÍÖп´µ½Ò»ÆªÌû×Ó£¬½²µÄÊÇDELETEµÄ²Ù×÷Á÷³Ì£¬ºÜ¼ò¶Ì£¬Í¬Ê±Ò²¾«±Ù¡£
===================================================================================
OracleÖУ¬Ò»¸öDelete²Ù×÷µÄÁ÷³Ì
ɾ³ý(DELETE)
1.Oracle¶ÁBlockµ½Buffer Cache(Èç¹û¸ÃBlockÔÚBufferÖв»´æÔÚ)
2.ÔÚredo log bufferÖмǼdelet ......
* Èç¹ûij¸öÔ¼ÊøÖ»×÷ÓÃÓÚµ¥¶ÀµÄ×ֶΣ¬¼´¿ÉÒÔÔÚ×ֶμ¶¶¨ÒåÔ¼Êø£¬Ò²¿ÉÒÔÔÚ±í¼¶¶¨ÒåÔ¼Êø£¬µ«Èç¹ûij¸öÔ¼Êø×÷ÓÃÓÚ¶à¸ö×ֶΣ¬
±ØÐëÔÚ±í¼¶¶¨ÒåÔ¼Êø
* ÔÚ¶¨ÒåÔ¼ÊøÊ±¿ÉÒÔͨ¹ýCONSTRAINT¹Ø¼ü×ÖÎªÔ¼ÊøÃüÃû£¬Èç¹ûûÓÐÖ¸¶¨£¬ORACLE½«×Ô¶¯ÎªÔ¼Êø½¨Á¢Ä¬ÈϵÄÃû³Æ
¶¨Òåprimary keyÔ¼Êø(µ¥¸ö×ֶΣ©
create t ......
·½·¨Ò»£¬Ê¹ÓÃSQL*Loader
Õâ¸öÊÇÓõĽ϶àµÄ·½·¨£¬Ç°Ìá±ØÐëoracleÊý¾ÝÖÐÄ¿µÄ±íÒѾ´æÔÚ¡£
´óÌå²½ÖèÈçÏ£º
1 ½«excleÎļþÁí´æÎªÒ»¸öÐÂÎļþ±ÈÈçÎļþÃûΪtext.txt£¬ÎļþÀàÐÍÑ¡Îı¾Îļþ£¨ÖƱí·û·Ö¸ô£©£¬ÕâÀïÑ¡Ô ......
¹Ø¼ü×Ö: ºÊýrank, over partitionʹÓÃ
ÅÅÁУ¨rank()£©º¯Êý¡£ÕâЩÅÅÁк¯ÊýÌṩÁ˶¨ÒåÒ»¸ö¼¯ºÏ£¨Ê¹Óà PARTITION ×Ӿ䣩£¬È»ºó¸ù¾ÝijÖÖÅÅÐò·½Ê½¶ÔÕâ¸ö¼¯ºÏÄÚµÄÔªËØ½øÐÐÅÅÁеÄÄÜÁ¦£¬ÏÂÃæÒÔscottÓû§µÄemp±íΪÀýÀ´ËµÃ÷rank over partitionÈçºÎʹÓÃ
1£©²éѯԱ¹¤Ð½Ë®²¢Á¬ÐøÇóºÍ
select deptno,ename,sal,
sum(sal)over ......