Методические указания к выполнению лабораторных и курсовой работ ЙошкарОла 2007

• Название: Методические указания к выполнению лабораторных и курсовой работ ЙошкарОла 2007
• Дата: 15.03.2022
• Размер: 0.8 Mb.
• Имя файла: sql (1).doc
• Тип: Методические указания; #397929
• страница: 5 из 11

EXECUTE имя_процедуры параметр [, параметр...]
Панель Object Browser содержит папку Stored Procedures для каждой базы данных, включая главную. Каждая хранимая процедура, содержа­щаяся в списке, имеет папку Parameters. В этой папке в определенном порядке размещаются параметры хранимой процедуры, поэтому вы мо­жете воспользоваться ею для проверки имен параметров и их позиций.

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

EXECUTE хранимая_процедура @имя_парам = значение

[, @имя_парам = значение...]

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

Если вы передаете параметры по позициям, обращение к умолчанию па­раметра зависит от его положения в списке. Если параметр находится в конце списка или этот параметр единственный, вы можете просто опус­тить его (не указывать). Если параметр не является последним парамет­ром в списке, вы можете воспользоваться ключевым словом DEFAULT, чтобы указать хранимой процедуре использовать значение по умолчанию.
Синтаксис для хранимой процедуры, возвращающей значения, является неким гибридом оператора EXECUTE и оператора SET:

EXECUTE @имя_переменной = хранимая_процедура

[, парам [, парам...] ]

Большинство системных процедур имеют возвращаемые значения, но по­скольку они являются параметрами по умолчанию, их можно игнорировать в описании вызова процедуры. Если вы не указали локальную переменную для приема результатов, SQL Server просто отбросит значение.
Создание хранимых процедур
Хранимые процедуры создаются с использо­ванием одной из разновидностей оператора CREATE - CREATE PROCEDURE. Синтаксис оператора CREATE PROCEDURE сле­дующий:

CREATE PROCEDURE имя_процедуры

[список параметров]

AS

операторы_процедуры

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

Операторы_процедуры, следующие после ключевого слова AS в опера­торе CREATE, определяют действия, которые будут выполняться при вызове хранимой процедуры. Они по своему функциональному назначе­нию полностью аналогичны сценариям. Фактически можно считать все, что находится перед ключевым словом AS, заголовком SQL-сценария.

Хранимые процедуры могут вызвать другие хранимые процедуры, т. е. реализовывать вложенность. Фактическая глубина вложенности храни­мых процедур составляет 32.

Каждый из параметров в списке_параметров имеет следующую струк­туру:

@имя_параметра тип данных [=значение_по_умолчанию] [OUTPUT]

Имя_параметра должно удовлетворять правилам, принятым для иден­тификаторов. Имена параметров должны начинаться с символа @, подобно локальным переменным. Параметры являются локальными переменны­ми; они видимы только в пределах хранимой процедуры. В одной храни­мой процедуре может быть использовано максимально 2100 параметров.

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

Возврат значений реализуется с помощью оператора RETURN, который имеет следующую форму:

RETURN(int)

В операторе RETURN int - это целочисленное значение. Как мы видели раньше, возврат значений чаще всего используется для определения ста­туса выполнения хранимой процедуры. При этом 0 указывает на успеш­ное завершение выполнения, а любое другое число указывает на ошибку. Ошибки могут быть проанализированы с помощью глобальной перемен­ной @@ERROR, которая возвращает статус выполнения последней ко­манды Transact-SQL: 0 указывает на успешное выполнение, а ненулевое значение указывает, что имела место ошибка. Также оператором RETURN часто возвращаются значения локальных переменных, которые проверяются в условном операторе или используются в качестве счетчика.
Пример создания простой хранимой процедуры:
USE master

GO

IF EXISTS (SELECT *

FROM sysobjects

WHERE name = 'proctab' AND type ='P' )

DROP PROCEDURE proctab

GO

CREATE PROCEDURE proctab

AS

SELECT *

FROM ptable

WHERE day > 17

GO
Пример создания хранимой процедуры с возвращаемым значением:
USE master

GO

IF EXISTS (SELECT *

FROM sysobjects

WHERE name = 'p2table' AND type ='P' )

DROP PROCEDURE p2table

GO
CREATE PROCEDURE p2table @param char(40)

AS

DECLARE @perem int

SELECT *

FROM ptable

WHERE semestr=@param

Set @perem =@@Error

return(@perem)
Вызов хранимой процедуры и отображение результатов:
DECLARE @Ret int

EXECUTE @Ret=p2table 'OS'

PRINT @Ret

Понятие о триггерах
Преимущество триггерной процедуры состоит в том, что SQL Server автоматически вызывает ее при обращении к коман­де, для которой она определена. Это повышает устойчивость базы данных к внешним воздействиям (робастность). Нет нужды беспокоиться о том, чтобы клиенты базы данных - пользователи или приложения - разбирались и пользова­лись всеми зависимостями данных и установленными для них правилами.

Предположим, в торговой организации существует прави­ло, в соответствии с которым заказ на общую сумму 10000$ или выше требует проверки кредитоспособности. Если Credit Approved является столбцом таблицы Customer, а заказ добавляется в таблицу Order, вы не сможете использовать проверочное ограничение типа CHECK для реализации правила. Дело в том, что триггеры могут обращаться к колонкам таблиц, отличных от таблицы, по которой они определены, в то время как ограничения CHECK действуют в рамках только одной таблицы. Триггер предоставля­ет механизм, обеспечивающий исполнение правила внутри базы данных.

Хотя производительность триггеров обычно не представляет про­блемы (в отличие от курсоров), следует всегда применять правила по воз­можности на самом низком уровне. Не используйте триггеры, если можно применить проверочное ограничение CHECK, а ограничение CHECK не следует использовать, если можно обойтись ограничением UNIQUE.
Триггеры AFTER и INSTEADOF
SQL Server поддерживает два различных типа триггеров: триггеры AFTER и триггеры INSTEAD OF. Триггеры AFTER вызываются после выполнения команды, которой они назначены, а триггеры INSTEAD OF вызываются вместо команды.

Триггеры AFTER вы можете использовать для команд INSERT, UPDATE и DELETE. Триггеры AFTER можно создавать только для таблиц, но не для представлений. Для каждой из этих трех команд может быть уста­новлено несколько триггеров. С другой стороны, один триггер может быть применен для любой комбинации этих трех команд.

Триггер AFTER вызывается после того, как выполнены все операции по обработке ограничений низкого уровня, и не будут вызваны в случае на­рушения ограничения. Например, если осуществляется попытка вставить строку, которая нарушает ограничение PRIMARY KEY для таблицы, опе­ратор INSERT не будет выполнен до того, как произойдет вызов триггера.
Триггеры INSTEAD OF заменяют команду, для которой они объявлены. Подобно триггерам AFTER, вы можете определять триггеры INSTEAD OF для команд INSERT, UPDATE или DELETE. Один триггер может быть применен к нескольким командам.

Однако, в отличие от триггеров AFTER, вы можете создавать триггеры INSTEAD OF как для таблиц, так и для представлений, но для каждого действия над этой таблицей или представлением может быть создан толь­ко один триггер INSTEAD OF.

Триггеры INSTEAD OF несовместимы с каскадными изменениями свя­занных данных. Вы не можете объявить триггер INSTEAD OF DELETE или INSTEAD OF UPDATE для таблицы, внешний ключ которой затра­гивается действиями удаления (DELETE) или модификации (UPDATE).

Поскольку триггеры INSTEAD OF могут быть объявлены для представ­лений, они чрезвычайно полезны для получения функциональных воз­можностей представления, которые не могут быть доступны иным спосо­бом. Например, SQL Server не дает возможности применить для представления оператор INSERT, содержащий фразу GROUP BY, но по­зволяет вам определить триггер INSTEAD OF INSERT для представле­ния. Вы можете воспользоваться триггером для вставки записей в табли­цы, лежащие в основе представления, тем самым давая знать пользователю, что новая строка была вставлена в представление.
Триггеры BEFORE
Триггера BEFORE не существует, но триггер INSTEAD OF может порож­дать команду, для которой он объявлен, и эта команда будет выдаваться, как если бы триггера INSTEAD OF не было.

Например, если необходимо проверить некое условие до выполнения ко­манды INSERT, можно объявить триггер INSTEAD OF INSERT. Триг­гер INSTEAD OF будет выполнять проверку, а затем выполнять команду INSERT для таблицы. Оператор INSERT будет выполняться обычным образом, не порождая рекурсивных вызовов триггера INSTEAD OF.
Создание триггеров
SQL Server накладывает ряд ограничений на действия, выполняемые триггерами. Невозможно создавать (командой CREATE), изменять (ко­мандой ALTER) или удалять (командой DROP) базу данных с использо­ванием триггера; невозможно восстанавливать базу данных или файл журнала; невозможно выполнять определенные операции, которые из­меняют конфигурацию SQL Server.

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

Теоретически можно использовать оператор RETURN для возврата значения из триггера, но нельзя полагаться на то, что клиентскому приложению известно о существовании триггера и на то, какие действия он выполняет. Обычно в таких случаях лучше использовать команду RAISERROR, поскольку в большинстве разрабатываемых приложений предусмотрена возможность обработки таких ошибок.
Как и любые другие объекты базы данных, триггер определяется с помо­щью соответствующей формы оператора CREATE. Базовый синтаксис оператора создания триггера следующий:

CREATE TRIGGER имя_триггера

ON таблица_или_представление тип_триггера, список_команд

AS onepaтopы_SQL

Имя_триггера должно удовлетворять правилам, принятым для иденти­фикаторов. Таблица_или_представление может быть именем представ­ления, только если тип_триггера есть INSTEAD OF, поскольку только этот тип триггера вы можете определять для представлений. Триггеры не могут быть созданы для временных или системных таблиц, но они мо­гут ссылаться на временные таблицы.

Тип_триггера должен быть выражен одним из ключевых слов AFTER, FOR или INSTEAD OF, в то время как список_команд может быть любой комбинацией команд INSERT, UPDATE или DELETE. Если вы указывае­те более одной команды, их следует отделять запятыми.

Ранние версии SQL Server поддерживали только триггеры AFTER и использовали ключевое слово FOR в качестве типа_триггера. Данный синтаксис по-прежнему поддерживается SQL Server 2000, но он будет означать то же самое, что триггер AFTER.

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

USE master

GO

CREATE TABLE Bicycle_Inventory

(

make_name char(10) NOT NULL,

make_id tinyint NOT NULL,

model_name char(12) NOT NULL,

model_id tinyint NOT NULL,

in_stock tinyint NOT NULL,

on_order tinyint NULL,

)

GO
IF EXISTS (SELECT name

FROM sysobjects

WHERE name = 'Print_Update' AND

type = 'TR')

DROP TRIGGER Print_Update

GO

CREATE TRIGGER Print_Update

ON Bicycle_Inventory

AFTER UPDATE

AS

PRINT ' The Bicycle_Inventory table was updated'

GO
Примериспользованиятриггера
INSERT INTO Bicycle_Inventory VALUES ('Trek',1,'5500',5,1,0)

GO

UPDATE Bicycle_Inventory

SET make_id = 2

WHERE model_name = '5500'

GO
Использованиефункции UPDATE
SQL Server предоставляет специальную функцию, UPDATE, которая может быть использована в триггере для определения, подвергся ли из­менению определенный столбец в строке. Функция UPDATE имеет сле­дующий синтаксис:

UPDATE (имя_столбца)

Функция UPDATE будет возвращать TRUE, если значения данных для указанного столбца были изменены командой INSERT или командой UPDATE.

Другая функция Transact-SQL, COLUMNS_UPDATED, возвраща­ет битовую маску, биты которой устанавливаются в единицу для тех стол­бцов, которые были модифицированы. Функция COLUMNS_UPDATED может оказаться более эффективной, чем функция UPDATE, если вам необходимо проверить статус нескольких столбцов.
Использование таблиц вставки и удаления
SQL Server создает две таблицы, облегчающие манипулирование дан­ными при применении триггеров. Таблицы вставки и удаления представ­ляют собой временные резидентно хранящиеся в памяти таблицы, ко­торые содержат строки, подвергшиеся воздействию команды, вызвавшей триггер.

Если триггер вызывается из команды DELETE, таблица удаления будет содержать строки, которые были удалены из таблицы. При вызове из ко­манды INSERT таблица вставки будет содержать копию новых (вставля­емых) строк. Физически оператор UPDATE представляет собой последо­вательное выполнение команды удаления DELETE и вставки INSERT, так что таблица удаления будет содержать старые значения, а таблица вставки - новые. Вы можете обращаться к содержимому этих таблиц из триг­гера, но вы не можете изменять их.

Напомним, что триггеры AFTER не вызываются до тех пор, пока не будет осуществлена модификация таблицы, т.е. строки в исходных таблицах уже подвергнутся изменениям. С другой стороны, триггер INSTEAD OF вы­зывается вместо действия, для которого он определен, поэтому таблица еще не будет изменена. Фактически таблица вообще не будет изменена, если команда INSTEAD OF не назначит соответствующие команды.

Триггер вызывается для команды, а не для строки. Следу­ет проявлять осторожность при написании триггеров, затрагивающих несколько строк (в случае их наличия) в таблицах вставки и удаления. Глобальная переменная @@ROWCOUNT будет возвращать количество записей в этих таблицах, если обратиться к ней в начале описания триггера.
Пример использования таблицыудаления

Все строки, удаленные из таблицы, сохраняются в резервной таблице для последующего анализа данных (сохраняются строки, удаленные из таблицы roysched, в новой таблице с именем roysched_backup):
USE pubs

GO

CREATE TABLE roysched_backup

(

title_id tid NOT NULL,

lorange int NULL,

hirange int NULL,

royalty int NULL

)

CREATE TRIGGER tr_roysched_backup

ON roysched

FOR DELETE

AS

INSERT INTO roysched_backup SELECT * FROM deleted

GO

SELECT * FROM roysched_backup

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

Функция является детерминированной, если при одном и том же задан­ном входном значении она всегда возвращает один и тот же результат. Так, встроенная функция DATEADD является детерминированной - до­бавление трех дней к дате 20 апреля 1958 г. всегда дает дату 23 апреля 1958 г.

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

Детерминизм пользовательской функции не зависит от того, является ли она скалярной или табличной, - функции обоих этих типов могут быть как детерминированными, так и недетерминированными. Пользовательские функции считаются детерминированными, если они обращаются к объек­там базы данных за пределами области действия (видимости) функции.

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

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

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