Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Working But Not Solving Mutating Table (Oracle 11g)
Procedure Working But Not Solving Mutating Table [message #575804] Mon, 28 January 2013 01:28 Go to next message
lukedba
Messages: 3
Registered: January 2013
Junior Member
Hi, I'm working on a database for a chiropractor in Bath. Am using Oracle 11g to model the design but may port everything over to MySQL later. I have made a procedure below that works perfectly (it updates one table based on values from a column from another table) but the problem is that I have not been able to execute it as a batch process. It only only updates based on the 'last' entry in the invoice table using the MAX function. Here is the code.

create or replace 
PROCEDURE update_customer_totalamt
AS
v_custid NUMBER;
v_amount NUMBER;

BEGIN
    SELECT AMOUNT into v_amount 
    FROM invoice
    WHERE invoiceid = (SELECT MAX(invoiceid) FROM invoice);
    
    SELECT CUSTID into v_custid
    FROM invoice
    WHERE invoiceid = (SELECT MAX(invoiceid) FROM invoice);
    
    UPDATE customer
    set TOTALAMT = TOTALAMT + v_amount
    where CUSTID = v_custid;
    COMMIT;
    
  
END;


What I wanted to do was trigger the invoice table to automatically update the customer table but that resulted in a mutating table error. Also many forums online seemed to negate the need for triggers in Oracle claiming that procedures were a better solution. All I want to do now is to run the above code as a batch each day at 7pm and have it operate on each row in the invoice table that was added between 7am and 7pm of current day.

Am new to this forum but not new to SQL. Any help is greatly appreciated. I am running Oracle on Windows Server 2008.

Thanks in advance, Luke
Re: Procedure Working But Not Solving Mutating Table [message #575808 is a reply to message #575804] Mon, 28 January 2013 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not clear what your procedure should do.
Please clarify and specify it.

If you want to update CUSTOMER table with all invoices that have occurred during the current day, just execute:
MERGE into CUSTOMER c 
  using (select CUST_ID, sum(AMOUNT) amount from INVOICE 
         where <invoice_date column> between trunc(sysdate)+7/24 and trunc(sysdate)+19/24
         group by CUST_ID) i
  on (i.CUST_ID = c.CUST_ID)
when matched then update set TOTALAMT = TOTALAMT + i.AMOUNT
when not matched then insert (CUST_ID, TOTALAMT) values (i.CUST_ID, i.AMOUNT)
/

You can put this in a job that will be executed each day at 7PM.

Regards
Michel

[Updated on: Mon, 28 January 2013 01:53]

Report message to a moderator

Re: Procedure Working But Not Solving Mutating Table [message #575819 is a reply to message #575808] Mon, 28 January 2013 02:29 Go to previous messageGo to next message
lukedba
Messages: 3
Registered: January 2013
Junior Member
Thank you! I reworked this solution a little and it actually works. Greatly appreciated. I had spent 2 days going around in circles with triggers and finally made small step forward with a procedure but this is much more specific. I will make sure to only post questions if a set of code is failing for a good 3 days or so.

Regards
Luke
Re: Procedure Working But Not Solving Mutating Table [message #575823 is a reply to message #575819] Mon, 28 January 2013 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also do it with a trigger, something like (code obviously non tested as I have not your tables):
create or replace trigger INVOICE_TRG_AIUR
after insert or update of amount on INVOICE
for each row
begin
  if inserting then
    begin
      update CUSTOMER set TOTALAMT = TOTALAMT + :new.amount where CUST_ID = :new.amount;
    exception
      when no_data_found then
        insert into CUSTOMER (CUST_ID, AMOUNT) values (:new.cust_id, :new.amount); 
  elsif updating then
    update CUSTOMER set TOTALAMT = TOTALAMT - :old.amount + :new.amount where CUST_ID = :old.cust_id;
  end if;
end;
/

But you have to take care how it will work when 2 sessions tries to work on the same customer(s).
If your application is OLTP (and it is likely for this kind of application) then it would work.
Given what you said ("I'm working on a database for a chiropractor in Bath"), it is more likely that 2 sessions will not work on the same customer.

Regards
Michel


Re: Procedure Working But Not Solving Mutating Table [message #575859 is a reply to message #575823] Mon, 28 January 2013 09:23 Go to previous messageGo to next message
Bill B
Messages: 1068
Registered: December 2004
Senior Member
Why are you maintaining a total in the customer record. A query like

select a.cust_id,a.cust_name,sum(b.amount) amount
from customer a, b invoice
where a.cust_id = b.cust_id
group by a.cust_id,a.cust_name


Will always be correct and is quick.

[Updated on: Mon, 28 January 2013 09:23]

Report message to a moderator

Re: Procedure Working But Not Solving Mutating Table [message #575861 is a reply to message #575859] Mon, 28 January 2013 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
A query like


A view with a query like...

Regards
Michel
Re: Procedure Working But Not Solving Mutating Table [message #575879 is a reply to message #575861] Mon, 28 January 2013 13:06 Go to previous message
lukedba
Messages: 3
Registered: January 2013
Junior Member
The MERGE solution seems to be closest to what I need. I had first thought to have the amounts as they are in the invoice table and just run queries on it obviously to get the total amount for each customer at any given time. However the client did specify that they wanted to see a 'running total' of how much eache customer has spent in the customer table so that is what I set out to do. My first attempt 2 days ago was to write a trigger which failed with a mutating table error. I found out quickly online that triggers are not always the best solution but it seemed to me to be the most logical. I then decided to write a procedure and use it as a batch process as I would have done many years ago when I was working on a Novel network doing things like backups. My procedure worked but only for the last record entered into the invoice table. So as it stands the MERGE solution updates my amount in the customer table accurately so I and the client can see the running total for each customer.

My customer table has the fields CUSTID, FIRSTNAME, LASTNAME, STREETADDRESS, POSTCODE, TELEPHONE, INJURY, NUMVISITS, TOTALAMT.

The client has been working in a clinic and only now just starting in her own clinic and all of her customer contact info is in a notebook. So this model will allow here to get contact info as well as sales data and cross reference it with location and type of injury so she is happy with that as a start. I am not doing any coding for an electronic payment system only the invoice table for legacy invoice data.

Regards Luke
Previous Topic: Schema populate
Next Topic: Unable to execute the procedure
Goto Forum:
  


Current Time: Wed Jul 30 00:08:59 CDT 2014

Total time taken to generate the page: 0.06150 seconds