| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
|
|
Параметрическое определение порядка сортировки данных |
|
|
Порядок сортировки результатов обработки запроса можно задать в виде параметра. В различных сетевых форумах часто обсуждается вопрос: как получить отсортированные результаты выполнения запроса, передавая хранимой процедуре некоторый параметр? Я собрал несколько решений этой задачи, предложенных талантливыми программистами. Основная часть идей, изложенных в этой статье, принадлежит обладателям звания 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:
Пока все идет хорошо. Но если попытаться запустить эту процедуру, указав в качестве параметра столбец CompanyName, она не будет работать:
Прочитав внимательно полученное сообщение об ошибке,
мы понимаем, что 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:
В таблице 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, то необходимо вызвать модифицированную хранимую процедуру следующим образом:
Использование динамического исполнения запросов Использование динамического исполнения запросов позволяет упростить процесс создания хранимой процедуры GetSortedShippers. При этом предложение SELECT строится динамически и исполняется при помощи команды EXEC(). Если в качестве параметра указывать название столбца, то хранимая процедура будет значительно короче, чем рассмотренные выше варианты:
В 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 не принимает такую функцию и выдает следующее сообщение об ошибке:
Буквальный перевод этого сообщения звучит следующим образом: "Оператор ORDER BY не следует применять в представлениях, встроенных функциях, производных таблицах и подзапросах, если только в нем не будет определено ключевое слово TOP". Обратите внимание на ту часть этого сообщения, которая начинается со слов "если только". SQL Server 2000 не позволяет задействовать оператор ORDER BY в представлениях, встроенных пользовательских функциях UDF, производных таблицах и подзапросах, поскольку все они должны возвращать таблицы, для которых не задан определенный порядок следования строк. Однако при использовании ключевого слова TOP оператор ORDER BY сможет определить, в каком порядке должны возвращаться строки. Поэтому применять оператор ORDER BY можно, если при этом указывать параметры ключевого слова TOP. Возможность применения оператора ORDER BY в пользовательской функции UDF, содержащей TOP, позволяет выполнить следующий трюк: просто заменить строку кода
строкой
Тогда смело можно создавать функцию, которая будет воспринимать название или номер столбца в качестве входного параметра и возвращать результирующий набор в отсортированном по указанному столбцу виде. Запускать такую функцию можно следующим образом:
В данной статье мы рассмотрели несколько способов использования параметров для определения порядка следования строк в наборе данных, полученном в результате обработки запроса. Описанные здесь приемы с успехом можно применять при создании приложений, которые предоставляют пользователям возможность самим указывать, по какому столбцу следует проводить сортировку результатов обработки запроса. При этом в качестве параметра используется название или номер столбца, а при конструировании решения применяется выражение CASE и динамический режим исполнения команд. Ицик Бен-ГанИцик Бен-Ган имеет сертификаты MCDBA, MCSE+I, MCSD, MCT и SQL Server MVP. Он работает старшим преподавателем на курсах по SQL Server в колледже Hi-Tech в Израиле. Является председателем израильской группы пользователей SQL Server. С ним можно связаться по следующему адресу: |
|
|
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|