Представления Курсоры Лекция 6 Бутенко И В

Представления Курсоры Лекция № 6 Бутенко И. В. 2017 год

Представления

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

Достоинства представлений • Обеспечение безопасности и конфиденциальности запросов • Высокая производительность • Механизмы фильтрации – вертикальный фильтр – горизонтальный фильтр

Синтаксис CREATE VIEW [ schema_name. ] view_name [ (column [ , . . . n ] ) ] [ WITH <view_attribute> [ , . . . n ] ] AS select_statement [ ; ] [ WITH CHECK OPTION ]

Обновляемые представления Можно изменять данные базовой таблицы через представление до тех пор, пока выполняются следующие условия: • Любые изменения, включая использование инструкций UPDATE, INSERT и DELETE, должны касаться столбцов только из одной базовой таблицы. • Изменяемые в представлении столбцы должны непосредственно ссылаться на данные столбцов базовой таблицы. • Предложения GROUP BY, HAVING и DISTINCT не влияют на изменяемые столбцы.

Секционированные представления Секционированное представление — это представление, определенное посредством объединения всех (UNION ALL) таблиц-элементов, структурированных одинаковым образом, но хранимых отдельно в форме разных таблиц либо в одном экземпляре SQL Server, либо в группе автономных экземпляров SQL Server, которые называются федеративными серверами баз данных. Как правило, представление считают секционированным, если оно соответствует следующему формату: SELECT <select_list 1>FROM T 1 UNION ALL SELECT <select_list 2>FROM T 2 UNION ALL. . . SELECT <select_listn>FROM Tn

Пример 1 if object_id ('marks_view 1', 'view') is not null drop view marks_view 1 ; go create view marks_view 1 as select m. mark, s. lastname from marks m join students s on m. stud_id = s. id go select * from marks_view 1

Пример 2 if object_id ('marks_view 2', 'view') is not null drop view marks_view 2 ; go create view marks_view 2 as select m. mark, m. ddate, m. subj_id, m. stud_id, s. lastname from marks m join students s on m. stud_id = s. id where m. ddate > '10/10/2015' with check option go insert into marks_view 2 (stud_id, subj_id, ddate, mark) values (1, 3, '09/09/2015', 3)

Пример 3 CREATE TABLE marks 1 ( stud_id int FOREIGN KEY REFERENCES students (id), subj_id int, ddatetime default getdate() check (ddate < '01/10/2010'), mark tinyint CHECK (mark > 1 and mark <= 5) CONSTRAINT marks 1_1 PRIMARY KEY (ddate, stud_id, subj_id) ) go CREATE TABLE marks 2 ( stud_id int FOREIGN KEY REFERENCES students (id), subj_id int, ddatetime default getdate() check (ddate >= '01/10/2010'), mark tinyint CHECK (mark > 1 and mark <= 5) CONSTRAINT marks 2_1 PRIMARY KEY (ddate, stud_id, subj_id) ) go create view all_marks_view as select * from marks 1 union all select * from marks 2 insert into all_marks_view (stud_id, subj_id, ddate, mark) select stud_id, subj_id, ddate, mark from marks select * from marks 1 select * from marks 2

Курсоры

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

Введение Курсоры позволяют усовершенствовать обработку результатов: • позиционируясь на отдельные строки результирующего набора; • получая одну или несколько строк от текущей позиции в результирующем наборе; • поддерживая изменение данных в строках в текущей позиции результирующего набора; • поддерживая разные уровни видимости изменений, сделанных другими пользователями для данных, представленных в результирующем наборе; • предоставляя инструкциям Transact-SQL в сценариях, хранимых процедурах и триггерах доступ к данным результирующего набора.

Типы курсоров • Transact-SQL. Язык Transact-SQL поддерживает синтаксис использования курсоров по стандарту SQL-92. • API-функции курсоров базы данных. SQL Server поддерживает функциональность курсоров для следующих API-интерфейсов баз данных: • ADO (Microsoft Active. X Data Object) • OLE DB • открытый интерфейс доступа к базам данных (ODBC).

Общий вид курсора • • • Связать курсор с результирующим набором инструкции Transact-SQL и задать его характеристики (например, возможность обновления строк). Выполнить инструкцию Transact-SQL для заполнения курсора. Получить в курсор необходимые строки. Операция получения в курсор одной и более строк называется выборкой. Выполнение серии выборок для получения строк в прямом или обратном направлении называется прокруткой. При необходимости выполнить операции изменения (обновления или удаления) строки в текущей позиции курсора. Закрыть курсор.

Типы курсора • Статические (static cursor, snapshot cursor) • Ключевые курсоры (keyset driven cursor) • Однонаправленные курсоры • Динамические курсоры

Выборка данных FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ] FROM ] { { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ , . . . n ] ]

$Завершение работы • CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }$

Завершение работы • CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name } • DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }

Функции работы с Курсором • @@CURSOR_ROWS • @@FETCH_STATUS: – 0 - Инструкция FETCH была выполнена успешно. – -1 - Выполнение инструкции FETCH завершилось неудачно или строка оказалась вне пределов результирующего набора. – -2 - Выбранная строка отсутствует.

Пример 1 declare @mark int declare @stud_id int declare @ddatetime declare @subj_id int declare @s. Stud. Name varchar(50) declare @s. Subj. Name varchar(50) declare c_curs 1 cursor static for select mark, stud_id, ddate, subj_id from marks open c_curs 1 while 1=1 begin fetch next from c_curs 1 into @mark, @stud_id, @ddate, @subj_id if (@@fetch_status <> -1) and (@@fetch_status <> -2) begin select @s. Stud. Name = lastname + ' ' + name from students where id = @stud_id select @s. Subj. Name = name from subjects where id = @subj_id print 'Студент ' + @s. Stud. Name + ' по предмету ' + @s. Subj. Name + ' получил ' + convert(varchar, @mark) end else break end close c_curs 1 deallocate c_curs 1

Пример 2 declare @id int declare @name varchar(50) declare @hours int declare c_curs 1 cursor dynamic for select id, name, hours from subjects open c_curs 1 while 1=1 begin fetch next from c_curs 1 into @id, @name, @hours if (@@fetch_status <> -1) and (@@fetch_status <> -2) begin print '1 по предмету ' + @name + ': ' + convert(varchar, @hours) fetch prior from c_curs 1 into @id, @name, @hours print 'Статус: ' + convert(varchar, @@fetch_status) print '2 по предмету ' + @name + ': ' + convert(varchar, @hours) fetch next from c_curs 1 into @id, @name, @hours update subjects set hours = hours + 1 where current of c_curs 1 end else break end close c_curs 1 deallocate c_curs 1