Задание по SQL. Часть 4

Создать архивные таблицы такой же структуры, что таблица задач («задача») и таблица, связующая задачи и сотрудников («задача-сотрудник»). Написать функцию, перемещающую записи из основных таблиц («задача» и «задача-сотрудник») в соответствующие архивные таблицы и работающую в многопользовательском режиме. В качестве параметра функция получает дату, которая указывает, что задачи, завершенные до этой даты должны быть перенесены в архив. Функция возвращает 0, если в архивные таблицы была перенесена хотя бы 1 задача; функция возвращает 1, если не было перенесено ни одной задачи и возвращает 2, если при выполнении возникла ошибка.

Логическая модель

Логическая модель



Физическая модель

Физическая модель


Создадим необходимые объекты (таблицы, ограничения, индексы):

CREATE TABLE "TASK_ARCHIVE" 
("TASK_NAME" VARCHAR2(32),
 "START_DATE" DATE,
 "END_DATE" DATE,
 "DESCRIPTION" VARCHAR2(4000)
) TABLESPACE MIRAVILD_DATA STORAGE(INITIAL 1048576 NEXT 1048576 ) -- Размер партиции можно взять с небольшим запасом (1МБ)
PARTITION BY RANGE (START_DATE)
   SUBPARTITION BY LIST (TASK_NAME)
      SUBPARTITION TEMPLATE 
      (
       SUBPARTITION "SP_TASK_NAME1" VALUES ('Задача №1'),
       SUBPARTITION "SP_TASK_NAME2" VALUES ('Задача №2'),
       SUBPARTITION "SP_TASK_NAME3" VALUES ('Задача №3')
       )
  (PARTITION "SP_2014" VALUES LESS THAN (TO_DATE('01.01.2014', 'DD.MM.YYYY')),
   PARTITION "SP_2015" VALUES LESS THAN (TO_DATE('01.01.2015', 'DD.MM.YYYY')),
   PARTITION "SP_2016" VALUES LESS THAN (TO_DATE('01.01.2016', 'DD.MM.YYYY')),
   PARTITION "SP_FUTURE" VALUES LESS THAN (MAXVALUE)
  ) ENABLE ROW MOVEMENT PARALLEL NOLOGGING;
  
COMMENT ON TABLE "TASK_ARCHIVE" IS 'Архив задач';
COMMENT ON COLUMN "TASK_ARCHIVE"."TASK_NAME" IS 'Название задачи';
COMMENT ON COLUMN "TASK_ARCHIVE"."START_DATE" IS 'Дата начала решения задачи';
COMMENT ON COLUMN "TASK_ARCHIVE"."END_DATE" IS 'Дата окончания решения задачи';
COMMENT ON COLUMN "TASK_ARCHIVE"."DESCRIPTION" IS 'Описание задачи';

ALTER TABLE "TASK_ARCHIVE" MODIFY ("TASK_NAME" CONSTRAINT TASK_ARCHIVE_NAME_NN NOT NULL ENABLE);
ALTER TABLE "TASK_ARCHIVE" MODIFY ("START_DATE" CONSTRAINT TASK_ARCHIVE_START_NN NOT NULL ENABLE);
ALTER TABLE "TASK_ARCHIVE" MODIFY ("END_DATE" CONSTRAINT TASK_ARCHIVE_END_NN NOT NULL ENABLE);
/

CREATE TABLE "SOLUTION_ARCHIVE" 
("PERSONNEL_NUMBER_EMPLOYEE" NUMBER,
 "TASK_NAME" VARCHAR2(32),
 "START_DATE" DATE,
 "TYPE_EMPLOYEE" INTEGER
) TABLESPACE MIRAVILD_DATA STORAGE(INITIAL 1048576 NEXT 1048576 ) -- Размер партиции можно взять с небольшим запасом (1МБ)
PARTITION BY RANGE (START_DATE)
   SUBPARTITION BY LIST (TASK_NAME)
      SUBPARTITION TEMPLATE 
      (
       SUBPARTITION "SP_TASK_NAME1" VALUES ('Задача №1'),
       SUBPARTITION "SP_TASK_NAME2" VALUES ('Задача №2'),
       SUBPARTITION "SP_TASK_NAME3" VALUES ('Задача №3')
       )
  (PARTITION "SP_2014" VALUES LESS THAN (TO_DATE('01.01.2014', 'DD.MM.YYYY')),
   PARTITION "SP_2015" VALUES LESS THAN (TO_DATE('01.01.2015', 'DD.MM.YYYY')),
   PARTITION "SP_2016" VALUES LESS THAN (TO_DATE('01.01.2016', 'DD.MM.YYYY')),
   PARTITION "SP_FUTURE" VALUES LESS THAN (MAXVALUE)
  ) ENABLE ROW MOVEMENT PARALLEL NOLOGGING;
  
COMMENT ON TABLE "SOLUTION_ARCHIVE" IS 'Архив задач';
COMMENT ON COLUMN "SOLUTION_ARCHIVE"."PERSONNEL_NUMBER_EMPLOYEE" IS 'Табельный номер сотрудника';
COMMENT ON COLUMN "SOLUTION_ARCHIVE"."TASK_NAME" IS 'Название задачи';
COMMENT ON COLUMN "SOLUTION_ARCHIVE"."START_DATE" IS 'Дата начала решения задачи';
COMMENT ON COLUMN "SOLUTION_ARCHIVE"."TYPE_EMPLOYEE" IS 'Тип сотрудника (1 - ответственный/0 - подключенный)';
 

ALTER TABLE "SOLUTION_ARCHIVE" MODIFY ("PERSONNEL_NUMBER_EMPLOYEE" CONSTRAINT NN_SOLUTION_ARCHIVE_PERSONNEL_NUMBER_EMPLOYEE NOT NULL ENABLE);
ALTER TABLE "SOLUTION_ARCHIVE" MODIFY ("TASK_NAME" CONSTRAINT NN_SOLUTION_ARCHIVE_TASK_NAM NOT NULL ENABLE);
ALTER TABLE "SOLUTION_ARCHIVE" MODIFY ("START_DATE" CONSTRAINT NN_SOLUTION_ARCHIVE_START NOT NULL ENABLE);
ALTER TABLE "SOLUTION_ARCHIVE" MODIFY ("TYPE_EMPLOYEE" CONSTRAINT NN_SOLUTION_ARCHIVE_TYPE_EMPLOYEE NOT NULL ENABLE);
/

P.S.
Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776

Tags: , , , , ,

Comments are closed.