Is it possible to rewrite this precedure using table vars

Question

I have a simple delete procedure which is using na cursor. I read somewhere that table vars should be preferred to cursors.

CREATE OR REPLACE PROCEDURE SMTAPP.LF_PLAN_VYMAZ (Str_oz varchar2, Num_rok number, Num_mesiac number, Str_s_trc_id varchar2) IS
    var_LF_PLAN_ID Number(20);
    cursor cur_plan is select id from lp_plan where lf_plan_id=var_LF_PLAN_ID;
BEGIN
   select ID into var_LF_PLAN_ID from lf_plan where oz=Str_oz and rok=Num_rok and mesiac=Num_mesiac and s_trc_id=Str_s_trc_id;
   for c1 in cur_plan loop
        delete from LP_PLAN_DEN where lp_plan_id=c1.id;
   end loop;
   delete from LP_PLAN where lf_plan_id=var_LF_PLAN_ID;
   delete from LP_PLAN_HIST where LF_PLAN_ID=var_LF_PLAN_ID;
   delete from LF_PLAN where id=var_LF_PLAN_ID;
END;

Answer 1

I don't know what a "table var" is (I have a feeling it is a SQL Server term?) but I would not use a cursor here, I would do this:

CREATE OR REPLACE PROCEDURE SMTAPP.LF_PLAN_VYMAZ 
   (Str_oz varchar2, Num_rok number, Num_mesiac number, Str_s_trc_id varchar2)
IS
    var_LF_PLAN_ID Number(20);
BEGIN
   select ID into var_LF_PLAN_ID
   from lf_plan
   where oz=Str_oz and rok=Num_rok and mesiac=Num_mesiac
   and s_trc_id=Str_s_trc_id;

   delete from LP_PLAN_DEN where lp_plan_id in 
      (select id from lp_plan where lf_plan_id=var_LF_PLAN_ID);
   delete from LP_PLAN where lf_plan_id=var_LF_PLAN_ID;
   delete from LP_PLAN_HIST where LF_PLAN_ID=var_LF_PLAN_ID;
   delete from LF_PLAN where id=var_LF_PLAN_ID;
END;

EDIT: "Table var" is indeed a SQL Server concept

I would code this as shown above, however since you specifically want to see how to do it with a collection, here is another way that uses one:

CREATE OR REPLACE PROCEDURE SMTAPP.LF_PLAN_VYMAZ 
   (Str_oz varchar2, Num_rok number, Num_mesiac number, Str_s_trc_id varchar2)
IS
    var_LF_PLAN_ID Number(20);
    TYPE id_table IS TABLE OF lp_plan.id%TYPE;
    var_ids id_table_type;
BEGIN
   select ID into var_LF_PLAN_ID
   from lf_plan
   where oz=Str_oz and rok=Num_rok and mesiac=Num_mesiac
   and s_trc_id=Str_s_trc_id;

   select id from lp_plan 
   bulk collect into var_ids
   where lf_plan_id=var_LF_PLAN_ID;

   forall i in var_ids.FIRST..var_ids.LAST
     delete from LP_PLAN_DEN where lp_plan_id = var_ids(i);

   delete from LP_PLAN where lf_plan_id=var_LF_PLAN_ID;
   delete from LP_PLAN_HIST where LF_PLAN_ID=var_LF_PLAN_ID;
   delete from LF_PLAN where id=var_LF_PLAN_ID;
END;

This will probably not perform as well as the previous method.

Answer 2

Perhaps not directly relevant to the point of the question, but...

I would do it as Tony suggests in most cases; but if I found myself in a situation where the cursor version was needed so some other processing could be done based on each deleted row, I'd use the FOR UPDATE and WHERE CURRENT OF functionality:

CREATE OR REPLACE PROCEDURE SMTAPP.LF_PLAN_VYMAZ (Str_oz varchar2, Num_rok number,
    Num_mesiac number, Str_s_trc_id varchar2)
IS
    var_LF_PLAN_ID Number(20);
    cursor cur_plan is
        select id from lp_plan where lf_plan_id=var_LF_PLAN_ID for update;
BEGIN
    select ID into var_LF_PLAN_ID
    from lf_plan
    where oz=Str_oz and rok=Num_rok and mesiac=Num_mesiac and s_trc_id=Str_s_trc_id;

    for c1 in cur_plan loop
        delete from LP_PLAN_DEN where current of cur_plan;
    end loop;

    delete from LP_PLAN where lf_plan_id=var_LF_PLAN_ID;
    delete from LP_PLAN_HIST where LF_PLAN_ID=var_LF_PLAN_ID;
    delete from LF_PLAN where id=var_LF_PLAN_ID;
END;

This would lock the rows you're interested it, and can make the intent of the delete clearer.