CREATE PROCEDURE "dba"."delete_xxxlist"()
BEGIN
  declare ll_id integer;
  declare ls_klassif char(30);
  declare err_notfound exception for sqlstate value '02000';
  declare c_xxxlist dynamic scroll cursor for select "id",klassif from xxxlist;
  set ll_id=0;
  open c_xxxlist;
  xxxlist_loop: loop
    fetch next c_xxxlist into ll_id,ls_klassif;
    if sqlstate = err_notfound then -- kui eof
      leave xxxlist_loop
    end if;

    if ll_id > 0 then
      case ls_klassif
      when 'pa_yksus' then
        delete from pa_tasu where yksusnr=ll_id;
        delete from pa_paryld where yksusnr=ll_id;
        delete from pa_reserv where yksusnr=ll_id;
        delete from pa_erisood where yksusnr=ll_id;
        delete from pl_resdef where yksusnr=ll_id;
        delete from pa_isik where yksusnr=ll_id;
        delete from pa_plnr where yksusnr=ll_id;
        delete from pa_yksus where pa_yksus.yksusnr=ll_id;
      end case;
    end if;

  end loop xxxlist_loop;
  close c_xxxlist;

  case ls_klassif
  when 'pa_yksus' then
    delete from pa_tgraaf from pa_tgraaf left outer join pa_isik on pa_tgraaf.lepnr=pa_isik.lepnr where pa_isik.yksusnr is null;
    delete from pa_leping from pa_leping left outer join pa_isik on pa_leping.lepnr=pa_isik.lepnr where pa_isik.yksusnr is null;
    delete from pa_kaader from pa_kaader left outer join pa_leping on pa_kaader.asutnr=pa_leping.asutnr where pa_leping.lepnr is null;
  end case;

END