Oracle中case語句

-- Create table

create table HKB_TEST3

(

  USER_ID     NUMBER(4),

  AGE         NUMBER(4),

  USER_NUMBER NUMBER(4)

)

SQL> select * from hkb_test3;

 

USER_ID   AGE USER_NUMBER

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

      1    12

      2    14

      3    16

      4    11

例子：

update hkb_test3

   set user_number = (select b.user_number

                        from (select a.user_id,

                                     case

                                       when a.age = 12 then

                                        '6'

                                       when a.age = 14 then

                                        '7'

                                       when a.age = 16 then

                                        '8'

                                       else

                                        '9'

                                     end user_number

                                from hkb_test3 a) b

                       where b.user_id = hkb_test3.user_id);

SQL> commit;

 

Commit complete

SQL> select * from hkb_test3;

 

USER_ID   AGE USER_NUMBER

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

      1    12           6

      2    14           7

      3    16           9

      4    11           9