Oracle Decodeº¯ÊýµÄÓ÷¨
DECODEº¯ÊýÊÇORACLE PL/SQLÊǹ¦ÄÜÇ¿´óµÄº¯ÊýÖ®Ò»£¬Ä¿Ç°»¹Ö»ÓÐORACLE¹«Ë¾µÄSQLÌṩÁ˴˺¯Êý£¬ÆäËûÊý¾Ý¿â³§É̵ÄSQLʵÏÖ»¹Ã»Óд˹¦ÄÜ¡£DECODEÓÐʲôÓÃÍ¾ÄØ£¿ Ïȹ¹ÔìÒ»¸öÀý×Ó£¬¼ÙÉèÎÒÃÇÏë¸øÖÇÐÇÖ°Ô±¼Ó¹¤×Ê£¬Æä±ê×¼ÊÇ£º¹¤×ÊÔÚ8000ÔªÒÔϵĽ«¼Ó20£¥£»¹¤×ÊÔÚ8000ÔªÒÔÉϵļÓ15£¥£¬Í¨³£µÄ×ö·¨ÊÇ£¬ÏÈÑ¡³ö¼Ç¼ÖеŤ×Ê×Ö¶ÎÖµ? select salary into var-salary from employee£¬È»ºó¶Ô±äÁ¿var-salaryÓÃif-then-else»òchoose caseÖ®ÀàµÄÁ÷¿ØÖÆÓï¾ä½øÐÐÅжϡ£ Èç¹ûÓÃDECODEº¯Êý£¬ÄÇôÎÒÃǾͿÉÒÔ°ÑÕâЩÁ÷¿ØÖÆÓï¾äÊ¡ÂÔ£¬Í¨¹ýSQLÓï¾ä¾Í¿ÉÒÔÖ±½ÓÍê³É¡£ÈçÏ£ºselect decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee ÊDz»ÊǺܼò½à£¿ DECODEµÄÓï·¨£ºDECODE(value,if1,then1,if2,then2,if3,then3,...,else)£¬±íʾÈç¹ûvalueµÈÓÚif1ʱ£¬DECODEº¯ÊýµÄ½á¹û·µ»Øthen1,...,Èç¹û²»µÈÓÚÈκÎÒ»¸öifÖµ£¬Ôò·µ»Øelse¡£³õ¿´Ò»Ï£¬DECODE Ö»ÄÜ×öµÈÓÚ²âÊÔ£¬µ«¸Õ²ÅÒ²¿´µ½ÁË£¬ÎÒÃÇͨ¹ýһЩº¯Êý»ò¼ÆËãÌæ´úvalue£¬ÊÇ¿ÉÒÔʹ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) --
Ïà¹ØÎĵµ£º
¡¡¡¡´ÓÓαêÌáÈ¡Êý¾Ý
¡¡¡¡´ÓÓαêµÃµ½Ò»ÐÐÊý¾ÝʹÓÃFETCHÃüÁÿһ´ÎÌáÈ¡Êý¾Ýºó£¬Óα궼ָÏò½á¹û¼¯µÄÏÂÒ»ÐС£Óï·¨ÈçÏ£º
FETCH cursor_name INTO variable[,variable,...]
¡¡¡¡¶ÔÓÚSELECT¶¨ÒåµÄÓαêµÄÿһÁУ¬FETCH±äÁ¿ÁÐ±í¶¼Ó¦¸ÃÓÐÒ»¸ö±äÁ¿ÓëÖ®Ïà¶ÔÓ¦£¬±äÁ¿µÄÀàÐÍÒ²ÒªÏàͬ¡£
¡¡¡¡Àý£º
SET SERVERIUTPUT ON
DECLARE
......
1. ¸´ÖƱí½á¹¹¼°ÆäÊý¾Ý£º
create table table_name_new as select * from table_name_old
2. Ö»¸´ÖƱí½á¹¹£º
create table table_name_new as select * from table_name_old where 1=2;
»òÕߣº
create table table_name_new like table_name_old
3. Ö»¸´ÖƱíÊý¾Ý£º
Èç¹ûÁ½¸ö±í½á¹¹Ò»Ñù£º
insert into table_name_ ......
Introduction to Schema Objects
A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types o ......
¶ÔÓÚOracle£¬±ÊÕßÒ²ÊÇÒ»¸ö³õѧÕߣ¬ÈçÎÄÕÂÖеIJ»×ãÖ®´¦»¹Çë¸÷Î»ÍøÓÑÖ¸Õý
ÏÂÃæÎÒÃÇ¿ªÊ¼°²×°Oracle£¬ÔÚ°²×°Ö®Ç°£¬ÎÒÃÇÏÈÀ´ÁгöһЩÔÚ°²×°OracleÊý¾Ý¿âʱÐèҪעÒâµÄÊÂÏOracle°²×°³ÌÐò£¨OUI£©²»Ö§³Ö×Ö·û½çÃæ°²×°£¬Òò´Ë°²×°·þÎñÆ÷±ØÐëÓÐX windowsÖ§³Ö ÏÂÃæÊÇÎÒ°²×°OracleÊý¾Ý¿âµÄϵͳÐÅÏ¢
²Ù×÷ϵ ......
¶ÔÓÚ Oracle µÄ rownum ÎÊÌ⣬ºÜ¶à×ÊÁ϶¼Ëµ²»Ö§³Ö>,>=,=,between...and£¬Ö»ÄÜÓÃÒÔÉÏ·ûºÅ(<¡¢<=¡¢!=)£¬²¢·Ç˵ÓÃ>,>=,=,between..and ʱ»áÌáʾSQLÓï·¨´íÎ󣬶øÊǾ³£ÊDz鲻³öÒ»Ìõ¼Ç¼À´£¬»¹»á³öÏÖËÆºõÊÇĪÃûÆäÃîµÄ½á¹ûÀ´£¬ÆäʵÄúÖ»ÒªÀí½âºÃÁËÕâ¸ö rownum αÁеÄÒâÒå¾Í²»Ó¦¸Ã¸Ðµ½¾ªÆæ£¬Í¬ÑùÊÇαÁУ¬rownum Ó ......