Notes

/*
Suman Chaulagain
18406485
*/


SET SERVEROUTPUT ON;


--Creating procedure
CREATE OR REPLACE PROCEDURE proc_imp_cursor (in_site_location sites.location%TYPE) IS
BEGIN

DELETE FROM sites WHERE location = in_site_location;
DBMS_OUTPUT.PUT_LINE(in_site_location || ' IS Deleted');

END proc_imp_cursor;
/
--Testing for errors
Show errors;

--CHECKING BEFORE INSERTION
SELECT site_id,location from sites;
--8 rows selected

--Insertion into sites table
INSERT INTO sites(site_id, location)
VALUES (9,'NORTHAMPTON');

--CHECKING AFTER INSERTION
SELECT site_id,location from sites;
--9 rows selected

--EXECUTING PROCEDURE
EXECUTE proc_imp_cursor('NORTHAMPTON');

--CHECKING AFTER RUNNING PROCEDURE
SELECT site_id,location from sites;
--8 rows selected

--DROPPING PROCEDURE
DROP PROCEDURE proc_imp_cursor;

--Insertion into sites table
INSERT INTO sites(site_id, location)
VALUES (9,'NORTHAMPTON');


--USING CURSOR
CREATE OR REPLACE PROCEDURE proc_imp_cursor (in_site_location sites.location%TYPE) IS
BEGIN
DELETE FROM sites WHERE location = in_site_location;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(in_site_location || ' Deleted');
ELSE
DBMS_OUTPUT.PUT_LINE(' NO SUCH LOCATION ');
END IF;
END proc_imp_cursor;
/
SHOW ERRORS;