发现pl/sql就行,我自己做的测试 SQL> declare 2 cursor c1 is select tname from tab where tabtype = 'TABLE'; 3 RecordNumber number; 4 sqlText varchar(100); 5 begin 6 for mytable in c1 loop 7 execute immediate 'select count(*) from '||mytable.tname into RecordNumber; 8 dbms_output.put_line(mytable.tname||' '||RecordNumber); 9 end loop; 10 end; 11 /
DEPT 4 EMP 14 BONUS 0 SALGRADE 5 EMP1 16 T1 28671
PL/SQL procedure successfully completed 原代码也发一个,免得你去行号: declare cursor c1 is select tname from tab where tabtype = 'TABLE'; RecordNumber number; sqlText varchar(100); begin for mytable in c1 loop execute immediate 'select count(*) from '||mytable.tname into RecordNumber; dbms_output.put_line(mytable.tname||' '||RecordNumber); end loop; end;列没对齐,你就自己想办法吧
执行前在sqlplus里面用个set serveroutput on, 要不看不了回显
搞了个好看点的: SQL> declare 2 cursor c1 is select tname from tab where tabtype = 'TABLE'; 3 RecordNumber number; 4 begin 5 dbms_output.put_line('TABLE NAME'||' '||'RECORD NUMBER'); 6 for mytable in c1 loop 7 execute immediate 'select count(*) from '||mytable.tname into RecordNumber; 8 dbms_output.put_line(mytable.tname||lpad(mytable.tname,18,chr(32))||RecordNumber); 9 end loop; 10 end; 11 /
TABLE NAME RECORD NUMBER DEPT DEPT4 EMP EMP14 BONUS BONUS0 SALGRADE SALGRADE5 EMP1 EMP116 T1 T128671
PL/SQL procedure successfully completed
这下终于对齐了SQL> declare 2 cursor c1 is select tname from tab where tabtype = 'TABLE'; 3 RecordNumber number; 4 begin 5 dbms_output.put_line('TABLE NAME'||' '||'RECORD NUMBER'); 6 for mytable in c1 loop 7 execute immediate 'select count(*) from '||mytable.tname into RecordNumber; 8 dbms_output.put_line(mytable.tname||rpad(' ',20-length(mytable.tname),chr(32))||RecordNumber); 9 end loop; 10 end; 11 /
TABLE NAME RECORD NUMBER DEPT 4 EMP 14 BONUS 0 SALGRADE 5 EMP1 16 T1 28671
PL/SQL procedure successfully completed
SQL> declare 2 cursor c1 is select tname from tab where tabtype='TABLE'; 3 RecordNumber number; 4 begin 5 dbms_output.put_line('TABLE NAME'||' '||'RECORD NUMBER'); 6 for mytable in c1 loop 7 execute immediate 'selec count(*) from '||mytable.tname into RecordNumbe r; 8 dbms_output.put_line(mytable.tname||rpad(' ',20-length(mytable.tname),chr (32))||RecordNumber); 9 end loop; 10 end; 11 / TABLE NAME RECORD NUMBER declare * ERROR 位于第 1 行: ORA-00900: 无效 SQL 语句 ORA-06512: 在line 7 请问lpc19598188,这是怎么回事啊?
select t.TABLE_NAME,t.NUM_ROWS from user_tables t
declare v_table varchar(50); v_num number; v_sql varchar(500); cursor c1 is select table_name from user_tables; begin open c1; loop fetch c1 into v_table; if c1%found then v_sql:='select count(*) from '||v_table||''; execute immediate v_sql into v_num; dbms_output.put_line('表:'||v_table||' 行号: '||v_num); else exit; end if; end loop; end;
analyze table tab_name estimate statistics; 之后可以用 select t.TABLE_NAME,t.NUM_ROWS from user_tables t 查看记录数
这个简单!不过动态SQL也是一种办法啊
其实可以这样,先用游标对每个表进行分析,然后再将记录数取出来: declare v_tName varchar(50); v_sqlanalyze varchar(500); v_num number; v_sql varchar(500); cursor c1 is select table_name from user_tables; begin open c1; loop fetch c1 into v_tName; if c1%found thenv_sqlanalyze :='analyze table '||v_tName||' estimate statistics';v_sql := 'select NUM_ROWS from user_tables where table_name =upper('''||v_tName||''')';execute immediate v_sql into v_num; dbms_output.put_line('表名: '||v_tName||' 行数: '||v_num); else exit; end if; end loop; end;
更正一下: declare v_tName varchar(50); v_sqlanalyze varchar(500); v_num number; v_sql varchar(500); cursor c1 is select table_name from user_tables; begin open c1; loop fetch c1 into v_tName; if c1%found thenv_sqlanalyze :='analyze table '||v_tName||' estimate statistics'; execute immediate v_sqlanalyze; v_sql := 'select NUM_ROWS from user_tables where table_name =upper('''||v_tName||''')';execute immediate v_sql into v_num; dbms_output.put_line('表名: '||v_tName||' 行数: '||v_num); else exit; end if; end loop; end;
SQL> declare
2 cursor c1 is select tname from tab where tabtype = 'TABLE';
3 RecordNumber number;
4 sqlText varchar(100);
5 begin
6 for mytable in c1 loop
7 execute immediate 'select count(*) from '||mytable.tname into RecordNumber;
8 dbms_output.put_line(mytable.tname||' '||RecordNumber);
9 end loop;
10 end;
11 /
DEPT 4
EMP 14
BONUS 0
SALGRADE 5
EMP1 16
T1 28671
PL/SQL procedure successfully completed
原代码也发一个,免得你去行号:
declare
cursor c1 is select tname from tab where tabtype = 'TABLE';
RecordNumber number;
sqlText varchar(100);
begin
for mytable in c1 loop
execute immediate 'select count(*) from '||mytable.tname into RecordNumber;
dbms_output.put_line(mytable.tname||' '||RecordNumber);
end loop;
end;列没对齐,你就自己想办法吧
要不看不了回显
SQL> declare
2 cursor c1 is select tname from tab where tabtype = 'TABLE';
3 RecordNumber number;
4 begin
5 dbms_output.put_line('TABLE NAME'||' '||'RECORD NUMBER');
6 for mytable in c1 loop
7 execute immediate 'select count(*) from '||mytable.tname into RecordNumber;
8 dbms_output.put_line(mytable.tname||lpad(mytable.tname,18,chr(32))||RecordNumber);
9 end loop;
10 end;
11 /
TABLE NAME RECORD NUMBER
DEPT DEPT4
EMP EMP14
BONUS BONUS0
SALGRADE SALGRADE5
EMP1 EMP116
T1 T128671
PL/SQL procedure successfully completed
2 cursor c1 is select tname from tab where tabtype = 'TABLE';
3 RecordNumber number;
4 begin
5 dbms_output.put_line('TABLE NAME'||' '||'RECORD NUMBER');
6 for mytable in c1 loop
7 execute immediate 'select count(*) from '||mytable.tname into RecordNumber;
8 dbms_output.put_line(mytable.tname||rpad(' ',20-length(mytable.tname),chr(32))||RecordNumber);
9 end loop;
10 end;
11 /
TABLE NAME RECORD NUMBER
DEPT 4
EMP 14
BONUS 0
SALGRADE 5
EMP1 16
T1 28671
PL/SQL procedure successfully completed
2 cursor c1 is select tname from tab where tabtype='TABLE';
3 RecordNumber number;
4 begin
5 dbms_output.put_line('TABLE NAME'||' '||'RECORD NUMBER');
6 for mytable in c1 loop
7 execute immediate 'selec count(*) from '||mytable.tname into RecordNumbe
r;
8 dbms_output.put_line(mytable.tname||rpad(' ',20-length(mytable.tname),chr
(32))||RecordNumber);
9 end loop;
10 end;
11 /
TABLE NAME RECORD NUMBER
declare
*
ERROR 位于第 1 行:
ORA-00900: 无效 SQL 语句
ORA-06512: 在line 7
请问lpc19598188,这是怎么回事啊?
v_table varchar(50);
v_num number;
v_sql varchar(500);
cursor c1
is
select table_name from user_tables;
begin
open c1;
loop
fetch c1 into v_table;
if c1%found then
v_sql:='select count(*) from '||v_table||'';
execute immediate v_sql into v_num;
dbms_output.put_line('表:'||v_table||' 行号: '||v_num);
else
exit;
end if;
end loop;
end;
之后可以用
select t.TABLE_NAME,t.NUM_ROWS from user_tables t
查看记录数
declare
v_tName varchar(50);
v_sqlanalyze varchar(500);
v_num number;
v_sql varchar(500);
cursor c1
is
select table_name from user_tables;
begin
open c1;
loop
fetch c1 into v_tName;
if c1%found thenv_sqlanalyze :='analyze table '||v_tName||' estimate statistics';v_sql := 'select NUM_ROWS from user_tables where table_name =upper('''||v_tName||''')';execute immediate v_sql into v_num;
dbms_output.put_line('表名: '||v_tName||' 行数: '||v_num);
else
exit;
end if;
end loop;
end;
declare
v_tName varchar(50);
v_sqlanalyze varchar(500);
v_num number;
v_sql varchar(500);
cursor c1
is
select table_name from user_tables;
begin
open c1;
loop
fetch c1 into v_tName;
if c1%found thenv_sqlanalyze :='analyze table '||v_tName||' estimate statistics';
execute immediate v_sqlanalyze;
v_sql := 'select NUM_ROWS from user_tables where table_name =upper('''||v_tName||''')';execute immediate v_sql into v_num;
dbms_output.put_line('表名: '||v_tName||' 行数: '||v_num);
else
exit;
end if;
end loop;
end;
很清楚虽然笨了点