create table emp as
with data as (select level lvl from dual connect by level <= 10)
select rownum employee_id ,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id,
0 salary_new,
0 christ_bonus
from hr.employees, data Table created.
create departments
create table departments as select * from hr.departments;
/create table jobs
create table jobs as select * from hr.jobs;
alter table jobs add ( raise_pct number);
alter table jobs add (christ_bonus_pct number);
/ALTER TABLE emp
ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id);
/
update jobs set raise_pct = 45 where job_id = 'AD_PRES';
update jobs set raise_pct = 10 where job_id = 'FI_MGR';
update jobs set raise_pct = 15 where job_id = 'IT_PROG';
update jobs set christ_bonus_pct = trunc(min_salary / 1000);
commit;
proces_old
create or replace package raise_salary_old is
procedure main;
procedure calc_raise(pnEmp_id number);
procedure init;
end;
/
create or replace package body raise_salary_old is
gSqlCnt number := 0;
gUptCnt number := 0;
--------------------------------------------------------------------------------------------------------------------------------
procedure main is
begin
init;
-- for every department except 10, row by row
for dept in (select * from departments where department_id <> 10) loop
gSqlCnt := gSqlCnt +1;
-- for every employee of the department
for emp in (Select employee_id, job_id, salary from emp
where department_id = dept.department_id) loop
gSqlCnt := gSqlCnt +1;
-- calc new salary
calc_raise(pnEmp_id => emp.employee_id);
end loop;
end loop;
dbms_output.put_line( 'OLD 1 prc- SQLs : '|| gSqlCnt|| '; upd: ' || gUptCnt);
end;
--------------------------------------------------------------------------------------------------------------------------------
procedure calc_raise(pnEmp_id number) is
vJob_id jobs.job_id%type;
nSalary number;
nOldSalary number;
nRaise_pct number;
begin
-- get main data - employee data
select salary, job_id into nOldSalary , vJob_id from emp where employee_id = pnEmp_id;
gSqlCnt := gSqlCnt +1;
-- get configuration - raise percentage
select raise_pct into nRaise_pct from jobs where job_id = vJob_id;
gSqlCnt := gSqlCnt +1;
-- calc new salary
nSalary := nOldSalary * (100 + nvl(nRaise_pct,0))/100;
-- save new data
update emp set salary_new = nSalary where employee_id = pnEmp_id;
gUptCnt := gUptCnt +1;
commit;
end;
--------------------------
procedure init is
begin
gSqlCnt := 0;
gUptCnt := 0;
end;
end;
/proces_new
create or replace package raise_salary_new is
type typ_jobs is table of jobs%rowtype index by varchar2(10);
type typEmp is table of emp%rowtype;
procedure init ;
procedure main;
procedure raise_salary(precEmp_io in out emp%rowtype, pnRaise_pct jobs.raise_pct%type);
function get_jobs( pnJob_id jobs.job_id%type) return jobs%rowtype;
procedure updEmp (pTabEmp typEmp) ;
end;
/
create or replace package body raise_salary_new is
--
gJobs typ_jobs;
gSqlCnt number := 0;
gUptCnt number := 0;
-------------------------------------------------------------------------------------------------------------------------------
-- control procedure
-------------------------------------------------------------------------------------------------------------------------------
procedure main is
nSalary number;
nRaise_pct number;
nBonus_pct number;
tabEmp typEmp;
recJobs Jobs%rowtype;
begin
init;
-- get data - dept and emp
select e.*
bulk collect into tabEmp
from departments d, emp e
where e.department_id = d.department_id
and d.department_id <> 10;
gSqlCnt := gSqlCnt +1;
for i in 1..tabEmp.count loop
-- get configuration
recJobs := get_jobs( pnJob_id => tabEmp(i).job_id);
raise_salary(precEmp_io => tabEmp(i), pnRaise_pct => recJobs.raise_pct);
end loop;
updEmp (pTabEmp => tabEmp);
dbms_output.put_line( 'NEW 1 prc - SQLs : '|| gSqlCnt|| '; upd: ' || gUptCnt);
end;
--------------------------------------------------------------------------------------------------------------------------------
-- loading the configuration
--------------------------------------------------------------------------------------------------------------------------------
function get_jobs( pnJob_id jobs.job_id%type) return jobs%rowtype is
nRaise_pct number;
begin
-- load ALL configuration to the global collection if empty
if gJobs.count = 0 then
for z in (select * from jobs) loop
gJobs(z.job_id) := z;
end loop;
gSqlCnt := gSqlCnt +1;
end if;
return gJobs(pnJob_id);
end;
--------------------------------------------------------------------------------------------------------------------------------
-- functional procedure
--------------------------------------------------------------------------------------------------------------------------------
procedure raise_salary(precEmp_io in out emp%rowtype, pnRaise_pct jobs.raise_pct%type) is
begin
-- salary calculation
precEmp_io.salary_new := precEmp_io.salary * (100 + nvl(pnRaise_pct,0))/100;
end;
--------------------------------------------------------------------------------------------------------------------------------
-- saving
--------------------------------------------------------------------------------------------------------------------------------
procedure updEmp (pTabEmp typEmp) is
begin
-- bulk update
forall i in 1..pTabEmp.count
update emp set salary_new = pTabEmp(i).salary_new
, christ_bonus = pTabEmp(i).christ_bonus
where employee_id = pTabEmp(i).employee_id;
gUptCnt := gUptCnt +1;
commit;
end;
--------------------------------------------------------------------------------------------------------------------------------
-- init
--------------------------------------------------------------------------------------------------------------------------------
procedure init is
begin
gJobs.delete;
gSqlCnt := 0;
gUptCnt := 0;
end;
end;
/proces_old_2
create or replace package raise_salary_old_2 is
procedure main;
procedure calc_raise(pnEmp_id number);
procedure init;
procedure calc_christ_bonus(pnEmp_id number);
end;
/
create or replace package body raise_salary_old_2 is
gSqlCnt number := 0;
gUptCnt number := 0;
--------------------------------------------------------------------------------------------------------------------------------
procedure main is
begin
init;
-- get dept data
for dept in (select * from departments where department_id <> 10) loop
gSqlCnt := gSqlCnt +1;
-- get emp data
for emp in (Select employee_id, job_id, salary from emp where department_id = dept.department_id) loop
gSqlCnt := gSqlCnt +1;
-- calc salary
calc_raise(pnEmp_id => emp.employee_id);
-- calc bonus
calc_christ_bonus(pnEmp_id => emp.employee_id) ;
end loop;
end loop;
dbms_output.put_line( 'OLD 2 prc - SQLs : '|| gSqlCnt|| '; upd: ' || gUptCnt);
end;
--------------------------------------------------------------------------------------------------------------------------------
procedure calc_raise(pnEmp_id number) is
vJob_id jobs.job_id%type;
nSalary number;
nOldSalary number;
nRaise_pct number;
begin
-- get emp data
select salary, job_id into nOldSalary , vJob_id from emp where employee_id = pnEmp_id;
gSqlCnt := gSqlCnt +1;
-- get configuration
select raise_pct into nRaise_pct from jobs where job_id = vJob_id;
gSqlCnt := gSqlCnt +1;
-- alghoritm
nSalary := nOldSalary * (100 + nvl(nRaise_pct,0))/100;
-- save data
update emp set salary_new = nSalary where employee_id = pnEmp_id;
gUptCnt := gUptCnt +1;
commit;
end;
--------------------------
procedure calc_christ_bonus(pnEmp_id number) is
vJob_id jobs.job_id%type;
nSalary number;
nBonus_pct number;
nBonus number;
begin
-- get emp data
select salary, job_id into nSalary , vJob_id from emp where employee_id = pnEmp_id;
gSqlCnt := gSqlCnt +1;
-- get configuration
select christ_bonus_pct into nBonus_pct from jobs where job_id = vJob_id;
gSqlCnt := gSqlCnt +1;
-- calc bonus
nBonus := nSalary * nvl(nBonus_pct,0)/100;
-- save data
update emp set christ_bonus = nBonus where employee_id = pnEmp_id;
gUptCnt := gUptCnt +1;
commit;
end;
--------------------------
procedure init is
begin
gSqlCnt := 0;
gUptCnt := 0;
end;
end;
/
proces_new_2
create or replace package raise_salary_new_2 is
type typ_jobs is table of jobs%rowtype index by varchar2(10);
type typEmp is table of emp%rowtype;
procedure init ;
procedure main;
procedure raise_salary(precEmp_io in out emp%rowtype, pnRaise_pct jobs.raise_pct%type);
procedure calc_christ_bonus(precEmp_io in out emp%rowtype, pnChrist_pct jobs.christ_bonus_pct%type ) ;
function get_jobs( pnJob_id jobs.job_id%type) return jobs%rowtype;
procedure updEmp (pTabEmp typEmp) ;
end;
/
create or replace package body raise_salary_new_2 is
--
gJobs typ_jobs;
gSqlCnt number := 0;
gUptCnt number := 0;
-------------------------------------------------------------------------------------------------------------------------------
-- control procedure
-------------------------------------------------------------------------------------------------------------------------------
procedure main is
nSalary number;
nRaise_pct number;
nBonus_pct number;
tabEmp typEmp;
recJobs Jobs%rowtype;
begin
init;
-- get data - dept and emp
select e.*
bulk collect into tabEmp
from departments d, emp e
where e.department_id = d.department_id
and d.department_id <> 10;
gSqlCnt := gSqlCnt +1;
for i in 1..tabEmp.count loop
-- get configuration
recJobs := get_jobs( pnJob_id => tabEmp(i).job_id);
raise_salary(precEmp_io => tabEmp(i), pnRaise_pct => recJobs.raise_pct);
calc_christ_bonus(precEmp_io => tabEmp(i), pnChrist_pct => recJobs.christ_bonus_pct );
end loop;
updEmp (pTabEmp => tabEmp);
dbms_output.put_line( 'NEW 2 prc - SQLs : '|| gSqlCnt|| '; upd: ' || gUptCnt);
end;
--------------------------------------------------------------------------------------------------------------------------------
-- loading the configuration
--------------------------------------------------------------------------------------------------------------------------------
function get_jobs( pnJob_id jobs.job_id%type) return jobs%rowtype is
nRaise_pct number;
begin
-- load ALL configuration to the global collection if empty
if gJobs.count = 0 then
for z in (select * from jobs) loop
gJobs(z.job_id) := z;
end loop;
gSqlCnt := gSqlCnt +1;
end if;
return gJobs(pnJob_id);
end;
--------------------------------------------------------------------------------------------------------------------------------
-- functional procedure
--------------------------------------------------------------------------------------------------------------------------------
procedure raise_salary(precEmp_io in out emp%rowtype, pnRaise_pct jobs.raise_pct%type) is
begin
-- salary calculation
precEmp_io.salary_new := precEmp_io.salary * (100 + nvl(pnRaise_pct,0))/100;
end;
--------------------------------------------------------------------------------------------------------------------------------
-- functional procedure
--------------------------------------------------------------------------------------------------------------------------------
procedure calc_christ_bonus(precEmp_io in out emp%rowtype, pnChrist_pct jobs.christ_bonus_pct%type ) is
begin
-- bonus calculation
precEmp_io.christ_bonus := precEmp_io.Salary * nvl(pnChrist_pct,0)/100;
end;
--------------------------------------------------------------------------------------------------------------------------------
-- saving
--------------------------------------------------------------------------------------------------------------------------------
procedure updEmp (pTabEmp typEmp) is
begin
-- bulk update
forall i in 1..pTabEmp.count
update emp set salary_new = pTabEmp(i).salary_new
, christ_bonus = pTabEmp(i).christ_bonus
where employee_id = pTabEmp(i).employee_id;
gUptCnt := gUptCnt +1;
commit;
end;
--------------------------------------------------------------------------------------------------------------------------------
-- init
--------------------------------------------------------------------------------------------------------------------------------
procedure init is
begin
gJobs.delete;
gSqlCnt := 0;
gUptCnt := 0;
end;
end;
/run
-----------------------------------------------------------
-- Old version with 1 process
-----------------------------------------------------------
declare
iOld1 integer := 1;
iNew1 integer := 1;
iOld2 integer := 1;
iNew2 integer := 1;
nStart number ;
nEnd number;
nTimeOld number;
nTimeNew number;
begin
if iOld1 = 1 then
dbms_output.put_line('------------------1------------------------------');
nStart := dbms_utility.get_time;
raise_salary_old.main; --- old
nEnd := dbms_utility.get_time;
nTimeOld := (nEnd - nStart)/100 ;
dbms_output.put_line('OLD 1 prc - Time: ' || nTimeOld);
------------------------------------------------------
end if;
-----------------------------------------------------------
-- New version with 1 process
-----------------------------------------------------------
if iNew1 = 1 then
nStart := dbms_utility.get_time;
raise_salary_new.main; --- new
nEnd := dbms_utility.get_time;
nTimeNew := (nEnd - nStart)/100 ;
--
dbms_output.put_line('NEW 1 prc - Time: ' || nTimeNew);
end if;
--------------------------------------------------------
-- Old version with 2 processes
-----------------------------------------------------------
if iOld2 = 1 then
---- ------------------------------------------------2
dbms_output.put_line('---------------------------2---------------------');
nStart := dbms_utility.get_time;
raise_salary_old_2.main; --- old
nEnd := dbms_utility.get_time;
nTimeOld := (nEnd - nStart)/100 ;
dbms_output.put_line('OLD 2 prc - Time: ' || nTimeOld);
end if;
if iNew2 = 1 then
-----------------------------------------------------------
-- New version with 2 processes
-----------------------------------------------------------
nStart := dbms_utility.get_time;
raise_salary_new_2.main; --- new
nEnd := dbms_utility.get_time;
nTimeNew := (nEnd - nStart)/100 ;
dbms_output.put_line('NEW 2 prc - Time: ' || nTimeNew);
end if;
end;
/