写一个存储过程,统计某用户下所有表的记录数,并将其一一对应的插入表hr.count_all中(hr是Oracle默认存在的账户)
创建count_all的sql语句:
create table hr.count_all(tablename varchar2(50),
count number);
以下PL/SQL块(1)是错误的,在插入一条记录后,循环到第二条记录时会报错
因为if块里面那个字符串v_select_sql一直在增长而没有清空,很显然构造的是非法的查询串;
(1):
declare
cursor c_tab_name is select table_name from user_tables;
tem_tab_name varchar2(20);
v_count number(8);
v_select_sql varchar(200) default 'select count(*) from emcd.';
v_insert_sql varchar2(200) default 'insert into hr.count_all values(';
begin
open c_tab_name;
loop
fetch c_tab_name into tem_tab_name;
exit when c_tab_name%notfound;
if tem_tab_name is not null then
dbms_output.put_line('table_name ==> '||tem_tab_name);
v_select_sql := v_select_sql||tem_tab_name;
dbms_output.put_line('v_select_sql ==> '||v_select_sql);
execut