oracle判斷語句怎麼寫

是存儲過程裏面的 IF/ELSE ？ 還是簡單的 DECODE ?

SQL> DECLARE

2 testvalue INT;

3 BEGIN

4 testvalue := 100;

5

6 IF testvalue > 100 THEN

7 dbms_output.put_line( '100+' );

8 ELSIF testvalue = 100 THEN

9 dbms_output.put_line( '100' );

10 ELSE

11 dbms_output.put_line( '100-' );

12 END IF;

13

14 END;

15 /

100

PL/SQL procedure successfully completed.

SQL> SELECT

2 DECODE(GROUPING(sale_item), 1, 'ALL', sale_item) AS iten,

3 SUM(sale_money) AS money

4 FROM

5 sale_report

6 GROUP BY

7 ROLLUP(sale_item);

ITEN MONEY

------ ----------

A 733285

B 2382

C 5738

ALL 741405

一個多條件判斷的sql:

select

oper.opid,

oper.user_name,

oper.user_host,

case

when oper.oper_type = 1 then 'System Manager'

when oper.oper_type = 2 then 'USER Manager'

end case,

case

when oper.oper_object_type = 1 then 'User'

when oper.oper_object_type = 2 then 'Role'

when oper.oper_object_type = 3 then 'Broker'

when oper.oper_object_type = 4 then 'QM Manager'

when oper.oper_object_type = 5 then 'User Group'

when oper.oper_object_type = 6 then 'Msg Flow'

when oper.oper_object_type = 7 then 'Queue'

end case

from esb_log_user_oper oper;

建表，測試數據：

create table test

（收款標誌 int）

insert into test values (1);

insert into test values (1);

insert into test values (1);

commit；執行：

select case

when a.cnt = b.cnt then

'未收款'

when a.cnt = d.cnt then

'已收款'

when c.cnt 0 then

'部分收款'

end 收款狀態

from (select count(*) cnt from test) a,

(select count(*) cnt from test where 收款標誌 = 1) b,

(select count(*) cnt from test where 收款標誌 = 2) c,

(select count(*) cnt from test where 收款標誌 = 3) d結果：

然後你自己換點其他數據測試一下吧，思路就這麼個思路了。

如果是select查詢做顯示的話select code, name, case when length(code) =3 then 1 when length(code)=6 then 2 when length(code)=9 then 3 end id from table；如果是要插入的話 update table set id =( case when length(code) =3 then 1 when length(code)=6 then 2 when length(code)=9 then 3 end)。