авторефераты диссертаций БЕСПЛАТНАЯ БИБЛИОТЕКА РОССИИ

КОНФЕРЕНЦИИ, КНИГИ, ПОСОБИЯ, НАУЧНЫЕ ИЗДАНИЯ

<< ГЛАВНАЯ
АГРОИНЖЕНЕРИЯ
АСТРОНОМИЯ
БЕЗОПАСНОСТЬ
БИОЛОГИЯ
ЗЕМЛЯ
ИНФОРМАТИКА
ИСКУССТВОВЕДЕНИЕ
ИСТОРИЯ
КУЛЬТУРОЛОГИЯ
МАШИНОСТРОЕНИЕ
МЕДИЦИНА
МЕТАЛЛУРГИЯ
МЕХАНИКА
ПЕДАГОГИКА
ПОЛИТИКА
ПРИБОРОСТРОЕНИЕ
ПРОДОВОЛЬСТВИЕ
ПСИХОЛОГИЯ
РАДИОТЕХНИКА
СЕЛЬСКОЕ ХОЗЯЙСТВО
СОЦИОЛОГИЯ
СТРОИТЕЛЬСТВО
ТЕХНИЧЕСКИЕ НАУКИ
ТРАНСПОРТ
ФАРМАЦЕВТИКА
ФИЗИКА
ФИЗИОЛОГИЯ
ФИЛОЛОГИЯ
ФИЛОСОФИЯ
ХИМИЯ
ЭКОНОМИКА
ЭЛЕКТРОТЕХНИКА
ЭНЕРГЕТИКА
ЮРИСПРУДЕНЦИЯ
ЯЗЫКОЗНАНИЕ
РАЗНОЕ
КОНТАКТЫ


Pages:     | 1 |   ...   | 30 | 31 || 33 |

«Е.Мамаев MS SQL SERVER 2000 Книга посвящена одной из самых мощных и популярных современных систем управления базами данных - Microsoft SQL Server 2000. ...»

-- [ Страница 32 ] --

SELECT * FROM titles business BU1032 The Busy Executive's Database Guide 19.9900 BU1111 Cooking with Computers: Surreptitious 11.9500 BU2075 You Can Combat Computer Stress! 2.9900 BU7832 Straight Talk About Computers 19.9900 (4 row(s) affected) Теперь можно приступать к объединению данных:

SELECT title_id, title=CAST(title as char(30))+ CASE WHEN LEN(title)30 THEN '...' ELSE " END, value=price*ytd_sales, type='psychology' FROM titles_psychology UNION SELECT title_id, title=CAST(title as char(30))+ CASE WHEN LEN(title)30 THEN '...' ELSE '' END, value=price*ytd_sales, type='business' FROM titles_business ' Этот запрос демонстрирует не только простое слияние данных двух таблиц, но и предварительную обработку этих данных перед слиянием. В частности, только данныезапроса будетt получен следующий результат: изменениям. Все остальные столбцы столбца t i i e i d не былиразличных выражений. В результате выпол нения сформированы на основе подвергнуты Глава 29. Выборка данных title_id title value type BU1032 The Busy Executive's Database... 81859.0500 business BU1111 Cooking with Computers: Surrep... 46318.2000 business BU2075 You Can Combat Computer Stress... 55978.7800 business BU7832 Straight Talk About Computers 81859.0500 business PS3333 Prolonged Data Deprivation: Fo... 81399.2800 psychology PS1372 Computer Phobic AND Non-Phobic... 8096.2500 psychology PS2091 Is Anger the Enemy? 22392.7500 psychology PS7777 Emotional Security: A New Algo... 26654.6400 psychology PS2106 Life Without Fear 777.0000 psychology (9 row(s) affected) В предыдущем запросе было рассмотрено слияние однотипных данных, однако в принципе не важно, какие данных объединяются. Важно лишь, чтобы они имели одинаковую структуру. Следующий пример демонстрирует объединение данных из таблиц с разной структурой. Кроме того, нет никакого смысла в вы полняемом объединении, и оно производится только для демонстрации воз можностей раздела UNION:

SELECT title_id, title, price FROM titles WHERE type='popular_comp' UNION SELECT au_id, au_lname, contract FROM authors WHERE state NOT IN CCA', 'UT 1, ' KS' ) UNION SELECT phone, type, contract*ytd_sales*0. FROM authors a, titleauthor ta, titles t WHERE state NOT IN ('CA', 'UT', 'KS') AND ta.au_id=a.au_id AND t.title_id=ta.title_id ~ Будет получен результат:

title_id title price 219 547-9982 mod_cook 17351. 301 946-8853 trad_cook 292. 503 745-6402 trad_cook 11774. 527-72-3246 Greene. 615 996-8275 modcook 1584. 648-92-1872 Blotchet-Halls 1. 712-45-1867 del Castillo 1. 722-51-5454 DeFrance 1. 807-91-6654 Panteley 1. PC1035 But Is It User Friendly? 22. PC8888 Secrets of Silicon Valley 20. PC9999 Net Etiquette NULL (12 row(s) affected) Как видно, первые строки объединения относятся к последнему запросу, затем идут строки второго запроса и только после этого включены строки первого запроса. Тем не менее, столбцы объединения имеют имена, соответствующие именам столбцов первого запроса.

1190 Часть V. Программирование Раздел ORDER BY По умолчанию данные в результат выводятся в соответствии с их физическим расположением в таблице. Если в таблице не определен кластерный индекс, то данные будут выводиться беспорядочно. Наличие в таблице кластерного индек са приводит к физическому перестроению порядка данных в соответствии с ука занными критериями. Таким образом, данные становятся упорядоченными. Од нако в таблице возможно наличие только одного кластерного индекса, что означает существование сортировки только по одному столбцу. Тем не менее, часто возникает необходимость отсортировать данные по различным критериям, причем иногда по нескольким сразу.

Для выполнения сортировки данных, возвращаемых запросом, в распоряжении пользователей имеется раздел ORDER BY, специально предназначенный для оп ределения параметров порядка вывода строк. Синтаксис этого раздела таков:

ORDER BY {order_by_expression [ASC I DESC]} [,...n] Рассмотрим назначение аргументов раздела:

О order_by_expression Этот параметр определяет выражение, в соответствии с которым будет вы полняться сортировка данных. В качестве выражения сортировки может ука зываться имя столбца, псевдоним или любое выражение. Также допускается указание номера столбца результата выборки, по которому следует осуществ лять сортировку. Следует отдельно обратить внимание, что сортировка не обязательно должна производиться по выражению, включенному в результат выборки.

• ASC I DESC Эти параметры указываются дополнительно к параметру o r d e r b y e x p r e s s i o n и определяют порядок сортировки. Можно указать только один из парамет ров. При использовании ASC данные располагаются по возрастанию, тогда как параметр DESC определяет порядок сортировки по убыванию. Если поря док сортировки не указан явно, то по умолчанию данные будут располагать ся по возрастанию.

• [,...п] Этот параметр говорит о том, что в одном запросе сортировка может выпол няться по нескольким критериям. Для этого необходимо через запятую ука зать выражения, по которым будет выполняться сортировка, а также допол нительно и порядок сортировки. Порядок указания выражений сортировки определяет приоритет того или иного выражения. Сначала сортировка про изводится по первому выражению. Затем выполняется дополнительная сор тировка строк одной группы по второму выражению и т. д. Если указано только одно выражение, но существует множество строк, имеющих одинако вые значения выражения сортировки, то эти строки будут располагаться в соответствии с их физическим порядком в таблице.

Глава 29. Выборка данных у/9 у Замечание При использовании раздела ORDER BY совместно с разделом UNION сортировка осуществляется по всему массиву данных, а не по данным каждого из запросов. Это относится и ко всем разделам, рассмотренным ранее. То есть сначала идет обра ботка разделов SELECT, INTO, FROM и т. д., а уже потом обрабатывается раздел ORDER BY.

В качестве примера рассмотрим сортировку данных таблицы t i t l e s сначала по типу (столбец type), затем по номеру публикации (столбец pubid) и потом по названию произведения (столбец t i t l e ) :

SELECT pub_id, type, title=CAST(title as char(30))+ CASE WHEN LEN(title)30 THEN '...' ELSE •' END, price FROM titles ORDER BY type, pub_id, Будет возвращен результат:

pub_id type title price 0736 business You Can Combat Computer Stress... 2. 1389 business Cooking with Computers: Surrep... 11. 1389 business Straight Talk About Computers 19. 1389 business The Busy Executive's Database... 19. 0877 mod_cook Silicon Valley Gastronomic Tre... 19. 0877 mod_cook The Gourmet Microwave 2. 1389 popular_comp But Is It User Friendly? 22. 1389 popular_comp Net Etiquette NULL 1389 popular_comp Secrets of Silicon Valley 20. 0736 psychology Emotional Security: A New Algo... 7. 0736 psychology Is Anger the Enemy? 10. 0736 psychology Life Without Fear 7. 0736 psychology Prolonged Data Deprivation: Fo... 19. 0877 psychology Computer Phobic AND Non-Phobic... 21. 0877 trad_cook Fifty Years in Buckingham Pala... 11. 0877 trad_cook Onions, Leeks, and Garlic: Coo... 20. 0877 trad_cook Sushi, Anyone? 14. 0877 UNDECIDED The Psychology of Computer Coo... NULL (18 row(s) affected) В предыдущем примере сортировка производилась по столбцам, которые суще ствуют физически и включены в результат. Следующий пример демонстрирует выполнение сортировки по выражению, не включенному в результат выборки и построенному на основе нескольких столбцов:

SELECT TOP 10 pub_id, type, title=CAST(title as char(30))+ CASE WHEN LEN(title)30 THEN '...' ELSE " END, price FROM titles ORDER BY ytd_sales%10*(price-10) Будет возвращен следующий результат:

pub_id type title price 0877 UNDECIDED The Psychology of Computer Coo... NULL 1389 popular_comp Net Etiquette NULL 1192 Часть V. Программирование 0877 mod_cook The Gourmet Microwave 2. 0736 business You Can Combat Computer Stress... 2. 0736 psychology Emotional Security: A New Algo... 7. 0736 psychology Life Without Fear 7. 1389 popular_comp But Is It User Friendly? 22. 0736 psychology Is Anger the Enemy? 10. 1389 business Cooking with Computers: Surrep... 11. 0877 trad_cook Fifty Years in Buckingham Pala... 11. (10 row(s) affected) Конечно, трудно найти смысл в такой сортировке. Однако приведенный запрос отлично демонстрирует использование в качестве критерия упорядочения про извольных выражений, не входящих в результат выборки.

Глава 29. Выборка данных ко оно должно содержаться в результате выборки. Это значит, что параметр expression должен быть ничем иным, чем одним из столбцов результата.

Если столбец формируется на основе результата вычисления выражения и для него не указан псевдоним, то ссылка на этот столбец в разделе COMPUTE возможна только с помощью выражения, на основе которого вычисляются значения для этого столбца. Если же столбец имеет конкретное имя (напри мер, указанное с помощью псевдонима), то ссылка на этот столбец возможна и по имени.

О AVG. Возвращает среднее арифметическое.

П COUNT. Возвращает количество строк, в которых значение выражения агреги рования не равно NULL.

• МАХ. Возвращает максимальное значение выражения во всех строках результата.

• MIN. Возвращает минимальное значение выражения во всех строках результата.

• STDEV. Возвращает статистическое стандартное отклонение для всех значений выражения.

• STDEVP. Возвращает смещенную оценку стандартного отклонения (квад ратный корень от значения, возвращаемого функцией VARP) ДЛЯ всех значе ний столбца.

• VAR. Возвращает несмещенную оценку дисперсии величин для всех значений выражения.

• VARP. Возвращает смещенную оценку дисперсии (второй центральный мо мент выборки относительно выборочного среднего) для всех значений выра жения.

• SUM. Возвращает сумму всех значений выражения.

• BY expression. Этот параметр позволяет применять функции агрегирования не ко всему набору данных, а к отдельным группам. Например, если анализирует ся таблица продаж товаров, то можно выполнить группировку по наименова нию товара и подсчитать, какое количество товара было продано и на какую сумму. Параметр BY указывается после выражения (или выражений), по кото рому осуществляется агрегирование. Использование параметра BY требует обя зательного указания раздела ORDER BY. Кроме того, выражений, по которому выполняется группировка, должно быть указано в разделе ORDER BY, Т. е. дан ные в результате должны быть отсортированы по выражению группировки. Ес ли группировка выполняется по нескольким выражениям и после параметра BY указано более одного выражения, то каждое из этих выражений должно быть также приведено и в разделе ORDER BY. Причем порядок перечисления выра жений после параметра BY и в разделе ORDER BY должен совпадать. При.этом необязательно, чтобы после параметра BY были указаны все выражения, пред ставленные в разделе ORDER BY. Тем не менее, не допускается пропуск пара метров. Например, если используется ORDER BY columni, coiumn2, coiumn3, то все возможные варианты для раздела COMPUTE будут BY columni, coiumn2, column3;

BY columni, column2 И BY columni.

Часть V. Программирование Замечание При наличии раздела COMPUTE не допускается применение параметра DISTINCT, запрещающего включение в результат выборки повторяющихся строк.

Приведем несколько примеров использования раздела COMPUTE:

SELECT title=CAST(title as char(30))+ CASE WHEN LEN(title)30 THEN '...' ELSE " END, price, ytd_sales FROM titles COMPUTE AVG (price), SUM(ytd_sales), COUNT (ytd_sales) Глава 29. Выборка данных (24 row(s) affected) Часть V. Программирование Наконец, приведем пример с использованием группировки по двум столбцам:

SELECT TOP 10 t i t l e = C A S T ( t i t l e as c h a r ( 3 0 ) ) + CASE WHEN L E N ( t i t l e ) 3 0 THEN '... ' ELSE '' END, type, pub_id, y t d _ s a l e s FROM t i t l e s ORDER BY t y p e, p u b _ i d, p r i c e COMPUTE SUM(ytd_sales) BY t y p e, p u b _ i d Глава 29. Выборка данных Раздел FOR Этот раздел применяется только в приложениях, работающих с DB-Library, a также при работе с XML. Синтаксис раздела таков:

[FOR {BROWSE I XML (RAW I AUTO I EXPLICIT} [, XMLDATA] [, ELEMENTS] [, BINARY BASE64] }] Указание ключевого слова BROWSE позволяет приложениям DB-Library выпол нять изменение выбранных данных непосредственно через форму просмотра.

Однако указания лишь параметра FOR BROWSE недостаточно. Необходимо вы полнить еще некоторые требования:

• таблица должна включать столбец time stamp;

• в таблице должен быть создан уникальный индекс;

О раздел FOR BROWSE должен быть последним разделом команды SELECT.

Обновление данных средствами браузера будет возможно только при соблюде нии всех этих требований.

С~ Замечание ^ Параметр FOR BROWSE не может применяться в запросе, который указан как подзапрос раздела U N I O N. Кроме того, совместно с разделом FOR BROWSE недопустимо использо вание хинта HOLDLOCK, предписывающего удерживать блокировку до конца транзакции.

При указании параметра XML сервер будет выполнять преобразование выби раемых данных в формат документа XML, что позволяет просматривать данные с помощью Интернет-браузера. При этом SQL Server 2000 может использовать различные режимы преобразования:

П RAW. Каждой строке результата выборки присваивается идентификатор.

П AUTO. Результат выполнения запроса возвращается в виде простого дерева XML.

• EXPLICIT. В этом случае форма документа XML определяется явно. Пользо ватель должен в запросе определить, какие элементы в какие ветви будут включены. Для этого первые два столбца результата выборки используются в качестве служебных.

Помимо определения режима преобразования данных можно также указать до полнительные параметры:

О XMLDATA. При наличии этого ключевого слова в результат выборки не будет включено определение корневого элемента. Это необходимо при включении результата выполнения запроса в уже существующий документ. Если же па раметр XMLDATA не указывается, то результат выборки представляет собой са мостоятельный документ.

О ELEMENTS. Столбцы будут возвращаться как подэлементы. Если же параметр ELEMENTS не указывается, то столбцы рассматриваются как атрибуты XML.

• BINARY BASE64. Указание этого параметра предписывает серверу возвратить данные в двоичном 64-битовом формате.

11Q8 Часть V. Программирование РазделOPTION Раздел OPTION предназначен для контроля хода выполнения запроса. С помо щью специальных хинтов (hint) пользователь может установить тот или иной режим слияния, объединения или агрегирования данных, а также контролиро вать некоторые другие тонкости выполнения запроса.

Раздел OPTION имеет следующую структуру:

OPTION ( {(HASH I ORDER} GROUP I {CONCAT | HASH I MERGE} UNION I { LOOP | MERGE I HASH } JOIN ! FAST number_rows I FORCE ORDER I MAXDOP number I ROBUST PLAN * I KEEP PLAN | KEEPFIXED PLAN I EXPAND VIEWS [,...n]}) Рассмотрим назначение и использование параметров раздела:

П {HASH I ORDER} GROUP Эта конструкция определяет, какой алгоритм агрегирования будет приме няться в разделах GROUP BY И COMPUTE. При указании HASH используется хэ ширование, тогда как указание ORDER приведет к обычному упорядочиванию данных.

• {CONCAT | HASH | MERGE} UNION С помощью этой конструкции указывается способ объединения данных при существовании раздела UNION.

• {LOOP I MERGE | HASH} JOIN Определяет способ связывания данных при использовании раздела JOIN.

СЗ FAST number_rows При задании этого хинта сервер оптимизирует запрос для быстрой выборки первых number_rows строк. После этого выполнение запроса продолжится в обычном режиме. Такой подход позволяет быстро получить первую порцию данных, не дожидаясь обработки значительной части запроса.

• FORCE ORDER При указании этого хинта будет сохранен порядок связывания данных, при веденный в запросе. В противном случае оптимизатор запроса может исполь зовать другой порядок связывания, отличный от указанного пользователем.

О MAXDOP n u m b e r Этот хинт разрешает явно указать стоимость распараллеливания запроса. Это позволяет переопределить значения, установленные с помощью хранимой про цедуры s p c o n f i g u r e с использованием опции 'max degree of p a r a l l e l i s m 1.

• ROBUST PLAN При указании этого хинта сервер выстраивает план выполнения запроса таким образом, чтобы обеспечить поддержку обработки данных большой длины. В Глава 29. Выборка данных обычном режиме объем резервируемой памяти не позволяет обрабатывать строки большой длины, и при попытке выполнить запрос оптимизатор запро сов (Query Optimizer) выдаст сообщение об ошибке еще на стадии создания плана выполнения запроса. Однако следует осторожно использовать ROBUST PLAN, т. к. в этом случае скорость выполнения запроса существенно падает.

П KEEP PLAN Указание этого хинта предписывает серверу сохранить план исполнения за проса, подготовленный оптимизатором запроса. Точнее, снижается порог по вторной компиляции плана выполнения запроса, что повышает производи тельность выполнения запросов. Порог определяет частоту перекомпиляции запроса в зависимости от объема изменений данных в таблице.

• KEEPFIXED PLAN Этот хинт предписывает серверу не выполнять перекомпиляции плана ис полнения запроса даже при изменении статистики или индексов.

П EXPAND VIEWS Данный хинт предписывает серверу при работе с индексированными пред ставлениями не подставлять вместо имени представления запрос, на основе которого было построено соответствующее представление. Это гарантирует, что будут использованы все преимущества индексированных представлений.

• [,...п] Этот параметр говорит о том, что в разделе OPTION может быть указано мно жество хинтов. Тем не менее, следует учитывать, что при использовании раз дела UNION раздел OPTION может применяться только для последнего запроса и будет определять ход выполнения всех объединяемых запросов, рассматри ваемых в конечном счете как один запрос, к которому и относится раздел OPTION.

Глава Хранимые процедуры В одной из предыдущих глав были рассмотрены основы языка Transact-SQL, используемого в SQL Server 2000 для доступа к данным. В частности, в двух предыдущих главах обсуждались команды INSERT, SELECT, UPDATE И DELETE, ис пользуемые, соответственно, для добавления, выборки, изменения и удаления данных. В большинстве рассмотренных примеров производились простые опе рации, реализуемые всего несколькими командами. Выполнение этих операций, в частности написание кода команд, не вызывает особых затруднений. Пользо ватель может написать запрос непосредственно в окне Query Analyzer и выпол нить его. Тем не менее, основными клиентами баз данных являются приложе ния, специально созданные для выполнения определенных задач. Эти прило жения часто реализуют сложные операции, требующие использования множест ва команд.

Чтобы выполнить такие операции, приложение отсылает на сервер одну или более команд, которые там выполняются. В ответ сервер отправляет клиенту (приложению) результат обработки запроса. Этим результатом может быть как сообщение об успешном завершении выполнения команды, занимающее всего несколько байт, так и огромный массив данных, включающий тысячи строк и занимающий несколько мегабайт. Клиент может обработать полученный резуль тат и на основе своего полученного результата отослать серверу новый запрос.

В рассмотренной ситуации логика обработки данных реализована на клиенте.

Сервер просто принимает набор инструкций и выполняет их. При написании приложения разработчик должен позаботиться о разработке запросов, коррект но работающих с данными и выполняющих все нужные действия.

Предложенный подход к обработке данных имеет несколько недостатков. На пример, если нужно изменить логику обработки данных, то следует изменять исходный код программы, после чего заново компилировать ее и распростра нять всем пользователям. Кроме того, если одна и та же логика обработки дан ных используется в нескольких приложениях, то в худшем случае для каждого из этих приложений нужно будет повторять процесс разработки запросов, а в лучшем переносить код из уже работающего приложения. Также следует обра тить внимание на сам процесс взаимодействия сервера и клиента. По всей ви димости, алгоритмы обработки данных будут реализованы в виде набора блоков команд, поочередно отправляемых на сервер. После выполнения блока прило жение получает определенный результат, после обработки которого решается, какой следующий блок и с какими параметрами должен быть выполнен. В не которых ситуациях обмен между клиентом и сервером наборами команд и ре зультатами может занимать много времени и генерировать большой сетевой Глава 30. Хранимые процедуры трафик, что отрицательно сказывается на работе приложения в целом и на ра боте других пользователей сети.

Также необходимо сказать о безопасности. Для выполнения обработки данных пользователь должен иметь соответствующие права доступа. Предполагается, что эти права будут использованы приложением для доступа к данным. Однако нельзя быть до конца уверенным, что пользователь не сможет обратиться к дан ным напрямую, например, с помощью Query Analyzer, и выполнить неразре шенные действия. Также нельзя быть уверенным, что команды, отправляемые приложением, осуществляют верные действия. Ошибка разработчика при созда нии запроса может иногда привести к повреждению данных. Кроме того, нельзя не учитывать, что злоумышленник или тот же разработчик способен изменить код запроса для получения несанкционированного доступа к данным или для их повреждения и даже уничтожения.

Все сказанное выше демонстрирует недостатки подхода к разработке систем, когда логика обработки данных реализуется на клиенте. Описанные проблемы могут быть решены за счет переноса алгоритмов обработки данных на сервер. В этом случае приложение просто сообщает серверу, какой именно набор команд необходимо выполнить. Дополнительно могут быть указаны параметры, которые в зависимости от реализации алгоритма будут влиять на ход выполнения про цесса обработки данных. При этом приложение сможет получать только конеч ный результат выполнения. Все промежуточные результаты будут обработаны сервером. Это позволяет снизить сетевой трафик. Этот набор команд, хранимых на сервере, и выполняемых как одно целое, в терминологии SQL Server называется хранимой процедурой (stored procedure).

Использование хранимых процедур позволяет снизить стоимость сопровожде ния системы и дает возможность избавиться от необходимости изменять кли ентские приложения. Если понадобится изменить логику обработки данных, чтобы она отразилась для всех приложений сети, количество которых может насчитывать десятки и сотни, то достаточно будет изменить только хранимую процедуру.

Кроме того, использование хранимых процедур также позволяет значительно повысить безопасность данных. Приложение или пользователь получает лишь специальное право на выполнение хранимой процедуры, которая и будет обра щаться к данным. Доступа же к самим данным пользователь не получает. В хра нимой процедуре можно реализовать проверки на правильность выполняемых изменений, что обеспечит логическую целостность данных. Также можно реали зовать проверки на права пользователя выполнять те или иные действия.

Системные хранимые процедуры Хранимые процедуры активно используются для решения самых разнообразных задач, связанных с аспектами работы сервера и функционированием баз данных и всех ее объектов. Информация обо всех аспектах работы сервера хранится в системных таблицах. Самая важная из них, база данных Master, содержит ин 1202 Часть V. Программирование формацию о параметрах работы ядра SQL Server 2000, обладает данными об учетных записях пользователей, сведениями о созданных на сервере базах дан ных, а также многой другой информацией.

Данные хранятся в специальных системных таблицах, каждая из которых имеет конкретное назначение. Изменение значений в этих таблицах приводит к изме нению тех или иных аспектов работы сервера или баз данных. Пользователи могут изменять значения в этих таблицах напрямую с помощью команд UPDATE, INSERT, SELECT и DELETE. Однако такие изменения требуют специальных зна ний. Иногда выполнение неверной команды может вывести из строя весь сервер и будет очень трудно восстановить его работоспособность. Кроме того, часто для осуществления одной операции необходимо выполнить множество команд.

Чтобы избавить пользователя от рутины и обеспечить высокую безопасность выполняемых изменений, в SQL Server 2000 используются специальные систем ные хранимые процедуры (system stored procedure). Количество этих процедур со ставляет несколько сотен. Для удобства они разбиты на отдельные группы, каж дая из которых предназначена для решения специфических задач. Например, имеются процедуры для управления системой репликации, процедуры для управления системой безопасности, процедуры для управления курсорами, про цедуры для управления работой службы SQLServerAgent и некоторые другие.

Системные хранимые процедуры характеризуются тем, что они хранятся в сис темной базе данных Master и в своем названии имеют префикс sp, после кото рого следует символ подчеркивания и затем имя собственно процедуры. Напри мер, sp_help_fulltext_columns_cursor ИЛИ sp_attach_db.

Расширенные хранимые процедуры Расширенные хранимые процедуры (extended stored procedure) по своей природе коренным образом отличаются от системных и пользовательских хранимых процедур. Хотя вызов этих процедур и выполняется подобно другим процедурам, тем не менее, они представляют собой динамически подключаемые библиотеки (файлы dll). To есть расширенные хранимые процедуры представляют собой от дельные программы, которые в операционной системе могут играть роль само стоятельного приложения. Расширенные хранимые процедуры имеют префикс хр.

В SQL Server 2000 имеется набор встроенных расширенных хранимых процедур, в частности используемых для отправки сообщений по электронной почте и для трассировки обращений к SQL Server 2000. Пользователи могут создавать новые хранимые процедуры с помощью любого языка, допускающего создание биб лиотек. При этом они должны использовать интерфейс программирования SQL Server Open Data Services API.

Готовую библиотеку необходимо зарегистрировать в SQL Server 2000, после чего пользователи смогут применять реализованные в ней функции. Регистрация биб лиотеки производится при помощи хранимой процедуры sp_addextendedproc, имеющей следующий синтаксис:

sp_addextendedproc (Sfunctname =] 'procedure', [Sdllname =] ' d l l ' Глава 30. Хранимые процедуры Параметр Sfunctname определяет имя, по которому будет вызываться функция.

Можно использовать любое имя, в том числе и с префиксом sp или хр. Однако это необязательно. Параметр @dllname задает путь к нужному dll-файлу. Регист рация расширенных хранимых процедур должна выполняться в контексте базы данных Master.

Типичным примером расширенной хранимой процедуры является процедура xpcmdsheli, с помощью которой можно вызвать интерфейс командной строки и выполнить любые команды. Приведенный ниже пример выводит в окне Query Analyzer содержимое корневого каталога диска С:.

xp_cmdshell 'dir c:\' Будет получен результат:

output В приведенном примере выводится просто список файлов, однако с таким же ус пехом можно выполнить удаление файлов на диске. Для выполнения процедуры x p c m d s h e l i необходимы права системного администратора (system adminis trator).

1204 Часть У. Программирование Другим примером использования расширенных хранимых процедур является отправка сообщений по электронной почте с помощью процедуры xpsendmaii.

Данная процедура позволяет отправлять не только сообщения» но и прикреп лять к ним результат выполнения запроса. Приведенный ниже пример отправ ляет на адрес emamaev@khakasnet.ru результат выборки ИЗ таблицы authors:

EXEC xp_sendmail g r e c i p i e n t s = 'emamaev@khakasnet.ru', @query = 'SELECT * F O p u b s.. a u t h o r s ', RM @subject = 'SQL Server R e p o r t ', Smessage = 'The c o n t e n t s of t a b l e p u b s.. a u t h o r s ', @attach_results = 'TRUE', Swidth = Возможность применения динамически подключаемых библиотек раскрывает перед разработчиками SQL Server 2000 огромные возможности, предоставляя в их распоряжение все ресурсы операционной системы. С их использованием можно легко написать проигрыватель музыкальных файлов, которые хранятся в базе данных SQL Server 2000.

Создание хранимой процедуры В двух предыдущих разделах было рассказано о системных и расширенных хра нимых процедурах. Однако наибольший интерес для разработчиков представля ют пользовательские хранимые процедуры, предназначенные для реализации алгоритмов обработки данных. Такие хранимые процедуры создаются на уровне конкретной базы данных. В SQL Server 2000 имеется специальное право досту па — EXECUTE, с помощью которого можно разрешить пользователю или роли базы данных выполнять указанную процедуру.

Создание хранимой процедуры производится с помощью команды CREATE PROCEDURE, имеющей следующий синтаксис:

CREATE PROCEDURE] p r o c e d u r e _ n a m e [;

number] [ {@parameter d a t a _ t y p e ) [VARYING] [= d e f a u l t ] [OUTPUT] ] [,... n ] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [FOR REPLICATION] AS s q l _ s t a t e m e n t [... n ] Рассмотрим назначение и использование параметров команды:

О procedure_name С помощью этого параметра указывается имя, которое будет иметь хранимая процедура. Имя должно следовать общим правилам именования объектов ба зы данных, которые были рассмотрены в главе 18. Если в имени процедуры используются недопустимые символы, например пробелы, то имя должно быть заключено в ограничители (квадратные скобки или двойные кавычки).

В SQL Server 2000 допускается создание глобальных и локальных временных хранимых процедур. Для этого перед именем процедуры ставится символ # (локальные) или символы ## (глобальные). Оба типа хранимых процедур ав томатически уничтожаются при закрытии соединения, в котором они были созданы. Глобальные временные процедуры доступны для выполнения изо Глава 30. Хранимые процедуры всех соединений, тогда как локальные могут вызываться только из того со единения, в котором они были созданы. Максимальная длина имени храни мой процедуры, включая символы # и ##, не должна превышать 128 симво лов. Дополнительно к имени самой процедуры разрешается указание имени владельца. Например, ' a n d y k. g e t _ s i l t _ s t u d e n t '. Указание имени базы дан ных не разрешается, поэтому хранимую процедуру можно создать только в текущей базе данных.

П /number Этот параметр предназначен для создания группы одноименных процедур.

При этом дополнительно к имени хранимой процедуры можно указать через точку с запятой целое число, которое будет являться номером процедуры.

Такой подход позволяет управлять несколькими процедурами как одним це лым. Например, если приложение использует 20 хранимых процедур и все их необходимо уничтожить, то при работе с группой для удаления всех проце дур достаточно выполнить всего одну команду DROP PROCEDURE. Номера про цедур не обязательно должны следовать друг за другом. Например, первая процедура может иметь номер 63, вторая 7, а третья 92. Если в имени проце дуры используются неразрешенные символы и оно заключено в ограничите ли, то номер процедуры должен указываться вне ограничителей. В против ном случае номер процедуры будет рассматриваться как часть имени.

Например [andyk]. [Get List student],-3. Для вызова одной из процедур группы также нужно будет указать ее номер: EXEC [andyk]. [Get List s t u dent],-з. При работе с группой процедур с помощью Enterprise Manager все процедуры будут сохранены под одним именем.

П Sparameter С помощью этого аргумента для хранимой процедуры указываются входные параметры. При вызове хранимой процедуры пользователь должен будет ука зать значения для этих параметров, если не определено значение по умолча нию. С помощью параметров хранимой процедуры пользователи могут управ лять логикой ее выполнения. В принципе, хранимая процедура может не иметь ни одного параметра. Но даже если параметры указаны, то они могут никак не влиять на ход выполнения процедуры. Параметры представляют собой не что иное, как обычные локальные переменные, создаваемые во время выполнения процедуры. Однако этим переменным автоматически присваиваются значения, указанные пользователями при вызове хранимой процедуры. В теле хранимой процедуры эти переменные используются точно так же, как и переменные, объявленные с помощью команды DECLARE. Как и все обычные переменные, имя параметра хранимой процедуры должно начи наться с символа @ и следовать стандартным правилам именования объектов.

П data_type Этот параметр определяет тип данных, который будет иметь соответствую щий параметр хранимой процедуры. Допускается использование не только встроенных в SQL Server 2000, но также и определенных пользователями ти пов данных (UDDT, user defined data type). Также разрешается использование 1206 Часть V. Программирование типа данных cursor, позволяющего передавать в процедуру или из нее набо ры строк, что представляет удобный механизм для обмена большими блока ми данных, не умещающимися в одну переменную.

• VARYING Это ключевое слово используется только для параметров хранимой процедуры, имеющих тип данных cursor. Использование VARYING необходимо, когда в хранимой процедуре происходит создание динамического курсора и получен ные данные нужно возвратить из процедуры для дальнейшего использования.

О default С помощью этого аргумента указывается значение по умолчанию, которое будет автоматически присваиваться соответствующему параметру хранимой процедуры, если при ее вызове пользователь не задал явного значения пара метра. Применение значения по умолчанию для параметра хранимой проце дуры очень напоминает указание значений по умолчанию для столбцов таб лицы. В обоих случаях, если не приведено конкретное значение, то будет использоваться значение по умолчанию. При работе с хранимыми процеду рами это позволяет упростить их вызов, освободив пользователя от необхо димости ввода всех параметров, количество которых может достигать не скольких десятков. В качестве значения по умолчанию разрешается исполь зовать только константы. Применение функций, вызовов хранимых проце дур, имен столбцов, подзапросов и т. д. не разрешается. При необходимости значение параметров хранимой процедуры можно изменить в теле процеду ры, проверив, что оно совпадает со значением по умолчанию, в качестве ко торого допускается также использовать значение NULL.

• OUTPUT Это ключевое слово приводится, когда необходимо возвратить измененное значение параметра хранимой процедуры. Например, если при вызове хра нимой процедуры в качестве значения параметра используется переменная, то при указании ключевого слова OUTPUT после завершения выполнения процедуры значение этой переменной будет относиться к значению соответ ствующего параметра хранимой процедуры. Не стоит понимать, что при ука зании OUTPUT параметр процедуры используется исключительно как выход ной. Он также может быть применен и в качестве входного параметра.

п Эта конструкция подразумевает, что для хранимой процедуры может быть указано множество параметров, общее количество которых может достигать 1024. Параметры должны быть указаны через запятую. Для каждого из них обязательно указывается тип данных. Дополнительно можно указать значе ние по умолчанию и параметры VARYING И OUTPUT.

3 WITH После этого ключевого слова приводятся дополнительные опции хранимой процедуры. Рассмотрим их:

Глава 30. Хранимые процедуры RECOMPILE. Использование этого параметра предписывает не выполнять • кэширование плана выполнения хранимой процедуры. То есть всякий раз при вызове процедуры будет происходить генерирование плана обработки запроса. В обычных ситуациях использование этого параметра не реко мендуется.

• ENCRYPTION. При указании данного параметра происходит шифрование кода процедуры в таблице syscomments, где хранится исходный текст процедуры.

Если разработанный алгоритм является коммерческой тайной, то использо вание шифрования позволит сохранить в тайне код процедуры.

• FOR REPLICATION. Этот параметр применяется только при выполнении ре пликации хранимых процедур. Указанный тип репликации позволяет пере давать от издателя к подписчикам не весь набор выполненных изменений, и лишь вызов хранимой процедуры со значениями всех входных парамет ров. Таким образом можно резко снизить объем сетевого трафика. Хра нимая процедура, созданная на подписчике с использованием FOR REPLICATION, не может быть вызвана пользователем. Ее запуск разрешен только подсистеме репликации.

• AS После этого ключевого слова начинается тело хранимой процедуры, которое содержит набор команд Transact-SQL.

О sql_statement [...п] Этот параметр подразумевает указание собственно команд Transact-SQL, из которых и формируется тело хранимой процедуры. В теле процедуры могут присутствовать вызовы других процедур, команды создания, фиксирования и отката транзакций, команды создания объектов базы данных, команды управ ления данными и т. д.

Использование номера процедуры Для лучшего понимания использования хранимых процедур и команды CREATE PROCEDURE рассмотрим несколько примеров создания и вызова хранимых проце дур. Для начала рассмотрим создание простой хранимой процедуры, которая выводит список всех авторов, проживающих вне Калифорнии:

CREATE PROCEDURE get_list_authors;

AS SELECT au_id, au_lname, state, phone FROM pubs..authors WHERE state!='CA' После того, как процедура будет создана, можно будет приступить к ее исполь зованию:

E E get_list_authors;

l XC Будет возвращен следующий результат:

au_id au_lname state phone 341-22-1782 Smith KS 913 843- •1208 Часть У. Программирование 527-72-3246 Greene 648-92-1872 Blotchet-Halls 712-45-1867 del Castillo 722-51-5454 DeFrance 807-91-6654 Panteley 899-46-2035 Ringer 998-72-3567 Ringer (8 row(s) affected) В приведенном примере была создана хранимая процедура, содержащая всего одну команду и не использующая параметров. Помимо имени процедуры был использован номер, который'также был указан при вызове процедуры.

Использование параметров Теперь создадим процедуру с тем же именем, но другим номером и использую щую параметр:

CREATE PROCEDURE g e t _ l i s t _ a u t h o r s ;

@state char(2) = 'UT' AS SELECT au_id, au_lnarae, s t a t e, phone F O pubs.Tauthors WHERE s t a t e = @ s t a t e RM В процедуре имеется параметр e s t a t e, который используется для выборки авто ров из определенного штата. Для этого параметра указано значение по умолча нию, равное ' ит'. То есть, если при вызове процедуры не будет явно указано значение параметра e s t a t e, то выведенным окажется список авторов, прожи вающих в штате Юта:

E E get_list_authors;

XC Будет возвращен результат:

au_id au_lname state phone 899-46-2035 Ringer UT 801 826- 998-72-3567 Ringer UT 801 826- (2 row(s) affected) Теперь попробуем указать значение входного параметра:

E E get_list_authors;

6 'CA' XC Будет возвращен такой результат:

au_id au_lname 172-32-1176 White 213-46-8915 Green 238-95-7766 Carson 267-41-2394 O'Leary 274-80-9391 Straight 409-56-7008 Bennet 427-17-2319 Dull 472-27-2349 Gringlesby 486-29-1786 Locksley Возвращение значений из процедуры Теперь же создадим хранимую процедуру, которая будет использовать параметр OUTPUT для возвращения значений. Процедура должна выполнять поиск телефо на автора по фамилии и имени или по идентификационному номеру автора.

Кроме того, также можно будет выполнить обратную операцию — возвратить идентификационный номер автора, его имя и фамилию по номеру телефона.

В процедуре нужно будет как-то определить, хотим мы найти номер автора по его данным или получить данные об авторе по его номеру телефона. Для этого мы будет проверять значение входного параметра процедуры, соответствующего номеру телефона. Если он содержит значение NULL, TO выполняется поиск но мера телефона по имени с фамилией или по идентификационному номеру. В противном случае телефонный номер будет использоваться для получения дан ных об авторе.

CREATE PROCEDURE g e t _ a u t h o r _ i n f o Sphone char(12)=NULL OUTPUT, @au_id char(11)=NULL OUTPUT, SLName char(40)=NULL OUTPUT, @FName char(20)=NULL OUTPUT AS IF Sphone IS NOT NULL SELECT @au_id=au_id, @LName=au_lname, @FName=au fname FROM a u t h o r s WHERE phone=@phone ELSE IF @au_id IS NOT NULL SELECT @phone=phone FROM a u t h o r s WHERE a u _ i d = @ a u _ i d ELSE SELECT @phone=phone FROM a u t h o r s WHERE au_lname=@LName AND au_fname=@FName Теперь используем эту процедуру для поиска идентификационного номера ав тора по номеру телефона:

DECLARE @phone c h a r ( 1 2 ), @ID c h a r ( 1 1 ), OLastName c h a r ( 4 0 ), @FirstName c h a r ( 2 0 ) SET @phone='503 7 4 5 - 6 4 0 2 ' EXEC g e t _ a u t h o r _ i n f o S p h o n e, @ID OUTPUT, SLastName OUTPUT, S F i r s t N a m e OUTPUT SELECT [ID а в т о р а ] = @ Ю, [Фамилия а в т о р а ] =@LastName, [Имя а в т о р а ] = @ F i r s t N a m e 1210 Часть V. Программирование Будет получен результат:

ID автора Фамилия автора Имя автора 648-92-1872 Blotchet-Halls Reginald (1 row(s) affected) Теперь же попробуем получить номер телефона автора по его имени и фамилии:

DECLARE Sphone char (12), @LastName char(40), SFirstName char(20) SET SLastName = 'Gringlesby' SET @FirstName = 'Burt' EXEC get_author_info @phone OUTPUT, @Lname = @LastName, @Fname = SFirstName SELECT [Фамилия автора] = SLastName, [Имя автора] = SFirstName, [Телефон] = @phone Будет получен такой результат:

Фамилия автора Имя автора Телефон Gringlesby Burt 707 938- (1 row(s) affected) Хотя в рассмотренных примерах использовалось всего несколько команд, на практике количество команд может составлять несколько десятков, а то и сотен.

Однако приведенные примеры позволяют понять механизм обмена данными между внешним приложением и хранимой процедурой.

( Замечание ^) Помимо обмена данными с помощью параметров также имеется возможность вставки в таблицу результата выборки, возвращаемого хранимой процедурой. Эта операция выполняется с помощью команды INSERT INTO, работа с которой была рассмотрена в главе 28.

Работа с курсорами Если с обменом данными через переменные ситуация прояснилась, то обмен данными с использованием курсоров требует дополнительных пояснений. Под робно использование курсоров будет рассмотрено в одной из следующих глав, однако все же приведем пример обмена сложными блоками данных. Создадим хранимую процедуру, которая будет возвращать курсор одного из двух типов.

Первый из них будет содержать список авторов, написавший книги указанной серии, тогда как второй станет содержать собственно список книг. Тип курсора будет указываться при вызове хранимой процедуры с помощью параметра @mode. Если этот параметр равен 1, то возвращается список авторов. Для полу чения списка книг необходимо будет использовать 2. Во всех остальных случаях будет возвращено сообщение об ошибке.

Глава 30. Хранимые процедуры CREATE PROCEDURE get_series_info @type char(12), SResult CURSOR VARYING OUTPUT, @mode int=l AS IF @mode=l BEGIN SET @Result = CURSOR STATIC FOR SELECT au_id, au_lname, state, phone FROM pubs..authors WHERE au_id IN (SELECT au_id FROM titleauthor ta INNER JOIN titles t ON ta.title_id = t.title_id AND type=@type) OPEN @Result RETURN END IF @mode= BEGIN SET OResult = CURSOR STATIC FOR SELECT title_id, title = CAST(title as char(30)), price, ytd_sales FROM titles WHERE type=Stype OPEN OResult RETURN END RAISERROR('Указаны неверные параметры при вызове хранимой процедуры',1,11) Теперь с помощью созданной процедуры попробуем получить список всех авто ров, написавших книги серии business:

DECLARE @curs cursor, @ID char(11), @phone char(12), SLastName char(40), @State char(2) EXEC get_series_info 'business', @curs output FETCH FIRST FROM Scurs INTO @ID, @LastName, SPhone, estate SELECT [ID автора]=@Ю, [Фамилия автора] =@LastName, [Телефон]=@phone, [IIlTaT]=@State WHILE @@FETCH_STATUS= BEGIN FETCH NEXT FROM @curs INTO @ID, SLastName, @Phone, estate SELECT [ID автора]=@ID, [Фамилия автора]=@LastName, [Телефон]=@phone, [UlTaT]=@State END Будет получен результат:

ID автора Фамилия автора Телефон Штат 213-4 6-8915 Green CA (1 row(s) affected) ID автора Фамилия автора Телефон Штат 267-41-2394 O'Leary CA (1 row(s) affected) j2)2 Часть V. Программирование ID автора Фамилия автора Телефон Штат 274-80-9391 Straight CA (1 row(s) affected) ID автора Фамилия автора Телефон Штат 409-56-7008 Bennet CA (1 row(s) affected) ID автора Фамилия автора Телефон Штат 724-80-9391 MacFeather ' CA (1 row(s) affected) ID автора Фамилия автора Телефон Штат 724-80-9391 MacFeather CA (1 row(s) affected) Теперь же попробуем получить список книг серии business:

DECLARE @curs cursor, @ID char(6), @Name char(30), Sprice numeric, @sales int EXEC get_series_info 'business1, @curs output, FETCH FIRST FROM @curs INTO SID, @Name, SPrice, SSales SELECT [ID книги]=@ID, [Название произведения]=@Name, [Цена]=@рг1се, [Продано]=@sales WHILE 8@FETCH_STATUS= BEGIN FETCH NEXT FROM Scurs INTO 8ID, @Name, @Price, @Sales SELECT [ID книги]=@ID, [Название произведения]=@Name, [Цена]=@рг1се, [Продано]=@sales END Будет получен результат:

ID книги Название произведения Цена Продано BU1032 The Busy Executive's Database 20 (1 row(s) affected) ID книги Название произведения Цена Продано BUI111 Cooking with Computers: Surrep 12 (1 row(s) affected) ID книги Название произведения Цена Продано BU2075 You Can Combat Computer Stress 3 (1 row(s) affected) Глава 30. Хранимые процедуры ID книги Название произведения Цена Продано BU7832 S t r a i g h t Talk About Computers 20 (1 row(s) affected) ID книги Название произведения Цена Продано BU7832 S t r a i g h t Talk About Computers 20 (1 row(s) affected) Приведенный пример хранимой процедуры демонстрирует использование одной и той же процедуры для получения самых разнообразных результатов. Пользователи могут включать в процедуры различные проверки на права доступа пользователей выполнять те или иные действия. Например, в последней процедуре можно было реализовать проверку имени пользователя, вызвавшего процедуру, и разрешить получение списка авторов по серии только нескольким пользователям, тогда как получение списка книг одной серии разрешить всем пользователям.

Использование кода завершения В последнем примере была указана команда RETURN, использование которой допускается только внутри хранимой процедуры. Когда встречается эта команда, выполнение хранимой процедуры прерывается и управление передается в то место, откуда произошел вызов процедуры. При этом в команде RETURN допус кается указание кода завершения. Для этого в конец команды просто добавляет ся целое число, которое и будет являться кодом завершения процедуры. Если значение не указано явно, то будет возвращено значение 0.

Замечание Код возврата можно рассматривать как еще один механизм обмена данными между клиентом и хранимой процедурой.

Конкретное назначение того или иного возвращаемого значения зависит от раз работчика. Обычно при удачном завершении процедуры возвращается значение 0. Код возврата может быть как положительным, так и отрицательным. Рас смотрим хранимую процедуру, которая использует код завершения процедуры для возвращения количества книг, стоимость которых выше значения, указан ного при вызове процедуры:

CREATE PROCEDURE get_series_info;

@price NUMERIC (6,2) AS DECLARE @cnt INT SELECT Sent = COUNT(*) FROM titles WHERE price=@price RETURN Sent Для получения кода завершения выполнения хранимой процедуры используется конструкция EXEC variable = stored procedurex Запустим созданную процедуру и получим код завершения:

Часть V. Программирование DECLARE @aa int EXEC @aa=get_series_info;

27 SELECT 'Количество книг'=@аа Будет возвращен следующий результат:

Количество книг (1 row(s) affected) Изменение хранимых процедур Как бы хорошо ни разрабатывалась хранимая процедура, все равно возникает надобность изменить в ней одну или более команд. Для этого используется команда ALTER PROCEDURE, имеющая следующий синтаксис:

ALTER PROC[EDORE] procedure_name [;

number] [{@parameter data_type } [VARYING] [= d e f a u l t ] [OUTPUT]] [,...n] [WITH {RECOMPILE I ENCRYPTION I RECOMPILE, ENCRYPTION}] [FOR REPLICATION] AS sql_statement [... n ] При выполнении изменений средствами Transact-SQL нет возможности моди фицировать часть команд, оставив другие без изменения. Команда ALTER PROCEDURE выполняет исправление всего кода процедуры. То есть пользователь при выполнении изменения должен указать весь код хранимой процедуры. При модификации процедуры с помощью Enterprise Manager пользователи могут ви деть код хранимой процедуры и могут изменять только ее часть. У пользователя может сложиться впечатление, что он изменяет непосредственно код процеду ры. Однако на самом деле происходит полная перезапись старого кода новым кодом, введенным пользователем.

с Замечание Одним из преимуществ использования команды A L T E R PROCEDURE вместо удаления и затем повторного создания процедуры является возможность сохранения прав дос тупа пользователей к хранимой процедуре. При удалении процедуры права доступа пользователей теряются, и после повторного создания процедуры необходимо будет заново предоставить пользователям право на выполнение процедуры. При использо вании команды A L T E R PROCEDURE права доступа пользователей сохраняются.

В SQL Server 2000 имеется хранимая процедура s p h e i p t e x t, с помощью кото рой можно просмотреть код другой хранимой процедуры. Например, попробуем вывести код хранимой процедуры get_series_infо, которая была создана в од ном из предыдущих примеров:


sp_helptext 'get_series_infо' Будет получен следующий результат:

Text Глава 30. Хранимые процедуры CREATE PROCEDURE get_series_info @type char(12), SResult CURSOR VARYING OUTPUT, @mode int = AS IF @mode = BEGIN SET SResult = CURSOR STATIC FOR SELECT au_id, au_lname, state, phone FROM pubs..authors WHERE au_id IN (SELECT au_id FROM titleauthor ta INNER JOIN titles t ON ta.title_id = t.title_id AND type=@type) OPEN @Result RETURN END IF @mode = BEGIN SET @Result = CURSOR STATIC FOR SELECT title_id, title = CAST(title as char(30)), price, ytd_sales FROM titles WHERE type=@type OPEN SResult RETURN END RAISERROR('Указаны неверные параметры при вызове хранимой процедуры',1,11) CREATE PROCEDURE get_series_info;

@price NUMERIC (6,2) AS DECLARE @cnt INT SELECT @cnt = COUNT ( ) FROM titles WHERE price=@price * RETURN @cnt Как видно, под именем одной процедуры на самом деле хранится две процеду ры. При внесении изменений следует учитывать эту особенность. Полученный текст можно использовать для выполнения изменений, достаточно скопировать его в буфер обмена и перенести в окно команд Query Analyzer.

с Замечание При модификации хранимой процедуры в Enterprise Manager для получения кода процедуры также используется хранимая процедура s p _ h e l p t e x t. Полученный текст отображается в диалоговом окне, в котором пользователь может вносить не обходимые изменения. При завершении редактирования и сохранении изменений происходит удаление старой процедуры, и вместо нее создается новая с тем же именем. Если обратить внимание, то в окне Enterprise Manager выводится весь код, с помощью которого выполняется создание процедуры, включая слова CREATE PROCEDURE, описание параметров и т. д.

Мы использовали хранимую процедуру s p h e l p t e x t для получения кода соз данной нами же процедуры базы данных pubs. Однако с помощью этой проце 1216 Часть V. Программирование дуры можно легко получить код и системных хранимых процедур, хранящихся в базе данных Master. Например, выведем код хранимой процедуры sp_addgroup:

USE master EXEC s p _ h e l p t e x t 'sp_addgroup' Будет возвращен следующий рерультат:

Text create procedure sp_addgroup Sgrpname sysnarae — name of new role as declare Sret int execute @ret = sp_addrole §grpname return Sret Как видно из приведенного кода, процедура sp_addgroup просто выполняет вы зов процедуры s p a d d r o l e, передавая ей входной параметр и возвращая код возврата, полученный после выполнения процедуры s p a d d r o l e. Дело в том, что процедура spaddgroup оставлена лишь для обеспечения совместимости с предыдущими версиями SQL Server. Но так как в SQL Server 2000 группы не используются, а вместо них предложены роли, то вместо создания группы будет создана пользовательская роль базы данных с таким же именем.

Удаление хранимых процедур Процедура может быть удалена при выполнении изменений или просто из-за не надобности. Для удаления хранимых процедур используется следующая команда:

DROP PROCEDURE { p r o c e d u r e } [,...n] С помощью одной команды DROP PROCEDURE МОЖНО удалить множество процедур.

Удаление процедуры включает удаление из системной таблицы sysobjects строки с информацией о хранимой процедуре, а также удаление из таблицы syscomments собственно кода хранимой процедуры. Так как SQL Server 2000 не позволяет вос станавливать удаленные строки таблиц, то нельзя будет и восстановить хранимую процедуру после удаления. Не стоит удалять процедуру, если есть даже небольшая вероятность того, что она в будущем снова может понадобиться. Процедуры за нимают не так много места, чтобы стремиться избавиться от них. При необходи мости можно изменить имя процедуры, добавив префикс, по которому можно было сразу же определить, что процедура не используется и может быть удалена.

В качестве такого префикса можно использовать, например • wasted При удалении процедур следует учитывать, что с помощью команды DROP PROCEDURE нельзя удалить отдельную процедуру из группы, а можно лишь уб рать всю группу процедур целиком.

Если все же необходимо удалить не всю группу, а лишь одну процедуру, то для этого можно использовать команду ALTER TABLE ИЛИ ВЫПОЛНИТЬ удаление всей группы и затем создать только нужные процедуры. Получить текст некоторой процедуры можно с помощью хранимой процедуры s p h e l p t e x t.

Глава Использование курсоров В ответ на запросы пользователей SQL Server 2000 может возвращать сотни ты сяч строк, общим объемом в десятки мегабайт. Передача такого объема данных по сети одновременно многими пользователями может вызвать значительную загрузку, что отрицательно скажется на работе всех пользователей сети. Кроме того, не каждый клиент имеет достаточный объем памяти, чтобы сохранить все полученные данные. К тому же обычно клиент работает лишь с небольшой ча стью данных, например отдельной строкой, а не со всем набором строк. То есть клиенту не нужен одновременно весь набор данных. Для него было бы предпоч тительней получать с сервера результат выборки отдельными порциями. Это бы позволило снизить требования к мощности компьютера-клиента и уменьшить интенсивность сетевого трафика. Кроме того, часто бывает просто необходимо иметь возможность обратиться к конкретной строке выборки по ее номеру, од нако с помощью команды SELECT сделать ЭТО ДОВОЛЬНО трудно.

Механизмом, обеспечивающим хранение результата выборки на сервере и пре доставляющим пользователю возможность доступа к любой строке выборки по ее номеру, являются курсоры (cursors). Курсор представляет своего рода окно, накладываемое на результат выборки. Пользователь может работать в каждый момент времени только с одной строкой, но, перемещая окно, он способен по лучить доступ к любой строке выборки.

Исходный набор данных, к которому обращается пользователь, называется полным набором строк (complete set of rows). В результате выполнения запроса SELECT пользователю возвращается набор данных, называемый результирующим набором (resulting set). Результирующий набор формируется в результате применения к полному набору строк горизонтального и вертикального фильтров. Горизонталь ная фильтрация выполняется с помощью указания одного или более логических условий в разделе WHERE. Вертикальная же фильтрация подразумевает включение в результирующий набор не всех столбцов исходного набора данных. Горизонталь ная и вертикальная фильтрации могут использоваться как по отдельности, так и вместе. Курсоры SQL Server 2000 работают с результирующим набором, предлагая пользователям дополнительные средства по его обработке.

При создании курсора пользователь указывает запрос SELECT, на основе кото рого создается результирующий набор данных. Аналогично формируется и представление. Однако с представлением пользователь работает как с обычной таблицей, используя стандартные команды обработки данных — SELECT, INSERT, UPDATE и DELETE. При этом сервер обращается ко всем строкам результирую 1218 Часть V. Программирование щего набора (если не указано условие в разделе WHERE). При работе с курсорами в каждый момент времени пользователь может работать только с одной строкой.

Каждая команда работы с данными курсора оперирует лишь с одной строкой результирующего набора. Если курсор содержит 1000 строк и нужно подсчитать сумму по одному из столбцов, то нужно будет организовать цикл и последова тельно обращаться к каждой строке. В итоге необходимо будет выполнить 1000 команд чтения данных. При использовании обычных средств работы с данными того же результата можно добиться с помощью единственной команды SELECT. Однако если вам требуется сформировать в приложении раскрываю щийся список, добавляя в него по одному элементу, то использование команды SELECT будет невозможно. Пользователь должен будет последовательно считы вать данные из каждой строки и на их основе формировать каждый элемент раскрывающегося списка. В этом случае именно курсоры предлагают лучшее решение задачи.

Во многих языках программирования позволяют обращаться к произвольным элементам результирующего набора. То есть, получив в ответ на запрос SELECT от сервера результирующий набор, приложение автоматически формирует набор строк (record set), который позволяет пользователям обращаться к отдельной строке результирующего набора в произвольном порядке. Возможно, при фор мировании раскрывающегося списка этот подход будет более предпочтителен.

Однако в этом случае весь набор данных хранится на клиенте.

Использование курсоров SQL Server 2000 дает преимущества, когда обработка данных большей частью происходит на сервере, а не на клиенте. То есть прило жение формирует на сервере набор данных, после чего отправляет запросы, обра щающиеся к этому набору данных. В ответ может передаваться только результат обработки данных, размер которого значительно меньше объема всего результи рующего набора.

Операции обработки данных с использованием курсоров выполняются заметно медленнее обычных команд обработки данных и не позволяют работать со всеми строками одновременно. Поэтому всегда, когда это возможно, следует избегать применения курсоров и пользоваться командами SELECT, UPDATE, INSERT И DELETE.

Виды курсоров По месту хранения и принципам работы курсоры классифицируются следую щим образом:

• Курсоры Transact-SQL (Transact-SQL Cursors). Создание курсоров этого типа и работа с ними ведется средствами команд Transact-SQL. Эти курсоры соз даются на сервере. Интенсивное применение курсоров может потребовать использования дополнительной оперативной памяти для хранения данных курсоров. Курсоры Transact-SQL могут создаваться и работать в транзакциях, хранимых процедурах и триггерах.

• Курсоры API сервера (API Server Cursors). Этот тип курсоров используется приложениями при работе с различными механизмами доступа к данным Глава 31. Использование курсоров (ODBC, OLE DB, DB Library и т. д.)- Используя соответствующий API, кли ент выполняет команду создания курсора. API сервера принимает запрос и создает на сервере курсор Transact-SQL. Работа с этим курсором выполняет ся средствами API, реализующего все базовые операции с курсорами и, воз можно, некоторые дополнительные операции. Как и в случае с курсорами Transact-SQL, при действиях с курсорами API сервера данные хранятся на сервере.


П Курсоры клиента (Client Cursors). Этот тип курсоров создается непосредст венно на клиенте. Сервер обрабатывает отправленный клиентом запрос и возвращает ему результирующий набор. Клиент получает весь результирую щий набор и уже сам организует нужные механизмы доступа к данным. Такой подход весьма удобен при работе с небольшим набором данных, т. к. позволяет повысить производительность за счет уменьшения количества обращений к се ти, требующих определенного времени на обработку. Однако при работе с большими наборами данных каждый из клиентов должен иметь необходи мый объем оперативной памяти.

Как уже было сказано, формирование содержимого курсоров происходит на основе результата, возвращаемого после выполнения запроса SELECT. ЭТО по зволяет в одном курсоре работать с данными, находящимися в разных таблицах.

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

Хотя ранее говорилось, что курсоры разрешают одновременно работать лишь с одной строкой, тем не менее, существуют так называемые блочные курсоры, по зволяющие за одну операцию обращаться сразу к нескольким строкам результи рующего набора. Однако курсоры этого типа являются своего рода расширени ем стандартного курсора. В SQL Server 2000 блочные курсоры могут создаваться средствами ODBC, OLE DB, ADO или DB Library. Однако создание блочных курсоров средствами Transact-SQL не поддерживается.

По способу обращения к данным курсоры можно разделить на две следующие категории:

О Последовательные (Forward-only). Этот тип курсоров разрешает только по следовательное считывание строк, начиная с первой строки и заканчивая по следней. После выполнения команды выборки сервер автоматически пере мещает указатель на следующую строку. Сам пользователь не может управ лять ходом выборки строк — например, считать предыдущую строку или строку через две после текущей. Последовательные курсоры, хотя и обладают ограниченной функциональностью, работают быстрее прокручиваемых кур соров.

• Прокручиваемые (Scrollable). В отличие от последовательных курсоров, кур соры этого типа позволяют обращаться к произвольной строке результирую щего набора. В распоряжении пользователей имеются средства как последо вательного обращения к строкам курсора, так и средства работы со строками 1220 Часть V. Программирование по их порядковому номеру в результирующем наборе. Направление перебора строк при последовательном обращении может быть не только прямым (от первой строки к последней), но и обратным (от последней к первой). Кроме того, можно в произвольном порядке комбинировать команды последова тельного и произвольного обращения к строкам курсора.

Замечание Серия последовательных выборок данных из курсоров называется прокручиванием (или скроллингом) курсора. Для последовательных курсоров допускается только прямой скроллинг, тогда как для прокручиваемых также и обратный.

Курсоры Transact-SQL В этой книге мы не будем рассматривать курсоры API сервера и курсоры клиен та, ограничившись подробным рассмотрением курсоров Transact-SQL.

В SQL Server 2000 реализовано четыре типа курсоров, обладающих разными возможностями. Иногда свойства курсоров разных типов (или даже одного ти па) полностью перекрывают возможности другого типа. Использование того или иного типа зависит от требований пользователя. По возможности следует при менять самые простые типы курсоров, т. к. они обрабатываются сервером быст рее и требуют меньше ресурсов.

Замечание Тип курсора указывается в момент его создания и после этого не может быть изменен.

Статические курсоры Как уже говорилось, для создания курсора необходимо указать запрос SELECT, на основе которого будет выполняться формирование результирующего набора данных, отображаемого в курсоре. При создании статического курсора (static cursor), или как его еще называют, курсора моментального снимка (snapshot cur sor) сервер сохраняет полученные данные в системной базе данных тетраь, ис пользуемой для временного хранения данных. При работе с курсором сервер обращается не к данным исходных таблиц, а к данным, сохраненным в базе данных Tempdb. Таким образом, в распоряжении пользователя имеется копия исходных данных (моментальный снимок).

Во время создания статического курсора сервер блокирует все данные, удовле творяющие критериям выборки. На момент формирования курсора ни одна транзакция не может изменить даже одну строку, входящую в курсор. Таким образом и получается моментальный снимок. Пользователи могут обращаться к произвольным строкам результирующего набора, на основе которого построен статический курсор.

В статических курсорах не поддерживается отображение изменений, сделанных в исходных данных. При использовании статических курсоров пользователь мо Глава 31. Использование курсоров жет работать с данными, которые уже не существуют в исходных таблицах или были изменены таким образом, что при повторном создании статического кур сора они бы уже не вошли в результирующий набор, т. к. не удовлетворяли бы условию горизонтальной фильтрации. Кроме того, в исходные таблицы могут быть добавлены новые строки, которые удовлетворяют условию горизонтальной фильтрации курсора. Помимо того, что изменения в исходных таблицах никак не отображаются в статическом курсоре, пользователь даже не получает никаких сообщений о выполненных изменениях.

Природа статического курсора не позволяет выполнять изменения отображае мых в нем данных. Это связано с тем, что нет никакой гарантии в существова нии строк, включенных в курсор, а также в том, что эти строки не претерпели изменений. Поэтому статические курсоры всегда открываются в режиме "только для чтения"(read only.) Несмотря на все описанные ограничения, статические курсоры весьма полезны при работе с данными, зависящими друг от друга. Например, если пользователь последовательно считывает номера паспортов из таблицы, и прочитал уже поло вину строк, а другой пользователь в это время удаляет строку из начала таблицы и вставляет ее в конец таблицы, то курсор прочитал бы одни и те же данные дважды. Это произойдет несмотря на то, что в каждый момент времени таблица содержит уникальные значения номеров паспорта и целостность данных не на рушена. При использовании статических курсоров пользователи могут быть уве рены, что описанная проблема не будет иметь места.

С Замечание ^ Так как данные курсора хранятся в системной базе данных Tempdb, то при активном использовании статических курсоров для хранения больших объемов данных мно гими пользователями следует позаботиться о том, чтобы в этой базе данных име лось достаточно свободного пространства. Возможно, лучшим решением будет раз решение автоматического увеличения (auto grow) размера этой базы данных.

Ключевые курсоры Курсоры, базирующиеся на наборе ключей (keyset driven cursors) или ключевые кур соры, представляют собой набор ссылок на строки, которые удовлетворяют ус ловиям горизонтальной фильтрации, указанным при создании курсора в разделе WHERE запроса SELECT. TO есть, в отличие от статического курсора, ключевые курсоры выбирают не всю строку, а лишь ключевые поля, позволяющие одно значно идентифицировать каждую строку результирующего набора в полном наборе. Полученный набор уникальных ссылок называется набором ключей (keyset). Таким образом, результирующий набор физически является ничем иным, как набором ключей. Пользователи могут обращаться к произвольным строкам результирующего набора.

При обращении к строке курсора сервер находит в наборе ключей нужный идентификатор и выбирает данные непосредственно из полного набора данных.

Такой подход позволяет отображать в курсоре любые изменения, сделанные в •J222 Часть V. Программирование исходном наборе данных. Однако набор ключей формируется только в момент открытия курсора и впоследствии не изменяется. Если в исходные таблицы бы ли добавлены новые строки, удовлетворяющие условиям горизонтальной фильт рации курсора, то такие строки не будут отображены в курсоре. Кроме того, пользователи могут удалить строки, входящие в результирующий набор курсора.

Такие строки показываются в курсоре как поврежденные (row missing). К ана логичному результату приведет и изменение значений в ключевых столбцах.

Кроме того, если при создании курсора использовались логические условия (раздел WHERE) ДЛЯ ограничения диапазона строк, входящих в результирующий набор, то если пользователи изменят строки таким образом, что они не будут удовлетворять условиям горизонтальной фильтрации, то эти строки все равно станут обрабатываться курсором. Это является следствием того, что набор клю чей формируется при открытии курсора и в дальнейшем не изменяется.

Замечание Набор ключей, как и данные статического курсора, хранится в системной базе дан ных Tempdb. При активном использовании ключевых курсоров и хранении больших объемов данных многими пользователями следует позаботиться о том, чтобы в этой базе данных имелось достаточно свободного пространства. Возможно, лучшим решением будет разрешение автоматического увеличения (auto grow) размера этой базы данных.

Последовательные курсоры Как уже было сказано ранее, по способу обращения к данным курсоры SQL Server 2000 можно классифицировать как последовательные (forward-only) и прокручиваемые (scroll).

Если предполагается произвести лишь одиночное последовательное сканирова ние всех строк курсора, то рекомендуется использовать специально для этого предназначенные последовательные курсоры (forward-only cursors). Этот тип кур соров поддерживает только обращение к следующей строке курсора и не позво ляет вернуться к любой из ранее выбранных строк. Кроме того, не разрешается перескакивать через одну или более строк. Таким образом, пользователи могут перебирать одну строку за другой, начиная с первой и заканчивая последней.

При создании последовательного курсора сервер не сохраняет значений ключей или копий строк. Сервер оперирует лишь запросом SELECT И номером текущей строки. При считывании следующей строки сервер выполняет поиск в исходных таблицах следующей после текущей строки, удовлетворяющей условиям гори зонтальной фильтрации. Такой подход позволяет отображать в курсоре все из менения, выполненные в результирующем наборе другими пользователями.

Кроме того, при работе с последовательными курсорами минимальны требова ния к объему оперативной памяти. Независимо от количества строк, доступных в курсоре, объем необходимой памяти для работы с последовательными курсо рами будет всегда примерно одинаков.

Глава 31. Использование курсоров Динамические курсоры Динамические курсоры (dynamic cursors) используют тот же принцип обращения к данным, что и последовательные, однако позволяют обращаться к любой про извольной строке результирующего набора. По способу доступа к данным и отображению в результирующем наборе изменений, сделанных другими пользо вателями, динамические курсоры напоминают ключевые курсоры. Однако меж ду этими типами имеется одно существенное различие.

Как было сказано ранее, ключевой курсор представляет собой набор идентифи каторов строк (набор ключей), входящих в результирующий набор курсора. Этот набор ключей не меняется за все время использования курсора. Следствием этого является то, что в курсоре содержатся даже те строки, которые после вне сения изменений пользователями не удовлетворяют условиям горизонтальной фильтрации.

Природа же динамических курсоров такова, что при каждом обращении к стро кам сервер заново обрабатывает ассоциированный с курсором запрос SELECT, обновляя тем самым результирующий набор. Если в исходный набор данных пользователями были внесены изменения, влияющие на список строк результи рующего набора, то такие изменения будут автоматически отражены в курсоре.

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

При работе с динамическими курсорами пользователи могут выполнять с по мощью них изменения данных в исходных таблицах. Для этого предназначены команды UPDATE, INSERT и DELETE. Однако каждая такая команда за один раз может работать только с одной строкой. Во время выполнения выборки или изменений данных с помощью курсора SQL Server 2000 блокирует соответст вующим образом нужную строку. При чтении данных с помощью курсора дру гие пользователи не смогут изменить данные, читаемые в курсоре. Если же в курсоре производится изменение данных, то другие пользователи не смогут да же прочитать эти данные. Такой подход полностью соответствует требованиям ACID, рассмотренным в главе 19.

( Замечание ^ Если установлен уровень изоляции, допускающий грязное чтение, то другие пользо ватели смогут читать изменяемые с помощью курсора данные, не дожидаясь фик сирования транзакции.

Работа с курсорами После того, как был выбран нужный тип курсора и разработан запрос SELECT, на основе которого будет формироваться результирующий набор данных, можно приступать к реализации и использованию этого курсора.

1224 Часть V. Программирование Весь процесс использования курсора можно разбить на пять этапов:

• Объявление курсора (declare cursor). Прежде чем выполнить любую операцию с курсором, его необходимо объявить. Объявление курсора можно сравнить с объявлением переменной или созданием таблицы. Объявление курсора под разумевает указание его имени и запроса SELECT, который будет использо ваться для формирования результирующего набора. При объявлении курсора также указывает его тип.

П Открытие курсора (open cursor). Сразу же после создания курсор не содержит никаких данных, так же как и после создания таблице в ней нет никаких данных. В процессе открытия курсора выполняется ассоциированный с кур сором запрос SELECT. Если создается статический курсор, то сервер будет копировать весь полученный результат в системную базу данных Tempdb. При работе с ключевым курсором в базу данных Tempdb будет помещен только набор ключей.

П Выборка данных из курсора и использование курсора для изменения исходных данных. После того, как курсор открыт, пользователь может приступать к выборке данных из этого курсора. В зависимости от типа созданного курсора также с помощью курсора возможно изменение исходных данных.

• Закрытие курсора (close cursor). После того, как из курсора были получены все интересующие данные и выполнены все необходимые изменения, его можно закрыть. Закрытие курсора подразумевает освобождение выделенного для него пространства в системной базе данных Tempdb. После закрытия кур сора пользователь не сможет обратиться ни к одной строке.

• Освобождение курсора (release cursor). Закрытый курсор может быть удален.

Удаление курсора подразумевает удаление из оперативной памяти описания курсора как объекта. Если курсор закрыт, но не удален, он может быть по вторно открыт для использования. При этом в него будет помещен новый набор строк. Можно удалить только закрытый курсор.

Рассмотрим более подробно каждый из этапов использования курсоров.

Объявление курсора Первое, что выполняется при работе с курсорами — это их объявление. Объявле ние курсора выполняется с помощью команды DECLARE CURSOR. SQL Server поддерживает два формата этой команды. Первый из них соответствует требова ниям стандарта ANSI SQL-92 и предоставляет базовый набор опций. Второй же формат является собственной разработкой Microsoft и предоставляет пользова телям более широкий набор возможностей.

Обсудим использование обоих вариантов команды. Сначала же рассмотрим формат ANSI SQL-92:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR {READ ONLY I UPDATE [OF column name [,...n]]}] Глава 31. Использование курсоров Рассмотри назначение и использование параметров команды:

О cursor_name Этот параметр определяет имя курсора, которое в дальнейшем будет приме няться для обращения к нему. Имя курсора должно удовлетворять общим требованиям именования объектов. В отличие от имени переменной, имя курсора должно начинаться не с символа @.

П INSENSITIVE Использование этого ключевого слова предписывает серверу создать статиче ский курсор. Результирующий набор курсора будет размещаться в базе дан ных Tempdb. Работа с таким курсором будет разрешена лишь в режиме "толь ко для чтения". Если ключевое слово INSENSITIVE не указывается, то созда ется динамический курсор.

• SCROLL При указании этой опции в создаваемом курсоре можно будет выполнять выборку данных в произвольном порядке. Если же опция SCROLL не указыва ется, то курсор можно будет просматривать только последовательно в на правлении от первой строки к последней. Однако не нужно путать курсор, не обеспечивающий выборку в произвольном порядке, с последовательным курсором.

П select_statement Этот параметр подразумевает указание кода запроса SELECT, который будет использоваться для формирования результирующего набора курсора. При написании кода запроса следует учитывать, что в запросе не допускается применение разделов INTO, GROUP BY И COMPUTE.

• READ ONLY При указании этого параметра работа с курсором будет возможна только в режиме чтения. Задание этого параметра не обязательно при создании стати ческого курсора, который по своей природе не допускает изменений. Однако не нужно путать курсор "только для чтения" со статическим курсором. Ис пользование параметра READ ONLY ДЛЯ динамических курсоров позволяет по высить скорость выполнения операций работы с курсором.

• UPDATE [OF c o l u m n _ _ n a m e [,...n]] Указание этого параметра разрешает проводить с помощью курсора измене ние исходных данных. Использование этого параметра не разрешается со вместно с использованием параметра INSENSITIVE. Аргумент coiumnname [,...п] определяет список столбцов, для которых будет поддерживаться возможность изменения данных. Если имена столбцов не указаны, то по умолчанию разрешается обновлять все столбцы курсора.

Как видно, стандарт ANSI SQL-92 предоставляет пользователям весьма скудные возможности использования курсоров. В частности, пользователи не могут при менять ключевые и последовательные курсоры.

1226 Часть V. Программирование Более широкие возможности по созданию курсоров предоставляет формат Transact-SQL команды DECLARE CURSOR:

DECLARE c u r s o r _ n a m e CURSOR [LOCAL ! GLOBAL] [FORWARD_ONLY I SCROLL] [STATIC | KEYSET I DYNAMIC I FAST_FORWARD] [READ_ONLY I SCROLL_LOCKS I O P T I M I S T I C ] [TYPE_WARNING] FOR s e l e c t _ s t a t e m e n t [FOR UPDATE [OF c o l u m n _ n a m e [,... n ] ] ] Рассмотрим назначение и использование параметров этого формата команды, однако не будем перечислять параметры, уже описанные ранее:

D LOCAL При указании этой опции создаваемый курсор будет локальным, т. е. суще ствовать только на время выполнения транзакции, хранимой процедуры или пакета команд. После завершения работы создавшей курсор транзакции, процедуры или пакета возникает неявное удаление курсора. Это происходит даже в том случае, если курсор не был закрыт. Данные локального курсора могут быть переданы из хранимой процедуры при использовании параметра OUTPUT. Курсор доступен только в пределах создавшей его структуры.



Pages:     | 1 |   ...   | 30 | 31 || 33 |
 





 
© 2013 www.libed.ru - «Бесплатная библиотека научно-практических конференций»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 1-2 рабочих дней удалим его.