PL SQL Program Examples in Commision

 * PL/SQL Block checks for existing commission and  assign new commission of given employ. declare
  veno emp.empno%type  := &employ; 
  vname emp.ename%type;
  vcomm emp.comm%type; 
  x emp.comm%type; 
  begin
  select ename,comm into vname,vcomm from emp
  where empno = veno;
  x := vcomm;  -- Storing old commission
  if vcomm is null then
  vcomm := 3000;
  elsif vcomm = 0 then
  vcomm := 2500;
  else
  vcomm := vcomm + vcomm * .25;
  end if;
  update emp set comm = vcomm where empno =  veno;
  dbms_output.put_line(veno||'  '||vname||'  '||x
  ||'    '||vcomm);
  commit;
  end;
---------------------------------------------------------------
 

 * Pl/sql Block calculates new commission for all       employees using Explicit Cursors:
declare
cursor c1 is select empno,ename,comm from emp
order by deptno;
veno emp.empno%type;
vname emp.ename%type;
vcomm emp.comm%type;
begin
open c1;
IF c1%isopen THEN
dbms_output.put_line(' Employ New Commission Report ');
loop
fetch c1 into veno,vname,vcomm;
exit when c1%notfound;
if vcomm is null then
vcomm := 3000;
elsif vcomm = 0 then
vcomm := 2500;
else
vcomm := vcomm + vcomm * .25;
end if;
update emp set comm = vcomm where empno = veno;
dbms_output.put_line(c1%rowcount||'  '||veno
||'  '||vname||'  '||vcomm);
end loop;
dbms_output.put_line(c1%rowcount ||' Employees are updated with new commission ');
close c1;
-- commit;
ELSE
dbms_output.put_line(' Unable to open cursor.......... ');
END IF;
end;
---------------------------------------------------------------
 

* Procedure calculates the new commision for all        employees of a particular department
create or replace procedure cal_comm(vdept number) is
   cursor c1 is select empno,ename,comm from emp
   where deptno = vdept;
   i c1%rowtype;
begin
dbms_output.put_line(' Employ commision Report ');
for i in c1 loop
if i.comm is null then
i.comm := 3000;
elsif i.comm = 0 then
i.comm := 2500;
else
i.comm := i.comm + i.comm * .25;
end if;
update emp set comm = i.comm where empno = i.empno;
dbms_output.put_line(i.empno||'  '||i.ename||'  '||i.comm);
end loop;
commit;
end;