Notes

--shivani
--week 20
--crusor

-- to view active set of row
DESC sites;
SELECT * FROM sites;
--context area 10 rows returned;


CREATE OR REPLACE PROCEDURE proc_imp_cursor(in_sites sites.site_id%TYPE)IS


BEGIN
DELETE FROM sites s
WHERE s.site_id = in_sites;

END proc_imp_cursor;

/

SHOW errors;
INSERT INTO sites (site_id , location)
VALUES(9 , 'NORTHAMPTON');

EXEC proc_imp_cursor(9);

--for view whether the
SELECT site_id, location from sites;


--poping up messages whether the location is deleted or not exist any such location


CREATE OR REPLACE PROCEDURE proc_imp_cursor(in_sites sites.location%TYPE)IS


BEGIN
DELETE FROM sites s
WHERE s.location = in_sites;

IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(in_sites || ' DELETED');
ELSE
DBMS_OUTPUT.PUT_LINE('no such location');


END IF;
END proc_imp_cursor;
/

--testing
INSERT INTO sites (site_id , location)
VALUES(9 , 'NORTHAMPTON');
SELECT site_id, location from sites;
EXEC proc_imp_cursor('NORTHAMPTON');
--for view whether the
SELECT site_id, location from sites;
--output
--SQL> EXEC proc_imp_cursor('NORTHAMPTON');
--DELETED
--PL/SQL procedure successfully completed.

-- TO DROP MY FILE
DROP PROCEDURE proc_imp_cursor;
--Procedure dropped.
--copy paste above procedure
--re-testing placing thing in parameter;
INSERT INTO sites (site_id , location)
VALUES(9 , 'NORTHAMPTON');
EXEC proc_imp_cursor('NO PLACE');

--output
--SQL> EXEC proc_imp_cursor('NO PLACE');
--no such location
--PL/SQL procedure successfully completed.




--using EXPLICIT Cursor in sql * plus
CREATE OR REPLACE PROCEDURE proc_exp_cursor(in_cost courses.cost%TYPE) IS

CURSOR cur_name IS
SELECT course_id , title , cost
FROM courses c
WHERE c.cost = in_cost;



BEGIN
FOR rec_cur_name IN cur_name LOOP
IF rec_cur_name.cost = in_cost THEN
DBMS_OUTPUT.PUT_LINE(title ,cost ,'the courses with the certain prices');

END IF;
END LOOP;

END proc_exp_cursor;

/

--tseting
EXEC proc_exp_cursor(1200);

--to dropped
drop procedure proc_exp_cursor;


--using EXPLICIT Cursor in sql * plus
CREATE OR REPLACE PROCEDURE proc_exp_cursor(in_cost courses.cost%TYPE) IS

CURSOR cur_name IS
SELECT course_id , title , cost
FROM courses c
WHERE c.cost >= in_cost;

BEGIN
FOR rec_cur_name IN cur_name%ROWCOUNT LOOP
IF rec_cur_name.cost = in_cost THEN
DBMS_OUTPUT.PUT_LINE('the courses with the certain prices');

END IF;
END LOOP;

END proc_exp_cursor;

/

--tseting
EXEC proc_exp_cursor(1200);















--BONUS ACTIVITY 2
CREATE OR REPLACE PROCEDURE proc_comp_cursor(in_trainig companies.training_budget%TYPE)IS

CURSOR cur_companies IS
SELECT company_id , company_name, training_budget from
companies c
WHERE c. training_budget> in_trainig;
rec_train_comp cur_companies%ROWTYPE;

BEGIN
OPEN cur_companies;
FETCH cur_companies INTO rec_train_comp;
IF cur_companies%NOTFOUND
THEN DBMS_OUTPUT.PUT_LINE('NO SUCH COMPSNIES');
ELSE
WHILE cur_companies%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(rec_train_comp.company_id || '' || rec_train_comp.company_name);
FETCH cur_companies INTO rec_train_comp;
END LOOP;
END IF;
CLOSE cur_companies;
END proc_comp_cursor;
/
--testing
EXEC(58933.55);