Notes

/* Cursor */

--@C:TempCSY2038(RM)Week19cur_prac_18413711.txt

/*
NAME : Rojen
UoN ID : 18413711
MODULE : DatabaseII
DATE : 24-03-2019
Week : 19
*/

-- creating a procedure called '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 procedure

-- Inserting a new site, with a site id of 9 and a location of NORTHAMPTON
INSERT INTO sites (site_id, location)
VALUES (50, 'NORTHAMPTON');

-- Checking to see if the site 9 has been inserted
SELECT * FROM sites WHERE location = 'NORTHAMPTON';

-- Executing your procedure passing NORTHAMPTON in as a parameter
EXEC proc_imp_cursor('NORTHAMPTON');

-- Checking to see if the site 9 has been deleted
SELECT * FROM sites WHERE location = 'NORTHAMPTON';




-- creating a procedure called 'proc_imp_cursor' with cursor
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE proc_imp_cursor(in_location sites.location%TYPE) IS

CURSOR cur_prac IS
SELECT site_id, location
FROM sites;

BEGIN

FOR rec_cur_prac IN cur_prac LOOP
IF rec_cur_prac.location = in_location THEN
DBMS_OUTPUT.PUT_LINE(rec_cur_prac.location || ' DELETED');
ELSE
DBMS_OUTPUT.PUT_LINE('NO SUCH LOCATION');
END IF;
END LOOP;

END proc_imp_cursor;
/
SHOW ERRORS;


-- Inserting a new site, with a site id of 9 and a location of NORTHAMPTON
INSERT INTO sites (site_id, location)
VALUES (50, 'NORTHAMPTON');

-- Checking to see if the site 9 has been inserted
SELECT * FROM sites WHERE location = 'NORTHAMPTON';

-- Executing your procedure passing '' in as a parameter
EXEC proc_imp_cursor('');

-- Checking to see if the site 9 has been deleted
SELECT * FROM sites WHERE location = 'NORTHAMPTON';






-- creating a procedure called 'proc_emp_cursor'
CREATE OR REPLACE PROCEDURE proc_emp_cursor(in_min_cost sites.location%TYPE) IS

CURSOR cur_courses IS
SELECT course_id, title, cost
FROM courses;

BEGIN

DBMS_OUTPUT.PUT_LINE('COURSE TITLE');
DBMS_OUTPUT.PUT_LINE('------------');
FOR rec_cur_courses IN cur_courses LOOP
IF rec_cur_courses.cost >= in_min_cost THEN
DBMS_OUTPUT.PUT_LINE(rec_cur_courses.title);
END IF;
END LOOP;

END proc_emp_cursor;
/
SHOW ERRORS;


-- testing procedure
-- determining the values in the cost column of the courses table
SELECT * FROM courses;

-- Executing your procedure passing '' in as a parameter
EXEC proc_emp_cursor(1000);






-- creating a procedure called 'proc_emp_cursor_line_number'
CREATE OR REPLACE PROCEDURE proc_emp_cursor_line_number(in_min_cost sites.location%TYPE) IS

CURSOR cur_courses IS
SELECT course_id, title, cost
FROM courses;

BEGIN

DBMS_OUTPUT.PUT_LINE('COURSE TITLE');
DBMS_OUTPUT.PUT_LINE('------------');
FOR rec_cur_courses IN cur_courses LOOP
IF rec_cur_courses.cost = in_min_cost THEN
DBMS_OUTPUT.PUT_LINE(rec_cur_courses.title);
END IF;
END LOOP;

END proc_emp_cursor_line_number;
/
SHOW ERRORS;


-- testing procedure
-- determining the values in the cost column of the courses table
SELECT * FROM courses;

-- Executing your procedure passing '' in as a parameter
EXEC proc_emp_cursor_line_number(1200);








-- creating a procedure called 'proc_emp_cursor_line_number'
CREATE OR REPLACE PROCEDURE proc_emp_cursor_line_number(in_min_cost sites.location%TYPE) IS

CURSOR cur_courses IS
SELECT course_id, title, cost
FROM courses;

BEGIN

DBMS_OUTPUT.PUT_LINE('COURSE NUMBER');
DBMS_OUTPUT.PUT_LINE('------------');
FOR rec_cur_courses IN cur_courses LOOP
IF rec_cur_courses.cost = in_min_cost THEN
DBMS_OUTPUT.PUT_LINE(rec_cur_courses.title);
END IF;
END LOOP;

END proc_emp_cursor_line_number;
/
SHOW ERRORS;


-- testing procedure
-- determining the values in the cost column of the courses table
SELECT * FROM courses;

-- Executing your procedure passing '' in as a parameter
EXEC proc_emp_cursor_line_number(1200);








-- creating a procedure called 'proc_emp_cursor_line_number'
CREATE OR REPLACE PROCEDURE proc_emp_cursor_line_no_threshold(in_min_cost sites.location%TYPE) IS

CURSOR cur_courses IS
SELECT course_id, title, cost
FROM courses
WHERE cost = in_min_cost;

count_threshold NUMBER(4) := cur_courses%ROWCOUNT;

BEGIN

DBMS_OUTPUT.PUT_LINE('COURSE NUMBER');
DBMS_OUTPUT.PUT_LINE('------------');

DBMS_OUTPUT.PUT_LINE(count_threshold);

END proc_emp_cursor_line_no_threshold;
/
SHOW ERRORS;


-- testing procedure
-- determining the values in the cost column of the courses table
SELECT * FROM courses;

-- Executing your procedure passing '' in as a parameter
EXEC proc_emp_cursor_line_no_threshold(1200);







-- BONUS ACTIVITY
-- creating a procedure that displays the companies who’s training budget exceeds a specified value, passed in
CREATE OR REPLACE PROCEDURE proc_emp_while_cursor(in_training_budget companies.training_budget%TYPE) IS

CURSOR cur_companies IS
SELECT company_id, company_name, training_budget
FROM companies;

rec_cur_companies cur_companies%ROWTYPE;

BEGIN

OPEN cur_companies;
FETCH cur_companies INTO rec_cur_companies;

WHILE cur_companies%FOUND LOOP
IF rec_cur_companies.training_budget > in_training_budget THEN
DBMS_OUTPUT.PUT_LINE(rec_cur_companies.company_id || ' ' || rec_cur_companies.company_name || ' ' || rec_cur_companies.training_budget);
END IF;

FETCH cur_companies INTO rec_cur_companies;

END LOOP;

-- CLOSE cur_companies;

END proc_emp_while_cursor;
/
SHOW ERRORS;

-- testing procedure
-- determining the values in the cost column of the courses table
SELECT * FROM companies;

-- Executing your procedure passing '' in as a parameter
EXEC proc_emp_while_cursor(100000);