Package for Insert/Update old salary value in emp_sal_history table whenever salary get changed without trigger

Question

This is my Function

create or replace
function sal_incr
(
p_grade number)
return number
is
v_inc number;
begin
select raise_percent into v_inc from sal_inc where grade_id = p_grade;
return  1 + (v_inc/100);
end;

This is my procedure:

create or replace 
procedure sal_increm 
is
begin
UPDATE emp_task SET sal = sal * sal_incr(grade_id);
end;

how to do that package.. without using triggers how to update "old sal","modified by" and "modified on" in separate table

Answer 1

You can have multiple DML statements in a procedure; they'd be rather less useful if you couldn't. You can do a single insert into your history table based on the data in the task table, adding the executing user with the USER function and and the current time with SYSDATE.

create or replace 
procedure sal_increm 
is
begin
  insert into emp_sal_history (empno, old_sal, modified_by, modified_on)
  select empno, sal, user, sysdate
  from emp_task;

  update emp_task set sal = sal * sal_incr(grade_id);
end;
/

If you want to record the new salary as well you can calculate that in the insert too.

This will record a history record even for employees whose grade doesn't get an increment. If you have those or want to handle that possibility and exclude them, you can add

where sal_incr(grade_id) != 1

You could add that to the update as well.