Написать процедуру, которая рассчитывает ежегодную премию для сотрудников. Входными параметрами процедуры являются: рассматриваемый уровень служебной иерархии, необходимое количество задач, за которые сотрудник был ответственен в течении года, и % от ежемесячной зарплаты, который и составляет премию. Для каждого сотрудника, принадлежащего указанному уровню иерархии и бывшего ответственным в этом году не менее, чем за указанное число задач, рассчитать премию (% от зарплаты). Выходными параметрами являются признак успешного или неуспешного завершения процедуры и индексная таблица, каждый элемент которой имеет следующую структуру: код сотрудника и размер премии.
Логическая модель
Физическая модель
Создадим необходимые объекты (таблицы, ограничения, индексы):
CREATE 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 "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);
/
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 PACKAGE calc_premium_pkg IS
-- Author : Makarenko Vladimir
-- Created : 22.05.2016
-- Purpose : Расчёт ежегодной премии сотрудникам
TYPE t_rec IS RECORD(
personnel_number_employee employee.personnel_number_employee%TYPE, -- табельный номер сотрудника
premium_volume NUMBER); -- размер премии
TYPE t_tbl IS TABLE OF t_rec INDEX BY PLS_INTEGER;
PROCEDURE premium_volume(p_level IN INTEGER, -- уровень служебной иерархии
p_number_task IN INTEGER, -- количество задач, за которые сотрудник был ответственен в течении года
p_percent IN NUMBER, -- % от ежемесячной зарплаты
p_boolean OUT BOOLEAN, -- успех/не успех завершения процедуры
p_table OUT t_tbl); -- индексная таблица
END calc_premium_pkg;
/
CREATE OR REPLACE PACKAGE BODY calc_premium_pkg IS
PROCEDURE premium_volume(p_level IN INTEGER, -- уровень служебной иерархии
p_number_task IN INTEGER, -- количество задач, за которые сотрудник был ответственен в течении года
p_percent IN NUMBER, -- % от ежемесячной зарплаты
p_boolean OUT BOOLEAN, -- успех/не успех завершения процедуры
p_table OUT t_tbl) IS
CURSOR cur IS
SELECT l.personnel_number_employee,
MAX(l.annnual_bonus) AS annnual_bonus
FROM (SELECT LEVEL AS lvl,
e.personnel_number_employee,
extract(YEAR FROM t.start_date) AS year_start,
((e.salary * p_percent) / 100) * 12 AS annnual_bonus -- размер ежегодной премии
FROM employee e
JOIN solution s
ON s.personnel_number_employee =
e.personnel_number_employee
AND s.type_employee = 1
JOIN task t
ON t.task_name = s.task_name
AND t.start_date = s.start_date
CONNECT BY PRIOR e.personnel_number_employee =
e.personnel_number_head
START WITH e.personnel_number_head IS NULL) l
WHERE l.lvl = p_level
GROUP BY l.lvl, l.personnel_number_employee, l.year_start
HAVING COUNT(*) > p_number_task;
BEGIN
OPEN cur;
LOOP
FETCH cur BULK COLLECT
INTO p_table;
EXIT WHEN cur%NOTFOUND;
END LOOP;
p_boolean := TRUE; -- Успешное завершение процедуры
EXCEPTION
WHEN OTHERS THEN
p_boolean := FALSE;
END premium_volume;
END calc_premium_pkg;
/
P.S.
Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776