Oracle 获取所有表名,及其行数

1. 新建表

 

CREATE TABLE "TEMP_TABLE" 
(
    "TABLE_NAME" VARCHAR2(256 BYTE), 
    "COUNT" NUMBER
)

 

2.新建存储过程

create or replace PROCEDURE TEST AS
     v_bcount int;
     v_bname varchar2(256);
     CURSOR cur_1 IS select table_name from user_tables;
BEGIN
     execute immediate 'truncate table temp_table';
     open cur_1;
        loop
            FETCH cur_1 INTO v_bname;            
            execute immediate 'select count(*)  from '||v_bname into v_bcount;
            execute immediate 'INSERT INTO temp_table VALUES(:1,:2)' using v_bname,TO_CHAR(v_bcount);
            dbms_output.put_line(v_bname||'------------------'||v_bcount);
            exit when cur_1%notfound;
        end loop;
     close cur_1;
  NULL;
END TEST;

3.执行存储过程