代码之家 › 专栏 › 技术社区 › user137348

可以用表变量重写这个过程吗

oracle sql

user137348 · 技术社区 · 15 年前

我有一个简单的删除过程,它使用了NA光标。我在某个地方读到表变量应该优先于游标。

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;

2 回复 | 直到 15 年前

Tony Andrews 15 年前

我不知道“表变量”是什么(我觉得它是一个SQL Server术语?)但我不会在这里使用光标,我会这样做:

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;

编辑 :“table var”实际上是 SQL Server concept

我将如上面所示对其进行编码,但是由于您特别想了解如何对集合进行编码,下面是另一种使用集合的方法:

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;

这可能不如以前的方法执行得好。

Alex Poole 15 年前

也许与问题的重点没有直接关系,但是…

在大多数情况下,我会按照Tony的建议来做;但是如果我发现自己处于一种需要光标版本的情况下,这样就可以根据每个已删除的行进行其他处理,那么我将使用 FOR UPDATE 和 WHERE CURRENT OF 功能:

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;

这将锁定您感兴趣的行,并且可以使删除的意图更加清晰。