SQLÖеÄIF ELSE£¨CASEÓï¾äµÄʹÓã©
----start
´ó¼Ò¶ÔIF ELSEÓï¾ä¿ÉÄܶ¼ºÜÊìϤ£¬ËüÊÇÓÃÀ´¶Ô¹ý³Ì½øÐпØÖƵġ£ÔÚSQLµÄÊÀ½çÖÐCASEÓï¾äÓï¾äÓÐÀàËƵÄЧ¹û¡£ÏÂÃæ¼òµ¥µÄ½éÉÜCASEÓï¾äµÄÓ÷¨¡£¿¼ÂÇÏÂÃæµÄÇé¿ö£¬¼ÙÉèÓиöuser±í£¬¶¨ÒåÈçÏ£º
CREATE TABLE USER
(
NAME VARCHAR(20) NOT NULL,---ÐÕÃû
SEX INTEGER,---ÐÔ±ð£¨1¡¢ÄÐ 2¡¢Å®£©
BIRTHDAY DATE---ÉúÈÕ
);
CASEʹÓó¡ºÏ1£º°Ñuser±íµ¼³öÉú³ÉÒ»¸öÎļþ£¬ÒªÇóÐÔ±ðΪÄлòÅ®£¬¶ø²»ÊÇ1ºÍ2£¬Ôõô°ì£¿ÎÒÃÇ¿ÉÒÔÓÃÈçϵÄÓï¾ä´¦Àí£º
SELECT
NAME,
CASE SEX
WHEN 1 THEN 'ÄÐ'
ELSE 'Å®'
END AS SEX,
BIRTHDAY
from USER;
CASEʹÓó¡ºÏ2£º¼ÙÉèuserĿǰûÓÐÖµ£¬È»ºóÄãÍùuserµ¼ÈëÁËÒ»ÅúÊý¾Ý£¬µ«ÊǺܲ»ÐÒ£¬´í°ÑÄÐÉèÖóÉΪ2£¬¶ø°ÑÅ®ÉèÖóÉΪ1£¬ÏÖÔÚÒªÇóÄã±ä»»¹ýÀ´£¬Ôõô°ì£¿
·½·¨1£ºÊ¹ÓÃÈýÌõÓï¾ä£¬ÏÈ°Ñ2¸üгÉ3£¬½Ó×Å°Ñ1¸üгÉ2£¬×îºó°Ñ3¸üгÉ1£¬ºÜÂé·³£¬²»ÊÇÂð£¿
UPDATE USER SET SEX=3 WHERE SEX=2;
UPDATE USER SET SEX=1 WHERE SEX=3;
UPDATE USER SET SEX=2 WHERE SEX=1;
·½·¨2£ºÊ¹ÓÃCASEÓï¾ä
UPDATE USER SET SEX=
(
CASE SEX
WHEN 1 THEN 2
WHEN 2 THEN 1
ELSE SEX
END
);
ϸÐĵÄÅóÓÑ¿ÉÄÜÒѾ·¢ÏÖÁË£¬ÉÏÃæµÄ·½·¨1µÄÈýÌõÓï¾äµÄÖ´ÐÐ˳ÐòÓÐÎÊÌ⣬û´í£¬ÊÇÎÒ¹ÊÒâÄÇЩдµÄ£¬½ö½öÊÇ°Ñ1±ä³É2£¬°Ñ2±ä³É1¾ÍÄÇôÂé·³£¬¶øÇÒºÜÈÝÒ׳ö´í£¬ÏëÏóһϣ¬Èç¹ûÓкܶàÕâÑùµÄÖµÐèÒª±ä»»£¬ÄÇÊÇÒ»ÖÖʲôÑùµÄÇé¿ö¡£»¹ºÃ£¬ÎÒÃÇÓÐCASEÓï¾ä£¬ÓкöàÕâÑùµÄÖµÐèÒª±ä»»£¬CASEÓï¾äÒ²²»»á´æÔÚÎÊÌâ¡£¿ÉÄÜÓÐЩÅóÓÑ»¹ÊÇÓÐÒÉÂÇ£¬ÕâÑù×ö»á²»»áËÀÑ»·°¡£¿¹þ¹þ£¬Ïë·¨ºÜºÃ£¬Èç¹ûÄã·¢ÏÖÕâÑù×ö»áËÀÑ»·£¬Ò»¶¨Òª¸æËßIBM£¬ÎÒ·´Õýû·¢ÏÖ¡£
---¸ü¶à²Î¼û£ºDB2 SQL ¾«Òª
----ÉùÃ÷£º×ªÔØÇë×¢Ã÷³ö´¦¡£
----last update at 2009.9.27
----write by wave at 2009.9.23
----end
Ïà¹ØÎĵµ£º
SQL×¢Èë¼òµ¥·ÖÎö
ʾÀýÓï¾ä£º
select * from admintable where adminName like '%a%'
ÔÚ²éѯÖÐÎÒÃÇÒ»°ãÔÚaÕâ¸öµØ·½ÓɽçÃæ´«È벻ͬµÄÖµ£¬µ±ÎÒÃÇÔÚaÕâÀï´«ÈëµÄֵΪ“'”µ¥ÒýºÅʱ£¬Æ´´Õ³ÉµÄSQLÓï¾ä¾ÍÈçÏ£º
select * from admintable where adminName like '%'%'
Ö´ÐÐÕâ¾äÓï¾äÎÒÃǻᷢÏÖ³öÏÖÒÔÏÂÒì³££º
......
ÔÚʹÓÃODP.NET½øÐÐOracle±à³Ìʱ£¬ÓÐʱºòSQLÓï¾ä·Ç³£¸´ÔÓ£¬ÐèÒª²ÉÓö¯Ì¬¹¹Ôì²éѯÓï¾äµÄÇé¿ö£¬ÓÐÁ½ÖÖ·½·¨¿ÉÒÔ¹¹Ô춯̬µÄSQLÓï¾ä£¬²¢Ö´Ðзµ»Ø½á¹û¼¯¡£
1¡¢ÔÚÊý¾Ý·ÃÎʲ㹹ÔìSQLÓï¾ä
ÀýÈçÏÂÃæµÄÓï¾ä£¬½«¹¹ÔìÍêÕûµÄSQLÓï¾ä¸³Öµ¸øCommandText£¬ÔÙ´«µÝµ½Êý¾Ý¿â½øÐÐÖ´ÐУ¬·µ»Ø½á¹û¼¯¡£
loadCommand.CommandType = Com ......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Ò»)--αÁÐROWNUMʹÓü¼ÇÉ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(¶þ)--±êÁ¿×Ó²éѯ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Èý)--PackageµÄÓŵã
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(ËÄ)--ÅúÁ¿´¦Àí
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Îå)--µ÷Óô洢¹ý³Ì·µ»Ø½á¹û¼¯
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Áù)- ......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Ò»)--αÁÐROWNUMʹÓü¼ÇÉ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(¶þ)--±êÁ¿×Ó²éѯ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Èý)--PackageµÄÓŵã
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(ËÄ)--ÅúÁ¿´¦Àí
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Îå)--µ÷Óô洢¹ý³Ì·µ»Ø½á¹û¼¯
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Áù)- ......
----start
·²ÊÇÖªµÀÊý¾Ý¿âµÄÈ˶¼ÖªµÀSQL£¬·²ÊǶÔSQLÓÐÒ»µãÁ˽âµÄÈ˶¼¾õµÃSQLºÜ¼òµ¥£¬·²ÊÇÓÐÕâÖָоõµÄÈ˶¼ÊÇSQLµÃ³õ¼¶Óû§£¬ÒòΪËûѧ»áÁËÔö²éɾ¸Ä¾ÍÒÔΪÕâ¾ÍÊÇSQLµÄÈ«²¿¡£Ä¿Ç°µÄ´ó²¿·ÖÓ¦ÓÃÈí¼þ¶¼ÊÇÒÔÊý¾Ý¿âΪÖÐÐÄ£¬Ëæ×ÅÈí¼þµÄÔËÐУ¬Êý¾ÝÁ¿»áÔ½À´Ô½´ó¡£ÈçºÎÓüò½à¡¢¸ßЧµÄSQLÓï¾ä²Ù×÷Êý¾ÝÏÔµÃÔ½À ......