Основы программирования в СУБД Oracle. SQL+PL/SQL.

Рассмотрим назначение этих таблиц и свойства некоторых столбцов. В таблице Employees содержатся данные о сотрудниках. Каждый сотрудник компании имеет уникальный идентификационный номер (employee_id), идентификационный номер должности (job_id), ставку заработной платы (salary) и менеджера (manager_id). Некоторые сотрудники в дополнение к зарплате получают комиссионные (commission_pct). Размер комиссионных определяется как часть от заработной платы. Столбец job_id используется для установления связи с таблицей Jobs, и для него определено ограничение внешнего ключа. Следствием этого является то, что значение данного столбца должно совпадать с одним из значений столбца job_id в таблице Jobs или иметь неопределенное значение NULL. Это ограничение обеспечивается средствами СУБД. Аналогичными свойствами обладает столбец department_id, который используется для установления связи с таблицей Departments.

В таблице Jobs содержится информация обо всех возможных должностях в организации. Каждая должность имеет уникальный идентификационный номер (job_id), наименование (job_title), минимальную (min_salary) и максимальную ставку заработной платы (max_salary).

Данные об отделах содержатся в таблице Departments. Каждый отдел имеет уникальный код (department_id), руководителя (manager_id), наименование (department_name), а также одно место расположения (location_id). Значение столбца manager_id должно совпадать со значением столбца employee_id в таблице Employees.

Эта компания имеет распределенную структуру, поэтому в таблице Locations хранятся данные о местонахождении отделов, которые состоят из адреса (street_address), почтового индекса (postal_code), названия города (city), названия штата (state_province) и кода страны (country_id). В таблице Locations также содержатся данные о населенных пунктах, в которых пока нет отделов.

Для того чтобы расширить спектр рассматриваемых задач, к уже рассмотренным таблицам были добавлены таблицы: Products, Orders, Customers Эти таблицы используются во многих демонстрационных базах. После добавления этих таблиц была получена схема базы данных HR_POC, используемая в этой книге, рисунок 1.3. Неиспользуемые таблицы были удалены. Ссылка для скачивания этой схемы: HR_POC (https://yadi.sk/d/_cFzi0CMazFIdg) (https://yadi.sk/d/_cFzi0CMazFIdg).

При решении некоторых задач, рассматриваемых в этой книге, в базу данных были добавлены вспомогательные таблицы. Ссылка для скачивания схемы, которая содержит вспомогательные таблицы:HR_POC_T (https://yadi.sk/d/a3XxApDuj2Ksxw) (https://yadi.sk/d/a3XxApDuj2Ksxw).

Рисунок 1.3. Схема базы данных HR_POC

Разберем назначение некоторых столбцов в таблицах Products, Orders, Customers и сформулируем бизнес-правила, которые могут быть определены с их использованием.

Столбец status в таблице Orders определяет состояние заказа и может принимать следующие значения: Pending – «в ожидании», Shipped – «отправлен», Canceled – «отменен». Используя этот столбец, сформулируем следующее бизнес-правило: можно изменить содержимое заказа, который находится в состоянии Pending, но нельзя изменить содержимое заказа, который находится в состоянии Shipped

Столбец credit_limit в таблице Customers содержит значение кредитного лимита клиента. Используя этот столбец можно сформулировать следующее правило: запретить оформление заказа, если общая сумма заказов клиента, находящихся в состоянии Pending, превышает его кредитный лимит. В этих таблицах отсутствуют данные об оплате заказов, поэтому будем считать, что заказы, находящиеся в состоянии Shipped, оплачены, а заказы, находящиеся в состоянии Pending, – нет.

Столбец price в таблице Products содержит текущую цену товара, а столбец unit_price в таблице Order_Items – цену, по которой он был продан. Разница между этими значениями может возникать из-за того, что клиенту предоставлена скидка. Также со временем значение price может измениться, а значение unit_price – нет.

В таблицу Employees был добавлен столбец rating_e. Значение элементов этого столбца целочисленные и должны лежать в диапазоне от 1 до 5. Будем считать, что значение столбца rating_e отражает квалификацию сотрудника.

В таблице Products содержится столбец rating_p. Значения элементов этого столбца также должны лежать в диапазоне от 1 до 5 и отражают сложность товара.

Используя эти столбцы, можно сформулировать следующее бизнес-правило: сотрудник имеет право продавать товары, рейтинг которых не превышает его рейтинга. Это бизнес-правило мы будем неоднократно использовать при решении задач.

Oracle SQL Developer

Oracle SQL Developer – это визуальная среда для создания, отладки и выполнения SQL-запросов и программ PL/SQL. Oracle SQL Developer позволяет создавать и редактировать объекты базы данных, управлять ими, импортировать и экспортировать данные, а также создавать всевозможные отчеты.

После запуска Oracle SQL Developer на экране появляется главное окно, представленное на рисунке 1.4.

Окно SQL Developer содержит три основные области:

– область Connections («Соединения») предназначена для создания соединений с базой данных;

– область Worksheet («Рабочее пространство») используется для ввода, редактирования и запуска запросов SQL и программ PL/SQL;

– область Reports («Отчеты») позволяет запускать предварительно определенные отчеты или создавать и добавлять собственные отчеты.

Рисунок 1.4. Главное окно Oracle SQL Developer

Для того чтобы приступить к работе с базой данных, необходимо создать соединение с ней. При запуске SQL Developer в области Connections отображаются все доступные соединения. Установить соединение можно только с существующей базой данных (схемой). Изначально после установки СУБД существует только схема администратора базы данных – system. Остальные схемы создает администратор. Имя схемы совпадает с именем пользователя. Команды создания пользователей и предоставления им привилегий будут рассмотрены позже.

Для создания нового соединения следует нажать кнопку New Connections, которая расположена на панели инструментов в области Connections. На экране появится диалоговое окно для создания подключений к базе данных (рисунок 1.5).

Имя соединения (Name) может быть произвольным, а имя пользователя и пароль должны быть предварительно заданы администратором. Имя пользователя должно начинаться с символов c##. Для одного пользователя (схемы базы данных) можно создать несколько соединений. При входе по любому из этих соединений вы будете работать с одной и той же базой данных. Значения Hostname и SID или Servce_name следует взять из файла tnsnames. ora в папке c:\app\user\product\18.0.0\dbhomexe\network\admin.

Рисунок 1.5. Окно для создания подключений к базе данных

После ввода всех параметров рекомендуется сначала нажать кнопку Test. Если проверка пройдет успешно, то в строке Status появится сообщение Success. В противном случае будет выведено сообщение об ошибке. Если ошибок не будет обнаружено, то следует нажать кнопку Connect и приступить к работе с базой данных.

После создания соединения с базой данных можно использовать область соединения для просмотра данных об объектах базы данных, включая таблицы, представления, индексы, пакеты, процедуры, триггеры.

Для того чтобы разорвать установленное соединение, следует щелкнуть на его имени правой кнопкой и в появившемся контекстном меню выбрать команду Disconnect.

Рассмотрим основные операции, которые можно выполнить, используя Oracle SQL Developer. Разберем случай, когда база данных уже создана, созданы таблицы и они заполнены данными. Нажав кнопку + рядом с именем соединения мы увидим объекты базы данных, для которой создано это соединение.

Самыми важными объектами базы данных являются таблицы. Для того чтобы увидеть список таблиц, существующих в базе данных, и получить возможность выполнять с таблицами различные действия, следует нажать кнопку + рядом с узлом Tables. Если после этого сделать двойной щелчок на имени таблицы, то в рабочей области появится окно, которое содержит несколько вкладок. Используя эти вкладки, можно просматривать данные о таблице и вносить в нее изменения.

На рисунке 1.6 показана вкладка Columns, в которой отображены столбцы таблицы Customers. Операции, которые вы можете выполнить с таблицей, содержатся в раскрывающемся списке Actions (рисунок 1.7).

Рисунок 1.6. Вкладка Columns

Рисунок 1.7. Раскрывающийся список Actions

На вкладке Data (рисунок 1.9), где отображается содержимое таблицы, можно просматривать и редактировать данные, содержащиеся в таблице.

Рисунок 1.9. Вкладка Data

На вкладке Model (рисунок 1.10) в графическом виде отображаются структура таблицы и ее связи с другими таблицами.

Рисунок 1.10. Вкладка Model

Использование рабочей области (SQL Worksheet)

При установлении соединения с базой данных автоматически открывается окно рабочей области (SQL Worksheet) для этого соединения. Это окно можно использовать для ввода, редактирования и выполнения операторов SQL и программ PL/SQL.

Для одного соединения можно создать несколько рабочих областей. Новую рабочую область можно создать, используя кнопку SQL Worksheet на панели инструментов или комбинацию клавиш Alt—F10. При создании новой рабочей области нужно выбрать соединение, для которого она будет использована. Рабочая область имеет собственную панель инструментов (рисунок 1.11).

Рисунок 1.11. Панель инструментов SQL Worksheet

Эта панель содержит значки, предназначенные для решения следующих задач:

– Run Statement: выполняет оператор, в котором находится курсор «переменные».

– Run Script: выполняет все операторы в рабочей области.

– Explain Plan: создает план выполнения. План выполнения – это последовательность операций, которые будут выполнены при выполнении оператора. План выполнения показывает исходное дерево строк с иерархией операций, составляющих оператор.

– Autotrace: генерирует информацию трассировки для оператора. Эта информация может помочь определить операторы SQL, которые выиграют от настройки.

– SQL Tuning Advisory: анализирует объемные операторы SQL и предлагает рекомендации по настройке.

– Commit: записывает любые изменения в базу данных и завершает транзакцию.

– Rollback: отменяет любые изменения в базе данных, не записывая их в базу данных, и завершает транзакцию.