Notes

/*
CURSOR PRACTICAL

NAME: PRAJWOL SIMKHADA
UoN ID: 18406512
DATE: 30 MAR, 2019
WEEK: 20
*/

SET SERVEROUTPUT ON;

-- viewing sites table and it contents
DESC sites;
SELECT * FROM sites;


--creating a procedure
CREATE OR REPLACE PROCEDURE proc_imp_cursor (in_location sites.location%TYPE) IS
BEGIN
DELETE FROM sites WHERE location=in_location;
END proc_imp_cursor;
/

SHOW ERRORS;

--viewing site id and location from sites table
SELECT site_id, location FROM SITES;

--inserting new data on the sites table
INSERT INTO sites(site_id, location)
VALUES(9,'NORTHAMPTON');

--executing the procedure

EXEC proc_imp_cursor(9);


--CREATING A PROCEDURE WITH ATTRIBUTE OF SQL CURSOR
CREATE OR REPLACE PROCEDURE proc_imp_cursor(in_sites sites.location%TYPE)IS


BEGIN
DELETE FROM sites s
WHERE s.location = in_sites;

IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(' DELETED');
ELSE
DBMS_OUTPUT.PUT_LINE('NO SUCH LOCATION');


END IF;
END proc_imp_cursor;
/

--EXEC
EXEC proc_imp_cursor('NO PLACE');
EXEC proc_imp_cursor('NORTHAMPTON');

--VIEWING COURSES TABLE
DESC COURSES;

--CREATING A PROCEDURE
CREATE OR REPLACE PROCEDURE proc_exp_cursor(in_cost courses.cost%TYPE) IS

CURSOR cur_name IS
SELECT course_id , title , cost
FROM courses c
WHERE c.cost = in_cost;

BEGIN
FOR rec_cur_name IN cur_name LOOP
IF rec_cur_name.cost = in_cost THEN
DBMS_OUTPUT.PUT_LINE(rec_cur_name.title || rec_cur_name.cost ||'the courses with the certain prices');

END IF;
END LOOP;

END proc_exp_cursor;
/