Основи Transact-SQL

Мова Т-SQL призначений для управління наборами даних З цієї причини він не володіє деякими характерними рисами традиційних мов, які необхідні для програмування додатків Якщо ви вже давно займаєтеся створенням додатків, то напевно протиставите мислення програмування в Т-SQL і в інших мовах, таких як VB, C # і Java

Пакети T-SQL

Запитом називають одну інструкцію Т-SQL, а пакетом – їх набір Вся послідовність інструкцій пакета відправляється серверу з клієнтських додатків як одна цілісна одиниця

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

Переривання виконання пакету

Файл сценарію SQL і вікно аналізатора запитів (Query Analyzer) може містити кілька пакетів В даному випадку всі пакети поділяють ключові слова термінаторів За замовчуванням цим ключовим словом є GO, і воно повинно бути єдиним в рядку Всі інші символи (навіть коментарі) нейтралізують роздільник пакета

Роздільник пакетів насправді є функцією Management Studio, а не самого сервера Його можна змінити на сторінці Query Execution діалогового вікна властивостей програми, але я не рекомендував би цього робити (принаймні, друзям)

Інструкції DDL

Деякі інструкції DDL мови Т-SQL, такі як Create Procedure, обовязково повинні бути першими інструкціями пакета Дуже довгі сценарії, які створюють безліч обєктів, часто вимагають наявності декількох роздільників пакетів Так як SQL Server окремо розбирає синтаксис по пакетах, таке наявність безлічі роздільників допомагає локалізувати помилки

Перемикання між базами даних

В інтерактивному режимі роботи поточна база даних завжди відображається на панелі інструментів, і в будь-який момент може бути змінена У програмному коді поточна база визначається за допомогою ключового слова USE Це ключове слово в пакеті вказує, з якою саме базою даних буде виконуватися робота, починаючи з поточної точки:

USE СНА2

На практиці рекомендується явно визначати поточну базу даних за допомогою команди USE – немає чого сподіватися на користувача

Виконання пакетів

Пакет може бути виконаний кількома способами

■ Сценарій SQL в повному обсязі (тобто всі вхідні в нього пакети) може бути виконаний шляхом відкриття файлу sql в редакторі SQL утиліти Manage ment Stu dio і натиснення клавіші (Або клацання на кнопці Execute панелі інструментів, або вибору в меню пункту Query1^ Execute) (Я настроїв свою операційну систему Windows так, щоб при подвійному натисканні на файлі Sql автоматично запускався аналізатор запитів)

■ У редакторі SQL утиліти Management Studio можуть бути виконані й окремі інструкції SQL Для цього їх потрібно виділити і натиснути клавішу (Або клацнути на кнопці Execute панелі інструментів, або вибрати в меню пункт Query1^Execute)

■ У додатку пакет Т-SQL можна виконати за допомогою ADO або ODBC

Про Сценарій Т-SQL може бути виконаний за допомогою утиліти командного рядка SQLCmd з передачею їй імені файлу sql як параметр

■ Утиліта SQLCmd має кілька параметрів і може бути легко налаштована практично для будь-яких потреб

Додаткова Детально про утиліту SQLCmd см в главі 6, присвяченій Management Studio

інформація

Виконання процедури, що зберігається

У пакеті SQL збережена процедура викликається за допомогою ключового слова Ехес При цьому слід дотримуватися ряду правил Так як розриви рядків для SQL Server не мають сенсу, то команда Ехес означає закінчення попередньої інструкції

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

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

Sp_help

Ехес sp_help

У цьому розділі ми розглянули тільки використання команди Ехес в пакеті Більш детальна інформація про творче використання ключового слова Ехес міститься в розділі Динамічний SQL.

Форматування в T-SQL

Протягом всієї цієї книги програмний код відформатований для поліпшення наочності в цьому розділі ми розглянемо ключові моменти форматування

Завершення інструкції

Стандарт ANSI SQL вимагає приміщення в кінці кожної інструкції крапки з комою У той же час при програмуванні на мові Т-SQL крапка з комою не обовязкова При цьому слід керуватися декількома правилами

■ Не розміщуйте її після оператора try end

■ Не розміщуйте її після умови if

■ Обовязково розміщуйте її після общетаблічних виразів сте

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

Продовження рядків

Інструкції Т-SQL за своєю природою мають властивість бути довгими Деякі запити останньої глави з численними обєднаннями та підзапитами займають цілу сторінку Особисто мені подобається, що в Т-SQL ігноруються символи пробілів і кінця рядка Це означає, що довга інструкція може бути продовжена на наступному рядку без необхідності наявності будь-якого спеціального символу Ця властивість дозволяє значною мірою підвищити читаність програмного коду

В інших реалізаціях SQL, таких як Access, для завершення інструкції потрібна наявність крапки з комою SQL Server допускає її використання, але вважає це необовязковим

Коментарі

Мова Т-SQL допускає використання в одному пакеті коментарів двох стилів: ANCI та мови С Перший з них починається з двох дефісів і закінчується в кінці рядка:

– Це коментар стилю ANSI

Також коментарі стилю ANSI можуть вставлятися в кінці рядка інструкції:

Select FirstName, LastName – видобувні стовпці FROM Persons – вихідна таблиця

Where LastName Like Hal%; – Обмеження на рядки

Редактор SQL може застосовувати і видаляти коментарі у всіх виділених рядках Для цього потрібно відповідно вибрати команду меню Edit ^ Advanced ^ Comment Out ( або ) або Edit ^ Advanced1^ Remove Comments ( або )

Коментарі стилю мови С починаються з косою риси й зірочки (/ *) і закінчуються тими ж символами в зворотній послідовності Цей тип коментарів краще використовувати для коментування блоків рядків, таких як заголовки чи великі тестові запити / *

Тригер вставки таблиці Order Пол Нільсен

версія 10 21 липня 2006 року логіка: і тд

версія 11: 31 липня 2006 року, додано те-то і те-то * /

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

Налагодження T-SQL

Коли редактор SQL виявляє помилку, він відображає її характер і номер рядка в пакеті Двічі клацнувши на помилку, можна відразу ж переміститися до відповідної рядку

Досить часто помилка розташована не в тому слові, яке зазначено в повідомленні, – все залежить від того, як розбиралася відповідна інструкція Зазвичай фактично помилка розташована безпосередньо до або після вказаного в повідомленні місця – в будь-якому випадку в повідомленні місце вказано досить точно

SQL Server пропонує кілька команд, що полегшують налагодження пакетів Зокрема, команда print відправляє повідомлення без генерації результуючого набору даних Особисто я вважаю команду print особливо цінної для відстеження ходу виконання пакета Коли аналізатор запитів знаходиться в режимі сітки, виконайте наступний пакет:

Select 3

Print 6

Результуючий набір даних відобразиться в сітці і складатиметься з одного рядка Водночас у вкладці Messages відобразиться наступний результат:

(1 row(s) affected)

6

Іноді корисно призупиняти виконання програми, щоб побачити блокування або вміст обєктів Команда pause дозволяє призупинити виконання пакету на заданий час Наприклад, при виконанні наступного коду його другий рядок виводу відобразиться після двухсекундной паузи:

Print 1 Початок ;

waitfor delay *00:00:02

Print Кінець1

Результат виконання коду:

Початок

Кінець

Ключовим моментом є те, що в утиліту Management Studio версії SQL Новинка Server 2005 не включено відладчик мови Т-SQL, – він присутній в пакеті

2005 аVisual Studio 2005 Якщо в майбутньому відладчик буде включений в який-небудь пакет

оновлень, я повідомлю про це на сайті www SQLServerBible com

Змінні

Будь-яка мова вимагає для тимчасового зберігання значень в памяті наявності змінних Змінні Т-SQL створюються за допомогою команди declare, за якою слідують імя змінної і її тип Використовувані для змінних типи даних в точності збігаються з існуючими в таблицях До цього можна додати табличний тип і тип SQLVariant В одній команді declare через кому може бути перераховано кілька змінних

Значення за замовчуванням і область визначення змінних

Область визначення змінних (тобто термін їх життя) поширюється тільки на поточний пакет За замовчуванням щойно створені змінні містять порожні значення null і до включення у вирази повинні бути ініціалізовані

У наступному сценарії створюються дві тестові змінні, при цьому продемонстровані їх область визначення і значення за замовчуванням Весь сценарій є одним виконуваним файлом, хоча з технічної точки зору складається з двох пакетів (розділених командою GO) Відразу після сценарію продемонстровані три його інструкції SELECT:

DECLARE @Test INT,

@TestTwo NVARCHAR(25)

SELECT @Test, @TestTwo

SET @Test = 1

SET @ TestTwo = значення;

SELECT @Test, @TestTwo

Go

SELECT @Test as BatchTwo, @TestTwo

NULL  NULL

(1 row(s) affected)

Value

1 значення

(1 row(s) affected)

Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable &quot@Test&quot.

Перша інструкція SELECT повертає два порожніх значення Після того як змінні були ініціалізовані, вони повертають присвоєні їм значення Після завершення пакета результатом наступної інструкції SELECT є повідомлення про помилку № 137

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

Використання команд set і select

Команди SET і SELECT можуть використовуватися для присвоєння значень змінним Основною відмінністю між ними є те, що команда SELECT може витягувати інформацію з джерела даних (тобто таблиці, підзапиту, уявлення, і тп) і включати в себе інші інструкції SELECT, в той час як команда SET обмежена витяганням даних з виразів Як одна, так і інша команда може містити функції Використовуйте простішу команду SET, коли потрібно присвоїти змінної результат функції або константу і не потрібно розглядати який-небудь джерело даних

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

USE Family

Declare ©TempID INT,

@TempLastName VARCHAR(25)

SET @TempID = 99

SELECT

@TempID = PersonID,

@TempLastName = LastName

FROM Person

ORDER BY PersonID

SELECT @TempID, @TempLastName

Результат виконання пакету:

32 @code last:Campbell

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

Якщо інструкція SELECT не вертає ні одного рядка, то на змінні не виявляється ніякого впливу Наступний запит не вертає значень, оскільки записи з ідентифікатором 100 в таблиці Person НЕ існує З цієї причини змінної @ TempIDvariable присвоюється значення останньої існуючої рядки, при цьому змінна прізвища зберігає початкове пусте значення:

Declare @TempID INT,

@TempLastName VARCHAR(25)

SET @TempID = 99

SELECT @TempID = PersonID,

@TempLastName = LastName FROM Person WHERE PersonID = 100 ORDER BY PersonID

SELECT @TempID, @TempLastName

99 @code last:NULL

Умовний відбір

Наступна інструкція SELECT містить пропозицію WHERE, і її синтаксис правильний, хоча для деяких може виглядати незвично:

SELECT @ змінна = вираз WHERE булево_вираженіе

У даному випадку пропозиція WHERE функціонує як умовний оператор if Якщо булево вираз істинно, то змінної присвоюється значення, в іншому випадку інструкція SELECT все одно виконується, але значення змінної не змінюється

Використання змінних у запитах SQL

Одним з моїх улюблених властивостей мови Т-SQL є те, що змінні можуть використовуватися в запитах без необхідності створення складних динамічних рядків, вбудовують змінні в програмний код Динамічний SQL продовжує своє існування, але одиночне значення можна змінити простіше – за допомогою змінної

Скрізь, де в запиті може використовуватися вираз, може використовуватися і змінна У наступному прикладі продемонстровано використання змінної в реченні WHERE: USE OBXKites

DECLARE @ProductCode CHAR(10)

SET @Code = 1001;

SELECT ProductName FROM Product

WHERE Code = @ProductCode

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

Name

Basic Box Kite 21 inch

Змінні з множинним присвоєнням

Змінні з множинним присвоєнням – це вражаючий метод, що дозволяє додавати змінну до самої себе за допомогою інструкції SELECT і підзапиту

У цьому розділі буде продемонстрований ряд прикладів з реального життя Так як це кілька незвичайне використання інструкції SELECT, дозвольте привести її базову форму:

SELECT @ змінна = @ змінна + dстолбец FROM (управляемая_табліца) as d

До змінної додається кожен рядок керованої таблиці, перетворюючи вертикальний стовпець в горизонтальний список

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

У наступному прикладі створюється список дат подій туру Outer Banks Lighthouses, пропонованого в навчальній базі даних Cape Hatter as Ad ventures:

USE CHA2

DECLARE

@EventDates VARCHAR(1024)

SET @EventDates =

SELECT ©EventDates = @EventDates + CONVERT(VARCHAR(15), ad,107 ) +

FROM (select DateBegin as [d] from Event join Tour

on EventTourID = TourTourlD WHERE Tour[Name] = Outer Banks Lighthouses) as a

SELECT Left(@EventDates, Len(@EventDates)-1)

AS Outer Banks Lighthouses Events;

Результат виконання пакету:

Outer Banks Lighthouses Events

Feb 02, 2001 Jun 06, 2001 Jul 03, 2001 Aug 17, 2001

Oct 03, 2001 Nov 16, 2001

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

Джерело: Нільсен, Пол 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>

*

*