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;
