Выражение case


Выражения

См. также

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

Case (Switch)

Эта форма оператора Case также называется формой CASE (поиск). Значение expression1 проверяется, затем проверяются выражения WHEN. Если expression1 соответствует какому либо из выражений WHEN, оно присваивает значение соответствующему выражению THEN.

Если не найдено соответствия ни одному выражению WHEN, присваивается значение по умолчанию, заданное выражением ELSE. Если выражение ELSE не задано, система автоматически добавляет ELSE NULL.

Если expression1 соответствует выражениям нескольких фраз WHEN, присваивается значение выражения, следующего только за первым соответствием.

Синтаксис

CASE expression1
WHEN expression2 THEN expression2

ELSE expression
END

Начало оператора CASE. После него должно следовать выражение и один или несколько операторов WHEN и THEN, необязательный оператор ELSE и ключевое слово END.

Задает условие, которое должно быть удовлетворено.

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

Задает значение, которое присваивается в том случае, если не удовлетворяется ни одно из условий WHEN. Если опущено, предполагается ELSE NULL.

Завершает оператор CASE.

Пример

CASE Score-par
WHEN -5 THEN ‘Пташка на уровне 6’
WHEN -4 THEN ‘Должен быть тигр’
WHEN -3 THEN ‘Три ниже номинала’
WHEN -2 THEN ‘Два ниже номинала’
WHEN -1 THEN ‘Пташка’
WHEN 0 THEN ‘Номинал’
WHEN 1 THEN ‘Пугало’
WHEN 2 THEN ‘Два пугала’
ELSE ‘Три пугала или хуже’
END

В примере выше операторы WHEN должны отражать точное равенство; условие WHEN

Case (If)

Эта форма оператора Case выполняет вычисление каждого условия WHEN и, если условие удовлетворяется, присваивает значение соответствующему выражению THEN.

Если ни одно из условий WHEN не удовлетворяется, присваивается значение по умолчанию, заданное выражением ELSE. Если выражение ELSE не задано, система автоматически добавляет ELSE NULL.

Синтаксис

Начало оператора CASE. После него должен следовать один или несколько операторов WHEN и THEN, необязательный оператор ELSE и ключевое слово END.

Задает условие, которое должно быть удовлетворено.

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

Задает значение для случая, если не удовлетворяется ни одно из условий WHEN. Если опущено, предполагается ELSE NULL.

Условные выражения CASE

Обеспечивают использование логики IF-THEN-ELSE в пределах SQL-оператора.

Используют два метода:


Два метода, которые используются, чтобы реализовать условную обработку (логику IF-THEN-ELSE) в SQL-операторе, — это выражение CASE и функция DECODE.

Отметьте : Выражение CASE удовлетворяет ANSI SQL. Функция DECODE специфической для синтаксиса Oracle.

Выражение CASE

Упрощает условные запросы, делая работу оператора IF-THEN-ELSE :

Выражения CASE позволяют Вам использовать логику IF-THEN-ELSE в SQL-операторах, не имея необходимости вызывать процедуры.

В простом условном выражении CASE сервер Oracle ищет первую пару WHEN . THEN, для которой expr равно comparison_expr и возвращает return_expr. Если ни одна из пар WHEN . THEN не удовлетворяет этому условию, и если выражение else существует, сервер Oracle возвращает else_expr. Иначе, сервер Oracle возвращает null. Нельзя указsdать NULL для всех return_exprs и для else_expr.

Выражения expr и comparison_expr должны иметь тот же самый тип данных, который может быть CHAR, VARCHAR2, NCHAR или NVARCHAR2. Все возвращаемые значения (return_expr) должны иметь одинаковый тип данных.

Выражение case

При программировании приложений баз данных иногда бывает необходимыми изменять представление данных. Например, род (gender) человека может быть кодирован с использованием значений 1, 2 и 3 (для женщины, мужчины и ребенка соответственно). Такие программные техники позволяют сократить время на реализацию программы. Выражение case в языке Transact-SQL делает подобный тип кодирования простым в реализации.

Выражение case имеет две различные формы:

♦ простое выражение case;

♦ поисковое выражение case. Синтаксис простого выражения case:

[when expression_2 then result_l].

[else result_n] end

Оператор Transact-SQL с простым выражением case отыскивает первое выражение в списке всех предложений where, которое соответствует выражению expression_i, и выполняет соответствующее предложение then. Если соответствие не найдено, то выполняется предложение else.

Синтаксис поискового выражения case:

[when condition_l then result_l] . [else result_n]

Оператор Transact-SQL с поисковым выражением case отыскивает первое выражение, которое при вычислении дает истину. Если никакое условие when не дает истину, то возвращается значение выражения else. В примере 6.47 показано использование поискового выражения case.

В примере 6.47 бюджеты всех проектов взвешиваются, и отображаются все весовые коэффициенты (вместе с именами соответствующих проектов).

В примере 6.48 показан другой вариант с выражением case, где предложение when содержит внутренний запрос как часть выражения.

Case-выражение

Выражение CASE позволяет определить результат на основе проверки одного или нескольких условий.

Выражение CASE может быть записано двумя способами.

Первый способ имеет следующий вид:

CASE WHEN условие_поиска1 THEN выражение1 [WHEN условие_поиска2 THEN выражение2 …]

[ ELSE выражениеL ] END

Вычисление выражения CASE, представленного таким образом, начинается с вычисления условия_поиска1. Если в результате будет получено значение false (ложь), вычисляется условие_поиска2, и т.д. Как только для какого-либо условия_поискаK будет получено значение true (истина), вычисляется связанное с ним выражениеK, определяющее результат вычисления выражения CASE, и на этом вычисления прекращаются. Если все условия поиска дают в результате значение false, результатом выражения CASE будет результат вычисления выраженияL, если присутствует ELSE, или же NULL, если ELSE не указано.

Следует отметить, что если при вычислении какого-либо условия поиска получается неопределенное значение (из-за наличия NULL значений), такое значение не является истинным и интерпретируется как false.


Второй способ записи выражения CASE имеет следующий вид:

CASE выражение WHEN выражение1 THEN результирующее_выражение1 [ WHEN выражение2 THEN результирующее_выражение2 …][ ELSE результирующее_выражениеL ] END

При вычислении выражения CASE, заданного таким способом, результат вычисления выражения последовательно сравнивается с результатами вычисления выражения1, выражения2 и т.д. (в соответствии с этим, тип результата вычисления выражения должен быть сравнимым с типом результатов вычисления выражений, записанных после WHEN). Как только очередное сравнение окажется успешным (выражение = выражениеK), вычисляется соответствующее результирующее_выражениеK, определяющее результат вычисления выражения CASE, и вычисления заканчиваются. Если же значение выражения не совпадает ни с одним из результатов выражений, указанных после WHEN, тогда результатом вычисления выражения CASE является результат вычисления результирующего_выраженияL, если указано ELSE, или NULL, если ELSE отсутствует.

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

• после ключевых слов THEN и ELSE вместо выражения может быть указано ключевое слово NULL;

• по крайней мере, после хотя бы одного THEN не должно быть указано NULL;

• результаты всех выражений, записанных после THEN и ELSE, должны иметь совместимый тип данных.

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

Не нашли то, что искали? Воспользуйтесь поиском:

Операторы ветвления в команде SELECT

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

Вложенные функции

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

Function1(parameter1, parameter2, …) = result

Замена параметра функции на вызов другой функции может привести к появлению выражений вида

F1( param1.1, F2( param2.1, param2.2, F3( param3.1)), param1.3)

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

  1. Вычисляется функция F3(param1) и возвращаемое значение используется как третий параметр для функции 2, назовём его param2.3
  2. Затем вычисляется функция F2(param1, param2.2, param2.3) и возвращаемое значение используется как второй параметр функции F1 – param1.2
  3. И наконец вычисляется функция F1(param1, param2, param1.3) и результат возвращается в вызывающую программу.

Таким образом функция F3 находится на третьем уровне вложенности.

select next_day(last_day(sysdate)-7, ‘tue’) from dual;

  1. В этом запросе три функции, от нижнего уровня к верхнему – SYSDATE, LAST_DAY, NEXT_DAY. Запрос выполняется следующим образом
  2. Выполняется самая вложенная функция SYSDATE. Она возвращает текущее системное время. Предположим, что текущая дата 28 октября 2009 года
  3. Далее вычисляется результат функция второго уровня LAST_DAY. LAST_DATE(’28-OCT-2009’) возвращает последний день окбября 2009 года, то есть значение 31 октябрая 2009.
  4. Затем происходит вычитания из этой даты семи дней – получается 24 октября.
  5. И наконец вычисляется функция NEXT_DAY(’24-OCT-2009’, ‘tue’), и запрос возвращает последний вторник октября – что в нашем примере 27-OCT-2009.

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

Функции ветвления

Функции ветвления, также известные как ЕСЛИ-ТО-ИНАЧЕ, используется для определения пути выполнения в зависимости от каких-либо обстоятельств. Функции ветвления возвращают разные результат основываясь не результате вычисления условия. В группе таких функций выделяют функции работы со значением NULL: NVL, NVL2, NULLIF и COALESCE. И также общие функции, представленные функцией DECODE и выражением CASE. Функция DECODE является Oracle функцией, тогда как CASE выражение присутствует в стандарте ANSI SQL.

Функция NVL


Функция NVL проверяет значение столбца или выражения любого типа данных на значение NULL. Если значение NULL – она возвращает альтернативное не-NULL значение по умолчанию, иначе возвращается исходное значение.

Илон Маск рекомендует:  Что такое код getsyscolor

У функции NVL два обязательных параметра и синтаксис NVL(original, ifnull) где original это исходное значение для проверки и ifnull результат возвращаемый функцией если original значение равно NULL. Тип данных параметров ifnull и original должен быть совместим. То есть либо тип данных должен быть одинаковым или должна быть возможность неявной конвертации значений из одного типа в другой. Функция NVL возвращает значение такого же типа данных как тип данных параметра original. Рассмотрим три запроса

Query 1: select nvl(1234) from dual;

Query 2: select nvl(null, 1234) from dual;

Query 3: select nvl(substr(‘abc’, 4), ‘No substring exists’) from dual;

Так как функции NVL необходимо два параметра, запрос 1 вернёт ошибку ORA-00909: invalid number of arguments. Запрос 2 вернёт 1234 так как проверяется значение NULL и оно равно NULL. Запрос три используется вложенную SUBSTR функцию которая пытается выделить четвёртый символ из строки длиной в три символа, возвращает значение NULL, а функция NVL возвращает строку ‘No sbustring exists’.

Функция NVL очень полезна при работе с числами. Она используется для конвертации NULL значений в 0, чтобы арифметические операции над числами не возвращали NULL

Функция NVL2

Функция NVL2 предоставляет больше функционала чем NVL, но служит также для обработки значения NULL. Она проверяет значение столбца или выражения любого типа на значение NULL. Если значение не равно NULL, то вовзращается второй параметр, иначе возвращается третий параметр, в отличии от функции NVL, которая в этом случае возвращает исходное значение.

У функции NVL2 три обязательных параметра и синтаксис NVL2(original, ifnotnull, ifnull), где original – это проверяемое значение, ifnotnull значение возвращаемое в случае если original не равно NULL и ifnull значение возвращаемое в случаем если original равно NULL. Типы данных параметров ifnotnull и ifnull должы быть совместимы, и они не могут быть типа LONG. Тип данных возвращаемых функцией NVL2 равен типу данных параметра ifnotnull. Рассмотрим несколько примеров

Query 1: select nvl2(1234, 1, ‘a string’) from dual;

Query 2: select nvl2(null, 1234, 5678) from dual;

Query 3: select nvl2(substr(‘abc’, 2), ‘Not bc’, ‘No substring’) from dual;

Параметра ifnotnull в запросе 1 это число, а параметр ifnull – это строка. Так как типы данных несовместимы, возвращается ошибка “ORA-01722: invalid number”. Запрос два возвращает ifnull параметр, так как original равно NULL и результатом будет 5678. Запрос три использует функция SUBSTR которая возвращает ‘bc’ и происходит вызов NVL2(‘bc’,’Not bc’,’No substring’) – который возвращает ifnotnull параметр – ‘Not bc’.

Функция NULLIF

Функция NULLIF проверяет два значения на идентичность. Если они одинаковы – возвращается NULL иначе возвращается первый параметр. У функции NULLIF два обязательных параметра и синтаксис NULLIF(ifunequal, comparison_item). Функция сравнивает два параметра и если они идентичны – возвращается NULL, иначе параметр ifunequal. Рассмотрим запросы

Query 1: select nullif(1234, 1234) from dual;

Query 2: select nullif(’24-JUL-2009′, ’24-JUL-09′) from dual;

Запрос один возвращает NULL так как параметры идентичны. Строки в запросе 2 не конвертируются в дату, а сравниваются как строки. Так как строки разной длины – возвращается параметра ifunequal 24-JUL-2009.

На рисунке 10-4 функция NULLIF вложена в функцию NVL2. В функции NULLIF в свою очередь используются функции SUBSTR и UPPER как части выражения в параметре ifunequal. Столбец EMAIL сравнивается с этим выражением, возвращающем первую букву имени, объединённую с фамилией для сотрудников у которых имя длиной в 4 символа. Когда эти значения равны, NULLIF вернёт NULL, иначение вернёт значение параметра ifunequal. Эти значения используюся как параметр для функции NVL2. NVL2 в свою очередь возвращает описание совпадали ли сравниваемые элементы или нет.

Рисунок 10-4 – Использование функции NULLIF

Функция COALESCE

Функция COALESCE возвращает первое значение не равное NULL из списка параметров. Если все параметры равны NULL, то возвращается NULL. У функции COALESCE два обязательных параметра и сколько угодно необязательных параметров и синтаксис COALESCE(expr1, expr2, …, exprn) где результатом будет expr1 если значение expr 1не NULL, иначе результатом будет expr2 если оно не NULL и т.д. COALESCE равно по смыслу вложенным функциям NVL

COALESCE(expr1, expr2) = NVL (expr1, expr2)

COALESCE(expr1, expr2, expr3) = NVL(expr1,NVL(expr2, expr3))

Тип данных возвращаемого значение если найдено не NULL значение равен типу данных первого не NULL значения. Для того чтобы избежать ошибки ‘ORA-00932: inconsistent data types’ все не NULL параметры должны быть совместимы с первым не NULL параметром. Рассмотрим три примера

Query 1: select coalesce(null, null, null, ‘a string’) from dual;

Query 2: select coalesce(null, null, null) from dual;


Query 3: select coalesce(substr(‘abc’, 4), ‘Not bc’, ‘No substring’) from dual;

Запрос 1 возвращает четвёртый параметр: строку, так как это первый не NULL параметр. Запрос два возвращает NULL так как все параметры равны NULL. Запрос 3 вычисляет первый параметр, получает значение NULL и возвращает второй параметр, так как он первый не NULL параметр.

Параметры функции NVL2 могут запутать если вы уже знакомы с функцие NVL. NVL(original, ifnull) возвращает original если значение не NULL, иначе ifnull. NVL2(original, ifnotnull, ifnull) возвращает ifnotnull если значение original не равно NULL иначе ifnull. Путаница происходит из-за того, что второй параметра функции NVL ifnull, тогда как у NVL2 это ifnotnull. Так что не надейтесь на позицию параметра в функции.

Функция DECODE

Функция DECODE реализует if-then-else логику проверяя первые два параметра на равенство и возвращая третье значение в случае их равенства или другое значение в случае неравенства. У функции DECODE три обязательных параметра и синтаксис DECODE(expr1, comp1, iftrue1, [comp2, iftrue2 … [compN, iftrueN], [iffalse]). Эти параметры используются как показано в слеующем примере псевдокода

IF expr1=comp1 then return iftrue1

Else if expr1=comp2 then return iftrue2

Else if exprN=compN then return iftrueN

Else return NULL|iffalse;

Вначале expr1 сравнивается с comp1. Если они равны возвращается значение iftrue1. Если expr1 не равно comp1, то что происходит дальше зависит от того заданы ли параметры comp2 и iftrue2. Если заданы, тов значение expr1 сравнивается с comp2. Если значения равны, то возвращается iftrue2. Если нет, то если есть пары параметров compN, iftrueN происходит сравнение expr1 и compN и в случае равнества возвращается iftrueN. Если не было найдено совпадение ни в одном наборе параметров, то возвращается или iffalse если этот параметр был задан, или NULL.

Все параметры в функции DECODE могут быть выражениями. Тип возвращаемого значения равен типу первого проверяющего элемента – параметра comp 1. Выражение expr 1 неявно преобразуется к типу данных параметра comp 1. Все остальные доступные параметры comp 1 … compN также неявно преобразуются к типу comp 1. DECODE рассматривает значение NULL как равное другому значению NULL, т.е. если expr1 is NULL и comp3 is NULL, а comp2 не NULL, то возвращается значение iftrue3. Рассмотрим несколько примеров

Query 1: select decode(1234, 123, ‘123 is a match’) from dual;

Query 2: select decode(1234, 123, ‘123 is a match’, ‘No match’) from dual;

Query 3: select decode(‘search’, ‘comp1’, ‘true1’, ‘comp2’, ‘true2’, ‘search’, ‘true3’, substr(‘2search’, 2, 6), ‘true4’, ‘false’) from dual;

Запрос один сравнивает значение 1234 и 123. Так как они не равны то iftrue1 игнорируется и так как не определено значение iffalse то возвращается NULL. Запрос два идентичен запросу 1 за тем исключением что значение iffalse определено. Так как 1234 не равно 123 то возвращается iffalse – ‘No match’. Запрос три проверяет значения параметров на совпадения значению search. Параметры comp1 и comp2 не равны ‘search’ поэтому результаты iftrue1 и iftrue2 пропускаются. Совпадение найдено в третьей операции сравнения элемента comp3 (позиция параметра 6) и возвращается значение iftrue3 (параметр 7) которое равно ‘true3’. Так как совпадение найдено больше вычисления не производятся. То есть несмотря на то что значение comp4 (параметр 8) также совпадает с expr1 – это выражение никогда не рассчитывается так как совпадение было найдено в предыдущем сравнении.

Выражение CASE

Все языки программирования третьего и четвертого поколения реализуют конструкцию case. Как и функция DECODE, выражение CASE позволяет реализовывать if-then-else логику. Доступны два варианта использования выражения CASE. Простое CASE выражение устанавливает исходный элемент для сравнения единожды, а затем перечисляет все необходимые условия проверки. Сложный (searched) CASE вычисляет оба оператора для каждого условия.

У выражения CASE три обязательных параметра. Синтаксис выражения зависит от типа. Для простого CASE выражения он выглядит так

WHEN comparison_expr1 THEN iftrue1

[WHEN comparison _expr2 THEN iftrue2

WHEN comparison _exprN Then iftrueN

Выражение заключается в CASE … END блок и должно иметь хотя бы один WHEN … THEN элемент. В своей простейшей форме с одинм WHEN … THEN элементом параметр search_expr сравнивается с comparison_expr1 и, если они равны, возвращается iftrue1. Если нет, то возвращается значение NULL если не указан элемент ELSE. Если ELSE присутствует в выражении, то возвращается значение iffalse. Если в выражении больше чем один WHEN … THEN, то происходит сравнение значения search_expr пока не будет найдено совпадение.

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

case substr(1234, 1, 3)

‘1234 is a match’

‘1235 is a match’

when concat(‘1′, ’23’) then

concat(‘1′, ’23’)||’ is a match’


Параметр search_expression рассчитываеся в функции SUBSTR и получается значение ‘123’. Первый WHEN … THEN сравнивает 134 и 123. Так как они не равны, проверяется следующий WHEN…THEN и сравнивается 123 с 12345 и значения снова не одинаковы. Третий WHEN … THEN сравнивает значение ‘123’ с выражением CONCAT(‘1’,’23’), которое даёт результат ‘123’. Сравниваемые значения равны, и возвращается результат третьего выражения ‘123 is a match’.

На рисунке 10-5 выполняется запрос, который выбирает столбцы LAST_NAME и HIRE_DATE из таблицы EMPLOYEES где значение DEPARTMENT_ID равны 10 или 60, а также два выражения и выражение CASE.

Илон Маск рекомендует:  Что такое код domdocument >create_comment

Рисунок 10-5 – сложное CASE выражение

Преположим что текущая дата 22 апреля 2020 года. Выражение с псевдонимом YEARS возвращает округленное в нижнюю сторону значения равного количеству месяцев с момента приёма на работу до текущей даты, разделённое на 12. Пять категорий лоялности сотрудника определены в зависимости от значения количества месяцев, проработанных в компании, делённых на число 60. Это значение формирует параметр condition1 для выражения CASE. Ни одна из строкв таблице не удовлетворяет первому условию, но удовлетворяет некоторым другим. Такие выражения называются сложными (searched) и синтаксис такого выражения

WHEN condition1 THEN iftrue1

[WHEN condition2 THEN iftrue2

WHEN conditionN THEN iftrueN

Сложное выражение заключается в конструкцию CASE … END и состоит минимум из одного WHEN…THEN блока. В простейшей форме с одним блоком WHEN…THEN вычисляется condition1 и если значение возвращает логическое ДА, то возвращается iftrue, иначе возвращается NULL если не указан ELSE iffalse, при наличии которого вместо NULL возвращается iffalse. Если в CASE выражении больше чем один блок WHEN…THEN то вычисление происходит пока не будет найдено совпадение. Запрос для получения результата, похожего на представленный на рисунке 10-5

select last_name, hire_date,

trunc(months_between(sysdate, hire_date)/12) years,

trunc(months_between(sysdate, hire_date)/60) «Years divided by 5»,

when trunc(months_between(sysdate, hire_date)/60)

Выражение case

Регулярное выражение — Регулярные выражения (англ. regular expressions, сокр. RegExp, RegEx, жарг. регэкспы или регексы) система синтаксического разбора текстовых фрагментов по формализованному шаблону, основанная на системе записи образцов для поиска. Образец (англ.… … Википедия

Оператор ветвления — (условная инструкция, условный оператор) оператор, конструкция языка программирования, обеспечивающая выполнение определённой команды (набора команд) только при условии истинности некоторого логического выражения, либо выполнение одной из… … Википедия

ECMAScript — Класс языка: мультипарадигменный: объектно ориентированное, обобщённое, функциональное, императивное, аспектно ориентированное, событийно ориентированное, прототипное программирование Появился в: 1995 Автор(ы) … Википедия

Обратная польская нотация — (ОПН) (Обратная польская запись, Обратная бесскобочная запись (ОБЗ), Постфиксная нотация, Бесскобочная символика Лукашевича, Польская инверсная запись, Полиз) форма записи математических выражений, в которой операнды расположены перед знаками… … Википедия

История вермахта — Основная статья: Вермахт Проверить информацию. Необходимо проверить точность фактов и достоверность сведений, изложенных в этой статье. На странице обсуждения должны быть пояснения … Википедия

Дело Pussy Riot — … Википедия

ЕВХАРИСТИЯ. ЧАСТЬ II — Е. в православной Церкви II тысячелетия Е. в Византии в XI в. К XI в. визант. богослужение приобрело почти тот вид, какой оно сохраняло в правосл. Церкви все последующее тысячелетие; в его основе лежала древняя к польская традиция, значительно… … Православная энциклопедия

Регулярные выражения — (англ. regular expressions, сокр. RegExp, RegEx, жарг. регэкспы или регексы) это формальный язык поиска и осуществления манипуляций с подстроками в тексте, основанный на использовании метасимволов (символов джокеров,… … Википедия

Регексп — Регулярные выражения (англ. regular expressions, сокр. RegExp, RegEx, жарг. регэкспы или регексы) система синтаксического разбора текстовых фрагментов по формализованному шаблону, основанная на системе записи образцов для поиска. Образец (англ.… … Википедия

Регексы — Регулярные выражения (англ. regular expressions, сокр. RegExp, RegEx, жарг. регэкспы или регексы) система синтаксического разбора текстовых фрагментов по формализованному шаблону, основанная на системе записи образцов для поиска. Образец (англ.… … Википедия

Регеспы — Регулярные выражения (англ. regular expressions, сокр. RegExp, RegEx, жарг. регэкспы или регексы) система синтаксического разбора текстовых фрагментов по формализованному шаблону, основанная на системе записи образцов для поиска. Образец (англ.… … Википедия

О разработке ПО и эффективности

Методы и инструменты эффективной разработки приложений.

понедельник, 18 апреля 2011 г.


Трюки в SQL – используем CASE

После серии статей “Основы SQL” я решил попробовать вспомнить некоторые нестандартные варианты использования Transact-SQL и начать серию статей “Трюки в SQL”.

В этой статье расскажу прежде всего о полезном но неочевидном использовании выражения CASE. Правда, речь пойдет о достаточно простых приемах (когда их знаешь). Основное содержание:

База данных для примеров

Если в прошлой серии я использовал максимально простую БД, которую создал и наполнил скриптами, то теперь я решил использовать стандартные примеры от Microsoft. Причина в том, что потребуются более сложные структуры и данные, да и примеры станут ближе к реальной жизни.

Поэтому для запуска примера потребуется БД AdventureWorks. Я пользовался облегченной версией для Microsoft SQL Server 2005 (там, в зависимости от версии, можно выбрать AdventureWorksLT.msi или AdventureWorksLT_x64.msi). Скорее всего, подойдут и более поздние версии с CodePlex.

План выполнения

Расскажу на случай, если вы не знаете про план выполнения. В этой статьях этой серии он будет периодически упоминаться. Это пятилетний план выполнения… шутка, надеюсь вашим запросам никогда не придется так долго работать :)

Если серьезно и не вдаваясь в подробности, движку базы данных необходимо знать, в какой последовательности и каким способом соединять таблицы, какие индексы использовать и т.п. Иными словами, перед выполнением запроса необходимо построить (или взять из кэша) план выполнения.

Актуальный план выполнения вы можете посмотреть в SSMS (SQL Server Management Studio), выбрав его в пункте меню “Query / Include Actual Execution Plan” или нажав “Ctrl+M”. В результате, после выполнения пакета запросов вы увидите вкладку “Execution Plan”, в которой для каждого запроса из пакета будет выведен план выполнения в графическом виде.

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

Синтаксис CASE

На всякий случай напомню синтаксис “CASE”, поскольку его используют сравнительно редко:

Хочу обратить ваше внимание на то, что “ELSE” можно не указывать, тогда, в случае отсутствия соответствующего “WHEN” вернется NULL.

Также имеет смысл отметить, что возвращаемые значения должны быть совместимы. То есть, так написать можно:

Обратите внимание: то же правило, как нетрудно догадаться, работает не только для констант, но и для столбцов таблиц. Так что будьте бдительны – ваш запрос может работать на одних данных (скажем, когда код товара хранится в строке, но содержит только цифры) и не работать на других (добавили в код товара буквы).

SUM по CASE

Итак, на первом месте, по праву – суммирование с условием внутри (возвращающим 0 или 1). Этот прием позволяет вместо нескольких однотипных запросов написать один и, что часто бывает, неплохо улучшить производительность.

Пример несколько синтетический, зато простой – представьте, что нужно периодически узнавать общее количество товаров, а также количество красных и черных товаров. Так можно решить задачу “в лоб”:

А так, используя “SUM по CASE”:

Если в SSMS (SQL Server Management Studio) включить вывод актуального плана выполнения, то можно увидеть, что стоимость последнего запроса в два раза меньше, чем суммарная стоимость первых трех.

В качестве лирического отступления – хорошее понимание работы Microsoft SQL Server мне чаще всего грело душу тогда, когда после “неплохого” улучшения производительности запроса, он начинал выполняться в десятки раз быстрее. И еще один интересный факт – мы с моим коллегой (администратором SQL) не сговариваясь заранее сошлись во мнении, что свободное оперирование “SUM по CASE” говорит о хорошем знании SQL. Так что я вам только что рассказал один из секретов, как сойти за знатока SQL :)

ORDER по CASE

Раз уж заговорили про группировку с “CASE”, то стоит упомянуть и сортировку, как правильно мне напомнили в одном из комментариев. Здесь все довольно просто – в “ORDER BY” (как и в “WHERE”, “GROUP BY” и т.п.) мы тоже можем использовать “CASE”, что дает возможность делать более интеллектуальную сортировку.

Хорошим примером будет таблица с категориями товаров (двух-уровневое дерево). Давайте подумаем, как нам вывести эту таблицу как дерево (сначала родительский элемент, потом все его дочерние, потом следующий родительский и т.д.). Если мы будем использовать запрос без сортировки или с сортировкой по родительской категории, то получим сначала все родительские элементы.

Теперь попробуем с “CASE”:

Уже лучше. Однако нам, если разобраться, просто повезло с порядком идентификаторов. Если мы отсортируем дочерние элементы по названию, то сразу увидим в чем проблема.

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


Резюме

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

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

Выражения в Oracle SQL

Конструкции (операторы) CASE для построения выражений

В качестве альтернативы функции DECODE (отсутствующее в стандарте решение Oracle, оформленное в виде функции) и других функций условной подстановки значений NVL, NVL2, NANVL и COALESCE начиная с версии 8.1.6 можно пользоваться «поисковым» CASE -выражением, а с версии 9 — «простым» CASE -выражением (оба входят в стандарт SQL-92). Формально конструкцию CASE можно считать оператором (с более сложной синтаксической структурой, нежели в случае, положим, арифметических операторов), предназначенным для построения выражений из более простых. Для употребления существенно, что результат CASE не «окончателен»; он представляет собой выражение, которое не возбраняется использовать для построения очередного более сложного. В этом конструкция CASE не отличается от прочих операторов.

Синтаксис «поискового» оператора CASE :

Проверки происходят сверху вниз, пока первое по порядку условное-выражениеI не станет TRUE . Тогда проверки прекратятся, и результатом CASE будет значение выражения-результатаI.

Синтаксис «простого» оператора CASE :

Проверки происходят сверху вниз, пока значение первого по порядку выраженияI не станет равным значению выражения0. Тогда проверки прекратятся, и результатом CASE будет значение выражения-результатаI.

Синтаксис условного-выражения в CASE соответствует синтаксису подобного в части WHERE предложений SELECT, UPDATE и DELETE , описываемых далее, и допускает достаточно сложные конструкции, как показывает пример ниже:

Илон Маск рекомендует:  Русский перевод файла "php ini"

Заметьте, что по нашим данным в результате служащий KING будет помечен как «высокооплачиваемый». Если в операторе CASE проверку зарплаты и местонахождения отдела поменять местами, KING окажется помечен как «работающий в Нью-Йорке».

Упражнение. Проверьте последнее утверждение.

Тем самым конструкция CASE вносит элемент процедурности в описательное в целом построение запроса, принятое в SQL.

Отсутствие конструкции ELSE может приводить к отсутствию значения в результате (к NULL ), однако же не к ошибке:

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

Вместо этого лучше написать:

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

Из-за того, что конструкция CASE оформлена в виде оператора языка, а не функции, как DECODE, NVL, NVL2, NANVL и COALESCE , она становится не только их более общим заменителем, но к тому же и быстрее их вычислимой, хотя бы и ненамного в каждом отдельном случае. Это создает стимул к применению в программировании именно ее, а не перечисленных функций условной подстановки значений. В то же время, в тексте запроса она обычно занимает больше места.

Скалярный запрос

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

При этом множественный результат воспринимается как ошибка, а пустой результат — как отсутствие значения, NULL :

Добавление нуля в выражении выше сделано, чтобы убедить читателя в отсутствии значения у приведенного скалярного выражения . Иначе подошло бы использование функции NVL .

Упражнение. Перепишите последний запрос с использованием функции NVL для выяснения реакции СУБД на отсутствие строк в скалярном запросе.

Одностолбцовость скалярного запроса Oracle в состоянии контролировать синтаксически, а вот однострочность — нет. Для повышения надежности текста некоторые предлагают в качестве искусственной меры включать в условное выражение во фразе WHERE запроса дополнительное условие ROWNUM , например:

Не исключено, что такая мера более важна как способ привлечения внимания программиста к содержательно правильному построению запроса, и такое дополнительное условие служит своего рода «активным комментарием» к тексту программы. Обратите внимание, что добавление AND ROWNUM несколько изменяет смысл запроса.

Скалярный подзапрос скалярен в том же смысле, что и упоминавшиеся скалярные функции, то есть результат его не может быть массивом (например, столбцом значений). В то же время единственное возвращаемое им значение вполне может быть объектом (в смысле объектных возможностей Oracle) и иметь понятную СУБД структуру.


Условные выражения

Условные выражения в Oracle существуют, но в отличие от числовых, строковых и временных не могут использоваться для придания значений полям строк таблиц БД, так как в Oracle отсутствует тип BOOLEAN (хотя он есть в стандарте SQL:1999). Не будучи в той же степени равными, они активно используются для проверки условия в операторе CASE (см. выше), а также в части START WITH фразы CONNECT BY и во фразах WHERE и HAVING предложений SELECT, UPDATE, DELETE (см. ниже).

Отдельные замечания по поводу отсутствия значения в выражениях

Выражение с операндом, значение которого отсутствует (обозначено как NULL ), приведет к отсутствующему же значению ( NULL ) в случае:

  • числовых и временных выражений, построенных арифметическими операциями;
  • сравнения выражений всех видов.

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

При работе с отсутствующими значениями в БД часто используют функцию NVL . Сравните ответы:

В случае (1) получим:

В случае (2) получим:

К сожалению, формального обоснования применения функции NVL в подобных случаях не существует. Стоит ее употребить или нет, решается смыслом, который проектировщик БД закладывает в допущение пропуска значения в столбце. В нашем случае, если смысл — «комиссионные неизвестны» (unknown, » значение отсутствует, потому что неизвестно базе данных, не поступило в БД «), то следует применить запрос (1). Если же смысл «комиссионных нет» («сотрудник не получил комиссионных»), то запрос (2). Смысл пропущенного значения в таблице SQL никак не означен в БД ; он существует вне БД , однако же должен учитываться в программе, работающей с БД . Это одна из давно известных неприятностей SQL .

Частично решить именно эту проблему можно было бы использованием вместо одного «безликого» признака отсутствия значения NULL хотя бы двух с разным смыслом (предлагалось «неприменимо» — missing but inapplicable — и «неизвестно» — missing but applicable). Однако в этом случае возникли бы другие проблемы, связанные со сложностью употребления четырехзначной логики, и по этой причине в SQL от этого отказались. Разработчики SQL советуют использовать пропущенные значения в столбцах только в смысле unknown = missing but applicable . В Oracle этот совет имеет относительную ценность, так как некоторые запросы (примеры встретятся далее) способны порождать пропущенные значения именно в смысле missing but inapplicable .

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

Использование case when then в условии запроса

Здравствуйте. Вот условие моего запроса, блок where:

Мне нужно проверить ещё одно поле на null, и если оно не null тогда тоже добавить по нему условие. Попробовал реализовать это примерно так, но не работает:

Что я делаю не так? И как можно сделать то что я хочу.

3 ответа 3

Просто воспользуйтесь операцией or вместо хитростей с case :

Хочу отметить, что запись case when условие then выражение [else выражение ] end не разрешает в выражение ставить условие. Вместо этого условие нужно прибавить в условие, а в then написать например 1, тогда case будет возвращать 1 при правильном выражении. Может это громоздко, но можно записать так case when THICKNESS_TO is not null and THICKNESS_TO > :t then 1 else 0 end = 1

Очень часто приходится сталкиваться с null. Во многих версиях sql есть функции COALESCE *, NVL (Oracle, Informix) ISNULL (MSSQL)которая выбирает первый аргумент если он не null, иначе второй аргумент. Для даного случая можно выкрутится так:

Т.е. если THICKNESS_TO не задано, используется :t+1 (или поставить большое число, как костыль). Если это записать через case when то это будет так: and case when THICKNESS_TO is null then 0 else THICKNESS_TO end > :t

*COALESCE — есть почти во всех версиях SQL, точно есть в Firebird,MSSQL,Oracle,Informix.

Использование выражений CASE

Выражение CASE — это именно выражение, а не управляющий оператор, поскольку оно возвращает величину определенного типа данных. Поскольку язык SQL является декларативным, в нем нет порядка выполнения операторов, которым можно было бы управлять, как это делается оператором CASE в других языках. Число новичков, которые не понимают разницы между выражением и оператором, просто пугает. Сама эта идея пришла из языка программирования ADA. Вот как выглядит формальный BNF-син-таксис для оператора CASE:

конструкция when с поиском>.


Выражение CASE с поиском
Выражение CASE с поиском, возможно, одна из наиболее часто используемых версий CASE. В ней конструкции WHEN и THEN выполняются слева направо. Первая конструкция WHEN, в которой проверяется истинность условия, возвращает величину, заданную в конструкции THEN, причем вы вольны вставлять выражения CASE друг в друга. Если в выражении CASE не содержится конструкции ELSE, СУБД по умолчанию вставит конструкцию “ELSE NULL”. Чтобы возвратить NULL в конструкции THEN, используйте выражение CAST (NULL AS ). Я всегда рекомендую явно определять конструкцию ELSE. Вы сможете изменить ее позднее, когда найдете что-нибудь определенное для вывода.

Простое выражение CASE
Простое выражение CASE определяется как выражение CASE с поиском, в котором все конструкции WHEN являются проверками равенства операнду CASE. Например:

WHEN 0 THEN ‘Unknown’

WHEN 1 THEN ‘Male’

WHEN 2 THEN ‘Female’

можно записать таю

WHEN iso_sex_code = 0 THEN ‘Unknown’

WHEN iso_sex_code = 1 THEN ‘Male’

WHEN iso_sex_code = 2 THEN ‘Female’

WHEN iso_sex_code = 9 THEN ‘N/A’

Однако в этом определении есть одна тонкость. Выражение:

WHEN 1 THEN ‘bar’

WHEN NULL THEN ‘no bar’

преобразуется к виду:

WHEN foo = 1 THEN ‘bar’

WHEN foo = NULL THEN ‘no bar’ — ошибка

Второй конструкции WHEN всегда сопоставлено значение UNKNOWN. По возможности используйте простое выражение CASE.

Другие виды выражения CASE
В стандартном SQL определены другие функции с использованием выражения CASE, которые делают язык немного более компактным и простым в использовании. Например, функция COALESCE() для одного или двух выражений может быть определена следующим образом:
1. COALESCE ( # 1>) эквивалентно ( # 1>).
2. COALESCE ( # 1>, # 2>) эквивалентно:

WHEN IS NOT NULL

Мы можем ввести рекурсивное определение для n выражений, где n больше либо равно 3, следующим образом: COALESCE ( # 1>, # 2>. n) эквивалентно:

WHEN IS NOT NULL

ELSE COALESCE ( , . n)

Аналогично, NULLIF ( # 1>, # 2>) эквивалентно:

Используйте наиболее компактную форму подобных CASE-выражеиий и не пытайтесь подробно расписать их компоненты.

Избегайте лишних выражений

Обоснование
Современные ядра SQL, как правило, замечательно сообразительны. Но это не всегда было так, поэтому старые SQL-программисты иногда добавляют в конструкцию WHERE лишние предикаты. Например, если в таблице Foobar ни один из столбцов не может принимать значение NULL, то в выражении:

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

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

Механическое удерживание земляных масс: Механическое удерживание земляных масс на склоне обеспечивают контрфорсными сооружениями различных конструкций.

Общие условия выбора системы дренажа: Система дренажа выбирается в зависимости от характера защищаемого.

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

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL