Параметричне визначення порядку сортування даних

Іцик Бен-Ган



Порядок сортування результатів обробки запиту можна задати в якості параметра.

У різних мережевих форумах часто обговорюється питання: як отримати відсортовані результати виконання запиту, передаючи збереженій процедурі деякий параметр? Я зібрав кілька рішень цієї задачі, запропонованих талановитими програмістами. Основна частина ідей, викладених у цій статті, належить власникам звання SQL Server MVP Брюсу П. Марголіну і Нейлу Пайка, а одне чудово дотепне рішення запропонував Річард Ромлі. Деякі з представлених у цій статті рішень разом з безліччю рішень інших завдань, пов'язаних з SQL Server, можна знайти в книзі Нейла Пайка "SQL Server: Common Problems, Tested Solutions ", що вийшла у видавництві Apress в 2000 році.

Застосування оператора IF … ELSE для виконання заздалегідь запрограмованого запиту

Мабуть, більшості програмістів відразу приходить на думку скористатися оператором IF … ELSE для виконання одного або декількох заздалегідь запрограмованих запитів. Припустимо, наприклад, що потрібно сформувати відсортований список постачальників з таблиці Shippers навчальної бази даних Northwind. При цьому код буде передавати стовпець, по якому слід відсортувати результат, в збережену процедуру в якості параметра. При такому вирішенні збережена процедура, швидше за все, буде виглядати приблизно так, як показано в лістингу 1. Переваги цього варіанта рішення полягають в тому, що код простий і зрозумілий в силу своєї прямолінійності, а оптимізатор запитів SQL Server може завчасно побудувати і оптимізувати план виконання кожної пропозиції SELECT, що забезпечить максимальну продуктивність. Основний недолік цього рішення полягає в тому, що потрібно підтримувати декілька окремих запитів SELECT (в даному прикладі три пропозиції) при зміні вимог до звіту.

Використання назв стовпців в якості параметрів

Інший підхід до вирішення полягає у використанні в ролі параметра назв стовпців. Наведений в лістингу 2 код показує видозмінену збережену процедуру GetSortedShippers. Вираз CASE визначає, який стовпець SQL Server використовує в операторі ORDER BY на підставі значення переданого параметра. Зверніть увагу на те, що вираз в операторі ORDER BY не входить до списку вихідних даних SELECT. У відповідності зі стандартом ANSI SQL-92 не дозволяється використовувати вираз в операторі ORDER BY, якщо цей вислів не зазначено в списку SELECT, проте в стандарті ANSI SQL-99 таке обмеження було знято. Зауважте, що в SQL Server завжди дозволялося це робити.


Тепер випробуємо нову збережену процедуру, передавши їй як параметр назву стовпця ShipperID:


EXEC GetSortedShippers `ShipperID`

Поки все йде добре. Але якщо спробувати запустити цю процедуру, вказавши в якості параметра стовпець CompanyName, вона не буде працювати:


EXEC GetSortedShippers `CompanyName`

Прочитавши уважно отримане повідомлення про помилку,


Server: Msg 245, Level 16, State 1, Procedure GetSortedShippers, Line 5
Syntax error converting the nvarchar value `Speedy
Express `to a column of data type int.

ми розуміємо, що SQL Server спробував перетворити значення `Speedy Express` (яке відноситься до типу даних nvarchar) у цілочисельне значення типу даних integer. Зрозуміло, зробити це виявилося неможливо. Помилка сталася через те, що вираз CASE визначає той тип даних, який отримає результат виконання цього виразу, відповідно до правил пріоритетів типів даних, Data Type Precedence. У відповідності з цими правилами, про які можна прочитати в SQL Server Books Online (BOL), у типу даних int більш високий пріоритет, ніж у типу даних nvarchar. Код збереженої процедури змусив SQL Server сортувати вихідні дані у відповідності зі стовпцем CompanyName, який відноситься до типу даних nvarchar. Наведене у цій процедурі вираз CASE може повертати або стовпець ShipperID з типом даних int, або CompanyName з типом даних nvarchar, або стовпець Phone з типом даних nvarchar. Оскільки тип даних int має найвищий пріоритет, то і результат виконання вираження CASE повинен мати тип даних int.


Щоб уникнути цієї помилки перетворення типів даних, можна спробувати трансформувати стовпець ShipperID в тип даних varchar. У результаті цього кроку тип даних nvarchar буде володіти найвищим пріоритетом серед повертаються цим запитом типів даних. У лістингу 3 показана скоригована збережена процедура GetSortedShippers. Якщо тепер запустити цю збережену процедуру, вказавши в якості параметра будь-яку назву стовпця з трьох можливих, то буде отриманий результат, який виглядає цілком правдоподібно. Створюється враження, що вказаний стовпець був використаний для визначення порядку сортування вихідних даних запиту. Однак у використовуваній таблиці містяться відомості лише про трьох постачальників з ідентифікаторами 1, 2 і 3. Припустимо, що в цю таблицю додали ще сім постачальників, як показано в лістингу 4 (стовпець ShipperID має властивість IDENTITY, так що SQL Server автоматично генерує значення для цього стовпця). Ще раз запустимо нашу збережену процедуру, вказавши в якості параметра стовпець ShipperID:


EXEC GetSortedShippers `ShipperID`

У таблиці 1 показані вихідні дані, отримані в результаті роботи цієї процедури, що зберігається. Запис, що відноситься до постачальника Shipper10, явно перебуває не на місці. Це сталося тому, що код справив сортування на підставі символьного представлення даних, а не числового. У символьній рядку 10 передує 2, оскільки 10 починається з символу 1. Для подолання цієї труднощі можна доповнити значення ідентифікаторів у стовпці ShipperID нульовими головними цифрами та знаками, щоб зробити їх всі однакової довжини. Тоді сортування на основі символів дасть такі ж результати, як і сортування на основі цифр. Модифікована в такий спосіб збережена процедура показана в лістингу 5. Десять нулів ставляться попереду абсолютного значення ідентифікатора в стовпці ShipperID, і з отриманого значення код використовує тільки 10 стоять праворуч знаків. Функція SIGN () визначає, чи слід ставити знак (+) для позитивних значень, або ж поставити знак (-) для від'ємних значень. Таким чином, результат завжди буде складатися з 11 символів, включаючи знак + або -, головні нулі і абсолютне значення ідентифікатора з шпальти ShipperID. Якщо негативних ідентифікаторів постачальників не може бути, то використовувати знак не обов'язково. Але я все ж додав знак, щоб отримати більш універсальне рішення задачі. Під час сортування знак – розміщується попереду знаку +, так що запропоноване рішення працює і в разі негативних значень ідентифікаторів.


Тепер наша збережена процедура буде чудово працювати, яку б з трьох можливих назв стовпців не було вказано як параметр. Проте чудове рішення, запропоноване Річардом Ромлі (воно наведено в лістингу 6), не вимагає прийняття будь-яких заходів для врахування особливостей обробки різних типів даних. Він розбив оператор ORDER BY на три окремі висловлювання CASE, кожне з яких обробляє свій стовпець даних. Тим самим Річард уникнув проблем, пов'язаних з тим, що вираз CASE може повертати значення тільки одного типу даних. Якщо скористатися цією конструкцією, SQL Server буде повертати потрібний тип даних для кожного виразу CASE без будь-яких перетворень типів даних. Однак при цьому майте на увазі, що індекс зможе оптимізувати операцію сортування тільки в тому випадку, коли в зазначеному стовпці не виконується ніяких обчислень.

Використання номера стовпця як параметра

Можливо, читачі вважатимуть за краще в якості параметра використовувати не назва, а номер стовпця (тобто те число, яке представляє стовпець, за яким слід проводити сортування результуючих даних). Наприклад, так, як це було зроблено в самому першому варіанті рішення. Ідея в основному збігається з тим випадком, коли в якості параметра використовується назва стовпця. Вираз CASE визначає, який стовпець необхідно використовувати, на підставі введеного номера стовпця. У лістингу 7 показаний код збереженої процедури GetSortedShippers, адаптований для роботи з номерами стовпців.


Звичайно ж, і в цьому випадку можна використовувати рішення, запропоноване Річардом. Це дозволить уникнути обліку типів даних тих стовпців, які використовуються в обороті ORDER BY. Якщо потрібно відсортувати вихідні дані відповідно до ідентифікаторами постачальників, ShipperID, то необхідно викликати модифіковану збережену процедуру наступним чином:


EXEC GetSortedShippers 1

Використання динамічного виконання запитів

Використання динамічного виконання запитів дозволяє спростити процес створення збереженої процедури GetSortedShippers. При цьому пропозиція SELECT будується динамічно і виконується за допомогою команди EXEC (). Якщо в якості параметра вказувати назву стовпця, то збережена процедура буде значно коротшим, ніж розглянуті вище варіанти:


ALTER PROC GetSortedShippers
  @ColName AS sysname
AS
EXEC (`SELECT * FROM Shippers ORDER BY` +
 @ColName)

У SQL Server 2000 і SQL Server 7.0 замість команди EXEC () можна скористатися системної збереженої процедурою sp_ExecuteSQL. У SQL Server Books Online (BOL) наведено перелік всіх переваг використання системної збереженої процедури sp_ExecuteSQL замість команди EXEC (). Однак у динамічного виконання є і свої недоліки. У загальному випадку можна надати повноваження на виконання збереженої процедури, не надаючи повноважень на доступ до певних об'єктів, якщо виконані три умови. По-перше, можна використовувати тільки пропозиції мови маніпулювання даними, Data Manipulation Language (DML), а саме, SELECT, INSERT, UPDATE, DELETE. По-друге, всі об'єкти, на які даються посилання, повинні належати тому ж власнику, якому належить і сама збережена процедура. І, по-третє, не можна використовувати динамічне виконання. Сама остання версія нашої збереженої процедури третій умові не задовольняє. У цьому випадку доведеться надати право виконувати операцію вибірки SELECT у таблиці постачальників Shippers всім користувачам і групам користувачів, які будуть працювати з цією збереженої процедурою. Якщо це припустимо, більше ніяких проблем не виникне. Аналогічно тому, як це робилося вище, можна скорегувати збережену процедуру таким чином, щоб замість назв стовпців вона використовувала їх номери. Відповідний код збереженої процедури показаний в лістингу 8.


Зверніть увагу на те, що при використанні функції необхідно сформувати пропозицію SELECT всередині змінної, а не всередині команди EXEC (). У цьому випадку вираз CASE буде динамічно визначати, який стовпець слід використовувати. Можна застосувати навіть ще більш коротку форму запису, оскільки мова T-SQL дозволяє застосовувати в операторі ORDER BY позицію стовпця у списку SELECT, як показано в лістингу 9. Така форма запису припустима з позицій стандарту ANSI SQL-92, але стандарт ANSI SQL-99 не підтримує її, тому краще нею не користуватися.

Застосування функцій користувача

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


Таке рішення приходить в голову більшості програмістів, як тільки вони приступають до роботи над завданням. Однак SQL Server не приймає таку функцію і видає таке повідомлення про помилку:


Server: Msg 1033, Level 15, State 1, Procedure ufn_GetSortedShippers,
 Line 24
The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.

Буквальний переклад цього повідомлення звучить наступним чином: "Оператор ORDER BY не слід застосовувати в уявленнях, вбудовані функції, похідних таблицях і підзапитах, якщо тільки в ньому не буде визначено ключове слово TOP ". Зверніть увагу на ту частину цього повідомлення, що починається зі слів" якщо тільки ". SQL Server 2000 не дозволяє задіяти оператор ORDER BY в уявленнях, вбудованих користувальницьких функціях UDF, похідних таблицях і підзапитах, оскільки всі вони мають повертати таблиці, для яких не задано певний порядок проходження рядків. Однак при використанні ключового слова TOP оператор ORDER BY зможе визначити, в якому порядку повинні повертатися рядка. Тому застосовувати оператор ORDER BY можна, якщо при цьому вказувати параметри ключового слова TOP. Можливість застосування оператора ORDER BY в призначеній для користувача функції UDF, містить TOP, дозволяє виконати такий трюк: просто замінити рядок коду


SELECT *

рядком


SELECT TOP 100 PERCENT *

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


SELECT * FROM ufn_GetSortedShippers(`ShipperID`)

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


Іцик Бен-Ган

Іцик Бен-Ган має сертифікати MCDBA, MCSE + I, MCSD, MCT і SQL Server MVP. Він працює старшим викладачем на курсах з SQL Server в коледжі Hi-Tech у Ізраїлі. Є головою ізраїльської групи користувачів SQL Server.

З ним можна зв'язатися за такою адресою: itzikb@hi-tech.co.il

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


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

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

Ваш отзыв

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

*

*