Notes

/*
Run command

@E:18408446db2week16Func_Prac_18408446.sql

Damodar Bhattarai
18408446
Week 16
2019.2.17

*/
SET SERVEROUT ON;

--couting number of instructors
SELECT COUNT(instructor_id) FROM instructors;

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

--creating procedure to test func_instructor_ct
--#######################################################
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;

EXEC proc_using_func;