Часть работы (предпросмотр) :

a)1.Последовательность:

CREATE SEQUENCE staff_id

INCREMENT BY 1

START WITH 13;

2.Процедура:

CREATE OR REPLACE

PROCEDURE addEmployee(first_name varchar2, last_name varchar2, email varchar2, phone varchar2, birthday date, job varchar2, department varchar2) IS

     s_id number;

     j_id number;

     d_id number;

BEGIN

if regexp_like(email, '\w+@\w+(\.\w{2,4}){1,2}') then

  if regexp_like(phone, '^\(\d{3}\)\d{3}-\d{2}-\d{2}$') then

    if trunc(months_between(sysdate, birthday) / 12) >= 18 then

      SELECT staff_id.nextval INTO s_id FROM DUAL;

      begin

        SELECT job_id INTO j_id FROM jobs WHERE lower(job_name) = lower(job);

      exception

        when NO_DATA_FOUND then raise_application_error(-20903, 'Bad job name');

      end;     

      begin

        SELECT department_id INTO d_id FROM departments WHERE lower(department_name) = lower(department);

      exception

        when NO_DATA_FOUND then raise_application_error(-20904, 'Bad department name');

      end;

      INSERT INTO staff VALUES(s_id, UPPER(SUBSTR(first_name, 1, 1)) || LOWER(SUBSTR(first_name, 2)), UPPER(SUBSTR(last_name, 1, 1)) || LOWER(SUBSTR(last_name, 2)), email, phone, SYSDATE, birthday, null, j_id, d_id);

    else raise_application_error(-20902, 'Too young');

    end if;

  else raise_application_error(-20901, 'Bad phone number');

  end if;

else raise_application_error(-20900, 'Bad e-mail');

end if;

END;