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

Question

程序：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

Answer 1

因你的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; 

Answer 2

少了单引号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;

Answer 3

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

Answer 4

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