发表于 2009-1-5 19:48
| 
璺ㄨ秺Oracle鍜孧SSQL鍏崇郴鏁版嵁搴撳紑鍙戔曗01寮鍏宠鍙
鍙戝竷: 2008-6-30 22:35 | 浣滆: admin | 鏌ョ湅: 24娆
璺ㄨ秺Oracle鍜孧SSQL鍏崇郴鏁版嵁搴撳紑鍙戔曗01寮鍏宠鍙
鐑
銆愬瓧浣擄細灏 澶с
璺ㄨ秺Oracle鍜孧SSQL鍏崇郴鏁版嵁搴撳紑鍙戔曗01寮鍏宠鍙
浣滆咃細- 鏂囩珷鏉ユ簮锛- 鐐瑰嚮鏁帮細643 鏇存柊鏃堕棿锛2007-8-10 
1. 姒傝堪
浣滀负鏈郴鍒楃殑绗竴绡囨枃绔狅紝鎴戞兂浠庝竴涓畝鍗曠殑涓撻寮濮嬨傛妧鏈殑鏂囩珷閫氬父閮芥瘮杈冩灟鐕ヤ箯鍛筹紝鎴戝敖閲忓啓鐨勯氫織鏄撴噦锛屽苟浣跨敤涓浜涙湁瓒ｇ殑渚嬪瓙銆
绫讳技浜庨珮绾ц瑷锛孫racle鍜孧SSQL閮芥彁渚涗簡寮鍏宠鍙ユ潵澶勭悊澶氬垎鏋濋昏緫銆傛湰鏂囧皢璁茶堪涓ょ鏁版嵁搴撳紑鍏宠鍙ョ殑鐩稿悓鍜屼笉鍚屼箣澶勶紝骞朵粙缁嶄竴浜涗娇鐢ㄧ粡楠屻
2. 浣跨敤鏉′欢璇彞琛ㄨ揪澶氶噸閫夋嫨
2.1 PL/SQL鐨勬潯浠惰鍙
銆銆鍦≒L/SQL涓紝鍙互浣跨敤IF鈥HEN鈥LSIF鈥ND IF鏉ヨ〃绀哄閲嶉夋嫨銆
銆銆浠ヤ笅鏄祴璇曚緥瀛愶細
IF v_grade = 'A' THEN
v_result := 'Excellent';
ELSIF v_grade = 'B' THEN
v_result := 'Very Good';
ELSIF v_grade = 'C' THEN
v_result := 'Good';
ELSIF v_grade = 'D' THEN
v_result := 'Fair';
ELSIF v_grade = 'F' THEN
v_result := 'Poor';
ELSE
v_result := 'No such grade';
END IF;
銆銆涓婇潰鐨勪唬鐮佹寜鐓у彉閲弙_grade鐨勫艰绠楀嚭涓嶅悓鐨勭瓑绾с侾L/SQL鐨凟LSIF鏄笓闂ㄨ璁℃垚鏀寔澶氶噸閫夋嫨鐨勶紝閬垮厤澶氬眰閫夋嫨銆
2.2 T-SQL鐨勬潯浠惰鍙
銆銆T-SQL鎻愪緵浜嗙被浼间簬C璇█鐨勫灞傛潯浠惰鍙ャ備笂涓灏忚妭鐨勪緥瀛愬彲浠ヨ〃杈句负锛
IF @grade = 'A'
BEGIN
@result := 'Excellent';
END
ELSE IF @grade = 'B'
BEGIN
@result := 'Very Good';
END
ELSE IF @grade = 'C'
BEGIN
@result := 'Good'
END
ELSE IF @grade = 'D'
BEGIN
@result := 'Fair'
END
ELSE IF @grade = 'F'
BEGIN
@result := 'Poor'
END
ELSE
BEGIN
@result := 'No such grade'
END
銆銆涓婅堪璇彞铏界劧鐪嬭捣鏉ュ拰PL/SQL鐨凟LSIF宸笉澶氾紝浣嗗疄闄呬笂鏄娇鐢ㄥ灞傞夋嫨銆
2. 浣跨敤寮鍏宠鍙ヨ〃杈惧閲嶉夋嫨
銆銆寮鍏宠鍙ュ氨鏄笓闂ㄤ负绠鍖栧閲嶉夋嫨鑰岃璁＄殑锛屾墍浠ワ紝濡傛灉鍙兘锛屽敖閲忎娇鐢ㄤ箣銆
2.1 Oracle鐨勫紑鍏宠鍙
2.1.1 DECODE鍑芥暟
銆銆鍦9i浠ュ墠鐨勭増鏈腑锛孫racle鍙彁渚涗簡DECODE涓涓紑鍏冲嚱鏁帮紝骞朵笖璇ュ嚱鏁板彧鑳藉湪SQL璇彞涓娇鐢紝涓嶈兘鐩存帴鍦≒L/SQL鐨勫叾浠栫被鍨嬭鍙ヤ腑浣跨敤銆
銆銆DECODE鐨勮娉曚负锛
DECODE(expr,search,result[,search,result]...[,default])
銆銆渚嬪锛屼笂涓灏忚妭涓殑渚嬪瓙鍙互鐢―ECODE琛ㄨ揪涓猴細
select DECODE(v_grade, 'A', 'Excellent',
'B', 'Very Good',
'C', 'Good',
'D', 'Fair',
'F', 'Poor',
'No such grade')
into v_result
from dual;
銆銆杩欓噷鍋囪v_grade鍜寁_result鍧囦负PL/SQL涓殑鍙橀噺銆
2.1.2 CASE璇彞
銆銆涓轰簡绠鍖朠L/SQL鐨勬帶鍒舵祦绋嬶紝浠9i寮鍙戯紝PL/SQL鎻愪緵浜嗙被浼间簬C璇█SWITCH璇彞鐨凜ASE璇彞銆
銆銆浠庤〃鐜板舰寮忔潵鐪嬶紝CASE璇彞鏈変袱绉嶈娉曘
銆銆璇硶涓锛
searched_case_statement ::=
[ <
CASE { WHEN boolean_expression THEN {statement;} ... }...
[ ELSE {statement;}... ]
END CASE [ label_name ];
銆銆璇硶浜岋細
simple_case_statement ::=
[ <
CASE case_operand
{ WHEN when_operand THEN {statement;} ... }...
[ ELSE {statement;}... ]
END CASE [ label_name ];
銆銆杩欓噷鐨刲abel_name鏄綔涓烘爣绛句娇鐢ㄧ殑锛屽彲蹇界暐銆
銆銆DECODE鐨勫姛鑳界浉褰撲簬绗簩绉嶈娉曠殑鍔熻兘銆
銆銆鎴戜滑鐢ㄤ袱绉嶄笉鍚岀殑璇硶鍒嗗埆鏉ヨ〃杈惧墠闈㈢殑渚嬪瓙銆
銆銆璇硶涓琛ㄨ揪锛
CASE
WHEN v_grade = 'A' THEN
v_result := 'Excellent';
WHEN v_grade = 'B' THEN
v_result := 'Very Good';
WHEN v_grade = 'C' THEN
v_result := 'Good';
WHEN v_grade = 'D' THEN
v_result := 'Fair';
WHEN v_grade = 'F' THEN
v_result := 'Poor';
ELSE
v_result := 'No such grade';
END CASE;
銆銆璇硶浜岃〃杈撅細
CASE v_grade
WHEN 'A' THEN
v_result := 'Excellent';
WHEN 'B' THEN
v_result := 'Very Good';
WHEN 'C' THEN
v_result := 'Good';
WHEN 'D' THEN
v_result := 'Fair';
WHEN 'F' THEN
v_result := 'Poor';
ELSE
v_result := 'No such grade';
END CASE;
銆銆鍙互鐪嬪嚭锛岃娉曚竴鐨勮〃杈捐兘鍔涜寮轰簬璇硶浜岋紝鍥犱负璇硶浜屾槸鍗曞兼瘮杈冿紝鑰岃娉曚竴鏄〃杈惧紡璁＄畻銆傞氬父锛屽嚒鏄娉曚簩鑳借〃杈剧殑锛岃娉曚竴鍧囪兘琛ㄨ揪锛屽弽涔嬪垯涓嶇劧銆備緥濡傦紝璇硶涓鍙互鏂逛究鐨勫垽鏂ぇ浜庢垨鑰呭皬浜庣瓑鍏崇郴锛岃娉曚簩鍒欏彧鑳藉垽鏂瓑浜庛
銆銆娉ㄦ剰锛孫racle骞舵病鏈夋彁渚涘湪SQL璇彞涓娇鐢ㄧ殑CASE鍑芥暟銆
2.1.3 鐢―ECODE鏉ヨ绠楀鏉傞昏緫
銆銆鍓嶉潰璁插埌锛孋ASE璇彞鐨勮娉曚簩鐨勮〃杈捐兘鍔涙湁闄愩傜敱浜嶥ECODE鐨勫姛鑳界浉褰撲簬CASE璇彞鐨勮娉曚簩锛屽洜姝ECODE鐨勮〃杈捐兘鍔涗篃鏄湁闄愮殑銆傚浜9i涔嬪墠鐗堟湰鐨勭敤鎴凤紝鏈夋椂闈复鐫琛ㄨ揪鑳藉姏涓嶅鐨勯棶棰樸傜壒鍒槸浠嶮SSQL杩佺Щ鏃讹紝鐢变簬MSSQL鎻愪緵浜咰ASE璇彞锛岄犳垚琛ㄨ揪鑳藉姏鐨勪笉涓鑷淬
銆銆杩欓噷涓鸿鑰呮彁渚涗竴绉嶆妧宸э紝浣跨敤DECODE鏉ヨ〃杈惧鏉傜殑鍏充簬锛屼緥濡傚ぇ浜庨昏緫銆
銆銆鍋囧畾鏈変竴寮犺瘉鍒镐氦鏄撹〃fsjour锛屽叾涓殑鍒梥tock_code琛ㄧず璇佸埜浠ｇ爜锛屾垜浠娇鐢―ECODE鏉ヨ绠楀嚭stock_code鎵灞炵殑鑲＄エ绉嶇被銆
銆銆鍏惰鍒欎负锛氬綋stock_code鍦600000鍜699999涔嬮棿涓衡滄勃A鈥濓紝鍦000000鍜099999涔嬮棿涓衡滄繁A鈥濓紝鍚﹀垯涓衡滃叾浠栤濄
select DECODE(1, (select 1 from dual
where stock_code between '600000' and '699999'), '娌狝',
(select 1 from dual
where stock_code between '000000' and '099999'), '娣盇',
'鍏朵粬')
from fsjour;
銆銆杩欓噷鐨勬妧宸ф槸灏嗗鏉傜殑鏉′欢杞Щ鍒板瓙璇彞鐨刉HERE閮ㄥ垎銆
銆銆濡傛灉涓や釜瀛愯鍙ョ殑缁撴灉鐩稿悓锛岄偅涔堝墠闈竴涓殑缁撴灉灏嗚杩斿洖銆
銆銆杩愮敤杩欑鎶宸э紝鍙互鐢―ECODE瀹炵幇CASE璇彞璇硶涓鐨勫姛鑳斤紝浣嗘槸璇彞浼氭樉寰楃浉褰撳鏉傘
2.2 MSSQL鐨勫紑鍏宠鍙
銆銆濡傚悓PL/SQL涓鏍凤紝T-SQL鏀寔鐨凜ASE璇彞涔熸湁闆峰悓鐨勪袱绉嶆牸寮忋
銆銆璇硶涓锛
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END
銆銆璇硶浜岋細
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END
銆銆涓嶱L/SQL涓嶅悓鐨勬槸锛孴-SQL鍙厑璁稿湪SQL璇彞涓娇鐢–ASE锛屽鍚孫racle鐨凞ECODE鍑芥暟閭ｆ牱銆
銆銆渚嬪锛2.1.1鑺備腑鐨勪緥瀛愬彲浠ヨ〃杈句负锛
select CASE @grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
WHEN 'D' THEN 'Fair'
WHEN 'F' THEN 'Poor'
ELSE 'No such grade'
END
