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; --动态SQL
end 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
44
PL/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;
