Notes

/*
Name - Rahul Khatiwada
UN ID - 18417137

*/

SET SERVEROUTPUT ON

--Procedure
CREATE OR REPLACE PROCEDURE proc_imp_cursor(vc_location sites.location%TYPE) IS
BEGIN
DELETE FROM sites
WHERE location =vc_location;
END proc_imp_cursor;
/


--Inserting new site
INSERT INTO sites(site_id ,location) VALUES(9,'NORTHAMPTON');
--Checking the insertion
SELECT site_id,location FROM sites WHERE site_id=9;


EXECUTE proc_imp_cursor('NORTHAMPTON');

CREATE OR REPLACE PROCEDURE proc_imp_cursor(vc_location sites.location%TYPE) IS
BEGIN
DELETE FROM sites
WHERE location =vc_location;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('DELETED');
ELSE
DBMS_OUTPUT.PUT_LINE('NO SUCH LOCATION');
END IF;
END proc_imp_cursor;
/

EXECUTE proc_imp_cursor('NORTHAMPTON');

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

--NO PLACE

EXECUTE proc_imp_cursor('NO PLACE');


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

CURSOR cur_courses IS
SELECT course_id,title,duration,cost,subject_id FROM courses
WHERE cost>in_cost;
vn_rows NUMBER(2);
BEGIN
vn_rows:=0;
FOR rec_cur_courses IN cur_courses LOOP
vn_rows:=vn_rows+1;
DBMS_OUTPUT.PUT_LINE('Title :'|| rec_cur_courses.title || ',Cost :'|| rec_cur_courses.cost);
END LOOP;
DBMS_OUTPUT.PUT_LINE('No of outputs : ' ||vn_rows);
END proc_exp_cursor;
/
SHOW ERRORS;


EXECUTE proc_exp_cursor(1000);

--BONUS 1


CREATE OR REPLACE PROCEDURE proc_comp_budget(in_budget companies.training_budget%TYPE) IS
CURSOR cur_companies IS
SELECT company_id, company_name, training_budget
FROM companies
WHERE training_budget < in_budget;
rec_cur_companies cur_companies%ROWTYPE;

BEGIN
OPEN cur_companies;
FETCH cur_companies INTO rec_cur_companies;
WHILE cur_companies%FOUND LOOP
DBMS_OUTPUT.PUT_LINE('Company :'|| rec_cur_companies.company_name
|| ',Budget :'|| rec_cur_companies.training_budget );
FETCH cur_companies INTO rec_cur_companies;
END LOOP;

END proc_comp_budget;
/
SHOW ERRORS;

EXECUTE proc_comp_budget(100000);