execute immediate使用遇到的问题

Question

在学习动态SQL的时候，接触到了“execute immediate”，然后通过它实现了动态建表。今天本来想在存储过程中删除表中数据，于是再次使用到了“execute immediate”，使用如下：create or replace procedure pro_trunc is
tablename varchar(15) :='URGE_T';
begin
  --execute immediate 'truncate table '||tablename;
  --execute immediate 'truncate table urge_t';
  execute immediate  'truncate table :1' using tablename;
  execute immediate  'truncate table :1' using 'urge_t';
end pro_trunc;注释的两句话可以执行，但是没注释的不可以。本来以为是在取表名的时候需要使用大写，于是把tablename字符串代表的表名写成大写，但是结果还是不行，下面是执行情况和错误提示：SQL> exec pro_trunc;begin pro_trunc; end;ORA-00903: 表名无效
ORA-06512: 在 "QIUYUE.PRO_TRUNC", line 6
ORA-06512: 在 line 1谢谢！

Answer 1

不可以这样写的，对于execute immediate直接执行时，传入的参数不可以是列名，表名如果你分两步是可以的例，下面这样写是可以的：create or replace procedure pro_trunc is
tablename varchar2(15) := 'urge_t';
p_val varchar2(1000);
begin
execute immediate  'select ''truncate table ''||:1
 from dual '
into p_val 
using tablename;
execute immediate p_val;
end pro_trunc;
/
exec pro_trunc;

Answer 2

使用列时的用法create or replace procedure proc_test is
colname varchar2(15) := 'empno';
colvalue number(5) := 7369;
p_val varchar2(1000);
begin
execute immediate  'select ename from emp where empno=:2' 
into p_val
using colvalue;
/*错误的写法
execute immediate  'select ename from emp where :1=:2' 
into p_val
using colname,colvalue;
*/
dbms_output.put_line(p_val);
end proc_test;
/exec proc_test;

Answer 3

太不好意思啦！以为在发帖的时候忘了选择那个“有回复的时候通知我”，所以，一直不知道，不要意思啊！
“对于execute immediate直接执行时，传入的参数不可以是列名，表名”，所以你在使用表的时候，之所以执行两步，是不是就是想通过第一条“select …… from dual”语句，构造一个条自定义的SQL语句，而在这条SQL语句中，就可以把在“execute immediate”中不可以直接作为参数的表名作为一个参数值并加入到SQL语句中？例如你下面的SQL语句：execute immediate  'select ''truncate table ''||:1
         from dual '
        into p_val 
        using tablename;
实际上就是产生一条这样的SQL语句：truncate table urge_t然后在把这条SQL语句放到一个字符串p_val，然后再执行~！

Answer 4

按照上面的方式，我让列值也作为一个参数传递过来，运行正常，麻烦您给看看这样写合适不？谢谢！create or replace procedure proc_test  is
colname varchar(15) :='empno';
colvalue number(5) :=1001;
p_val varchar(500);
str_sql varchar(500);
begin
   execute immediate 'select''select ename from scott.emp where ''||:1||''=''||:2  from dual' 
     into str_sql
     using colname,colvalue;
   execute immediate  str_sql into p_val;              
  dbms_output.put_line(p_val);
   
end proc_test ;

Answer 5

可以的，只要你執行execute immediate時，不執行執行含表名，列名的參數就可以了拼結字符串是可以的

Answer 6

--加個錯誤處理吧，不然你的寫法沒數據會有錯的
...
dbms_output.put_line(p_val);
exception
when others then 
dbms_output.put_line('err');

Answer 7

into 的时候，如果查询不出结果的话，就会出现问题，例如select name into dec_name from stu where id=dec_id如果dec_id对应的id号存在的话，要么可以查找到结果，要么id号对应的名字为空，变量dec_name也为空。但是dec_id如果在表中不存在的话，好像编译的时候，会出现错误。不知道你说的异常是不是就是这种异常啊？
我的方法是在程序中都是先判断一下，如下：select count(*) into dec_count from stu where id=dec_id
if dec_count=0 then
  insert into log valuse(……);
else
  select name into dec_name from stu where id=dec_id;
end if;
这样写是不是和你用的异常可以达到相同的结果啊？