Возможности команды SELECT

• Три операции доступные для команды SELECT это проекция, выборка и объединение
• Проекция – это ограничение столбцов, выбираемых из таблцы. Используя проекцию вы получаете только столбцы в которых вы заинтересованы, вместо всех возможных.
• Выборка используется во время вычитки строк из таблицы. Выборка включает наложение ограничений на выбираемые строки на основе критериев или условий. Это позволяет вам получать только те строки, которые вам необходимы
• Объединение включает в себя связь таблиц основываясь на общиъ аттрибутах. Объединение позволяет хранить данные в третьей нормальной форме в разных таблицах вместо одной большой таблицы.
• Команда DESCRIBE выводит список названий столбцов, их типов данных и ограничение обязательности

Базовая команда SELECT

• Директива SELECT определяет проекцию столбцов. Другими словами, SELECT определяет какие именно столбцы должны быть включены в результат
• Ключевое слово DISTINCT препятствует возникновению строк, содержащих дубликаты всех столбцов в возвращаемом результате
• Выражения и столбцы могут использовать псевдоним используя ключевое слово AS или пробел между столбцом или выражением и псевдонимом
• Одиночная кавычка в символьном литерале может быть экранирована путём добавления дополнительной одинарной кавычки или используя оператор quote.

Ограничение строк, получаемых запросом

• Одно или более условий образуют раздел WHERE. Эти условия определяют правила, которые должны выполнять данные чтобы быть выбраны в результат
• Для каждой строки, проверяемой в условии, есть операнды, которые находятся слева и справа от оператора. Операндом может быть значение столбца, литерал или выражение.
• Операторы сравнения могут проверять данные по-разному. Равенство или неравенство проверяются очень часто, но доступны также операторы проверки диапазона, вхождения в группу и проверка на основании шаблона.
• Булевыми операторами являются операторы AND, OR и NOT. Операторы AND и OR позволяют использовать несколько условий в разделе WHERE.
• Оператор NOT меняет смысл оператора сравнения на противоположный.

Сортировка строк, возвращаемых запросом

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

Подстановка переменной

• Подстановка переменной позволяет использовать написанный ранее запрос добавляя возможность заменить некоторые элементы во время выполнения. Один и тот же запрос может быть выполнен несколько раз с указанием разных параметров.
• Переменные сессии могут задаваться явно, используя команду DEFINE. Команда UNDEFINE позволяет удалить переменные сессии включая явно созданные командой DEFINE и неявно созданные (используя двойной амперсант).
• Команда VERIFY управляет выводом пользователю запроса до и после подстановки переменной

Вы разработали хороший запрос и возможно захотите использовать его в будущем. Иногда полезно иметь заготовку запроса, в которой указаны переменные, которые будут заменены на значения при выполнении запросы. Oracle предоставляет такой функционал в виде так называемой заменты переменной (ampersand substitution). Каждый элемент команды SELECT может быть подставлен во время выполнения, и путём оставления в запросе только ключевых элементов и ввода динамических переменных вы можете избежать много скучной и повторяемой работы. Мы рассмотрим подстановку переменной и ключевые слова DEFINE и VERIFY.

Замена переменных

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

Замена одиночным амперсандом

Самая простая и популярная форма SQL элемента это замена одинарного амперсанта. Символ амперсанта (&) выбран для назначения переменной в запросе и переменная состоит из амперсанта и названия переменной без пробела между ними. Когда запрос выполняется, серверный процесс Oracle видит переменную для замены и пытается определить её значение двумя способами. Во первых просматривается определена ли переменная в сессии пользователя. (Команду DEFINE мы рассмотрим чуть позже). Если переменная не определена, то пользовательский процесс запрашивает значение на которое будет заменена сооветствующая переменная. После того как значение введено, запрос выполняется сервером Oracle. Замена переменной амперсанта происходит в момент выполнения запроса и иногда называется связывание во время выполнения  (runtime binding) или подстановка во время выполнения (runtime substitution).

Вам может требоваться поиск значения телефона по заданной фамилии или номеру сотрудника. Такой запрос может быть написан следующим образом

select employee_id, last_name, phone_number from employees

where last_name = &LASTNAME or employee_id = &EMPNO;

Когда вы запустите запрос, Oracle спросит входное значения для переменной с именем LASTNAME. Вы можете ввести фамилию сотрудника, если вы её знаете, например ‘King’. Если вы не знаете фамилию но знаете номер, вы можете ввести любой значение и нажать OK для ввода значения. Затем Oracle запросит значения для переменной EMPNO. После ввода значения, например 0, и нажатия OK, не остаётся переменных для замены и выполняется следующий запрос

select employee_id, last_name, phone_number from employees

where last_name = ‘King’ or employee_id = 0;

Переменной можно назначить любой символьное значение с валидным именем. Значение-литерала на которое будет произведена замена должно быть соответственного типа данных, иначе вы получите ошибку “ORA-00904: invalid identifier”. Если переменная подразумевает символьное значение или значение данных, то литерал должен быть заключен в одинарные кавычки. Полезным способом избежать ошибки типа данных является обрамление переменной в кавычки при необходимости. Тогда пользователю нет необходимости знать о типе данных.

select employee_id, last_name, phone_number from employees

where last_name = ‘&LASTNAME’ or employee_id = &EMPNO;

Двойной амперсант

Когда переменная используется несколько раз в запросе, Oracle будет запрашивать значение каждый раз когда встречается переменная в запросе. Для сложных запросов, это может быть очень неэффективно и приводить к ошибкам. Следующий запрос выбирает FIRST_NAME и LAST_NAME из таблицы EMPLOYEES который содержит символы в обоих столбцах

select first_name, last_name from employees

where last_name like ‘%&SEARCH%’ and first_name like ‘%&SEARCH%’;

Два условия одинаковые но они проверяются для разных столбцов. Когда запрос будет выполняться, вначале Oracle потребует ввод значения для переменной SEARCH используемом в первом условии со столбцом LAST_NAME. Затем потребуется ввод данных для замены значения переменной SEARCH используемом при сравнении с FIRST_NAME. Это привносит две проблемы. Во первых это неэффективно вводить одно и тоже значение два раза, и во вторых, что более важно, можно допустить опечатку при повторном вводе, так как Oracle не проверяет идентичность ввода значения для переменной. В этом примере, допущено логическое предположение что значение переменных должно быть одинаковым, но тот факт что у переменной одинаковое имя не значит для Oracle что значение должно быть одинаковым. На первом примере на рисунке 9-7 отображён результат выполнения запроса и ввода двух разных значений для подстановки переменной. В этом примере результат неверный, так как исходно требование было таким, что фамилия и имя сотрудника должны содержать одинаковые литералы.

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

На втором примере на рисунке 9-7 показано как использовать переменную SEARCH с двумя амперсантами в условии для столбца LAST_NAME, а затем переменная с тем же именем и одним амперсантом не затребует ввода значения. Когда запрос будет выполняться, Oracle запросит значение для SEARCH только один раз установит значение переменной SEARCH для сессии введённое значение и будет использовать его дальше. Для того чтобы сбросить это значение вам необходимо будет выполнить команду UNDEFINE.

Рисунок 9-7 Использование двойного амперсанта

Подстановка названий столбцов

До этого мы обсуждали подстановку литералов в секции WHERE, но можно заменять любой элемент SQL запроса. В следующем примере столбцы FIRST_NAME и JOB_ID статическиу и будут возвращены в любом случае, но третий столбец это переменная для подстановки во время выполнения с именем COL. Результат также сортируется используя этот столбец-переменную указанную в директиве ORDER BY

select first_name, job_id, &&col

from employees

where job_id in (‘MK_MAN’,’SA_MAN’)

order by &col;

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

Подстановка выражений и текста

Практически все элементы SQL запроса могут быть заменены во время выполнения. Ограничение, установленное Oracle – только первое слово должно быть статичным. В случае команды SELECT, минимальной командой будет ключевое слово SELECT, а всё остальное может быть заменено во время выполнения как в следующем примере

select &rest_of_statement;

Когда команда будет выполняться, будет выведен запрос для ввода значения для переменной с именем REST_OF_STATEMENT, которая добавится к слову SELECT. Лучшими кандидатами для использования подстановки переменных являются запросы, которые выполняются много раз, и незначительно отличаются друг от друга.

Команды DEFINE и VERIFY

Двойной амперсант используется чтобы избежать ввода значения несколько раз для одной и тоже переменной которая используется несколько раз в запросе. Когда встречается двойной амперсант, переменная сохраняется как переменная сессии. Когда запрос выполняется, все остальные вхождения переменной автоматически заменяются, используя сохраненную переменную. Все последующие выполнения запроса в той же сессии автоматически определяют значения переменной из переменной сессии. Это не всегда то что требуется и также ограничивает функционал подстановки переменной. Как-бы там ни было, Oracle предоставляет механизм для очистки переменных сессии. Команда VERIFY это команда с клиентской стороны и она контролирует будут или нет выводиться пользователю заменённые элементы перед выполнением запроса, который использует переменные.

DEFINE и UNDEFINE

Переменные сессии создаются, когда они встречаются в запросах использующих двойной амперсант. Они существуют всё время жизни сессии или пока они явно неудалены. Сессия завершается, когда пользователь выходит из клиентской программы к примеру, SQL *Plus, или, когда пользовательский процесс принудительно завершается.

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

UNDEFINE variablename

Рассмотрим простой динамический запрос, который выбирает статические столбцы и столбцы-переменные из таблицы EMPLOYEES и сортирует результат на основании столбца-переменной

select last_name, &&COLNAME

from employees where department_id=30 order by &COLNAME;

Первый раз когда запрос будет выполняться, появится запрос на ввод значения для переменной COLNAME. Предположим вы ввели SALARY. Это значение подменяется и запрос выполняется. Все последующие выполнения этого запроса в той же сессии не будут запрашивать значения для переменной COLNAME, так как уже создалась переменная и её значение SALARY. Переменная может быть удалена командой UNDEFINE COLNAME. После того как переменная сессии удалена – следующее выполнение запроса заново запросит пользователя ввести значение для переменной COLNAME.

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

DEFINE;

DEFINE variable=value;

Как показано на рисунке 9-8, переменная с именем EMPNAME явно определяется со значением ‘King’. Команда DEFINE без параметров возвращает список переменных сессии явно заданных и исользованных в запросах с двойным амперсантом.

Рисунок 9-8 – Пример использования команд DEFINE и UNDEFINE

Затем выполняются два простых запроса в которых используется явно определённая переменная EMPNAME. Затем, переменная удаляется.

Поддержка переменных-сессии может быть включена или отключена, когда необходимо с помощью команды SET DEFINE ON|OFF. Команда SET это не команда SQL, это команда управления окружением SQL. Если вы указываете SET DEFINE OFF, клиентская программа (к примеру, SQL *Plus) не сохраняет переменные сессии, а считает, что амперсант — это обычный литерал. Таким образом команда SET DEFINE OFF|ON управляет доступна ли замена переменной для вашей сессии. Следующий запрос использует символ амперсанта как обычный литерал. Когда запрос будет выполняться, будет выведен запрос для ввода значения дла переменной SID.

select ‘Coda&Sid’ from dual;

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

SET DEFINE OFF;

select ‘Coda&Sid’ from dual;

SET DEFINE ON;

После выполнения запроса, команда SET DEFINE ON может быть использована для включения функционала подстановки переменной обратно. Если подстановка переменной выключена и амперсанд используется не как литерал, запрос вернёт ошибку.

Команда VERIFY

Существует два типа команд при работе с Oracle: SQL команды и команды управления окружением SQL. Команда SELECT это команда языка SQL, команда SET управляет окружением. Доступно много параметров команды SET, но для контроля подстановки переменной доступны всего две: DEFINE и VERIFY.

Команда VERIFY управляет выводом введённого значения на экран, чтобы вы могли убедиться (verify) что подстановка осуществлена правильно. Сообщение выводится на экран в формате старого запроса, за ним идёт новый запрос с замененным значением. Команда VERIFY включает или выключает вывод на экран используя параметр OFF или ON. Если проверка выключена, то во время выполнения запроса с использованием амперсанта у вас запрашивается ввод значения, переменная заменяется, запрос выполняется и отображается результат. Если проверка включена и выполняется тот же запрос, то после ввода значения, но перед выводом результата, Oracle отображает раздел, в котором находилась переменная как старое значение с добавлением номеров строк, а затем ниже отображается новое значение с уже замененным значением.

Результат может быть гораздо более полезным если добавить возможность упорядочивания или сортировки информации. Информация может сортироваться по алфавиту, по численному порядку, хронологическому порядку, в режиме по возрастанию или убыванию. Более того данные можно отсортировать используя комбинацию столбцов и можно использовать столбцы которые не возвращаются запросом. Сортировка результата обычно происходи после того как результат команды SELECT готов. Параметры сортировки не влияют на данные в результате, они влияют лишь на то как результат отобразится. Одни и теже строки могут быть получены как в результате запроса с сортировкой, так и в результате запроса без сортировки. Сортировка результата обеспечивается предикатом ORDER BY

Предикат ORDER BY

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

SELECT *|{[DISTINCT] column|expression [alias],…}

FROM table

[WHERE condition(s)]

[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];

Сортировка по возрастанию или убыванию

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

ORDER BT col(s)|expr

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

select last_name, hire_date, salary from employees

where job_id in (‘SA_REP’,’MK_MAN’)

order by last_name desc nulls last;

Выбранные данные можно сортировать используя любые столбцы таблицы в секции FROM, включая столбцы которые не перечислены в списке SELECT. Добавляя ключевое слово DESC к столбцу или выражению в секции ORDER BY строки сортируются по убыванию. Параметр NULLS  LAST указывает, что если в строках результата присутствуют значения NULL то строки со значением NULL будут отображены в конце результата, после всех других отсортированных значений. Для указания сортировки таким образом чтобы значения NULL были вначале – используется ключевое слово NULL FIRST. Также данные можно сортировать основываясь на выражениях, как показано в следующем примере

select last_name, salary, hire_date, sysdate-hire_date tenure

from employees order by tenure;

Наименьшее значение TENURE отображается первым в результате, так как ORDER BY указывает БД отсортировать результат используя псевдоним. Обратите внимание, что результат можно сортировать по выражению без псевдонима, но использование псевдонима делает запрос более читабельным.

По умолчанию используются следующие параметры ORDER BY.  Самым важным является то, что если явно не задать режим DESC  — предполагается сортировка по возрастанию. Если в результате есть значения NULL то по умолчанию используется NULLS LAST при сортировке ASC и NULLS FIRST при сортировке DESC. Если не указать директиву ORDER BY в запросе, то одинаковый запрос может вернуть одинаковые данные, но в разном порядке, таким образом нельзя строить предположения о порядке возвращаемых данных без указания порядка сортировки.

Сортировка по позиции

В Oracle доступен альтернативный,более короткий метод указания столбца или выражения для сортировки. Вместо указания имени столбца используется его позиция в списке SELECT. Рассмотрим пример

select last_name, hire_date, salary from employees order by 2;

В ORDER BY директиве указано число 2. Это эквивалентно указанию поля HIRE_DATE, так как второй столбец в списке SELECT является столбцом HIRE_DATE. Сортировка по позиции доступна только для столбцов которые указаны в списке столбцов команды SELECT.

Составная сортировка

Результат может быть отсортирован по нескольким столбцам используя составную сортировку. Несколько столбцов могут быть указаны (неважно по имени или по позиции) как составной ключ сортировки, путём перечисления их в секции ORDER BY через запятую. Для выборки JOB_ID, LAST_NAME, SALARY и HIRE_DATE из таблицы EMPLOYEES и сортировки данных по убыванию в зависимости от значения столбца JO_ID, затем по возрастанию по LAST_NAME и наконец по убыванию зарплаты можно выполнить запрос такого вида

select job_id, last_name, salary, hire_date from employees

where job_id in (‘SA_REP’,’MK_MAN’) order by job_id desc nulls last, last_name, 3 desc;

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

Предикат WHERE

Предикат WHERE расширяет команду SELECT предоставляя возможность ограничить строки результата наложением одного или нескольких условий. Запрос к таблице используя только предикаты SELECT и FROM приводит к тому, что все строки хранящиеся в таблице будут возвращены в результате выполнения запроса. Использование ключевого слова DISTINCT позволяет убрать дубликаты и результат выполнения запроса ограничивается в какой-то мере. Но что если нам нужна из таблицы только определённая информация, например, только те данные которые содержат определённое значение в конкретном столбец? Как нам получить данные только тех стран которые находятся в Европе из таблицы COUNTRIES? Или как получить только сотрудников работающих торговыми представителями? Все эти запросы можно выполнить используя предикат WHERE для указания какие конкретные строки мы хотим получить. Синтаксис команды SELECT с предикатом WHERE выглядит следующим образом

SELECT *|{[DISTINCT] column|expression [alias],…}

FROM table

[WHERE condition(s)];

Предикат WHERE всегда находится после предиката FROM. Квадратные скобки указывает на то что использование WHERE необязательно. Одно или несколько условий могут применяться для ограничения результата. Условия определяется оператором сравнения и двумя операндами. Операндами могут быть значения столбцов, литераты или выражения. Оператор равенства (equality) наиболее часто используемые оператор для ограничения результата. Пример использования предиката WHERE

select country_name

from countries

where region_id=3;

Этот пример проецирует столбец COUNTRY_NAME из таблицы COUNTRIES. Вместо выбора всех строк из таблицы предикат WHERE ограничивает результат только теми строками у которые значение столбца REGION_ID равно трём.

Условия для числовых данных

Условия должны быть сформулированы в зависимости от типа данных. Условия для численных значений можно устанавливать несколькими способами. Рассмотрим столбец SALARY в таблице EMPLOYEES. Тип данных столбца NUMBER(8,2). Ограничить строки по значению этого столбца можно следующим способом

select last_name, salary from employees where salary = 10000;

Этот запрос на фамилию и запрлату сотрудников кто зарабатывает больше 10000 выполнится успешно так как типы данных обоих операндов совпадают и совместимы. Столбец с численным типом данных также можно сравнить с другим численным столбцом той же строки в условии WHERE

select last_name, salary from employees where salary = department_id;

В этом примере условие WHERE никогда не выполнится так как значение зарплаты находится в диапазоне от 2100 до 99999.99 а значение номера отдела – от 10 до 110. Так как значения не пересекаются, значит строк удовлетворяющих условию нет и запрос возвращает пустой результат.

Условие WHERE также можно использовать для сравнения столбцов и выражений или сравнения выражения с выражением

select last_name, salary from employees where salary = department_id*100;

select last_name, salary from employees where salary/10 = department_id*10;

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

Условия для символьных данных

Условия для выборки строк основанные на символьных данных определяются с помощью заключения литералов в условии в одинарные кавычки. Столбец JOB_ID в таблице EMPLOYEES имеет тип данных VARCHAR(20). Предположим что вам нужен список фамилий тех сотрудников, которые работают сейчас в должности торгового представителя. Значение должности торгового представителя равно SA_REP. Следующий запрос можно использовать для этой цели

select last_name from employees where job_id=’SA_REP’;

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

Условие 1: where job_id=SA_REP

Условие 2: where job_id=’Sa_Rep’

Условие 3: where job_id=’sa_rep’

Условие один приведёт к возникновению ошибки ORA-00904: «SA_REP»: invalid identifier так как значение литерала не заключено в кавычки и воспринимается как название столбца. Условия два и три синтаксически валидны, но не эквивалентны. Более того, ни одно из условий не вернёт никаких данных, так как в таблице нет строки ни со значением Sa_Rep ни со значением sa_rep.

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

Условие 1: where ‘A ‘||last_name||first_name = ‘A King’

Условие 2: where first_name||’ ‘||last_name = last_name||»||first_name

Условие 3: where ‘SA_REP’||’King’ = job_id||last_name

Условие 4: where job_id||last_name =’SA_REP’||’King’

Условия над датами

Столбцы с типом данных DATE используются для хранения данных о дате и времени. Литералы даты должны заключаться в одинарные кавычки так же как символьные данные. Когда используется в условии WHERE столбец с типом данных даты – его можно сравнивать с другим столбцом даты, литералом или выражением. Литералы конвертируются к типу данных DATE используя формат по умолчанию DD-MON-RR. Если литерал встречается в выражении использующем столбец с типом данных даты, он автоматически конвертируется в дату используя формат по умолчанию. DD обозначает день, MON – первые три буквы месяца и RR – две последние цифры года (если RR – между 50 и 99 это значит что используется предыдущий век, иначе используется текущий век). Также можно указать все четыре цифры года. Рассмотрим следующие условия

Условие 1: where start_date = end_date;

Условие 2: where start_date = ’01-JAN-2001′;

Условие 3: where start_date = ’01-JAN-01′;

Условие 4: where start_date = ’01-JAN-99′;

Первое условия проверяет равенство двух столбцов с типом данных DATE. Результатом будут строки в которых значение START_DATE и END_DATE одинаковое. Важно помнить, что даты равны только тогда, когда равны всех их части, включая день, месяц, год, часы, минуты и секунды. В главе 10 мы обсудим тип данных дата подробнее. Пока что не обращаем внимание на часы, минуты и секунды. Во втором условии START_DATE сравнивается с литералом ’01-JAN-2001’. Указаны все четыре цифры года. Такое условие валидно. Третий запрос эквивалентен воторому, так как литерал ’01-JAN-01’ преобразуется в значение ’01-JAN-2001’. Так происходит потому что RR значение меньше 50 и используется текущий (21) век. В четвёртом условии значение 01-JAN-99 будет преобразовано в 1999 так как 99 входит в диапазон от 50 до 99 и используется предыдущий век.

Также поддерживаются арифметические действия над датами такие как добавление и вычитание. Выражение вида END_DATE – START_DAE вернёт число – количество дней между датой начала и датой завершения, а выражением STAR_DATE+30 вернёт дату которая будет через 30 дней после даты начала.

Exam tip

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

Операторы сравнения

Оператор равенства обычно используется для отображения концепции ограничения данных используя предикат WHERE. Но ещё доступны и некоторые другие операторы. Операторы неравенства такие как «меньше» или «больше или равно» могут использоваться для создания нестрогих равенств. Оператор BETWEEN используется для определения входит ли значение столбца в диапазон между двумя значениями. Оператор IN проверяет вхождение значения в заданный набор значений (равенство хотя бы одному значению из заданного набора). Оператор LIKE – это инструмент, позволяющий сравнивать символьные значения на основании специальных шаблонов. И последний оператор сравнения это оператор IS NULL, который позволяет проверять строки на значение NULL в столбце. Эти операторы можно комбинировать в секции WHERE.

Равенство и неравенство

Ограничение строк возвращаемых запросом требует создания подходящего условия WHERE. Если ограничения слишком строгие, то могут быть получены всего несколько или вообще ни одной строки. Если условия построено слишком размытым – то можно получить больше строк чем необходимо. Рассмотрев разные операторы вы сможете строить гибкие условия для получения именно тех строк что вам необходимы. Как работает проверка на равенство обычно интуитивно понятно. Такие условия создаются используя оператор равенства (=). Рассмотрим запрос

select last_name, salary from employees where job_id=’SA_REP’;

Столбец JOB_ID каждой строки таблицы EMPLOYEE проверяется на равенство символьному литералу SA_REP. Для символьной инфомрации, понятие равенства означает что они должны быть посимвольно идентичны учитывая регистр. Когда находится такое равенство, значения необходимых столбцов из этой строки помещаются в результат запроса. Обратите внимание на то, что хотя столбец JOB_ID используется в условии – нет никакой обязательности проецировать этот столбец в результат запроса.

Условия неравенства расширяют возможности предиката WHERE. Проверку на вхождение в диапазон значений или в набор предопределённых значений можно сделать используя операторы равенства и неравенства, но обычно предпочтительнее использовать операторы BETWEEN и LIKE. Операторы неравенства описаны в таблице 9-3.

Операторы неравенства позволяют выполнять запросы для диапазонов. У вас может возникнуть задача вернуть строки где значение столбца больше чем другое значение. Следующий пример выбирает фамилию и зарплату сотрудников у которых зарплата больше 5000:

select last_name, salary from employees where salary > 5000;

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

Условие 1: where salary <= 3000;

Условие 2: where salary <> department_id;

В первом условии выбираются строки в которых значение зарплаты меньше или равно 3000. В условии номер два показана одна из форма как получить оператор «не равно». При использовании этого условия будут отобраны строки в которых значение зарплаты не равно номеру департамента.

Неравество чисел интуитивно понятно. Сравнение строк и дат не так очевидно. Строки сравниваются следующим образом: оба операнда преобразуются в числовые значения. На основании кодировки и настроек NLS, каждому символу назначается числовое значение и значения суммируются. Именно эти значения в итоге сравниваются. Рассмотрим запрос

select last_name from employees where last_name<‘King’

Строковый-литера Kink преобразуется в число. Предположим что используется US7 ASCII кодировка с настройками локали NLS = AMERICAN, тогда значение литерала будет равно K + i + n + g = (75+105+110+103=393). Для каждой строки из таблицы EMPLOYEES значение поля LAST_NAME преобразуется в численное значение. Если полученное значение меньше 393 – то строка будет возвращена в результате запроса.

Сравнение на неравенство со значениями даты работает примерно как сравнение строковых значений. Oracle хранит значения типа данных дата во встроенном числовом формате, и эти значения используется при сравнении. Рассмотрим пример

select last_name from employees where hire_date < ’01-JAN-2010′

Этот запрос вернёт фамилии сотрудников значение HIRE_DATE которых ранее первого января 2010 года.

Сравнение диапазона. Оператор BETWEEN

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

Предположим что вам нужны фамилии сотрудников, зарплата у которых находится между значениями 3400 и 4000. Используя оператор BETWEEN запрос будет выглядеть так

select last_name from employees where salary between 3400 and 4000;

Условие в операторе BETWEEN можео переписать используя два оператора неравенства

select last_name from employees where salary >=3400 and salary <=4000;

Но быстрее и проще использовать оператор BETWEEN

Проверка вхождения в группу. Оператор IN

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

select last_name from employees where salary in (1000,4000,6000);

Значение SALARY у каждой строки сравнивается со всеми значениями указанными в скобках. Если значение равно 1000, 4000 или 6000 то строка будет включена в результат. Следующие запросы используют IN для символьных данных и данных типа дата

select last_name from employees where last_name in (‘Watson’,’Garbharran’,’Ramklass’);

select last_name from employees where hire_date in (’01-JAN-2008′,’01-DEC-2009′);

Сравнение с шаблоном. Оператор LIKE

Оператор LIKE создан для символьных данных и он предоставляет гибкий механизм для поиска символов или строк. LIKE использует два специальных символа для составления шаблона: символ процента (%) и нижнее подчеркивание (_). Знак процента используется для указания нуля или более любых символов, а нижнее подчеркивание заменяет один любой символ. Вы можете использовать следующий пример для поиска сотрудников чьё имя начинается с буквы A

select first_name from employees where first_name like ‘A%’;

Значение столбца FIRST_NAME сравнивается со значением в кавычках как со значением литерала. В дополнении к этому символ процента вместе с оператором LIKE рассматриваются как «значение начинается с символа A,  и затем может быть ноль или более любых символов». Специальные символы могут использоваться в любой части шаблона: в начале, в середине или в конце. Также можно использовать просто специальный символ

where first_name like ‘%’

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

where last_name like ‘King’;

where last_name = ‘King’;

Символ нижнего подчеркивания означает «обязательно один любой символ». Для поиска сотрудников, чья фамилия состоит из четырёх символов, начинается на K, имеет любой второй символ и заканчивается на ng можно выполнить следующий запрос

select last_name from employees where last_name like ‘K_ng’;

на рисунке 9-6 показано что оба специальных символа могут использоваться отдельно, вместе, или даже несколько раз в одном шаблоне в одном условии. Первый запрос выбирает записи где COUNTRY_NAME начинается с символа I, после которого может быть один или больше символов, один из которых обязательно a. Второй запрос ищет страны, в имени которых пятым символом должен быть символ i. Длина после пятого символа не важна, и первыми четырьмя символами могут быть любые символы.

А что если вам нужно найти литерал, который содержит специальный символ? Специальные символы как данные можно экранировать используя идентификатор ESCAPE. В следующем примере выполняется поиск JOB_ID первыми тремя символами которых должны быть “SA_”

select job_id from jobs where job_id like ‘SA\_%’ escape ‘\’;

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

select job_id from jobs where job_id like ‘SA$_%’ escape ‘$’;

Знак процента обрабатывается точно также.

Сравнение значения NULL с помощью оператора IS NULL

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

Рассмотрим запрос которые выбирает фамилии сотрудников, у которых значение COMMISION_PCT – NULL

select last_name from employees where commission_pct is null;

Булевы операторы

Булевы или логические операторы позволяют использовать несколько условий в предикате WHERE команды SELECT. Это позволяет более гибко извлекать необходимые данные из БД. К примеру нам могут быть интерестны сотрудники фамилия которых начинается на J и значение COMMISSION_PCT которых больше чем десят процентов. То есть первое условие, это ограничить фамилию сотрудников использяу шаблон “J%”. И второе условие, это проверить значение COMMISION_PCT и выбрать только те записи где значение больше 10. Эти два условия можно объединить, используя логическое «И», и применить последовательно в секции WHERE. Результат выполняющий все или некоторые, или не выполняющий определённые условия может быть получен путём использования операторов AND, OR и NOT соответственно.

Оператор AND

Оператор AND (логическое «И») объединяет условия в одно условие и только те данные которые выполняют все условия будут включаться в результат. Если два условия указаны в WHERE и используется оператор AND, то все строки будут проверять последовательно на выполнение обоих условий. Если данные не удовлетворяют условиям или удовлетворяют только одно условие – то данные не будут влючены в результат. Сотрудники, у которых имя начинается на J и комиссия больше 10 процентов могут быть получены следующим запросом

select first_name, last_name, commission_pct, hire_date from employees where first_name like ‘J%’ and commission_pct > 0.1

Обратите внимание что у нас два условия, но зарезервированное слово WHERE используется только один раз. Оператор AND объединяет два условия. Для добавления дополнительных обязательных условий, просто добавьте их, и убедитесь, что они разделены оператором AND. Вы можете определять любое количество условий. Помните, что чем больше вы условий добавляете, тем более строгой будет выборка и тем менее значений будет возвращено.

Оператор OR

Оператор OR  (логическое ИЛИ) разделяет два условия таким образом, что для попадания в результат данные должны удовлетворять минимум одному условию. Если у вас в секции WHERE указаны два условия, разделённые оператором OR то если выполняется либо одно, либо оба условия строка будет возвращена. Если не удовлетворяется ни одно из условий – строка исключается из выдачи. Запрос для поиска сотрудников с фамилией, начинающейся с символа “B” или с комиссией больше 35% выглядит так

select first_name, last_name, commission_pct, hire_date from employees where first_name like ‘B%’ or commission_pct > 0.35;

Условия разделены оператором OR. Все сотрудники фамилия которых начинается с “B” буду в результате запроса, несмотря на их значение комиссии, даже если значение COMISSION_PCT это NULL. Плюс все строки в которых значение комиссии больше 35 % будут в результате (вне зависимости от первого символа их фамилии). Дополнительные условия можно добавлять используя оператор OR. Чем больше условий OR вы добавляете тем менее строгой становится выборка и тем больше строк может быть возрващено результатом.

Оператор NOT

Оператор NOT (логическое отрицание) отменяет операторы условия. То есть данные должны удовлетворять логически обратному условию для попадания в результат. Булевы операторы тогда записываются как показано в таблице 9-4

Оператор NOT меняет сравнение вне зависимости от оператора сравнения. Будь то оператор равенства, неравенства, вхождения в диапазон или группу и т.п.

Правила приоритета

Арифметические операторы, символьные операторы, операторы сравнения и логические выражения рассматривались в контексте предиката WHERE. Но как эти операторы работают друг с другом? Иерархия приоритетов показана в таблице 9-5. Операторы одинакового уровня приоритета выполняются слева направо если они встречаются вместе в выражении. Когда оператор NOT изменяет оператор LIKE, IS NULL или IN, то приоритет оператора остаётся таким же как и без NOT.

Рассмотрим запрос в котором используются разные операторы

select

last_name,salary,department_id,job_id,commission_pct

from

employees

where

(last_name like ‘%a%’ and salary > department_id * 200

or

job_id in (‘MK_REP’,’MK_MAN’)) and commission_pct is not null;

Столбцы LAST_NAME, SALARY, DEPARTMENT_ID, JOB_ID и COMISSION_PCT проецируются из таблицы EMPLOYEES основываясь на двух условиях. Вначале проверяется условие что в фамилии есть символ “a” и сравнивается значение с номером департамента умноженным на двести. Умножение выполняется перед сравнением, так как приоритет умножения выше чем сравнения на неравенство. Затем выбираются строки где значение JOB_ID или MK_MAN или MK_REP и значение COMISSION_PCT не NULL. Для строки, чтобы быть включенной в результат этого запроса достаточно удовлетворять либо первому либо второму условию. Изменение порядка операторов меняет смысл запроса. Рассмотрим пример

select

last_name,salary,department_id,job_id,commission_pct

from

employees

where

last_name like ‘%a%’ and salary > department_id * 100 and commission_pct is not null

or

job_id = ‘MK_MAN’;

В этом запросе два составных условия. Первое условие проверяет фамилию на наличие символа “a” плюс значение зарплаты должно быть больше чем номер департамента умноженный на сто и значение комисси должно быть указано. Второе условие требует чтобы значение JOB_ID было MK_MAN. Строка включается в результат если выполняются либо оба, либо хотя бы одно условие.

Exam tip

Булевы операторы OR и AND позволяют использовать несколько условий в предикате WHERE в то время как оператор NOT меняет оператор сравнения и может быть использован несколько раз в одном условии. Операторы равенства, неравенства, BETWEEN, IN и LIKE сравнивают два операнда в одном условии. Только один оператор сравнения используется в одном условии.

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

Синтаксис простой команды SELECT

Правила которые следует соблюдать

Выражения и операторы

NULL – это ничего

Синтаксис простой команды SELECT

В самой примитивной форме команда SELECT поддерживает проекцию столбцов и использование арифметических, строковых выражений, а также работу с датами. Также доступна возможность исключения дубликатов из результирующего набора. Базовый синтаксис команды SELECT

SELECT * | {[DISTINCT] column|expression [alias],…}

FROM table;

Ключевое слово (или зарезервированное) команды SELECT написано в верхнем регистре. Когда пишете команду, регистр ключевых слов не важен, но ключенвые слова нельзя использовать как название столбцов или имени другого объекта базы данных. SELECT, DISTINCT и FROM это ключевые слова. Команда SELECT обычно содержит два или более параметров. Два обязательных параметра это сама команда SELECT и слово FROM. Символ | используется как лоическое ИЛИ. То есть вы можете прочитать предыдущую команду как

SELECT * FROM table

В таком формате символ * используется для обозначения всех столбцов. SELECT * это простой способ попросить Oracle вернуть все доступные столбцы. Такой формат используется как быстрый способ вместо набора SELECT column1, column2, … columnN для выбора всех столбцов. Параметр FROM указывает какие таблицы использовать для выбора столбцов требуемых в команде SELECT. Вы можете выполнить следующий запрос для получения всех столбцов всех строк из таблицы REGIONS в схеме HR

select * from regions;

Когда выполнится это команда, результатом будет набор всех строк со всеми столбцами принадлежащие этой таблице. Использование звёздочки в команде SELECT иногда называют «слепым» запросом так как столбцы которые будут возвращены не указаны.

Вторая форма запроса SELECT использует такую же часть FROM но другой формат SELECT

SELECT {[DISTINCT] column|expression [alias],…} FROM table;

Такой запрос может быть разбит на два формата

SELECT column1 (possibly other columns or expressions) [alias optional]

или

SELECT DISTINCT column1 (possibly other columns or expressions) [alias optional]

Алиас это альтернативное имя столбца или выражения.  Алиасы обычно используются для отображения результата в понятном пользователю виде. Они также используются для сокращения количество символов для набора когда ссылаешься на столбец или выражение.  Перечисляя столбцы в команде SELECT вы фактически проецируете конкретную выборку результата которую вы хотите получить. Следующий запрос вернёт только столбец REGION_NAME таблицы REGIONS

select region_name from regions;

Вас могут попросить предоставить все должности сотрудников организации за всё время. Для этого вы можете выполнить команду SELECT * FROM JOB_HISTORY. Но такой запрос вернёт вам также EMPLOYEE_ID, START_DATE и END_DATE столбцы. Запрос который возвращает только нужные столбцы JOB_ID и DEPARTMENT_ID можно написать так

select job_id, department_id from job_history;

Использование ключевого слова DISTINCT позволит убрать дубликаты из результата. В некоторых случаях уникальность строк необходима. Важно понимать что критерий определения уникальности для Oracle находится после слова DISTINCT. Выборка уникальных значений job_ib из таблицы job_history с помощью следующего запроса вернёт восемь уникальных должностей

select distinct job_id from job_history

Важно понимать что уникальность определяется комбинацией столбцов после слова DISTINCT.

select distinct department_id from job_history;

select distinct job_id,department_id from job_history;

Правила которые следует соблюдать

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

Верхний или нижний регистр

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

Есть только один нюанс в использовании разного регистра. Когда вы работаете со значениями литералов – регистр имеет значение. Раасмотрим столбец JOB_ID таблицы JOB_HISTORY. Этот столбец хранит строки данных в верхнем регистре: SA_REP, ST_CLERK и т.д. Когда вы пишете запрос который будет ограничиваться значениями литералов – регистр важен. Oracle рассматривает запрос к таблице JOB_HISTORY с условием St_Clerk по другому чем запрос с условием ST_CLERK.

Метаданные об объектах БД хранятся по умолчанию в верхнем регистре в словаре данных. Если вы посмотрите данные о таблицах в схеме HR – все имена будут в верхнем регистре. Это не значит что таблица не может быть создана с именем в нижнем регистре – это можно делать. Это всего лишь общее поведение по умолчанию.

Exam tip

SQL запросы могут быть написаны с использованием любого регистра. Нужно следить за регистром когда вы работаете со значениями-литералами и алиасами. Использование в запросе JOB_ID или job_id в названии столбца вернёт одинаковый результат, но запрос на поиск значения PRESIDENT или President вернёт разный результат.

Символ конца запроса

Обычно используется “;” как символ конца запроса. SQL *Plus всегда требует символа конца запроса и обычно это “;”. Запросы или группы запросов часто сохраняются как файлы скрипты для будущего использования. Запросы в скриптах обычно пишутся в нескольких строках разделяемых символом перевода строки и после завершения одной команды используется “/” или  “;”. Вы можете написать команду SELECT разбить её на строки символом переноса строки, а затем после последней строки добавить новую строку в которой будет “/” и сохранить это как файл. Затем файл можно вызвать из SQL *Plus. SQL Developer не требует символа конца строки если создаётся только один запрос. Считается хорошим тоном всегда завершать ваши запросы символом конца строки. Рассмотрим два запроса в SQL *Plus:

select country_name, country_id, location_id from countries;

select

city,

location_id,

state_province,

country_id

from

locations

;

Первый пример показывает два важных правила. Первое – запрос заканчивается “;”. Второе – весь запрос написан в одну строку. Допустимо использовать и первый и второй варинты запроса, главное чтобы все слова запроса были до символа конца запроса.

Отступы, читаемость и good practices

Рассмотрим запрос

select

city,

location_id,

state_province,

country_id

from

locations

/

Этот запрос показывает преимущества разделения запроса на строки для улучшения читаемости кода. Oracle всё равно как написан запрос, в одну строку или в несколько, какие отступы использованы и так далее. Хорошим тоном считается разделение блоков запроса на разные строки. Когда выражения в блоке SELECT или WHERE достаточно сложные, то разбиение этих блоков на разные строки существенно улучшает читаемость запроса. Когда вы пишете запрос – обычно этот процесс итеративный. Интерпретатор SQL гораздо более полезен если вы разрабатываете сложные выражения разбивая их на строки, так как ошибки интерпретатора выглядят как “Error at line X:”. Процесс отладки станет гораздо проще.

Выражения и операторы

В синтаксисе простого запроса SELECT мы видели что можно использовать столбцы или выражения. Выражения – это обычно результат какой-либо операции над значениями одного (или нескольких) столбцов или выражений. Операторы которые могут использоваться в выражениях зависят от типа данных операндов. Для численных значений доступны сложение, вычитание, умножение и деление. Для строк доступно сложения (конкатенация). Сложение и вычитание доступно для типов данных даты и времени. Как и в обычной арифметике существует предопределённый порядок выполнения операторов (operator precedence) если в выражении используется больше чем один оператор. Круглые кавычки имеют самый высокий приоритет. Деление и умножение следующие в иерархии и рассчитываются до выполнения операторов сложения и вычитания, которые имеют наименьший приоритет.

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

Арифметические операторы

Рассмотрим таблицу JOB_HISTORY где хранится дата начала и конца назначения должности сотруднику. Для расчёта налогов или пенсии может возникнуть потребность расчёта как долго сотрудник занимал определённую должность. Эту информацию можно получить используя арифметический оператор. Рассмотрим некоторые элементы запроса и результата выполнения этого запроса показанные на рисунке 9-5.

Рисунок 9-5 – Запрос с использованием арфметического оператора

В блоке SELECT перечислены пять элементов. Четыре из них это обычные столбцы таблицы JOB_HISTORY, когда пятый используя значения исходных столбцов рассчитывает сколько дней провел сотрудник в конкретной должности. Рассмотрим сотрудника с номером 176, 9 строку результата. Этот сотрудник работал как менеджер по продажам с 1 Января 2007 года по 31 Декабря 2007 года. То есть сотрудник работал в этой должности ровно один год, 2007, который состоял из 365 дней.

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

Для улучшения читаемости, подвыражение (операция вычитания над датами) end_date-start_date выделено кавычками. Добавление единицы заставляет результат учитывать последний день.

Tip

Во время работы с SQL вы можете часто встречать две распространённые ошибки «ORA-00923: FROM keyword not found where expected» и «ORA-00942: table or view does not exist». Обычно они указаывают на ошибку в синтаксисе или пунктуации, такие как пропущенная круглая кавычка или забытый символ конца литерала при работе со строками.

Выражения и псевдонимы столбцов

На рисунке 9-5 показан новый принцип называющийся псевдонимом столбца. Обратите внимание что столбец с результатом выражения в результате выполнения запроса озаглавлен понятным названием “Days Employed”. Этот заголовок – это и есть псевдоним. Псевдоним это альтернативное имя для столбца или выражения. Если в этом выражении не использовать псевдоним, заголовок столбца будет (END_DATE-START_DATE)+1, что не очень понятно. Псевдонимы особенно полезны при работе с выражениями или суммированием и могут реализовываться несколькими способами.  Есть несколько правил при работе с псевдонимами в команде SELECT. Псевдоним “Days Employed” на рисунке 9-5 был указан путём добавления пробела после выражения и заключен в двойные кавычки. Эти кавычки обязательны по двум причинам. Во-первых, псевдоним сотоит из нескольких слов. Во-вторых, сохранение регистра псевдонима возможно только при его заключении в двойные кавычки. Если вы укажете псевдоним из двух слов разделённых пробелом без кавычек – вы получите ошибку «ORA-00923: FROM keyword not found where expected». SQL предлагает более формальный метод указания псевдонимов путём добавления ключевого слова AS между столбцом или выражением и его псевдонимом.

SELECT

EMPLOYEE_ID AS «Employee ID»,

JOB_ID AS «Occupation»,

START_DATE, END_DATE,

(END_DATE-START_DATE)+1 «Days Employed»

FROM JOB_HISTORY;

Оператор для работы со строками

Символ || представляет оператор конкатенации строк. Этот оператор используется для объединения строк или выражений вместе для создания более длинного выражения. Столбцы таблицы можно объединять между собой или с значением строкового-литерала для создания одного результирующего выражения.

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

SELECT ‘THE ‘||REGION_NAME||’ region is on Planet Earth’ «Planetary Location» FROM REGIONS;

В этом запросе строковый-литерал “The” конкатенируется со значением столбца REGION_NAME. Затем полученная строка также конкатенируется со строковым литералом “region is on Planet Earth” и всему выражению назначается псевдоним “Planetary location”.

Литералы и таблица DUAL

Литералы часто используются в выражениях и ссылаются на данные которые не принадлежат объектам базы данных. Конечно конкатенация существующих столбцов тоже используется, но что делать с обработкой литералов которые не зависят от данных в столбцах таблицы. Чтобы обеспечивать согласованность, Oracle придумал решение проблемы использования базы данных для вычисления выражений которым не нужны данные объектов БД. Для того чтобы база данных рассчитала выражение, должна быть выполнена синтаксически корректная команда SELECT. Что делать если вы хотите узнать сумму двух чисел? Вы можете использовать специальную таблицу с одной строкой и одним столбцом с названием DUAL.

Вызов таблицы DUAL показан на рисунке 9-1. В таблице доступен один столбец с названием DUMMY и типом данных строка. Вы можете выполнить запрос SELECT * FROM DUAL и вам вернётся строка с одним столбцом со значением “X”. Тестирование сложных выражений в процессе разработки выполняя запросы к таблице DUAL – это эффективный метод проверки того что вычисление выражений работает как задумано. Выражения с литералами можно проверять на любой таблице, но помните что выражения будут обрабатываться для всех строк в таблице, а в таблице DUAL всего одна строка.

select ‘literal ‘||’processing using the REGIONS table’ from regions;

select ‘literal ‘||’processing using the DUAL table’ from dual;

Первый запрос вернёт вам четыре строки, так как в таблице REGIONS четыре строки, а второй запрос вернёт одну строку.

Кавычки

Строковые литералы в нашим примерах были простыми выражениями иногда добавляемыми к столбцам. Такие строковые литералы заключаются в одинарные кавычки. Например

select ‘I am a character literal string’ from dual;

А что если литерал уже содержит символ кавычки? Рассмотрим пример

select ‘Plural’s have one quote too many’ from dual;

Этот запрос вернёт ошибку ORA-00923. Так как обрабатывать такие строки? Для этого доступно два способа. Первый и самый популярный это добавление дополнительной одинарной кавычки для каждого встречаемого значения кавычки в выражении. Предыдущий пример с использованием этого способа будет выглядеть так

select ‘Plural»s have one quote too many’ from dual;

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

Рассмотрим оператор q. Он позволяет установить символом начала и конца строкового литерала либо одинарный символ или один из четырёх видо скобок (), {}, [], <>. Рассмотрим примеры использования оператора q

SELECT q'<Plural’s can also be specified with alternate quote operators>’ «q<>» FROM DUAL;

SELECT q'[Even square brackets’ [] can be used for Plural’s]’ «q[]» FROM DUAL;

SELECT q’XWhat about UPPER CASE X for Plural’sX’ «qX» FROM DUAL;

Синтаксис оператора q

q‘delimiter character literal which may include single quotes delimiter’

где delimiter может быть символ или соотвествующая скобка. В примерах выше показано использование скобок (в первом и втором запросе), и использование символа “X” как символа начала и конца строкового литерала. Обратите внимание что символ  X тоже может использоваться в значении литерала – конец строки обозначается символом “X” и одинарной кавычкой.

NULL – это ничего

NULL обозначает отсутствие данных. Строка в которой содержится NULL в столбце – рассматривается как строка у которой нет данного для этого столбца. Формально NULL обозначает значение, которое неизвестно или непреминимо. Если не обрабатывать специальным образом значения NULL то ваши запросы скорее всего не выполнятся или что ещё хуже, вернёт неправильный результат. В этом разделе мы рассматриваем как обрабатывать значение NULL в столбце и как значение NULL влияет на вычисление выражений.

Обязательные и необязательные столбцы

Таблица хранят строки данные которые разбиты на один или несколько столбцов. У этих столбцов есть названия и тип данных. Некоторые из столбцов имею ограничения на обязательность указания значения. Таким образом любая строка дожна хранить какое либо значение отличное от NULL в этих столбцах.  Когда столбец таблицы не указан как обязательный то вы рискуете встретить значение NULL для этого столбца.

Tip

Любая арифметическая операция со значением NULL всегда возвращает NULL

Oracle позволяет работать со значением NULL используя специальные функции. Подробнее об этом мы поговорим в главе 10. Деление на NULL вернёт NULL, а деление на 0 приведёт к ошибке. Когда строка объединяется со значением NULL – то значение NULL игнорируется.

SELECT 1+NULL FROM DUAL;

SELECT ‘1’||NULL FROM DUAL;

Внешние ключи и необязательные столбцы

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

Первичный ключ таблицы DEPARTAMENTS – это столбец DEPARTMENT_ID. В таблице EMPLOYEES столбец DEPARTMENT_ID хранит ограничение внешнего ключа для связи с таблицей DEPARTAMENTS. Это значит что в таблице EMPLOYEES не может находиться запись с таким значением столбца DEPARTMENT_ID  которого нет в таблице DEPARTMENTS. Такое ограничение обусловлено выполнением третьей нормальной формы и критически важно для ограничения целостности базы данных.

А что насчёт значений NULL? Может быть значение NULL в столбце DEPARTAMENT_ID таблицы DEPARTAMENTS? Ответ – нет. Значение первичного ключа всегда должно быть указано. А значение в таблице EMPLOYEES? Это спорный вопрос, так как для обеспечения гибкости и покрытия всех возможных сценаривев, Oracle не может настаивать на том чтобы столбцы используемые для обеспечения ссылочной целостности были обязательными.

Поэтому поле DEPARTMENT_ID в таблице EMPLOYEES необязательное и существует вероятность что в таблице будут существовать записи со значением NULL в столбце DEPARTMENT_ID. И такие записи существуют. Модель данных позвоняет сотруднику работать в каком-то отделе или не работать ни в одном отделе. Когда происходит операция объединения то абсолютно возможно что какие-то записи которые содержат NULL в значении ключа будут отсутствовать в результате. В главе 12 мы рассмотрим как работать с объединениями.

Мы будем использовать для примеров две схемы. Встроенную схему HR, которая представляет из себя пример приложения для управления человеческими ресурсами и схема WEBSTORE – пример приложения для обработки заказов. Схема HR может быть создана при создании базы данных либо быть создана позднее выполнив скрипты из Oracle Home.

Схемы HR и WEBSTORE

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

Два из отношений на рисунке 9-3 могут быть сразу не совсем понятны. Первое, это отношение один-ко-многим таблицы EMPLOYEE к таблице EMPLOYEE. Это self-referencing внешний ключ. Это значит что много сотрудников может быть связано с одним сотрудником, и это основано на том факте что у многих сотрудников может быть один менеджер, но менеджер это также сотрудник. Эта связь реализована за счёт столбца внешнего ключа manager_id и первичного ключа employee_id.

Вторая связь которая возможно требует дополнительного пояснения это двунаправленная  связь между DEPARTMENTS  и EMPLOYEES. Многие сотрудники могут работать в одном департаменте, и один сотрудник может быть менеджером многих департаментов.

В таблице 9-1 отображены столбцы таблиц схемы HR, используя описанную ранее нотацию для отображения первичных ключей (#), внешних ключей (\), обязательности (*) или необязательности (o) значения.

Таблицы в схеме

REGIONS содержит строки для георафических областей

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

LOCATIONS содержат адреса, которые могут принадлежать стране

DEPARTAMENTS хранит информацию об отделах, с необязательным адресом и необязательным полем менеджера (менеджер должен существовать как сотрудник)

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

JOBS содержит информацию о должностях в компании. Много сотрудников могут иметь одинаковую должность

JOB_HISTORY хранит информацию о предыдущих должностях сотрудника, уникально определяемую полями employee_id и start_date; сотруднику не может быть назначено несколько должностей в один момент времени. Каждая запись в истории ссылается на сотрудника с одной должностью в определённый момент и возможной принадлежности какому-либо отделу. Мы будем использовать HR схему для примеров и упражнений, так что она должна быть создана.

Схема WEBSTORE уже была создана если вы выполняли лабораторные работы. Если нет, мы определим отношения и связи и создадим схему и необходимые объекты. Схема WEBSTORE состоит из четырёх таблиц, связанных первичными и внешними ключами. На рисунке 9-4 покаданы связи между таблицами, как диаграмма сущность-связь.

Магазин управляет продуктами, покупателями и заказами в соответсвенно названных таблицах. Каждый заказ может состоять из нескольких продуктов с разным количеством – эти данные хранятся в таблице ORDER_ITEMS. Столбец order_item_id хранит номер для каждого уникального продукта в каждом заказе. Каждый заказ связан с одной или несколькими строками таблицы ORDER_ITEMS .

Таблицы схемы WEBSTORE

PRODUCTS хранит информацию о продуктах: описание, цена, статус и доступное количество.

CUSTOMERS содержит информацию о покупателях

ORDERS хранит информацию о заказах. Один покупатель может совершить много заказов. Заказ не может быть создан без существующего покупателя.

ORDER_ITEMS хранит детальную информацию какие продукты были заказаны в каком заказе.

Создание демонстрационных схем

Если вы создавали БД используя лабораторное пособие то схема HR уже создана. Доступна опция создания демонстрационной схемы при создании базы с помощью DBCA.

Если схема не была создана в момент создания БД вы можете создать её выполнив скрипт установленный в домашнюю директорию БД. Этот скрип необходимо выполнить используя SQL *Plus иди SQL Developer с правами пользователя SYSDBA. Во время выполнения будут запрошены некоторые значения. Например в среде Linux вначале запустим SQL *Plus из командной строки ОС

root@> sqlplus / as sysdba

Доступны разные параметры для подключения к базе данных, но такая команда подключит вас к базе данных если вы работаете на той жемашине на которой установлен Oracle.

Затем выполните команду в SQL *Plus

SQL> @?/demo/schema/human_resources/hr_main.sql

Символ “?” это переменная которую SQL *Plus заменит на путь к домашней директории Oracle. Выведется запрос на пароль пользователя HR, табличное и временное пространство, пароль аккаунта SYS и путь куда записывать лог о выполнении скрипта. Обычно значения табличного пространства по умолчанию и временного пространтсва это USERS и TEMP, но они должны быть созданы перед запуском скрипта. После выполнения скрипта вы будете подключены к БД как пользователь HR. Чтобы убедиться в этом выполните команду

SQL> show user

Вы увидите что подключен аккаунт HR; затем выполните

select table_name from user_tables;

И вы увидите семть таблиц схемы HR.

Для создания схемы WEBSTORE (если она не создана в процессе выполнения лабораторных работ) выполните следующие запросы для создания необходимых объектов и добавления данных которые будут использоваться далее.

root@> sqlplus / as sysdba

SQL> create user webstore identified by oracle default tablespace users temporary tablespace temp quota unlimited on users;

SQL> grant create session, create table, create sequence to webstore;

SQL> ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;

SQL> connect webstore/oracle

SQL> create table customers(

customer_id number(8) not null constraint pk_customer_id primary key,

join_date date default sysdate not null,

customer_status varchar2(8) not null, customer_name varchar2(20) not null,

creditrating varchar2(10) not null, email varchar2(50) not null);

SQL> create table products(

product_id number(8) not null constraint pk_product_id primary key,

product_description varchar2(20) not null,

product_status varchar2(8) not null, price number(10,2) not null,

price_date date not null, stock_count number(8) not null);

SQL> create table orders(

order_id number(8) not null constraint pk_order_id primary key,

order_date date not null, order_status varchar2(8) not null,

order_amount number(10,2) not null,

customer_id number(8) constraint fk_customer_id references customers (customer_id));

SQL> create table order_items(

order_item_id number(8) not null,

order_id number(8) constraint fk_order_id references orders(order_id),

product_id number(8) constraint fk_prod_id references products(product_id),

quantity number);

SQL> create sequence cust_seq;

SQL> create sequence order_seq;

SQL> create sequence prod_seq;

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

insert into customers (customer_id, customer_status, customer_name, creditrating, email)

values (cust_seq.nextval, ‘&cust_status’, ‘&cust_name’, ‘&creditrating’, ‘&email’);

insert into products(product_id, product_description, product_status, price, price_date, stock_count)

values (prod_seq.nextval, ‘&product_description’, ‘ACTIVE’, &price, sysdate, &stock_count);

insert into orders(order_id, order_date, order_status, order_amount, customer_id)

values (order_seq.nextval, sysdate, ‘&order_status’, &order_amount, &customer_id);

insert into order_items values (&item_id, &order_id, &product_id, &quantity);

commit;