Practice - 2

1) Create a Pl/SQL block to declare a cursor to select last name,first name,salary and hire date from the Employee table. Retrieve each row from the cursor and print the employee's information if the employee's salary is greater than $50,000 and the hire date is before 31-DEC-1997.

 

Declare

  Cursor cur1 Is

Select lastname,firstname,salary,hiredate;

From emp_tbl02;

Begin

If emp.sal>50000 and hire date<31-dec-1997;

emp_rec  emp_cur1%rowtype;

begin

open emp_cur1;

loop

fetch emp_cur1 into emp_rec;

exit when emp_cur1%notfound;

dbms_output.put_line(emp_rec.firstname||' '||emp_rec.lastname||' '||emp_rec.salary||' '||emp_rec.hiredate);

end loop;

end;

End;

2) Create a cursor named as emp_cur that contains employee's last name,first name,salary and commission. Use the while loop to work with one row at a time. Within the while loop,employee's salary and commission are added together to find the total income. Print the total company wages (the total of all employee salaries and commissions).

declare

cursor emp_cur is

select f_name,l_name,emp_sal,commision from emp_tabl;

emp_rec emp_cur%rowtype;

emp_income number(20);

v_counter number := 1;

cmp_wages number(20) := 0;

begin

open emp_cur;

while(v_counter <= 3) loop

fetch emp_cur into emp_rec;

emp_income := emp_rec.emp_sal + emp_rec.commision;

cmp_wages := cmp_wages + emp_income;

v_counter := v_counter + 1;

end loop;

dbms_output.put_line('cmpny wages are '||cmp_wages);

end;