CREATE SEQUENCE empno_seq
START WITH 207
INCREMENT BY 1
NOCACHE
NOCYCLE;

CREATE OR REPLACE PACKAGE emp_actions
AS
CURSOR desc_salary RETURN EMPLOYEES%ROWTYPE;
PROCEDURE hire_employee (
FIRST_NAME VARCHAR2,
LAST_NAME VARCHAR2,
EMAIL VARCHAR2,
JOB_ID VARCHAR2,
SALARY NUMBER,
DEPARTMENT_ID NUMBER);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;

CREATE OR REPLACE PACKAGE BODY emp_actions
AS
CURSOR desc_salary RETURN EMPLOYEES%ROWTYPE
IS
SELECT * FROM
EMPLOYEES WHERE SALARY > 13000 ORDER BY SALARY DESC;
PROCEDURE hire_employee(
FIRST_NAME VARCHAR2,
LAST_NAME VARCHAR2,
EMAIL VARCHAR2,
JOB_ID VARCHAR2,
SALARY NUMBER,
DEPARTMENT_ID NUMBER)
AS
BEGIN
INSERT INTO EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,
LAST_NAME, EMAIL, HIRE_DATE, JOB_ID,
SALARY, DEPARTMENT_ID)
VALUES (empno_seq.NEXTVAL, FIRST_NAME, LAST_NAME,
EMAIL, SYSDATE, JOB_ID, SALARY, DEPARTMENT_ID);
IF SQL%ROWCOUNT >= 1 THEN
DBMS_OUTPUT.PUT_LINE('New employee is added');
END IF;
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER)
IS
BEGIN
DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id;
IF SQL%ROWCOUNT >= 1 THEN
DBMS_OUTPUT.PUT_LINE('Employee ('||emp_id||') is deleted');
ELSE
DBMS_OUTPUT.PUT_LINE('Employee does not exist');
END IF;
END fire_employee;
END emp_actions;

SQL>@'C:\oracle_12c\c11_oracle_12c\emp_actions_spec.txt'
SQL>@'C:\oracle_12c\c11_oracle_12c\emp_actions_body.txt'

SQL>EXECUTE emp_actions.hire_employee('ALISON', 'CLARK', 'alison.clark@cadcim.com', 'IT_PROG', 7500, 60);

SQL>SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, JOB_ID, SALARY,
DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 207;