|
|
Триггеры в диалекте Oracle PL/SQLВ Oracle возможности создания триггеров шире, чем в Informix и Transact-SQL. В этой СУБД для создания триггеров тоже используется инструкция create trigger, но структура ее иная. Подобно Informix, она позволяет связать триггер с различными этапами обработки запроса, но на трех разных уровнях: • Триггер уровня инструкции (statement) вызывается один раз для каждой инструкции SQL. Он может быть вызван до или после ее выполнения. • Триггер уровня записи (row) вызывается один раз для каждой модифицируемой записи. Он также может вызываться до или после модификации; • Замещающий триггер (instead OF) выполняется вместо инструкции SQL. С помощью такого триггера можно отслеживать попытки приложения или пользователя обновить, добавить или удалить записи и вместо этих действий выполнять свои собственные. Вы можете определить триггер, который должен выполняться вместо некоторой инструкции или вместо каждой попытки изменения строки таблицы. Всего получается 14 различных типов триггеров. Двенадцать из них — это комбинации операций insert, update и delete с опциями before или after на уровне row или statement (3x2*2) плюс еще два триггера типа instead of уровня row и statement. Однако на практике в реляционных базах данных Oracle триггеры типа instead OF применяются редко; они были введены в Oracle8 для поддержки ее некоторых новейших объектно-ориентированных функций.
Дополнительные вопросы, связанные с использованием триггеров В базах данных с триггерами могут быть связаны те же деловые правила, что и с инструкциями update и delete. Например, триггер может вызвать серию каскадных операций. Предположим, что триггер активизируется в ответ на попытку пользователя обновить содержимое таблицы. В теле этого триггера выполняется инструкция update, обновляющая другую таблицу. Триггер этой второй таблицы может обновить еще одну таблицу и т.д. Ситуация ухудшается, когда один из этих триггеров пытается обновить исходную таблицу, обновление которой и вызвало всю серию операций. В этом случае получается бесконечный цикл активизации триггеров. Вразличных СУБД эта проблема решается по-разному, водних системах ограничивается набор действий, которые могут выполняться триггерами. В других предусмотрены встроенные функции, которые позволяют триггеру определить уровень вложенности, на котором он работает. В-третьих, имеются системные установки, определяющие, допустимо ли "каскадирование" триггеров. А есть системы, которые ограничивают уровень вложенности каскадно выполняемых триггеров. Вторая проблема триггеров состоит в том, что при пакетных операциях с базой данных, например при внесении в нее больших объемов информации, триггеры сильно замедляют работу базы данных. Поэтому некоторые СУБД позволяют избирательно отключать триггеры в таких ситуациях. В Oracle, например, предусмотрена такая форма инструкции alter trigger: ALTER TRIGGER TEST DISABLE; Аналогичную возможность обеспечивает инструкция create trigger в Informix. Рассмотри пример создания триггера в СУБД Sybase SQL Anywhere.
Процедуры и триггеры хранят операторы SQL и управляющие операторы в базе данных для их использования любыми приложениями. Они улучшают безопасность, эффективность и стандартизацию баз данных.
Пример триггера уровня записи INSERT Показанный ниже триггер проверяет, что бы дата рождения (birthdate) введенная для нового сотрудника была приемлема.
CREATE TRIGGER check_birth_date
AFTER INSERT ON Employee REFERENCING NEW AS new_employee FOR EACH ROW BEGIN DECLARE err_user_error EXCEPTION FOR SQLSTATE ‘99999’; IF new_employee.birth_date > ‘June 6, 1994’ THEN SIGNAL err_user_error; END IF; END
Триггер вызывается сразу после добавления любой записи в таблицу. Он обнаруживает и не допускает новые записи в которых дата рождения позднее чем 6 июня 1994. Конструкция REFERENCING NEW AS new_employee позволяет операторам в тексте триггера ссылаться на данные в новой записи используя алиас new_employee.
Для оператора INSERT который добавляет несколько записей в таблицу employee, триггер check_birth_date вызывается для каждой новой записи. Если триггер завершается неудачно хотя бы на одной записи, все изменения сделанные оператором INSERT отменяются.
Можно определить триггер срабатывающий до того как запись будет добавлена, для этого достаточно изменить первую строку примера:
CREATE TRIGGER mytrigger BEFORE INSERT ON Employee
Выражение REFERENCING NEW позволяет ссылаться на значения добавляемой записи; и эта ссылка не зависит от типа триггера (BEFORE or AFTER).
Пример триггера уровня записи для операции DELETE.
CREATE TRIGGER mytrigger BEFORE DELETE ON employee
REFERENCING OLD AS oldtable FOR EACH ROW BEGIN ... END
Выражение REFERENCING OLD позволяет ссылаться в модуле триггера на значения в удаляемой записи с помощью алиаса oldtable. Триггер легко преобразовать к типу after путем изменения первой строки примера.
CREATE TRIGGER mytrigger BEFORE DELETE ON employee
Выражение REFERENCING OLD не зависит от типа триггера (BEFORE or AFTER).
Пример триггера уровня оператора для операции UPDATE.
CREATE TRIGGER mytrigger AFTER UPDATE ON employee
REFERENCING NEW AS table_after_update OLD AS table_before_update FOR EACH STATEMENT BEGIN ... END
Выражения REFERENCING NEW и REFERENCING OLD позволяют в тексте триггра ссылаться на старые и новые значения записи, для которой выполняется операция UPDATE. Столбцы с новыми значениями доступны через алиас table_after_update, столбцы со старыми значениями через алиас table_before_update. Выражения REFERENCING NEW и REFERENCING OLD имеют разный смысл для триггеров уровня оператора и триггеров уровня записи. Для триггеров уровня оператора REFERENCING OLD или NEW являются алиасами таблиц, в триггере уровня записи они ссылаются на изменяемую запись.
Контрольные вопросы 1. Перечислите преимущества и недостатки триггеров. 2. Особенности реализации триггеров в Transact-SQL. 3. Особенности реализации триггеров в СУБД Informix. 4. Особенности реализации триггеров в Oracle PL/SQL. 5. Укажите последовательность создания триггера в Sybase Sql Anywhere.
Лекция 13 Триггеры выполняются автоматически при выполнении операций INSERT, UPDATE, или DELETE для таблиц, с которыми они связаны. Триггеры уровня записи выполняются для каждой изменяемой записи, триггеры уровня оператора выполняются один раз для соответствующего оператора вне зависимости от количества изменяемых записей. Когда срабатывают INSERT, UPDATE, или DELETE триггеры, порядок выполнения операций следующий:
1 Выполняется какой-либо BEFORE триггер. 2 Выполняются действия определенные правилами ссылочной целостности. 3 Выполняется соответствующая операция. 4 Выполняются триггеры типа AFTER.
Если на любом шаге обнаруживается ошибка, которая не обрабатывается процедурой или триггером все сделанные изменения отменяются, последующие шаги не выполняются и операция, вызвавшая срабатывание триггера завершается неудачей.
Интерфейсы взаимодействия с БД
Протокол ODBC и стандарт SQL/CLI Функции SQL/CLI и их группировка Структура протокола ODBC Функции ODBC и расширенные возможности ODBC.
Протокол ODBC и стандарт SQL/CLI
ODBC (Open Database Connectivity — открытый доступ к базам данных) – это разработанный компанией Microsoft универсальный программный интерфейс для доступа к базам данных. Хотя в современном компьютерном мире Microsoft играет важную роль как производитель программного обеспечения для баз данных, все же в первую очередь, она один из ведущих производителей операционных систем, именно это послужило мотивом создания ODBC: Microsoft захотела облегчит разработчикам приложений Windows доступ к базам данных. Все дело в том, что различные СУБД существенно отличаются друг от друга, как и их программные интерфейсы. Если разработчику нужно было написать приложение, работающее с базами данных нескольких СУБД, для каждой из них приходилось писать отдельный интерфейсный модуль (обычно называемый драйвером). Чтобы избавить программистов от выполнения одной и той же рутинной и достаточно сложной работы, Microsoft решила на уровне операционной системы стандартизировать интерфейс взаимодействия между приложениями и СУБД, благодаря чему во всех программах мог бы использоваться один и тот же универсальный набор функций, поддерживаемый всеми производителями СУБД. Таким образом, от внедрения ODBC выиграли и разработчики приложений, и производители СУБД, для которых также решалась проблема совместимости.
Формирование стандарта SQL/CLI
Даже если бы протокол ODBC был всего лишь собственным стандартом компании Microsoft, его значение все равно было бы очень велико. Однако Microsol постаралась сделать его независимым от конкретной СУБД. Одновременно ассоциация производителей СУБД (SQL Access Group) работала над стандартизацией протоколов удаленного доступа к базам данных в архитектуре клиент/сервер. Microsoft убедила ассоциацию принять ODBC в качестве независимого стандарта доступа базам данных. В дальнейшем этот стандарт перешел в ведение другой организации, Европейского консорциума Х/Open, и был включен в ее комплект стандартов САЕ (Common Application Environment — единая прикладная среда). С ростом популярности программных интерфейсов доступа к базам данных организации, ответственные за принятие официальных стандартов, стали уделять этому аспекту SQL все более пристальное внимание. На основе стандарта Х/Open (базировавшегося на ной версии ODBC, разработанной компанией Microsoft) с небольшими модификациями - был разработан официальный стандарт ANSI/ISO. Этот стандарт, известный SQL/CLI (SQL/Call Level Interface — интерфейс уровня вызовов функций), был опубликован в 1995 году под названием ANSI/ISO/IEC 9075-3-1995. Он представляет собой Часть 3 разрабатываемого многоуровневого стандарта SQL3, который является развитием опубликованного в 1992 году стандарта SQL2. Microsoft привела ODBC в соответствие со стандартом SQL/CLI, реализация которого составляет ядро последней версии ее протокола - ODBC 3. Однако полный набор высокоуровневых функций ODBC 3 выходит далеко за рамки спецификации CLI: он предоставляет разработчикам приложений гораздо более широкие возможности и решает ряд специфических задач, связанных с использованием ODBC как части операционной системы Windows. Преимущества связки ODBC/CLI как для разработчиков приложений, так и для производителей СУБД были настолько очевидны, что оба стандарта очень быстро получили самую широкую поддержку. Практически все производители реляционных СУБД включили в свои продукты соответствующие интерфейсы. ODBC и CLI Поддерживаются тысячами приложений, включая ведущие пакеты инструментальных средств разработки. Стандартом SQL/CLI определяется около сорока функций (см. табл.). Они служат подключения к серверу баз данных, выполнения инструкций SQL, обработки результатов запросов, а также обработки ошибок, произошедших в ходе выполнения инструкций. Эти функции обеспечивают полный набор возможностей, предоставляемых встроенным SQL, включая как статический, так и динамический SQL. Функции SQL/CLI
Функция Описание Управление ресурсами и подключением к базе данных SQLAllocHandle() Выделяет ресурсы для среды SQL; сеанса подключения к базе данных, описателя CLI или инструкции SQLFreeHandle () Освобождает ранее выделенные ресурсы SQLAllocEnv () Выделяет ресурсы для среды SQL SQLFreeEnv () Освобождает ресурсы, выделенные для среды SQL SQLAllocConnect () Выделяет ресурсы для сеанса подключения к базе данных SQLFreeConnect () Освобождает ресурсы, выделенные для сеанса подключения к базе данных SQLAllocstmt () Выделяет ресурсы для инструкции SQL SQLFreeStmt() Освобождает ресурсы, выделенные для инструкции SQL SQLConnect () Устанавливает соединение с базой данных SQLDisconnect() Разрывает соединение с базой данных
Выполнение инструкций SQL SQLExecDirect() Непосредственно выполняет инструкцию SQL SQLPrepare() Подготавливает инструкцию SQL к последующему выполнению SQLExecute() Выполняет ранее подготовленную инструкцию SQL SQLRowCount() Возвращает количество строк, обработанных последней инструкцией SQL
Управление транзакциями SQLEndTran() Завершает или отменяет транзакцию SQLCancel() Отменяет выполнение текущей инструкции SQL
Обработка параметров
SQLBindParam() Связывает параметр инструкции SQL с адресом программного буфера SQLParamData() Сообщает приложению адрес параметра подготавливаемой инструкции, для которого необходимо предоставить данные, прежде чем инструкция сможет быть выполнена SQLPutData() Предоставляет данные для параметра подготавливаемой инструкции; эта функция может вызываться многократно, чтобы передавать данные по частям Обработка результатов запроса
SQLSetCursorName() Назначает имя набору записей SQLGetCursorName() Возвращает имя набора записей SQLBindCol() Связывает столбец в таблице результатов запроса с программным буфером SQLFetch() Возвращает следующую строку из таблицы результатов запроса SQLFetchScroll() Возвращает указанную строку из таблицы результатов запроса SQLCloseCursor() Закрывает набор записей SQLGetData() Возвращает значение указанного столбца из таблицы результатов запроса Описание результатов запроса
SQLNumResultCols() Возвращает количество столбцов в таблице результатов запроса SQLDescribeCol() Возвращает описание указанного столбца в таблице результатов запроса SQLColAttribute() Возвращает информацию об указанном атрибуте заданного столбца в таблице результатов запроса SQLGetDescField() Возвращает значение указанного поля из описателя CLI SQLSetDescField() Устанавливает значение указанного поля в описателе CLI SQLGetDescRec() Возвращает значения набора полей из описателя CLI SQLSetDescRec() Устанавливает значения набора полей в описателе CLI SQLCopyDesc() Копирует содержимое одного описателя CLI в другой Обработка ошибок
SQLError() Возвращает информацию об ошибке, произошедшей во время последнего вызова функции CL1 SQLGetDiagField() Возвращает значение указанного поля из специальной структуры CLI, содержащей диагностическую информацию SQLGetDiagRec() Возвращает значения набора полей из специальной структуры CLI, содержащей диагностическую информацию Управление атрибутами
SQLSetEnvAttr() Устанавливает значение указанного атрибута среды SQL SQLGetEnvAttr() Возвращает значение указанного атрибута среды SQL SQLSetConnectAttr() Устанавливает значение указанного атрибута сеанса подключения к базе данных SQLGetConnectAttr() Возвращает значение указанного атрибута сеанса подключения к базе данных SQLSetStmtAttr() Устанавливает значение указанного атрибута инструкции SQL SQLGetStmtAttr() Возвращает значение указанного атрибута инструкции SQL Управление драйвером SQLDataSources() Возвращает список доступных серверов баз данных SQLGetFunctions() Возвращает информацию о функциях CLI, поддерживаемых текущим драйвером SQLGetlnfо() Возвращает общую информацию об источнике данных и драйвере, которые связаны с указанным сеансом подключения к базе данных SQLGetTypelnfо() Возвращает информацию о поддерживаемых типах данных
Большинство программ использующих функции CLI выполняют последовательность действий показанную ниже: - программа подключается к библиотеке CLI и выделяет память под структуры данных, используемые функциями этой библиотеки; - программа подключается к конкретному серверу баз данных; - программа формирует инструкции SQL в собственных буферах памяти; - программа вызывает функции CLI, с помощью которых она просит сервер выполнить инструкции SQL и узнает о завершении этих инструкций; - в случае успешного выполнения инструкций SQL программа с помощью еще одной функции CLI просит сервер завершить транзакцию; - программа отключается от сервера баз данных и освобождает память, занимаемую структурами данных.
Все функции CLI возвращают код состояния. Это значение показывает код ошибки или предупреждения. В реальных программах коды состояния необходимо проверять, чтобы убедиться, что каждая вызываемая функция CLI выполнена успешно.
Протокол ODBC. Структура ODBC.
Структура протокола ODBC изображена на рис. Программное обеспечение ODBC состоит из трех основных уровней: Интерфейс вызовов функций. На самом верхнем уровне ODBC находится единый программный интерфейс, который может использоваться всеми приложениями. Этот API реализован в виде библиотеки динамической компоновки (DLL), которая является неотъемлемой частью Windows. Драйверы ODBC. На нижнем уровне располагается набор драйверов: по одному драйверу для каждой поддерживаемой СУБД. Задачей драйвера является трансляция стандартных вызовов функций ODBC в вызовы соответствующих функций, поддерживаемых конкретной СУБД (их может быть и несколько для одной функции ODBC). Каждый драйвер устанавливается в операционной системе независимо. Это позволяет производителям СУБД разрабатывать для своих продуктов собственные драйверы и распространять их независимо от Microsoft. Если СУБД располагается в той же системе, что и драйвер ODBC, то драйвер обычно напрямую вызывает внутренние API-функции СУБД. Если же доступ к базе данных осуществляется: по сети, то драйвер может: а) направлять все вызовы в клиентскую часть СУБД; которая будет переадресовывать их на сервер; б) самостоятельно управлять сеансом сетевого подключения к удаленной базе данных. Диспетчер драйверов. Средний уровень занят диспетчером драйверов ODBC, который отвечает за загрузку и выгрузку драйверов по запросам приложений, а также за направление вызовов функций ODBC, производимых приложениями соответствующим драйверам для выполнения. Когда приложению нужно получить доступ к базе данных посредством ODBC оно выполняет ту же самую последовательность начальных действий, которая предусматривается стандартом SQL/CLI. Прежде всего, программа получает дескрипторы среды и сеанса, а затем вызывает функцию SQLConnect(), указав конкретный источник данных, с которым она хочет работать. В ответ на это диспетчер драйверов ODBC анализирует переданную программой информацию о подключении и определяет, какой драйвер ей нужен. Диспетчер загружает этот драйвер в память, если только к настоящему времени драйвер не используется никакой другой программой. Все последующие вызовы функций в пределах данного сеанса обрабатываются этим драйвером. Программа может затем вызывать функцию SQLConnect() для подключения к другим источникам данных, что вызовет загрузку дополнительных драйверов; но, тем не менее, обращения к различным базам данных и различным СУБД будут производиться посредством единого набора функций.
Независимое взаимодействие сразличными СУБД
Разработав универсальный программный интерфейс и многоуровневую архитектуру ODBC с диспетчеризацией вызовов, Microsoft значительно продвинулась в направлении независимого от СУБД доступа к базам данных, но сделать доступ абсолютно "прозрачным" вряд ли возможно. Драйверы ODBC для различных СУБД могут легко замаскировать незначительные отличия их программных интерфейсов и диалектов SQL, но более фундаментальные отличия скрыть трудно или даже невозможно. ODBC частично решает эту проблему, разделяя полный набор предлагаемых возможностей на несколько функциональных уровней и вводя набор функций, позволяющих драйверу ODBC "рассказать" о себе приложению: предоставить ему информацию о своих возможностях, наборе поддерживаемых функций и типов данных. Однако само наличие нескольких уровней функциональных возможностей драйверов и выборочная поддержка драйверами этих возможностей ставит приложение в зависимость от СУБД, хотя и на другом уровне. Поэтому на практике большинство приложений использует только базовые функции ODBC. Контрольные вопросы 1. Определите назначение протокола ODBC. 2. Перечислите группы функций входящие в протокола ODBC. 3. Опишите структуру ODBC. 4. Сформулируйте преимущества использования ODBC.
Лекция 14
Функции ODBC для работы с системными каталогами.
Одной из областей, в которых ODBC предлагает возможности, выходящие за рамки стандарта SQL/CLI, является получение информации о структуре базы данных из системного каталога. Стандарт ODBC не считает обязательным наличие в базе данных информационной схемы. Взамен этот протокол включает набор специальных функций, которые предоставляют приложению информацию о структуре источника данных. Вызывая эти функции, приложение может в процессе своего выполнения получать информацию о таблицах, столбцах, привилегиях, первичных и внешних ключах и хранимых процедурах, составляющих структуру источника данных.
Функции ODBC, предназначенные для работы с системными каталогами, обычно не нужны в специализированных приложениях, а вот программам общего назначения, таким как модули формирования запросов, генераторы отчетов и утилиты анализа данных, без подобных функций обойтись. Эти функции можно вызывать в любое время после подключения к источнику данных. Например, генератор отчетов может вызвать функцию SQLConnect() и сразу же вслед за ней - функцию SQLTables(), чтобы узнать, какие таблицы имеются в базе данных. Все функции, работающие с каталогами, возвращают информацию в виде таблицы результатов запроса. Для получения этой информации приложения применяют же методы, которые используются для получения результатов обычных запросов, выполняемых посредством CLI. Например, набор записей, сформированный функцией SQLTables(), будет содержать по одной записи для каждой таблицы базы данных.
Функции ODBC, предназначенные для работы с системным каталогами
Функция Описание
SQLTables() Возвращает список таблиц заданного каталога (каталогов) и схемы (схем) SQLTablePrivileges () Возвращает список привилегий заданной таблицы (таблиц) SQLColumns() Возвращает список имен столбцов заданной таблицы (таблиц) SQLColumnPrivileges() Возвращает список привилегий на указанные столбцы заданной таблицы SQLPrimaryKeys() Возвращает список имен столбцов, составляющих первичный ключ заданной таблицы SQLForeignKeys() Возвращает список внешних ключей заданной таблицы и список внешних ключей других таблиц, которые связаны с заданной таблицей SQLSpecialColumns() Возвращает список столбцов, которые однозначно идентифицируют строку таблицы, или столбцов, которые автоматически обновляются при обновлении строки SQLStatistics() Возвращает статистические данные о таблице и ее индексах SQLProcedures() Возвращает список хранимых процедур заданного источника данных SQLProcedureColumns() Возвращает список входных и выходных параметров и имена возвращаемых столбцов для заданной хранимой процедуры (процедур)
Расширенные возможности ODBC
Как уже упоминалось, ODBC предоставляет дополнительные возможности помимо тех, что определены стандартом SQL/CLI. Многие из них предназначены, для повышения производительности приложений, использующих ODBC, за счет минимизации количества вызовов функций ODBC, необходимых для выполнения типичных задач, а также за счет сокращения сетевого трафика при работе с ODBC. Кроме того, ODBC предоставляет ряд возможностей, обеспечивающих большую независимость приложения от конкретной СУБД и помогающих приложению устанавливать соединение с источником данных. Часть дополнительных возможностей ODBC реализована в виде функций, показанных ниже. Остальные управляются атрибутами инструкций и сеансов. Многие из расширенных возможностей ODBC появились в версии 3.0 этого протокола и еще не все драйвера их поддерживают.
Некоторые дополнительные функции ODBC
Функция Описание
SQLBrowseConnect() Возвращает информацию об атрибутах указанного источника данных ODBC, которые необходимо задать для подключения к источнику SQLDrivers() Возвращает список доступных драйверов и имен их атрибутов SQLDriverConnect() Расширенная форма функции SQLConnect(), предназначенная для передачи ODBC дополнительной информации о сеансе подключения SQLNumParams() Возвращает количество параметров последней подготовленной инструкции SQL SQLBindParameter() Дополняет возможности функции SQLBindParam() SQLDescribeParam() Возвращает информацию о параметре инструкции SQL SQLBulkOperations() Выполняет пакетные операции с использованием механизма закладок SQLMoreResults() Определяет, остались ли еще необработанные записи в таблице результатов запроса SQLSetPos() Задает позицию указателя в результирующем наборе записей, разрешая приложению выполнять позиционные операции над этим набором SQLNativeSQL() Возвращает перевод заданной инструкции SQL на “родной” диалект SQL той СУБД, с которой ведется работа
Управление сеансами
Две из расширенных возможностей ODBC связаны с организацией сеансов. Механизм просмотра информации о подключении предназначен для упрощения процесса подключения к источнику данных. В основе этого механизма лежит функция SQLBrowseConnect(). Сначала приложение вызывает эту функцию, указывая имя источника данных, и в ответ получает описание необходимых для подключения атрибутов (таких как имя пользователя и пароль). Программа собирает нужную информацию (например, запросив ее у пользователя) и передает ее функции SqlBrowseConnect(), которая возвращает описание последующих атрибутов. Цикл одолжается до тех пор, пока приложение не предоставит ODBC всю информацию, обходимую для подключения к заданному источнику данных. Как только это будет сделано, соединение будет установлено. Механизм группировки подключений предназначен для более эффективного управления процессами установления/разрыва соединения в среде клиент/сервер. Когда режим группировки подключений активизирован, ODBC, получив вызов функции SQLDisconnect(), не завершает сеанс подключения. Он остается в неактивном состоянии в течение некоторого времени, и если за это время поступит новый вызов функции SQLConnect(), ODBC просто активизирует имеющееся подключение (если, конечно, приложению нужен тот же источник данных). Повторное использование подключений позволяет существенно снизить расходы, связанные с многократным входом в серверную систему (и последующим выходом из нее) в приложениях, выполняющих большое число коротких транзакций.
|
|
