CODEWITHSUNDEEP
oracle-databaseplsql
F0cus
F0cus

Reputation: 625

Oracle - Bulk multiple row Calculation

I have a similar requirement as below

Table Name : Salary_detail(has at least 10,00,000 records)

columns

salary - Number

hike - Number

net_salary --> cal_netsal(salary,hike)

(cal_netsal is a function to calculate net_sal)

total_sal ---> total_sal(salary,hike)

total_sal is a function

previous_sal --> previous_sal(salary,hike)

previous_sal is a function.

I need to calculate net_salary,total_sal and previous_sal columns

1.I need to calculate all three columns in a single row

2.insert back to the corresponding columns in that table. what would be the best way to achieve this?

Kindly help

Upvotes: 0

Views: 91

Answers (1)

Gaurav Soni
Gaurav Soni

Reputation: 6346

You can easily update the existing column by using the functions already created,The below update will take care of the existing records in the table.

UPDATE salary_details 
  SET net_salary = cal_netsal (salary, hike)
     , total_sal = total_sal (salary, hike)
     , previous_sal = previous_sal (salary, hike) WHERE 1 = 1

If new records are inserted or updated in the table,you can create a before insert trigger that will update the existing columns and no need to do it manually each time later on after the trigger is created.Trigger will take care of the DML operation happening on the table

CREATE OR REPLACE TRIGGER  sal_biu_trg
              BEFORE INSERT OR UPDATE ON salary_details
                 FOR EACH ROW
   BEGIN
      IF INSERTING OR UPDATING THEN
        :NEW.net_salary = cal_netsal (:NEW.salary,:NEW.hike);
        :NEW.total_sal = total_sal (:NEW.salary,:NEW.hike);
        :NEW.previous_sal = previous_sal (:NEW.salary,:NEW.hike);
     END IF;  
   EXCEPTION 
    WHEN OTHERS THEN 
       dbms_output.put_line(SQLERRM);  
   END;
/

Upvotes: 1

Related Questions