Задание 1. Какой из перечисленных блоков кода выполнится успешно?

BEGIN
END;

DECLARE
  amount INTEGER(10);
END;

DECLARE
BEGIN
END;

DECLARE
  amount INTEGER(10);
BEGIN
  DBMS_OUTPUT.PUT_LINE(amount);
END;

Задание 2. Напишите и выполните анонимный блок кода для вывода фразы Hello, World!. Сохраните созданный скрипт в файле lab_01_02.sql.

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;

Hello, World!

Задание 1. Укажите правильные и неправильные имена переменных:

• today — правильно.
• last_name — правильно.
• today’s date — неправильно!
• Number_of_days_February_this_year — неправильно!
• IsLeap$year — правильно.
• #number — неправильно!
• NUMBER# — правильно.
• number1to7 — правильно.

Задание 2. Укажите правильные и неправильные инструкции декларирования и инициализации:

• Numbers_of_copies PLS_INTEGER; — правильно.
• printer_name constant VARCHAR2(10); — неправильно!
• deliver_to VARCHAR2(10):=Jonson; — неправильно!
• by_when DATE:=SYSDATE+1; — правильно.

Задание 3. Проанализируйте следующий анонимный блок и выберите подходящее утверждение:

SET SERVEROUTPUT ON
DECLARE
  fname VARCHAR(20);
  lname VARCHAR(15) DEFAULT 'fernandez';
BEGIN
  DBMS_OUTPUT.PUT_LINE(FNAME || ' ' || lname);
END;

• Блок выполнится успешно и распечатает fernandez.
• Блок выдаст ошибку из-за того, что переменная fname используется без инициализации.
• Блок выполнится успешно и напечатает null fernandez.
• Блок выдаст ошибку, потому что нельзя использовать инструкцию DEFAULT для инициализации переменных типа VARCHAR2.
• Блок выдаст ошибку, потому что переменная FNAME не декларирована.

Задание 4. Загрузите в редактор скрипт для создания анонимного блока из пункта 2 первой Лабораторной работы, который вы сохранили в файле lab_01_02.sql.

• Добавьте в блок секцию декларирования переменных и включите в нее:
  • Переменную с именем today, типом данных DATE и с инициализацией по умолчанию с помощью системной функции SYSDATE.
  • Переменную tomorrow такого же типа данных, что и переменная today. Воспользуйтесь при этом директивой %TYPE.
• В исполняемой секции блока инициализируйте переменную tomorrow с помощью выражения прибавляющего один день к текущему значению переменной today.
• Распечатайте значения переменных today и tomorrow после фразы Hello, World!.

DECLARE
  today DATE := SYSDATE;
  tomorrow today%type;
BEGIN
  tomorrow := today + 1;
  DBMS_OUTPUT.PUT_LINE('Hello, World!');
  DBMS_OUTPUT.PUT_LINE('Today is ' || today || ' and tomorrow is ' || tomorrow);
END;

Hello, World!
Today is 18.02.19 and tomorrow is 19.02.19

Задание 5. Добавьте в скрипт 2 связанных (bind) переменных: basic_perсent и pf_percent типа NUMBER;

• В исполняемой секции кода присвойте этим переменным значения 45 и 12 соответственно.
• Отделите исполняемый блок от остальной части скрипта инструкцией / и выведите значения связанных переменных с помощью инструкции PRINT.
• Сохраните скрипт под именем lab_02_05.sql — он пригодится вам в следующих работах.

VARIABLE basic_perсent NUMBER;
VARIABLE pf_percent    NUMBER;
DECLARE
  today DATE := SYSDATE;
  tomorrow today%type;
BEGIN
	:basic_perсent := 45;
	:pf_percent    := 12;
	tomorrow       := today + 1;
  DBMS_OUTPUT.PUT_LINE('Hello, World!');
  DBMS_OUTPUT.PUT_LINE('Today is ' || today || ' and tomorrow is ' || tomorrow);
END;
/
PRINT basic_perсent pf_percent;

BASIC_PERСENT
-------------
           45

PF_PERCENT
----------
        12

Hello, World!
Today is 18.02.19 and tomorrow is 19.02.19

Задание 1. Проанализируйте блок кода и дополните его трассировочными инструкциями, необходимыми для ответа на вопросы:

DECLARE
  weight NUMBER(3) := 600;
  message VARCHAR2(255) := 'Product 10012';
BEGIN
  DECLARE
    weight NUMBER(3) := 1;
    message VARCHAR2(255) := 'Product 11001';
    new_locn VARCHAR2(50) := 'Europe';
  BEGIN
    weight := weight + 1;
    new_locn := 'Western ' || new_locn;
    /*(1)*/
  END;
  weight := weight + 1;
  message := message || ' is in stock';
  new_locn := 'Western ' || new_locn;
  /*(2)*/
END;
/

Данный блок кода не работает из-за 16-ой строки (обращение к переменной new_locn, которая определена лишь во вложенном блоке). Если убрать 16-ую строку, то получим:

• Значение переменной weight в позиции (1) — 2.
• Значение переменной new_locn в позиции (1) — 'Western Europe'.
• Значение переменной weight в позиции (2) — 601.
• Значение переменной message в позиции (2) — 'Product 10012 is in stock'.
• Значение переменной new_locn в позиции (2) — переменная не доступна в данной позиции.

DECLARE
  weight NUMBER(3) := 600;
  message VARCHAR2(255) := 'Product 10012';
BEGIN
  DECLARE
    weight NUMBER(3) := 1;
    message VARCHAR2(255) := 'Product 11001';
    new_locn VARCHAR2(50) := 'Europe';
  BEGIN
    weight := weight + 1;
    new_locn := 'Western ' || new_locn;
    DBMS_OUTPUT.PUT_LINE('(1) weight = ' || weight);
    DBMS_OUTPUT.PUT_LINE('(1) new_locn = ' || new_locn);
    /*(1)*/
  END;
  weight := weight + 1;
  message := message || ' is in stock';
  -- new_locn := 'Western ' || new_locn;
  DBMS_OUTPUT.PUT_LINE('(2) weight = ' || weight);
  DBMS_OUTPUT.PUT_LINE('(2) message = ' || message);
  /*(2)*/
END;
/

(1) weight = 2
(1) new_locn = Western Europe
(2) weight = 601
(2) message = Product 10012 is in stock

Задание 2. Проанализируйте блок кода и ответьте на вопросы (добавьте необходимые трассировочные инструкции):

DECLARE
  customer VARCHAR2(50) := 'Womansport';
  credit_rating VARCHAR2(50) := 'EXCELLENT';
BEGIN
  DECLARE
    customer NUMBER(7) := 201;
    name VARCHAR2(25) := 'Unisports';
  BEGIN
    credit_rating :='GOOD';
    …
  END;
  …
END;
/

• Значение и тип данных переменной customer во вложенном блоке: 201, NUMBER(7).
• Значение и тип данных переменной credit_rating во вложенном блоке: 'GOOD', VARCHAR2(50).
• Значение и тип данных переменной customer в основном (внешнем) блоке: 'Womansport', VARCHAR2(50).
• Значение и тип данных переменной name в основном блоке: not accessible.
• Значение и тип данных переменной credit_rating в основном блоке: 'GOOD', VARCHAR2(50).

DECLARE
  customer VARCHAR2(50) := 'Womansport';
  credit_rating VARCHAR2(50) := 'EXCELLENT';
BEGIN
  DECLARE
    customer NUMBER(7) := 201;
    name VARCHAR2(25) := 'Unisports';
  BEGIN
    credit_rating :='GOOD';
    DBMS_OUTPUT.PUT_LINE('nested customer = ' || customer || ', type = NUMBER(7)');
    DBMS_OUTPUT.PUT_LINE('nested credit_rating = ' || credit_rating || ', type = VARCHAR2(50)');
  END;
  DBMS_OUTPUT.PUT_LINE('main customer = ' || customer || ', type = VARCHAR2(50)');
  DBMS_OUTPUT.PUT_LINE('main name is not accessible in this block');
  DBMS_OUTPUT.PUT_LINE('main credit_rating = ' || credit_rating || ', type = VARCHAR2(50)');
END;
/

nested customer = 201, type = NUMBER(7)
nested credit_rating = GOOD, type = VARCHAR2(50)
main customer = Womansport, type = VARCHAR2(50)
main name is not accessible in this block
main credit_rating = GOOD, type = VARCHAR2(50)

Задание 3. Откройте скрипт, сохраненный в файле lab_02_05.sql. Выполните скрипт. Посмотрите на результат его работы. Теперь в него надо внести изменения:
Замечание: по смыслу и в соответствии с дальнейшими заданиями, необходимо закомментировать декларацию и присвоение для переменных today и tomorrow.

• Закомментируйте строчку, где выполняется декларирование связанных переменных.
• Закомментируйте инструкции, в которых связанным переменным присваиваются значения.
• Декларируйте две переменные: fname типа VARCHAR2(15), и emp_sal типа NUMBER(10).
• В исполняемую секцию включите инструкцию выборки имени и зарплаты 110 сотрудника из таблицы employees.

  SELECT first_name, salary
  INTO fname, emp_sal FROM employees
  WHERE employee_id=110;

• Поменяйте инструкцию, которая печатала «Hello, World!» на инструкцию, которая печатает «Hello, » и имя сотрудника.
• Подсчитайте налоговый вычет на основе зарплаты сотрудника. Используйте для этого следующую схему: налоговый вычет составляет 12% от базовой части зарплаты, а базовая часть зарплаты — это 45% от зарплаты.
• После приветствия сотрудника по имени – распечатайте его зарплату, и подсчитанный налоговый вычет.
• Результат должен выглядеть примерно так:

  Hello John
  YOUR SALARY IS: 8200
  YOUR CONTRIBUTION TOWARDS PF: 442.8
  PL/SQL procedure successfully completed.

-- VARIABLE basic_perсent NUMBER;
-- VARIABLE pf_percent    NUMBER;
DECLARE
  -- today DATE := SYSDATE;
  -- tomorrow today%type;
  fname VARCHAR2(15);
  emp_sal NUMBER(10);
BEGIN
  -- :basic_perсent := 45;
  -- :pf_percent    := 12;
  -- tomorrow       := today + 1;
  SELECT first_name, salary
  INTO fname, emp_sal FROM employees
  WHERE employee_id=110;
  DBMS_OUTPUT.PUT_LINE('Hello, ' || fname);
  DBMS_OUTPUT.PUT_LINE('YOUR SALARY IS: ' || emp_sal);
  DBMS_OUTPUT.PUT_LINE(
       'YOUR CONTRIBUTION TOWARDS PF: '
    || ROUND((0.12 * 0.45) * emp_sal, 1)
  );
  -- DBMS_OUTPUT.PUT_LINE('Today is ' || today || ' and tomorrow is ' || tomorrow);
END;
/
-- PRINT basic_perсent pf_percent;

Hello, John
YOUR SALARY IS: 8200
YOUR CONTRIBUTION TOWARDS PF: 442.8

Задание 4. В этом задании вы будете работать с переменными подстановки, запрашивая их значения в run-time. Продолжайте модифицировать скрипт из 3-го пункта.

• Добавьте в скрипт инструкцию PROMPT с информационным сообщением «Укажите номер сотрудника».
• Добавьте декларацию переменной empno так, чтобы она инициализировалась значением, полученным у пользователя.
• Измените инструкцию SELECT так, чтобы при поиске информации о сотруднике использовать значение переменной empno.
• Выполните скрипт и сохраните его под именем lab_03_04_soln.sql.
• Результат должен выглядеть примерно так (при empno = 100):

Hello Steven
YOUR SALARY IS: 24000
YOUR CONTRIBUTION TOWARDS PF: 1296
PL/SQL procedure successfully completed.

ACCEPT empno NUMBER PROMPT 'Укажите номер сотрудника';
DECLARE
  fname VARCHAR2(15);
  emp_sal NUMBER(10);
BEGIN
  SELECT first_name, salary
  INTO fname, emp_sal FROM employees
  WHERE employee_id = &&empno;
  DBMS_OUTPUT.PUT_LINE('Hello, ' || fname);
  DBMS_OUTPUT.PUT_LINE('YOUR SALARY IS: ' || emp_sal);
  DBMS_OUTPUT.PUT_LINE(
       'YOUR CONTRIBUTION TOWARDS PF: '
    || ROUND((0.12 * 0.45) * emp_sal, 1)
  );
END;
/

Hello, Steven
YOUR SALARY IS: 24000
YOUR CONTRIBUTION TOWARDS PF: 1296

Задание 5. Выполните скрипт lab_03_05.sql (CREATE TABLE employee_details AS SELECT * FROM EMPLOYEES). Будет создана таблица EMPLOYEE_DETAILS. Таблицы employees и employee_details имеют одинаковую структуру и данные. Все дальнейшие модификации выполняйте над данными из таблицы employee_details.

Задание 1. Создайте скрипт для выборки максимального номера отдела, сохраните его в переменной max_deptno, распечатайте полученное значение:

• Декларируйте переменную max_deptno типа NUMBER.
• В исполняемой секции блока выберите максимальное значение номера отдела и сохраните его в переменной.
• Распечатайте значение переменной в конце исполняемой секции.
• Сохраните результат в файле lab_04_01_soln.sql.
• Результат должен выглядеть примерно так:

The maximum department_id is: 270
PL/SQL procedure successfully completed.

DECLARE
  max_deptno NUMBER;
BEGIN
  SELECT MAX(Department_ID)
  INTO max_deptno
  FROM Departments;
  DBMS_OUTPUT.PUT_LINE('The maximum department_id is: ' || max_deptno);
END;

The maximum department_id is: 270

Задание 2. Измените скрипт из упражнения 1.

• В скрипте lab_04_01_soln.sql декларируйте две переменные: dept_name такого же типа, что и столбец departments.department_name и связанную переменную dept_id типа NUMBER.
• Присвойте переменной dept_name значение «Education» в секции декларации.
• Прибавьте к полученному максимальному значению номера отдела 10 и присвойте полученную цифру в переменную dept_id.
• Воспользуйтесь значениями переменных dept_name и dept_id в инструкции INSERT в таблицу departments. Для заполнения столбца location_id используйте NULL значение.
• С помощью атрибута SQL%ROWCOUNT узнайте количество строк обработанных инструкцией INSERT.
• С помощью SELECT инструкции убедитесь, что строчка добавлена.
• Сохраните скрипт в файле lab_04_02_soln.sql.

VARIABLE dept_id NUMBER;
DECLARE
  max_deptno NUMBER;
  dept_name Departments.Department_Name%TYPE := 'Education';
BEGIN
  SELECT MAX(Department_ID)
  INTO max_deptno
  FROM Departments;

  :dept_id := max_deptno + 10;
  
  INSERT INTO Departments (Department_ID, Department_Name, Location_ID)
  VALUES                  (:dept_id,      dept_name,       NULL       );
  
  DBMS_OUTPUT.PUT_LINE('The maximum department_id is: ' || max_deptno);
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' row(s) added');
END;
/

SELECT *
FROM Departments;

The maximum department_id is: 280
1 row(s) added

Задание 3. С помощью инструкции UPDATE измените значение location_id, для вновь добавленного отдела на 3000. Продолжите работу с предыдущим скриптом.

• В исполняемой секции блока выполните инструкцию UPDATE.
• Выведите новое состояние записи с помощью инструкции SELECT.
• После проверки удалите добавленную строку.
• Сохраните скрипт под именем lab_04_03_soln.sql.

VARIABLE dept_id NUMBER;
DECLARE
  max_deptno NUMBER;
  dept_name Departments.Department_Name%TYPE := 'Education';
BEGIN
  SELECT MAX(Department_ID)
  INTO max_deptno
  FROM Departments;

  :dept_id := max_deptno + 10;
  
  INSERT INTO Departments (Department_ID, Department_Name, Location_ID)
  VALUES                  (:dept_id,      dept_name,       NULL       );
  The maximum department_id is: 270
  1 row(s) added

  UPDATE Departments
  SET Location_ID = 3000
  WHERE Department_ID = :dept_id;
END;
/

SELECT *
FROM Departments;

The maximum department_id is: 270
There were added 1 rows.

DELETE FROM Departments
WHERE Department_ID = (SELECT MAX(Department_ID) FROM Departments);

1 row deleted.