Задание 1. Выполните скрипт lab_05_01.sql для создания таблицы messages. Напишите блок кода для заполнения таблицы:

• Добавьте числа в интервале от 1 до 10, пропустив 6 и 8. По одному числу на строку.
• Добавьте инструкцию COMMIT в конце блока.
• Проверьте результат с помощью инструкции SELECT.

BEGIN
  FOR i IN 1..10 LOOP
    CONTINUE WHEN i IN (6, 8);
    INSERT INTO Messages (Results) VALUES (i);
  END LOOP;
END;
/

COMMIT;

SELECT *
FROM Messages;

Задание 2. Выполните скрипт lab_05_02.sql. Этот скрипт создает реплику таблицы employees с именем emp и добавляет в нее новый столбец stars типа VARCHR2(50). Вам надо создать PL/SQL блок, который добавляет по одной «*» за каждую 1000$ в зарплате сотрудника.

• С помощью инструкции DEFINE определите переменную empno и присвойте ей значение 176.
• В начале исполняемого блока инициализируйте empno с помощью переменной подстановки.
• Декларируйте переменную asteriks типа emp.stars и присвойте ей значение NULL; Декларируйте переменную sal типа emp.salary.
• В исполняемой секции напишите блок кода, с помощью которого переменная asteriks получает по одной звездочке за каждую $1000 в зарплате сотрудника.
• Обновите столбец stars сотрудника с помощью полученного значения строчной переменной asteriks.
• Добавьте в конец исполняемого блока инструкцию COMMIT.
• С помощью инструкции SELECT выведите из таблицы измененную строчку.
• Сохраните скрипт в файле lab_05_02_soln.sql.
• Результат должен выглядеть примерно так:

  EMPLOYEE_ID	SALARY	STARS
  176	8600	********

DEFINE empno = 176

DECLARE
  empno NUMBER;
  asteriks Emp.Stars%TYPE := NULL;
  sal      Emp.Salary%TYPE;
BEGIN
  empno := &empno;

  SELECT Salary
  INTO   sal
  FROM Emp
  WHERE Employee_ID = empno;

  FOR i IN 1..NVL(FLOOR(sal / 1000), 0) LOOP
    asteriks := asteriks || '*';
  END LOOP;

  UPDATE Emp SET Stars = asteriks WHERE Employee_ID = empno;
END;
/

SELECT Employee_ID, Salary, Stars
FROM Emp
WHERE Employee_ID = &empno;

Задание 1. Напишите скрипт для распечатки информации о выбранной стране:

• Декларируйте переменную country_record типа countries%ROWTYPE.
• С помощью директивы DEFINE определите переменную countryid и присвойте ей значение CA по умолчанию. В исполняемой секции кода присвойте ей значение через переменную подстановки.
• Извлеките информацию из таблицы countries для страны с заданным значением countryid в переменную, и выведите данные с помощью переменной country_record.
• Результат должен выглядеть примерно так:

  Country Id: CA Country Name: Canada Region: 2
  PL/SQL procedure successfully completed.

• Протестируйте работу скрипта на странах с идентификаторами DE, UK, US.

DEFINE countryID = 'CA'

DECLARE
  country_record Countries%ROWTYPE;
  countryID VARCHAR2(64);
BEGIN
  countryID := '&countryID';
  
  SELECT *
  INTO country_record
  FROM Countries
  WHERE Country_ID = countryID;

  DBMS_OUTPUT.PUT_LINE(
       'Country Id: '    || countryID
    || ' Country Name: ' || country_record.Country_Name
    || ' Region: '       || country_record.Region_ID
  );
END;

Результат для страны DE:

Country Id: DE Country Name: Germany Region: 1

Результат для страны UK:

Country Id: UK Country Name: United Kingdom Region: 1

Результат для страны US:

Country Id: US Country Name: United States of America Region: 2

Задание 2. Напишите блок кода для выборки имени департамента из специальной структуры хранения INDEX BY … TABLE. Сохраните скрипт как lab_06_02_soln.sql.

• Декларируйте тип INDEX BY TABLE с именем dept_table_type предназначенный для хранения значений типа departments.department_name%TYPE. Декларируйте переменную my_dept_table типа dept_table_type.
• Декларируйте две переменные loop_count и deptno типа NUMBER. Присвойте им значения по умолчанию 10 и 0 соответственно.
• С помощью цикла получите из таблицы departments имена 10 отделов и сохраните их в my_dept_table. Начните с dept_id равного 10. Увеличивайте счетчик и номер отдела соответственно с шагом в 10 единиц. Номера и названия отделов, которые необходимо скопировать в индексированную таблицу, показаны на рисунке:

  DEPARTMENT_ID	DEPARTMENT_NAME
  10	Administration
  20	Marketing
  30	Purchasing
  40	Human Resourses
  50	Shipping
  60	IT
  70	Public Relations
  80	Sales
  90	Executive
  100	Finance

• С помощью другого цикла, извлеките значения из индексированной таблицы и распечатайте их.
• Сохраните скрипт под именем lab_06_02_soln.sql. Результат работы показан на рисунке:

  Administration
  Marketing
  Purchasing
  Human Resourses
  Shipping
  IT
  Public Relations
  Sales
  Executive
  Finance
  PL/SQL procedure successfully completed.

DECLARE
  -- Department table type
  TYPE dept_table_type IS TABLE OF
    Departments.Department_Name%TYPE
    INDEX BY PLS_INTEGER;
  -- Department table
  my_dept_table dept_table_type;
  
  -- useless variables
  loop_count NUMBER := 10;
  deptno     NUMBER := 0;
BEGIN
  FOR i IN 1..10 LOOP
    SELECT Department_Name
    INTO   my_dept_table(i)
    FROM Departments
    WHERE Department_ID = 10*i;
  END LOOP;

  FOR i IN my_dept_table.FIRST..my_dept_table.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(my_dept_table(i));
  END LOOP;
END;

Administration
Marketing
Purchasing
Human Resources
Shipping
IT
Public Relations
Sales
Executive
Finance

Задание 3. Измените скрипт из второго пункта, таким образом, чтобы в индексированной таблице хранились записи (record), содержащие всю информацию об отделах.

• Продолжайте работать с предыдущим скриптом.
• Измените декларирование типа dept_table_type так, чтобы теперь в индексированной таблице хранились элементы типа departments%ROWTYPE.
• Измените инструкцию SELECT таким образом, чтобы извлекать все колонки из таблицы departments для каждой записи.
• Измените второй цикл, чтобы вывести все данные сохраненные в индексированной таблице.
• Сохраните скрипт под новым именем. Результат должен выглядеть примерно так:

  Department Number: 10 Department Name: Administration Manager Id: 200 Location Id: 1700
  Department Number: 20 Department Name: Marketing Manager Id: 201 Location Id: 1800
  Department Number: 30 Department Name: Purchasing Manager Id: 114 Location Id: 1700
  Department Number: 40 Department Name: Human Resources Manager Id: 203 Location Id: 2400
  Department Number: 50 Department Name: Shipping Manager Id: 121 Location Id: 1500
  Department Number: 60 Department Name: IT Manager Id: 103 Location Id: 1400
  Department Number: 70 Department Name: Public Relations Manager Id: 204 Location Id: 2700
  Department Number: 80 Department Name: Sales Manager Id: 145 Location Id: 2500
  Department Number: 90 Department Name: Executive Manager Id: 100 Location Id: 1700
  Department Number: 100 Department Name: Finance Manager Id: 108 Location Id: 1700

DECLARE
  -- Department table type
  TYPE dept_table_type IS TABLE OF
    Departments%ROWTYPE
    INDEX BY PLS_INTEGER;
  -- Department table
  my_dept_table dept_table_type;
  
  -- useless variables
  loop_count NUMBER := 10;
  deptno     NUMBER := 0;
BEGIN
  FOR i IN 1..10 LOOP
    SELECT *
    INTO   my_dept_table(i)
    FROM Departments
    WHERE Department_ID = 10*i;
  END LOOP;

  FOR i IN my_dept_table.FIRST..my_dept_table.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(
         'Department Number: ' || my_dept_table(i).Department_ID
      || ' Department Name: '  || my_dept_table(i).Department_Name
      || ' Manager Id: '       || my_dept_table(i).Manager_ID
      || ' Location Id: '      || my_dept_table(i).Location_ID
    );
  END LOOP;
END;

Department Number: 10 Department Name: Administration Manager Id: 200 Location Id: 1700
Department Number: 20 Department Name: Marketing Manager Id: 201 Location Id: 1800
Department Number: 30 Department Name: Purchasing Manager Id: 114 Location Id: 1700
Department Number: 40 Department Name: Human Resources Manager Id: 203 Location Id: 2400
Department Number: 50 Department Name: Shipping Manager Id: 121 Location Id: 1500
Department Number: 60 Department Name: IT Manager Id: 103 Location Id: 1400
Department Number: 70 Department Name: Public Relations Manager Id: 204 Location Id: 2700
Department Number: 80 Department Name: Sales Manager Id: 145 Location Id: 2500
Department Number: 90 Department Name: Executive Manager Id: 100 Location Id: 1700
Department Number: 100 Department Name: Finance Manager Id: 108 Location Id: 1700

Задание 1. Напишите PL/SQL блок для определения n самых высоких зарплат сотрудников.

• Выполните скрипт lab_07_01.sql для создания новой таблицы top_salaries, предназначенной для хранения зарплаты сотрудников.
• Запросите у пользователя значение переменной n. Пользователи могут захотеть увидеть зарплату 5-ти, 10-ти и так далее самых высокооплачиваемых сотрудников. С помощью инструкции DEFINE заранее определите переменную p_num для хранения значения n.
• Декларируйте переменную num типа NUMBER для хранения значения p_num, переменную sal типа employees.salary, курсор emp_cursor, который выбирает зарплату сотрудников в порядке убывания. Помните, что повторов значений быть не должно.
• В исполняемой секции кода откройте цикл и выберите первые n записей из курсора и добавьте их в таблицу top_salaries. Не забывайте про атрибуты %ROWCOUNT и %FOUND.
• После добавления значений в таблицу просмотрите их с помощью инструкции SELECT. Пример результата:

  SALARY
  24000
  17000
  14000
  13500
  13000

• Протестируйте работу скрипта на различных значениях n: 0, 5, 10, 100, 1000. Не забывайте очищать таблицу top_salaries после каждого теста.

DEFINE p_num = &Enter_The_n

TRUNCATE TABLE Top_Salaries;

DECLARE
  num NUMBER := &p_num;
  sal Employees.Salary%TYPE;
  CURSOR emp_cursor IS
    SELECT DISTINCT Salary
    FROM Employees
    ORDER BY Salary DESC;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO sal;
    EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%ROWCOUNT > num;
    INSERT INTO Top_Salaries (Salary) VALUES (sal);
  END LOOP;
  CLOSE emp_cursor;
END;
/

SELECT *
FROM Top_Salaries;

Запросы для n: 0, 5, 10, 100, 1000:

Задание 2. Создайте PL/SQL блок, который делает следующее:

• С помощью директивы DEFINE определите переменную p_deptno для хранения department_id значения.
• Декларируйте переменную deptno типа NUMBER и присвойте ей значение p_deptno.
• Декларируйте курсор emp_cursor для выборки last_name, salary и manager_id сотрудников, работающих в отделе с указанным значением deptno.
• В исполняемой секции с помощью инструкции FOR и цикла пройдитесь по курсору и если salary меньше 5000 и manager_id 101 или 124 отобразите сообщение «<<last_name>> Due for a raise» иначе покажите сообщение «<<last_name>> Not due for a raise».
• Примерный результат:

  Department_ID	Message
  10	Whalen Due for a raise
  20	Hartstein Not due for a raise Fay Not due for a raise
  50	Weiss Not due for a raise Fripp Not due for a raise Kaufling Not due for a raise Vollman Not due for a raise Mourgos Not due for a raise ...................... Grant Due for a raise
  80	Russell Not due for a raise Partners Not due for a raise Errazuriz Not due for a raise ........................... Hutton Not due for a raise Taylor Not due for a raise Livingston Not due for a raise Johnson Not due for a raise

DEFINE p_deptno = &Enter_The_Department

DECLARE
  deptno NUMBER := &p_deptno;
  depOut   VARCHAR2(10);
  raiseOut VARCHAR2(10);
  CURSOR emp_cursor IS
    SELECT Last_Name, Salary, Manager_ID
    FROM Employees
    WHERE Department_ID = deptno;
BEGIN
  FOR emp IN emp_cursor LOOP
    depOut :=
      CASE
        WHEN emp_cursor%ROWCOUNT = 1
        THEN RPAD(deptno, LENGTH(deptno) + 3, ' ')
        ELSE RPAD(' ',    LENGTH(deptno) + 3, ' ')
      END;
    raiseOut :=
      CASE
        WHEN emp.Salary < 5000 AND emp.Manager_ID IN (101, 124)
        THEN ' Due'
        ELSE ' Not due'
      END;
    DBMS_OUTPUT.PUT_LINE(depOut || emp.Last_Name || raiseOut || ' for a raise');
  END LOOP;
END;

Результат для отдела 10:

10   Whalen Due for a raise

Результат для отдела 20:

20   Hartstein Not due for a raise
     Fay Not due for a raise

Результат для отдела 30:

30   Raphaely Not due for a raise
     Khoo Not due for a raise
     Baida Not due for a raise
     Tubias Not due for a raise
     Himuro Not due for a raise
     Colmenares Not due for a raise

Задание 3. Напишите PL/SQL для работы с параметризованным курсором.

• Декларируйте курсор dept_cursor для извлечения department_id, department_name для отделов с номером меньше 100. Отсортируйте выборку по возрастанию номеров отделов.
• Декларируйте другой курсор emp_cursor, который принимает deptartment_id на вход в качестве параметра и извлекает last_name, job_id, hire_date и salary для тех сотрудников, у которых employee_id меньше 120 и работающих в соответствующем отделе.
• Декларируйте переменные, для хранения значений, извлеченных из каждого курсора. Воспользуйтесь директивой %TYPE.
• Откройте dept_cursor и с помощью простого цикла и инструкции fetch выберите значения в соответствующую переменную. Распечатайте имя и номер отдела.
• Для каждого отдела отройте emp_cursor. С помощью вложенного цикла пробегитесь по нему, и извлеките значения в соответствующую переменную и распечатайте их.
• Закройте все циклы и курсоры в правильной последовательности. Выполните скрипт. Результат должен быть похож на рисунок:

  Department Number: 10 Department Name: Administration
  ---------------------------------------------------------
  Department Number: 20 Department Name: Marketing
  ---------------------------------------------------------
  Department Number: 30 Department Name: Purchasing
  Raphaely PU_MAN 07-DEC-02 11000
  Khoo PU_CLERK 18-MAY-03 3100
  Baida PU_CLERK 24-DEC-05 2900
  Tubias PU_CLERK 24-JUL-05 2800
  Himuro PU_CLERK 15-NOV-06 2600
  Colmenares PU_CLERK 10-AUG-07 2500
  ---------------------------------------------------------
  Department Number: 40 Department Name: Human Resources
  ---------------------------------------------------------
  Department Number: 50 Department Name: Shipping
  ---------------------------------------------------------
  Department Number: 60 Department Name: IT
  Hunold IT_PROG 03-JAN-06 9000
  Ernst IT_PROG 21-MAY-07 6000
  Austin IT_PROG 25-JUN-05 4800
  Pataballa IT_PROG 05-FEB-06 4800
  Lorentz IT_PROG 07-FEB-07 4200
  ---------------------------------------------------------
  Department Number: 70 Department Name: Public Relations
  ---------------------------------------------------------
  Department Number: 80 Department Name: Sales
  ---------------------------------------------------------
  Department Number: 90 Department Name: Executive
  King AD_PRES 17-JUN-03 24000
  Kochhar AD_VP 21-SEP-05 17000
  De Haan AD_VP 13-JAN-01 17000
  ---------------------------------------------------------

DECLARE
  -- Department cursor
  CURSOR dept_cursor IS
    SELECT Department_ID, Department_Name
    FROM Departments
    WHERE Department_ID < 100;

  -- Employee cursor
  CURSOR emp_cursor (depID Departments.Department_ID%TYPE) IS
    SELECT Last_Name, Job_ID, Hire_Date, Salary
    FROM Employees
    WHERE Employee_ID < 120 AND Department_ID = depID;

  -- Department variables
  depID   Departments.Department_ID%TYPE;
  depName Departments.Department_Name%TYPE;

  -- Employee variables
  lastName Employees.Last_Name%TYPE;
  jobID    Employees.Job_ID%TYPE;
  hireDate Employees.Hire_Date%TYPE;
  sal      Employees.Salary%TYPE;
BEGIN
  OPEN dept_cursor;
  LOOP -- Department
    FETCH dept_cursor INTO depID, depName;
    EXIT WHEN dept_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Department Number: ' || depID || ' Department Name: ' || depName);
    OPEN emp_cursor (depID);
    LOOP -- Employee
      FETCH emp_cursor INTO lastName, jobID, hireDate, sal;
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(
           lastName || ' '
        || jobID    || ' '
        || TO_CHAR(hireDate, 'dd-MON-rr', 'NLS_DATE_LANGUAGE = English') || ' '
        || sal
      );
    END LOOP; -- Employee
    CLOSE emp_cursor;
    DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------');
  END LOOP; -- Department
  CLOSE dept_cursor;
END;

Department Number: 10 Department Name: Administration
---------------------------------------------------------
Department Number: 20 Department Name: Marketing
---------------------------------------------------------
Department Number: 30 Department Name: Purchasing
Raphaely PU_MAN 07-DEC-02 11000
Khoo PU_CLERK 18-MAY-03 3100
Baida PU_CLERK 24-DEC-05 2900
Tubias PU_CLERK 24-JUL-05 2800
Himuro PU_CLERK 15-NOV-06 2600
Colmenares PU_CLERK 10-AUG-07 2500
---------------------------------------------------------
Department Number: 40 Department Name: Human Resources
---------------------------------------------------------
Department Number: 50 Department Name: Shipping
---------------------------------------------------------
Department Number: 60 Department Name: IT
Hunold IT_PROG 03-JAN-06 9000
Ernst IT_PROG 21-MAY-07 6000
Austin IT_PROG 25-JUN-05 4800
Pataballa IT_PROG 05-FEB-06 4800
Lorentz IT_PROG 07-FEB-07 4200
---------------------------------------------------------
Department Number: 70 Department Name: Public Relations
---------------------------------------------------------
Department Number: 80 Department Name: Sales
---------------------------------------------------------
Department Number: 90 Department Name: Executive
King AD_PRES 17-JUN-03 24000
Kochhar AD_VP 21-SEP-05 17000
De Haan AD_VP 13-JAN-01 17000
---------------------------------------------------------

Задание 1. Основная задача этого упражнения — продемонстрировать использование предопределенных исключений. Напишите PL/SQL блок для выборки сотрудников с определенной зарплатой.

• Удалите все записи из таблицы messages. С помощью директивы DEFINE определите переменную sal и инициализируйте ее значением 6000.
• В секции декларации исполняемого блока определите переменную ename типа employees.last_name%TYPE и переменную emp_sal типа employees.salary%TYPE и инициализируйте ее с помощью подстановочной переменной &sal.
• В исполняемой секции выберите фамилии тех сотрудников, чьи зарплаты равны значению переменной emp_sal. Не используйте явных курсоров! Если выборка вернет одну строку — добавьте в таблицу messages имя и зарплату сотрудника.
• Если для заданного значения зарплаты не найдено соответствующих записей или найдено несколько записей, обработайте соответствующие исключительные ситуации, и добавьте в таблицу messages записи «No employee with a salary of <salary>» или «More than one employee with a salary of <salary>».
• Просмотрите таблицу messages, чтобы оценить успешно или нет отработал PL/SQL блок.

TRUNCATE TABLE Messages;

DEFINE sal = 6000

DECLARE
  ename Employees.Last_Name%TYPE;
  emp_sal Employees.Salary%TYPE := &sal;
BEGIN
  SELECT Last_Name
  INTO ename
  FROM Employees
  WHERE Salary = emp_sal;
  
  INSERT INTO Messages (Results)
  VALUES (ename || ': ' || emp_sal || '$');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    INSERT INTO Messages (Results)
    VALUES ('No employee with a salary of ' || emp_sal);
  WHEN TOO_MANY_ROWS THEN
    INSERT INTO Messages (Results)
    VALUES ('More than one employee with a salary of ' || emp_sal);
END;
/

COMMIT;

SELECT *
FROM Messages;

Задание 2. В этом упражнении вы посмотрите, как создавать именованные исключения для стандартных системных ошибок Oracle сервера ORA-02292 (нарушение ограничений целостности).

• В декларативной секции объявите childrecord_exists типа EXCEPTION. Проассоциируйте эту переменную со стандартной ошибкой Oracle -02292. Воспользуйтесь инструкцией PRAGMA EXCEPTION_INIT.
• В исполняемой секции блока напечатайте сообщение «Deleting department 40........» и выполните инструкцию DELETE для удаления отдела с department_id равным 40.
• Добавьте секцию перехвата и обработки ошибок и поймайте в ней childrecord_exists. При обработке ошибке выведите соответствующее сообщение («Cannot delete this department. There are employees in this department»).

DECLARE
  childrecord_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT (childrecord_exists, -02292);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Deleting department 40........');
  DELETE FROM Departments
  WHERE Department_ID = 40;

EXCEPTION
  WHEN childrecord_exists THEN
    DBMS_OUTPUT.PUT_LINE('Cannot delete this department. There are employees in this department');
END;
/

Deleting department 40........
Cannot delete this department. There are employees in this department