該過程統計 RATING.ALL_PARAM_TABLE?表時包含的所有表的資料量

該過程統計 RATING.ALL_PARAM_TABLE表時包含的所有表的資料量
create table RATING.gather_tab_cnt(owner varchar2(20),table_name varchar(30),count number) tablespace D_LOG_01;
第一種寫法：－－－使用||拼變數
create or replace procedure gather_tab_cnt 
is 
cursor all_tab is select table_name from RATING.ALL_PARAM_TABLE;
v_tabname varchar2(50);
v_sql01 varchar2(2000);
v_sql02 varchar2(2000);
v_sql03 varchar2(2000);
own varchar2(40);
tb_name varchar2(40);
cnt number;


begin 
for i in all_tab loop 
v_tabname:=i.table_name;
dbms_output.put_line('v_tabname:'||v_tabname);
v_sql01:='select owner,table_name from dba_tables where table_name='''||v_tabname||'''';
execute immediate v_sql01 into own,tb_name;
dbms_output.put_line('own,tb_name:'||own||','||tb_name);
v_sql02:='select  count(*) from '||own||'.'||tb_name;
execute immediate v_sql02 into cnt;
v_sql03:='insert into RATING.gather_tab_cnt values ('''||own||''','''||tb_name||''','''||cnt||''')';
execute immediate v_sql03;
commit;
end loop;
end;
/

每二種寫法：－－－－直接用using 來指定變數

create or replace procedure gather_tab_cnt 
is 
cursor all_tab is select table_name from RATING.ALL_PARAM_TABLE where rownum<2;
v_tabname varchar2(50);
v_sql01 varchar2(2000);
v_sql02 varchar2(2000);
v_sql03 varchar2(2000);
own varchar2(40);
tb_name varchar2(40);
cnt number;


begin 
for i in all_tab loop 
v_tabname:=i.table_name;
v_sql01:='select owner,table_name from dba_tables where table_name=:v_tame';
execute immediate v_sql01  into own,tb_name  using v_tabname;
v_sql02:='select  count(*)  from '||own||'.'||tb_name;
execute immediate v_sql02 into cnt ;
dbms_output.put_line(cnt);


v_sql03:='insert into RATING.gather_tab_cnt values(:owner,:tab_name,:cnt)';
execute immediate v_sql03 using own,tb_name,cnt;
commit;
end loop;
end;
/