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


--Command to execute procedure
EXEC proc_using_func;

--Test
SELECT COUNT(instructor_id) FROM instructors;


--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


--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;


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

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

--drop command
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;

--BONUS ACTIVITY

--creating staff table
CREATE TABLE staff(
staff_id NUMBER(6),
firstname VARCHAR2(15),
surname VARCHAR2(15),
username VARCHAR2(10)
);
--inserting values into staff
INSERT INTO staff VALUES(1000,'ALISON','APPLE',NULL);
INSERT INTO staff VALUES(1001,'BERTY','BANABA',NULL);
INSERT INTO staff VALUES(1002,'CANDY','CANE',NULL);


--creating a function call func_username_staff

CREATE OR REPLACE FUNCTION func_username_staff
(in_staff_id staff.staff_id%TYPE) RETURN VARCHAR2 IS
vc_username staff.username%TYPE;

BEGIN
SELECT CONCAT(SUBSTR(firstname,1,2),SUBSTR(surname,1,5))
INTO vc_username
FROM staff
WHERE staff_id = in_staff_id;

RETURN vc_username;

END func_username_staff;
/
SHOW ERROR;

--creating a procedure to call a function to update data
CREATE OR REPLACE PROCEDURE proc_username
(in_staff_id IN staff.staff_id%TYPE) IS
vc_username staff.username%TYPE;

BEGIN
vc_username:= func_username_staff(in_staff_id);

UPDATE staff SET
username = vc_username
WHERE staff_id = in_staff_id;

END proc_username;
/
SHOW ERROR;

--calling the procedure to change the username
EXEC proc_username(1000);
EXEC proc_username(1001);
EXEC proc_username(1002);