PL/SQL Лабы. Блок 4

Практическое занятие 14. Встроенные пакеты Oracle

Задание 1. Создайте процедуру employee_report для генерации сводной выборки из таблицы Employees и сохранения ее в текстовом файле операционной системы с помощью стандартного пакета UTL_FILE. Отчет должен содержать информацию о сотрудниках, чья ежемесячная зарплата превышает среднюю зарплату по его отделу.

Процедура должна принимать на вход 2 параметра. Первый параметр — dir — используется для передачи имени директории, в которую будет сохраняться файл с отчетом. Второй параметр передает имя файла, который будет создан в указанной директории.
В качестве имени директории используйте STUD_PLSQL.
Примечание: физическое расположение директории \\Westfold\Student\PLSQL — у вас есть права на просмотр файлов в этой директории по сети.
Создайте процедуру (в пакете emp_pkg или отдельно), в которую добавьте секцию для обработки ошибок.
В шапке отчета добавьте свою фамилию, номер группы и системное дату и время формирования отчета.
Запустите программу на исполнение, сформируйте второй параметр таким образом, чтобы имя Вашего отчета в директории было уникальным. Шаблон для формирования имени: sal_rpt_XXXX_YYYY.txt, где XXXX — это ваша учетная запись, а YYYY — текущее системное дата и время с точностью до секунды.

CREATE OR REPLACE PROCEDURE
  fileErrorsHandler (caller VARCHAR2, errorCode PLS_INTEGER) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(caller ||' error:');
  CASE errorCode
    WHEN -29280
    THEN DBMS_OUTPUT.PUT_LINE('- The directory''s name is wrong.');
    ELSE DBMS_OUTPUT.PUT_LINE('- Something went wrong.');
  END CASE;
END fileErrorsHandler;
/

CREATE OR REPLACE PROCEDURE
  employee_report (dir VARCHAR2, fileName VARCHAR2) IS
  file UTL_FILE.FILE_TYPE;
BEGIN
  file := UTL_FILE.FOPEN(dir, fileName, 'W');
  UTL_FILE.PUT_LINE(
    file,
       '--- Отчёт. Влад Фурман, 33536/2 ['
    || TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS')
    ||'] ---'
  );
  FOR row IN (
    SELECT *
    FROM (
      SELECT
        Employee_ID, First_Name, Last_Name, Department_ID, Salary,
        AVG(Salary) OVER (PARTITION BY Department_ID) AS Average
      FROM Employees
    )
    WHERE Salary > Average
    ORDER BY Employee_ID
  )
  LOOP
    UTL_FILE.PUT_LINE(
      file,
         'Emp #' || row.Employee_ID || ': '
      || row.First_Name || ' ' || row.Last_Name || ', '
      || 'dep #' || row.Department_ID || ', '
      || 'salary is ' || row.Salary
    );
  END LOOP;

  UTL_FILE.FCLOSE(file);
EXCEPTION
  WHEN OTHERS THEN
    fileErrorsHandler('employee_report('''|| dir ||''', '''|| fileName ||''')', SQLCODE);
END employee_report;
/


DECLARE
  dir           VARCHAR(64)   := 'STUD_PLSQL';
  theDate       VARCHAR(64)   := TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24-MI-SS');
  fileName      VARCHAR2(256) := 'sal_rpt_vlafur1_'|| theDate ||'.txt';
  currentString VARCHAR2(1024);
  file UTL_FILE.FILE_TYPE;
BEGIN
  employee_report (dir, fileName);
  DBMS_OUTPUT.PUT_LINE('done');
  file := UTL_FILE.FOPEN(dir, fileName, 'R');

  DBMS_OUTPUT.PUT_LINE('Содержимое файла '|| fileName ||':');

  LOOP
    BEGIN
      UTL_FILE.GET_LINE(file, currentString);
      DBMS_OUTPUT.PUT_LINE(currentString);
    EXCEPTION WHEN NO_DATA_FOUND THEN EXIT;
    END;
  END LOOP;

  UTL_FILE.FCLOSE(file);
END;
/

Содержимое файла sal_rpt_vlafur1_16.04.2019 22-29-22.txt:
--- Отчёт. Влад Фурман, 33536/2 [16.04.2019 22:29:22] ---
Emp #100: Steven King, dep #90, salary is 24000
Emp #103: Alexander Hunold, dep #60, salary is 9000
Emp #104: Bruce Ernst, dep #60, salary is 6000
Emp #108: Nancy Greenberg, dep #100, salary is 12008
Emp #109: Daniel Faviet, dep #100, salary is 9000
Emp #114: Den Raphaely, dep #30, salary is 11000
Emp #120: Matthew Weiss, dep #50, salary is 8000
Emp #121: Adam Fripp, dep #50, salary is 8200
Emp #122: Payam Kaufling, dep #50, salary is 7900
Emp #123: Shanta Vollman, dep #50, salary is 6500
Emp #124: Kevin Mourgos, dep #50, salary is 5800
Emp #137: Renske Ladwig, dep #50, salary is 3600
Emp #141: Trenna Rajs, dep #50, salary is 3500
Emp #145: John Russell, dep #80, salary is 14000
Emp #146: Karen Partners, dep #80, salary is 13500
Emp #147: Alberto Errazuriz, dep #80, salary is 12000
Emp #148: Gerald Cambrault, dep #80, salary is 11000
Emp #149: Eleni Zlotkey, dep #80, salary is 10500
Emp #150: Peter Tucker, dep #80, salary is 10000
Emp #151: David Bernstein, dep #80, salary is 9500
Emp #152: Peter Hall, dep #80, salary is 9000
Emp #156: Janette King, dep #80, salary is 10000
Emp #157: Patrick Sully, dep #80, salary is 9500
Emp #158: Allan McEwen, dep #80, salary is 9000
Emp #162: Clara Vishney, dep #80, salary is 10500
Emp #163: Danielle Greene, dep #80, salary is 9500
Emp #168: Lisa Ozer, dep #80, salary is 11500
Emp #169: Harrison Bloom, dep #80, salary is 10000
Emp #170: Tayler Fox, dep #80, salary is 9600
Emp #174: Ellen Abel, dep #80, salary is 11000
Emp #184: Nandita Sarchand, dep #50, salary is 4200
Emp #185: Alexis Bull, dep #50, salary is 4100
Emp #188: Kelly Chung, dep #50, salary is 3800
Emp #189: Jennifer Dilly, dep #50, salary is 3600
Emp #192: Sarah Bell, dep #50, salary is 4000
Emp #193: Britney Everett, dep #50, salary is 3900
Emp #200: Jennifer Whalen, dep #10, salary is 4400
Emp #201: Michael Hartstein, dep #20, salary is 13000
Emp #205: Shelley Higgins, dep #110, salary is 12008

Задание 2. Создайте новую процедуру web_employee_report.

Процедура ничего не принимает на вход и, с помощью стандартного пакета HTP, генерирует отчет, подобный отчету из предыдущей процедуры, в формате HTML.
Для тестирования работы процедуры сначала выполните инструкции SET SERVEROUTPUT ON, затем выполните процедуру. Результат работы процедуры будет помещен в буфер. Исполните OWA_UTIL.SHOWPAGE, чтобы вывести содержимое буфера на экран.
Вручную создайте файл web_employee_report_USERNAME_SYSDATE.html, скопируйте из SQL Developer и вставьте в него результат работы процедуры. Сохраните файл в своем каталоге в папке WORKDATA.

CREATE OR REPLACE PROCEDURE web_employee_report IS
  paramVal OWA.VC_ARR;
BEGIN
  paramVal(1) := 1;
  OWA.INIT_CGI_ENV(paramVal);

  HTP.HTMLOPEN;
    HTP.HEADOPEN;
      HTP.TITLE('Отчёт');
      HTP.PRINT('<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">'); -- подключаем Bootstrap
    HTP.HEADCLOSE;
    HTP.BODYOPEN;
      HTP.PRINT('<div class="container">');
        HTP.PRINT(
            '<h1>--- Отчёт. Влад Фурман, 33536/2 ['
          || TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS')
          ||'] ---</h1>'
        );
        HTP.PRINT('<table class="table table-SQL table-bordered table-hover table-sm">');
          HTP.PRINT('<thead class="thead-dark"><tr><th>Emp #</th><th>First Name</th><th>Last Name</th><th>Department #</th><th>Salary</th></tr></thead>');
          HTP.PRINT('<tbody>');
            FOR queryR IN (
              SELECT *
              FROM (
                SELECT
                  Employee_ID, First_Name, Last_Name, Department_ID, Salary,
                  AVG(Salary) OVER (PARTITION BY Department_ID) AS Average
                FROM Employees
              )
              WHERE Salary > Average
              ORDER BY Employee_ID
            ) LOOP
              HTP.PRINT(
                '<tr><td>'||    queryR.Employee_ID
                ||'</td><td>'|| queryR.First_Name
                ||'</td><td>'|| queryR.Last_Name
                ||'</td><td>'|| queryR.Department_ID
                ||'</td><td>'|| queryR.Salary ||'</td></tr>'
              );
            END LOOP;
          HTP.PRINT('</tbody>');
        HTP.PRINT('</table>');

      HTP.PRINT('</div>');
    HTP.BODYCLOSE;
  HTP.HTMLCLOSE;
END;
/

SET SERVEROUTPUT ON;
BEGIN
  web_employee_report;
  OWA_UTIL.SHOWPAGE;
END;
/

Результат может быть найден здесь.

Задание 3. С помощью пакета DBMS_SCHEDULER создайте задание, для регулярной генерации отчетов в формате текстовых файлов, которое по указанному расписанию запускает на исполнение процедуру employee_report.

Создайте хранимую процедуру schedule_report, которая принимает на вход частоту повторений запуска задания (интервал) и длительность выполнения задания в минутах (по умолчанию — 10).
В теле процедуры создайте задание с именем empsal_report, которое должно представлять собой анонимный блок, запускающийся на исполнение сразу же после вызова процедуры schedule_report.
Задание должно вызывать процедуру employee_report (имя файла для сохранения отчёта должно генерироваться по указанным в задании 5.2 правилам, в качестве имени директории используйте STUD_PLSQL). Для хранения текста анонимного блока (тела задания) используйте локальную переменную plsql_block типа VARCHAR2(200).
Сформируйте параметр end_date следующим образом: длительность выполнения задания в минутах разделите на количество минут в сутках и прибавьте получившееся значение к текущему значению даты и времени.
Протестируйте работу процедуры schedule_report. Создайте задание, запускающее процедуру на исполнение каждые 2 минуты в течение 6 минут.
После запуска процедуры просмотрите содержимое системной таблички USER_SCHEDULER_JOBS. Убедитесь, что там есть информация о задании. Просмотрите каталог \\Westfold\Student\PLSQL. Там должно появиться несколько отчетов с вашим именем.

CREATE OR REPLACE PROCEDURE
  schedule_report (interval SIMPLE_INTEGER, duration SIMPLE_INTEGER := 10) IS
      theDate VARCHAR(64)   := TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24-MI-SS');
     fileName VARCHAR2(256) := 'sal_rpt_vlafur1_'|| theDate ||'.txt';
  plsql_block VARCHAR2(200) := 'BEGIN employee_report(''STUD_PLSQL'', '''|| fileName ||'''); END;';
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
            JOB_NAME => 'empsal_report'
   ,        JOB_TYPE => 'PLSQL_BLOCK'
   ,      JOB_ACTION => plsql_block
   ,      START_DATE => SYSTIMESTAMP
   ,        END_DATE => (SYSTIMESTAMP + duration / (60 * 24))
   , REPEAT_INTERVAL => 'FREQUENCY=MINUTELY; INTERVAL='|| interval
   ,         ENABLED => TRUE
  );
END schedule_report;
/

BEGIN
  schedule_report(2, 6);
END;
/

SELECT Job_Name, Job_Type
FROM USER_SCHEDULER_JOBS;

JOB_NAME	JOB_TYPE
EMPSAL_REPORT	PLSQL_BLOCK

Практическое занятие 15. Использование динамического SQL

Задание 1. Создайте пакет table_pkg, с помощью которого можно создавать и удалять таблицы, модифицировать, добавлять и удалять записи из таблиц с помощью Native Dynamic SQL.

Создайте спецификацию пакета со следующими процедурами:
- процедура для создания таблиц. Входные параметры — имя таблицы и спецификация столбцов:
```
PROCEDURE make(table_name VARCHAR2, col_specs VARCHAR2);
```
- процедура для вставки записей в таблицу принимает на вход имя таблицы, список столбцов, значения:
```
PROCEDURE add_row(table_name VARCHAR2, col_values VARCHAR2, cols VARCHAR2 := NULL);
```
- процедура для изменения записей в таблице принимает на вход название таблицы, условие отбора изменяемых записей, инструкции модификации:
```
PROCEDURE upd_row(table_name VARCHAR2, set_values VARCHAR2, conditions VARCHAR2 := NULL);
```
- процедура для удаления записей из таблицы:
```
PROCEDURE del_row(table_name VARCHAR2, conditions VARCHAR2 := NULL);
```
- процедура для удаления (DROP) таблицы:
```
PROCEDURE remove(table_name VARCHAR2);
```
Создайте тело пакета, в котором все перечисленные процедуры, кроме remove должны быть реализованы с использованием Native Dynamic SQL. Для реализации процедуры remove используйте DBMS_SQL.
Протестируйте работу пакета. Выполните процедуру
```
table_pkg.make('my_contacts', 'id number(4), name varchar2(40)');
```
Воспользуйтесь оператором DESCRIBE для просмотра структуры созданной таблицы. Убедитесь, что все создано правильно.
Выполните процедуру add_row 4 раза, чтобы добавить в новую таблицу 4 записи (указанные значения необходимо передавать в столбцы ID и Name соответственно):
1 Lauran Serhal
2 Nancy
3 Sunitha Patel
4 Valli Pataballa
С помощью оператора SELECT убедитесь, что записи добавлены в таблицу.
С помощью процедуры del_row удалите запись со значением ID = 3.
С помощью процедуры upd_row измените значение столбца Name на 'Nancy Greenderg' для записи с ID = 2. Проверьте с помощью SELECT, что все выполняется правильно.
С помощью процедуры table_pkg.remove удалите таблицу My_Contacts.

CREATE OR REPLACE PACKAGE table_pkg IS
  PROCEDURE make    (table_name VARCHAR2, col_specs  VARCHAR2);
  PROCEDURE add_row (table_name VARCHAR2, col_values VARCHAR2, cols       VARCHAR2 := NULL);
  PROCEDURE upd_row (table_name VARCHAR2, set_values VARCHAR2, conditions VARCHAR2 := NULL);
  PROCEDURE del_row (table_name VARCHAR2, conditions VARCHAR2 := NULL);
  PROCEDURE remove  (table_name VARCHAR2);
END table_pkg;
/

Package created.

CREATE OR REPLACE PACKAGE BODY table_pkg IS
  
  PROCEDURE make (table_name VARCHAR2, col_specs VARCHAR2) IS
  BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE '|| table_name ||' ('|| col_specs ||')';
  END;

  PROCEDURE add_row (table_name VARCHAR2, col_values VARCHAR2, cols VARCHAR2 := NULL) IS
    statement VARCHAR2(512) := 'INSERT INTO '|| table_name; 
  BEGIN
    IF cols IS NOT NULL THEN
      statement := statement ||' ('|| cols ||')';
    END IF;
    EXECUTE IMMEDIATE statement ||' VALUES ('|| col_values ||')';
  END;

  PROCEDURE upd_row (table_name VARCHAR2, set_values VARCHAR2, conditions VARCHAR2 := NULL) IS
    statement VARCHAR2(512) := 'UPDATE '|| table_name ||' SET '|| set_values;
  BEGIN
    IF conditions IS NOT NULL THEN 
      statement := statement ||' WHERE '|| conditions;
    END IF;
    EXECUTE IMMEDIATE statement;
  END;

  PROCEDURE del_row (table_name VARCHAR2, conditions VARCHAR2 := NULL) IS
    statement VARCHAR2(512) := 'DELETE FROM '|| table_name;
  BEGIN
    IF conditions IS NOT NULL THEN
      statement := statement ||' WHERE '|| conditions;
    END IF;
    EXECUTE IMMEDIATE statement;
  END;

  PROCEDURE remove (table_name VARCHAR2) IS
    cursorID PLS_INTEGER;
  BEGIN
    cursorID := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cursorID, 'DROP TABLE '|| table_name, DBMS_SQL.NATIVE);
    DBMS_SQL.CLOSE_CURSOR(cursorID);
  END;

END table_pkg;
/

Package body created.

EXECUTE table_pkg.make('my_contacts', 'id number(4), name varchar2(40)');

PL/SQL procedure successfully completed.

DESCRIBE My_Contacts;

Name Null? Type         
---- ----- ------------ 
ID         NUMBER(4)    
NAME       VARCHAR2(40)

EXECUTE table_pkg.add_row('my_contacts', '1, ''Lauran Serhal''');
EXECUTE table_pkg.add_row('my_contacts', '2, ''Nancy''');
EXECUTE table_pkg.add_row('my_contacts', '3, ''Sunitha Patel''');
EXECUTE table_pkg.add_row('my_contacts', '4, ''Valli Pataballa''');

SELECT * FROM My_Contacts;

ID	NAME
1	Lauran Serhal
2	Nancy
3	Sunitha Patel
4	Valli Pataballa

EXECUTE table_pkg.del_row('my_contacts', 'ID = 3');
SELECT * FROM My_Contacts;

ID	NAME
1	Lauran Serhal
2	Nancy
4	Valli Pataballa

EXECUTE table_pkg.upd_row('my_contacts', 'Name = ''Nancy Greenderg''', 'ID = 2');
SELECT * FROM My_Contacts;

ID	NAME
1	Lauran Serhal
2	Nancy Greenderg
4	Valli Pataballa

EXECUTE table_pkg.remove('my_contacts');

PL/SQL procedure successfully completed.

Задание 2. Создайте пакет compile_pkg, для перекомпиляции именованных блоков кода в вашей схеме.

В спецификации пакета опишите процедуру make, которая принимает на вход имя программной единицы, которую необходимо скомпилировать.
В теле пакета:
- Опишите private функцию get_type, которая по имени блока извлечет из системных словарей его тип.
  - Если имя объекта не будет найдено в системных словарях, функция должна вернуть NULL.
  - Учтите, что для пакетов в системных словарях фиксируется по одному имени 2 объекта: PACKAGE и PACKAGE BODY. Функция должна возвращать только PACKAGE.
- Создайте тело процедуры make следующим образом:
  - Один входной параметр name для передачи имени программного модуля.
  - С помощью функции get_type узнайте тип программного модуля и воспользуйтесь им для формирования инструкции перекомпиляции. Если объект не найден в системных словарях в процессе работы функции get_type — выдайте пользовательское сообщение об ошибке.
Протестируйте работу процедуры compile_pkg.make на примере:
- процедуры web_employee_report,
- пакета emp_pkg,
- несуществующего объекта emp_details (зафиксируйте сообщение об ошибке).

CREATE OR REPLACE PACKAGE compile_pkg IS
  PROCEDURE make (objectName USER_SOURCE.Name%TYPE);
END compile_pkg;
/

Package created.

CREATE OR REPLACE PACKAGE BODY compile_pkg IS
 
  /*
    --- PRIVATE ---
  */

  FUNCTION get_type (objectName USER_SOURCE.Name%TYPE)
  RETURN USER_SOURCE.Type%TYPE IS
    objectType USER_SOURCE.Type%TYPE;
  BEGIN
    SELECT DISTINCT Type
    INTO      objectType
    FROM USER_SOURCE
    WHERE Name = UPPER(objectName) AND Type != 'PACKAGE BODY';

    RETURN objectType;

  EXCEPTION
    WHEN OTHERS THEN RETURN NULL;
  END;

  /*
    --- PUBLIC ---
  */

  PROCEDURE make (objectName USER_SOURCE.Name%TYPE) IS
    objectType USER_SOURCE.Type%TYPE := get_type(objectName);
    statement VARCHAR2(256);
  BEGIN
    IF objectType IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('compile_pkg.make('''|| objectName ||''') error:');
      DBMS_OUTPUT.PUT_LINE('- the type of '|| objectName ||' was not found.');
    RETURN; END IF;

    statement := 'ALTER '|| objectType ||' '|| objectName ||' COMPILE';
    IF objectType != 'PACKAGE' THEN
      EXECUTE IMMEDIATE statement;
      DBMS_OUTPUT.PUT_LINE('Compiled '|| objectName ||'.');
    ELSE
      EXECUTE IMMEDIATE statement || ' SPECIFICATION';
      EXECUTE IMMEDIATE statement || ' BODY';
      DBMS_OUTPUT.PUT_LINE('Compiled '|| objectName ||'''s specification and body.');
    END IF;
  END;

END compile_pkg;
/

BEGIN
  compile_pkg.make('web_employee_report');
  compile_pkg.make('emp_pkg');
  compile_pkg.make('emp_details');
END;
/

Package body created.

Compiled web_employee_report.
Compiled emp_pkg's specification and body.
compile_pkg.make('emp_details') error:
- the type of emp_details was not found.

Практическое занятие 16. Конструирование PL/SQL кода

Задание 1. Дополните пакет emp_pkg новой процедурой, которая выбирает сотрудников указанного отдела и сохраняет выбранные записи в локальную для пакета PL/SQL таблицу:

В спецификации пакета:
- Добавьте объявление процедуры get_employees. Процедура принимает на вход один параметр dept_id, типа Employees.Department_ID%TYPE.
- Объявите emp_tabletype типа TABLE OF Employees%ROWTYPE.
В теле пакета:
- Объявите локальную переменную emp_table типа emp_tabletype.
- Напишите реализацию процедуры get_employees. Процедура должна заполнять локальную таблицу emp_table с помощью операций массовой закачки данных (bulk fetch).
Создайте новую public процедуру show_employees в спецификации и теле пакета, которая не принимает на вход аргументов. Процедура отображает содержимое private таблицы emp_table. Данная процедура должна использовать процедуру print_employee.
Запустите на исполнение процедуру emp_pkg.get_employees для выборки сотрудников из 30 отдела и отобразите выборку с помощью emp_pkg.show_employees. Повторите это для обработки сотрудников 60 отдела.

CREATE OR REPLACE PACKAGE emp_pkg IS
  TYPE emp_tableType IS TABLE OF Employees%ROWTYPE;

  PROCEDURE add_employee (
    firstName Employees.First_Name%TYPE
  ,  lastName Employees.Last_Name%TYPE
  ,      mail Employees.Email%TYPE
  ,       job Employees.Job_ID%TYPE          := 'SA_REP'
  ,       mgr Employees.Manager_ID%TYPE      := 145
  ,       sal Employees.Salary%TYPE          := 1000
  ,      comm Employees.Commission_PCT%TYPE  := 0
  ,     depID Employees.Department_ID%TYPE   := 30
  );

  PROCEDURE add_employee (
    firstName Employees.First_Name%TYPE
  ,  lastName Employees.Last_Name%TYPE
  ,     depID Employees.Department_ID%TYPE
  );

  PROCEDURE get_employee (
    id            Employees.Employee_ID%TYPE
  , jobID     OUT Employees.Job_ID%TYPE
  , empSalary OUT Employees.Salary%TYPE
  );

  FUNCTION get_employee (p_emp_id Employees.Employee_ID%TYPE)
  RETURN Employees%ROWTYPE;

  FUNCTION get_employee (p_family_name Employees.Last_Name%TYPE)
  RETURN Employees%ROWTYPE;

  PROCEDURE get_employees (dept_id Employees.Department_ID%TYPE);
  PROCEDURE show_employees;

  PROCEDURE init_departments;

  PROCEDURE print_employee (emp Employees%ROWTYPE);
END emp_pkg;
/

Package created.

CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  emp_table emp_tableType;

  TYPE boolean_tab_type IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
  valid_departments boolean_tab_type;

  FUNCTION valid_deptid (depID Departments.Department_ID%TYPE) RETURN BOOLEAN;

  /*
    --- PUBLIC ---
  */
  PROCEDURE add_employee (
      firstName Employees.First_Name%TYPE
    ,  lastName Employees.Last_Name%TYPE
    ,      mail Employees.Email%TYPE
    ,       job Employees.Job_ID%TYPE          := 'SA_REP'
    ,       mgr Employees.Manager_ID%TYPE      := 145
    ,       sal Employees.Salary%TYPE          := 1000
    ,      comm Employees.Commission_PCT%TYPE  := 0
    ,     depID Employees.Department_ID%TYPE   := 30
    ) IS
  BEGIN
    IF valid_deptid(depID) THEN
      INSERT INTO Employees (
        Employee_ID
      , First_Name
      , Last_Name
      , Email
      , Hire_Date
      , Job_ID
      , Manager_ID
      , Salary
      , Commission_PCT
      , Department_ID
      )
      VALUES (
        Employees_Seq.NEXTVAL -- Employee_ID
      , firstName
      , lastName
      , mail
      , TRUNC(SYSDATE) -- Hire_Date
      , job
      , mgr
      , sal
      , comm
      , depID
      );
    ELSE
      DBMS_OUTPUT.PUT_LINE(
        'Error: cannot add an employee to a nonexistent department #' || depID || '.'
      );
    END IF;
  END add_employee;

  PROCEDURE add_employee (
      firstName Employees.First_Name%TYPE
    ,  lastName Employees.Last_Name%TYPE
    ,     depID Employees.Department_ID%TYPE
    ) IS
    email Employees.Email%TYPE := UPPER(SUBSTR(firstName, 1, 1)) || UPPER(SUBSTR(lastName, 1, 7));
  BEGIN
    add_employee(firstName, lastName, mail => email, depID => depID);
  END add_employee;

  PROCEDURE get_employee (
      id            Employees.Employee_ID%TYPE,
      jobID     OUT Employees.Job_ID%TYPE,
      empSalary OUT Employees.Salary%TYPE
    ) IS
  BEGIN
    SELECT Job_ID, Salary
    INTO   jobID,  empSalary
    FROM Employees
    WHERE Employee_ID = id;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('get_employee error:');
      DBMS_OUTPUT.PUT_LINE(
           '  could not get Employee information from'
        || ' get_employee(' || id || ', jobID, empSalary).'
      );
      DBMS_OUTPUT.PUT_LINE('  There is no employee with ID ' || id || '.');
  END get_employee;

  FUNCTION get_employee (p_emp_id Employees.Employee_ID%TYPE)
  RETURN Employees%ROWTYPE IS
    emp Employees%ROWTYPE;
  BEGIN
    SELECT *
    INTO   emp
    FROM Employees
    WHERE Employee_ID = p_emp_id;

    RETURN emp;
  END;

  FUNCTION get_employee (p_family_name Employees.Last_Name%TYPE)
  RETURN Employees%ROWTYPE IS
    emp Employees%ROWTYPE;
  BEGIN
    SELECT *
    INTO   emp
    FROM Employees
    WHERE Last_Name = p_family_name;

    RETURN emp;
  END;

  PROCEDURE get_employees (dept_id Employees.Department_ID%TYPE) IS
    CURSOR testCursor IS
      SELECT * FROM Employees WHERE Department_ID = dept_id;
  BEGIN
    OPEN testCursor;
    FETCH testCursor BULK COLLECT INTO emp_table;
    CLOSE testCursor;
  END;

  PROCEDURE show_employees IS
  BEGIN
    FOR i IN 1..emp_table.LAST LOOP
      print_employee(emp_table(i));
    END LOOP;    
  END;

  PROCEDURE init_departments IS BEGIN
    FOR row IN (SELECT Department_ID FROM Departments) LOOP
      valid_departments(row.Department_ID) := TRUE;
    END LOOP;
  END;

  PROCEDURE print_employee (emp Employees%ROWTYPE) IS BEGIN
    DBMS_OUTPUT.PUT_LINE(
         'Emp#' || emp.Employee_ID || ': '
      || emp.First_Name || ' ' || emp.Last_Name
      || ', depID is '  || emp.Department_ID
      || ', job is '    || emp.Job_ID
      || ', salary is ' || emp.Salary || '.'
    );
  END;


  /*
    --- PRIVATE ---
  */
  FUNCTION valid_deptid (depID Departments.Department_ID%TYPE)
  RETURN BOOLEAN IS 
  BEGIN
    IF valid_departments(depID) THEN RETURN TRUE;
    ELSE RETURN FALSE; END IF;
  EXCEPTION
    WHEN OTHERS THEN RETURN FALSE;
  END valid_deptid;

BEGIN
  init_departments;
END emp_pkg;
/

Package body created.

BEGIN
  emp_pkg.get_employees(30);
  emp_pkg.show_employees;
END;
/

Emp#114: Den Raphaely, depID is 30, job is PU_MAN, salary is 11000.
Emp#115: Alexander Khoo, depID is 30, job is PU_CLERK, salary is 3100.
Emp#116: Shelli Baida, depID is 30, job is PU_CLERK, salary is 2900.
Emp#117: Sigal Tubias, depID is 30, job is PU_CLERK, salary is 2800.
Emp#118: Guy Himuro, depID is 30, job is PU_CLERK, salary is 2600.
Emp#119: Karen Colmenares, depID is 30, job is PU_CLERK, salary is 2500.

BEGIN
  emp_pkg.get_employees(60);
  emp_pkg.show_employees;
END;
/

Emp#103: Alexander Hunold, depID is 60, job is IT_PROG, salary is 9000.
Emp#104: Bruce Ernst, depID is 60, job is IT_PROG, salary is 6000.
Emp#105: David Austin, depID is 60, job is IT_PROG, salary is 4800.
Emp#106: Valli Pataballa, depID is 60, job is IT_PROG, salary is 4800.
Emp#107: Diana Lorentz, depID is 60, job is IT_PROG, salary is 4200.

Задание 2. Необходимо обеспечить аудит добавления новых сотрудников в таблицу Employees с помощью процедуры emp_pkg.add_employee. Для этого:

Запустите на исполнение скрипт lab_07_02_a.sql (путь к файлу: \\feanor\public2\КИТвП\Курс 4\Семестр 8\Проектирование Web-приложений\Oracle Разработка программных единиц PLSQL\Oracle Database 10g - Develop PL-SQL Program Units\labs). Этот скрипт создаст в вашей схеме таблицу Log_NewEmp и последовательность log_newemp_seq.
```
CREATE TABLE log_newemp (
  entry_id  NUMBER(6) CONSTRAINT log_newemp_pk PRIMARY KEY,
  user_id   VARCHAR2(30),
  log_time  DATE,
  name      VARCHAR2(60)
);

CREATE SEQUENCE log_newemp_seq;
```
```
Table LOG_NEWEMP created.
Sequence LOG_NEWEMP_SEQ created.
```
В теле пакета emp_pkg измените реализацию той версии процедуры add_employee, которая на самом деле выполняет оператор INSERT в таблицу Employees. Добавьте в реализацию локальную процедуру audit_newEmp.
- Процедура audit_newEmp должна быть объявлена как автономная транзакция и выполнять оператор INSERT в таблицу log_newEmp.
- Процедура должна добавлять значения USER (имя текущего пользователя, который выполняет модификацию), текущего времени, и имени нового, добавляемого сотрудника.
- Используйте Log_NewEmp_Seq для заполнения столбца Entry_ID.
В теле процедуры add_employee вызовите на исполнение локальную процедуру audit_newEmp перед тем, как добавить запись о новом сотруднике в таблице Employees.
Протестируйте работу новой версии процедуры, добавив с помощью нее информацию о сотрудниках Max Smart из 20 отдела и Clark Kent из 10 отдела.
Просмотрите содержимое таблиц Employees и Log_NewEmp. Сколько записей добавлено в ту и другую таблицу?
Выполните ROLLBACK оператор для отката добавления данных о сотрудниках.
- Просмотрите содержимое таблицы Employees.
- Просмотрите содержимое таблицы Log_NewEmp.

CREATE OR REPLACE PACKAGE BODY emp_pkg IS

    -- ... ... ...

  PROCEDURE add_employee (
      firstName Employees.First_Name%TYPE
    ,  lastName Employees.Last_Name%TYPE
    ,      mail Employees.Email%TYPE
    ,       job Employees.Job_ID%TYPE         := 'SA_REP'
    ,       mgr Employees.Manager_ID%TYPE     := 145
    ,       sal Employees.Salary%TYPE         := 1000
    ,      comm Employees.Commission_PCT%TYPE := 0
    ,     depID Employees.Department_ID%TYPE  := 30
    ) IS
    PROCEDURE audit_newEmp IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
      INSERT INTO Log_NewEmp (Entry_ID, User_ID, Log_Time, Name)
      VALUES (
        Log_NewEmp_Seq.NEXTVAL
      , USER
      , SYSDATE
      , firstName ||' '|| lastName
      );
      COMMIT;
    END;
  BEGIN
    IF valid_deptid(depID) THEN
      audit_newEmp;

      INSERT INTO Employees (
        Employee_ID          , First_Name   , Last_Name , Email ,
        Hire_Date            , Job_ID       , Manager_ID, Salary,
        Commission_PCT       , Department_ID
      )
      VALUES (
        Employees_Seq.NEXTVAL, firstName    , lastName  , mail  ,
        TRUNC(SYSDATE)       , job          , mgr       , sal   ,
        comm                 , depID
      );
    ELSE
      DBMS_OUTPUT.PUT_LINE(
        'Error: cannot add an employee to a nonexistent department #' || depID || '.'
      );
    END IF;
  END add_employee;

  -- ... ... ...

END emp_pkg;
/

Package body created.

BEGIN
  emp_pkg.add_employee('Max', 'Smart', 20);
  emp_pkg.add_employee('Clark', 'Kent', 10);
END;
/

SELECT Employee_ID, First_Name, Last_Name, Department_ID
FROM Employees
WHERE Last_Name IN ('Smart', 'Kent');

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT_ID
228	Clark	Kent	10
227	Max	Smart	20

SELECT * FROM Log_NewEmp;

ENTRY_ID	USER_ID	LOG_TIME	NAME
1	RUMINAT	2019-04-17 03:59:58	Max Smart
2	RUMINAT	2019-04-17 03:59:58	Clark Kent

В обе таблицы было добавлено по 2 записи.

ROLLBACK;

SELECT Employee_ID, First_Name, Last_Name, Department_ID
FROM Employees
WHERE Last_Name IN ('Smart', 'Kent');

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT_ID

SELECT * FROM Log_NewEmp;

ENTRY_ID	USER_ID	LOG_TIME	NAME
1	RUMINAT	2019-04-17 03:59:58	Max Smart
2	RUMINAT	2019-04-17 03:59:58	Clark Kent

Задание 3. Необходимо обеспечить работу пакета emp_pkg под правами вызывающего.

С помощью оператора GRANT выдайте другому студенту права на исполнение (EXECUTE) вашего пакета emp_pkg.
Попросите коллегу выполнить процедуру XXX.emp_pkg.add_employee, где XXX — имя вашей схемы. По умолчанию, вызов происходит с правами владельца. Проверьте, в какой таблице Employees появилась новая запись.
Измените спецификацию своего пакета, добавив в нее инструкцию AUTHID CURRENT_USER. Перекомпилируйте пакет.
Попросите коллегу еще раз вызвать на исполнение вашу процедуру add_employee из пакета emp_pkg.
В какой схеме появиться запись о новом сотруднике?

Я (Ruminat) выполняю:

GRANT EXECUTE ON emp_pkg TO C##Bubblegum;

Пользователь C##Bubblegum выполняет:

BEGIN
  Ruminat.emp_pkg.add_employee('Elon', 'Musk', 10);
END;

SELECT Employee_ID, First_Name, Last_Name, Department_ID
FROM Employees
WHERE Last_Name = 'Musk';

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT_ID

SELECT Employee_ID, First_Name, Last_Name, Department_ID
FROM Ruminat.Employees
WHERE Last_Name = 'Musk';

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT_ID
231	Elon	Musk	10

Новая строчка была добавлена в мою таблицу.

CREATE OR REPLACE PACKAGE emp_pkg AUTHID CURRENT_USER IS
  -- ... ... ...
END emp_pkg;
/

Package created.

BEGIN
  Ruminat.emp_pkg.add_employee('Albert', 'Einstein', 10);
END;

SELECT Employee_ID, First_Name, Last_Name, Department_ID
FROM Employees
WHERE Last_Name = 'Einstein';

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT_ID
228	Albert	Einstein	10

SELECT Employee_ID, First_Name, Last_Name, Department_ID
FROM Ruminat.Employees
WHERE Last_Name = 'Einstein';

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT_ID

Новая строчка была добавлена в таблицу пользователя C##Bubblegum.

Практическое занятие 17. Создание триггеров

Задание 1. В таблице Jobs хранятся значения максимально и минимально допустимой зарплаты для данной должности. Необходимо создать триггер, связанный с операциями INSERT и UPDATE таблицы Employees, который не позволит устанавливать некорректную зарплату для сотрудников. Для этого:

Создайте процедуру check_salary:
- Процедура принимает на вход 2 параметра — идентификатор должности сотрудника и величину его зарплаты.
- Процедура использует Job_ID для определения максимально и минимально возможной зарплаты для этой должности (на основе данных в таблице Jobs).
- Если значение второго параметра не попадает в установленный для данной должности диапазон значений процедура должна генерировать исключение 'Invalid salary {sal} for this job. Salaries must be between {min} and {max}'. Заменяйте именованные зоны соответствующими значениями.
Создайте триггер check_salary_trg на таблицу Employees, который срабатывает для каждой строки перед операциями INSERT и UPDATE.
- Триггер должен вызывать на исполнение процедуру check_salary.
- Триггер должен передавать процедуре значения Job_ID и зарплаты.

CREATE OR REPLACE PROCEDURE
  check_salary (jobID Employees.Job_ID%TYPE, sal Employees.Salary%TYPE) IS
  minSal Jobs.Min_Salary%TYPE;
  maxSal Jobs.Max_Salary%TYPE;
BEGIN
  SELECT Min_Salary, Max_Salary
  INTO   minSal,     maxSal
  FROM Jobs
  WHERE Job_ID = jobID;

  IF sal NOT BETWEEN minSal AND maxSal THEN
    RAISE_APPLICATION_ERROR(
      -20001,
        'Invalid salary '|| sal ||' for this job. Salaries must be between '
      || minSal ||' and '|| maxSal
    );
  END IF;
END check_salary;
/

CREATE OR REPLACE TRIGGER check_salary_trg
BEFORE INSERT OR UPDATE OF Salary, Job_ID ON Employees
FOR EACH ROW
BEGIN
  check_salary(:NEW.Job_ID, :NEW.Salary);
END;
/

Procedure created.
Trigger created.

Задание 2. Протестируйте работу триггера.

Добавьте нового сотрудника с помощью процедуры emp_pkg.add_employee c именем Eleanor Beh и номером отдела 30. Что произойдет и почему? Какая зарплата присваивается сотруднику при добавлении?
Измените зарплату сотруднику 115 на $2000. С помощью отдельной операции измените его должность на HR_REP. Что произойдет и почему?
Измените зарплату 115 сотрудника на $2800. Что произойдет?

EXECUTE emp_pkg.add_employee('Eleanor', 'Beh', 30);

ERROR at line 1:
ORA-20001: Invalid salary 1000 for this job. Salaries must be between 6000 and 12008
ORA-06512: at "RUMINAT.CHECK_SALARY", line 11
ORA-06512: at "RUMINAT.CHECK_SALARY_TRG", line 2
ORA-04088: error during execution of trigger 'RUMINAT.CHECK_SALARY_TRG'
ORA-06512: at "RUMINAT.EMP_PKG", line 34
ORA-06512: at "RUMINAT.EMP_PKG", line 57
ORA-06512: at line 1

У нас не получилось добавить нового сотрудника, так как процедура emp_pkg.add_employee по умолчанию устанавливает зарплату, равную $1000.

UPDATE Employees SET Salary = 2000 WHERE Employee_ID = 115;

ERROR at line 1:
ORA-20001: Invalid salary 2000 for this job. Salaries must be between 2500 and 5500
ORA-06512: at "RUMINAT.CHECK_SALARY", line 11
ORA-06512: at "RUMINAT.CHECK_SALARY_TRG", line 2
ORA-04088: error during execution of trigger 'RUMINAT.CHECK_SALARY_TRG'

UPDATE Employees SET Job_ID = 'HR_REP' WHERE Employee_ID = 115;

ERROR at line 1:
ORA-20001: Invalid salary 3100 for this job. Salaries must be between 4000 and 9000
ORA-06512: at "RUMINAT.CHECK_SALARY", line 11
ORA-06512: at "RUMINAT.CHECK_SALARY_TRG", line 2
ORA-04088: error during execution of trigger 'RUMINAT.CHECK_SALARY_TRG'

У нас не получилось изменить строку, так как триггер вызывается и при изменении зарплаты, и при изменении должности.

UPDATE Employees SET Salary = 2800 WHERE Employee_ID = 115;

1 row updated.

Теперь зарплата попадает в нужный диапазон, всё замечательно.

Задание 3. Внесите изменения в триггер таким образом, чтобы он срабатывал только при фактическом изменении зарплаты или должности сотрудника. Проверять допустимость зарплаты нужно в 2 случаях — собственно при изменении зарплаты, и при изменении должности сотрудника. Почему?

Добавьте в описание триггера WHEN-конструкцию, проверяющую, что значение в столбцах Salary или Job_ID изменилось. Убедитесь, что Вы предусмотрели ситуацию, когда прежним (OLD) значением было NULL (в каком случае это происходит?).
Протестируйте работу триггера с помощью процедуры emp_pkg.add_employee и следующим набором параметров:
- p_first_name = 'Eleanor',
- p_last_name = 'Beh',
- p_Email = 'EBEH',
- p_Job = 'IT_PROG',
- p_sal = 5000.
Обновите зарплату сотрудников в должности IT_PROG — повысьте зарплату на $2000. Зафиксируйте результат.
Присвойте Eleanor Beh зарплату равную $9000.
Измените должность Eleanor Beh на ST_MAN. Зафиксируйте результат.

CREATE OR REPLACE TRIGGER check_salary_trg
BEFORE INSERT OR UPDATE OF Salary, Job_ID ON Employees
FOR EACH ROW
WHEN (
     OLD.Job_ID IS NULL       OR OLD.Salary IS NULL
  OR OLD.Job_ID != NEW.Job_ID OR OLD.Salary != NEW.Salary
)
BEGIN
  check_salary(:NEW.Job_ID, :NEW.Salary);
END;
/

Trigger created.

BEGIN
  emp_pkg.add_employee(
      firstName => 'Eleanor'
    ,  lastName => 'Beh'
    ,      mail => 'EBEH'
    ,       job => 'IT_PROG'
    ,       sal => 5000
  );
END;
/

SELECT Employee_ID, First_Name, Last_Name, Department_ID, Salary, Job_ID
FROM Employees
WHERE Last_Name = 'Beh';

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT_ID	SALARY	JOB_ID
235	Eleanor	Beh	30	5000	IT_PROG

UPDATE Employees SET Salary = Salary + 2000 WHERE Job_ID = 'IT_PROG';

Error starting at line : 16 in command -
UPDATE Employees SET Salary = Salary + 2000 WHERE Job_ID = 'IT_PROG'
Error report -
ORA-20001: Invalid salary 11000 for this job. Salaries must be between 4000 and 10000
ORA-06512: на  "RUMINAT.CHECK_SALARY", line 11
ORA-06512: на  "RUMINAT.CHECK_SALARY_TRG", line 2
ORA-04088: ошибка во время выполнения триггера 'RUMINAT.CHECK_SALARY_TRG'

UPDATE Employees SET Salary = 9000 WHERE First_Name = 'Eleanor' AND Last_Name = 'Beh';

1 row updated.

Задание 4. Напишите триггер, запрещающий удаление сотрудников в рабочие часы.

Создайте statement триггер с именем delete_emp_trg на таблицу Employees. Запретите удаление в период с 09:00 до 18:00. (или другой период актуальный на момент тестирования).
Попробуйте удалить сотрудника в должности SA_REP, который не приписан ни к какому отделу.

CREATE OR REPLACE TRIGGER delete_emp_trg
BEFORE DELETE ON Employees
BEGIN
  IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) NOT BETWEEN 8 AND 17 THEN
    RAISE_APPLICATION_ERROR(
      -20001,
      'You can delete an employee only during normal business hours (08:00–18:00)'
    );      
  END IF;
END;
/

Trigger created.

DELETE FROM Employees WHERE Job_ID = 'SA_REP' AND Department_ID IS NULL;

Error starting at line : 1 in command -
DELETE FROM Employees WHERE Job_ID = 'SA_REP' AND Department_ID IS NULL
Error report -
ORA-20001: You can delete an employee only during normal business hours (08:00–20:00)
ORA-06512: на  "RUMINAT.DELETE_EMP_TRG", line 3
ORA-04088: ошибка во время выполнения триггера 'RUMINAT.DELETE_EMP_TRG'

Четвёртый Блок

Практическое занятие 14. Встроенные пакеты Oracle

Практическое занятие 15. Использование динамического SQL

Практическое занятие 16. Конструирование PL/SQL кода

Практическое занятие 17. Создание триггеров