Notes

/*
Ramesh Kunwar
18413696
Week 20
Term II
Database II

*/


--**************************************************************************************************************************--
--Creating the procedure named proc_imp_cursor

CREATE OR REPLACE PROCEDURE proc_imp_cursor(in_location sites.location%TYPE) IS

CURSOR cur_sites IS
SELECT location
FROM sites;

BEGIN
FOR rec_cur_sites IN cur_sites LOOP
IF rec_cur_sites.location=in_location THEN
DELETE FROM sites
WHERE location=in_location;
DBMS_OUTPUT.PUT_LINE('LOCATION DELETED');
END IF;
END LOOP;
END proc_imp_cursor;
/
SHOW ERRORS;

---TESTING-----------------------------------------------------------------------------
SELECT * FROM sites;
-- 8 rows selected.

SELECT locations FROM sites;
--Executing the procedre passing the loactin value to
EXEC proc_imp_cursor('EUROPE WEST');

--7 rows selected. and sites having location - EUROPE WEST is deleted.


--Now inserting new row to the table.
INSERT INTO sites (site_id, address, classroom, location) VALUES (
9, address_type('3 PLACE DE CONCORDE','PARIS','FRANCE'),
classroom_varray_type (classroom_type (1, 20, NULL),
classroom_type (2, 24, NULL),
classroom_type (3, 30, NULL)),
'NORTHAMPTON');

--1 row created
--Now executing the procedure having cursor by passing argument as NORTHAMPTON
EXEC proc_imp_cursor('NORTHAMPTON');
--RESULT: LOCATION DELETED;

--SELECT * FROM sites;
--7 rows selected.

--**************************************************************************************************************************--