将表名与表记录数插入进新表tablecount
create or replace procedure countAll
is
begin
declare
cursor c_tab is select table_name from user_tables where table_name like 'CP_%'; --声明游标
tem_name varchar2(30);
v_count number(8);
--v_select_sql varchar2(200) default 'select count(*)from emcd.';
--v_insert_sql varchar2(200) default 'insert into tabcount values(';
v_select_sql varchar2(200);
v_insert_sql varchar2(200);
num number;
begin
--判断记录表tablecount是否为空 select count(1) into num from user_tables where table_name =upper('tabcount') or table_name = lower('tabcount'); if num>0 then dbms_output.put_line('已存在该表,删除掉新建'); v_select_sql:='drop table tabcount'; execute immediate v_select_sql; v_select_sql:='create table tabcount(tablename varchar2(100),tabcount number)'; execute immediate v_select_sql; end if; if num<=0 then v_select_sql:='create table tabcount(tablename varchar2(100),tabcount number)'; dbms_output.put_line('没存在,新建一个吧'); execute immediate v_select_sql; end if; begin --注意这里的时间格式mi表示分钟,mm表示月份,若果将hh24:mi:ss写成hh24:mm:ss --则显示的分钟部分将表示的是月份的值,显然这是错误的,要格外小心. dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')); open c_tab; --打开游标 loop fetch c_tab into tem_name; --取数据 exit when c_tab%notfound; if tem_name is not null then --动态sql串. v_select_sql := 'select count(*)from '; v_select_sql := v_select_sql ||tem_name; dbms_output.put_line('v_select_sql ==> '||v_select_sql); execute immediate v_select_sql into v_count; v_insert_sql := 'insert into tabcount values('; v_insert_sql := v_insert_sql || ''''||tem_name||''','||v_count||')'; dbms_output.put_line('v_insert_sql ==> '||v_insert_sql); execute immediate v_insert_sql; commit; end if; end loop; close c_tab; --循环结束后关闭游标 dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')); end; end; end countAll;