存储过程错误

存储过程错误:

目的就是找出表ucs_credit_days 中的失效记录，然后更新对应表中的相关字段
create or replace procedure CREDIT_CHANGE as

cursor cursor_emp1 IS select old_credit,new_credit,subscription_id,service_type from ucs_credit_days where inactive_date > sysdate;
cursor cursor_emp2(svc_type ucs_subs_type.subs_svc_type%type) IS select subs_type_tbname from ucs_subs_type where subs_svc_type = svc_type;
v_old_credit number(9);
v_new_credit number(9);
v_subs_id number(9);
v_svc_type number(4);
tab_name varchar2(32);
l_sql varchar2(100);

begin
open cursor_emp1;
FETCH cursor_emp1 INTO v_old_credit, v_new_credit, v_subs_id,v_svc_type;
WHILE cursor_emp1%FOUND LOOP
  FETCH cursor_emp1 INTO v_old_credit, v_new_credit, v_subs_id,v_svc_type;

  open cursor_emp2(v_svc_type);
  FETCH cursor_emp2 INTO tab_name;
  l_sql := 'update ';
  l_sql := l_sql||tab_name||' set credit_fee = '||v_old_credit||' where subscription_id = '||v_subs_id;
  execute immediate l_sql;
  CLOSE cursor_emp2;


END LOOP;
CLOSE cursor_emp1;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
  NULL;
end;


------解决方法--------------------------------------------------------
不知道行不行

SQL code

create or replace procedure CREDIT_CHANGE as   cursor cursor_emp1 IS select old_credit,new_credit,subscription_id,service_type from ucs_credit_days   where inactive_date > sysdate;   cursor cursor_emp2(svc_type ucs_subs_type.subs_svc_type%type) IS select subs_type_tbname from ucs_subs_type   where subs_svc_type = svc_type;   v_old_credit number(9);   v_new_credit number(9);   v_subs_id number(9);   v_svc_type number(4);   tab_name varchar2(32);   l_sql varchar2(100); begin     open cursor_emp1;      LOOP       FETCH cursor_emp1 INTO v_old_credit, v_new_credit, v_subs_id,v_svc_type;      exit when cursor_emp1%notfound;          open cursor_emp2(v_svc_type);          Loop             FETCH cursor_emp2 INTO tab_name;             exit when cursor_emp2%notfound;            l_sql := 'update ';             l_sql := l_sql | |tab_name | |' set credit_fee = ' | |v_old_credit | |' where subscription_id = ' | |v_subs_id;             execute immediate l_sql;           END LOOP;          CLOSE cursor_emp2;     END LOOP;     CLOSE cursor_emp1;    EXCEPTION     WHEN NO_DATA_FOUND THEN         NULL; end;