Автор работы: Пользователь скрыл имя, 27 Ноября 2013 в 14:01, шпаргалка
Работа содержит ответы на вопросы для экзамена по "Информатике".
Удаление строк из результирующего множества курсора
С помощью конструкции where current of в операторе delete можно удалять строку, где находится курсор. Если строка удаляется из результирующего множества, то она также удаляется из соответствующей базовой таблицы. С помощью курсора за один раз можно удалить только одну строку.
Предложение delete... where current of имеет следующий синтаксис:
delete [from] [[база_данных.]владелец.]{
where current of название_курсора
Название таблицы или вьювера, указанные в этом предложении, должна совпадать с названием таблицы или вьювера, указанных в предложении from оператора выбора, определяющего курсор.
Например,
можно удалить строку, на которую
указывает курсор authors_crsr
delete from authors
where current of authors_crsr
Ключевое слово from здесь можно не указывать.
Замечание: Нельзя удалять строки с помощью курсора, который определен через соединение, даже если он объявлен как обновляемый.
После удаление строки с помощью курсора SQL Сервер располагает курсор перед строкой, которая следует за удаленной строкой в результирующем множестве. Нужно по-прежнему использовать оператор fetch, чтобы получить доступ к следующей строке. Если была удалена последняя строка, то SQL Сервер располагает курсор за последней строкой результирующего множества.
Например, после удаления строки в предыдущем примере (которая соответствует Мишелю ДеФрансу) можно просчитать следующие три строки результирующего множества следующим образом:
fetch authors_crsr
au_id au_lname
--------------- -------------
807-91-6654 Panteley
899-46-2035 Ringer
998-72-3567 Ringer
Конечно, можно удалить строку базовой таблицы и не обращаясь к курсору. Результирующее множество курсора будет изменяться в соответствии с изменением базовой таблицы.
Обновление
строк результирующего
Используя конструкцию where current of в операторе update, можно обновить содержимое строки, на которую указывает курсор. Каждое обновление результирующего множества курсора приводит к обновлению содержимого базовой таблицы, из которой получено множество курсора.
Оператор update... where current of имеет следующий синтаксис:
update [[база_данных.]владелец.]{
set [[[база_данных.]владелец.]{
название_столбца1 = { выражение1 | NULL | (оператор_выбора)}
[, название_столбца2 = { выраж
where current of название_курсора
В предложении set указываются названия столбцов и их новые (обновляемые) значения. Если здесь указывается несколько столбцов, то они должны разделяться запятыми.
Название таблицы или вьювера, указанное в этом операторе, должно совпадать с названием таблицы или вьювера, указанным в предложении from оператора выбора, определяющего курсор. Если в предложенииfrom указано несколько таблиц или вьюверов (в случае соединения), то можно указать только ту таблицу (вьювер), которая действительно обновляется.
Например, можно обновить строку, на которую указывает курсор pubs_crsr, следующим образом:
update publishers
set city = "Pasadena",
state = "CA"
where current of pubs_crsr
После обновления позиция курсора остается неизменной. Можно продолжать обновление строки, на которую указывает курсор, до тех пор, пока другой SQL оператор не изменит позицию курсора.
SQL позволяет обновлять столбцы базовой таблицы, которые не были указаны в списке столбцов оператора выбора, определяющего курсор. Однако, если в предложении for update указывается список столбцов, то обновлять можно содержимое только этих столбцов.
ЗАКРЫТИЕ И УДАЛЕНИЕ КУРСОРА
Когда работа с результирующем множеством закончена, курсор можно закрыть. Команда закрытия имеет следующий вид:
close название_курсора
Закрытие курсора не изменяет его определения. После этого можно вновь открыть курсор, тогда SQLСервер создаст новое результирующее множество с помощью того же запроса. Например:
close authors_crsr
open authors_crsr
После этого можно считывать данные через курсор authors_crsr, начиная с начала результирующего множества. Все условия, связанные с этим курсором (такие как число строк считываемых за один раз) остаются в силе.
Например:
fetch authors_crsr
au_id au_lname
----------- -------------
341-22-1782 Smith
527-72-3246 Greene
648-92-1872 Blotchet-Halls
Если курсор больше не нужен, то его следует удалить (deallocate). Синтаксис оператора deallocate имеет следующий вид:
deallocate cursor название_курсора
Удаление
курсора освобождает все
ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ КУРСОРА
Последующие примеры использования курсоров будут базироваться на следующем запросе:
select author = au_fname + " " + au_lname, au_id
from authors
order by au_lname
Результат этого запроса имеет следующий вид:
author
------------------------------
Abraham Bennet 409-56-
Reginald Blotchet-Halls 648-92-1872
Cheryl Carson 238-95-
Michel DeFrance 722-51-
Ann Dull 427
Marjorie Green 213-46-
Morningstar Greene 527-72-3246
Burt Gringlesby 472-
Sheryl Hunter 846-92-
Livia Karsen 756-
Chastity Locksley 486-29-1786
Stearns MacFeather 724-80-9391
Heather McBadden 893-72-1158
Michael O'Leary 267-41-
Sylvia Panteley 807-91-
Anne Ringer 899-
Albert Ringer 998-72-
Meander Smith 341-22-1782
Dick Straight 274-
Dirk Stringer 724-
Johnson White 172-32-
Akiko Yokomoto 672-71-
Innes del Castillo 712-45-
В следующих пунктах показано как использовать курсор в этом запросе:
1. Сначала необходимо объявить курсор. В операторе declare курсор определяется с помощью вышеприведенного оператора выбора:
declare newauthors_crsr cursor for
select author = au_fname + " " + au_lname, au_id
from authors
order by au_lname
2. После объявления курсор можно открыть:
open newauthors_crsr
3. Теперь можно считывать строки, используя курсор:
fetch newauthors_crsr
author au
------------------------- -
Abraham Bennet 409-56-7008
4. Можно считывать несколько строк за один раз, установив число с помощью команды set:
set cursor rows 5 for newauthors_crsr
fetch newauthors_crsr
author
-------------------------
Reginald Blotchet-Halls 648-92-1872
Cheryl Carson 238-95-
Michel DeFrance 722-51-
Ann Dull 427
Marjorie Green 213-46-
Каждое последующее считывание будет сдвигать курсор еще на пять строк:
fetch newauthors_crsr
author au
------------------------- -
Morningstar Greene 527-72-3246
Burt Gringlesby 472-27-
Sheryl Hunter 846-92-7186
Livia Karsen 756-30-
Chastity Locksley 486-29-1786
5. После окончания работы с курсором его можно закрыть:
close newauthors_crsr
Закрытие курсора приводит к закрытию (releases) результирующего множества, но курсор остается определенным. Если его снова открыть командой open, то SQL Сервер снова выполняет запрос для формирования результирующего множества и устанавливает курсор перед первой строкой этого множества. По прежнему каждый оператор fetch будет считывать по пять строк.
Для полного удаления курсора следует выполнить команду deallocate:
deallocate cursor newauthors_crsr
Нельзя использовать название курсора до тех пор, пока курсор не удален командой deallocate:
КУРСОРЫ В СОХРАНЕННЫХ ПРОЦЕДУРАХ
Курсоры особенно полезны в сохраненных процедурах. С их помощью можно выполнить задание, требующее несколько запросов, всего одним запросом. Однако, все операции с курсором должны быть выполнены в одной процедуре. В сохраненной процедуре нельзя открывать, считывать или закрывать курсор, который не был объявлен в этой процедуре. Курсор не определен за пределами области действия (scope) сохраненной процедуры.
Например, следующая сохраненная процедура au_sales проверяет таблицу продаж, чтобы определить, продается ли у данного автора достаточно хорошо хотя бы одна книга:
create procedure au_sales (@author_id id)
as
/* declare local variables used for fetch */
declare @title_id tid
declare @title varchar(80)
declare @ytd_sales int
declare @msg varchar(120)
/* declare the cursor to get each book written by given author */
declare author_sales cursor for
select ta.title_id, t.title, t.total_sales
from titleauthor ta, titles t
where ta.title_id = t.title_id
and ta.au_id = @author_id
open author_sales
fetch author_sales
into @title_id, @title, @ytd_sales
if (@@sqlstatus = 2)
begin
print "We do not sell books by this author."
close author_sales
return
end
/* if cursor result set is not empty, then process each row of information */
while (@@sqlstatus = 0)
begin
if (@ytd_sales = NULL)
begin
select @msg = @title +
" had no sales this year."
print @msg
end
else if (@ytd_sales < 500)
begin
select @msg = @title +
" had poor sales this year."
print @msg
end
else if (@ytd_sales < 1000)
begin
select @msg = @title +
" had mediocre sales this year."
print @msg
end
else
begin
select @msg = @title +
" had good sales this year."
print @msg
end
fetch author_sales into @title_id, @title,
@ytd_sales
end
/* if error occurred, call a designated handler */
if (@@sqlstatus = 1) exec error_handle
close author_sales
deallocate cursor author_sales
return
Дополнительную информацию о сохраненных процедурах можно получить в главе 14 "Использование сохраненных процедур".
10) Индексы.
Простейшим примером применения индексов в реальной жизни является оглавление книги. Вместо перебора всех страниц книги, читатель может открыть конкретную главу книги, получив номер ее начальной страницы из оглавления. Той же самой цели служат и индексы в таблице. Они позволяют получить данные из определенной записи без перебора всех записей в таблице.
[В начало]
2. Кучи и Индексы
Физически данные хранятся на 8Кб страницах. Сразу после создания, пока таблица не имеет индексов, таблица выглядит как куча (heap) данных. Записи не имеют определенного порядка хранения. Рис 1. иллюстрирует таблицу Customers из базы данных Northwind, хранящихся в виде кучи.
Рис. 1 Куча (A heap)
Когда вы хотите получить доступ к данным, SQL Server будет производить сканирование таблицы (table scan). SQL Server сканирует всю таблицу что бы найти искомые записи. Например мы хотим найти запись, удовлетворяющую условию:
SELECT * FROM Customers
WHERE CustomerID = ‘ROMEY’
SQL Server прочитает все записи начиная с первой и заканчивая последней и выберет те, которые будут удовлетворять указанному условию. SQL Server не знает что в таблице существует только одна запись, удовлетворяющая условию, пока в таблице не существует unique constraint, unique index или primary key. Во всех трёх перечисленных случаях создается индекс для поддержания ограничения. Приведенный пример иллюстрирует две базовые функции индексов:
Несмотря на достоинства, индексы так же имеют и ряд недостатков. Первый из них – индексы занимают дополнительное место на диске и в оперативной памяти. Каждый раз когда вы создаете индекс, вы сохраняете ключи в порядке убывания или возрастания, которые могут иметь многоуровневую структуру. И чем больше/длиннее ключ, тем больше размер индекса. Второй недостаток – замедляются операции вставки, обновления и удаления записей. Однако алгоритмы построения индексов разработаны таким образом что бы иметь как можно меньший негативный эффект для указанных операций и даже позволяет выполнять их быстрее, как будет показано позднее.
В SQL Server индексы хранятся в виде B-деревьев (B-tree). “B” означает сбалансированное (не путать с бинарным). Рис 2 показывает индекс, созданный для поля CustomerID для таблицы Customers.
Рис. 2 B-Tree индекс
Теперь если выполнить предыдущий запрос по поиску записи CustomerID = ‘ROMEY’, будут прочитаны только страницы 30, 22 и 10 в указанном порядке.
Как указывалось ранее индексы в SQL Server представляют собой сбалансированные деревья. Это означает, что длины веток для всех ответвлений индекса, одинаковы. Если посмотреть на Рис. 2 сверху вниз, вам придется просканировать только три страницы что бы найти запись удовлетворяющую условию. Каждая ветка сбалансирована и внутренний механизм построения индексов держит это дерево сбалансированным при любых изменениях в таблице.
Обратите внимание
что на Рис. 2 данные не отсортированы.
Это значит что при создании индекса должен
быть создан уровень листьев (leaf level), содержащий
указатели на данные отсортированные
по указанному ключу (это уровень, обозначенный
страницами с 20 по 23). На Рис.2 указатель
представляет собой row ID, который имеет
следующий формат: НомерФайла:НомерСтраницы: