Notes

--@E:amreetUserCreate.txt
/*
NAME: Amrit Thapa
ID: 18406540
WEEK:14
Activity : 1
DATE:
*/

--Creating a procedures to calla function name func_instructor_ct
SET SERVEROUTPUT ON

--create a function func_instructor_ct which return no of instructor
CREATE 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;
/--FUNCTION CREATED

--VIEW ERRORS
SHOW ERRORS;

--create the procedure to test the function which print the no of instructor
CREATE OR REPLACE PROCEDURE proc_using_func IS
vn_no_of_instructors NUMBER(3);
--vN prefix is used for the new variable because we are storing NUMBER data
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;--END OF THE PROCEDURE
/

--testing
-- 1) Select without execute
SELECT * FROM instructors;
--14 rows returned

-- 2) Execute the procedure
EXEC proc_using_func;

-- 3)Select after execute
SELECT * FROM instructors;
--14 rows returned

--create a function func_instructor_ct which return
CREATE FUNCTION func_param_ct(in_salary instructors.salary%TYPE) RETURN NUMBER IS
vn_instructor_ct NUMBER(3);
BEGIN
SELECT COUNT(salary)
INTO vn_instructor_ct
FROM instructors
WHERE salary > in_salary;
RETURN vn_instructor_ct;
END func_param_ct;
/
--FUNCTION CREATED

--create the procedure to test the function which print the no of instructor
CREATE OR REPLACE PROCEDURE proc_param_using_func(in_salary instructors.salary%TYPE) IS
vn_no_of_instructors NUMBER(3);
--vN prefix is used for the new variable because we are storing NUMBER data
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;
/

--testing
-- 1) Select without execute
SELECT * FROM instructors;
--14 rows returned

-- 2) Execute the procedure
EXEC proc_param_using_func;

-- 3)Select after execute
SELECT * FROM instructors;
--14 rows returned











--CREATE A TABLE STAFF
CREATE TABLE staff(
staff_id NUMBER(6),
firstname VARCHAR2(15),
surname VARCHAR2(15),
username VARCHAR2(10));

--INSERT INTO TABLE STAFF
INSERT INTO staff VALUES (1000, 'ALISON','APPLE', NULL);
INSERT INTO staff VALUES (1001, 'BERTY','BANANA', NULL);
INSERT INTO staff VALUES (1002, 'CANDY','CANE', NULL);

--CREATE A FUNCTION TO CREATE A USERNAME FROM THE FIRST NAME AND THE SURNAME OF THE TABLE STAFF
CREATE FUNCTION func_staff(in_staff_id NUMBER) RETURN VARCHAR2 IS
vc_stff_un VARCHAR2(10);
BEGIN
SELECT CONCAT(SUBSTR(firstname,1,2),SUBSTR(surname,1,5))
INTO vc_stff_un
FROM staff
WHERE staff_id = in_staff_id;
RETURN vc_stff_un;
END func_staff;
/

--FIND THE ERRORS
SHOW ERRORS;

--CREATE A PROCEDURE FOR A FUNCTION func_staff TO EXECUTE THE FUNCTION
CREATE OR REPLACE PROCEDURE proc_staff(in_staff_id staff.staff_id%TYPE) IS
vc_staff_username staff.username%TYPE;
--vc prefix is used for the new variable because we are storing varchar2(string) data
BEGIN
vc_staff_username := func_staff(in_staff_id);
UPDATE staff SET username = vc_staff_username
WHERE staff_id = in_staff_id;
END proc_staff;--END OF THE PROCEDURE
/

--TESTING
-- 1)BEFORE EXECUTING THE PROCEDURE
SELECT * FROM STAFF;
--3 rows return where username is null

-- 2) EXECUTING THE PROCEDURE PROC_STAFF
EXEC PROC_STAFF(1000);

-- 3) AFTER EXECUTING THE PROCEDURE
SELECT * FROM STAFF;
-- 3 rows return where the 1000 staff_id has a username ALAPPLE