神谕。如何根据输入参数操作过程中的变量和操作

Question

首先，我要感谢 StackOwerflow 社区的每一个人。这个页面及其贡献者是信息和知识的源泉，在许多危急情况下解渴了。

对于这篇文章的篇幅，我也深表歉意，我只是想表达清楚。

我在 Oracle(10g 版本 10.2.0.5.0)数据库中有一个表，工作人员通过 php-jquery 网络应用程序将过滤器(大圆柱形袋)的状态记录到该表中。

这个表叫做 Filter_Bag_entries 有以下列

ID || EMPLOYEE_ID || THEDATE || FILTER_NO || BIN_NO || ACTION || FILTER_BAG_ID || INSERT_DATE || PERIOD

相关列是 Filter_NO 是过滤器的编号 (1-3)，Bin_no 是每个过滤器中的 bin 编号 (1-10)，Filter_bag_id 是每个过滤器在每个 bin 中的 ID (A1 -20:F1-20), Period 是bin当前的生命周期(X年后每个bin中的所有袋子都被更换，然后period number增加1)，最后是最后一个相关的列Action，它接受单个字母，例如 B(盲装，即损坏的包)、E(更新(冰岛))、U、S...等。

日志记录工作正常，一切正常。

但是，我需要的是按需轻松清点每个垃圾箱中的盲袋。这对于当前的表格来说可能有点棘手，因为袋子可以被隐藏、更新、再次隐藏等等。所以简单地计算 Bag_id 的 action=B 是不够的。这可以通过 php 例程来完成，但目前我在日志表中有很多数据，我想要一个好的、可靠的方法。

我的想法是使用另一个表，Blinded_Bags，它有以下列。

FILTER_NO || BIN_NO || IS_BLIND || FILTER_BAG_ID || PERIOD

ISBLIND 列在袋子被盲化时取值 1，在更新时取值 0。我将对 Period、Filter_NO、Bin_NO 和 Filter_bag_id 设置唯一约束，以便每个周期的每个过滤器的每个 bin 中的每个包只能存在一个条目。

然后我想使用执行以下操作的存储过程:

Worker 选择 Bag_id=B15，in Bin=1，Filter=1，并在 web-application 中选择“Blind”并提交到数据库。

数据库将条目输入到日志表中，就像今天一样。然后另一件事发生(通过程序)，只有当 Action 是“E”或“B”时才应该发生。检查 Action (在本例中为 B)，如果是“B”，则触发变量 var_isblind=1。该过程执行以下操作:

INSERT INTO BLINDED_BAGS(FILTER_NO, BIN_NO, PERIOD, FILTER_BAG_ID, ISBLIND) VALUES(1, 1, (select max(period) from filter_bag_entries where filter_no=1,bin_no=1), var_isblind)

如果触发约束错误，则意味着当前包有一个条目(可能 ISBLIND 的值为 0)，然后该过程会更新表。

现在，我知道如何制作一个过程，我可能也可以弄清楚如何将一个过程插入到多个表中，我还知道如何处理异常以在唯一约束失败时选择更新和插入。我不知道的是如何操纵变量。也就是说，我不知道如何在 oracle 中执行以下操作

if action == 'E'

    var_isblind = 0; // and contintue to insert or update into BLINDED_BAGS

else if action == 'B'

    var_isblind = 1; // and contintue to insert or update into BLINDED_BAGS

else

    Return false; // Exit procedure and don‘t do anything to BLINDED_BAGS

Answer 1

请在下面找到不言自明的代码在这里您可以找到工作 example

Oracle 中有一个“UPSERT”语句，称为MERGE

我在触发器中使用了它

create table filter_bag_entries (
  id            int     not null,
  employee_id   int,
  thedate       date,
  filter_no     int,
  bin_no        int,
  action        varchar2(3),
  filter_bag_id int,
  insert_date   date    default sysdate,
  period        int,
  constraint pk_filter_bag_entries primary key(id)
);


create table blinded_bags (
  filter_no     int,
  bin_no        int,
  is_blind      int,
  filter_bag_id int,
  period        int,
  constraint pk_blinded_bags primary key (filter_bag_id, period, filter_no, bin_no)
);


create or replace trigger tr_flt_bag_entr
after insert or update
on filter_bag_entries
for each row
when (new.action in ('E','B'))
declare
    l_isblind   int;
begin
    l_isblind := case :new.action
                    when 'E' then 0
                    when 'B' then 1
                 end;

    merge into blinded_bags tgt
    using ( select :new.filter_no as filter_no, :new.bin_no as bin_no, l_isblind as is_blind,
            :new.filter_bag_id as filter_bag_id, :new.period as period from dual
    ) src
    on (src.filter_no = tgt.filter_no and src.bin_no = tgt.bin_no
      and src.filter_bag_id = tgt.filter_bag_id and src.period = tgt.period
    )
    when matched then
        update  set tgt.is_blind = l_isblind
    when NOT matched then
        insert values (src.filter_no, src.bin_no, src.is_blind, src.filter_bag_id, src.period);
end;
/
show err

truncate table filter_bag_entries;

insert into filter_bag_entries
    (id, employee_id, thedate, filter_no, bin_no, action, filter_bag_id, period)
values (1, 100, sysdate, 1, 1, 'B', 31, 10);

insert into filter_bag_entries
    (id, employee_id, thedate, filter_no, bin_no, action, filter_bag_id, period)
values (2, 100, sysdate, 2, 2, 'B', 32, 10);

insert into filter_bag_entries
    (id, employee_id, thedate, filter_no, bin_no, action, filter_bag_id, period)
values (3, 100, sysdate, 3, 3, 'E', 33, 10);

insert into filter_bag_entries
    (id, employee_id, thedate, filter_no, bin_no, action, filter_bag_id, period)
values (4, 101, sysdate, 3, 3, 'E', 33, 11);

commit;

select * from blinded_bags;


update filter_bag_entries
  set action = 'B'
where filter_no=3 and bin_no=3 and filter_bag_id=33 and period=10 and action!='B';

select * from blinded_bags;

输出:

SQL> select * from blinded_bags;

 FILTER_NO     BIN_NO   IS_BLIND FILTER_BAG_ID     PERIOD
---------- ---------- ---------- ------------- ----------
         1          1          1            31         10
         2          2          1            32         10
         3          3          0            33         10
         3          3          0            33         11

SQL> update filter_bag_entries
  set action = 'B'
where filter_no=3 and bin_no=3 and filter_bag_id=33 and period=10 and action!='B';
  2    3
1 row updated.

SQL> select * from blinded_bags;

 FILTER_NO     BIN_NO   IS_BLIND FILTER_BAG_ID     PERIOD
---------- ---------- ---------- ------------- ----------
         1          1          1            31         10
         2          2          1            32         10
         3          3          1            33         10
         3          3          0            33         11