Create a PL/SQL database programming block with DECLARE BEGIN END; In Declare section define a local scalar (simple) variables. lv_deptno NUMBER And assign initial values as 10 lv_deptno NUMBER := 50; In Declare section again, define a cursor and select last_name, salary and manager_id of employees the ones who are working in the department defined in a) In this example department will be lv_deptno:=50; Sample code: Cursor c_emp as SELECT last_name, salary, manager_id FROM employees WHERE department_id = lv_deptno; In the execution section (BEGIN and END) Create CURSOR FOR LOOP for cursor to process In this cursor loop, your code has to check salary and also manager_id numbers each time new records being fetched. And based on these conditions either employee gets raise or not get raise. If employee salary is less than 5000 and also if employees managers is either 101 or 124 then it means employee is due for raise otherwise employee is not due for raise Sample code FOR erec IN c_emp LOOP If erec.salary < 5000 and erec.manager_id IN (101,124) THEN DBMS_OUTPUT.PUT_LINE (erec.last_name || ‘ Due for Raise’); ELSE DBMS_OUTPUT.PUT_LINE (erec.last_name || ‘ Not due for Raise’); END LOOP;
Create a PL/SQL
DECLARE
BEGIN
END;
- In Declare section define a local scalar (simple) variables. lv_deptno NUMBER
And assign initial values as 10
lv_deptno NUMBER := 50;
- In Declare section again, define a cursor and select last_name, salary and manager_id of employees the ones who are working in the department defined in a)
In this example department will be lv_deptno:=50;
Sample code:
Cursor c_emp as
SELECT last_name, salary, manager_id FROM employees
WHERE department_id = lv_deptno;
- In the execution section (BEGIN and END)
Create CURSOR FOR LOOP for cursor to process
In this cursor loop, your code has to check salary and also manager_id numbers each time new records being fetched. And based on these conditions either employee gets raise or not get raise.
If employee salary is less than 5000 and also if employees managers is either 101 or 124 then it means employee is due for raise otherwise employee is not due for raise
Sample code
FOR erec IN c_emp LOOP
If erec.salary < 5000 and erec.manager_id IN (101,124) THEN
DBMS_OUTPUT.PUT_LINE (erec.last_name || ‘ Due for Raise’);
ELSE
DBMS_OUTPUT.PUT_LINE (erec.last_name || ‘ Not due for Raise’);
END LOOP;
Step by step
Solved in 2 steps
