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

Создание пользователей и предоставление привилегий

Все действия с базой данных, включая создание ее объектов, осуществляются пользователями, поэтому на первом этапе необходимо создать пользователя и предоставить ему необходимые права (привилегии) для работы с базой данных.

Эту операцию должен выполнить администратор базы данных. По умолчанию администратором базы данных является пользователь SYSTEM.

Для создания нового пользователя используется команда CREATE USER. Упрощенный синтаксис этой команды имеет следующий вид:

CREATE USER {имя пользователя} IDENTIFIED BY {пароль}

DEFAULT TABLESPACE tablespace_name

QUOTA size (K | M | G) UNLIMITED ON tablespace_name;

где:

– tablespace_name – имя области данных, в которой будут создаваться объекты пользователя. Если имя области данных не указано, то объекты пользователя по умолчанию создаются в области данных SYSTEM;

– tablespace_tmp – имя области данных для временных объектов пользователя. Если имя области данных не указано, то временные объекты создаются в области данных SYSTEM;

– size – размер используемого пространства для области данных TABLESPACE в килобайтах (К), мегабайтах (М) и гигабайтах (G). Ключевое слово UNLIMITED используется для предоставления пространства неограниченного размера.

Пример:

CREATE USER C##HR_POC IDENTIFIED BY PASS123

DEFAULT TABLESPACE USERS

QUOTA 500M ON USERS;

Изменение пароля осуществляется командой:

ALTER USER {имя пользователя} IDENTIFIED BY {пароль};

Например:

ALTER USER C##HR_POC IDENTIFIED BY PASS456;

Для просмотра данных об объектах пользователя можно использовать следующий запрос:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED,

STATUS

FROM SYS. DBA_OBJECTS

WHERE OWNER = ′ {имя пользователя} ′;

Например:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS

FROM SYS. DBA_OBJECTS

WHERE OWNER = ′ C##HR_POC ′;

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

SELECT USERNAME FROM DBA_USERS ORDER BY 1;

Для удаления пользователя и принадлежащей ему схемы служит команда:

DROP USER {имя пользователя} CASCADE;

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

Привилегии делятся на системные и объектные. В таблице 1.1 приведены основные системные привилегии, которые администратор может предоставить пользователю, а таблица 1.2 содержит основные объектные привилегии.

Таблица 1.1. Основные системные привилегии

Таблица 1.2. Основные объектные привилегии

Для предоставления пользователю системных привилегий используется команда GRANT, которая имеет следующий синтаксис:

GRANT {имя привилегии} ON {имя пользователя};

Пример предоставления системных привилегий пользователю:

GRANT CREATE SESSION, CREATE TABLE ON C##HR_POC;

Для отзыва привилегии используется команда:

REVOKE {имя привилегии} ON {имя пользователя};

Пример отзыва системной привилегии у пользователя:

REVOKE CREATE TABLE ON C##HR_POC;

Для предоставления пользователю объектных привилегий используется команда GRANT, которая имеет следующий синтаксис:

GRANT {имя привилегии} ({список столбцов}) ON {имя таблицы}

TO {имя пользователя};

где: ({список столбцов}) – список столбцов, для которых предоставляется привилегия. Если column_list отсутствует, то привилегия предоставляется для всех столбцов.

Пример предоставления объектной привилегии:

GRANT UPDATE (RATING_E, SALARY) ON EMPLOYEES TO C##HR_POC;

Для отзыва привилегии используется команда:

REVOKE {имя привилегии} ({список столбцов})

ON {имя таблицы} TO {имя пользователя};

Пример отзыва объектной привилегии:

REVOKE UPDATE (SALARY) ON EMPLOYEES TO C##HR_POC;

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

SELECT *

FROM USER_SYS_PRIVS;

Для просмотра объектных привилегий служит запрос

SELECT *

FROM USER_TAB_PRIVS_MADE

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

Таблица 1.3. Список имен предопределенных ролей

Используя команду

GRANT {имя роли} ON {имя пользователя};

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

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

REVOKE {имя привилегии} ON {имя пользователя};

Для отзыва всех привилегий, назначенных ролью, следует использовать команду:

REVOKE {имя роли} ON {имя пользователя};

Следует иметь в виду, что роль RESOURCE включает предоставление привилегии UNLIMITED TABLESPACE и не включает привилегию CREATE VIEW. Учитывая это, привилегии, которые следует предоставлять пользователям, не являющимся администраторами базы данных, можно предоставить командой:

GRANT CONNECT, RESOURCE, CREATE VIEW TO {имя пользователя};

ЧАСТЬ 2. SQL – ЯЗЫК СТРУКТУРИРОВАННЫХ ЗАПРОСОВ

SQL (Structured Query Language) – язык структурированных запросов, является основным языком определения, манипулирования и управления данными в современных СУБД. Принципы работы с данными, на которых основан SQL, существенно отличаются от принципов решения таких задач при использовании алгоритмических языков программирования.

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

Стандарт SQL определяется Американским национальным институтом стандартов (American National Standards Institute, ANSI) и в данное время также принимается Международной организацией по стандартизации (International Organization for Standardization, ISO). Названия этих стандартов состоят из аббревиатуры SQL и года, когда они были приняты. К настоящему времени известны следующие стандарты: SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011, SQL:2016, SQL:2019. При этом реализация SQL, используемой в конкретной версии СУБД, лишь отчасти соответствует тому или иному стандарту. Например, в ¹содержится описание соответствия версии SQL, используемой в СУБД Oracle 18, стандартам языка SQL.

Операторы SQL разделены на три группы:

• Операторы манипулирования данными (Data Manipulation Language, DML) – предназначены для выборки и изменения данных: SELECT, INSERT, UPDATE, MERGE, DELETE.

• Операторы определения данных (Data Definition Language, DDL) – предназначены для создания и модификации объектов базы данных. Основными операторами этой группы являются: CREATE, ALTER, DROP.

• Операторы управления данными (Data Control Language, DCL) – предназначены для предоставления пользователям прав на выполнение определенных действий с базой данных: GRANT, REVOKE.

Глава 2. Структура оператора SELECT и формирование условий выбора

Оператор SELECT

Оператор SELECT предназначен для выборки данных из таблиц, то есть он реализует одно из основных назначений базы данных – предоставлять пользователю информацию. Результатом выполнения оператора SELECT является таблица.

Согласно классической классификации оператор SELECT относится к операторам DML. Однако в Oracle версии SQL к операторам DML относят только INSERT, UPDATE, MERGE и DELETE, а оператор SELECT выделен в отдельную группу.

В общем виде структура оператора SELECT может быть представлена в следующем виде:

SELECT [ALL|DISTINCT] {список столбцов или выражений}

FROM {список таблиц}

[WHERE {условия выбора}]

[ORDER BY {столбцы сортировки [ASC|DESC]]}

[GROUP BY {столбцы группировки}]

[HAVING {условия на группу}];

(Квадратными скобками отмечены необязательные элементы.)

Дадим предварительное описание элементов данного оператора.

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

В предложении FROM указываются источники данных. В качестве таких источников можно использовать таблицы базы данных, а также таблицы, которые возвращают подзапросы или представления. В тех запросах, где используется несколько таблиц, необходимо обязательно указывать условия соединения. Если этого не сделать, то будет осуществляться декартово произведение таблиц.

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

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

В предложении ORDER BY указываются имена столбцов, по значению которых следует упорядочить результат выполнения запроса. По умолчанию строки упорядочиваются в порядке возрастания значений столбца. Для сортировки в порядке убывания после имени столбца следует указать параметр DESC. Если указать несколько столбцов, то результат будет упорядочиваться сначала по значению первого столбца; строки, имеющие одинаковые значения первого столбца, упорядочиваются по значению второго столбца, и так далее.

В предложении GROUP BY можно указать столбцы, по которым следует осуществить группировку. Группировка состоит в том, что несколько строк, имеющих совпадающие значение столбцов, по которым осуществляется группировка, объединяются в одну строку. Обычно группировка используется в запросах, использующих агрегатные функции, например: Sum (), Max ().

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

При изучении SQL следует обратить внимание на то, что для формирования запроса необходимо:

– определить структуру запроса, соответствующую заданной задаче обработки данных;

– синтаксически правильно записать запрос.

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

В своей простейшей форме оператор SELECT должен включать в себя следующее:

– предложение SELECT, где указываются имена столбцов, значение которых будет отображаться в результате выполнения запроса;

– предложение FROM, в котором указывается имя таблицы, содержащей данные.

SELECT {список столбцов}

FROM {таблица};

Пример 2.1. Вывод содержимого одного столбца

SELECT employee_id

FROM Employees;

Пример 2.2. Вывод содержимого нескольких столбцов

SELECT employee_id, first_name, last_name, department_id

FROM Employees;

Если в качестве результата выполнения запроса нужно вывести значения всех столбцов, то вместо списка столбцов указывается символ *.

Пример 2.3. Вывод значений всех столбцов

SELECT *

FROM Employees;

Исключение дублирования данных

Рассмотрим запрос, который выводит коды должностей сотрудников.

Пример 2.4. Вывод значений столбца job_id

SELECT job_id

FROM Employees;

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

Пример 2.5 Вывод значений столбца job_id без дублирования

SELECT DISTINCT job_id

FROM Employees;

Условия выбора

Для того чтобы выводить только те данные, которые удовлетворяют определенным условиям, оператор SELECT должен содержать предложение WHERE, которое содержит условное выражение.

SELECT {список столбцов}

FROM {таблица}

WHERE {условное выражение};

Условное выражение для каждой строки таблицы может принимать значения: ИСТИНА (TRUE), ЛОЖЬ (FALSE), НЕ ОПРЕДЕЛЕНО (UNKNOWN). Результат выполнения запроса будет содержать только те строки, для которых условное выражение будет иметь значение ИСТИНА (TRUE).

Пример 2.6. Вывод данных о сотрудниках, зарплата которых больше 5000

SELECT employee_id, first_name, last_name, salary, department_id

FROM Employees

WHERE salary> 5000;

Пример 2.7. Вывод данных о сотрудниках, принятых на работу 20.08.1997

SELECT employee_id, first_name, last_name, salary, department_id

FROM Employees

WHERE hire_date = ′ 20.08.1997 ′;

В процессе выполнения этого оператора осуществляется неявное преобразование строки ′ 20.08.1997 ′ в формат Date. Для того чтобы это преобразование произошло без ошибок, содержимое строки, содержащую дату, должно быть совместимо с настройками Oracle SQL Developer (рисунок 1.21).

Для указаний условий выбора могут быть использованы операторы сравнения: =,>, <и логические операторы: NOT, AND, OR. Логические операторы используются для формирования сложных условий выбора и имеют разный приоритет. Сначала выполняются все операторы NOT, потом операторы AND; операторы OR выполняются в последнюю очередь. Для исключения возможных ошибок при формировании сложных запросов следует использовать скобки. Выражения внутри скобок выполняются первыми, слева направо.

Рассмотрим примеры запросов, использующих логические операторы при формировании условий выбора.

Пример 2.8. Вывод данных о сотрудниках, которые работают в отделе 50 и занимают должность ST_MAN

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE (department_id = 50) AND (job_id= ′ ST_MAN ′);

Пример 2.9. Вывод данных о договорах, заключенных сотрудником 150 с клиентом 49, совершенных в определенную дату (27.09.2017)

SELECT * FROM Orders

WHERE (salesman_id = 150) AND (customer_id=49)

AND (order_date = ′ 27.09.2017 ′);

Использование скобок при формировании условий выбора может существенным образом изменять логику выполнения запроса.

Пример 2.10. Вывод данных о договорах сотрудника 155, заключенных 15.03.2018 или 02.11.2019

SELECT * FROM Orders

WHERE (salesman_id = 155) AND (order_date = ′ 15.03.2018 ′

OR order_date = ′ 02.11.2019 ′);

Если в предложении WHERE скобки поставить так, как это показано в примере 2.11, то запрос будет иметь совсем другой смысл.

Пример 2.11. Вывод данных о договорах сотрудника 155, заключенных 15.03.2018, или обо всех договорах, заключенных 02.11.219

SELECT * FROM Orders

WHERE (salesman_id = 155) AND (order_date = ′ 15.03.2018 ′)

OR (order_date = ′ 02.11.2019 ′);

Специальные операторы

Для формирования условий выбора можно использовать специальные операторы, представленные в таблице 2.1.

Таблица 2.1. Специальные операторы

Рассмотрим примеры запросов, использующих специальные операторы.

Оператор LIKE

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

– символ подчеркивания _ обозначает один символ;

– символ процента % обозначает несколько символов.

Синтаксис:

{имя столбца} LIKE ′ шаблон ′

Пример 2.12. Вывод данных о сотрудниках, имена которых начинаются на букву L

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE first_name LIKE ′ L% ′;

Пример 2.13. Вывод имен сотрудников, вторым символом которых является буква а

SELECT DISTINCT first_name

FROM Employees

WHERE first_name LIKE ′ _a% ′;

Пример 2.14. Вывод имен сотрудников, которые состоят из четырех символов, начинаются на букву J и заканчиваются буквой n

SELECT DISTINCT first_name

FROM Employees

WHERE first_name LIKE ′ J__n ′;

Для поиска в строке символов _ и % при построении шаблона используется опция ESCAPE /. Символ, который в шаблоне будет располагаться после /, будет рассматриваться как символ поиска. Вместо символа / можно использовать и другие символы, например!.

Пример 2.15. Вывести имя и адрес клиентов, столбец address которых содержит символ /

SELECT c_name, address

FROM Customers

WHERE address LIKE ′ %//% ′ ESCAPE ′ / ′;

Оператор BETWEEN

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

Синтаксис:

{имя столбца} BETWEEN V_MIN AND V_MAX

V_MIN – нижняя граница диапазона;

V_MAX – верхняя граница диапазона

Оператор BETWEEN осуществляет поиск среди всех значений диапазона, включая границы. Оператор BETWEEN эквивалентен двум операциям сравнения, объединенным логическим оператором AND.

({имя столбца}> = V_MIN) AND ({имя столбца} <= V_MAX)

Пример 2.16. Вывести данные о сотрудниках, зарплата которых находится в определенном диапазоне

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE salary BETWEEN 6000 AND 8000;

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

Пример 2.17. Получить данные о сотрудниках, у которых значение комиссионных находится в определенном диапазоне

SELECT employee_id, first_name, last_name, department_id,

salary, commission_pct

FROM Employees

WHERE commission_pct BETWEEN 0.15 AND 0.2;

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

SELECT * FROM Orders

WHERE order_date BETWEEN ′ 01.09.2019 ′

AND ′ 30.09.2019 ′;

Оператор BETWEEN можно использовать совместно с логическим оператором NOT.

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

SELECT * FROM Orders

WHERE order_date NOT BETWEEN ′ 01.09.2019 ′ AND ′ 30.09.2019 ′;

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

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

Пример 2.20. Получить данные о сотрудниках, имена которых начинаются с букв в диапазоне с A по B (содержит ошибку)

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE first_name BETWEEN ′ A ′ AND ′ B ′;

Но анализ результатов этого запроса показывает, что данные о сотрудниках, чьи имена начинаются на букву B, в результат выполнения запроса не попали, хотя такие сотрудники есть, например Bruce.

Это происходит потому, что значение строки B меньше значения строки Bruce, поэтому данные о сотрудниках, чьи имена начинаются на букву B, в результат выполнения запроса не попали. Эту проблему можно решить, указывая в качестве верхнего диапазона следующую букву.

Пример 2.21. Получить данные о сотрудниках, имена которых начинаются с букв в диапазоне с A по B

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE first_name BETWEEN ′ A ′ AND ′ C ′;

Оператор IN

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

Синтаксис:

{имя столбца} IN {список значений}

Список значений в операторе IN может формироваться в результате выполнения оператора SELECT (подзапроса).

Пример 2.22. Вывести данные о сотрудниках, которые работают в отделах с определенными номерами

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE department_id IN (40, 10, 110);

Пример 2.23. Вывести данные о договорах, заключенных в определенные даты

SELECT * FROM Orders

WHERE order_date IN (′ 07.09.19 ′, ′ 14.09.19 ′, ′ 02.11.19 ′);

Оператор IN можно использовать вместе с логическим оператором NOT. В этом случае результат запроса будет содержать строки, в которых значение проверяемого столбца не совпадает ни с одним из значений, указанных в списке.

Пример 2.24. Вывести данные о сотрудниках, которые не работают в отделах с определенными номерами

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE department_id NOT IN (40, 10, 110);

Условия выбора, формируемые оператором IN, можно объединять с другими условиями выбора.

Пример 2.25. Вывести названия городов, которые расположены в США или Канаде и почтовый индекс которых заканчивается цифрой 2

SELECT city FROM Locations

WHERE (country_id IN (′ US ′, ′ CA ′))

AND (postal_code LIKE ′ %2 ′);

Следует иметь в виду, что если список значений в IN будет содержать NULL, то результат выполнения оператора не будет содержать строк, у которых проверяемый столбец имеет значение NULL, так как результат сравнения NULL имеет значение НЕ ОПРЕДЕЛЕНО (UNKNOWN).

Пример 2.26. Вывести данные о сотрудниках, которые работают в отделах с определенными номерами, и о сотрудниках, у которых не задан номер отдела

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE department_id IN (40, 10, 110, NULL);

При этом в таблице Employees есть строки, у которых столбец department имеет значение NULL (см. результаты выполнения запроса из примера 2.29).

Если список значений в NOT IN будет содержать NULL, то результат выполнения оператора SELECT будет пуст. Это происходит, потому что оператор

X NOT IN (A1, A2, AN)

эквивалентен выражению

X <> A1 AND X <> A2 AND …X <> AN

Если одно из Ai будет NULL, то результат этого выражения будет иметь значение НЕ ОПРЕДЕЛЕНО (UNKNOWN).

Пример 2.27. Вывести данные о сотрудниках, которые не работают в отделах с определенными номерами

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,

DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID NOT IN (30,50,60,80,90,100,NULL);

Результат выполнения этого запроса не будет содержать строк.

Оператор IS NULL

Оператор IS NULL используется для определения строк с неопределенным значением заданного столбца.

Синтаксис:

{имя столбца} IS NULL

Данное выражение принимает значение TRUE, если значение проверяемого столба будет NULL.

Пример 2.28. Получить данные о сотрудниках, для которых неизвестен номер руководителя

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE manager_id IS NULL;

Пример 2.29. Вывести данные о сотрудниках, у которых не задан номер отдела

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE department_id IS NULL;

Можно также использовать разновидность данного оператора IS NOT NULL, который возвращает значение FALSE, если значение проверяемого столба будет NULL.

Пример 2.30. Получить данные о сотрудниках, для которых известен номер руководителя

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE manager_id IS NOT NULL;