Notes

--@E:18406550-Database2ScriptFilescur_prac_18406550.sql
/*
Sandesh Bindukar 18406550
Database-2 Week 20 Lab Work
March 30, 2019
*/
--to display output on the screen
SET SERVEROUTPUT ON;

DESC sites;

--DROP
DROP PROCEDURE proc_imp_cursor;
DROP PROCEDURE proc_exp_cursor;
DROP PROCEDURE proc_cur_comp;


--creating procedure
CREATE OR REPLACE PROCEDURE proc_imp_cursor(in_location sites.location%TYPE) IS
BEGIN
DELETE FROM sites WHERE location = in_location;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(in_location || ' DELETED ');
ELSE
DBMS_OUTPUT.PUT_LINE('NO SUCH LOCATION');
END IF;
END proc_imp_cursor;
/
SHOW ERRORS;
--no errors

--TESTING

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

INSERT INTO sites(site_id, location)
VALUES(9,'NORTHAMPTON');
--1 rows created

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

--executing procedure
exec proc_imp_cursor('NORTHAMPTON');
--NORTHAMPTON DELETED is shown and deleted sucessfully.

--executing procedure
exec proc_imp_cursor('NO PLACE');
--no such location

SELECT site_id, location FROM sites;
--10 rows returned and the site 9 is deleted sucessfully.


DESC courses;

--creating procedure to display the courses above a minimun cost
CREATE OR REPLACE PROCEDURE proc_exp_cursor(in_cost courses.cost%TYPE) IS
CURSOR cur_cost IS
SELECT course_id, title, cost
FROM courses
WHERE cost > in_cost;
vn_counter NUMBER(3) := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('SN' || ' ' || 'TITLE' || ' ' || 'COST' );
FOR rec_cur_cost IN cur_cost LOOP
DBMS_OUTPUT.PUT_LINE(cur_cost%ROWCOUNT || ' ' || rec_cur_cost.title || ' ' || rec_cur_cost.cost );
vn_counter := vn_counter + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('There are ' || vn_counter || ' courses that meets the thresshold');
END proc_exp_cursor;
/
SHOW ERRORS;
--no errors

--TESTING
--executing procedure
exec proc_exp_cursor(1000);
--courses above 1000 cost are displayed
--There are 7 courses that meets the thresshold is displayed

--executing procedure
exec proc_exp_cursor(5000);
--no courses were displayed
--There are 0 courses that meets the thresshold is displayed


--BONUS ACTIVITY 1

DESC companies;

--creating procedure
CREATE OR REPLACE PROCEDURE proc_cur_comp(in_training_budget companies.training_budget%TYPE) IS
CURSOR cur_ck_comp IS
SELECT company_id, company_name, training_budget
FROM companies
WHERE in_training_budget > training_budget;
rec_cur_ck_comp cur_ck_comp%ROWTYPE;
BEGIN
OPEN cur_ck_comp;
FETCH cur_ck_comp INTO rec_cur_ck_comp;
DBMS_OUTPUT.PUT_LINE('SN' || ' ' || 'COMPANY ID' || ' ' || 'COMPANY NAME' );
WHILE cur_ck_comp%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(cur_ck_comp%ROWCOUNT || ' ' || rec_cur_ck_comp.company_id || ' ' || rec_cur_ck_comp.company_name );

FETCH cur_ck_comp INTO rec_cur_ck_comp;
END LOOP;
END proc_cur_comp;
/
SHOW ERRORS;
--created without any errors

--TESTING

SELECT company_id, company_name, training_budget FROM companies;
--8 rows selected

--executing procedures
exec proc_cur_comp(200000);
--3 rows returned with the comapny id and name