Notes

SET SERVEROUTPUT ON;

--describing staff
DESC 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;

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

--INSERTING INNTO SITES
INSERT INTO sites(site_id,location)
VALUES(11, 'NORTHAMPTON');
--inserted

SELECT site_id, location FROM sites;
--10 rows

--EXECUTING THE PROCEDURE PASSING NORTHAMPTON
EXEC proc_imp_cursor('NORTHAMPTON');
--procedure sucessfully completed

--checking
SELECT site_id, location FROM sites;
--9 rows returned procedure deleted sucessfully

--------------**----------------------------**
--CREATING THE IMPLICIT CURSOR
CREATE OR REPLACE PROCEDURE proc_imp_implicit(in_location sites.location%TYPE)IS
BEGIN
DELETE FROM sites WHERE location = in_location;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('DELETED' ||in_location);
ELSE
DBMS_OUTPUT.PUT_LINE('NO SUCH PLACE');
END IF;
END proc_imp_implicit;
/
SHOW ERRORS;
--procedure created with no errors

--EXECUTING THE PROCEDURE PASSING NORTHAMPTON
EXEC proc_imp_implicit('NO SUCH PLACE');

SELECT site_id, location FROM sites;


--TESTING
INSERT INTO sites(site_id,location)
VALUES(10, 'NORTHAMPTON');

--executing procedure
EXEC proc_imp_implicit('NORTHAMPTON');
--DISPLAYED 'DELETED NORTHAMPTON'
SELECT site_id,location FROM sites;
--9 rows selected

--------------**----------------------------**
--CREATING THE EXPLICIT CURSOR
CREATE OR REPLACE PROCEDURE proc_exp_cursor(in_cost courses.cost%TYPE)IS
CURSOR cur_cost IS
SELECT title,cost
FROM courses
WHERE cost>= in_cost;

vn_rows NUMBER(2);

BEGIN
DBMS_OUTPUT.PUT_LINE('SN COURSES COST');
DBMS_OUTPUT.PUT_LINE('--------------------------');
FOR rec_cur_cost IN cur_cost LOOP
DBMS_OUTPUT.PUT_LINE(cur_cost%ROWCOUNT||' '||rec_cur_cost.title||' '||rec_cur_cost.cost);
vn_rows:=cur_cost%ROWCOUNT;
END LOOP;

DBMS_OUTPUT.PUT_LINE('THERE ARE '||vn_rows||' ROWS');
END proc_exp_cursor;
/
SHOW ERRORS
--created

--TESTING
SELECT title, cost FROM courses;
--9 ROWS

EXEC proc_exp_cursor(1200);
--5 ROWS SHOWN
C
--no rows

--BONUS ACTIVITY 1 AND 2