Notes

--@E:DikshyaWeek15cur_prac_18406490.txt

/*

Dikshya Ghale
UON Id: 18406490
Lab activity: Week20
March 24

*/

SELECT site_id, location
FROM sites;
--9 rows returned

--creating the procedure named proc_imp_cursor

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

BEGIN
DELETE FROM sites
WHERE location = in_location;

END proc_imp_cursor;
/
--PROCEDURE CREATED
SHOW ERRORS;
--NO ERRORS

--testing procedure
INSERT INTO sites(site_id,location)
VALUES(9, 'NORTHAMPTON');
--1 ROW INSERTED

--viewing the contents of sites
SELECT site_id, location
FROM sites;
--10 rows returned

--executing the procedure named proc_imp_cursor
EXEC proc_imp_cursor('NORTHAMPTON');
--procedure successfully executed

--testing if the procedure worked or not
SELECT site_id, location
FROM sites;
--9 rows returned
--location named northampton is deleted through procedure


--creating procedure named proc_imp_cursor
CREATE OR REPLACE PROCEDURE proc_imp_cursor(in_location IN sites.location%TYPE)
IS

BEGIN
DELETE FROM sites
WHERE location = in_location;

IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('NO SUCH LOCATION');

ELSE
DBMS_OUTPUT.PUT_LINE('DELETED '|| in_location);

END IF;
END proc_imp_cursor;
/
SHOW ERRORS;

--TESTING THE ABOVE PROCEDURE
INSERT INTO sites(site_id,location)
VALUES(9, 'NORTHAMPTON');

--executing the procedure named proc_imp_cursor passign wrong info
EXEC proc_imp_cursor('NEPAL');
--NO SUCH LOCATION

--executing the procedure named proc_imp_cursor passign right info
EXEC proc_imp_cursor('CHANDOL');
--DELETED CHANDOL

--using explicit cursors
CREATE OR REPLACE PROCEDURE proc_exp_cursor(in_cost IN courses.cost%TYPE) IS

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

BEGIN
FOR rec_cur_courses IN cur_courses LOOP
DBMS_OUTPUT.PUT_LINE
--IF rec_cur_courses.cost > in_cost THEN
--DBMS_OUTPUT.PUT_LINE(in_cost);
--END IF;
END LOOP;
END proc_exp_cursor;
/
--PROCEDURE CREATED
SHOW ERRORS;
--NO ERRORS

EXEC proc_exp_cursor(1200);
--procedure executed successfully

--using rowcount which is wrong
CREATE OR REPLACE PROCEDURE proc_exp_cursor(in_cost IN courses.cost%TYPE) IS
vn_cost NUMBER(10);

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

BEGIN
FOR rec_cur_courses IN cur_courses LOOP
vn_cost := in_cost%ROWCOUNT;
--DBMS_OUTPUT.PUT_LINE(vn_cost);
END LOOP;
END proc_exp_cursor;
/