Notes

-- 18406508
-- SONAM SHERPA
-- FUNCTIONS

--CREATING A FUCNTION

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

--CREATING A PROCEDURE

CREATE OR REPLACE PROCEDURE proc_using_function 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_function;
/
SHOW ERRORS

--TO DISPLAY THE CONTENT
SET SERVEROUTPUT ON

--TEST SCRIPTS

--EXECUTING THE PROCEDURE
EXEC proc_using_function;

--RESULT AFTER EXECUTION OF PROCEDURE
--There are 14 instructor(s) in the database.

--CREATING A FUCNTION NAMED func_param_ct

CREATE OR REPLACE FUNCTION func_param_ct(in_salary instructors.salary%TYPE) RETURN NUMBER IS
vn_instructor_salary instructors.salary%TYPE;
BEGIN
SELECT salary INTO vn_instructor_salary from instructors WHERE salary > in_salary;

RETURN vn_instructor_salary;

END func_param_ct;
/
SHOW ERRORS


--CREATING A PROCEDURE NAMED proc_param_using_func

CREATE OR REPLACE PROCEDURE proc_using_function 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_function;
/
SHOW ERRORS