Отправляет email-рассылки с помощью сервиса Sendsay

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru)

Подписаться Бесплатная «Серебряная» новостная рассылка . Подписчиков 2.594 RSS
←   Январь 2009   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


За последние 60 дней ни разу не выходила


Сайт рассылки: http://www.sql-ex.ru
Открыта: 15-09-2004

Автор
Serge I

Статистика

2.594 подписчиков
0 за неделю
←   Все выпуски   →

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 226


Новости сайта "Упражнения по SQL"

Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)"

Выпуск 226 от 24 января 2009 г.

Новым посетителям сайта

SQL Exercises Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.

Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 50-й задачи первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений.

Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.

Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий.


Новости сайта

§ Автор (anddros) решил поднять сложность задачи 69 до 3 баллов.
Это привело к некоторым изменениям в рейтинге.

§ Как справедливо заметил Barysok Valery, проверку на размер запроса (не более 8000 символов) на клиенте, можно обойти, чем и пользовались некоторые "продвинутые" участники. Добавил аналогичную проверку на сервере, дабы вывести их на более высокий уровень мастерства. :-)

§ TomGolab - первый "иностранец", решивший 138 задачу. Если он не остановится на этом и пройдет накопившиеся новые задачи второго этапа, то третий этап вполне можно будет считать международным соревнованием. :-)

§ Изменения среди лидеров (решенные за неделю задачи третьего этапа):
Смена лидера!
1. Артём С. (150, 151, 152)
12. anddros (143)
24. Johan (148)

§ Продвинулись в рейтинге:
47. AKudrakov (задач 135, время 37.820)
56. TomGolab (132, 67.264)
61. nebiros (133, 423.535)
85. Romul_T (125, 92.316)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
107. Vasilko (136, 8.965)
131. nadush (119, 197.517)
146. maar (114, 87.521)
171. vitaliiS (112, 86.869)

§ На этой неделе сертифицированы:
alx_ol (A09038531) [BK] - г.Новосибирск, Россия

§ Число подписчиков - 3850

Число участников рейтинга - 20979

Число участников второго этапа - 1692

Число участников третьего этапа - 45

Сертифицировано на сайте - 348

Лучшие результаты (ТОР 20)

NoPersonNumber of
Sel_ex		Last_SelNumber of
DML_ex		ScoresDaysDays_2Days_3S_3LastSolvedLastVisit
1>Селезнёв А.С. (Артём С.)1481522135944246.79037.5244623 Jan 2009 23 Jan 2009
2Креславский О.М. (Arcan)1521522137170759.89341.0174606 Jan 2009 23 Jan 2009
3Карасёва Н.В. (vlksm)1521522137197579.78750.7234605 Jan 2009 23 Jan 2009
4Никотин В.М. (@Nikotin)150150213651088.3713.7514013 Dec 2008 23 Jan 2009
5Сальников С.А. ($erges)150150213652913.4873.8244013 Dec 2008 23 Jan 2009
6Сенкевич С.В. (GreyC)1501502136534252.02521.2684010 Jan 2009 23 Jan 2009
7Печатнов В.В. (pvv)1461492135235730.84917.4903610 Oct 2008 23 Jan 2009
8Муллаханов Р.Х. (rem)1491522136048314.42720.0563505 Jan 2009 23 Jan 2009
9Зотов П.Г. (Ozzy)1421472134330788.048105.7633111 Jan 2009 23 Jan 2009
10Шиндин А.В. (AlShin)147150213557920.4587.2033005 Jan 2009 15 Jan 2009
11Мурашкин И.В. (lepton)1421502134299547.79737.3123012 Dec 2008 18 Dec 2008
12Дроздков А.Н. (anddros)146151213532375.5456.1932819 Jan 2009 23 Jan 2009
13Держальцев В.А. (MadVet)13714621333125760.78328.4822824 Sep 2008 30 Dec 2008
14Любченко В.А. (IAS56)13614621332615403.343373.6172811 May 2008 01 Dec 2008
15Голубин Р.С. (Roman S. Golubin)14014521335112293.04258.8222513 Sep 2008 06 Dec 2008
16Nikolaenko A.V. (Shadow77)1421472133943677.45114.0102322 Oct 2008 11 Dec 2008
17Солдатенков Ю.С. (SolYUtor)1381462133181922.6156.1022014 Aug 2008 23 Oct 2008
18Белогурова К. (Katy_Ekb)1331432132155210.6664.6731827 Nov 2008 18 Jan 2009
19Егоров А.Б. (ABEgorov)1371442132918012.8978.8151803 Aug 2008 12 Aug 2008
20Умрихина Е.В. (Umrikhina)1431432134326511.70014.0581814 Jan 2009 23 Jan 2009

Лучшие результаты за неделю

Nosurnamen_selsel_allsel_scoresdml_scoresscoresratinglast_visit
1>Филиппкин Д.В. (_dimon_)5152951110696723 Jan 2009
2>Z E. (Elz)435887087100722 Jan 2009
3Задорожнев А.М. (kridan)405083083180422 Jan 2009
4>Shevelev D.S. (Dimon77777)336759167583423 Jan 2009
5bbb A.B. (!qaz)373774074269921 Jan 2009
6>Vasylchenko Y. (Freezzer)334573073212223 Jan 2009
7>Amatov S.A. (Sanjar)2828541771292023 Jan 2009
8Исаев В. (val75)363668068312723 Jan 2009
9>mlvljr (mlvl.jr)295359059172523 Jan 2009
10>Strelkov F.N. (fest84)263759059293723 Jan 2009
11>Безлер (Vladimir68)323256056413123 Jan 2009
12unspecifieduser (unspecified)272752355421222 Jan 2009
13>Ичалов В. (a66at)23925005027023 Jan 2009
14>Дружинин С.Е. (StarleY)193345247310123 Jan 2009
15Антошкин Р. (RAntoshkin)233047047433923 Jan 2009
16Зубов И.К. (Garich)1321271946439623 Jan 2009
17Ларина О.В. (_olga_)116014304483221 Jan 2009
18W S. (Szymon)193043043428622 Jan 2009
19Клевцов В.В. (Elochnik)172942042424722 Jan 2009
20>Наркевич А. (pektop)232342042578623 Jan 2009
21>Луковенков А.В. (luks)162735540455723 Jan 2009
22Акуленко Н.Н. (Red Hat)212137340595318 Jan 2009

Изучаем SQL

Массивы и списки в SQL Server 2005 (начало в вып.217-225)

Erland Sommarskog (оригинал: Arrays and Lists in SQL Server 2005 )
Перевод: Моисеенко С.И.

Функции CLR, использующие расщепление (split)

Мы рассмотрим две реализации функции список-в-таблицу в CLR. Первый, с очень небольшим количеством нашего собственного кода, служит введением в табличные функции CLR. Во втором варианте мы накрутим наш собственный код, который обладает большей гибкостью.

Код

Полный файл C#, который реализует две функции список-в-таблицу - один для строк, а другой для целых чисел - будет не больше этого:

using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class CLR_split
{
    [SqlFunction(FillRowMethodName="CharlistFillRow")]
    public static IEnumerable CLR_charlist_split(SqlString str,
                                                 SqlString delimiter)
    {
        return str.Value.Split(delimiter.Value.ToCharArray(0, 1));
    }

    public static void CharlistFillRow(object row,
out string str)
    {
        str = (string)row;
        str = str.Trim();
    }

    [SqlFunction(FillRowMethodName="IntlistFillRow")]
    public static IEnumerable CLR_intlist_split(SqlString str)
    {
        return str.Value.Split((char[]) null,
                               System.StringSplitOptions.RemoveEmptyEntries);
     }

    public static void IntlistFillRow(object row, out int n)
    {
        n = System.Convert.ToInt32((string)
row);
    }
}

Компиляция и установка

Для компиляции данного кода, откройте окно командной строки и удостоверьтесь, что у вас прописан следующий или подобный путь: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727. (Более поздние версии .Net Framework также будут работать. Но версия 1.1 не будет.) Предполагая, что файл имеет имя CLR_split.cs, команда имеет вид:


csc/target:library CLR_split.cs

Эта команда создаст файл CLR_split.dll. Если ваш SQL Server находится не на вашей локальной машине, Вы должны будете скопировать DLL на сервер, или сделать файл DLL видимым со стороны сервера. Теперь выполните в окне запроса:

CREATE ASSEMBLY CLR_split FROM 'C:\somewhere\CLR_split.dll'
go
CREATE FUNCTION CLR_charlist_split(@list nvarchar(MAX),
                                   @delim nchar(1) = N',')
RETURNS TABLE (str nvarchar(4000))
AS EXTERNAL NAME CLR_split.CLR_split.CLR_charlist_split
go
CREATE FUNCTION CLR_intlist_split(@list nvarchar(MAX))
RETURNS TABLE (number int)
AS EXTERNAL NAME CLR_split.CLR_split.CLR_intlist_split
go

(Примечание: возможно также развернуть функции из Visual Studio, но я не могу показать Вам, как это сделать, поскольку сам не знаю этого. Visual Studio, главным образом, загоняет меня в лабиринт, но в то же самое время я нахожу командную строку очень простой в использовании. Мне говорили, что VS может потребовать, чтобы Вы добавили дополнительные атрибуты в функции, если Вы хотите развернуть функции этим способом.)

Теперь мы создали функции и можем использовать их в T-SQL. Ниже пример для обеих:

CREATE PROCEDURE get_company_names_clr @customers nvarchar(2000) AS
   SELECT C.CustomerID, C.CompanyName
   FROM   Northwind..Customers C
   JOIN   listtest..CLR_charlist_split(@customers, DEFAULT) s
     ON   C.CustomerID = s.str
go
EXEC get_company_names_clr 'ALFKI, BONAP, CACTU, FRANK'
CREATE PROCEDURE get_product_names_clr @ids varchar(50) AS
   SELECT P.ProductName, P.ProductID
   FROM   Northwind..Products P
   JOIN   CLR_intlist_split(@ids) i ON
P.ProductID = i.number
go
EXEC get_product_names_clr '9 12 27  37'

Как и iter_intlist_to_tbl, функция CLR_intlist_split принимает на входе разделенный пробелами список целых чисел.

Что дальше?

Если Вы никогда не работали прежде с табличными функциями CLR, Вы можете спросить, как это все работает, и я попробую вам объяснить.

CREATE ASSEMBLY загружает DLL в SQL Server. Замечу, что эта операция не просто сохраняет указатель на файл; сам DLL также сохраняется в базе данных. Поскольку CREATE ASSEMBLY работает из SQL Server, путь к файлу ведет к дискам на сервере, а не на вашей локальной машине. (Если Вы загружаете сборку из разделяемого сетевого диска, лучше указать местоположение с помощью имени \\ servername, а не буквы диска). Также возможно загрузить сборку в виде шестнадцатеричной строки.

Оператор CREATE FUNCTION выглядит точно так же как операторы для создания многооператорных функций. Таким образом, Вы задаете список параметров и возвращаете таблицу. Но вместо тела после AS следует EXTERNAL NAME, где Вы указываете используемый метод CLR. Это состоящее из трех частей имя, в котором первая часть - сборка, вторая часть - класс в пределах сборки, а последняя часть - непосредственно название метода. В этом примере я использую то же самое имя для сборки в SQL Server, поскольку я делаю это для класса.

Есть одна небольшая деталь относительно возвращаемой таблицы: для многооператорных табличнозначных функций Вы можете указать, что столбец допускает null-значения, Вы можете задать также CHECK и DEFAULT ограничения и определить PRIMARY KEY. Но это невозможно для функций CLR.

Если мы вернемся к коду C#, табличнозначная функция реализуется посредством двух методов C#. Первый метод - это метод, на который мы указываем в операторе CREATE FUNCTION. Второй метод определяется через атрибут, который идет первым в определении первого метода. То есть вот эта строка:

[SqlFunction (FillRowMethodName = "CharlistFillRow")]

Эта строка определяет, что метод - это табличнозначная функция и указывает на второй метод функции. CLR_charlist_split - есть точка входа и указывается один раз. Точка входа должна возвратить коллекцию или счетчик, и CLR будет вызывать метод, указанный в FillRowMethodName по разу для каждого элемента в коллекции/счётчике, и каждое обращение дает строку в выходной таблице этой функции.

Итак, что происходит, когда Вы называете CLR_charlist_split из T-SQL. Метод C# вызывает метод Split класса String, который расщепляет строку на коллекцию по разделителю. (Подробную информацию относительно Split вы сможете найти в .Net Framework SDK библиотеки MSDN). Так как мы получаем коллекцию, то больше ничего и не нужно. CLR вызывает CharlistFillRow для каждого элемента в коллекции. И как я отметил выше, как только строка получена, она может использоваться во внешнем запросе, не дожидаясь, пока будет получена вся таблица.

Как насчет параметров? Как Вы можете догадаться, список параметров входного метода должен быть согласован со списком параметров в операторе CREATE FUNCTION. Точные правила соответствия типов данных SQL c типами CLR выходят за рамки данной статьи; пожалуйста, обратитесь к Books Online за подробностями.

Первый параметр заполняющего метода (CharlistFillRow) имеет тип object. Это текущий элемент в коллекции/перечислении и, чтобы использовать его, вам следует преобразовать его к типу real. Остальные параметры в заполняющем методе являются выходными параметрами, и они соотносятся с выходной таблицей в операторе CREATE FUNCTION.

Следует обратить внимание еще на одну вещь: тип возвращаемого параметра входной функции. В данном примере это - IEnumerable, так как Split возвращает коллекцию. Единственная альтернатива этому - IEnumerator, который мы рассмотрим вкратце.

(Продолжение следует...)

Полезная информация

§ Приглашаем вас посетить новый проект - Интерактивный учебник по SQL.
   Ресурс позиционируется как "справочное обеспечение" для сайта SQL-EX.RU, но может использоваться и независимо от него.

§ Онлайновый выпуск рассылки можно почитать на сайте.

§ Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL.

§ Хотите поддержать проект? Вот инструкция по применению. :-)

Контакты

По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.
Подписка Subscribe.Ru
Новости сайта "Упражнения по SQL"
В избранное