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)。