Green-sell.info

Новые технологии
14 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Case access sql

Примеры использования выражения CASE в Transact-SQL

В этой статье мы рассмотрим выражение 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, но сначала разберем исходные данные, которые я буду использовать в примерах, чтобы было более понятно и наглядно.

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

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

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

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

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

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

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

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

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

Как видите, условие WHEN Price = 300 AND ProductId = 1 с виду выполняется, но CASE вернул результат первого выражения, после которого анализ был прекращен, и дело до следующих условий не дошло.

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

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

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

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

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

понедельник, 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). Этот прием позволяет вместо нескольких однотипных запросов написать один и, что часто бывает, неплохо улучшить производительность.

Читать еще:  Контекстное меню access

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

А так, используя “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”:

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

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

Резюме

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

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

Использование языка SQL MS ACCESS;

Язык структурированных запросов (SQL) является стандартным средством для работы с базами данных. Может использоваться как для интерактивной работы с БД, так и включаться в языки программирования. В CУБД Access SQL позволяет:

· создавать, модифицировать или удалять таблицы в базе данных Access;

· создавать или удалять ключи, индексы для таблиц в базе данных Access;

· вставлять, удалять или модифицировать записи таблиц;

· получать сводную информацию о данных в таблицах (число записей, суммы, средние, минимальные, максимальные значения и др.);

· осуществлять поиск и извлекать данные из одной или более таблиц по запросу.

Рассмотрим основные операторы языка SQL для поиска и извлечения информации из базы данных. Для этого используется оператор SELECT, синтаксис которого следующий:

SELECT список имен полей 1

FROM список имен таблиц

[WHERE условие]

[ORDER BY список имен полей 2]

[GROUP BY список имен полей 3]

SELECT, FROM, WHERE, ORDER BY, GROUP BY — ключевые слова;

список имен полей 1 набор имен полей, которые выбираются из одной или нескольких таблиц, указанных в списке имен таблиц, вычисляемых полей, агрегированных функций (например, count, sum, min, max, avg и т.д.). Для выбора всех полей из списка таблиц вместо перечисления их имен можно использовать символ «*». Если имя поля таблицы содержит пробел, т.е. состоит из нескольких слов, то это имя должно заключаться в квадратные скобки;

список имен таблиц — набор имен таблиц, из которых производится выбор информации.

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

Все приведенные далее примеры основаны на использовании БД по библиографии, логическая структура которой приведена на рисунке 2.1.

Примеры:

SELECT * FROM Книга — выбор всех полей всех книг из таблицы «Книга» базы данных по библиографии.

SELECT Название, [Год издания] FROM Книга — выбор полей заголовков (Название ) и года издания (Год издания) всех книг из таблицы «Книга». Имя поля года издания указано в квадратных скобках (имеет пробел).

Таблица «Издательство» Таблица «Книга» Таблица «Автор»

Рис. 2.1. Структура и схема БД по библиографии

Для поиска информации, удовлетворяющей некоторому условию, в операторе SELECT используется раздел WHERE, который имеет следующий синтаксис:

SELECT список имен полей FROM список имен таблиц WHERE условие,

где условиеопределяет критерий поиска информации.

В условии используются имена полей, операции сравнения ( , >=, <>) и специальные операции сравнения IN, LIKE, BETWEЕN. Эти операции могут объединяться с помощью логических операций (And, Or) и задавать сложные условия поиска информации.

Примеры:

SELECT Название, [Год издания] FROM Книга

WHERE [Год издания] > 1991 определяет выбор названий книг, год выпуска которых позже 1991.

SELECT Фамилия, Имя FROM Автор

WHERE Фамилия = ”Иванов” определяет выбор полей имен и фамилий авторов из таблицы «Автор», фамилии которых совпадают с фамилией Иванов. Здесь символ “ используется для задания текстовых констант.

Операция IN сравнивает содержимое поля со списком заданных значений, определяющих критерий поиска информации.

Примеры:

SELECT Название, [Год издания] FROM Книга

WHERE [Год издания] IN (1995, 1996) определяет выбор названий и года издания книг, опубликованных в 1995 или в 1996 гг.

SELECT Фамилия, Имя, Город FROM Автор

WHERE Город IN (“Омск”, “Москва”, “Киев”) определяет выбор авторов, живущих в городах Омск, Москва или Киев.

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

Символами шаблона являются: *, ?, #. Символ * соответствует цепочке символов, ? — соответствует одному символу, # — соответствует одной цифре.

Примеры:

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

КИ? — возможные результаты поиска кий, кит.

12345# — возможные результаты поиска 123455, 123457.

Список диапазона заключается в квадратные скобки, первый и последний символы диапазона отделяются дефисом (-). Диапазон задается в возрастающем порядке.

Примеры:

SELECT Фамилия, Имя, Отчество FROM Автор

WHERE Фамилия LIKE ‘И*’ определяет выбор авторов, фамилии которых начинаются с буквы И.

SELECT Фамилия FROM Автор WHERE Фамилия LIKE [A-K] определяет выбор авторов, фамилии которых начинаются с букв от А до К.

SELECT Название FROM Книга

WHERE Название LIKE “*база данных*” AND [Год издания] = 1996 определяет выбор названий книг, выпущенных в 1996 году и в названии которых присутствует фраза «база данных».

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

Примеры:

SELECT Название, [Год издания] FROM Книга

WHERE [Год издания] Between #01.01.1994# And #30.06.1994# определяет выбор книг, изданных в первой половине 1994 года. Здесь символ # используется для задания констант типа дата.

SELECT [Название издат], адрес, телефон FROM Издательство

WHERE [Название издат] Between ‘Нау’ And ‘Фин’ определяет выбор информации об издательских фирмах, начальные буквы названий которых, расположенные в алфавитном порядке, находятся в диапазоне между ‘Нау’ и ‘Фин’.

Читать еще:  Как убрать фоновый рисунок в powerpoint

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

SELECT список имен полей FROM список имен таблиц [WHERE условие] ORDER BY имя поля 1 [ASC | DESC][, имя поля 2[ASC | DESC]] ,

где имя поля1, имя поля2 поля (из списка имен полей), по которым производится упорядочение. Опция ASCустанавливает порядок сортировкипо возрастанию (данный порядок установлен по умолчанию), DESC устанавливает порядок сортировки по убыванию. Упорядочение может вестись по нескольким полям (например, по фамилии, а затем по имени)

Пример:

SELECT Название FROM Книга

WHERE Название LIKE “*база данных*” AND [Год издания] = 1996

ORDER BY Название определяет выбор названий книг, выпущенных в 1996 году и в названии которых присутствует фраза «база данных», и упорядочивает названия в алфавитном порядке.

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

SELECT список имен полей FROM список имен таблиц [WHERE условие] GROUP BY имя поля 1 [, имя поля 2],

где имя поля1, имя поля2 поля (из списка имен полей), по которым производится группировка записей. Порядок следования полей в этом предложении определяет уровни их группировки. Группировку можно производить для выражений (например, вычисляемых полей), тогда в списке полей этому выражению должно быть присвоено какое-нибудь имя. Для агрегированных функций (count, sum, min, max, avg и т.д.) группировка обязательна.

Пример:

SELECT Фамилия, [Год издания], Sum (Цена) AS [Стоимость]

FROM Книга, Автор

WHERE Автор.[Код автора]= Книга.[Код автора]

GROUPBY Фамилия, [Год издания]

ORDER BY Фамилия, [Год издания] определяет суммарную цену (Стоимость) книг года издания для каждого автора.

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

SELECT список имен полей FROM список имен таблиц WHERE список связей,

где список связей определяет, как таблицы в списке имен таблиц связаны между собой. Например, имя_таблицы1.имя_поля1 = имя_таблицы2.имя_поля2.

Примеры:

SELECT Книга.Название, Издательство. [Название издат]

FROM Книга, Издательство

WHERE Книга. [Код издат]= Издательство. [Код издательства] And

Издательство. [Название издат]=”Наука”

Этот запрос определяет выбор названий книг (Название) из таблицы «Книга» и названий издательств (Название издат) из таблицы издательств (Издательство). Раздел WHERE осуществляет следующее:

· определяет связь между таблицами (выбирается название книги и номер издательства «Код издат» из таблицы «Книга», по номеру издательства «Код издательства» в таблице издательств «Издательство» находится название издательства);

· задает условие для выбора книг, изданных только в издательстве «Наука».

Case access sql

Question

I want to use switch — case statement in T-SQL stored procedure.

Can any one help regarding the same.

case 1 : stmt 1; break;

case 2 : stmt 2; break;

case 3 : stmt 3; break;

Answers

See the following example

DECLARE @TestVal int
SET @TestVal = 3

SELECT
CASE @TestVal
WHEN 1 THEN ‘First’
WHEN 2 THEN ‘Second’
WHEN 3 THEN ‘Third’
ELSE ‘Other’
END

  • Marked as answer by Lukasz Pawlowski — MS Microsoft employee, Moderator Tuesday, January 6, 2009 9:31 PM

All replies

See the following example

DECLARE @TestVal int
SET @TestVal = 3

SELECT
CASE @TestVal
WHEN 1 THEN ‘First’
WHEN 2 THEN ‘Second’
WHEN 3 THEN ‘Third’
ELSE ‘Other’
END

  • Marked as answer by Lukasz Pawlowski — MS Microsoft employee, Moderator Tuesday, January 6, 2009 9:31 PM

I tried this way but dosen’t work when i want to calculate or insert or delete

it can be used to view the result directly without any calculation

  • Proposed as answer by Mephak Thursday, July 1, 2010 9:31 PM

Try something like this.

Insert into mytableB
Select YEAR,
case when PERIOD in (1,2,3) then Q1
when PERIOD in (4,5,6) then Q2
when PERIOD in (7,8,9) then Q3
Else Q4 end ,
Amount, getdate()
From myTableA
Where Year = 2010
and Period = 5

Insert into mySalesB
Select SalesPerson,
case when Sales

  • Proposed as answer by Mephak Thursday, July 1, 2010 9:43 PM

I know this is 3 years old, But its number 1 on google for «switch in t-sql».

I don’t think the OP is asking for a case statement, which can only be used inside a select or update statement. I think he truly wants a switch statement would break down into if/else logic. I don’t think t-sql has this. You need to use If statements.

Here’s an example that might help:

declare @var as int
declare @EvenNumbers as table(
EvenNum int
)
declare @OddNumbers as table(
OddNum int
)

if(@var % 2 = 0) begin
insert into @EvenNumbers(EvenNum) values (@var)
print ‘It»s even!’
end

if(@var % 2 = 1) begin
insert into @OddNumbers(OddNum) values(@var)
print ‘it»s odd!’
end

select *
from @EvenNumbers
select *
from @OddNumbers
«Winners never quit, and quitters never win.»

Команды и выражения CASE языка PL/SQL на примерах

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

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

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 и т. д.

Читать еще:  Remote desktop web access настройка 2020

Приведем пример простой команды 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 , о которой мы поговорим в следующем разделе.

Поисковая команда 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 .

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

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

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

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

Ссылка на основную публикацию
Adblock
detector