需要写过程来执行,用动态语句 将select table from table2传给一个变量,然后拼成一个SQL,用execute immediate 去执行
create or replace procedure ctype as type cursor1_type IS REF CURSOR; cursor1 cursor1_type; tbname varchar2(20); tbrowcount number; sqlstr varchar2(100); begin open cursor1 for select table_name from user_tables; loop fetch cursor1 into tbname; sqlstr := 'select count(*) from '||tbname; dbms_output.put_line(sqlstr); execute immediate sqlstr into tbrowcount; dbms_output.put_line(tbname||' 记录数:'||tbrowcount); exit when cursor1%notfound; end loop; close cursor1; end;简单弄个例子,你参考一下。
declare V_ta varchar2(100); begin selcet table into V_ta from TABLE2 where rownum=1; execute immediate 'select * from '||V_ta; end; 用 execute immediate 动态执行语句select * from TABLE1;
将select table from table2传给一个变量,然后拼成一个SQL,用execute immediate 去执行
as
type cursor1_type IS REF CURSOR;
cursor1 cursor1_type;
tbname varchar2(20);
tbrowcount number;
sqlstr varchar2(100);
begin
open cursor1 for select table_name from user_tables;
loop
fetch cursor1 into tbname;
sqlstr := 'select count(*) from '||tbname;
dbms_output.put_line(sqlstr);
execute immediate sqlstr into tbrowcount;
dbms_output.put_line(tbname||' 记录数:'||tbrowcount);
exit when cursor1%notfound;
end loop;
close cursor1;
end;简单弄个例子,你参考一下。
begin
selcet table into V_ta from TABLE2 where rownum=1;
execute immediate 'select * from '||V_ta;
end;
用 execute immediate 动态执行语句select * from TABLE1;