Notes

--DROP PROCEDURES
DROP PROCEDURE proc_add_company;
DROP PROCEDURE proc_delete_company;

--command to determine if a sequence exists for company_id
SELECT seq_company_id.currval FROM dual;

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

--creating procedures to add new company
CREATE OR REPLACE PROCEDURE proc_add_company IS
vc_company_name companies.company_name%TYPE:='NEW COMPANY';
--vc prefix is used for the new variable because we are storing varchar2(string) data
BEGIN
INSERT INTO companies(company_id,company_name)
VALUES(seq_company_id.nextval, vc_company_name);
END proc_add_company;
/
SHOW ERRORS
--procedure created with no compilation error


--creating procedures to delete a 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
--procedure created with no compilation error