Notes

/*
Name:Rahul Khatiwada
UN ID-18417137

*/


--creating func_instructor_ct functions
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;
/
SHOW ERRORS



--to check wether the procedure is created or not
select object_name from user_objects;
--output: func_instructor_ct is created



--procedure of the func_instructor_ct functions

SET SERVEROUTPUT ON

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;
/
SHOW ERRORS


--to check wether the procedure is created or not
select object_name from user_objects;
--output: proc_using_func is created


--command to execute the procedure
--execute name_of_procedure;



SET SERVEROUTPUT ON
--what it does


--copy and rename func_instructor_ct to func_param_ct
CREATE OR REPLACE FUNCTION func_param_ct (in_salary instructors.salary%TYPE) 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;
/
SHOW ERRORS

--to check wether the procedure is created or not
select object_name from user_objects;
--output: func_param_ct function is created




--procedure of func_param_ct function
CREATE OR REPLACE PROCEDURE proc_param_using_func (in_salary instructors.salary%TYPE) 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
--output: proc_param_using_func procedure is created




--BONUS Activity

CREATE OR REPLACE FUNCTION func_upda_username (in_staff_id staff.staff_id%TYPE) RETURN VARCHAR2 IS
vc_name VARCHAR2(15);
BEGIN
SELECT firstname INTO first, surname INTO second
FROM staff
WHERE staff_id = in_staff_id;

SELECT CONCAT(SUBSTR(first,1,2), SUBSTR(second,1,5)) INTO vc_name
FROM staff
WHERE staff_id = in_staff_id;

UPDATE staff SET username = vc_name
WHERE staff_id = in_id;

RETURN vc_name;
END func_upda_username;
/
SHOW ERRORS