oracle 存储过程出错,存储过程错误

存储过程错误:

目的就是找出表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;