Oracle CASE±í´ïʽ
CASE±í´ïʽ¿ÉÒÔÔÚSQLÖÐʵÏÖif-then-elseÐ͵ÄÂß¼£¬¶ø²»±ØʹÓÃPL/SQL¡£CASEµÄ¹¤×÷·½Ê½ÓëDECODE()ÀàËÆ£¬µ«Ó¦¸ÃʹÓÃCASE,ÒòΪËüÓëANSI¼æÈÝ¡£
CASEÓÐÁ½ÖÖ±í´ïʽ:
1. ¼òµ¥CASE±í´ïʽ,ʹÓñí´ïʽȷ¶¨·µ»ØÖµ.
Óï·¨:
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
ELSE default_result
END
Àý:
select product_id,product_type_id,
case product_type_id
when 1 then 'Book'
when 2 then 'Video'
when 3 then 'DVD'
when 4 then 'CD'
else 'Magazine'
end
from products
½á¹û:
PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
1 1 Book
2 1 Book
3 2 Video
4 2 &
Ïà¹ØÎĵµ£º
×Ô¼ºÔÚ×öÕâ¸ö³ÌÐòµÄʱºò¿´¹ýºÜ¶àµÄ×ÊÁÏ£¬ÉÏÍøÒ²²éÁ˲»ÉÙµÄ×ÊÁÏ£¬¿ÉÊǶà°ë˵µÄÊdzöÉñÈ뻯£¬ÔÆÀïÎíÀï...²»¹â¿´Á˲»Ã÷°×£¬¶øÇÒÊÇÓÐÃ÷°×Ò»µãµÄÈË£¬¿´ÁËÒ²±äµÄÓÐЩģºýÁË¡£
ÕâÀïÎÒÑÚÊÎÒ»Ì×ÍêÕûµÄjava jdbc Á¬½ÓOracle9iµÄ·¶Àý¡£
package com.lxh.dbcon;//´ò°ü
import ......
ÓкܶàÓ¦ÓÃÏîÄ¿, ¸ÕÆð²½µÄʱºòÓÃMYSQLÊý¾Ý¿â»ù±¾ÉÏÄÜʵÏÖ¸÷ÖÖ¹¦ÄÜÐèÇó£¬Ëæ×ÅÓ¦ÓÃÓû§µÄÔö¶à£¬Êý¾ÝÁ¿µÄÔö¼Ó£¬MYSQL½¥½¥µØ³öÏÖ²»¿°ÖظºµÄÇé¿ö£º
Á¬½ÓºÜÂýÉõÖÁå´»ú£¬ÓÚÊǾÍÓаÑÊý¾Ý´ÓMYSQLǨµ½ORACLEµÄÐèÇó£¬Ó¦ÓóÌÐòÒ²ÒªÏàÓ¦×öһЩÐ޸ġ£±¾ÈË×ܽá³öÒÔϼ¸µã×¢ÒâÊÂÏϣÍû¶Ô´ó¼ÒÓÐËù°ïÖú¡£
1£® ×Ô¶¯Ôö³¤µÄÊý¾ÝÀàÐ ......
Oracle±Ê¼Ç
l ¹ØÓÚTRUNCº¯Êý
SELECT
RELATED_ID ,
DOC_ID ,
CAT_ID ,
CAT_CODE ,
RELEASE_DATE ,
&n ......
Ö»ÊÇsqlserver ÌṩµÄÔ¶³ÌÊý¾Ý·ÃÎʺ¯Êý; ÔÚ±¾µØsqlserver ÖÐÈ¡ÍⲿÊý¾ÝÔ´Êý¾Ýʱºò¿ÉÓÃ;
¶ÔÁ¬½Ó±¾µØ oracle ²Ù×÷Ô¶³Ì oracle ²»ÄÜʹÓÃ; ²âÊÔ: pl/sql ÖÐʹÓÃ:
select * from openrowset(................); ÎÞЧ!!!!!!!!!!!!!!
ÔÚoracle ÖÐÐèÒª·ÃÎÊÔ¶³ÌÊý¾Ý,ÐèÒª½¨Á¢Ò»Á¬½ÓÔ¶³Ìoracle µÄ dblink ;
ÔÙÓÃÈçÏ·½ ......
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......