这个过程编译通过，但是执行报如下错，为什么？

[yanjingrong]

程序：

create or replace procedure yjr004 is

v_rowid varchar2(30);
i number;
v1 number;
v_acct_month dw_key_target_js.acct_month%type;
v_js_type dw_key_target_js.js_type%type;
v_svc_id dw_key_target_js.svc_id%type;
v_area_name dw_key_target_js.area_name%type;
v_js_income dw_key_target_js.js_income%type;
v_js_payout dw_key_target_js.js_payout%type;
v_js_net_income dw_key_target_js.js_net_income%type;
v_b_asp dw_key_target_js.b_asp%type;

begin

for i in 1 .. 1000 loop

select no1,
acct_month,
js_type,
svc_id,
area_name,
js_income,
js_payout,
js_net_income,
b_asp
into v_rowid,
v_acct_month,
v_js_type,
v_svc_id,
v_area_name,
v_js_income,
v_js_payout,
v_js_net_income,
v_b_asp
from (select rownum no,
rowid no1,
acct_month,
js_type,
svc_id,
area_name,
js_income,
js_payout,
js_net_income,
b_asp
from dw_key_target_js)
where no = i;

select count(*)
into v1
from dw_key_target_js
where acct_month = v_acct_month
and js_type = v_js_type
and svc_id = v_svc_id
and area_name = v_area_name
and js_income = v_js_income
and js_payout = v_js_payout
and js_net_income = v_js_net_income
and b_asp = v_b_asp;

if v1 > 1 then

execute immediate 'delete from dw_key_target_js where rowid='||v_rowid;



end if;
end loop;
end yjr004;


编译通过，但是执行exec yjr004 报错：

ORA-00904: "AAAJRMAAHAAB3MNAAH": 标识符无效
ORA-06512: 在 "NEWEBA2.YJR004", line 64
ORA-06512: 在 line 2

[sleepzzzzz]

因你的v_rowid是一串字符，所以不能这么用execute immediate 。

--execute immediate 'delete from dw_key_target_js  where rowid='||v_rowid; 
  这句改为:

  execute immediate 'delete from dw_key_target_js  where rowid= :1' using v_rowid; 

[范佩西_11]

少了单引号execute immediate 'delete from dw_key_target_js where rowid='||v_rowid;

create or replace procedure yjr004 is

  v_rowid         varchar2(30);
  i               number;
  v1              number;
  v_acct_month    dw_key_target_js.acct_month%type;
  v_js_type       dw_key_target_js.js_type%type;
  v_svc_id        dw_key_target_js.svc_id%type;
  v_area_name     dw_key_target_js.area_name%type;
  v_js_income     dw_key_target_js.js_income%type;
  v_js_payout     dw_key_target_js.js_payout%type;
  v_js_net_income dw_key_target_js.js_net_income%type;
  v_b_asp         dw_key_target_js.b_asp%type;

begin

  for i in 1 .. 1000 loop
  
    select no1,
           acct_month,
           js_type,
           svc_id,
           area_name,
           js_income,
           js_payout,
           js_net_income,
           b_asp
      into v_rowid,
           v_acct_month,
           v_js_type,
           v_svc_id,
           v_area_name,
           v_js_income,
           v_js_payout,
           v_js_net_income,
           v_b_asp
      from (select rownum no,
                   rowid no1,
                   acct_month,
                   js_type,
                   svc_id,
                   area_name,
                   js_income,
                   js_payout,
                   js_net_income,
                   b_asp
              from dw_key_target_js)
     where no = i;
  
    select count(*)
      into v1
      from dw_key_target_js
     where acct_month = v_acct_month
       and js_type = v_js_type
       and svc_id = v_svc_id
       and area_name = v_area_name
       and js_income = v_js_income
       and js_payout = v_js_payout
       and js_net_income = v_js_net_income
       and b_asp = v_b_asp;
  
    if v1 > 1 then
    
      execute immediate 'delete from dw_key_target_js  where rowid=''' ||
                        v_rowid || '''';
    
    end if;
  end loop;
end yjr004;

[sleepzzzzz]

像楼上linzhangs拼串当然也是可以的．建议使用bind变量方式吧．

[yanjingrong]

谢谢大家，thank you very much!!!