Лекция 5: Выборка данных. Общее построение предложения SELECT и фразы FROM и WHERE

Условный оператор LIKE

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

• _ — один любой символ;
• % — отсутствие символа или цепочка любых символов.

Примеры:

SELECT ename FROM emp WHERE ename LIKE 'M%';
-- сотрудники, имена которых начинаются на 'M' (а возможно, этим и оканчиваются)
SELECT ename FROM emp WHERE ename LIKE '%AR_';
-- сотрудники, две предпоследних буквы имени которых — 'AR'
SELECT ename, sal FROM emp WHERE sal LIKE 2 || '%';
-- автоматическое преобразование типов

Когда необходимо, чтобы символ _ или % в шаблоне играл обычную, а не особую роль, его следует предварить каким-нибудь произвольно выбранным символом, о котором сообщить отдельно в продолжении ESCAPE оператора LIKE. Например, требуется найти таблицы словаря-справочника, выдающие те или иные сведения о столбцах таблиц в моей схеме. Из опыта работы со словарем-справочником Oracle программист знает, что имена подобных таблиц скорее всего содержат шаблон '_COL_'.Правильно составленый запрос будет таким:

SELECT table_name
FROM   dictionary
WHERE  table_name LIKE 'USER%\_COL\_%' ESCAPE '\'
;

Упражнение. Сравните ответ на предыдущий запрос с ответом на другой, в котором шаблон сравнения указан просто как 'USER%_COL_'.

Для отрицания можно использовать форму NOT LIKE, например:

SELECT ename FROM emp WHERE ename NOT LIKE 'M%';

Начиная с версии 9.2 оператор LIKE (вместе с некоторыми строковыми функциями, например, INSTR, SUBSTR) применим не только к типам CHAR и VARCHAR2, но также к CLOB и NCLOB.

Помимо LIKE имеются еще операторы LIKEC, LIKE2 и LIKE4, реализующие разные логики сравнения символов в Unicode.

Условный оператор REGEXP_LIKE

Выразительные возможности оператора LIKE невелики. С версии 10 в Oracle существует намного более мощный оператор REGEXP_LIKE, позволяющий делать проверку наличия в тексте шаблона, построенного техникой регулярных выражений.

Пример:

SELECT ename FROM emp WHERE REGEXP_LIKE ( ename, '(^.LL|TT)' );
-- сотрудники с двумя подряд 'T' где угодно или с двумя подряд 'L' на втором и 
-- третьем местах сначала

Как видно, формально оператор REGEXP_LIKE устроен традиционно, а не инфиксно, подобно LIKE. Однако соответствующий ему в SQL:1999 оператор SIMILAR устроен инфиксно.

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

SELECT table_name 
FROM   dictionary 
WHERE  REGEXP_LIKE ( table_name, '^(USER|ALL)_COL_' )
;

Возможный вариант того же запроса, когда регистр не играет роли (а может быть, заранее не известен):

SELECT table_name 
FROM   dictionary 
WHERE  REGEXP_LIKE ( table_name, '^(user|all)_col_', 'i' )
;

В третьем аргументе REGEXP_LIKE значение 'i' означает case-insensitive, то есть неразличение верхнего и нижнего регистров.

Регулярные выражения используются, кроме того, для анализа и изменения строк функциями REGEXP_COUNT, REGEXP_INSTR, REGEXP_REPLACE и REGEXP_SUBSTR, доступными в числовых и текстовых выражениях SQL.

Для составления регулярных выражений Oracle дает достаточно широкий набор специальных символов и условных обозначений, полный перечень которых имеется в документации. В целом правила составления таких выражений в Oracle напоминают принятые в Perl, .NET, Java и установленные стандартом POSIX, но содержат и отличия от всего перечисленного.

Условный оператор BETWEEN

Используется для проверки "попадания" значения в указаный диапазон. Пример запроса:

SELECT ename FROM emp WHERE sal BETWEEN 1000 AND 2000;

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

Выражение

  E1 BETWEEN E2 AND E3

равносильно

  ( E1 >= E2 ) AND ( E1 <= E3 )

Следствия:

• Оператор действует на значениях всех типов, допускающих сравнения на неравенство (числовых, символьных, временных и в некоторых случаях объектных).
• Если хотя бы одно из Еn будет NULL, результат будет NULL.

Выражение

  E1 NOT BETWEEN E2 AND E3

равносильно

  NOT ( E1 BETWEEN E2 AND E3 )

и равносильно

  ( E1 < E2 ) OR ( E1 > E3 )

По причине наличия равносильных более общих формулировок, оператор BETWEEN ничего содержательно нового в SQL не привносит. Его ценность в ином:

• он способствует лучшему пониманию текста программистом, что понижает шанс для ошибок;
• он вычисляется эффективнее своей более традиционной равносильной переформулировке.

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

Пример:

SQL> SELECT ename, comm FROM emp WHERE comm BETWEEN 10 AND 1000;
ENAME            COMM
---------- ----------
ALLEN             300
WARD              500
SQL> SELECT ename, comm
  2  FROM   emp
  3  WHERE  comm BETWEEN ( SELECT comm FROM emp WHERE ename = 'SCOTT' )
  4                  AND 1000
  5  ;
no rows selected

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