Notes

CREATE OR REPLACE PROCEDURE proc_get_details(in_festival_date festival_staff.festival_staff_date%TYPE) IS
CURSOR cur_manage_festival IS
SELECT t.festival_staff_id, t.staff_id as id, t.nature_id, t.ward_id, f.festival_name
FROM festival_staff t
JOIN festivals f ON f.nature_id = t.nature_id AND f.ward_id = t.ward_id
WHERE t.festival_staff_date = in_festival_date;

rec_cur_manage_festival cur_manage_festival%ROWTYPE;

CURSOR cur_staffs IS
SELECT distinct(f.staff_id) as sid,r.hours_worked as hrs
FROM festival_staff f, TABLE(f.staff_pay) r;

CURSOR cur_sts IS
SELECT s.staff_id, s.instructor_id, s.staff_first_name, s.staff_last_name, s.role, s.pay_rate
FROM staff s;

rec_cur_staffs cur_staffs%ROWTYPE;

vn_salary NUMBER(6);
BEGIN
OPEN cur_manage_festival;
FETCH cur_manage_festival INTO rec_cur_manage_festival;
IF cur_manage_festival%FOUND THEN
WHILE cur_manage_festival%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(rec_cur_manage_festival.festival_name||chr(10)||'-----------'||chr(10)||'Managed By '||chr(10)||'--------');
FOR rec_cur_sts IN cur_sts LOOP
IF (rec_cur_sts.instructor_id = rec_cur_manage_festival.id) THEN
FOR rec_cur_staffs IN cur_staffs LOOP
IF rec_cur_staffs.sid = rec_cur_manage_festival.id THEN
vn_salary := rec_cur_sts.pay_rate * rec_cur_staffs.hrs;
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(rec_cur_sts.staff_first_name||' '|| rec_cur_sts.staff_last_name ||' '||rec_cur_sts.role||' Salary Rs.'||vn_salary||chr(10));
END IF;
END LOOP;
FETCH cur_manage_festival INTO rec_cur_manage_festival;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('NO ROWS FOUND');
END IF;
END;
/