Використання виразів і скалярних функцій

Коли мій син Дейвід був молодший, він конструював неймовірних монстрів з окремих блоків KNEX Якщо ви ще не знаєте, що таке KNEX, знайдіть відповідний сайт за допомогою Google і подивіться, які неймовірні речі діти здатні створювати з його допомогою

Що робить конструктор KNEX настільки прекрасним, так це те, що будь-який блок можна вставити в інший блок Така взаємоповязаність окремих блоків і забезпечує конструктору гнучкість Аналогічно, взаємоповязаність виразів і функцій SQL забезпечує цій мові таку силу і гнучкість

Вирази можуть отримувати дані з підзапитів, обробляти складну логіку, перетворювати типи і маніпулювати даними Якщо основний секрет майстерності розробника баз даних полягає в умінні створювати запити, то безумовно головним його арсеналом будуть вираження і скалярні функції

Вираз – це деяка комбінація констант, функцій і формул, що повертає одне значення Вирази можуть бути такими простими, як зумовлене число, і такими складними, як вираження CASE, що включають в себе масу функцій і формул

Вирази можуть бути задіяні в безлічі елементів синтаксису SQL Практично скрізь, де може використовуватися значення, можна вставити вираз Це можуть бути значення стовпців, пропозиції JOIN, WHERE, HAVING і ORDER BY Водночас вираження не можна підставляти замість імен обєктів, таких як таблиці або стовпці

Інструкції та вирази SQL не залежить від ре-Рада гістре символів – вони будуть виконані оди

наково, якщо буде використаний верхній або нижній регістр або їх комбінація

Створення виразів

Рис 81 Створення вираження і призначення псевдоніма в конструкторі запитів утиліти Management Studio

Вирази SQL можна створити практично з нескінченного списку констант, змінних, операторів і функцій, як показано в табл 81 На рис 81 проілюстровано створення вираження за допомогою графічного інтерфейсу і призначення стовпцях псевдонімів

Підзапити розглянуті в главі 10, змінні-в главі 18, а для користувача функції детально описані в главі 22

Оператори

Незважаючи на те що сенс багатьох констант, операторів і виразів очевидний і не відрізняється від інших мов програмування, деякі з них мають спеціальне тлумачення

■ Математичний оператор ділення по модулю (%) повертає залишок від ділення Математичні функції floor () і ceiling () повертають округлене більший чи менший ціле число, повязані з ним Функція floor () є еквівалентом функції int () мови Basic:

SELECT 15%4 as Modulo,

FLOOR(125) as [Floor], CEILING(125) as [Ceiling]

Результат запиту буде наступним:

Modulo               Floor Ceiling

3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 12

■ Оператор + використовується як в математичних виразах, так і для конкатенації рядків Цей оператор відрізняється від символу MS-DOS, використовуваного для строковой конкатенації (&):

SELECT 123 + 456 as Addition,

1abc + defg as Concatenation

Результат буде наступним:

Addition Concatenation

579                  abcdefg

Дані з стовпців таблиць і строкових літералів можна обєднувати оператором конкатенації для повернення довільних даних:

Select Product: + ProductName as [Product]

From Product

Результат буде наступним:

Product

Product: Basic Box Kite 21 inch Product: Dragon Flight Product: Sky Dancer

Бінарні оператори

Бінарні оператори використовують для маніпуляцій двійкові числа Їх рідко використовують в транзакційних базах даних, однак вони довели свою незамінність у деяких операціях з метаданими Наприклад, одним із способів визначення, які з стовпців були оновлені тригером (програмою, яка автоматично виконується при вставці, оновлення або видалення даних докладно вона розглянута в розділі 23), є використання функції columns_updated (), яка повертає двійкове подання цих стовпців

Потім тригер може протестувати повернене цією функцією значення за допомогою бінарних операторів і відреагувати на поновлення кожного з стовпців

Булеві бітові оператори (and, or і not) є основним будівельним блоком цифрової електроніки та довічного програмування У той час як в цифровій електроніці булеві оператори працюють з окремими битами, бінарні оператори працюють з кожним бітом значень цілочисельних типів (int, smallint, tinyint і bit)

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

Булев оператор AND, представлений символом амперсанда (&), повертає значення true (тобто істина’), якщо обидва значення рівні true Якщо будь-який з операндів або обидва мають значення false (тобто брехня), то результатом виразу буде також false Наприклад, результатом вираження SELECT 1 & 1

буде 1

А ось ще один приклад:

SELECT 1&amp0

Тепер результатом буде 0

Якщо операндами є цілі числа, то битовая операція виконується над кожною парою відповідних значень в двійковому поданні Проілюструємо це на прикладі:

—          3 =          011

—          5 =          101

— AND             

—          1 =          001

Таким чином, інструкція

SELECT 3&amp5

в результаті дасть одиницю

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

Булев оператор OR, представлений символом вертикальної риси (|), повертає значення true, якщо будь-який з операндів (або обидва відразу) має значення true Наприклад, результатом вираження SELECT 1 | 1 буде одиниця

Наступна інструкція, що комбінує помилкове і істинне значення

SELECT l|0,

також в результаті дасть одиницю

Якщо операндами є цілі числа, то битовая операція виконується над кожною парою відповідних значень в двійковому поданні Проілюструємо це на прикладі:

— 3 = 011 — 5 = 101

— OR  

— 7 = 111

Таким чином, інструкція

SELECT 3|5

в результаті дасть число 7

Булев оператор Exclusive OR

Булев оператор Exclusive OR, представлений символом галочки (ж), Повертає значення true, якщо будь-який з операндів має значення true, але не обидва одночасно Його використання аналогічно застосуванню оператора OR до двох парам операторів AND, до кожного операнду яких, в свою чергу, застосований оператор NOT Хоча це досить просто реалізувати в цифровій електроніці, в програмі цей оператор використовувати ще легше

Результатом виразу SELECT 1Ж1 буде 0

Водночас поєднання помилкового і істинного значень SELECT 0 * 1 дасть у результаті 1

Бінарний оператор not

Останній бінарний оператор, представлений символом тильди (~), є двійковій функцією NOT Зазвичай цей оператор застосовується до одного з операндів для коригування результату операцій AND або OR В даному випадку все дещо інакше Оператор NOT виконує логічне звернення кожного біта в вираженні, а результат залежить від довжини самого вираження Наведемо приклад:

DECLARE @ А BIT

SET @ А = 1 SELECT ~ @ А

Результатом буде нуль

Бінарний оператор NOT не підходить для використання в булевих виразах, таких як умова IF Наприклад, наступний код некоректна:

SELECT * FROM Product WHERE -(1=1)

Слід зауважити, що оператор NOT може служити доповненням до інших булевим операторам

Оператор case

Команда CASE в SQL Server – гнучке і зручний засіб створення динамічних виразів Якщо ви програміст, то безсумнівно використовуєте команду CASE в інших мовах програмування Однак команда CASE в даному випадку трохи відрізняється Вона використовується не для програмного перемикання управління, а для логічного визначення значення виразу на основі заданої умови, подібно функції if О в інших мовах програмування

Подібно до всіх інших виразів, CASE не може автоматично задати імя стовпця Таким чином, як правило, кожен вираз CASE має псевдонім

Коли програміст пише текст процедури, частина формули може змінюватися в залежності від даних При процедурному мисленні в даному випадку краще організувати цикл по рядках і використовувати безліч операторів if для вибору коректної формули У той же час використання виразу case для виконання різних обчислень і операцій в одному запиті дозволяє SQL Server оптимізувати і значно прискорити процес

Так як оператор CASE повертає вираз, його можна використовувати в будь-якому місці будь-якої інструкції SQL DML (SELECT, INSERT, UPDATE і DELETE), де може вико-

тися звичайний вираз (наприклад, вирази для стовпців, а також пропозиції JOIN, WHERE, HAVING і ORDER BY)

Оператор CASE має дві форми: просту і булеву Вони будуть описані в наступних розділах

Проста форма оператора case

У простій формі оператора CASE спочатку представлено саме значення, після чого перераховуються всі тестові значення Однак ця форма обмежена тим, що можна використовувати тільки порівняння на предмет рівності Даний оператор CASE послідовно перевіряє всі умови WHEN і повертає значення THEN з першого рядка, в якій виконається умова WHEN

У наступному прикладі, заснованому на базі даних OBXKites, один тип (поле customer type) призначається за замовчуванням для всіх нових клієнтів, при цьому в стовпець is default заноситься значення true Оператор CASE порівнює значення в стовпці isdefault з усіма можливими значеннями біта, після чого повертає символьну рядок 1 default type або 1 possible 1 залежно від поточного значення:

USE OBXKites

SELECT CustomerTypeName,

CASE [IsDefault]

WHEN 1 THEN default type

WHEN 0 THEN possible

ELSE –

End as AssignStatus From CustomerType

Буде отримано наступний результат:

CustomerTypeName AssignStatus

Preferred             possible

Wholesale            possible

Retail                   default type

Оператор CASE завершується ключовим словом END і псевдонімом У даному прикладі оператор CASE тестував значення стовпця isdef ault, але формував стовпець AssignStatus в результуючому наборі даних інструкції SELECT

Булева форма оператора case

Булева форма оператора CASE більш гнучка, ніж проста У ній кожен оператор WHEN має свою умову Таким чином, є можливість не тільки використовувати умовні оператори, відмінні від рівності, але і посилатися на різні стовпці

SELECT

CASE

WHEN 1 <0 THEN 'Реальність примарна.'

WHEN GetDate() = 11/30/2005

THEN Дейвід отримав водійські права’

WHEN 1> 0 THEN Життя триває’

END AS RealityCheck

Результат запиту буде отриманий, коли буде виконана перевірка шістнадцятиріччя Дейвіда:

Дейвид отримав водійські права

Як і в простій формі оператора CASE, виконується оператор THEN в першому рядку, в якому умова WHEN було виконано В даному випадку, навіть якщо одиниця раптом виявиться менше нуля (тільки уявіть), оператор CASE поверне рядок Життя триває. Коли Дейвід виповниться 16 років, оператор повідомить про отримання ним водійських прав Якщо жодна з перерахованих подій не відбудеться, ми отримаємо повідомлення, що життя триває

Наведений як приклад код продемонстрував, що булева форма оператора CASE більш гнучка, ніж проста У прикладі були використані різні оператори порівняння, при цьому в операторі WHEH порівнювалися різні дані

Булева форма оператора CASE може містити складні умови, в тому числі оператори or і and У наступному прикладі для створення оператора CASE використаний пакет (у тому числі задіяні змінні Т-SQL, які ми розглянемо в розділі 18), а саме вираження CASE містить оператори and і between:

DECLARE @b INT, @q INT SET @b = 2007 SET @q = 25 Select CASE

WHEN @b = 2 007 AND @q BETWEEN 10 AND 3 0 THEN 1 ELSE NULL END AS Test

Результат виконання цього пакету буде наступним:

Test

1

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*