除了循环是否还有更好的解决办法

[魏飞翔]

假设有个表的结构如下,我要实现的是把列名是以tag为前缀,而且值不为空的数据存到另外一个表中
,我现在是在一个存储过程中写的双循环，外层循环行,内层循环列,并判断列名和值,但是效率太低，是否还有更好的解决方案呢.

表结构:


关键代码:

[小灰狼W]

可以去掉一层循环 for x in (select table_name,column_name from user_tab_columns where table_name='TDM_CUSTOMERINFO') loop if x.column_name like 'TAG%' then execute immediate 'insert into tdm_bqdm select custid,'||x.column_name||',d_updatetime from '||x.table_name; commit; end if;

[Z_B_Hrunqian]

for x in (select column_name,'where '||column_name||' is not null' col from user_tab_columns where table_name='TDM_CUSTOMERINFO' and column_name like 'TAG%';) loop execute immediate ' insert into tdm_bqdm select custid,'''||x.column_name||''',d_updatetime from TDM_CUSTOMERINFO '||x.col; end loop ;

[魏飞翔]

引用 1 楼 wildwave 的回复:

可以去掉一层循环
for x in (select table_name,column_name from user_tab_columns where table_name='TDM_CUSTOMERINFO') loop
if x.column_name like 'TAG%' then
execute immediate 'insert into tdm_bqdm select custid,'||x.column_name||',d_updatetime from '||x.table_name;
commit;
end if;

我另外一个表存的是Id,和单个值

[魏飞翔]

目前的解决办法是，一次循环查询所有字段，然后再 insert ...select ... from union all select ...