SQL
Хранимые процедуры
это сохраненная коллекция инструкций языка Transact-SQL, которая может принимать и возвращать предоставленные пользователем параметры.
-Делают возможным модульное программирование. Можно, однажды создав хранимую процедуру, сохранить ее в базе данных, а затем любое число раз вызывать из своей программы.
-Позволяют ускорить выполнение. Хранимые процедуры снижают стоимость компиляции кода Transact-SQL, кэшируя и повторно используя планы выполнения. Это означает, что для хранимых процедур нет необходимости выполнять повторный синтаксический анализ и оптимизацию при каждом вызове, что значительно ускоряет их выполнение.
-Позволяют уменьшить сетевой трафик. Вместо передачи на сервер тестов больших запросов Transact-SQL, запросы можно оформить в виде хранимых процедур. При этом для их выполнения будут передаваться только имена процедур и значения параметров.
Создание
CREATE { PROC | PROCEDURE } procedure_name
[
{ @parameter data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS ]]
AS
[ BEGIN ] <statements> [ END ]
Вызов
EXEC procedure_name value1, value2 ...
EXEC procedure_name @arg1=value1, @arg2= value2 ...
Система безопасности
Аутентификация пользователя
процесс, при котором пользователь в зависимости от указанного имени пользователя и пароля допускается или нет к установлению соединения с MS SQL Server.
Роли сервера:
определенные права на выполнение операторов и работу с объектами базы данных. Роли объединяют нескольких пользователей в группу, наделенную определенными правами, причем одному пользователю может быть назначено несколько ролей.
• sysadmin – может выполнять любые действия на MS SQL Server. По умолчанию сюда входит учетная запись sa и все члены группы администраторов Windows;
• setupadmin – управляет связанными серверами (linked servers) и процедурами, которые выполняются вместе с запуском сервера;
• securityadmin – может создавать и управлять логинами, читать журнал ошибок и создавать БД;
• processadmin – обладает правами управления процессами внутри MS SQL Server, например, член этой роли может завершать задачи, которые выполняются слишком долго;
• dbcreator – разрешено создание и изменение баз данных;
• diskadmin – управляет файлами баз данных: назначает файлы в группы, присоединяет/отсоединяет базы данных и т.д.;
• bulkadmin – позволяет выполнять команду BULK INSERT для вставки сразу большого количества записей в таблицу;
Хранимые процедуры:
• sp_helpsrvrole – возвращает список ролей сервера и описание каждой роли;
• sp_helpsrvrolemember [‘имя роли’] – возвращает список ролей и учетных записей, которым присвоены эти роли;
• sp_srvrolepermission [‘имя роли’] – возвращает список разрешений, присвоенных этим ролям.
Роли БД:
• db_owner – включает в себя права все других ролей базы данных. Пользователь получает права владельца базы.
• db_accessadmin – похожа на серверную роль securityadmin, за исключением того, что ограничена одной базой данных. Она не позволяет создавать новые логины MS SQL Server, но разрешает добавлять новых пользователей в базу данных.
• db_datareader – разрешает выполнение оператора SELECT для всех таблиц базы данных.
• db_datawriter – разрешает выполнять INSERT, UPDATE и DELETE для всех таблиц базы данных.
• db_ddladmin – позволяет добавлять, удалять и изменять объекты в базе данных.
• db_securityadmin – еще одна роль похожая на серверную роль securityadmin. В отличие от db_accessadmin, она не разрешает создавать новых пользователей в базе, но позволяет управлять ролями и членством в ролях, а также правами на доступ к объектам базы данных.
• db_backupoperator – позволяет создавать резервные копии базы данных.
• db_denydatareader – запрещает выполнение SELECT для всех таблиц базы данных.
• db_denydatawriter – запрещает выполнение INSERT, UPDATE и DELETE для всех таблиц базы данных
Разрешения:
Предоставление доступа
GRANT
{ разрешение [,...n] }{ ON таблица | представление [(поле[,...n])]
| ON [хранимая процедура[,...n] }
TO учетная запись [,...n][WITH GRANT OPTION]
Запрещение доступа
DENY
{ разрешение [,...n] }{ ON таблица | представление [(поле[,...n])]
| ON [хранимая процедура[,...n] }
TO учетная запись [,...n][CASCADE]
Неявное отклонение доступа
REVOKE [GRANT OPTION FOR]
{ разрешение [,...n] }{ ON таблица | представление [(поле[,...n])]
| ON [хранимая процедура[,...n] }
TO учетная запись [,...n][CASCADE]
Курсоры
это область в памяти базы данных, которая предназначена для хранения последнего оператора SQL.
В соответствии со стандартом SQL при работе с курсорами можно выделить следующие основные действия:
• создание или объявление курсора ;
• открытие курсора, т.е. наполнение его данными, которые сохраняются в многоуровневой памяти ;
• выборка из курсора и изменение с его помощью строк данных;
• закрытие курсора, после чего он становится недоступным для пользовательских программ;
• освобождение курсора, т.е. удаление курсора как объекта, поскольку его закрытие необязательно освобождает ассоциированную с ним память
SQL Server поддерживает три вида курсоров:
• курсоры SQL применяются в основном внутри триггеров, хранимых процедур и сценариев;
• курсоры сервера действуют на сервере и реализуют программный интерфейс приложений для ODBC, OLE DB, DB_Library;
• курсоры клиента реализуются на самом клиенте. Они выбирают весь результирующий набор строк из сервера и сохраняют его локально, что позволяет ускорить операции обработки данных за счет снижения потерь времени на выполнение сетевых операций
Категории:
последовательные
прокручиваемые
Команды:
• DECLARE – создание или объявление курсора ;
• OPEN – открытие курсора, т.е. наполнение его данными;
• FETCH – выборка из курсора и изменение строк данных с помощью курсора;
• CLOSE – закрытие курсора ;
• DEALLOCATE – освобождение курсора, т.е. удаление курсора как объекта
Триггеры
вид хранимой процедуры, выполняемый автоматически при возникновении событий языка обработки данных (DML) в базе данных.
События:
INSERT
UPDATE
DELETE
-Каждый триггер привязывается к конкретной таблице
-Все производимые модификации данных рассматриваются как одна транзакция
-Создает триггер только владелец базы данных
Цели:
-Проверка корректности введенных данных и выполнение сложных ограничений целостности данных
-Выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновлении таблицы
-Накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили
-Поддержка репликации
Типы:
Триггеры AFTER выполняются после успешного выполнения вызвавших его команд
Триггеры INSTEAD OF – выполняются вместо выполнения команд
Программирование
При выполнении команд добавления, изменения и удаления записей сервер создает две специальные таблицы:
-inserted
содержит добавленные строки или новые значения строк после выполнения UPDATE
-deleted
содержит удаленные строки или старые значения строк после выполнения UPDATE
XML
это встроенный в SQL Server тип данных
Позволяет создавать -
столбцы таблиц:
CREATE TABLE T
(Col1 int primary key, Col2 xml)
переменные типа xml:
DECLARE @x xml
Методы типа данных XML
Метод query()
Определяет запрос XQuery для экземпляра типа данных xml. Метод возвращает экземпляр нетипизированного xml
Метод value()
Выполняет запрос XQuery к структуре XML и возвращает скалярное значение
Метод exist()
Возвращает значение типа bit, показывающее существует ли указанный элемент xml
Метод nodes()
Результатом метода nodes() является набор строк, содержащий логические копии исходных экземпляров XML. В этих логических копиях контекстным узлом каждого экземпляра строки устанавливается один из узлов, идентифицируемых выражением запроса.
Метод modify()
Изменяет содержимое XML-документа используя XML DML
OPENXML
-разбор документа во внутреннее DOM представление при помощи процедуры sp_xml_preparedocument
-выборка и формирование реляционного набора строк на основе XML-документа
FOR XML
Получение результатов SQL-запроса в формате XML