Notes

--Ramesh Kunwar
--Term II week 16

--creating new fuction func_instructor_ct
--SELECT COUNT(*) FROM instructors;

CREATE OR REPLACE FUNCTION func_instructor_ct RETURN NUMBER IS vn_inst_num NUMBER(5);

BEGIN
SELECT COUNT(*)
INTO vn_inst_num
FROM instructors;

RETURN vn_inst_num;

END func_instructor_ct;
/

SHOW ERRORS;


--creating procedures to test function

CREATE OR REPLACE PROCEDURE proc_test_function IS
no_of_instructors NUMBER(20);

BEGIN
no_of_instructors := func_instructor_ct;
DBMS_OUTPUT.PUT_LINE ('There are ' || no_of_instructors || ' instructor(s) in the database.');

END proc_test_function;
/
SHOW ERRORS;

--testing functin
SET SERVEROUTPUT ON
EXEC proc_test_function;

--14 instructors



---creating new function with parameter using ancheor


CREATE OR REPLACE FUNCTION func_param_ct(in_salary instructors.salary%TYPE) RETURN NUMBER IS vn_inst_num NUMBER(5);

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

RETURN vn_inst_num;

END func_param_ct;
/

SHOW ERRORS;

--creating procedure to test function

CREATE OR REPLACE PROCEDURE proc_test_salary(in_salary instructors.salary%TYPE) IS numbers NUMBER(3);
BEGIN
numbers := func_param_ct(in_salary);
DBMS_OUTPUT.PUT_LINE ('There are ' || numbers || ' instructor(s) in the database having salary more than'||in_salary);
END proc_test_salary;
/
SHOW ERRORS;


--test
EXEC proc_test_salary(5000);
--2 instructors
EXEC proc_test_salary(3000);
--9 instructors


--checking the procedures

COLUMN object_name FORMAT A20;
COLUMN object_type FORMAT A20;
SELECT object_name, object_type FROM user_objects;


---BONUS ACTIVITY ONE

--creating the table staffs
CREATE TABLE staff(
staff_id NUMBER(5),
firstname VARCHAR2(20),
surname VARCHAR2(20),
username VARCHAR2(20));


--inserting into staffs
INSERT INTO staff(staff_id,firstname,surname)
VALUES (1,'RAMESH','KUNWAR');

INSERT INTO staff(staff_id,firstname,surname)
VALUES (2,'PEMBA','SHERPA');

INSERT INTO staff(staff_id,firstname,surname)
VALUES (3,'ALBIN','BHATTARAI');

INSERT INTO staff(staff_id,firstname,surname)
VALUES (4,'MOON','RISE');




--creating procedure to insert into staff table

CREATE OR REPLACE PROCEDURE proc_insert_staff(in_staff_id staff.staff_id%TYPE,in_firstname staff.firstname%TYPE,in_surname staff.surname%TYPE) IS

BEGIN
INSERT INTO staff(staff_id,firstname,surname)
VALUES (in_staff_id,in_firstname,in_surname);

END proc_insert_staff;
/


--test
EXEC proc_insert_staff(5,'BIGYAN','PATHAK');



--creating fuction to return username

CREATE OR REPLACE FUNCTION func_username(in_staff_id NUMBER) RETURN VARCHAR2 IS
vn_username VARCHAR2(20);

BEGIN
SELECT CONCAT( SUBSTR(firstname,1,2), SUBSTR(surname,1,5) )
INTO vn_username
FROM staff
WHERE staff_id=in_staff_id;
RETURN vn_username;

END func_username;
/

SHOW ERRORS;

--creating procedures test the function
CREATE OR REPLACE PROCEDURE proc_test_username(in_staff_id staff.staff_id%TYPE) IS vn_username VARCHAR2(20);

BEGIN
vn_username := func_username(in_staff_id);
DBMS_OUTPUT.PUT_LINE ('The username is = ' || vn_username );

END proc_test_username;
/
SHOW ERRORS;

--testing the procedure
EXEC proc_test_username(1);
EXEC proc_test_username(3);


--BONUS ACTIVITY TWO

CREATE OR REPLACE PROCEDURE proc_test_update(in_staff_id staffs.staff_id%TYPE) IS vn_username VARCHAR2(20);

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

END proc_test_update;
/
SHOW ERRORS;

--test proc_test_update
EXEC proc_test_update(1);
EXEC proc_test_update(2);
EXEC proc_test_update(4);


--quering data from staff table.
SELECT * FROM staff;


--DROP statements
--table
DROP TABLE staff;

--functions
DROP PROCEDURE proc_test_function;
DROP PROCEDURE proc_test_salary;
DROP PROCEDURE proc_test_username;
DROP PROCEDURE proc_insert_staff;
DROP PROCEDURE proc_test_update;

--procedure
DROP FUNCTION func_username;
DROP FUNCTION func_param_ct;
DROP FUNCTION func_instructor_ct;