问题二 set serveroutput on declare v_table varchar2(20):='A_TAB'; str varchar2(100); type t_sor is ref cursor; v_sor t_sor; begin str:='select * from '||v_table; open v_sor for str; end; /
非常感谢,增加了我学ORACLE的决心,thank beckhambobo(beckham) for your help, very good;
问题一set serveroutput on declare v_table varchar2(20):='NEWSHEALTH'; str varchar2(100); type t_sor is ref cursor; v_sor t_sor; cursor p_sor is select table_name from tabs where table_name like 'AA%'; begin for m_sor in p_sor loop str:='select count(*) num from '||m_sor.table_name; open v_sor for str; --dbms_output.put_line(...); end loop; end; /
ok , thank again to beckhambobo(beckham) there is mine! -----------------copy right------------------------ set serveroutput on DECLARE CURSOr TABRows is select tname from tab where tname like 'AA%' AND tname not like '%TEMP%' ORDER BY LENGTH(TNAME); TYPE TAB_CURSOR IS REF CURSOR ; TABCOUNT TAB_CURSOR; TN VARCHAR2(30); sqltext varchar2(100); record_count varchar2(10); BEGIN OPEN TABRows; fetch TABRows into tn; while TABRows%found loop sqltext:='select count(*) num from '||' '||tn; --record_count:=' '; OPEN TABCOUNT FOR sqltext; fetch TABCOUNT into record_count; DBMS_OUTPUT.PUT_LINE('table '||tn||'; record '||record_count); close TABCOUNT; fetch TABRows into tn; end loop; close TABRows;
set serveroutput on
declare
v_table varchar2(20):='A_TAB';
str varchar2(100);
type t_sor is ref cursor;
v_sor t_sor;
begin
str:='select * from '||v_table;
open v_sor for str;
end;
/
declare
v_table varchar2(20):='NEWSHEALTH';
str varchar2(100);
type t_sor is ref cursor;
v_sor t_sor;
cursor p_sor is
select table_name from tabs where table_name like 'AA%';
begin
for m_sor in p_sor loop
str:='select count(*) num from '||m_sor.table_name;
open v_sor for str;
--dbms_output.put_line(...);
end loop;
end;
/
there is mine!
-----------------copy right------------------------
set serveroutput on
DECLARE
CURSOr TABRows is select tname from tab where tname like 'AA%' AND tname not like '%TEMP%' ORDER BY LENGTH(TNAME);
TYPE TAB_CURSOR IS REF CURSOR ;
TABCOUNT TAB_CURSOR;
TN VARCHAR2(30);
sqltext varchar2(100);
record_count varchar2(10);
BEGIN
OPEN TABRows;
fetch TABRows into tn;
while TABRows%found loop
sqltext:='select count(*) num from '||' '||tn;
--record_count:=' ';
OPEN TABCOUNT FOR sqltext;
fetch TABCOUNT into record_count;
DBMS_OUTPUT.PUT_LINE('table '||tn||'; record '||record_count);
close TABCOUNT;
fetch TABRows into tn;
end loop;
close TABRows;
END;