Notes

Write a note in this area. It's really easy to share with others. Click h--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;









-- 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.




















ere ...