Третий Блок

Практическое занятие 9. Создание процедур

  • Создание процедур.
  • Режимы параметров.

Задание 1. Создайте процедуру add_job для добавления записей в таблицу Jobs.

  • Процедура должна принимать на вход 2 параметра — id и job_title.
  • Запустите процедуру на исполнение с помощью анонимного блока или инструкции EXECUTE. Протестируйте работу на примере следующих значений, зафиксируйте и объясните результат:
    • IT_DBA и Database Administrator.
  • Запустите процедуру на исполнение с помощью анонимного блока или инструкции EXECUTE. Протестируйте работу на примере следующих значений, зафиксируйте и объясните результат:
    • ST_MAN и Stock Manager.
CREATE OR REPLACE PROCEDURE
  add_job (id Jobs.Job_ID%TYPE, job_title Jobs.Job_Title%TYPE) IS
BEGIN
  INSERT INTO Jobs (Job_ID, Job_Title)
  VALUES           (id,     job_title);
END add_job;
Procedure ADD_JOB compiled

EXECUTE add_job('IT_DBA', 'Database Administrator')
PL/SQL procedure successfully completed.

Всё прошло успешно, так как в таблице Jobs нет записи с ключом IT_DBA.

EXECUTE add_job('ST_MAN', 'Stock Manager')
Error starting at line : 1 in command -
BEGIN add_job('ST_MAN', 'Stock Manager'); END;
Error report -
ORA-00001: нарушено ограничение уникальности (RUMINAT.JOB_ID_PK)
ORA-06512: на  "RUMINAT.ADD_JOB", line 4
ORA-06512: на  line 1
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

Мы получили ошибку, так как в таблице Jobs уже есть запись с ключом ST_MAN.

Задание 2. Создайте процедуру upd_job для изменения значения в таблице Jobs.

  • Процедура должна принимать на вход параметр id и новое значение для столбца Job_Title. Процедура должна оповещать пользователей, в случае если никаких модификаций не было сделано. Для этого воспользуйтесь атрибутом SQL%FOUND и инструкцией RAISE_APPLICATION_ERROR или DBMS_OUTPUT.PUT_LINE.
  • Запустите процедуру на исполнение и поменяйте с ее помощью Job_Title для записи IT_DBA на Data Administrator. Сделайте выборку из таблицы Jobs, чтобы убедиться в модификации данных.
  • Протестируйте работу процедуры в случае получения на вход несуществующего Job_ID — к примеру, IT_WEB.
CREATE OR REPLACE PROCEDURE
  upd_job (id Jobs.Job_ID%TYPE, jobTitle Jobs.Job_Title%TYPE) IS
BEGIN
  UPDATE Jobs
  SET Job_Title = jobTitle
  WHERE Job_ID = id;
  
  IF NOT SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('upd_job error:');
    DBMS_OUTPUT.PUT_LINE(
         '  there were no modifications applied for'
      || ' upd_job(''' || id || ''', ''' || jobTitle || ''').'
    );
    DBMS_OUTPUT.PUT_LINE('  There is no job with ID ''' || id || '''.');
  END IF;
END upd_job;
Procedure UPD_JOB compiled

EXECUTE upd_job ('IT_DBA', 'Data Administrator')
SELECT * FROM Jobs WHERE Job_ID = 'IT_DBA';
JOB_IDJOB_TITLEMIN_SALARYMAX_SALARY
IT_DBAData Administrator(null)(null)
EXECUTE upd_job ('IT_WEB', 'Web developer')
upd_job error:
  there were no modifications applied for upd_job('IT_WEB', 'Web developer').
  There is no job with ID 'IT_WEB'.

Задание 3. Создайте процедуру del_job для удаления записей из таблицы Jobs.

  • Создайте процедуру с одним входным параметром — id. Создайте в процедуре обработчик на случай, если ни одна запись не будет удалена.
  • Протестируйте работу процедуры: вызовите её на исполнение и попытайтесь удалить существующую запись с Job_ID = IT_DBA.
  • Протестируйте работу процедуры: вызовите её на исполнение и попытайтесь с ее помощью удалить несуществующую запись, к примеру с Job_ID = IT_WEB. Зафиксируйте сообщение об ошибке.
CREATE OR REPLACE PROCEDURE
  del_job (id Jobs.Job_ID%TYPE) IS
BEGIN
  DELETE FROM Jobs WHERE Job_ID = id;
  
  IF NOT SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('del_job error:');
    DBMS_OUTPUT.PUT_LINE(
         '  there were no records deleted by'
      || ' del_job(''' || id || ''').'
    );
    DBMS_OUTPUT.PUT_LINE('  There is no job with ID ''' || id || '''.');
  END IF;
END del_job;
Procedure DEL_JOB compiled

EXECUTE del_job ('IT_DBA')
SELECT * FROM Jobs WHERE Job_ID = 'IT_DBA';
JOB_IDJOB_TITLEMIN_SALARYMAX_SALARY
EXECUTE del_job ('IT_WEB')
del_job error:
  there were no records deleted by del_job('IT_WEB').
  There is no job with ID 'IT_WEB'.

Задание 4. Создайте процедуру get_employee для выборки из таблицы Employees значений Job_ID и Salary указанного сотрудника.

  • Процедура должна принимать на вход Employee_ID и возвращать с помощью OUT параметров значения Job_ID и Salary.
  • Протестируйте работу процедуры передавая на вход локальные переменные блока вызова или хост-переменные. Просмотрите зарплату и должность для 120 сотрудника.
  • Просмотрите зарплату и должность для 300 сотрудника.
CREATE OR REPLACE 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;
Procedure GET_EMPLOYEE compiled
DECLARE
  jobID     Employees.Job_ID%TYPE;
  empSalary Employees.Salary%TYPE;
BEGIN
  get_employee(120, jobID, empSalary);
  DBMS_OUTPUT.PUT_LINE(
       'Employee #120: Job_ID = ' || NVL(jobID, '(null)')
    || ', Salary = ' || NVL(TO_CHAR(empSalary), '(null)') || '.'
  );
  get_employee(300, jobID, empSalary);
  DBMS_OUTPUT.PUT_LINE(
       'Employee #300: Job_ID = ' || NVL(jobID, '(null)')
    || ', Salary = ' || NVL(TO_CHAR(empSalary), '(null)') || '.'
  );
END;
Employee #120: Job_ID = ST_MAN, Salary = 8000.
get_employee error:
  could not get Employee information from get_employee(300, jobID, empSalary).
  There is no employee with ID 300.
Employee #300: Job_ID = (null), Salary = (null).

Практическое занятие 10. Создание функций

  • Создание функций.
  • Уровень чистоты.

Задание 1. Создайте функцию get_job, которая возвращает Job_Title.

  • Создайте функцию get_job, которая возвращает Job_Title, соответствующий переданному на вход Job_ID.
  • Для тестирования работы функции создайте анонимный блок, объявите хост-переменную b_title типа VARCHAR2, сохраните в нее результат работы функции и выведите результат работы функции для значения входного параметра SA_REP.
CREATE OR REPLACE FUNCTION
  get_job (id Jobs.Job_ID%TYPE)
RETURN Jobs.Job_Title%TYPE IS
  title Jobs.Job_Title%TYPE;
BEGIN
  SELECT Job_Title
  INTO   title
  FROM Jobs
  WHERE Job_ID = id;

  RETURN title;
END get_job;
Function GET_JOB compiled

VARIABLE b_title VARCHAR2;
DECLARE
  id Jobs.Job_ID%TYPE := 'SA_REP';
BEGIN
  :b_title := get_job(id);
  DBMS_OUTPUT.PUT_LINE('get_job(''' || id || ''') = ' || :b_title);
END;
get_job('SA_REP') = Sales Representative

Задание 2. Создайте функцию get_annual_comp для формирования фактической зарплаты сотрудника по заданной формуле.

  • Формула: (salary * 12) + (commission_pct * salary * 12). Функция должна принимать на вход месячную зарплату сотрудника и комиссию, и даже в случае если оба значения будут NULL возвращать не NULL.
  • Протестируйте работу функции с помощью оператора SELECT для сотрудников из 30 отдела.
CREATE OR REPLACE FUNCTION
  get_annual_comp (
    sal Employees.Salary%TYPE,
    pct Employees.Commission_PCT%TYPE
  )
RETURN NUMBER IS 
BEGIN
  RETURN (NVL(sal, 0) * 12) + (NVL(pct, 0) * NVL(sal, 0) * 12);
END get_annual_comp;
Function GET_ANNUAL_COMP compiled

SELECT
  Last_Name                               AS "Employee"      ,
  Salary                                  AS "Salary"        ,
  NVL(TO_CHAR(Commission_PCT), ' ')       AS "Commission_PCT",
  get_annual_comp(Salary, Commission_PCT) AS "Result" 
FROM Employees
WHERE Department_ID = 30;
EmployeeSalaryCommission_PCTResult
Raphaely11000132000
Khoo310037200
Baida290034800
Tubias280033600
Himuro260031200
Colmenares250030000

Задание 3. Создайте процедуру add_employee для добавления нового сотрудника в табличку Employees. Перед добавлением сотрудника процедура должна проверять с помощью функции valid_deptid, что указанный для него номер отдела присутствует в таблице Departments.

  • Создайте функцию valid_deptid, которая принимает на вход номер отдела и возвращает TRUE или FALSE, в зависимости от того есть или нет такой отдел в таблице Departments.
  • Создайте процедуру add_employee, которая добавляет запись в таблицу Employees только в том случае, если функция valid_deptid вернула TRUE, иначе выдает соответствующее сообщение об ошибке. Процедура принимает на вход следующие параметры и присваивает им указанные в скобках значения по умолчанию:
    1. first_name,
    2. last_name,
    3. e-mail,
    4. job (SA_REP),
    5. mgr (145),
    6. sal (1000),
    7. comm (0),
    8. deptid (30).
    Для генерации уникального идентификатора сотрудника воспользуйтесь последовательностью Employees_Seq. Установите значение в столбце Hire_Date с помощью функции TRUNC(SYSDATE).
  • Протестируйте работу процедуры для сотрудника Jane Harris из 15 отдела с e-mail-ом JAHARRIS (остальные значения оставьте по умолчанию). Зафиксируйте и объясните результат.
  • Протестируйте работу процедуры для сотрудника Joe Harris из 80 отдела с e-mail-ом JHARRIS (остальные значения оставьте по умолчанию). Зафиксируйте и объясните результат.
CREATE OR REPLACE FUNCTION
  valid_deptid (depID Departments.Department_ID%TYPE)
RETURN BOOLEAN IS 
BEGIN
  FOR i IN (
      SELECT 'Exists' FROM dual
      WHERE EXISTS (
        SELECT Department_ID
        FROM Departments
        WHERE Department_ID = depID
      )
    )
  LOOP
    RETURN TRUE;
  END LOOP;
  RETURN FALSE;
END valid_deptid;
/


CREATE OR REPLACE 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;
/
Function VALID_DEPTID compiled
Procedure ADD_EMPLOYEE compiled

EXECUTE add_employee('Jane', 'Harris', 'JAHARRIS', depID => 15)
Error: cannot add an employee to a nonexistent department #15.

Ничего не получилось, потому что не существует 15-го отдела.

EXECUTE add_employee('Joe', 'Harris', 'JHARRIS', depID => 80)
SELECT * FROM Employees WHERE Last_Name = 'Harris'
EMPLOYEE_IDFIRST_NAMELAST_NAMEEMAILPHONE_NUMBERHIRE_DATEJOB_IDSALARYCOMMISSION_PCTMANAGER_IDDEPARTMENT_ID
207JoeHarrisJHARRIS(null)26.03.19SA_REP1000014580

Теперь мы добавляем в существующий отдел, поэтому всё хорошо.

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

  • Создание пакетов.
  • Элементы пакета.
  • private и public конструкции пакета.

Задание 1. Создайте спецификацию и тело пакета job_pkg, и сохраните в нем созданные ранее процедуры и функции add_job, upd_job, del_job, get_job. Сохраните код создания спецификации и тела пакета в разных файлах *.sql. Для упрощения отладки включите инструкцию show errors в каждый скрипт.

  • Создайте спецификацию пакета. Объявите публичные элементы.
  • Создайте тело пакета. Включите в него реализацию процедур и функций.
  • Удалите ранее созданные (независимые) процедуры и функции: add_job, upd_job, del_job, get_job.
  • Протестируйте работу пакета. Запустите на исполнение процедуру job_pkg.add_job передав в качестве значений параметров:
    • IT_SYSAN, Systems analyst.
  • Сделайте выборку из таблицы Jobs, чтобы просмотреть результат работы процедуры.
CREATE OR REPLACE PACKAGE job_pkg IS
  PROCEDURE add_job (id Jobs.Job_ID%TYPE, job_title Jobs.Job_Title%TYPE);
  PROCEDURE upd_job (id Jobs.Job_ID%TYPE, jobTitle Jobs.Job_Title%TYPE);
  PROCEDURE del_job (id Jobs.Job_ID%TYPE);
  FUNCTION  get_job (id Jobs.Job_ID%TYPE) RETURN Jobs.Job_Title%TYPE;
END job_pkg;
Package JOB_PKG compiled
CREATE OR REPLACE PACKAGE BODY job_pkg IS

  PROCEDURE add_job (id Jobs.Job_ID%TYPE, job_title Jobs.Job_Title%TYPE) IS
  BEGIN
    INSERT INTO Jobs (Job_ID, Job_Title)
    VALUES           (id,     job_title);
  END add_job;


  PROCEDURE upd_job (id Jobs.Job_ID%TYPE, jobTitle Jobs.Job_Title%TYPE) IS
  BEGIN
    UPDATE Jobs
    SET Job_Title = jobTitle
    WHERE Job_ID = id;
    
    IF NOT SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE('upd_job error:');
      DBMS_OUTPUT.PUT_LINE(
           '  there were no modifications applied for'
        || ' upd_job(''' || id || ''', ''' || jobTitle || ''').'
      );
      DBMS_OUTPUT.PUT_LINE('  There is no job with ID ''' || id || '''.');
    END IF;
  END upd_job;


  PROCEDURE del_job (id Jobs.Job_ID%TYPE) IS
  BEGIN
    DELETE FROM Jobs WHERE Job_ID = id;
    
    IF NOT SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE('del_job error:');
      DBMS_OUTPUT.PUT_LINE(
           '  there were no records deleted by'
        || ' del_job(''' || id || ''').'
      );
      DBMS_OUTPUT.PUT_LINE('  There is no job with ID ''' || id || '''.');
    END IF;
  END del_job;


  FUNCTION get_job (id Jobs.Job_ID%TYPE)
  RETURN Jobs.Job_Title%TYPE IS
    title Jobs.Job_Title%TYPE;
  BEGIN
    SELECT Job_Title
    INTO   title
    FROM Jobs
    WHERE Job_ID = id;

    RETURN title;
  END get_job;

END job_pkg;
Package Body JOB_PKG compiled

DROP PROCEDURE add_job;
DROP PROCEDURE upd_job;
DROP PROCEDURE del_job;
DROP FUNCTION  get_job;
Procedure ADD_JOB dropped.
Procedure UPD_JOB dropped.
Procedure DEL_JOB dropped.
Function GET_JOB dropped.

EXECUTE job_pkg.add_job('IT_SYSAN', 'Systems analyst')
SELECT * FROM Jobs WHERE Job_ID = 'IT_SYSAN'
JOB_IDJOB_TITLEMIN_SALARYMAX_SALARY
IT_SYSANSystems analyst(null)(null)

Задание 2. Создайте пакет, состоящий из public и private конструкций.

  • Создайте спецификацию и тело пакета emp_pkg, в котором есть public процедуры add_employee и get_employee, и private функция valid_deptid.
  • Протестируйте работу процедуры emp_pkg.add_employee. Добавьте в таблицу информацию о сотруднике Jane Harris из 15 отдела, с e-mail-ом JAHARRIS. Вы должны получить сообщение об ошибке, потому что 15 отдела не существует.
  • Запустите процедуру еще раз и добавьте информацию о David Smith из 80 отдела, с e-mail-ом DASMITH.
  • Сделайте выборку из таблицы Employees, чтобы убедиться в корректном добавлении новых сотрудников.
CREATE OR REPLACE PACKAGE 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
  );

  PROCEDURE get_employee (
    id            Employees.Employee_ID%TYPE
  , jobID     OUT Employees.Job_ID%TYPE
  , empSalary OUT Employees.Salary%TYPE
  );
END emp_pkg;
Package EMP_PKG compiled
CREATE OR REPLACE PACKAGE BODY emp_pkg IS

  /*
    --- PRIVATE ---
  */
  FUNCTION valid_deptid (depID Departments.Department_ID%TYPE)
  RETURN BOOLEAN IS 
  BEGIN
    FOR i IN (
        SELECT 'Exists' FROM dual
        WHERE EXISTS (
          SELECT Department_ID
          FROM Departments
          WHERE Department_ID = depID
        )
      )
    LOOP
      RETURN TRUE;
    END LOOP;
    RETURN FALSE;
  END valid_deptid;


  /*
    --- 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 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;

END emp_pkg;
Package Body EMP_PKG compiled

EXECUTE emp_pkg.add_employee('Jane', 'Harris', 'JAHARRIS', depID => 15)
Error: cannot add an employee to a nonexistent department #15.

EXECUTE emp_pkg.add_employee('David', 'Smith', 'DASMITH', depID => 80)
SELECT * FROM Employees WHERE Last_Name = 'Smith'
EMPLOYEE_IDFIRST_NAMELAST_NAMEEMAILPHONE_NUMBERHIRE_DATEJOB_IDSALARYCOMMISSION_PCTMANAGER_IDDEPARTMENT_ID
210DavidSmithDASMITH(null)27.03.19SA_REP1000014580
159LindseySmithLSMITH011.44.1345.72926810.03.05SA_REP80000.314680
171WilliamSmithWSMITH011.44.1343.62926823.02.07SA_REP74000.1514880

Практическое занятие 13. Работа с пакетами

  • Обслуживание пакетов.
  • Устойчивое состояние пакета.
  • PRAGMA SERIALLY_REUSABLE.

Задание 1. Измените пакет emp_pkg, созданный в практическом занятии 12. Необходимо перегрузить процедуру add_employee. Для этого:

  • В спецификацию пакета добавьте описание еще одной процедуры add_employee, принимающей на вход 3 параметра:
    • first_name, last_name и department_id.
  • Сохраните и скомпилируйте внесенные изменения.
  • Создайте реализацию новой процедуры в теле пакета.
    • Процедура должна формировать e-mail адрес из первой буквы имени и семи первых букв фамилий сотрудника — в верхнем регистре.
    • После формирования e-mail, новая версия процедуры add_employee должна вызывать на исполнение старую версию этой процедуры из этого же пакета, для добавления записи в таблицу. Сформированный e-mail передается при вызове в качестве входного параметра.
    • Сохраните и скомпилируйте тело пакета.
  • Запустите процедуру на исполнение передав значения параметров:
    • Samuel Joplin и 30 отдел.
CREATE OR REPLACE PACKAGE emp_pkg IS
  
  -- .....................

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

  -- .....................

END emp_pkg;
Package EMP_PKG compiled
CREATE OR REPLACE PACKAGE BODY emp_pkg IS

  -- .....................

  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;

  -- .....................

END emp_pkg;
Package Body EMP_PKG compiled

EXECUTE emp_pkg.add_employee('Samuel', 'Joplin', 30)
SELECT * FROM Employees WHERE Last_Name = 'Joplin'
EMPLOYEE_IDFIRST_NAMELAST_NAMEEMAILPHONE_NUMBERHIRE_DATEJOB_IDSALARYCOMMISSION_PCTMANAGER_IDDEPARTMENT_ID
211SamuelJoplinSJOPLIN(null)02.04.19SA_REP1000014530

Задание 2. В пакете emp_pkg создайте 2 функции get_employee.

  • В спецификацию пакета добавьте объявление функций:
    • get_employee, которая принимает на вход параметр p_emp_id типа Employees.Employee_ID%TYPE Функция должна возвращать значение типа Employees%ROWTYPE (запись, соответствующую структуре таблицы Employees).
    • get_employee, которая принимает на вход параметр p_family_name типа Employees.Last_Name%TYPE. Функция должна возвращать значение типа Employees%ROWTYPE (запись, соответствующую структуре таблицы Employees).
  • Скомпилируйте спецификацию пакета.
  • В тело пакета добавьте реализацию функций.
    • Первая get_employee должна содержать выборку, возвращающую запись о сотруднике с переданным на вход значением Employee_ID,
    • Вторая get_employee должна отбирать записи о сотрудниках с переданной на вход фамилией p_family_name.
  • Скомпилируйте тело пакета.
  • Добавьте в пакет процедуру print_employee.
    • Процедура принимает на вход параметр типа Employees%ROWTYPE.
    • С помощью DBMS_OUTPUT.PUT_LINE выводит значение из полей Department_ID, Employee_ID, First_Name, Last_Name, Job_ID, Salary.
  • Скомпилируйте пакет.
  • С помощью анонимного блока протестируйте работу пакета. Для этого запустите на исполнение процедуру emp_pkg.get_employee, передав ей на вход employee_id = 100, family_name = 'Joplin'. А потом с помощью процедуры emp_pkg.print_employee распечатайте результат.
CREATE OR REPLACE PACKAGE emp_pkg IS
  
  -- .....................

  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 print_employee (emp Employees%ROWTYPE);
END emp_pkg;
Package EMP_PKG compiled
CREATE OR REPLACE PACKAGE BODY emp_pkg IS

  -- .....................

  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 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;

END emp_pkg;
Package Body EMP_PKG compiled

BEGIN
  emp_pkg.print_employee(emp_pkg.get_employee(100));
  emp_pkg.print_employee(emp_pkg.get_employee('Joplin'));
END;
Emp#100: Steven King, depID is 90, job is AD_PRES, salary is 24000.
Emp#211: Samuel Joplin, depID is 30, job is SA_REP, salary is 1000.

Задание 3. Структура отделов организации меняется не часто, поэтому для улучшения производительности пакета emp_pkg добавьте в него public процедуру init_departments и private индексную таблицу valid_departments. Процедура будет один раз, в начале работы с пакетом, заполнять индексную таблицу номерами существующих отделов, для повышения производительности использования функции valid_deptid, которая теперь будет анализировать не актуальную таблицу Departments, а индексную таблицу в теле пакета. Для этого:

  • Добавьте в спецификацию пакета объявление процедуры init_departments, не принимающей на вход никаких параметров.
  • Добавьте в тело пакета декларирование типа индексной таблицы, саму таблицу valid_departments и описание процедуры init_departments.
    • Таблица хранит значения типа BOOLEAN и использует значения из столбца Departments.Department_ID в качестве индекса:
      • TYPE boolean_tab_type IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
valid_departments boolean_tab_type;
    • Добавьте описание процедуры init_departments в конец тела пакета. Процедура должна заполнить индексную таблицу значениями: используйте значение столбца Department_ID в качестве индекса записи в valid_departments, а в качестве соответствующего значения записи используйте TRUE.
  • В теле пакета создайте инициализационный блок, в котором вызывается на исполнение процедура init_departments.
  • Сохраните изменения и скомпилируйте пакет заново.
CREATE OR REPLACE PACKAGE emp_pkg IS
  PROCEDURE init_departments;

  -- .....................

END emp_pkg;
Package EMP_PKG compiled

CREATE OR REPLACE PACKAGE BODY emp_pkg IS

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

  -- .....................

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

BEGIN
  init_departments();
END emp_pkg;
/
Package Body EMP_PKG compiled

Задание 4. Измените реализацию функции valid_deptid, таким образом, чтобы она анализировала содержимое индексированной таблицы.

  • Поменяйте реализацию функции. Сохраните и скомпилируйте изменения.
  • Протестируйте работу пакета. Вызовите на исполнение процедуру add_employee с параметрами
    • name = 'James Bond' и department_id = 15.
    Что произойдет?
  • Добавьте в таблицу Departments новый отдел с номером 15 и названием Security. Подтвердите модификацию с помощью инструкции COMMIT.
  • Ещё раз вызовите на исполнение процедуру add_employee с параметрами:
    • name = 'James Bond' и department_id = 15.
    Что произойдет теперь? Почему?
  • Запустите на исполнение процедуру emp_pkg.init_departments. Зачем это надо сделать? На что это повлияет?
  • Ещё раз вызовите на исполнение процедуру add_employee с параметрами
    • name = 'James Bond' и department_id = 15.
    Что произойдет теперь? Почему?
  • Удалите запись о James Bond и 15 отделе из соответствующих таблиц и ещё раз обновите таблицу valid_departments.
CREATE OR REPLACE PACKAGE emp_pkg IS
  PROCEDURE init_departments;

  -- .....................

END emp_pkg;
Package EMP_PKG compiled

CREATE OR REPLACE PACKAGE BODY emp_pkg IS

  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 IS
  BEGIN
    IF valid_departments(depID) THEN RETURN TRUE;
    ELSE RETURN FALSE; END IF;
  EXCEPTION
    WHEN OTHERS THEN RETURN FALSE;
  END valid_deptid;

  -- .....................

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

BEGIN
  init_departments();
END emp_pkg;
/
Package Body EMP_PKG compiled

EXECUTE emp_pkg.add_employee('James', 'Bond', 15)
Error: cannot add an employee to a nonexistent department #15.

Ошибка — у нас нет 15-го отдела.

INSERT INTO Departments (Department_ID, Department_Name)
VALUES                  (15,            'Security');
COMMIT;
1 row inserted.
Commit complete.

EXECUTE emp_pkg.add_employee('James', 'Bond', 15)
Error: cannot add an employee to a nonexistent department #15.

Всё равно ошибка — мы не обновили таблицу valid_departments.

EXECUTE emp_pkg.init_departments()

Мы обновили таблицу, 15-ый отдел теперь виден функции valid_deptid.

EXECUTE emp_pkg.add_employee('James', 'Bond', 15)
SELECT * FROM Employees WHERE Last_Name = 'Bond'
EMPLOYEE_IDFIRST_NAMELAST_NAMEEMAILPHONE_NUMBERHIRE_DATEJOB_IDSALARYCOMMISSION_PCTMANAGER_IDDEPARTMENT_ID
214JamesBondJBOND(null)02.04.19SA_REP1000014515
DELETE FROM Employees WHERE Last_Name = 'Bond';
COMMIT;
1 row deleted.
Commit complete.
DELETE FROM Departments WHERE Department_ID = 15;
COMMIT;
1 row deleted.
Commit complete.

EXECUTE emp_pkg.init_departments()

Задание 5. Поменяйте местами описание процедур и функций в пакете так, чтобы их имена шли в алфавитном порядке.

  • Перепишите спецификацию пакета так, чтобы все элементы были перечислены в алфавитном порядке. Сохраните и скомпилируйте спецификацию пакета.
  • Перепишите тело пакета так, чтобы все элементы были перечислены в алфавитном порядке. Сохраните и скомпилируйте тело пакета. Зафиксируйте результат.
  • Сделайте все необходимое, чтобы исправить ошибки компиляции. С чем они связаны? Выполните необходимые действия (подсказка: forward declaration).
CREATE OR REPLACE PACKAGE 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
  );

  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 init_departments;

  PROCEDURE print_employee (emp Employees%ROWTYPE);
END emp_pkg;
Package EMP_PKG compiled
CREATE OR REPLACE PACKAGE BODY emp_pkg IS

  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 init_departments IS BEGIN
    DBMS_OUTPUT.PUT_LINE(someVar || ' -- some var');
    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 EMP_PKG compiled

Если мы закомментируем строчку (с forward declaration):

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

то получим следующую ошибку: