Notes

/*

--@E:stabyaTERM2ProcPrac_18413715(3).txt

NAME: STABYA ACHARYA
ID: 18413715
Activity : 2
DATE:
*/
--Drop command
DROP PROCEDURE proc_add_company;

-- showing table structure of companies
DESC companies;

-- Creating sequence for company id
CREATE SEQUENCE seq_company_id
START WITH 90
INCREMENT By 2 ;

-- creating procedure called 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 proc_add_company
Select company_id,company_name FROM companies;
EXEC proc_add_company;

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


BEGIN
SELECT vn_company_id.currval
INTO vn_company_id
FROM dual;

END proc_delete_company;
/
SHOW ERRORS;

--TEST proc_delete_company
SELECT company_id, company_name FROM companies;
EXEC proc_delete_company;

--
CREATE OR REPLACE PROCEDURE proc_add_company(in_company_name IN 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 proc_add_company
SELECT company_id,company_name FROM companies;

EXEC proc_add_company('ESEWA');
--8 rowsselected