Oracle. How can I manipulate variables and actions in procedures based on input-argument

Question

First I would like to thank everyone in the StackOwerflow community. This page and it's contributors is and are a fountain of information and knowledge, which has quenched my thirst in many a dire-situation.

I also apologize for the length of this post, I just wanted to be very clear.

I have a table in an Oracle (10g Release 10.2.0.5.0) database into which workers log the status of filters (large cylindrical bags) through a php-jquery web application.

This table is called Filter_Bag_entries has the following columns

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

The relevant columns are Filter_NO which is the number of the filter (1-3), Bin_no which is the number of bin in each filter (1-10), Filter_bag_id which is an ID for each filter in each bin (A1-20:F1-20), Period which is the current life-span of the bin (all the bags in each bin are replaced after X years and the period number then increases by 1), and finally the last relevant column Action, which takes single letters like B (for blinded, that is a damaged bag), E (for renewed (icelandic)), U, S... etc.

The logging works well and everything is fine.

What I need, however, is on demand easy go get count for blind bags in each bin. This can be a bit tricky with the current table since a bag can be blinded, renewed, blinded again and so on. So simply counting Bag_id‘s with action=B is not good enough. This could be accomplished with a php routine but currently I have a lot of data in the log-table and I want a good, solid method.

My idea is to use another table, Blinded_Bags, which has the following columns.

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

Where the ISBLIND column takes the value of 1 if the bag has been blinded and the value of 0 if it is renewed. I will place unique constraints on Period, Filter_NO, Bin_NO and Filter_bag_id so there can only exist a single entry for each bag in each bin in each filter for each period.

I then want to use a stored procedure that does the following:

Worker selects Bag_id=B15, in Bin=1, Filter=1, and selects „Blind“ in the web-application and submits to database.

The database enters the entry into the log table, just as it does today. Then another thing happens (through a proceedure) which should only happen if the Action is ‚E‘ or ‚B‘. The Action (B in this case) checked and if it is „B“ a variable is fired var_isblind=1. The procedure does the following:

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)

If a constraint-error is fired, it means that the current bag has an entry (presumably ISBLIND has the value 0) and the procedure then updates the table instead.

Now, I know how to make a procedure, I can probably figure out how to make a procedure insert into multiple tables as well, I also know how to manipulate exceptions to choose beteen update and insert if the unique constraint fails. What I don‘t know, is how to manipulate a variable. That is, I don‘t know how I can do the following in 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

Please find the self-explanatory code below Here you can sea a working example

There is an "UPSERT" statement in Oracle, called MERGE

I used it in the trigger

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;

Output:

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