十万火急:如何在动态语句中使用表名的变量?

[tangru2]

---------------------------------------------------------------------
偶想查找动态表的记录数,但是执行如下语句时却出错,
请大侠指点,谢谢!
SQL> declare
2 v_sqlstr varchar2(200);
3 v_TbName varchar2(100);
4 v_num number;
5 begin
6 v_TbName:='scott.emp';
7 v_sqlstr:='select count(*) into v_num from ' || v_TbName;
8 execute immediate v_sqlstr;
9 dbms_output.put_line(v_num);
10 end;
11 /
ORA-00905: 缺失关键字
ORA-06512: 在 line 8

[jametong]

你的语法不对啊..

select col into var from tab

是plsql的语法..

可以使用以下两种方式实现你的要求...

declare
tab_name varchar2(20);
v_cnt number;
begin
tab_name := 'mu_account';
execute immediate 'begin select count(*) into :v_cnt from '|| tab_name ||'; end;' using out v_cnt;
dbms_output.put_line(v_cnt);
end;
/

declare
tab_name varchar2(20);
v_cnt number;
begin
tab_name := 'mu_account';
execute immediate 'select count(*) from '|| tab_name ||'' into v_cnt;
dbms_output.put_line(v_cnt);
end;
/


附带execute immediate 的语法..
execute_immediate_statement ::=
EXECUTE_IMMEDIATE dynamic_string
[ INTO { define_variable [, define_variable ...] | record_name } ]
[ USING [ IN | OUT | IN OUT ] bind_argument
[, [ IN | OUT | IN OUT ] bind_argument] ... ]
[ {RETURNING | RETURN } INTO bind_argument [, bind_argument]... ];

[huiwsey]

非常详细了