Notes

/* FUNCTIONS*/
--@E:18406492_DB2/FuncPrac_18406492.txt


/*
NAME : ROSHAN GIRI
UoN ID : 18406492
MODULE : DatabaseII
Week : 16
*/

--CREATING FUNCTION NAME func_username
CREATE OR REPLACE FUNCTION func_username(in_staff_id staff.staff_id%TYPE)
RETURN VARCHAR2 IS
vc_username VARCHAR2(20);
BEGIN
SELECT CONCAT(SUBSTR(firstname,1,1),SUBSTR(surname,1,5))
FROM staff
WHERE staff_id = in_staff_id;

RETURN vc_username;
END func_username;
/

--CREATING FUNCTION NAME func_instructor_ct
CREATE OR REPLACE FUNCTION func_instructor_ct
RETURN NUMBER IS
vn_instructor_ct NUMBER(3);
BEGIN
SELECT COUNT(*)
INTO vn_instructor_ct
FROM instructors;

RETURN vn_instructor_ct;

END func_instructor_ct;
/
SHOW ERRORS


--CREATING PROCEDURE NAME 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||' instructors in the database.');
END proc_using_func;
/
SHOW ERRORS;
--No any errors

--To display output
SET SERVEROUTPUT ON;

EXEC proc_using_func;
--There are 14 instructors in the database

--CREATING FUNCTION func_param_ct using a parameter
CREATE OR REPLACE FUNCTION func_param_ct(in_salary NUMBER)
RETURN NUMBER IS
vn_instructor_ct instructors.salary%TYPE;
BEGIN
SELECT COUNT(*)
INTO vn_instructor_ct
FROM instructors
WHERE salary>in_salary;

RETURN vn_instructor_ct;

END func_param_ct;
/
SHOW ERRORS;
--No errors
--CREATING FUNCTION func_param_ct
CREATE OR REPLACE FUNCTION func_param_ct(in_salary NUMBER)
RETURN NUMBER IS
vn_instructor_ct instructors.salary%TYPE;

BEGIN
SELECT COUNT(*)
INTO vn_instructor_ct
FROM instructors
WHERE salary>in_salary;

RETURN vn_instructor_ct;

END func_param_ct;
/
SHOW ERRORS

--Creating the procedure named proc_using_func
CREATE OR REPLACE PROCEDURE proc_param_using_func(in_salary NUMBER) IS
vn_no_of_instructors instructors.salary%TYPE;

BEGIN
vn_no_of_instructors := func_param_ct(in_salary);
DBMS_OUTPUT.PUT_LINE('There are '||vn_no_of_instructors||' instructors in the database.');

END proc_param_using_func;
/
SHOW ERRORS;

--Executing the function
EXEC proc_param_using_func(4000);
There are 4 instructors in the database

--BONUS ACTIVITY ONE

CREATE OR REPLACE FUNCTION function_username
(in_staff_id NUMBER) RETURN VARCHAR2 IS

vc_username VARCHAR2(15);

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

RETURN vc_username;

END function_username;
/
SHOW ERRORS;


CREATE OR REPLACE PROCEDURE proc_username(in_staff_id staff.staff_id%TYPE)
IS
vc_firstlast VARCHAR2(15);

BEGIN
vc_firstlast := function_username(in_staff_id);
DBMS_OUTPUT.PUT_LINE('you are '||vc_firstlast);

END proc_username;
/
show errors;

--BONUS ACTIVITY 2
CREATE OR REPLACE PROCEDURE proc_test_update(in_staff_id staff.staff_id%TYPE) IS vc_firstlast VARCHAR2(20);

BEGIN
vc_firstlast := function_username(in_staff_id);
DBMS_OUTPUT.PUT_LINE ('The username is = ' || vc_firstlast );
UPDATE staff
SET username = vc_firstlast
WHERE staff_id = in_staff_id;

END proc_test_update;
/
SHOW ERRORS;