Notes

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE proc_staff_email_cursor(in_festival_id festivals.festival_id%TYPE) IS

CURSOR cur_festivals IS
SELECT f.festival_id,s.firstname,s.surname,s.role,s.salary,s.email,s.contact,s.address.city city,s.address.street street,s.address.country country
FROM festivals f, TABLE(f.staff)s
WHERE f.festival_id=in_festival_id;

vc_staff1_firstname VARCHAR2(30);
vc_staff1_surname VARCHAR2(30);
vc_staff1_role VARCHAR2(30);
vn_staff1_salary NUMBER(8,2);
vc_staff1_email VARCHAR2(30);
vc_staff1_contact VARCHAR2(15);
vc_staff1_city VARCHAR2(30);
vc_staff1_street VARCHAR2(30);
vc_staff1_country VARCHAR2(30);

vc_staff2_firstname VARCHAR2(30);
vc_staff2_surname VARCHAR2(30);
vc_staff2_role VARCHAR2(30);
vn_staff2_salary NUMBER(8,2);
vc_staff2_email VARCHAR2(30);
vc_staff2_contact VARCHAR2(15);
vc_staff2_city VARCHAR2(30);
vc_staff2_street VARCHAR2(30);
vc_staff2_country VARCHAR2(30);

vc_staff3_firstname VARCHAR2(30);
vc_staff3_surname VARCHAR2(30);
vc_staff3_role VARCHAR2(30);
vn_staff3_salary NUMBER(8,2);
vc_staff3_email VARCHAR2(30);
vc_staff3_contact VARCHAR2(15);
vc_staff3_city VARCHAR2(30);
vc_staff3_street VARCHAR2(30);
vc_staff3_country VARCHAR2(30);
BEGIN

FOR rec_cur_festivals IN cur_festivals LOOP
if((cur_festivals%ROWCOUNT)=1) THEN
vc_staff1_firstname:=rec_cur_festivals.firstname;
vc_staff1_surname :=rec_cur_festivals.surname;
vc_staff1_role:=rec_cur_festivals.role;
vn_staff1_salary:=rec_cur_festivals.salary;
vc_staff1_email:=rec_cur_festivals.email;
vc_staff1_contact:=rec_cur_festivals.contact;
vc_staff1_city:=rec_cur_festivals.city;
vc_staff1_street:=rec_cur_festivals.street;
vc_staff1_country:=rec_cur_festivals.country;


ELSIF((cur_festivals%ROWCOUNT)=2) THEN
vc_staff2_firstname:=rec_cur_festivals.firstname;
vc_staff2_surname :=rec_cur_festivals.surname;
vc_staff2_role:=rec_cur_festivals.role;
vn_staff2_salary:=rec_cur_festivals.salary;
vc_staff2_email:=rec_cur_festivals.email;
vc_staff2_contact:=rec_cur_festivals.contact;
vc_staff2_city:=rec_cur_festivals.city;
vc_staff2_street:=rec_cur_festivals.street;
vc_staff2_country:=rec_cur_festivals.country;

ELSE
vc_staff3_firstname:=rec_cur_festivals.firstname;
vc_staff3_surname :=rec_cur_festivals.surname;
vc_staff3_role:=rec_cur_festivals.role;
vn_staff3_salary:=rec_cur_festivals.salary;
vc_staff3_email:=rec_cur_festivals.email;
vc_staff3_contact:=rec_cur_festivals.contact;
vc_staff3_city:=rec_cur_festivals.city;
vc_staff3_street:=rec_cur_festivals.street;
vc_staff3_country:=rec_cur_festivals.country;

END IF;
END LOOP;

vc_staff1_email:=vc_staff1_firstname||vc_staff1_surname||'@gmail.com';

UPDATE festivals f
SET f.staff=staff_varray_type(staff_type(vc_staff1_firstname,vc_staff1_surname,vc_staff1_role,vn_staff1_salary,vc_staff1_email,vc_staff1_contact,address_type(vc_staff1_city,vc_staff1_street,vc_staff1_country)));

END proc_staff_email_cursor;
/
SHOW ERRORS;


EXEC proc_staff_email_cursor(300005);