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.

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

--CREating with else

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(in_location||' IS DELETED');

ELSE
DBMS_OUTPUT.PUT_LINE('NO SUCH LOCATION FOUND');
END IF;
END LOOP;
END proc_imp_cursor;
/
SHOW ERRORS;

--TESTING-----------------------------------------------------------------------------
SELECT * FORM SITES;
--7 Rows selected

--trying to execute procedure having location that does not exist in table;
EXEC proc_imp_cursor('NEPAL');
--RESULT:NO SUCH LOCATION FOUND

--Re-inserting site id 9 with the location NORTHAMPTON
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.
--Execute your procedure passing NO PLACE in as the parameter
EXEC proc_imp_cursor('NO PLACE');

--What output do you get, is it as expected?
--NO SUCH LOCATION FOUND

--What output do you get, is it as expected?
--NO SUCH LOCATION FOUND
-------------------------------------------------------------------------------

--Using Explicit Cursors in SQL*Plus
CREATE OR REPLACE PROCEDURE proc_exp_cursor(in_cost courses.cost%TYPE) IS
vn_count NUMBER(4);

CURSOR cur_courses IS
SELECT title,duration,cost
FROM courses
WHERE cost>in_cost;

BEGIN
FOR rec_cur_courses IN cur_courses LOOP
IF rec_cur_courses.cost>in_cost THEN
vn_count:=cur_courses%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(rec_cur_courses.title);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL COURSES '||vn_count);


END proc_exp_cursor;
/
SHOW ERRORS;

--TESTING-----------------------------------------------------------------------------
--NO ERRORS
SELECT * FROM courses;
--9 rows selected.

SELECT title,cost FROM courses;
/*
TITLE COST
------------------------------ ----------
UNIX 1995
C++ 1995
ADA 1500
ORACLE 1995
SYBASE 1200
DOS 500
VMS 995
NETWARE 1995
INFORMIX 1200

9 rows selected.
*/

--Now executing the procedure

EXEC proc_exp_cursor(1500);
/*
RESULT:

UNIX
C++
ADA
ORACLE
NETWARE

*/

-----------------------------------------------------
--BONUS ACTIVITY 1