Question

写了个存储过程，编译的时候通过了，执行的时候报错 提示execute immediate语句无效

create or replace procedure pro_t is cursor c is select table_name from user_all_tables where table_name in (select table_name from user_tab_columns where column_name = 'POSITION_ID'); tablename varchar2(100); strinsert varchar2(1000);
begin open c; loop fetch c into tablename; strinsert:= 'update' || tablename || 'set POSITION_ID =' || 1 ;execute immediate strinsert;
end loop; commit; close c;end;

Answer 1

create or replace procedure pro_t is
cursor c is
select table_name
from user_all_tables
where table_name in (select table_name
from user_tab_columns
where column_name = 'POSITION_ID');
tablename varchar2(100);
strinsert varchar2(1000);
begin
open c;
loop
fetch c
into tablename;
exit when c%notfound;--你之前没有循环退出条件
strinsert:= 'update '|| tablename ||' set POSITION_ID =1' ;--之前sql语句拼的有问题
execute immediate strinsert;
end loop;
commit;
close c;
end;

追问

• 你好 我想再更新个字段该怎么写？ 前面条件都一样 想strinsert := 'update  ' || tablename || ' set POSITION_ID =1 and POSITION_NAME = 新院区';就是同时也更新position_name这个字段  我这样写法是报错的  麻烦说清楚好不

追答

strinsert := 'update  ' || tablename || ' set POSITION_ID =1 and POSITION_NAME = ''新院区''';

追问

我写上了  怎么会提示命令没正确结束？ 麻烦再说下  先谢谢啦

追答

你循环写的不对，之前就说过你循环有问题