1. Главная страница » Компьютеры » Oracle case when описание

Oracle case when описание

Автор: | 16.12.2019

В этой статье мы рассмотрим выражение CASE языка Transact-SQL, Вы узнаете, что это за выражение, его синтаксис, а также мы разберем несколько примеров использования выражения CASE.

CASE в Transact-SQL

CASE – это инструкция, которая проверяет список условий и возвращает соответствующий результат. Если говорить в целом о программировании, то CASE – это что-то вроде многократного использования конструкции IF-ELSE, во многих языках есть похожая конструкция SWITCH, так вот CASE, как я уже отметил, делает примерно то же самое.

Выражение CASE можно использовать практически в любой инструкции T-SQL, где есть возможность использовать допустимые выражения, например: SELECT, UPDATE, WHERE, SET и даже в ORDER BY.

CASE имеет два так называемых формата:

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

Синтаксис CASE в Transact-SQL

Простое выражение CASE

Поисковое выражение CASE

Описание параметров:

  • input_expression — выражение, которое необходимо проверить в простом формате CASE;
  • WHEN when_expression — выражение, с которым сравнивается input_expression, в случае с простым форматом. Тип данных when_expression должен быть такой же, как и у input_expression, или хотя бы неявно преобразовываться;
  • THEN result_expression — выражение, которое будет возвращено, если текущее условие выполняется;
  • ELSE else_result_expression – дополнительный параметр ELSE, который предназначен для случаев, когда ни одно из перечисленных в CASE условий не выполнилось. Это необязательный параметр. Если ELSE не указано, а условия не выполнились, вернётся NULL;
  • WHEN Boolean_expression — логическое выражение, используемое в поисковом формате CASE, которое служит для вычисления результата. Это своего рода проверочное условие и таких условий может быть несколько.

CASE возвращает результат первого выражения (THEN result_expression), условие которого выполнилось, т.е. WHEN возвращает TRUE. Таким образом, если CASE содержит несколько эквивалентных условий WHEN, которые будут возвращать TRUE, вернется результат (указанный в THEN) первого выражения.

Тип данных возвращаемого результата выражением CASE, будет соответствовать наиболее приоритетному типу данных из набора типов в выражениях result_expressions и else_result_expression.

Примеры CASE в Transact-SQL

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

Исходные данные для примеров

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

Примечание! Если Вы начинающий программист и не знаете, что делает вышеуказанная инструкция, то рекомендую Вам почитать мою книгу, посвященную основам языка Transact-SQL – «Путь программиста T-SQL» – это своего рода самоучитель по языку Transact-SQL, в котором я очень подробно рассказываю обо всех конструкциях языка, начиная с простых и заканчивая продвинутыми.

Пример простого выражения CASE в инструкции SELECT

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

Пример поискового выражения CASE в инструкции SELECT

Следующий запрос эквивалентен предыдущему, но в нем используется уже поисковый формат CASE.

Как видим, результат точно такой же.

Давайте немного усложним запрос, добавим в него некий анализ цены (Price). Также я здесь покажу, что в случае, если по факту у нас может выполниться несколько условий, результат будет возвращен первого выражения с TRUE (т.е. условие выполнилось), выражения, идущие после, не обрабатываются.

Как видите, условие WHEN Price = 300 AND Product >

Пример выражения CASE в инструкции SET

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

Примечание! Про основы программирования на языке T-SQL в Microsoft SQL Server можете почитать в материале «Основы программирования на T-SQL».

Команда CASE позволяет выбрать для выполнения одну из нескольких последовательностей команд. Эта конструкция присутствует в стандарте SQL с 1992 года, хотя в Oracle SQL она не поддерживалась вплоть до версии Oracle8i, а в PL/SQL — до версии Oracle9i Release 1. Начиная с этой версии, поддерживаются следующие разновидности команд CASE :

  • Простая команда CASE — связывает одну или несколько последовательностей команд PL/SQL с соответствующими значениями (выполняемая последовательность выбирается с учетом результата вычисления выражения, возвращающего одно из значений).
  • Поисковая команда CASE — выбирает для выполнения одну или несколько последовательностей команд в зависимости от результатов проверки списка логических значений. Выполняется последовательность команд, связанная с первым условием, результат проверки которого оказался равным TRUE .
Читайте также:  Asus amd radeon hd 5450

NULL или UNKNOWN?

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

В PL/SQL это утверждение истинно, но в более широком контексте реляционной теории считается некорректным говорить о возврате NULL из логического выражения. Реляционная теория говорит, что сравнение с NULL следующего вида:

дает логический результат UNKNOWN , причем значение UNKNOWN не эквивалентно NULL . Впрочем, вам не стоит особенно переживать из-за того, что в PL/SQL для UNKNOWN используется обозначение NULL . Однако вам следует знать, что третьим значением в трехзначной логике является UNKNOWN . И я надеюсь, что вы никогда не попадете впросак (как это бывало со мной!), используя неправильный термин при обсуждении трехзначной логики с экспертами в области реляционной теории.

Кроме команд CASE , PL/SQL также поддерживает CASE -выражения. Такое выражение очень похоже на команду CASE , оно позволяет выбрать для вычисления одно или несколько выражений. Результатом выражения CASE является одно значение, тогда как результатом команды CASE является выполнение последовательности команд PL/SQL.

Простые команды CASE

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

Ветвь ELSE здесь не обязательна. При выполнении такой команды PL/SQL сначала вычисляет выражение, после чего результат сравнивается с результат_1 . Если они совпадают, то выполняются команды_1 . В противном случае проверяется значение результат_2 и т. д.

Приведем пример простой команды CASE , в котором премия начисляется в зависимости от значения переменной employee_type :

В этом примере присутствует явно заданная секция ELSE , однако в общем случае она не обязательна. Без секции ELSE компилятор PL/SQL неявно подставляет такой код:

Иначе говоря, если не задать ключевое слово ELSE и если никакой из результатов в секциях WHEN не соответствует результату выражения в команде CASE , PL/SQL инициирует исключение CASE_NOT_FOUND . В этом и заключается отличие данной команды от IF . Когда в команде IF отсутствует ключевое слово ELSE , то при невыполнении условия не происходит ничего, тогда как в команде CASE аналогичная ситуация приводит к ошибке.

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

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

Вернувшись к команде IF. THEN. ELSIF , реализующей ту же логику, вы увидите, что в команде CASE определена секция ELSE , тогда как в команде IF–THEN–ELSIF ключевое слово ELSE отсутствует. Причина добавления ELSE проста: если ни одно из условий начисления премии не выполняется, команда IF ничего не делает, и премия получается нулевой. Команда CASE в этом случае выдает ошибку, поэтому ситуацию с нулевым размером премии приходится программировать явно.

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

Приведенная выше команда CASE TRUE кому-то покажется эффектным трюком, но на самом деле она всего лишь реализует поисковую команду CASE , о которой мы поговорим в следующем разделе.

Читайте также:  1С розница импорт номенклатуры

Поисковая команда CASE

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

Поисковая команда CASE , как и простая команда, подчиняется следующим правилам:

  • Выполнение команды заканчивается сразу же после выполнения последовательности исполняемых команд, связанных с истинным выражением. Если истинными оказываются несколько выражений, то выполняются команды, связанные с первым из них.
  • Ключевое слово ELSE не обязательно. Если оно не задано и ни одно из выражений не равно TRUE , инициируется исключение CASE_NOT_FOUND .
  • Условия WHEN проверяются в строго определенном порядке, от начала к концу.

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

Если оклад некоего сотрудника равен 20 000, то первые два условия равны FALSE , а третье — TRUE , поэтому сотрудник получит премию в 1500 долларов. Если же оклад равен 21 000, то результат второго условия будет равен TRUE , и премия составит 1000 долларов. Выполнение команды CASE завершится на второй ветви WHEN , а третье условие даже не будет проверяться. Стоит ли использовать такой подход при написании команд CASE — вопрос спорный. Как бы то ни было, имейте в виду, что написать такую команду возможно, а при отладке и редактировании программ, в которых результат зависит от порядка следования выражений, необходима особая внимательность.

Логика, зависящая от порядка следования однородных ветвей WHEN , является потенциальным источником ошибок, возникающих при их перестановке. В качестве примера рассмотрим следующую поисковую команду CASE , в которой при значении salary , равном 20 000, проверка условий в обеих ветвях WHEN дает TRUE :

Представьте, что программист, занимающийся сопровождением этой программы, легкомысленно переставит ветви WHEN , чтобы упорядочить их по убыванию salary . Не отвергайте такую возможность! Программисты часто склонны «доводить до ума» прекрасно работающий код, руководствуясь какими-то внутренними представлениями о порядке. Команда CASE с переставленными секциями WHEN выглядит так:

На первый взгляд все верно, не так ли? К сожалению, из-за перекрытия двух ветвей WHEN в программе появляется коварная ошибка. Теперь сотрудник с окладом 20 000 получит премию 1000 вместо положенных 1500. Возможно, в некоторых ситуациях перекрытие между ветвями WHEN желательно и все же его следует по возможности избегать. Всегда помните, что порядок следования ветвей важен, и сдерживайте желание доработать уже работающий код — «не чините то, что не сломано».

Поскольку условия WHEN проверяются по порядку, можно немного повысить эффективность кода, поместив ветви с наиболее вероятными условиями в начало списка. Кроме того, если у вас есть ветвь с «затратными» выражениями (например, требующими значительного процессорного времени и памяти), их можно поместить в конец, чтобы свести к минимуму вероятность их проверки. За подробностями обращайтесь к разделу «Вложенные команды IF ».

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

Вложенные команды CASE

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

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

Выражения CASE

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

Синтаксис этих двух разновидностей выражений CASE :

Выражение CASE возвращает одно значение — результат выбранного для вычисления выражения. Каждой ветви WHEN должно быть поставлено в соответствие одно результирующее выражение (но не команда). В конце выражения CASE не ставится ни точка с запятой, ни END CASE . Выражение CASE завершается ключевым словом END .

Читайте также:  Dual band wi fi что это

Далее приводится пример простого выражения CASE , используемого совместно с процедурой PUT_LINE пакета DBMS_OUTPUT для вывода на экран значения логической переменной.
(Напомним, что программа PUT_LINE не поддерживает логические типы напрямую.) В этом примере выражение CASE преобразует логическое значение в символьную строку, которая затем выводится процедурой PUT_LINE :

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

Выражение CASE может применяться везде, где допускается использование выражений любого другого типа. В следующем примере CASE-выражение используется для вычисления размера премии, умножения его на 10 и присваивания результата переменной, выводимой на экран средствами DBMS_OUTPUT :

В отличие от команды CASE , если условие ни одной ветви WHEN не выполнено, выражение CASE не выдает ошибку, а просто возвращает NULL .

CASE expressions let you use IF . THEN . ELSE logic in SQL statements without having to invoke procedures. The syntax is:


Description of the illustration case_expression.gif


Description of the illustration simple_case_expression.gif


Description of the illustration searched_case_expression.gif


Description of the illustration else_clause.gif

In a simple CASE expression, Oracle Database searches for the first WHEN . THEN pair for which expr is equal to comparison_expr and returns return_expr . If none of the WHEN . THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr . Otherwise, Oracle returns null. You cannot specify the literal NULL for every return_expr and the else_expr .

In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr . If no condition is found to be true, and an ELSE clause exists, Oracle returns else_expr . Otherwise, Oracle returns null.

Oracle Database uses short-circuit evaluation . That is, for a simple CASE expression, the database evaluates each comparison_expr value only before comparing it to expr , rather than evaluating all comparison_expr values before comparing any of them with expr . Consequently, Oracle never evaluates a comparison_expr if a previous comparison_expr is equal to expr . For a searched CASE expression, the database evaluates each condition to determine whether it is true, and never evaluates a condition if the previous condition was true.

For a simple CASE expression, the expr and all comparison_expr values must either have the same datatype ( CHAR , VARCHAR2 , NCHAR , or NVARCHAR2 , NUMBER , BINARY_FLOAT , or BINARY_DOUBLE ) or must all have a numeric datatype. If all expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

For both simple and searched CASE expressions, all of the return_expr s must either have the same datatype ( CHAR , VARCHAR2 , NCHAR , or NVARCHAR2 , NUMBER , BINARY_FLOAT , or BINARY_DOUBLE ) or must all have a numeric datatype. If all return expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

The maximum number of arguments in a CASE expression is 255. All expressions count toward this limit, including the initial expression of a simple CASE expression and the optional ELSE expression. Each WHEN . THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the return_expr itself is a CASE expression.

Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

"Numeric Precedence" for information on numeric precedence

COALESCE and NULLIF for alternative forms of CASE logic

Oracle Data Warehousing Guide for examples using various forms of the CASE expression

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*

code