Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru)
| Информационный Канал Subscribe.Ru |
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 46 (30 июля 2005 г.)
 |
Новым посетителям сайтаСайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 59-ти задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. |
Новости сайта
§ Появилась страница кандидатов на попадание в ТОР 100. На странице ТОР 100 имеется ссылка.
§ Привел, наконец, в соответствие рейтинговые позиции, выводящиеся в рейтинге за неделю.
§ По сообщению Shark добавил проверочных данных под задачу 104.
§ Замеченный Putin'ым прокол в проверке задачи 82 подлатал. § Число подписчиков - 2265Число участников рейтинга - 3690
Число участников второго этапа - 346
Лучшие результаты (ТОР 20)
| No | Person | Number of Sel_ex |
Last_Sel | Number of DML_ex |
Scores | Days | Days_2 | LastSolved | LastVisit |
| 1 | Кувалкин К.С. (Cyrilus) | 137 | 112 | 20 | 312 | 224 | 5.129 | 06 Jul 2005 | 15 Jul 2005 |
| 2 | Гонтовой В.А. (noname) | 137 | 112 | 20 | 312 | 105 | 9.808 | 29 Jun 2005 | 02 Jul 2005 |
| 3 | Леденев С.А. (Shurgenz) | 137 | 112 | 20 | 312 | 313 | 9.900 | 27 Jun 2005 | 15 Jul 2005 |
| 4 | Бураков С.Г. (burakov58) | 137 | 137 | 20 | 312 | 164 | 12.100 | 12 Jul 2005 | 14 Jul 2005 |
| 5 | Валуев Д.И. (Fiolent) | 137 | 112 | 20 | 312 | 662 | 26.627 | 27 Jun 2005 | 01 Jul 2005 |
| 6 | Галиаскаров Э.Г. (Galogen) | 137 | 112 | 20 | 312 | 221 | 61.437 | 01 Jul 2005 | 01 Jul 2005 |
| 7 | Мельникова И.А. (Iris_m) | 135 | 137 | 20 | 308 | 380 | 89.865 | 27 May 2005 | 14 Jun 2005 |
| 8 | Зверев Д.Л. (dimzv) | 135 | 136 | 20 | 306 | 580 | 2.471 | 06 Jun 2005 | 15 Jun 2005 |
| 9 | Колосов А.С. (KAS) | 134 | 137 | 20 | 306 | 25 | 3.398 | 11 Mar 2005 | 14 Jun 2005 |
| 10 | Сныткин В.Л. (Ded I) | 134 | 136 | 20 | 304 | 252 | 7.456 | 12 May 2005 | 15 Jul 2005 |
| 11 | Рахманов И.Е. (bloom) | 134 | 136 | 20 | 304 | 148 | 14.171 | 11 May 2005 | 15 Jun 2005 |
| 12 | Hakobyan H.H. (hamlet) | 134 | 136 | 20 | 304 | 220 | 37.869 | 07 May 2005 | 03 Jun 2005 |
| 13 | Шипунов И. (IAS) | 134 | 136 | 20 | 304 | 334 | 82.080 | 13 May 2005 | 26 May 2005 |
| 14 | Иткин И.Л. (joseph_itkin) | 132 | 136 | 20 | 299 | 375 | 2.849 | 07 Mar 2005 | 13 Apr 2005 |
| 15 | Spirin (spirin) | 131 | 136 | 19 | 296 | 158 | 13.461 | 21 Jan 2005 | 24 Jan 2005 |
| 16 | Михайлов В.Г. (mslava) | 132 | 136 | 17 | 293 | 648 | 10.504 | 25 Mar 2005 | 25 Mar 2005 |
| 17 | Gershovich (VIG) | 128 | 128 | 20 | 293 | 850 | 13.073 | 09 Jul 2005 | 15 Jul 2005 |
| 18 | Пятница О.А. (Robin) | 125 | 128 | 20 | 287 | 754 | 74.630 | 19 Mar 2005 | 14 Jul 2005 |
| 19 | Митронин А.А. (mitronin) | 123 | 124 | 20 | 283 | 701 | 27.541 | 31 May 2005 | 01 Jun 2005 |
| 20 | Булаев В.В. (Kvix) | 119 | 121 | 20 | 274 | 266 | 24.671 | 18 May 2005 | 18 May 2005 |
Лучшие результаты за неделю
| No | surname | n_sel | sel_all | sel_scores | dml_scores | scores | rating | last_visit |
| 1 | Борисов А.П. (AlexanderB) | 40 | 40 | 72 | 0 | 72 | 685 | 27 Jul 2005 |
| 2 | >Tsenglevych V. (VladTor) | 42 | 42 | 61 | 0 | 61 | 834 | 29 Jul 2005 |
| 3 | Семиренко Л.Н. (LeoSam) | 28 | 28 | 49 | 0 | 49 | 1006 | 27 Jul 2005 |
| 4 | Agarwal S. (Sachin4u) | 31 | 31 | 49 | 0 | 49 | 1014 | 26 Jul 2005 |
| 5 | Паршин Ф.В. (Philipp) | 34 | 34 | 43 | 5 | 48 | 1053 | 27 Jul 2005 |
| 6 | Богучаров Ю.А. (Xan) | 25 | 25 | 40 | 1 | 41 | 1162 | 27 Jul 2005 |
| 7 | Махортов (tek) | 19 | 42 | 39 | 0 | 39 | 645 | 27 Jul 2005 |
| 8 | Шестаков В.С. (Patalogo@natom) | 24 | 24 | 37 | 0 | 37 | 1255 | 27 Jul 2005 |
| 9 | Карманов (ikar) | 24 | 24 | 37 | 0 | 37 | 1258 | 27 Jul 2005 |
| 10 | Belookaya M. (Marina) | 19 | 54 | 35 | 0 | 35 | 465 | 28 Jul 2005 |
| 11 | Kuch (nibbles01) | 16 | 95 | 34 | 0 | 34 | 61 | 28 Jul 2005 |
| 12 | Бахтин Д.А. (sergeant) | 15 | 39 | 34 | 0 | 34 | 696 | 27 Jul 2005 |
| 13 | Дешко Д.Г. (dimoniy) | 21 | 21 | 29 | 5 | 34 | 1365 | 26 Jul 2005 |
| 14 | Subbotin (sarges) | 16 | 60 | 32 | 0 | 32 | 164 | 28 Jul 2005 |
| 15 | >Шилова Н. (Neznakomka) | 17 | 38 | 23 | 9 | 32 | 836 | 29 Jul 2005 |
| 16 | Секрет К.Б. (Костик) | 21 | 22 | 30 | 0 | 30 | 1495 | 27 Jul 2005 |
| 17 | Shark_ (Shark) | 11 | 113 | 29 | 0 | 29 | 33 | 27 Jul 2005 |
| 18 | Кочетов А.В. (Use_0k!!!) | 11 | 46 | 14 | 13 | 27 | 406 | 27 Jul 2005 |
| 19 | krasavin D.V. (denvk) | 7 | 27 | 19 | 6 | 25 | 931 | 27 Jul 2005 |
| 20 | Gromov I.V. (IL76) | 19 | 19 | 25 | 0 | 25 | 1641 | 27 Jul 2005 |
| 21 | Большаков В. В. В. (ZzzeE H. P. CoS) | 11 | 23 | 22 | 0 | 22 | 1389 | 26 Jul 2005 |
| 22 | Левин В.Б. (LeVSy) | 14 | 21 | 22 | 0 | 22 | 1567 | 27 Jul 2005 |
| 23 | krizhevich (alexander_) | 16 | 19 | 22 | 0 | 22 | 1678 | 27 Jul 2005 |
| 24 | Pak V. (Vadim Pak) | 16 | 16 | 20 | 2 | 22 | 1743 | 27 Jul 2005 |
| 25 | Омельченко (Goer) | 17 | 17 | 21 | 0 | 21 | 1772 | 27 Jul 2005 |
| 26 | >Пономарев (alexpon) | 9 | 36 | 17 | 3 | 20 | 996 | 29 Jul 2005 |
| 27 | Бережной В.А. (LBK-fighter) | 9 | 24 | 20 | 0 | 20 | 1289 | 27 Jul 2005 |
Изучаем SQL
Массивы в SQL Server 2000
Alex Grinberg (оригинал: The ARRAY In SQL Server 2000)
Перевод Моисеенко С.И.
Иногда приходится сожалеть о том, что в Transact-SQL Microsoft SQL Server нет такой структуры как Array (массив). Многие программисты SQL поддержат меня. Массив - одна из наиболее общих и часто используемых структур программирования. Действительно, T-SQL не предусматривает полностью функциональную структуру массива. Однако SQL 2000 ввел новую возможность, называемую переменной табличного типа, которая позволяет имитировать массив и/или использоваться вместо курсора в SQL Server.
В этой статье я детально остановлюсь на том, как можно использовать такой "массив" в хранимых процедурах, что в значительной степени расширяет функциональные возможности программирования на языке SQL.
Я разбил свой подход на три части.
1. Создание массива в хранимых процедурах.2. Передача и возвращение табличных переменных в/из хранимых процедур.
3. Использование 'массива' вместо курсоров.
Мой метод, прежде всего, основан на использовании переменных табличного типа, которые стали доступным, начиная с SQL Server 2000. Если Вы все еще работаете в версиях SQL Server 6.5 или 7, части 1 и 3 этой статьи могут все таки оказаться полезными для Вас. Однако Вы должны будете использовать временную таблицу (с префиксом #) вместо табличной переменной.
Часть 2 посвящена использованию XML, что является причиной, по которой этот подход окажется бесполезным в любой среде разработки, предшествующей SQL 2000. ВТ (временные таблицы) также могут использоваться в версии 2000, однако используя временную таблицу, Вы увеличиваете взаимодействие с дисками, так как временные таблицы создаются базе данных TempDb в противоположность ТП (табличным переменным), создаваемым в памяти. Другими словами, хранимые процедуры, оперирующие временными таблицами медленнее тех, которые используют ТП. Для того чтобы SQL Server создал ВТ, система должна
1. Создать таблицу в системной базе данных TempDb2. Ввести данные в таблицу (insert)
3. Извлечь данные (select)
4. Удалить таблицу (drop)
Подобный процесс требуется и для ТП, но все операции проводятся 100-процентно в памяти. Я недавно экспериментировал с этими вариантами, и выяснил, что та же самая хранимая процедура, использующая ТП, выполнялась вдвое быстрей по сравнению с хранимой процедурой, которая использовала временные таблицы.
Чтобы смоделировать массив (ТП), мы нуждаемся в структуре, подобной array, известной в большинстве сред программирования. Объявление табличной переменной выглядит следующим образом:
Declare @tbl table ( )Данный оператор создает эквивалент array. Тип этого "массива" зависит от типа данных столбца. Например,
Declare @tbl table (FirstName varchar(20), LastName varchar(20))создает массив строкового типа (string). Другие типы данных, такие как int, float, datetime, money и т.д., также могут использоваться. Нужно помнить, что SQL Server не имеет функциональности (подобной VB и некоторым другим средам программирования) по неявному преобразованию типов данных. Это означает, что данные, которые Вы намереваетесь использовать для заполнения массива, должны быть отформатированы согласно спецификации каждого объявленного типа данных. Соответственно могут использоваться функции Convert и Cast для форматирования данных.
Чтобы иметь "совершенный" массив с полными функциональными возможностями, у нас отсутствует индекс. Чтобы восполнить этот недостаток, я использую дополнительный столбец с типом данных Int и свойством Identity. Определение Identity требует 2 аргумента: начальное значение и приращение. Таким образом, мы можем создать массив с отсчетом от 0 или 1. Следующий массив обладает полными функциональными возможностями:
Declare @tbl table (RowId int identity(1,1), ItemName varchar(100))Теперь мы можем заполнить этот массив (используя базу данных Northwind) следующим образом:
Insert @tblSelect ProductName
FROM Products
WHERE ProductName like 'M%'
Select * from @tbl
GO
-- Результат:
RowId ItemName
---------------------------------------
1 Mishi Kobe Niku
2 Mascarpone Fabioli
3 Maxilaku
4 Manjimup Dried Apples
5 Mozzarella di Giovanni
[Как видно, в этом примере столбец RowID обеспечивает функциональность индекса, а ItemName хранит фактические значения.]
Очевидно, что ТП является структурой программирования, эквивалентной МАССИВУ.
Мы продемонстрировали создание массива в SQL Server программным образом. Наша главная задача состоит в том, чтобы использовать массив в хранимых процедурах, включая передачу ТП в качестве аргумента. Наряду с этим, я хотел бы детально остановиться еще на одной возможности передачи всего списка значений в хранимую процедуру через аргумент и использования его внутри процедуры. Для этого я использую XML. Однако эта структура стала доступной, только начиная с версии 2000 SQL Server. Когда требуется подобная операция? Например, если Вы намереваетесь передать в процедуру более одного значения, но не знаете заранее, сколько всего значений будут передаваться. Рассмотрим запрос:
Select * from customers WHERE Region IN ('OR','CA','WA')Оператор IN в предложении WHERE может использовать переменное число значений и возвращает результирующий набор в соответствии с этим множественным критерием.
Передача такого списка значений в хранимую процедуру может стать проблемой. В прежние времена я использовал так называемые "составные запросы", или "динамический SQL". Подход заключается в построении строки запроса, которая затем компилируется и выполняется при помощи оператора EXECUTE. Например:
CREATE PROCEDURE CustomerByRegion@List varchar(100)
AS
declare @sql varchar(1000)
set @sql = 'select * from Customers where Region IN (' + @List + ')'
execute (@sql)
-- Вызов процедуры
declare @List varchar(100)
set @List = '''OR'', ''CA'', ''WA'''
execute CustomerByRegion @List
Этот метод имеет некоторые ограничения. Переменная типа Varchar не может содержать более чем 8000 символов, а переменная типа Text/nText недопустима для объявления локальных переменных. Давайте рассмотрим сценарий, когда XML используется, чтобы передать список значений в хранимую процедуру. Из этого списка мы можем СОЗДАТЬ 'МАССИВ' (ТП), а затем использовать этот Массив, в данном случае в операторе IN - части предложения WHERE оператора SQL.
Create Procedure ShowOrders@XMLData text
/* тип данных TEXT позволяет нам принимать большую строку по сравнению с типом Varchar, который имеет ограничение в 8000 символов. */
AS
Set Nocount ON
Declare @hDoe int
/* число, необходимое системе, чтобы распознать документ XML */
Declare @tbl table (ProductName varchar(100))
/* объявление ТП ('Array') */
Exec sp_xml_preparedocument @hDoc Output, @XMLdata
/* Подготовка строки XML для дальнейшей обработки */
Insert @tbl select XML_Emp.ProductName
From OPENXML(@hdoc,'root/products')
With Products XML_Emp
Рассмотрим блок вставки более подробно. Мы наполнили массив, используя строку XML. Операторы OPENXML и OPENXML WITH создают представление набора строк (ROWSET VIEW ), используя документ XML. Полученный результат может быть загружен непосредственно в ТП или ВТ. Оператор WITH позволяет использовать существующее имя таблицы или создание новой схемы. Я рекомендовал бы второй вариант, так как это улучшает удобочитаемость и обслуживание. Кроме того, если Вы используете предложение WHERE, первый вариант может генерировать ошибку.
-- Вариант 1Insert @tbl
select XML_Emp.ProductName
From OPENXML(@hdoc,'root/products')
With Products XML_Emp
-- Вариант 2:
Insert @tbl
select ProductName
From OPENXML(@hdoc,'root/products')
With (ProductName VARCHAR(20))
(окончание следует...)
Полезная информация
Конкурс
§ Мы выставили наш сайт на конкурс Интернить 2005. Победитель определяется числом поданых голосов. Просьба проголосовать. (рекомендуемая оценка 3 :-)).
Контакты
По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77@yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.
| Subscribe.Ru
Поддержка подписчиков Другие рассылки этой тематики Другие рассылки этого автора |
Подписан адрес:
Код этой рассылки: comp.soft.db.sqlex |
Отписаться
Вспомнить пароль |
| В избранное | ||
