case语句在select中的用法，以及，insert和select和case合用的例子

 

 

 

SQL> select tag_name,tp_name,value from ins_dynamic;

TAG_NAME     TP_NAME     VALUE

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

a                    NoString       1

b                    NoString       2

 

 

 

 

 

SQL> select (case when value='1' then 'value=1' when value='2' then 'value=2' else 'other value' end) tag_name1 from ins_dynamic;

 

 

TAG_NAME1

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

value=1

value=2

 

 

 

 

SQL> insert into ins_dynamic(tag_name,tp_name) select 'zzzz.'||tag_name,(case when value='1' then 'value=1' when value='2' then 'value=2' else 'other value' end) from ins_dynamic;

 

已创建2行。

SQL> select tag_name,tp_name,value from ins_dynamic;

TAG_NAME     TP_NAME     VALUE

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

a                    NoString       1

b                    NoString       2

zzz.a              value=1        NoString

zzz.b              value=2        NoString

最后写一个生猛的

DELETE FROM dict_column_info;

INSERT INTO dict_column_info(table_name,column_name,data_type,data_length,is_nullable,is_key) 

SELECT t1.table_name,t1.column_name,

(case when t1.data_type='VARCHAR2' then 1 

      when t1.data_type='DATE' then 2 

      when t1.data_type='NUMBER' AND t1.data_precision=22 AND t1.data_scale=6 then 3 

      when t1.data_type='NUMBER' AND t1.data_precision=10 AND t1.data_scale=0 then 4 

      else 0 end),

(case when t1.data_type='VARCHAR2' then t1.data_length 

      when t1.data_type='DATE' then 4 

      when t1.data_type='NUMBER' AND t1.data_precision=22 AND t1.data_scale=6 then 4 

      when t1.data_type='NUMBER' AND t1.data_precision=10 AND t1.data_scale=0 then 4 

      else 0 end),

(case when t1.nullable='Y' then 1 

      when t1.nullable='N' then 0 

      else -1 end), 

(case when (t1.table_name,t1.column_name) IN (SELECT t2.table_name,t2.column_name FROM all_cons_columns t2 where t2.owner = 'ISCS' AND t2.constraint_name IN (SELECT t3.constraint_name FROM all_constraints t3 where t3.owner = 'ISCS' AND t3.constraint_type = 'P')) then 1

      else 0 end)

from user_tab_columns t1;