100分相送！在线等待！

[zgysc]

我写个过程如下：
create or replace procedure aa
as
talename varchar2(100);
tempid number;
begin
tablename :='table1';
select id from tablename into tempid;
insert into table2(vv) values(tempid);
commit;
end;
但始终报错：系统没根本没有tablename这个表，要是把 select id from tablename into tempid;里的tablename 换成 table1 才能执行

也就是说用变量根本代替不了表名，我怎么样才能实现用变量替代表名呢?

[jiezhi]

... from ||tablename|| ...

[jiezhi]

下面是我以前寫的過程里面的一段：
str1:='';
str2:='';
OPEN c_cur FOR SELECT tname FROM t_sjbgl;
LOOP
FETCH c_cur INTO v_tname;
EXIT WHEN c_cur%NOTFOUND;
IF v_tname='T_DYJCJL_SAME' THEN
str1:='delete from '||v_tname||' where dybh='||
'(select dybh from T_DYJBXXB_SAME where ygid='''||v_ygid||''')';
ELSIF v_tname='T_DNZWLL_SAME' THEN
str1:='delete from '||v_tname||' where dybh='||
'(select dybh from T_DYJBXXB_SAME where ygid='''||v_ygid||''')';
ELSIF v_tname='T_HTFJ_SAME' THEN
str1:='delete from '||v_tname||' where htbh='||
'(select htbh from T_YGHT_SAME where ygid='''||v_ygid||''')';
ELSIF v_tname='T_GZJSXGZXM_SAME' THEN
str1:='delete from '||v_tname||' where bh='||
'(select bh from T_GZJSXB_SAME where ygid='''||v_ygid||''')';
ELSE
str1:='delete from '||v_tname||' where ygid='''||v_ygid||'''';
END IF;
EXECUTE IMMEDIATE str1;
END LOOP;

[zgysc]

谢谢二位，不过我知道EXECUTE IMMEDIATE ，我现在只想有个函数什么的能把字符串转为表对象

[Lastdrop]

不行，你必须用动态SQL，才可以用变量当表名。

[LGQDUCKY]

绑定变量，使用动态SQL
table_name := 'T' || LTRIM(TO_CHAR(my_bbh, v_format ));
sqlcmd := 'SELECT COUNT(*) FROM tabs WHERE TABLE_NAME=:TBNAME';
DBMS_SQL.PARSE( nSQL, sqlcmd, DBMS_SQL.v7 ) ;
DBMS_SQL.BIND_VARIABLE( nSQL, ':TBNAME', table_name ) ;
DBMS_SQL.DEFINE_COLUMN( nSQL, 1, my_count ) ;
ret := DBMS_SQL.EXECUTE( nSQL )

[beckhambobo]

create or replace procedure aa
as
talename varchar2(100);
tempid number;
str varchar2(50);
begin
tablename :='table1';
str:='select id from '||tablename;
execute immediate str into tempid;
insert into table2(vv) values(tempid);
exception
when others then
dbms_output.put_line('ddd');
commit;
end;
/