Предложение create procedure

Предложение CREATE PROCEDURE используется для создания хранимой процедуры. Оно имеет следующий упрощенный синтаксис:

CREATE PROC proc_name

[

 {@parameterdata_type} [= default] [OUTPUT]

]

[...]

AS

 sql_statements

Аргумент proc_name устанавливает имя хранимой процедуры, которое должно быть уникально в рамках текущей базы данных. Аргумент @parameter определяет параметр процедуры. В предложении CREATE PROCEDURE можно определить один или более параметров. Если для параметра нет значения по умолчанию, он должен быть передан пользователем (или клиентским приложением) при вызове процедуры. В Microsoft SQL Server 7.0 число параметров хранимой процедуры не должно превышать 1024; по умолчанию они могут иметь NULL-значения.

Отметим, однако, что некоторые универсальные механизмы доступа к данным могут накладывать дополнительные ограничения на число параметров хранимых процедур. Например, BDE-драйвер для Oracle 8 способен работать только с процедурами, число параметров которых не превышает 10.

Аргумент data_type указывает тип данных для параметра. Ключевое слово default может быть использовано для установки значений по умолчанию — это может быть константа или NULL. Если указано значение по умолчанию, процедура может быть вызвана без указания значения параметра. Если процедура использует параметр с ключевым словом LIKE, ее значение по умолчанию может содержать групповые символы (%, _, [] и [^]).

Ключевое слово OUTPUT показывает, что это возвращаемый параметр.

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

Процедура, созданная с помощью предложения CREATE PROCEDURE, будет сохранена в текущей базе данных. В Microsoft SQL Server имена процедур содержатся в системной таблице sysobjects, а исходный текст — в таблице syscomments.

Для изменения созданной ранее хранимой процедуры следует использовать предложение ALTER PROCEDURE, кратко описанное в следующем разделе.

Предложение alter procedure

Это SQL-предложение применяется для изменения уже существующей хранимой процедуры. Предложение ALTER PROCEDURE имеет тот же синтаксис, что и предложение CREATE PROCEDURE. Один из примеров, когда нужно изменить хранимую процедуру, — зашифровать ее исходный текст после того, как процедура отлажена, с целью скрыть ее исходный текст от других пользователей.

Если хранимая процедура больше не нужна, мы можем удалить ее с помощью предложения DROP PROCEDURE, которое будет обсуждаться ниже.

Предложение drop procedure

Это предложение используется для удаления хранимых процедур из базы данных. Предложение DROP PROCEDURE принимает один аргумент — имя удаляемой процедуры.

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

Создание и использование хранимых процедур

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

SELECT Territories.TerritoryDescription,

 Region.RegionDescription

FROM Territories INNER JOIN

 Region ON Territories.RegionID = Region.RegionID

WHERE Territories.RegionID = 1

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

В базе данных NorthWind четыре региона с номерами от 1 до 4. Это означает, что переменная RegID должна быть целого типа. Код хранимой процедуры приведен ниже:

CREATE PROCEDURE ShowRegion

 @RegIDint

AS

SELECT Territories.TerritoryDescription,

   Region.RegionDescription

 FROM Territories INNER JOIN

   Region ON Territories.RegionID = Region.RegionID

 WHERE Territories.RegionID = @RegID

25) Триггеры назначение создание, типы Триггеры являются одной из разновидностей хранимых процедур. Их исполнение происходит при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML). Триггеры используются для проверки целостности данных, а также для отката транзакций.

Триггер – это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных. Применение триггеров большей частью весьма удобно для пользователей базы данных. И все же их использование часто связано с дополнительными затратами ресурсов на операции ввода/вывода. В том случае, когда тех же результатов (с гораздо меньшими непроизводительными затратами ресурсов) можно добиться с помощью хранимых процедур или прикладных программ, применение триггеров нецелесообразно.

Триггеры – особый инструмент SQL-сервера, используемый для поддержания целостности данных в базе данных. С помощью ограничений целостности, правил и значений по умолчанию не всегда можно добиться нужного уровня функциональности. Часто требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность. Кроме того, иногда необходимо отслеживать изменения значений таблицы, чтобы нужным образом изменить связанные данные. Триггеры можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответствии с правилами, стандартными значениями и т.д.

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

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

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

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

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

• выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновлении таблицы, реализованном определенным образом;

• накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;

• поддержка репликации.

Основной формат команды CREATE TRIGGER показан ниже:

<Определение_триггера>::=

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

BEFORE | AFTER <триггерное_событие>

ON <имя_таблицы>

[REFERENCING

<список_старых_или_новых_псевдонимов>]

[FOR EACH { ROW | STATEMENT}]

[WHEN(условие_триггера)]

<тело_триггера>

триггерные события состоят из вставки, удаления и обновления строк в таблице. В последнем случае для триггерного события можно указать конкретные имена столбцов таблицы. Время запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанных с ним событий) или AFTER (после их выполнения).

Выполняемые триггером действия задаются для каждой строки (FOR EACH ROW), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT).

Обозначение <список_старых_или_новых_псевдонимов> относится к таким компонентам, как старая или новая строка (OLD / NEW) либо старая или новая таблица (OLD TABLE / NEW TABLE). Ясно, что старые значения не применимы для событий вставки, а новые – для событий удаления.

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

• сложность: при перемещении некоторых функций в базу данных усложняются задачи ее проектирования, реализации и администрирования;

• скрытая функциональность: перенос части функций в базу данных и сохранение их в виде одного или нескольких триггеров иногда приводит к сокрытию от пользователя некоторых функциональных возможностей. Хотя это в определенной степени упрощает его работу, но, к сожалению, может стать причиной незапланированных, потенциально нежелательных и вредных побочных эффектов, поскольку в этом случае пользователь не в состоянии контролировать все процессы, происходящие в базе данных;

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

Неправильно написанные триггеры могут привести к серьезным проблемам, таким, например, как появление "мертвых" блокировок. Триггеры способны длительное время блокировать множество ресурсов, поэтому следует обратить особое внимание на сведение к минимуму конфликтов доступа.

26) Средства работы с удаленными БД инструменты компоненты Средства работы с удаленными БД Система Delphi обеспечивает разработку приложений для различных серверов, предоставляя для этого соответствующие средства. Отметим, что многие изло­женные ранее принципы разработки приложений и средства для работы с ло­кальными БД относятся и к работе с удаленными БД. В частности, для разра­ботки приложений используются такие компоненты, как источник данных DataSource, наборы данных Table и Query, сетка DBGrid и др. Замечание Для реализации реляционного способа доступа к удаленной БД необходимо использовать только средства языка ^ SQL. Поэтому в качестве компонентов должны выбираться такие, как Query, StoredProcили UpdateSQL. Кроме того, для набора данных нельзя использовать методы, характерные для навигаци­онного способа доступа, например, Next и Previous для перемещения текуще­го указателя или Edit, Insert, Appendили Delete для изменения записей. Напомним, что если при выполнении с помощью компонента Query модифи­цирующего БД запроса нет необходимости в получении результирующего набо­ра данных, то этот запрос предпочтительнее выполнять с помощью метода ExecSQL. Например: Queryl.Close; Queryl.SQL.Clear; Queryl.SQL.Add('DELETE FROM Personnel WHERE Position = 'Менеджер'); Queryl.ExecSQL; Для работы с таблицами и запросами по-прежнему можно использовать такие программы, как DatabaseDesktop и SQL Explorer. Средства Delphi, предназначенные для работы с удаленными БД, можно разде­лить на два вида:

• инструментальные средства;

• компоненты.

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

• InterBaseServerManager — программа управления запуском сервера InterBase;

• IBConsole — консоль сервера InterBase;

• SQL Monitor — программа отслеживания порядка выполнения SQL-запросов к удаленным БД.

Отметим, что в предыдущей версии сервера вместо программы IBConsole ис­пользовалась InterBaseWindowsInteractive SQL (WISQL). Кроме того, значи­тельно упростилась программа InterBaseServerManager, а многие ее функции теперь реализованы в программе IBConsole. Компоненты предназначены для создания приложений, выполняющих опера­ции с удаленной БД. Перечислим наиболее важные из них:

• Database— соединение с БД;

• Session — текущий сеанс работы с БД;

• StoredProc — вызов хранимой процедуры;

• UpdateSQL — модификация набора данных, основанного на SQL-запросе;

• DCOMConnection — DCOM-соединение;

• компоненты страниц dbExpressи InterBaseПалитры компонентов.

Отметим, что многие из названных компонентов, например, Database, UpdateSQL и Session, используются также при работе с локальными БД. Так, компонент Database позволяет реализовать механизм транзакций, как было по­казано в главе, посвященнойнавигационному способу доступа к данным. Одна­ко наиболее часто эти компонентыприменяются именно при работе с удален­ными базами. Часть компонентов, например, клиентский набор данных ClientDataSet и со­единение с сервером DCOMConnection, предназначена для работы в трехзвенной архитектуре "клиент-сервер" ("тонкий" клиент) и используется для построения сервера приложений. В основе операций, выполняемых с удаленными БД как с помощью инструмен­тальных средств, так и программно, лежит язык ^ SQL. Например, при создании таблицы с помощью программы IBConsole (в ее окне Interactive SQL) необхо­димо набрать и выполнить SQL-запрос (оператор) createtable. Если создание таблицы осуществляется из приложения пользователя, то для этой цели исполь­зуется набор данных Query, который выполняет такой же запрос. Различие за­ключается в основном в том, каким образом выполняется SQL-запрос к удален­ной БД. Итак, для удаленных БД разница между средствами, используемыми в приложе­нии, и инструментальными средствами намного меньше, чем для локальных баз данных. Поэтому далее для удаленных БД использование инструментальных средств (на примере программы IBConsole) и создание приложений рассматри­ваются параллельно.

27) проблемы при параллельной обработке транзакций В предыдущей статье защита данных – восстановление мы рассмотрели принципы транзакций, контрольных точек, восстановление носителей, двухфазной фиксации и зеркального копирования. Теперь настало время рассказать о принципах параллелизма.

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

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

Если 2 транзакции одновременно изменяют и фиксируют изменения одних и тех же данных, то сохраняются результаты той транзакции, которая завершилась последней по времени.

2. Незафиксированные зависимости, или т.н. «грязное чтение» (DirtyRead). Появляется, если с помощью некой транзакции Т2 осуществляется чтение или изменение некоторого кортежа, только что измененного другой транзакцией Т1,однако изменения еще не были подтверждены. Если данные транзакции Т1 подтверждены не будут, то результаты транзакции Т2 окажутся неверными.

3. Несогласованные данные (неповторяемое чтение FuzzyRead). Возникает, когда одна транзакция читает кортеж, а другая изменяет его и фиксирует изменения до окончания первой.

4. Строки-призраки (Phantom).Возникает при работе не с одним кортежем, а при выполнении групповых операции (вычисление среднего, подсчет баланса, т. е. когда обрабатывается несколько строк одновременно). В таких сложных обработках, как правило,некий массив кортежей читается неоднократно. Если между первым и вторым чтениями была удалена или добавлена запись в массив строк, при повторном считывании массива появляется лишняя строка (фантом), что может привести к неверным результатам.

Т.о. СУБД должна гарантировать, что если 2 транзакции выполняются параллельно, то результаты их выполнения должны быть аналогичны тем, которые были бы при их последовательном выполнении, каждый пользователь должен чувствовать себя так, как будто он работает монопольно (концепция сериализации транзакций).

НУЖНАЯ ИНФА

Доменпредставляет собой именованное описание столбца. После определения домена его имя можно использовать для описания других столбцов. Аналогом домена является тип данных.

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

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

• вместо текста запроса серверу передается по сети короткое обращение к хранимой процедуре;

• хранимая процедура не требует предварительной синтаксической проверки.

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

Функция, определяемая пользователем, представляет собой обычную функцию, написанную на алгоритмическом языке, например, Pascal. Созданная функция оформляется в виде динамической библиотеки DLL, откуда может быть вызвана обычным способом. Для обеспечения вызова функции системе Windows должен быть известен путь к соответствующей библиотеке. Использование таких функций расширяет состав функций языка SQL.

Механизм кэшированных изменений заключается в том, что на компьютере клиента в кэше (буфере) создается локальная копия данных, и все изменения в данных выполняются в этой копии. Для хранения локальной копии используется специальный буфер (кэш). Сделанные изменения можно подтвердить, перенеся их в основную БД, хранящуюся на сервере, или отказаться от них. Этот меха­низм напоминает механизм транзакций, но, в отличие от него, снижает нагрузку на сеть, т. к. все изменения передаются в основную БД одним пакетом. Следует иметь к виду, что для всех записей локальной копии отсутствуют блокировки на изменение их значений. Блокировки могут быть установлены другими приложе­ниями для основной БД, находящейся на сервере.Механизм кэшированных изменений реализуется в приложении, для чего компоненты, в первую очередь Database, Table и Query (используемые при доступе с помощью BDE), имеют соответствующие средства. Кроме того, механизм кэшированных изменений поддерживается предназначенным для этого компонен­том UpdateSQL.Основные достоинства рассматриваемого механизма проявляются для удален­ных БД, но его можно использовать и при работе с локальными БД.

Привилегии представляют собой права доступа к БД. Управление привилегиями заключается в их установке и удалении. После создания объекта БД (например, таблицы) доступ к ней разрешен только создателю и системному администрато­ру, имеющему имя SYSDBA. Для доступа к БД остальных пользователей им нуж­но назначить соответствующие привилегии. Сразу после появления нового пользователя, созданного например, с помощью программы InterBaseManagerServer , этот пользователь имеет минимальные права доступа: ему разрешено только войти в БД (соединиться с ней), указав свое имя и пароль, однако ни один объект этой БД ему не доступен. Чтобы обеспечить возможность активной работы с БД, нужно определить (переопределить) привилегии.

Установку привилегий выполняет инструкция Grant. Привилегии позволяют разграничить доступ к таблицам и просмотрам со сторо­ны пользователей. При этом под "пользователем" понимается любой объект, обращающийся к данным. Кроме собственно пользователя (приложения), таки­ми объектами могут быть таблицы, просмотры, хранимые процедуры и тригге­ры. Если привилегия предоставляется одновременно нескольким пользователям, то их имена перечисляются через запятую.