过程如下:
1.在user_tab_columns找出所有含有bu_cd的表的名字。 select table_name,column_name
from user_tab_columns
where column_name= 'BU_CD ' 结果如下:
--------------------------------------
table_name column_name
--------------------------------------
ACCESSAUTHORITYMST BU_CD
ADDON_USE_MST BU_CD
APLVERSION_MST BU_CD
AUTONO_MG BU_CD 2.然后再查出以上表中bu_cd为10的纪录条数。
select count(*) from
( )
where bu_cd= '10 '
3.以上过程要求用一个sql语句实现。 望高手指点?多谢!!!
1.在user_tab_columns找出所有含有bu_cd的表的名字。 select table_name,column_name
from user_tab_columns
where column_name= 'BU_CD ' 结果如下:
--------------------------------------
table_name column_name
--------------------------------------
ACCESSAUTHORITYMST BU_CD
ADDON_USE_MST BU_CD
APLVERSION_MST BU_CD
AUTONO_MG BU_CD 2.然后再查出以上表中bu_cd为10的纪录条数。
select count(*) from
( )
where bu_cd= '10 '
3.以上过程要求用一个sql语句实现。 望高手指点?多谢!!!
select table_name,column_name,max(flag)keep(dense_rank last order by flag )over(partition by column_name) mxl
from(
select table_name,column_name ,row_number() over(order by column_name) flag
from user_tab_columns
where column_name= 'BU_CD ' ) ) where mxl = 10
cur sys_refcursor;
begin
for rec in (select table_name,column_name
from user_tab_columns
where column_name= 'BU_CD')
loop
open cur for 'select null from ' || rec.table_NAME||' where '||rec.column_name||' = ''10''' ;
dbms_output.put_line('select null from ' || rec.table_NAME||' where '||rec.column_name||' = ''10''');
dbms_output.put_line(to_char(cur%rowcount));
close cur;
end loop;
end;
create type mytable as table of integer;declare
atable mytable ;
begin
for rec in (select table_name,column_name
from user_tab_columns
where column_name= 'BU_CD')
loop
execute immediate 'select count(*) from ' || rec.table_NAME||' where '||rec.column_name||' = ''10'' ' bulk collect into atable ;
dbms_output.put_line('select 1 from ' || rec.table_NAME||' where '||rec.column_name||' = ''10'' ');
dbms_output.put_line(to_char(atable.count));
end loop;
end;
运用5楼的方法,显示如下错误,再次请教一下,多谢!
ORA-00923: FROM keyword not found where expected
ORA-06512: at line 8
问题是,怎么通过程序进行实现,把数据放到数据集里去呢?
运用临时表不太好,有没有其他的方法。
望高人多多指点!多谢!!declare
num varchar2(10);
cur sys_refcursor;
begin
for rec in (select table_name,column_name
from user_tab_columns
where column_name='BU_CD')
loop
open cur for 'select count(*) from '|| rec.table_name ||' where '|| rec.column_name||'=''10''';
rec.column_name||'=''10''');
fetch cur into num;
dbms_output.put_line(rec.table_name||' '||num);
close cur;
end loop;
end;