Notes

--bonus activity
CREATE TABLE staff(
staff_id NUMBER(6),
staff_fname VARCHAR2(15),
staff_lname VARCHAR2(15),
staff_username VARCHAR2(10)
);

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 OR REPLACE FUNCTION func_create_username
(in_staff_id NUMBER) RETURN VARCHAR2 IS
vc_staff_username staff.staff_username%TYPE;
BEGIN
SELECT CONCAT(SUBSTR(s.staff_fname,1,2),SUBSTR(s.staff_lname,1,5))
INTO vc_staff_username
FROM staff s
WHERE s.staff_id=in_staff_id ;

RETURN vc_staff_username;
END func_create_username;
/
SHOW ERRORS

CREATE OR REPLACE PROCEDURE proc_update_username(in_staff_id NUMBER) IS
vc_staff_username staff.staff_username%TYPE;

BEGIN
vc_staff_username:=func_create_username(in_staff_id);
UPDATE staff SET staff_username=vc_staff_username
WHERE staff_id=in_staff_id;
END proc_update_username;
/
SHOW ERRORS

EXECUTE proc_update_username(1000);