--Notice that the SELECT INTO statement uses an equijoin.
The join condition is listed in the JOIN clause, indicating columns that are part of the primary key and foreign key constraints.
EXAMPLE 5: To determine employee bonus depending on on salary criteria.In this program,
the IF condition is evaluated first and if true, the bonus of the employee is calculated.
DECLARE
dno number:=&dno;
lname varchar2(50);
v_bonus number;
CURSOR EMPCUR IS SELECT last_name,department_name,salary from EMPLOYEES e
join departments d on (e.department_id=d.department_id )and e.department_id=dno;
BEGIN
FOR i IN EMPCUR LOOP
IF i.salary >= 20000 THEN
V_BONUS :=i.salary*.2;
ELSIF i.salary>=15000 THEN
V_BONUS := i.salary*.3;
ELSIF i.salary>= 10000 THEN
V_BONUS := i.salary *.4;
ELSE
V_BONUS := i.salary*.5;
END IF;
dbms_output.put_line('Name: '||i.last_name||'Salary is drawing :'||i.salary||' bonus= '||v_bonus);
END LOOP;
END;
/
The join condition is listed in the JOIN clause, indicating columns that are part of the primary key and foreign key constraints.
EXAMPLE 5: To determine employee bonus depending on on salary criteria.In this program,
the IF condition is evaluated first and if true, the bonus of the employee is calculated.
DECLARE
dno number:=&dno;
lname varchar2(50);
v_bonus number;
CURSOR EMPCUR IS SELECT last_name,department_name,salary from EMPLOYEES e
join departments d on (e.department_id=d.department_id )and e.department_id=dno;
BEGIN
FOR i IN EMPCUR LOOP
IF i.salary >= 20000 THEN
V_BONUS :=i.salary*.2;
ELSIF i.salary>=15000 THEN
V_BONUS := i.salary*.3;
ELSIF i.salary>= 10000 THEN
V_BONUS := i.salary *.4;
ELSE
V_BONUS := i.salary*.5;
END IF;
dbms_output.put_line('Name: '||i.last_name||'Salary is drawing :'||i.salary||' bonus= '||v_bonus);
END LOOP;
END;
/