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

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

Пример 1

CREATE VIEW Order View

AS

SELECT o.OrderNumber, p.Name AS ProductName,

m.Name AS ManufacturerName

FROM Order As o

INNER JOIN Product AS p

ON p.Product_Id = o.Product_Id

LEPT JOIN Manufacturer AS m

ON p.Manufacturer_Id = m.Manufacturer_Id

04.11.2015

Модифицируемые представления

В большинстве случаев представления используется только для получения данных. Однако могут быть созданы модифицируемые представления к которым применимы INSERT, UPDATE, DELETE. Эти команды вызывают изменения в базовой таблице. Модифицируемое требование должно основывается на одной базовой таблице и не содержать операции соединения, объединения, группировки, агрегирующих функций, пользовательских или встроенных функции, констант, под запросов и др. операции преобразований данных.

Модифицируемое представления может быть создано с этой опцией - WITH CHECK OPTION. В этом случае к нему могут применятся данные только те команды модификации данных, после выполнения которых изменённые данные продолжают отображается данным представлениям. То есть не нарушается определенные представления наборов данных. Модифицируемые представления check OPTION являются гибким представлениям данных пользователя.

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

Для решения каких задач применяются представления:

1) Логическая независимость данных – представления и хранимые процедуры могут служить логическим интерфейсам БД, скрывающим от клиентских приложений структуру пользовательских таблиц, при применений такой архитектуры, рестрокрутизация БД потребует только переопределения представлений или вызывает изменения в клиентском коде.

2) Упрощения запросов связанных таблиц – при выборке данных одно из более получаемых операции является соединения таблиц. Часто применяемые соединения целесообразно вынести в представления чтобы предоставить клиентским приложениям доступ к данным.

3) Гибкая настройка прав пользователя – настройка прав доступа пользователям представления и хранимым процедурам в сочетания на запрет к физическим таблицам и обеспечивает высокую степень защищённости и безопасности данных.

4) Дополнительный контроль целостности - создания модифицируемых представлений с опцией WITH CHECK OPTION служит дополнительным представлениям к данным.

5) Гибкая настройка данных – при помощи представления и хранимых процедур может быть создан индивидуальный образ БД для разных категорий пользователей.

05.11.2015

Хранимые процедуры

Хранимая процедура представляет собой набор инструкции на языке транзакции SQL, который хранится в БД и может быть вызван как из другой хранимой процедуры так из клиентского приложения.

ЖЦ хранимой процедуры

CREATE PROCEDURE можно записать и более коротко CREATE PROC

< Список параметров>

AS

BEGIN

…….

INSERT…..

……..

DELETE….

SELECT …….

END

Что бы изменить хранимую процедуру командой – ALTER PROCEDURE. Cинтакс команде alter аналогичен команде create.

DROP PROCEDURE < имя>

Передача параметров хранимой процедуры

@ - НАЧИНАЕТСЯ ЗАПИСЬ имя ПАРАМЕТРОВ, направления передачи, может быть указана значения по-умолчанию.

CREATE PROCEDURE

@Param1 int,

@Param2 varchar(20) = {‘Вася’}

@Param3 float OUTPUT

AS

BEGIN

……

END

EXECUTE < имя проц> сокрашенно EXEC

При вызове хранимой процедуры для получения значения выходных параметров задаются переменные.

DECLARE @var1 FLOAT EXECUTE SomeProc 23, ‘Петя’, @var1 OUTPUT

Вызвать команду select чтобы посмотреть значения

SELECT @var1

Это пример вызова позиционный

Сейчас будет именной вызов

EXECUTE SomeProc @Param3 = @var1 OUTPUT, @Param1=48

@Param2 - будет введен по-умолчанию

EXECUTE SomeProc 48, ‘Вася’, @var1 OUTPUT

Управляющие конструкции языка транзакции SQL

Переменные

Переменные объявляются с помощью инструкции:

DECLARE @var1 int, @var2 varchar(40).

SET @var1 = 67.

SELECT @var2 = Name

FROM Perdon

Where Person_Id = @var1

Такая флома select при приминима если запросов возвращяет одну строку

EXEC Proc1 @var1 OUTPUT

Условное ветвление

If < действия>

BEGIN

…….

END

ELSE

BEGIN

…..

END

Цикл

Для транзакции существует единственный цикл – это while

WHILE < действия>

Begin

…..

END

И есть еще CONTINUE, BREAK, RETURN

В отличие от алгоритмических языков в транзакт SQL циклы выполняются редко, так как основные dml операторы (SELECT, INSERT, …..) и так ориентированы на работу с множеством.

Return-безусловный выход из процедуры. Хранимой процедуру RETURN может выполнятся для возврата целочисленного значения.

RETURN 0 – обычно интерпретируется как признак успешного, а возврат другого целочисленного значения считается ошибкой.

Преимущества применения хранимых процедур:

· Упрощения логики обработки данных - многоэтапные задания проще реализовать виде хранимой процедуры, чем описывать как отдельные реализации

· Инкапсуляция логики обработки данных (может не всегда нужен) – последовательность SQL операторов реализованных виде хранимой процедуры для клиентского кода выглядит как единственное атомарная инструкция

· Повышения гибкости – изменения кода хранимой процедуры не имеет …

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

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

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

11.11.2015

Пользовательские функции

Определенная пользователем функция представляет собой инструкции языка транзакт SQL, вычисляющего некоторое значение и возвращающего его как результат выполнения функции. Результатом функции может быть скалярное значение или таблица. Функция делится на 3 вида:

· Скалярная (Scalar)- возвращает нескорую функцию

· (Inline) возвращает набор данных виде таблицы как результат единственно правильно управления select

· (Multi-statement) – возвращает набор данных виде таблицы, как результат выполнения множества sql операторов

Скалярные функции

Пример

CREATE FUNCTION < имя функции >

(< список параметров >)

RETURNS < тип результата >

AS

BEGIN

…….

RETURN (< список выражений >)

END

При объявлении функции returns указывается скалярный тип данных, к которому принадлежит результат функции. В теле функции должна присутствовать команда return, которая завершает выполнения функции и возвращает ее результат.

Пример

CREATE FUNCTION user1.Sales(@data DATE)

RETURNS INT

AS

BEGIN

DECLARE @c INT

SELECT @c = sum (amount)

FROM Orders

WHERE Order_date = @date

RETURN (@c)

END

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

Пример вызова функции

DECLARE @var INT

SET @var = user1.Sales(‘2015—11-11’)

INLINE функции

CREATE FUNCTION < имя функции >

(< список параметров >)

RETURNS TABLE

AS

RETURN (SELECT ….)

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

Пример

CCREATE FUNCTION user1.Orders_from_Person

RETURNS TABLE

AS

RETURN(

SELECT Order_date, Product_Name

FROM Orders

WEHERE Person_Id = @Person_Id

)

SELECT * FROM user1.Orders_from_Person(28)

InLine функции вызиваются как правило..

Inline функция может вызватся как присвоенная функция.

Multi-statement

CREATE FUNCTION < имя функции>

(< список параметров>)

RETURNS < вых парметров> TABLE

< определение выходной таблицы>

AS

BEGIN

….

RETURN

END

Multi-statement функции также возвращает значение типа TABLE, однако структура результирующей таблицы не определяется автоматически, а должна быть задана в разделе returns при определении функции. Тело функции состоит из операторов формирующию выходную таблицу и завершается.

Пример

CREATE FUNCTION user1.Get_Report(@user_Id INT)

RETURNS @report TABLE(

NAME VARCHAR (200),

Order_Date DATE)

AS

BEGIN

….

INSERT INTO @report …..

….

RETURN

END

SELECT * FROM user1.Get_Report(35)

12.11.2015

ДОПИСАТЬ

1) DML – триггеры. INSERT, DELETE, UPDATE

2) DDL – триггеры - активизируется при изменении данных (CREATE, ALTER, DROP, GRANT, DENY, REVOKE, ….)

3) Login – триггеры входа. Активизируется при входе в БД.

Наиболее часто используемых первый вид DML – триггеров. И они тоже делятся на 3 вида:

· AFTER – выполняется после инициирующей команды.

· INSEAD – вместо инициирующей команды

· OF

При этом INSEAD and OF повторно вызывает инициирующую команду. Это не приводит к повторному возбуждению триггера. DML – триггер всегда создается для конкретной таблицы. При этом он может ассоциирован как с одной из DML команд, так и с их комбинациями. Для каждой DML команды может быть определенно несколько триггеров.

Создания DML – триггера

Пример

{CREATE|ALTER } TRIGGER < имя триггера>

ON < таблица>

{ FOR |AFTER|INSTEAD OF}

{ [DELETE], [, ] [INSERT] [, ] [UPDATE] }

AS

BEGIN

…….

END

Создания триггера для операции удаления и обновления

CREATE TRIGGER Orders_Del_Upd

ON Orders

FOR DELETE, UPDATE

AS

BEGIN

…..

END

Особенности триггеров

По скольку триггер вызывается не явно ему не могут быть переданы параметры, однако из тела триггера доступны 2 служебные таблицы Insert and Delete, которые автоматически заполняются данными. Затронутыми из соответствующей операции.

Существует функция @@ROW COUNTВ. В IMS отсутствует строковые триггеры вызываемые для каждой строки.

Выполнения триггеров

ПРИ ВЫПОЛНЕНИИ DML КОМАНД ПРОИСХОДЯТ СЛЕДУЮЩИЕ ДЕЙСТВИЯ:

1) Проверка декларативных ограничений целостности

2) Выполнения DML команд или триггера INSEAD OF

3) Выполнения триггером AFTER, если с одной и той же командой ассоциированы несколько триггеров AFTER, порядок их распределён.

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

Применения триггеров

Основная задача триггеров контроль целостности данных.

Как и деклароватийные решения триггеры применяются для контроль целостности данных.

Триггеры менее эффективны чем деклароватийные ограничения, однако позволяют реализовать более сложные правила. Триггеры применяются для протоколирования и аудита изменения БД. При примении триггера возможны некоторые отрицательные последствия:

· Снижения производительности

· Появления скрытого функционала

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

18.11.2015

Транзакции MICROSOFT SQL SERVER

Под транзакцией понимается не делимое действие над данными, которая рассматривается как одна операция. Результаты которой полностью фиксируются или полностью отменяются.

Управления транзакциями выполняет 2 важные роли:

1. Сохранения целостности и не противоречивости при много этапной обработки

2. Предотвращения искажения данных при одновременной работе множества пользователей

ACID – свойство транзакции

Atomictity - транзакция может быть либо полностью выполнена, либо полностью отменена.

Согласованность(Consistency) – на момент начала транзакции БД находится не в противоречивом состоянии и после фиксации, она должна быть в не противоречивом состоянии.

Isolation (изолированный) – результаты транзакции не зависят от остальных транзакции. пользователь не видит изменений внесенных чужими транзакциями до тех пор пока не будут зафиксированы.

Durability (Устойчивость) - после фиксации сделанный транзакции не обратимы, в том числе и сбоя системы. Внесенные транзакціями изменения, протоколируются в журнале транзакции и могут быть восстановленные в случае сбоя.

Управления транзакциями

Управления транзакцией осуществляется в одном из трех режимов:

· Автоматическое

· Не явное (под разучивающиеся управления)

· Явное

Режим автоматического управлениями транзакциями – каждая DML операция рассматривается как отдельная. В случае сбоя все изменения выполнение данной операцией теряются, включая изменения внесённые транзакциями и триггерами.

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

Явное управления – начало и конец явно указываются пользователем.

Явное управления транзакциями

BEGIN TRANSACTION [< имя> ] – явная транзакцыя начинается так. При выполнении которой в журнале транзакции фиксируется данные.

Фиксацыя транзакции выполняется командой COMMIT TRANSACTION [< имя> ] – все внесенные изменения фиксируются в БД и данная транзакция помечается как выполнена.

Откат выполняется так ROLL BACK [TRANSACTION [< имя транзакции> |< имя точки сохранения> ]]

При выполнении ROLL BACK выполнябтся, снимаются свои аналогии. Данные возвращаются впервоначальную обстановку.

SAVE TRANSAZcTION < имя точки сохранения> - возврат к точки сохранения, позволяет повторно выполнить вызвавщиеся сбой операции и все таки зафиксировать.

Пример

BEGIN TRY

BEGIN TRANSACTION

…..

DELETE….

INSERT….

……

CoMMIT

END TRY

BEGIN CATCH

…

ROLLBACK

END CATCH

Блокировки и уровни изоляции транзакции

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

1) Потеря изменений м- при одновременном изменений данных 2-мя пользователями первое будет поделено

2) Грязное чтение – на отдельных этапах целостность может нарушатся. Чтение данных в таком состоянии приводит к получению искаженной информации.

3) Не повторяемое чтение – возникает если в процессе работы транзакции, полученные ею данных были изменены и зафиксированы другой транзакцией.

4) Фантомное чтение –возникает если в процессе работы транзакции

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

19.11.2015

Уровни изоляции транзакции

Уровни изоляции транзакции определяет степень независимости транзакции друг от друга, зависящего от характера накладываемого блокировки.

SET TRANSACTION ISOLATION LEVEL < уровень>

Определенно 4 уровня изоляции:

1) READ VNCOMMITED – не завершённое чтение. Никакие блокировки не накладываются.

2) READ COMMITED – завершённое чтение. Гарантируется отсутствие грязного чтения, однако возможно не повторяемое и фантомное чтение.

3) REPETABLE READ - гарантируется отсутствие грязного и не повторимого чтения, однако возможно создания фантомного

4) SERIALIZABLE – это максимально жесткий уровень блокировки. Найвищий уровень изоляции транзакции, чтения данных с конкурирующими транзакциями запрещено до завершения текущей транзакции, то есть конкурирующие транзакции будут выполнятся последовательно

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

1) По возможности во всех транзакциях обращаться ко объектам БД в постоянном порядке

2) Минимизировать длительность транзакции

3) Избегать взаимодействия с пользователем в теле транзакции

4) Без необходимости не принимать высокие уровни транзакции

Технология доступов данных на платформе ADO.NET

Технология ADO.NET

ADO.NET представляет собой API для операции доступов к данным. ADO.NET обеспечивает доступ к данным без постоянного соединения с БД.

Классы и объяты ADO.NET делятся на соединенные и не соединенные уровни

Объекты соединенного уровня

Выполняют операцию с данными не посредственно в БД

Data Provider – обеспечивает взаимодействия с конкретной СУБД.

Важную роль играет Data Adapter, обеспечивающий выполнения GRUD операции с конкретной таблицей БД и служащим мостом между физической таблицей БД представляющей ее объектом Data TABLE.

Объект класса Command, обеспечивает выполнения SQL запросов и хранимых процедур. Объекты класса Connection управляют соединением с БД.

Отсоединённый уровень

Обеспечивает работу с данными без поддержания активного содержания. Основную роль играет Data Set (набор данных) представляемую собой упрощенную БД, позволяющие приложению осуществлять основные операции и поддерживающею модель программирования не зависимо от источника данных. Модель данных DATA SET представляет собой коллекцию таблиц и отношения между ними. Таблица данных рассматривается как коллекция строк и столбцов, а также наложенных на таблицу ограничении.

25.11.2015