SM5604btnCGRKFX_SP

create or replace procedure SM5604btnCGRKFX_SP(p_DJBHList in varchar2
                                               ,p_CGZBIDList varchar2) is

v_sql varchar2(1000);
begin


v_sql:='update SM_CG_CGZB_TB set JE=JHDJ*SSSL,ZT=2,rkrq=sysdate where id in ('||TRIM('''' FROM p_CGZBIDList)
||')';

execute immediate v_sql;
commit;

 for rs in (select column_value as DJBH from table(string_split(TRIM('''' FROM p_DJBHList),',')) )  loop
  UPDATE SM_CG_CGZB_TB SET WRKS = (SELECT MAX(SJQGSL)- nvl(sum(t.sssl),0) 
 from SM_CG_CGZB_TB t where t.zt=2 and t.DJBH=chr(39)||RS.DJBH||chr(39)) 
 where DJBH=chr(39)||RS.DJBH||chr(39);
 
 v_sql:='UPDATE SM_CG_CGZB_TB SET WRKS = (SELECT MAX(SJQGSL)- nvl(sum(t.sssl),0) 
 from SM_CG_CGZB_TB t where t.zt=2 and t.DJBH='||chr(39)||RS.DJBH||chr(39)||') 
 where DJBH='||chr(39)||RS.DJBH||chr(39);
   execute immediate v_sql;
  end loop;
COMMIT;

exception
  when others then
   RAISE_APPLICATION_ERROR(-20002, 'SM5604btnCGRKFX_SP');
   
end SM5604btnCGRKFX_SP;