Notes

/*

Name: Nom Rana

*/
SET SERVEROUTPUT ON

--view sites table
DESC sites

--#1
--create procedure proc_imp_cursor
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 proc_imp_cursor
SELECT site_id,location FROM sites;


--insert value fr 9 NORTHAMPTON in sites
INSERT INTO sites(site_id, location) VALUES (9, 'NORTHAMPTON');

-- check for addition of the new insert
SELECT site_id,location FROM sites;


--executing procedure
EXEC proc_imp_cursor('NORTHAMPTON');


SELECT site_id,location FROM sites;
--12 rows selected






--#2
--creating procedure with implicit cursor
CREATE OR REPLACE PROCEDURE proc_implicit_cursor(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_implicit_cursor;
/
SHOW ERRORS


--TESTING
EXEC proc_implicit_cursor('NO PLACE');

--view sites
SELECT site_id,location FROM sites;


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

-- view sites location
SELECT site_id,location FROM sites;


--executing procedure
EXEC proc_implicit_cursor('NORTHAMPTON');
--DISPLAYED 'DELETED NORTHAMPTON'


SELECT site_id,location FROM sites;
--12 rows selected


--#3
--viewing courses table's structure
DESC courses

--creating a procedure named proc_exp_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(1500);
--5 ROWS SHOWN
EXEC proc_exp_cursor(8888);
--no rows


-- BONUS ACTIVITY
--viewing companies table
DESC companies

-- creating procedire to display compnies training budget more than iput value
CREATE OR REPLACE PROCEDURE proc_exp_companies(in_budget companies.training_budget%TYPE) IS
CURSOR cur_training IS
SELECT company_id, company_name, training_budget FROM companies WHERE training_budget>= in_budget;

rec_cur_training cur_training%ROWTYPE;
BEGIN
OPEN cur_training;
FETCH cur_training INTO rec_cur_training;

IF cur_training%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('NO SUCH COMPANY FOUND');
ELSE
WHILE cur_training%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(rec_cur_training.company_id||' '||rec_cur_training.company_name||' '||rec_cur_training.training_budget);
FETCH cur_training INTO rec_cur_training;

END LOOP;
END IF;
END proc_exp_companies;
/
SHOW ERRORS
--PROCEDURE CREATED

--TESTING
SELECT company_id, company_name, training_budget FROM companies;

--EXECUTING
EXEC proc_exp_companies(33000);
--DISPLAYED
EXEC proc_exp_companies(9999900);
--NO SUCH COMPANY DISPLAYED