Oracle. How can I manipulate variables and actions in procedures based on input argument
First I would like to thank everyone in the StackOwerflow community. This page and its contributors are and are a source of information and knowledge that has quenched my thirst in many dire situations.
I also apologize for the length of this post, I just wanted to be very clear.
I have a table in an Oracle database (10g Release 10.2.0.5.0) where workers log the status of filters (large cylindrical packages) through a php-jquery web app.
This table called Filter_Bag_entries has the following columns
ID || EMPLOYEE_ID || THEDATE || FILTER_NO || BIN_NO || ACTION || FILTER_BAG_ID || INSERT_DATE || PERIOD
The corresponding columns are Filter_NO, which is the filter number (1-3), Bin_no, which is the bin number in each filter (1-10), Filter_bag_id, which is the identifier for each filter in each bin (A1 -20: F1-20) A period, which is the current life of the bin (all bags in each bin are replaced after X years and then the period number is increased by 1) and finally the last corresponding column Action, which takes single letters such as B (for the blinded, this is damaged bag), E (for renewal (Icelandic)), U, S ... etc.
The logging works well and everything is fine.
What I need, however, is, on demand, it is easy to get a blind bag counter in each bin. This can be a little tricky with the current table, as the bag can be blinded, refurbished, blinded again, and so on. So just counting Bag_id with action = B is not good enough. This can be done with a php routine, but currently I have a lot of data in the log table and I want a good, reliable 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
If the ISBLIND column is 1 if the bag was blinded, and 0 if it was updated. I will set unique constraints on Period, Filter_NO, Bin_NO and Filter_bag_id, so in each bin in each filter, for each period, there can only be one entry for each packet.
Then I want to use a stored procedure that does the following:
The worker chooses Bag_id = B15, in Bin = 1, Filter = 1 and chooses "Blind" in the web application and is sent to the database.
The database enters the record into the log table as it does today. Then another thing happens (through the process), which should only happen if there is an action, E 'or, B'. The action (B in this case) is checked, and if it is "B" the variable is run 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 occurs, it means that the current bag has a record (presumably ISBLIND is 0) and then the procedure updates the table.
Now that I know how to make a procedure, I can probably figure out how to make an insert procedure on multiple tables, I also know how to manipulate exceptions to select the last update and insert if a unique constraint fails. I don't know how to manipulate the 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
source to share
Below is the code without explanation You can create a working example here
Oracle has a "UPSERT" statement called MERGE
I used it in a 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
source to share