Notes

/*
procedures
--@D:year2tutosdatabases2proc_prac.sql
NAME: Ranjit Kunwar
ID: 18413648
WEEK:14
Activity : 1
DATE: feb 21 2019

*/

SET SERVEROUTPUT ON


--dropping procedures
DROP PROCEDURE proc_add_company;
DROP PROCEDURE proc_delete_company;
DROP PROCEDURE proc_param_del_co;
DROP PROCEDURE proc_display_fname;
DROP PROCEDURE proc_display_mname;
DROP PROCEDURE proc_display_lname;
DROP PROCEDURE proc_display_allname;


--to create a sequence for company_id in companies table
CREATE SEQUENCE seq_company_id
INCREMENT BY 2
START WITH 90;
--sequence created

--viewing companies table structure
DESC companies
--creating a new procedure named proc_add_company
CREATE OR REPLACE PROCEDURE proc_add_company IS
vc_company_name companies.company_name%TYPE:='NEW COMPANY';

BEGIN
INSERT INTO companies(company_id,company_name)
VALUES (seq_company_id.nextval, vc_company_name);

END proc_add_company;
/
SHOW ERRORS
--PROCEDURE CREATED
--NO ERRORS

/*
TEST proc_add_company
*/
--first checking companies table data
SELECT company_id FROM companies;
--16 rows returned


--executing the procedure
exec proc_add_company
--PL/SQL procedure successfully completed.

--testing
SELECT company_id FROM companies;
--17 rows detected


--creating a new procedure named proc_delete_company
CREATE OR REPLACE PROCEDURE proc_delete_company IS
vn_company_id companies.company_id%TYPE;

BEGIN
SELECT seq_company_id.currval
INTO vn_company_id
FROM dual;

--delete sql
DELETE FROM companies WHERE company_id=vn_company_id;

END proc_delete_company;
/
SHOW ERRORS
--PROCEDURE CREATED
/*
TEST proc_delete_company
*/
--executing the procedure
exec proc_delete_company
--PL/SQL procedure successfully completed.

--testing
SELECT company_id FROM companies;
--16 rows detected



--creating a new procedure with parameter named proc_param_del_co
CREATE OR REPLACE PROCEDURE proc_param_del_co(in_company_id companies.company_id%TYPE) IS

BEGIN
--delete sql
DELETE FROM companies WHERE company_id=in_company_id;

END proc_param_del_co;
/
SHOW ERRORS
--PROCEDURE CREATED
--no erros

/*
TEST proc_delete_company
*/
--executing the procedure
exec proc_param_del_co(92)
--PL/SQL procedure successfully completed.

--testing
SELECT company_id FROM companies;
--15 rows detected


/* BONUS ACIVITY **/

--create procedure that accepts firstname as a parameter
CREATE OR REPLACE PROCEDURE proc_display_fname(in_firstname VARCHAR2) IS
vn_length NUMBER(3);
vn_counter NUMBER(2):=1;

BEGIN
DBMS_OUTPUT.PUT_LINE('------------FIRSTNAME-----------');
vn_length := LENGTH(in_firstname);
--starting the loop
LOOP
EXIT WHEN vn_counter>vn_length;
--this prints each letter of the first name
DBMS_OUTPUT.PUT_LINE(SUBSTR(in_firstname, vn_counter, 1));
vn_counter:= vn_counter+1;
END LOOP;
END proc_display_fname;
/
SHOW ERRORS;
--PROCEDURE CREATED

--executing procedure
exec proc_display_fname('RANJEET');
--output shown correctly


----create procedure that accepts middle name as a parameter using while loop
CREATE OR REPLACE PROCEDURE proc_display_mname(in_midname VARCHAR2) IS
vn_length NUMBER(3);
vn_counter NUMBER(2):=1;

BEGIN
DBMS_OUTPUT.PUT_LINE('------------MIDDLE NAME-----------');
vn_length := LENGTH(in_midname);
--starting the loop
WHILE vn_counter<=vn_length LOOP
--this prints each letter of the first name
DBMS_OUTPUT.PUT_LINE(SUBSTR(in_midname, vn_counter, 1));
vn_counter:= vn_counter+1;
END LOOP;
END proc_display_mname;
/
SHOW ERRORS;
--PROCEDURE CREATED

--executing procedure
exec proc_display_mname('JUNG');
--output shown correctly


----create procedure that accepts last name as a parameter using FOR loop
CREATE OR REPLACE PROCEDURE proc_display_lname(in_lastname VARCHAR2) IS
vn_length NUMBER(3);

BEGIN
DBMS_OUTPUT.PUT_LINE('------------LAST NAME-----------');
vn_length := LENGTH(in_lastname);
--starting the loop
FOR vn_counter IN 1..vn_length LOOP
--this prints each letter of the first name
DBMS_OUTPUT.PUT_LINE(SUBSTR(in_lastname, vn_counter, 1));

END LOOP;
END proc_display_lname;
/
SHOW ERRORS;
--PROCEDURE CREATED

--executing procedure
exec proc_display_lname('KUNWAR');
--output shown correctly


--Create a final procedure that calls the other 3
CREATE OR REPLACE PROCEDURE proc_display_allname(in_fname VARCHAR2,in_mname VARCHAR2,in_lname VARCHAR2) IS

BEGIN
proc_display_fname(in_fname);
proc_display_mname(in_mname);
proc_display_lname(in_lname);
END;
/
SHOW ERRORS
--PROCEDURE CREATED WITH NO ERRORS

--executing procedure
exec proc_display_allname('RANJEET','JUNG','KUNWAR');
--output shown correctly