Notes

--TEST SCRIPT
--++++++++++++++++++++++++++++++++++++++++++++++++++++++
--querying number of instructors from instructor table
SELECT COUNT(instructor_id) FROM instructors;
--14 Rows were returned
--EXECUTING procedure
EXEC proc_using_func;
--same result
--++++++++++++++++++++++++++++++++++++++++++++++++++++++

--creating function 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 func_param_ct;
/
SHOW ERRORS;
--******************************************************


--PROCEDURE FOR 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;
--#####################################################


--++++++++++++++++++++++TEST SCRIPT+++++++++++++++++++++++++++

--querying number of instructors from instructor table
SELECT salary FROM instructors;
SELECT COUNT(instructor_id) FROM instructors WHERE SALARY >5000;
--2 Rows were returned
--EXECUTING procedure
EXEC proc_param_using_func(5000);
--same result
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++