CREATE PROCEDURE "dba"."delete_klassif_id"()
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 'meetlist' then
      update dk_alus set meetnr=null where meetnr=ll_id;
      update dk_autoviivis set meetnr=null where meetnr=ll_id;
      update dk_earve set meetnr=null where meetnr=ll_id;
      update dk_earve_rida set meetnr=null where meetnr=ll_id;
      update dk_graafik set meetnr=null where meetnr=ll_id;
      update dk_kanne set meetnr=null where meetnr=ll_id;
      update dk_viivis set meetnr=null where meetnr=ll_id;
      update ea_alus set meetnr=null where meetnr=ll_id;
      update ea_kanne set meetnr=null where meetnr=ll_id;
      update kp_alus set meetnr=null where meetnr=ll_id;
      update kp_kanne set meetnr=null where meetnr=ll_id;
      update lisalist set meetnr=null where meetnr=ll_id;
      update ls_makslist set meetnr=null where meetnr=ll_id;
      update ls_yksus set meetnr=null where meetnr=ll_id;
      update mt_alus set meetnr=null where meetnr=ll_id;
      update mt_kanne set meetnr=null where meetnr=ll_id;
      update mt_menu set meetnr=null where meetnr=ll_id;
      update mt_yksus set meetnr=null where meetnr=ll_id;
      update pa_erisood set meetnr=null where meetnr=ll_id;
      update pa_paryld set meetnr=null where meetnr=ll_id;
      update pa_reserv set meetnr=null where meetnr=ll_id;
      update pa_taslist set meetnr=null where meetnr=ll_id;
      update pa_tasu set meetnr=null where meetnr=ll_id;
      update pa_yksus set meetnr=null where meetnr=ll_id;
      update pl_resdefalus set meetnr=null where meetnr=ll_id;
      update pr_alus set meetnr=null where meetnr=ll_id;
      update pr_kanne set meetnr=null where meetnr=ll_id;
      update pv_kanne set meetnr=null where meetnr=ll_id;
      update pv_kanne_leping set meetnr=null where meetnr=ll_id;
      update pv_yksus set meetnr=null where meetnr=ll_id;
      delete from meetlist where meetnr=ll_id;
    when 'kullist' then
      update dk_alus set kulnr=null where kulnr=ll_id;
      update dk_autoviivis set kulnr=null where kulnr=ll_id;
      update dk_earve set kulnr=null where kulnr=ll_id;
      update dk_earve_rida set kulnr=null where kulnr=ll_id;
      update dk_graafik set kulnr=null where kulnr=ll_id;
      update dk_kanne set kulnr=null where kulnr=ll_id;
      update dk_viivis set kulnr=null where kulnr=ll_id;
      update ea_alus set kulnr=null where kulnr=ll_id;
      update ea_kanne set kulnr=null where kulnr=ll_id;
      update kp_alus set kulnr=null where kulnr=ll_id;
      update kp_kanne set kulnr=null where kulnr=ll_id;
      update lisalist set kulnr=null where kulnr=ll_id;
      update ls_makslist set kulnr=null where kulnr=ll_id;
      update ls_yksus set kulnr=null where kulnr=ll_id;
      update mt_alus set kulnr=null where kulnr=ll_id;
      update mt_kanne set kulnr=null where kulnr=ll_id;
      update mt_menu set kulnr=null where kulnr=ll_id;
      update mt_yksus set kulnr=null where kulnr=ll_id;
      update pa_erisood set kulnr=null where kulnr=ll_id;
      update pa_paryld set kulnr=null where kulnr=ll_id;
      update pa_reserv set kulnr=null where kulnr=ll_id;
      update pa_taslist set kulnr=null where kulnr=ll_id;
      update pa_tasu set kulnr=null where kulnr=ll_id;
      update pa_yksus set kulnr=null where kulnr=ll_id;
      update pl_resdefalus set kulnr=null where kulnr=ll_id;
      update pr_alus set kulnr=null where kulnr=ll_id;
      update pr_kanne set kulnr=null where kulnr=ll_id;
      update pv_kanne set kulnr=null where kulnr=ll_id;
      update pv_kanne_leping set kulnr=null where kulnr=ll_id;
      update pv_yksus set kulnr=null where kulnr=ll_id;
      delete from kullist where kulnr=ll_id;
    when 'tallist' then
      update dk_alus set talnr=null where talnr=ll_id;
      update dk_autoviivis set talnr=null where talnr=ll_id;
      update dk_earve set talnr=null where talnr=ll_id;
      update dk_earve_rida set talnr=null where talnr=ll_id;
      update dk_graafik set talnr=null where talnr=ll_id;
      update dk_kanne set talnr=null where talnr=ll_id;
      update dk_viivis set talnr=null where talnr=ll_id;
      update ea_alus set talnr=null where talnr=ll_id;
      update ea_kanne set talnr=null where talnr=ll_id;
      update kp_alus set talnr=null where talnr=ll_id;
      update kp_kanne set talnr=null where talnr=ll_id;
      update lisalist set talnr=null where talnr=ll_id;
      update ls_makslist set talnr=null where talnr=ll_id;
      update ls_yksus set talnr=null where talnr=ll_id;
      update mt_alus set talnr=null where talnr=ll_id;
      update mt_kanne set talnr=null where talnr=ll_id;
      update mt_menu set talnr=null where talnr=ll_id;
      update mt_yksus set talnr=null where talnr=ll_id;
      update pa_erisood set talnr=null where talnr=ll_id;
      update pa_paryld set talnr=null where talnr=ll_id;
      update pa_reserv set talnr=null where talnr=ll_id;
      update pa_taslist set talnr=null where talnr=ll_id;
      update pa_tasu set talnr=null where talnr=ll_id;
      update pa_yksus set talnr=null where talnr=ll_id;
      update pl_resdefalus set talnr=null where talnr=ll_id;
      update pr_alus set talnr=null where talnr=ll_id;
      update pr_kanne set talnr=null where talnr=ll_id;
      update pv_kanne set talnr=null where talnr=ll_id;
      update pv_kanne_leping set talnr=null where talnr=ll_id;
      update pv_yksus set talnr=null where talnr=ll_id;
      delete from tallist where talnr=ll_id;
    when 'artlist' then
      update dk_alus set artnr=null where artnr=ll_id;
      update dk_autoviivis set artnr=null where artnr=ll_id;
      update dk_earve set artnr=null where artnr=ll_id;
      update dk_earve_rida set artnr=null where artnr=ll_id;
      update dk_graafik set artnr=null where artnr=ll_id;
      update dk_kanne set artnr=null where artnr=ll_id;
      update dk_viivis set artnr=null where artnr=ll_id;
      update ea_alus set artnr=null where artnr=ll_id;
      update ea_kanne set artnr=null where artnr=ll_id;
      update kblist set artnr=null where artnr=ll_id;
      update kontlist set artnr=null where artnr=ll_id;
      update kp_alus set artnr=null where artnr=ll_id;
      update kp_kanne set artnr=null where artnr=ll_id;
      update lisalist set artnr=null where artnr=ll_id;
      update ls_makslist set artnr=null where artnr=ll_id;
      update ls_yksus set artnr=null where artnr=ll_id;
      update mt_alus set artnr=null where artnr=ll_id;
      update mt_kanne set artnr=null where artnr=ll_id;
      update mt_menu set artnr=null where artnr=ll_id;
      update mt_yksus set artnr=null where artnr=ll_id;
      update pa_erisood set artnr=null where artnr=ll_id;
      update pa_reserv set artnr=null where artnr=ll_id;
      update pa_taslist set artnr=null where artnr=ll_id;
      update pa_yksus set artnr=null where artnr=ll_id;
      update pl_resdefalus set artnr=null where artnr=ll_id;
      update pr_alus set artnr=null where artnr=ll_id;
      update pr_kanne set artnr=null where artnr=ll_id;
      update pv_kanne set artnr=null where artnr=ll_id;
      update pv_kanne_leping set artnr=null where artnr=ll_id;
      update pv_yksus set artnr=null where artnr=ll_id;
      delete from artlist where artnr=ll_id;
    when 'tullist' then
      update dk_alus set tulnr=null where tulnr=ll_id;
      update dk_autoviivis set tulnr=null where tulnr=ll_id;
      update dk_earve set tulnr=null where tulnr=ll_id;
      update dk_earve_rida set tulnr=null where tulnr=ll_id;
      update dk_graafik set tulnr=null where tulnr=ll_id;
      update dk_kanne set tulnr=null where tulnr=ll_id;
      update dk_viivis set tulnr=null where tulnr=ll_id;
      update ea_alus set tulnr=null where tulnr=ll_id;
      update ea_kanne set tulnr=null where tulnr=ll_id;
      update kblist set tulnr=null where tulnr=ll_id;
      update kontlist set tulnr=null where tulnr=ll_id;
      update kp_alus set tulnr=null where tulnr=ll_id;
      update kp_kanne set tulnr=null where tulnr=ll_id;
      update lisalist set tulnr=null where tulnr=ll_id;
      update ls_makslist set tulnr=null where tulnr=ll_id;
      update ls_yksus set tulnr=null where tulnr=ll_id;
      update mt_alus set tulnr=null where tulnr=ll_id;
      update mt_kanne set tulnr=null where tulnr=ll_id;
      update mt_menu set tulnr=null where tulnr=ll_id;
      update mt_yksus set tulnr=null where tulnr=ll_id;
      update pa_erisood set tulnr=null where tulnr=ll_id;
      update pa_reserv set tulnr=null where tulnr=ll_id;
      update pa_taslist set tulnr=null where tulnr=ll_id;
      update pa_yksus set tulnr=null where tulnr=ll_id;
      update pl_resdefalus set tulnr=null where tulnr=ll_id;
      update pr_alus set tulnr=null where tulnr=ll_id;
      update pr_kanne set tulnr=null where tulnr=ll_id;
      update pv_kanne set tulnr=null where tulnr=ll_id;
      update pv_kanne_leping set tulnr=null where tulnr=ll_id;
      update pv_yksus set tulnr=null where tulnr=ll_id;
      delete from tullist where tulnr=ll_id;
    when 'objlist' then
      update dk_alus set objectnr=null where objectnr=ll_id;
      update dk_autoviivis set objectnr=null where objectnr=ll_id;
      update dk_earve set objectnr=null where objectnr=ll_id;
      update dk_earve_rida set objectnr=null where objectnr=ll_id;
      update dk_graafik set objectnr=null where objectnr=ll_id;
      update dk_kanne set objectnr=null where objectnr=ll_id;
      update dk_viivis set objectnr=null where objectnr=ll_id;
      update ea_alus set objectnr=null where objectnr=ll_id;
      update ea_kanne set objectnr=null where objectnr=ll_id;
      update kp_alus set objectnr=null where objectnr=ll_id;
      update kp_kanne set objectnr=null where objectnr=ll_id;
      update lisalist set objectnr=null where objectnr=ll_id;
      update ls_makslist set objectnr=null where objectnr=ll_id;
      update ls_yksus set objectnr=null where objectnr=ll_id;
      update mt_alus set objectnr=null where objectnr=ll_id;
      update mt_kanne set objectnr=null where objectnr=ll_id;
      update mt_menu set objectnr=null where objectnr=ll_id;
      update mt_yksus set objectnr=null where objectnr=ll_id;
      update pa_erisood set objectnr=null where objectnr=ll_id;
      update pa_paryld set objectnr=null where objectnr=ll_id;
      update pa_reserv set objectnr=null where objectnr=ll_id;
      update pa_taslist set objectnr=null where objectnr=ll_id;
      update pa_tasu set objectnr=null where objectnr=ll_id;
      update pa_yksus set objectnr=null where objectnr=ll_id;
      update pl_resdefalus set objectnr=null where objectnr=ll_id;
      update pr_alus set objectnr=null where objectnr=ll_id;
      update pr_kanne set objectnr=null where objectnr=ll_id;
      update pv_kanne set objectnr=null where objectnr=ll_id;
      update pv_kanne_leping set objectnr=null where objectnr=ll_id;
      update pv_yksus set objectnr=null where objectnr=ll_id;
      delete from objlist where objectnr=ll_id;
    when 'osaklist' then
      update dk_alus set osaknr=null where osaknr=ll_id;
      update dk_autoviivis set osaknr=null where osaknr=ll_id;
      update dk_earve set osaknr=null where osaknr=ll_id;
      update dk_earve_rida set osaknr=null where osaknr=ll_id;
      update dk_graafik set osaknr=null where osaknr=ll_id;
      update dk_kanne set osaknr=null where osaknr=ll_id;
      update dk_viivis set osaknr=null where osaknr=ll_id;
      update ea_alus set osaknr=null where osaknr=ll_id;
      update ea_kanne set osaknr=null where osaknr=ll_id;
      update kp_alus set osaknr=null where osaknr=ll_id;
      update kp_kanne set osaknr=null where osaknr=ll_id;
      update lisalist set osaknr=null where osaknr=ll_id;
      update ls_makslist set osaknr=null where osaknr=ll_id;
      update ls_yksus set osaknr=null where osaknr=ll_id;
      update mt_alus set osaknr=null where osaknr=ll_id;
      update mt_kanne set osaknr=null where osaknr=ll_id;
      update mt_menu set osaknr=null where osaknr=ll_id;
      update mt_yksus set osaknr=null where osaknr=ll_id;
      update pa_erisood set osaknr=null where osaknr=ll_id;
      update pa_paryld set osaknr=null where osaknr=ll_id;
      update pa_reserv set osaknr=null where osaknr=ll_id;
      update pa_taslist set osaknr=null where osaknr=ll_id;
      update pa_tasu set osaknr=null where osaknr=ll_id;
      update pa_yksus set osaknr=null where osaknr=ll_id;
      update pl_resdefalus set osaknr=null where osaknr=ll_id;
      update pr_alus set osaknr=null where osaknr=ll_id;
      update pr_kanne set osaknr=null where osaknr=ll_id;
      update pv_kanne set osaknr=null where osaknr=ll_id;
      update pv_kanne_leping set osaknr=null where osaknr=ll_id;
      update pv_yksus set osaknr=null where osaknr=ll_id;
      delete from osaklist where osaknr=ll_id;
    when 'sublist' then
      update dk_alus set subjectnr=null where subjectnr=ll_id;
      update dk_autoviivis set subjectnr=null where subjectnr=ll_id;
      update dk_earve set subjectnr=null where subjectnr=ll_id;
      update dk_earve_rida set subjectnr=null where subjectnr=ll_id;
      update dk_graafik set subjectnr=null where subjectnr=ll_id;
      update dk_kanne set subjectnr=null where subjectnr=ll_id;
      update dk_viivis set subjectnr=null where subjectnr=ll_id;
      update ea_alus set subjectnr=null where subjectnr=ll_id;
      update ea_kanne set subjectnr=null where subjectnr=ll_id;
      update kp_alus set subjectnr=null where subjectnr=ll_id;
      update kp_kanne set subjectnr=null where subjectnr=ll_id;
      update lisalist set subjectnr=null where subjectnr=ll_id;
      update ls_makslist set subjectnr=null where subjectnr=ll_id;
      update ls_yksus set subjectnr=null where subjectnr=ll_id;
      update mt_alus set subjectnr=null where subjectnr=ll_id;
      update mt_kanne set subjectnr=null where subjectnr=ll_id;
      update mt_menu set subjectnr=null where subjectnr=ll_id;
      update mt_yksus set subjectnr=null where subjectnr=ll_id;
      update pa_erisood set subjectnr=null where subjectnr=ll_id;
      update pa_paryld set subjectnr=null where subjectnr=ll_id;
      update pa_reserv set subjectnr=null where subjectnr=ll_id;
      update pa_taslist set subjectnr=null where subjectnr=ll_id;
      update pa_tasu set subjectnr=null where subjectnr=ll_id;
      update pa_yksus set subjectnr=null where subjectnr=ll_id;
      update pl_resdefalus set subjectnr=null where subjectnr=ll_id;
      update pr_alus set subjectnr=null where subjectnr=ll_id;
      update pr_kanne set subjectnr=null where subjectnr=ll_id;
      update pv_kanne set subjectnr=null where subjectnr=ll_id;
      update pv_kanne_leping set subjectnr=null where subjectnr=ll_id;
      update pv_yksus set subjectnr=null where subjectnr=ll_id;
      delete from sublist where subjectnr=ll_id;
    when 'suundlist' then
      update dk_alus set suundnr=null where suundnr=ll_id;
      update dk_autoviivis set suundnr=null where suundnr=ll_id;
      update dk_earve set suundnr=null where suundnr=ll_id;
      update dk_earve_rida set suundnr=null where suundnr=ll_id;
      update dk_graafik set suundnr=null where suundnr=ll_id;
      update dk_kanne set suundnr=null where suundnr=ll_id;
      update dk_viivis set suundnr=null where suundnr=ll_id;
      update ea_alus set suundnr=null where suundnr=ll_id;
      update ea_kanne set suundnr=null where suundnr=ll_id;
      update kp_alus set suundnr=null where suundnr=ll_id;
      update kp_kanne set suundnr=null where suundnr=ll_id;
      update lisalist set suundnr=null where suundnr=ll_id;
      update ls_makslist set suundnr=null where suundnr=ll_id;
      update ls_yksus set suundnr=null where suundnr=ll_id;
      update mt_alus set suundnr=null where suundnr=ll_id;
      update mt_kanne set suundnr=null where suundnr=ll_id;
      update mt_menu set suundnr=null where suundnr=ll_id;
      update mt_yksus set suundnr=null where suundnr=ll_id;
      update pa_erisood set suundnr=null where suundnr=ll_id;
      update pa_paryld set suundnr=null where suundnr=ll_id;
      update pa_reserv set suundnr=null where suundnr=ll_id;
      update pa_taslist set suundnr=null where suundnr=ll_id;
      update pa_tasu set suundnr=null where suundnr=ll_id;
      update pa_yksus set suundnr=null where suundnr=ll_id;
      update pl_resdefalus set suundnr=null where suundnr=ll_id;
      update pr_alus set suundnr=null where suundnr=ll_id;
      update pr_kanne set suundnr=null where suundnr=ll_id;
      update pv_kanne set suundnr=null where suundnr=ll_id;
      update pv_kanne_leping set suundnr=null where suundnr=ll_id;
      update pv_yksus set suundnr=null where suundnr=ll_id;
      delete from suundlist where suundnr=ll_id;
    when 'taotlist' then
      update dk_alus set taotnr=null where taotnr=ll_id;
      update dk_autoviivis set taotnr=null where taotnr=ll_id;
      update dk_earve set taotnr=null where taotnr=ll_id;
      update dk_earve_rida set taotnr=null where taotnr=ll_id;
      update dk_graafik set taotnr=null where taotnr=ll_id;
      update dk_kanne set taotnr=null where taotnr=ll_id;
      update dk_viivis set taotnr=null where taotnr=ll_id;
      update ea_alus set taotnr=null where taotnr=ll_id;
      update ea_kanne set taotnr=null where taotnr=ll_id;
      update kp_alus set taotnr=null where taotnr=ll_id;
      update kp_kanne set taotnr=null where taotnr=ll_id;
      update lisalist set taotnr=null where taotnr=ll_id;
      update ls_makslist set taotnr=null where taotnr=ll_id;
      update ls_yksus set taotnr=null where taotnr=ll_id;
      update mt_alus set taotnr=null where taotnr=ll_id;
      update mt_kanne set taotnr=null where taotnr=ll_id;
      update mt_menu set taotnr=null where taotnr=ll_id;
      update mt_yksus set taotnr=null where taotnr=ll_id;
      update pa_erisood set taotnr=null where taotnr=ll_id;
      update pa_paryld set taotnr=null where taotnr=ll_id;
      update pa_reserv set taotnr=null where taotnr=ll_id;
      update pa_taslist set taotnr=null where taotnr=ll_id;
      update pa_tasu set taotnr=null where taotnr=ll_id;
      update pa_yksus set taotnr=null where taotnr=ll_id;
      update pl_resdefalus set taotnr=null where taotnr=ll_id;
      update pr_alus set taotnr=null where taotnr=ll_id;
      update pr_kanne set taotnr=null where taotnr=ll_id;
      update pv_kanne set taotnr=null where taotnr=ll_id;
      update pv_kanne_leping set taotnr=null where taotnr=ll_id;
      update pv_yksus set taotnr=null where taotnr=ll_id;
      delete from taotlist where taotnr=ll_id;
    end case;
  end if;

  end loop xxxlist_loop;
  close c_xxxlist;

END