Notes

SELECT seq_company_id FROM companies;

--creating seq for company-id
CREATE SEQUENCE seq_company_id
START WITH 90
INCREMENT BY 2
NOCYCLE;

--creating procedure

CREATE OR REPLACE PROCEDURE proc_add_company AS
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 ERROR

exec proc_add_company

-- creating procedure for 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 error;


-- creating parameterize procedure

CREATE OR REPLACE PROCEDURE proc_param_del_co(in_company_id IN companies.company_id%TYPE)IS

BEGIN
DELETE FROM companies
WHERE company_id=in_company_id;

END proc_param_del_co;
/
show error;


CREATE OR REPLACE PROCEDURE proc_firstname(in_student_firstname IN students.student_firstname