先select name into 变量 from tablea; 然后open cursorname for'select * from '||变量;
create table a ( name varchar2(20) ); insert into a(name) values ('a_test'); commit;create or replace procedure create_table as table_name varchar2(100); begin select t.name into table_name from a t; execute immediate 'select * from ' || table_name; --动态SQLend create_table;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 Connected as scott
SQL> SQL> create table tbl_store 2 (id number(10), 3 name varchar2(10));
Table created
SQL> insert into tbl_store values(1,'emp');
1 row inserted
SQL> commit;
Commit complete
SQL> set serveroutput on SQL> SQL> declare 2 cv_emp sys_refcursor; 3 v_table varchar2(10); 4 begin 5 select name into v_table from tbl_store where rownum=1; 6 open cv_emp for 'select ename from '||v_table; 7 loop 8 fetch cv_emp into v_table ; 9 exit when cv_emp%notfound; 10 dbms_output.put_line(v_table); 11 end loop; 12 close cv_emp; 13 end; 14 /
SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER
PL/SQL procedure successfully completed
SQL>
--用动态拼接SQL> edi 已写入 file afiedt.buf 1 declare 2 type cur1_type is ref cursor; 3 cur1 cur1_type; 4 v_tb varchar2(40); 5 v_id varchar2(20); 6 begin 7 select tbname into v_tb from t_tab where rownum=1; 8 open cur1 for 'select id from '||v_tb; 9 fetch cur1 into v_id; 10 while cur1%found loop 11 dbms_output.put_line(v_id); 12 fetch cur1 into v_id; 13 end loop; 14 close cur1; 15* end; SQL> /PL/SQL 过程已成功完成。SQL> set serveroutput on SQL> / wkc168 2 2 2 2 2 8 test 11 22 44PL/SQL 过程已成功完成。
--用动态sql写 declare table_name varchar(10); sql_statement varchar2(100) begin select name into table_name from where id=&v_id; sql_statement:='select * from '||table_name; execute immediate sql_statement; end;
然后open cursorname for'select * from '||变量;
create table a (
name varchar2(20)
);
insert into a(name) values ('a_test');
commit;create or replace procedure create_table as
table_name varchar2(100);
begin
select t.name into table_name from a t;
execute immediate 'select * from ' || table_name; --动态SQLend create_table;
Connected as scott
SQL>
SQL> create table tbl_store
2 (id number(10),
3 name varchar2(10));
Table created
SQL> insert into tbl_store values(1,'emp');
1 row inserted
SQL> commit;
Commit complete
SQL> set serveroutput on
SQL>
SQL> declare
2 cv_emp sys_refcursor;
3 v_table varchar2(10);
4 begin
5 select name into v_table from tbl_store where rownum=1;
6 open cv_emp for 'select ename from '||v_table;
7 loop
8 fetch cv_emp into v_table ;
9 exit when cv_emp%notfound;
10 dbms_output.put_line(v_table);
11 end loop;
12 close cv_emp;
13 end;
14 /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
PL/SQL procedure successfully completed
SQL>
已写入 file afiedt.buf 1 declare
2 type cur1_type is ref cursor;
3 cur1 cur1_type;
4 v_tb varchar2(40);
5 v_id varchar2(20);
6 begin
7 select tbname into v_tb from t_tab where rownum=1;
8 open cur1 for 'select id from '||v_tb;
9 fetch cur1 into v_id;
10 while cur1%found loop
11 dbms_output.put_line(v_id);
12 fetch cur1 into v_id;
13 end loop;
14 close cur1;
15* end;
SQL> /PL/SQL 过程已成功完成。SQL> set serveroutput on
SQL> /
wkc168
2
2
2
2
2
8
test
11
22
44PL/SQL 过程已成功完成。
declare
table_name varchar(10);
sql_statement varchar2(100)
begin
select name into table_name from where id=&v_id;
sql_statement:='select * from '||table_name;
execute immediate sql_statement;
end;