Написать функцию, добавляющую данные о новой задаче и подключенных к ее решению сотрудниках в соответствующие таблицы БД. Параметрами функции являются: название задачи, даты начала и завершения по плану, описание задачи, код сотрудника, являющегося ответственным за эту задачу, и коды сотрудников, подключенных к ее решению. Последние передаются в виде строки с фиксированным разделителем между кодами различных сотрудников. Задача считается ранее введенной в систему, если существует задача с таким же названием и датой начала. В этом случае функция должна вернуть 1. Если допущены ошибки в параметрах функции, например, сотрудника с таким кодом не существует или параметр даты не преобразуется к корректному формату даты, то функция должна вернуть 2. В случае прочих ошибок, возникших при выполнении функции, вернуть 3. В случае успешного завершения функция должна вернуть 0.
Логическая модель
Физическая модель
Создадим необходимые объекты (таблицы, ограничения, индексы):
CREATE TABLE "EMPLOYEE"
("PERSONNEL_NUMBER_EMPLOYEE" NUMBER,
"PERSONNEL_NUMBER_HEAD" NUMBER,
"POSITION" VARCHAR2(32),
"SALARY" NUMBER
) TABLESPACE MIRAVILD_DATA;
COMMENT ON TABLE "EMPLOYEE" IS 'Сотрудник';
COMMENT ON COLUMN "EMPLOYEE"."PERSONNEL_NUMBER_EMPLOYEE" IS 'Табельный номер сотрудника';
COMMENT ON COLUMN "EMPLOYEE"."PERSONNEL_NUMBER_HEAD" IS 'Табельный номер руководителя';
COMMENT ON COLUMN "EMPLOYEE"."POSITION" IS 'Должность сотрудника';
COMMENT ON COLUMN "EMPLOYEE"."SALARY" IS 'Зарплата сотрудника';
CREATE UNIQUE INDEX "U_IDX_EMPLOYEE" ON "EMPLOYEE" ("PERSONNEL_NUMBER_EMPLOYEE") TABLESPACE MIRAVILD_INDEX;
ALTER TABLE "EMPLOYEE" ADD CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY ("PERSONNEL_NUMBER_EMPLOYEE")
USING INDEX "U_IDX_EMPLOYEE" ENABLE VALIDATE;
ALTER TABLE "EMPLOYEE" ADD CONSTRAINT "FK_EMPLOYEE" FOREIGN KEY ("PERSONNEL_NUMBER_HEAD")
REFERENCES "EMPLOYEE" ("PERSONNEL_NUMBER_EMPLOYEE") ENABLE;
ALTER TABLE "EMPLOYEE" MODIFY ("POSITION" CONSTRAINT NN_EMPLOYEE_POSITION NOT NULL ENABLE);
ALTER TABLE "EMPLOYEE" MODIFY ("SALARY" CONSTRAINT NN_EMPLOYEE_SALARY NOT NULL ENABLE);
/
REATE TABLE "TASK"
("TASK_NAME" VARCHAR2(32),
"START_DATE" DATE,
"END_DATE" DATE,
"DESCRIPTION" VARCHAR2(4000)
) TABLESPACE MIRAVILD_DATA;
COMMENT ON TABLE "TASK" IS 'Задача';
COMMENT ON COLUMN "TASK"."TASK_NAME" IS 'Название задачи';
COMMENT ON COLUMN "TASK"."START_DATE" IS 'Дата начала решения задачи';
COMMENT ON COLUMN "TASK"."END_DATE" IS 'Дата окончания решения задачи';
COMMENT ON COLUMN "TASK"."DESCRIPTION" IS 'Описание задачи';
CREATE UNIQUE INDEX "U_IDX_TASK" ON "TASK" ("TASK_NAME", "START_DATE") TABLESPACE MIRAVILD_INDEX;
ALTER TABLE "TASK" ADD CONSTRAINT "PK_TASK" PRIMARY KEY ("TASK_NAME", "START_DATE")
USING INDEX "U_IDX_TASK" ENABLE VALIDATE;
/
CREATE TABLE "SOLUTION"
("PERSONNEL_NUMBER_EMPLOYEE" NUMBER,
"TASK_NAME" VARCHAR2(32),
"START_DATE" DATE,
"TYPE_EMPLOYEE" INTEGER
) TABLESPACE MIRAVILD_DATA;
COMMENT ON TABLE "SOLUTION" IS 'Решение задачи';
COMMENT ON COLUMN "SOLUTION"."PERSONNEL_NUMBER_EMPLOYEE" IS 'Табельный номер сотрудника';
COMMENT ON COLUMN "SOLUTION"."TASK_NAME" IS 'Название задачи';
COMMENT ON COLUMN "SOLUTION"."START_DATE" IS 'Дата начала решения задачи';
COMMENT ON COLUMN "SOLUTION"."TYPE_EMPLOYEE" IS 'Тип сотрудника (1 - ответственный/0 - подключенный)';
ALTER TABLE "SOLUTION" ADD CONSTRAINT "FK_EMPLOYEE_SOLUTION" FOREIGN KEY ("PERSONNEL_NUMBER_EMPLOYEE")
REFERENCES "EMPLOYEE" ("PERSONNEL_NUMBER_EMPLOYEE") ENABLE;
ALTER TABLE "SOLUTION" ADD CONSTRAINT "FK_TASK_SOLUTION" FOREIGN KEY ("TASK_NAME", "START_DATE")
REFERENCES "TASK" ("TASK_NAME", "START_DATE") ENABLE;
CREATE UNIQUE INDEX "U_IDX_SOLUTION" ON "SOLUTION" ("PERSONNEL_NUMBER_EMPLOYEE", "TASK_NAME", "START_DATE") TABLESPACE MIRAVILD_INDEX;
ALTER TABLE "SOLUTION" ADD CONSTRAINT "PK_SOLUTION" PRIMARY KEY ("PERSONNEL_NUMBER_EMPLOYEE", "TASK_NAME", "START_DATE")
USING INDEX "U_IDX_SOLUTION" ENABLE VALIDATE;
ALTER TABLE "SOLUTION" MODIFY ("TYPE_EMPLOYEE" CONSTRAINT NN_TYPE_EMPLOYEE NOT NULL ENABLE);
/
Вариант функции, добавляющую данные о новой задаче и подключенных к ее решению сотрудниках:
CREATE OR REPLACE FUNCTION add_task_employee(p_task_name IN task.task_name%TYPE, -- название задачи
p_start_date IN task.start_date%TYPE, -- дата начала решения задачи
p_end_date IN task.end_date%TYPE, -- дата завершения задачи
p_description IN task.description%TYPE, -- описание задачи
p_responsible_employee IN employee.personnel_number_employee%TYPE, -- сотрудник, ответственный за задачу
p_list_connected_employees IN VARCHAR2) -- список сотрудников, подкючённые к решению задачи
RETURN PLS_INTEGER IS
v_task task%ROWTYPE;
CURSOR cur IS
SELECT t.*
FROM task t
WHERE t.task_name = p_task_name
AND t.start_date = p_start_date;
PROCEDURE add_task(i_task_name IN task.task_name%TYPE,
i_start_date IN task.start_date%TYPE,
i_end_date IN task.end_date%TYPE,
i_description IN task.description%TYPE) IS
BEGIN
INSERT INTO task t
(t.task_name, t.start_date, t.end_date, t.description)
VALUES
(i_task_name, i_start_date, i_end_date, i_description);
END add_task;
PROCEDURE add_responsible_employee(i_employee_code IN solution.personnel_number_employee%TYPE,
i_task_name IN solution.task_name%TYPE,
i_start_date IN solution.start_date%TYPE) IS
BEGIN
INSERT INTO solution s
(s.personnel_number_employee,
s.task_name,
s.start_date,
s.type_employee)
VALUES
(i_employee_code, i_task_name, i_start_date, 1);
END add_responsible_employee;
PROCEDURE add_list_connected_employees(i_list_connected_employees IN VARCHAR2,
i_task_name IN solution.task_name%TYPE,
i_start_date IN solution.start_date%TYPE) IS
TYPE t_type IS TABLE OF solution%ROWTYPE INDEX BY PLS_INTEGER;
v_type t_type;
v CONSTANT CHAR(1) := ';'; -- Допустим разделитель - ;
BEGIN
SELECT to_number(regexp_substr(str, '[^' || v || ']+', 1, LEVEL)) str,
i_task_name,
i_start_date,
0
BULK COLLECT
INTO v_type
FROM (SELECT i_list_connected_employees str FROM dual) t
CONNECT BY instr(str, v, 1, LEVEL - 1) > 0;
FORALL i IN v_type.first .. v_type.last
INSERT INTO solution s VALUES v_type (i);
END add_list_connected_employees;
BEGIN
OPEN cur;
FETCH cur
INTO v_task;
IF cur%FOUND THEN
RETURN 1; -- Задача считается ранее введенной в систему, если существует задача с таким же названием и датой начала
END IF;
CLOSE cur;
BEGIN
-- Добавляем данные о новой задаче
add_task(i_task_name => p_task_name,
i_start_date => p_start_date,
i_end_date => p_end_date,
i_description => p_description);
-- Добавляем сотрудника, ответственного за задачу
add_responsible_employee(i_employee_code => p_responsible_employee,
i_task_name => p_task_name,
i_start_date => p_start_date);
-- Добавляем сотрудников, подключённых к задаче
add_list_connected_employees(i_list_connected_employees => p_list_connected_employees,
i_task_name => p_task_name,
i_start_date => p_start_date);
EXCEPTION
WHEN OTHERS THEN
RETURN 2;
END;
-- Успешное завершение функции
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN 3;
END add_task_employee;
/
Тестирование:
DECLARE
l PLS_INTEGER;
BEGIN
l := add_task_employee(p_task_name => 'Задача №1',
p_start_date => to_date('13.08.2016',
'dd.mm.yyyy'),
p_end_date => to_date('23.08.2016',
'dd.mm.yyyy'),
p_description => 'Комментарий',
p_responsible_employee => 4,
p_list_connected_employees => '1;2;3');
sys.dbms_output.put_line('/' || to_char(l) || '/');
END;
/
P.S.
Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776