Notes

/*
Uonid:18413710
Sandip kumar subba
week 15
*/

--@E:18413710week15.txt

--To display the output in SQL*Plus you need to issue the SQL*Plus command SET SERVEROUTPUT ON
SET SERVEROUTPUT ON


--Function func_instructor_ct
CREATE OR REPLACE FUNCTION func_instructor_ct
RETURN NUMBER IS
vn_instructor_ct NUMBER(3);
BEGIN
SELECT COUNT(instructor_id) INTO vn_instructor_ct
FROM instructors;
RETURN vn_instructor_ct;
END func_instructor_ct;
/
SHOW ERRORS


--the SQL command to count the number of instructors in the instructor table
SELECT COUNT(instructor_id) FROM instructors;


--get any compilation errors, if so, what are they and how do you solve them?
-- No


--Procedure proc_using_func
CREATE OR REPLACE PROCEDURE proc_using_func IS
vn_no_of_instructors NUMBER(3);
BEGIN
vn_no_of_instructors := func_instructor_ct;
DBMS_OUTPUT.PUT_LINE ('There are ' || vn_no_of_instructors || ' instructor(s) in the database.');
END proc_using_func;
/
SHOW ERRORS


--the following accomplish? DBMS_OUTPUT.PUT_LINE ('There are ' || vn_no_of_instructors || ' instructor(s) in the database.');
-- Display the number of instructors


--get any compilation errors, if so, what are they and how do you solve them?
-- No errors


--Command to execute procedure
EXEC proc_using_func;

--Test
SELECT COUNT(instructor_id) FROM instructors;

--the result?
--14

--get any errors, if so what are they and how do you solve them?
--No errors



--Function func_param_ct which takes salary parameter
--@return number of instructors with salary higher than in_salary
CREATE OR REPLACE FUNCTION func_param_ct (in_salary NUMBER)
RETURN NUMBER IS
vn_instructor_ct NUMBER(3);
BEGIN
SELECT COUNT(instructor_id) INTO vn_instructor_ct
FROM instructors
WHERE salary > in_salary;
RETURN vn_instructor_ct;
END func_param_ct;
/
SHOW ERRORS


--Q What differences will the parameter have on the function?
-- The parameter will restrict the values


--Procedure proc_param_using_func
CREATE OR REPLACE PROCEDURE proc_param_using_func (in_salary NUMBER) IS
vn_no_of_instructors NUMBER(3);
BEGIN
vn_no_of_instructors := func_param_ct(in_salary);
DBMS_OUTPUT.PUT_LINE ('There are ' || vn_no_of_instructors || ' instructor(s) in the database.');
END proc_param_using_func;
/
SHOW ERRORS


-- Now type the following into SQL*Plus at the prompt
COLUMN object_name FORMAT A20;
COLUMN object_type FORMAT A20;
SELECT object_name, object_type FROM user_objects;


--Q What result does this give you? What does it tell you about procedures and functions?
-- Procedures and functions are also objects


--Command to execute procedure
EXEC proc_param_using_func(3000);

--Test
SELECT COUNT(instructor_id)
FROM instructors
WHERE salary > 3000;

--Q What are the results? Are they different? Why?
-- 9 instructors. Results are not different.

--Q Do you get any errors, if so what are they and how do you solve them?
-- No errors


--Q What are the commands to delete procedures and functions?
-- Include the commands to drop all the procedures and functions you have created in this activity

DROP FUNCTION func_instructor_ct;
DROP PROCEDURE proc_using_func;
DROP FUNCTION func_param_ct;
DROP PROCEDURE proc_param_using_func;


--viewing sequnces
DESC user_SEQUENCES;

SELECT sequence_name from user_SEQUENCES;

--creating sequence
CREATE SEQUENCE seq_company_id
START WITH 90
INCREMENT BY 2


--creating procedure
CREATE OR REPLACE PROCEDURE proc_add_company IS
vc_company_name companies.company_name%TYPE:='NEW COMPANY';

BEGIN
INSERT INTO companies(company_id, company_name)
VALUES(seq_company_id.NEXTVAL, vc_company_name);

END;
/
SHOW ERRORS;

--testing
--testing the inserted data
SELECT company_id from companies;
--7 rows
--Executing procedure
EXEC proc_add_company;
--testing the inserted data
SELECT company_id FROM companies;
--8 rows


--creating delete procedure
CREATE OR REPLACE PROCEDURE proc_delete_company IS
vn_company_id companies.company_id%TYPE;

BEGIN
SELECT seq_company_id.currval INTO vn_company_id
FROM dual;

DELETE FROM companies
WHERE company_id = vn_company_id;

END;
/
SHOW ERRORS;


--creating new delete procedure
CREATE OR REPLACE PROCEDURE proc_param_del_co
(in_company_id IN companies.company_id%TYPE)IS

BEGIN
DELETE FROM companies
WHERE company_id = vn_company_id;

END;
/
SHOW ERRORS;