Notes

--Ramesh Kunwar
--18413696
--Week 13
--Tutorial
--2/3/2019
--@D:RameshPro_PracProcPrac_yourlogi.sql

--DROP procedures
--Dropping the procedure 'proc_add_company'
DROP PROCEDURE proc_add_company;

--Dropping the procedure 'proc_delete_company'
DROP PROCEDURE proc_delete_company;

--Dropping the procedure 'proc_param_delete_co'
DROP PROCEDURE proc_param_delete_co;

--dropping proc_display_firstname
DROP PROCEDURE proc_display_firstname;
DROP PROCEDURE proc_display_middlename;
DROP PROCEDURE proc_display_surname;






-- showing table structure of companies
DESC companies;

--creating sequence
CREATE SEQUENCE seq_company_id
START WITH 90
INCREMENT BY 2;

--Creating procedure '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;

--TEST
--Teting the the procedure 'proc_add_company
SELECT company_id,company_name FROM companies;
EXEC proc_add_company;


--Creating new procedure '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 FROM companies
WHERE company_id=vn_company_id;

END proc_delete_company;
/
SHOW ERRORS;

--TEST
SELECT company_id,company_name FROM companies;
EXEC proc_delete_company;
/* before execucating procedure
no of rows=8
after
no of rows=7

The procedure works becaue the newly added row is deleted from table after running the procedure.
*/

--Using parameters in thge procedure 'proc_add_company'
CREATE OR REPLACE PROCEDURE proc_add_company(in_company_name companies.company_name%TYPE ) IS

BEGIN
INSERT INTO companies(company_id,company_name)
VALUES (seq_company_id.nextval, in_company_name);
END proc_add_company;
/
SHOW ERRORS;

--TEST
SELECT company_id,company_name FROM companies;
--7 rows selected.
EXEC proc_add_company('ESEWA');
--8 rows selected.


--changing the proc_delete_company
CREATE OR REPLACE PROCEDURE proc_param_delete_co(in_company_id companies.company_id%TYPE) IS --changing the name of the procedure.
BEGIN
DELETE FROM companies
WHERE company_id=in_company_id;

END proc_param_delete_co;
/
SHOW ERRORS;


--TEST
SELECT company_id,company_name FROM companies;
--8 rows selected.

EXEC proc_param_delete_co(130);
-- 7 rows selected
-- company name having id 130 is deleted.


--BONUS ACTIVITY ONE
--Using EXIT WHEN LOOP for firstname


CREATE OR REPLACE PROCEDURE proc_display_firstname(in_firstname VARCHAR2) IS
vn_counter NUMBER(10) :=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(in_firstname, vn_counter, 1));

EXIT WHEN vn_counter> LENGTH(in_firstname);
vn_counter := vn_counter + 1;
END LOOP;


END proc_display_firstname;
/
SHOW ERRORS;

--TEST
SET SERVEROUTPUT ON
EXEC proc_display_firstname('RAMESH');


--Creating the procedure for middle name using while loop
CREATE OR REPLACE PROCEDURE proc_display_middlename(in_middlename VARCHAR2) IS
vn_counter NUMBER(10) :=1;
BEGIN
WHILE vn_counter<=LENGTH(in_middlename) LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(in_middlename, vn_counter, 1));
vn_counter := vn_counter + 1;
END LOOP;


END proc_display_middlename;
/
SHOW ERRORS;

--TEST
SET SERVEROUTPUT ON
EXEC proc_display_middlename('BDR');


--Creating the procedure for surname using for loop
CREATE OR REPLACE PROCEDURE proc_display_surname(in_surname VARCHAR2) IS

BEGIN
FOR vn_counter IN 1..LENGTH(in_surname) LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(in_surname, vn_counter, 1));
END LOOP;

END proc_display_surname;
/
SHOW ERRORS;

--TEST
SET SERVEROUTPUT ON
EXEC proc_display_surname('KUNWAR');



-- NOW calling all three procedures

CREATE OR REPLACE PROCEDURE proc_display_name(in_firstname VARCHAR2,in_middlename VARCHAR2,in_surname VARCHAR2) IS

BEGIN
proc_display_firstname(in_firstname);
proc_display_middlename(in_middlename);
proc_display_surname(in_surname);

END proc_display_name;
/
SHOW ERRORS;

--TEST
SET SERVEROUTPUT ON
EXEC proc_display_name('RAMESH','BDR','KUNWAR');