Язык SQL, как мы уже видели в лекции 5, предназначен для организации доступа к базам данных. При этом предполагается, что доступ к БД может быть осуществлен в двух режимах: в интерактивном режиме и в режиме выполнения прикладных программ (приложений).
Эта двойственность SQL создает ряд преимуществ:
SQL действительно является языком по работе с базами данных, но в явном виде он не является языком программирования. В нем отсутствуют традиционные операторы, организующие циклы, позволяющие объявить и использовать внутренние переменные, организовать анализ некоторых условий и возможность изменения хода программы в зависимости от выполненного условия. В общем случае можно назвать SQL подъязыком, который служит исключительно для управления базами данных. Для создания приложений, настоящих программ необходимо использовать другие, базовые языки программирования, в которые операторы языка SQL будут встраиваться.
Базовыми языками программирования могут быть языки C, COBOL, PL/1, Pascal.
Существуют два способа применения SQL в прикладных программах:
Процесс выполнения операторов SQL может быть условно разделен на 5 этапов (см. рис. 12.1).
(рис 12.1) Процесс выполнения операторов SQLСледует отметить, что перечисленные этапы отличаются по числу обращений к БД и по процессорному времени, требуемому для их выполнения. Синтаксический анализ проводится очень быстро, он не требует обращения к системным каталогам БД. Семантический анализ уже требует работы с базой метаданных, то есть с системными каталогами БД, поэтому при выполнении этого этапа происходит обращение к системному каталогу и серьезная работа с ним. Этап, связанный с оптимизаций плана запроса, требует работы не только с системным каталогом, но и со статистической информацией о БД, которая характеризует текущее состояние всех отношений, используемых в запросе, их физическое расположение на страницах и сегментах внешней памяти. В силу указанных причин этап оптимизации наиболее трудоемкий и длительный в процессе выполнения запроса. Однако если не проводить этап оптимизации, то стоимость (время) выполнения неоптимизированного запроса может в несколько раз превысить стоимость оптимизированного запроса. Время, потраченное на
Этапы выполнения операторов SQL одни и те же как в интерактивном режиме, так и внутри приложений. Однако при работе с готовым приложением многие этапы СУБД может выполнить заранее.
При объединении операторов SQL c базовым языком программирования должны соблюдаться следующие принципы:
Операторы манипулирования данными не требуют изменения для их встраивания в программный SQL. Однако оператор поиска ( SELECT ) потребовал изменений.
Стандартный оператор SELECT возвращает набор данных, релевантный сформированным условиям запроса. В SELECT должен создавать структуры данных, которые согласуются с базовыми языками программирования. Во встроенном SQL запросы делятся на 2 типа:
Первый тип запроса — однострочный запрос во встроенном SQL вызвал модификацию оператора SQL, которая выглядит следующим образом:
SELECT [{ALL | DISTINCT}]
<список возвращаемых столбцов>
INTO <список переменных базового языка>
FROM <список исходных таблиц>
[WHERE <условия соединения и поиска>]
Мы видим, что во встроенный SELECT добавился новый для нас раздел, содержащий список переменных базового языка. Именно в эти переменные будет помещен результат однострочного запроса, поэтому список переменных базового языка должен быть согласован как по порядку, так и по типу и размеру данных со списком возвращаемых столбцов. По правилам любого языка программирования все базовые переменные предварительно описаны в прикладной программе. Например, если в нашей БД "Библиотека" существует таблица READERS (Читатели), мы можем получить сведения о конкретном читателе.
CREATE TABLE READERS ( READER_ID Smallint(4) PRIMARY KEY, FIRST_NAME char(30) NOT NULL, LAST_NAME char(30) NOT NULL, ADRES char(50) , HOME_PHON char(12) , WORK_PHON char(12) , BIRTH_DAY date CHECK( DateDiff(year, GetDate(), BIRTH_DAY) >=17 ) );
Для этого опишем базовые переменные. Рассмотрим пример для MS SQL SERVER 7.0, используя язык Transact SQL. При описании локальных переменных в языке Transact SQL используется специальный символ @. Комментарии в Transact SQL заключены в парные символы /* комментарий */.
DECLARE @READER_ID int
DECLARE @FIRS_NAME Char(30), @LAST_NAME Char(30), @ADRES Char(50)
DECLARE @HOME_PHON Char(12),@WORK_PHON Char(12)
/* зададим уникальный номер читательского билета */
SET @READER_ID = 4
/* теперь выполним запрос и поместим полученные сведения в определенные
ранее переменные */
SELECT READERS.FIRST_NAME, READERS.LAST_NAME,
READERS.ADRES, READERS.HOME_PHON,
READERS.WORK_PHON
INTO @FIRS_NAME, @LAST_NAME, @ADRES,
@HOME_PHON,@WORK_PHON
FROM READERS WHERE READERS.READER_ID = @READER_ID
В этом простом примере мы имена переменных сделали такими же, как и имена столбцов таблицы READERS, но это необязательно. Однако транслятор различает эти объекты, именно поэтому в диалекте Transact SQL принято локальные переменные предварять специальным символом @. В примере мы использовали квалифицированные имена полей, имена полей, предваряемые именем таблицы. В нашем случае это тоже необязательно, потому что запрос выбирает данные только из одной таблицы.
В нашем примере базовые переменные играют разную роль. Локальная переменная @READER_ID является входной по отношению к запросу. Ей присвоено значение 4, и в запросе это значение используется для фильтрации данных, поэтому эта переменная используется в условии WHERE.
Остальные базовые переменные играют роль выходных переменных, в них СУБД помещает результат выполнения запроса, помещая в них значения соответствующих полей отношения READERS, извлеченные из БД.
Рассмотрим более сложные многострочные запросы.
Для реализации многострочных запросов вводится новое понятие — понятие курсора или указателя набора записей. Для работы с курсором добавляется несколько новых операторов SQL:
DECLARE CURSOR OPEN дает команду СУБД выполнить описанный запрос, создать виртуальный набор строк, который соответствует заданному запросу. Оператор OPEN устанавливает указатель записей (курсор) перед первой строкой виртуального набора строк результата.FETCH продвигает указатель записей на следующую позицию в виртуальном наборе записей. В большинстве коммерческих СУБД оператор перемещения FETCH реализует более широкие функции перемещения, он позволяет перемещать указатель на произвольную запись, вперед и назад, допускает как абсолютную адресацию, так и CLOSE закрывает курсор и прекращает доступ к виртуальному набору записей. Он фактически ликвидирует связь между курсором и результатом выполнения базового запроса. Однако в коммерческих СУБД оператор CLOSE не всегда означает уничтожение виртуального набора записей. Мы коснемся этого далее, когда будем рассматривать работу с курсором в MS SQL Server 7.0.Стандарт определяет следующий синтаксис оператора определения курсора:
DECLARE <имя_курсора> CURSOR FOR <спецификация_курсора> <спецификация курсора>::= <выражение_запроса SELECT>
Имя курсора — это допустимый идентификатор в базовом языке программирования.
В OPEN значения всех базовых переменных, используемых в качестве входных переменных, связанных с условиями фильтрации значений в базовом запросе, должны быть уже заданы.
Определим курсор, который содержит список всех должников нашей библиотеки. Должниками назовем читателей, которые имеют на руках хотя бы одну книгу, срок сдачи которой уже прошел.
DECLARE Debtor_reader_cursor CURSOR FOR SELECT READERS.FIRST_NAME, READERS.LAST_NAME, READERS.ADRES, READERS.HOME_PHON, READERS.WORK_PHON, BOOKS.TITLE FROM READERS,BOOKS,EXEMPLAR WHERE READERS.READER_ID = EXEMPLAR.READER_ID AND BOOKS.ISBN = EXEMPLARE.ISBN AND EXEMPLAR.DATA_OUT > Getdate() ORDER BY READERS.FIRST_NAME
При определении курсора мы снова использовали функцию Transact SQL Getdate(), которая возвращает значение текущей даты. Таким образом, определенный курсор будет создавать набор строк, содержащих перечень должников, с указанием названий книг, которые они не вернули вовремя в библиотеку.
В соответствии со стандартом
DECLARE <имя_курсора> [INSENSITIVE] [SCROLL] CURSOR
FOR <оператор выбора SELECT>
[FOR {READ ONLY | UPDATE [OF <имя_столбца 1> [,...n]]}]
Параметр INSENSITIVE (нечувствительный) определяет режим создания набора строк, соответствующего определяемому курсору, при котором все изменения в исходных таблицах, произведенные после
СУБД более быстро и экономно может обрабатывать такой курсор, поэтому если для вас действительно важно рассмотреть и обработать состояние БД на некоторый конкретный момент времени, то имеет смысл создать "нечувствительный курсор".
Ключевое слово SCROLL определяет, что допустимы любые режимы перемещения по курсору ( FIRST, LAST, ) в операторе FETCH.
Если не указано ключевое слово SCROLL, то считается доступной только стандартное перемещение вперед: спецификация NEXT в операторе FETCH.
Если указана спецификация READ ONLY (только для чтения), то изменения и обновления исходных таблиц не будут выполняться с использованием данного курсора. Курсор с данной спецификацией может быть самым быстрым в обработке, однако если вы не укажите специально спецификацию READ ONLY, то СУБД будет считать, что вы допускаете операции модификации с базовыми таблицами, и в этом случае для обеспечения целостности БД СУБД будет гораздо медленнее обрабатывать ваши операции с курсором.
При использовании параметра UPDATE [OF <имя столбца 1> [,...<имя столбца n>]] мы задаем перечень столбцов, в которых допустимы изменения в процессе нашей работы с курсором. Такое ограничение упростит и ускорит работу СУБД. Если этот параметр не указан, то предполагается, что допустимы изменения всех столбцов курсора.
Вернемся к нашему примеру. Если мы преследуем цель мгновенного слепка БД, дающего сведения о должниках, то применим все параметры, позволяющие ускорить работу с нашим курсором. Тогда оператор описания курсора будет выглядеть следующим образом:
DECLARE Debtor_reader_cursor INSENSITIVE CURSOR FOR SELECT READERS.FIRST_NAME, READERS.LAST_NAME, READERS.ADRES, READERS.HOME_PHON, READERS.WORK_PHON, BOOKS.TITLE FROM READERS,BOOKS,EXEMPLAR WHERE READERS.READER_ID = EXEMPLAR.READER_ID AND BOOKS.ISBN = EXEMPLARE.ISBN AND EXEMPLAR.DATA_OUT > Getdate() ORDER BY READERS.FIRST_NAME FOR READ ONLY
При описании курсора нет ограничений на вид оператора SELECT, который используется для создания базового набора строк, связанного с курсором. В операторе SELECT могут использоваться группировки и встроенные
Оператор
OPEN <имя_курсора> [USING <список базовых переменных>]
Именно оператор DECLARE … CURSOR. При выполнении оператора OPEN СУБД производит семантическую проверку курсора, то есть выполняет этапы со 2 по 5 в алгоритме выполнения запросов (рис. 12.1), поэтому именно здесь СУБД возвращает коды ошибок прикладной программе, сообщающие ей о результатах выполнения базового запроса. Ошибки могут возникнуть в результате неправильного задания имен полей или имен исходных таблиц или при попытке извлечь данные из таблиц, к которым данный пользователь не имеет доступа.
По стандарту СУБД возвращает код завершения операции в специальной системной переменной SQLCODE. В прикладной программе пользователь может анализировать эту переменную, что необходимо делать после выполнения каждого оператора SQL. При неудачном выполнении операции SQLCODE.
В случае удачного завершения выполнения оператора
Однако надо помнить, что СУБД автоматически закрывает все курсоры в случае завершения транзакции ( COMMIT ) или отката транзакции ( ROLLBACK ). После того как курсор закрыт его можно открыть снова, но при этом соответствующий запрос выполнится заново. Поэтому допустимо, что содержимое первого курсора будет не соответствовать его содержимому при повторном открытии, потому что за это время изменилось состояние БД.
После открытия указатель текущей строки установлен перед первой строкой курсора. Стандартно оператор FETCH перемещает указатель текущей строки на следующую строку и присваивает базовым переменным значение столбцов, соответствующее текущей строке.
Простой оператор FETCH имеет следующий синтаксис:
FETCH <имя_курсора> INTO <список переменных базового языка >
Оператор извлечения очередной строки из курсора будет выглядеть следующим образом:
FETCH Debtor_reader_cursor into
@FIRST_NAME, @LAST_NAME,
@ADRES, @HOME_PHON,
@WORK_PHON, @TITLE
Расширенный оператор FETCH имеет следующий синтаксис:
FETCH [NEXT | PRIOR | FIRST | LAST
ABSOLUTE {n | <имя_переменной>}
|RELATIVE{n|<имя_переменной>}] FROM
<имя_курсора>
INTO <список базовых переменных>
Здесь параметр NEXT задает выбор следующей строки после текущей из базового набора строк, связанного с курсором. Параметр FIRST задает перемещение на первую строку набора, а параметр LAST задает перемещение на последнюю строку набора.
Кроме того, в расширенном операторе перемещения допустимо переместиться сразу на заданную строку, при этом допустима как абсолютная адресация, заданием параметра ABSOLUTE, так и RELATIVE. При
Однако для применения расширенного оператора FETCH в соответствии со стандартом SCROLL. Иногда такие курсоры называют в литературе FETCH: одну, которая соответствует стандарту, и другую, которая расширяет стандарт дополнительными возможностями, предоставляемыми только данной СУБД для работы с курсором.
Если вы предполагаете, что ваша БД может быть перенесена на другую платформу, а это надо всегда предусматривать, то лучше пользоваться стандартными возможностями. В этом случае ваше приложение будет более платформенно-не-зависимым и легче будет его перенести на другую СУБД.
Оператор
CLOSE <имя_курсора>
Оператор
В некоторых коммерческих СУБД кроме оператора
DEALLOCATE <имя_курсора>
Здесь оператор
При выполнении оператора DECLARE. После выполнения этой команды невозможно выполнение команды OPEN для данного курсора.
Курсоры в прикладных программах часто используются для последовательного просмотра данных. Если курсор не связан с операцией группировки, то фактически каждая строка курсора соответствует строго только одной строке исходной таблицы, и в этом случае курсор удобно использовать для оперативной корректировки данных. В стандарте определены операции модификации данных, связанные с курсором. Операция удаления строки, связанной с текущим указателем курсора, имеет следующий синтаксис:
DELETE FROM <имя_таблицы> WHERE CURRENT OF <имя курсора>
Если указанный в операторе курсор открыт и установлен на некоторую строку, и курсор определяет изменяемую таблицу, то текущая строка курсора удаляется, а он позиционируется перед следующей строкой. Таблица, указанная в разделе FROM оператора DELETE, должна быть таблицей, указанной в самом внешнем разделе FROM спецификации курсора.
Если нам необходимо прочитать следующую строку курсора, то надо снова выполнить оператор FETCH NEXT.
Аналогично курсор может быть использован для модификации данных. Синтаксис операции позиционной модификации следующий:
UPDATE <имя_таблицы>
SET <имя_столбца1>= {<значение> | NULL}
[{,<имя_столбца_N>= {<значение> | NULL}}...]
WHERE CURRENT OF <имя_курсора>
Одним оператором позиционного обновления могут быть заменены несколько значений столбцов строки таблицы, соответствующей текущей позиции курсора. После выполнения операции модификации позиция курсора не изменяется.
Для того чтобы можно было применять DELETE ) и модификации ( UPDATE ), курсор должен удовлетворять определенным требованиям. Согласно стандарту SQL1, это следующие требования:
FROM запроса SELECT, связанного с определением курсора ( DECLARE CURSOR ORDER BY. Для того чтобы сохранялось взаимно однозначное соответствие строк курсора и исходной таблицы, курсор не должен идентифицировать упорядоченный набор данных.DISTINCT.GROUP BY или HAVING.Использование курсора для операций обновления значительно усложняет работу с подобным курсором со стороны СУБД, поэтому операции, связанные с позиционной модификацией, выполняются гораздо медленнее, чем операции с курсорами, которые используются только для чтения. Именно поэтому рекомендуется обязательно указывать в операторе определения курсора предложение READ ONLY, если вы не собираетесь использовать данный курсор для операций модификации. По умолчанию, если нет дополнительных указаний, СУБД создает курсор с возможностью модификации.
Курсоры — удобное средство для формирования бизнес-логики приложений, но следует помнить, что если вы открываете курсор с возможностью модификации, то СУБД блокирует все строки базовой таблицы, вошедшие в ваш курсор, и тем самым блокируется работа других пользователей с данной таблицей.
Чтобы свести к минимуму количество требуемых блокировок, при работе интерактивных программ следует придерживаться следующих правил:
COMMIT после каждого запроса и как можно скорее после изменений, сделанных программой.SCROLL ), потому что они требуют блокирования всех строк выборки, связанных с FETCH, что минимизирует блокировки других пользователей, работающих параллельно с вами и использующих те же таблицы.READ ONLY.Однако когда мы рассматривали модели "клиент—сервер", применяемые в БД, то определили, что в развитых моделях серверов баз данных большая часть бизнес-логики клиентского приложения выполняется именно на сервере, а не на клиенте. Для этого используются специальные объекты, которые называются -хранимыми процедурами и хранятся в БД, как таблицы и другие базовые объекты.
В связи с этим фактом курсоры, которые могут быть использованы в приложениях, обычно делятся на курсоры сервера и курсоры клиента. Курсор сервера создается и выполняется на сервере, данные, связанные с ним, не пересылаются на компьютер клиента. Курсоры сервера определяются обычно в хранимых процедурах или триггерах.
Курсоры клиента — это те курсоры, которые определяются в прикладных программах, выполняемых на клиенте. Набор строк, связанный с данным курсором, пересылается на клиент и там обрабатывается. Если с курсором связан большой набор данных, то операция пересылки набора строк, связанных с курсором, может занять значительное время и значительные ресурсы сети и клиентского компьютера.
Конечно, курсоры сервера более экономичны и выполняются быстрее. Поэтому последней рекомендацией, связанной с использованием курсоров, будет рекомендация трансформировать логику работы вашего приложения, чтобы как можно чаще вместо курсоров клиента использовать курсоры сервера.
С точки зрения приложений, работающих с БД, хранимые процедуры (
Хранимые процедуры могут быть активизированы не только пользовательскими приложениями, но и триггерами.
Хранимые процедуры пишутся на специальном встроенном языке программирования, они могут включать любые операторы SQL, а также включают некоторый набор операторов, управляющих ходом выполнения программ, которые во многом схожи с подобными операторами процедурно ориентированных языков программирования. В коммерческих СУБД для написания текстов хранимых процедур используются собственные языки программирования, так, в СУБД Oracle для этого используется язык PL /SQL, а в MS SQL Server и System11 фирмы Sybase используется язык Transact SQL. В последних версиях Oracle объявлено использование языка Java для написания хранимых процедур.
Хранимые процедуры являются объектами БД. Каждая хранимая процедура компилируется при первом выполнении, в процессе компиляции строится оптимальный план выполнения процедуры. Описание процедуры совместно с планом ее выполнения хранится в системных таблицах БД.
Для CREATE PROCEDURE.
По умолчанию выполнить хранимую процедуру может только ее владелец, которым является владелец БД, и создатель хранимой процедуры. Однако владелец хранимой процедуры может делегировать права на ее запуск другим пользователям.
Имя хранимой процедуры является идентификатором в языке программирования, на котором она пишется, и должно удовлетворять всем требованиям, которые предъявляются к идентификаторам в данном языке.
В MS SQL Server хранимая процедура создается оператором:
CREATE PROCEDURE]
<имя_процедуры> [;<версия>]
[{@параметр1 тип_данных} [VARYING]
[= <значение_по_умолчанию>] [OUTPUT]] [, .параметрN…]
[ WITH { RECOMPILE
ENCRYPTION
RECOMPILE, ENCRYPTION}]
[FOR REPLICATION] AS Тело процедуры
Здесь необязательное ключевое слово
Ключевое слово
Ключевое слово ENCRYPTION определяет режим, при котором исходный текст хранимой процедуры не сохраняется в БД. Такой режим применяется для того, чтобы сохранить авторское право на интеллектуальную продукцию, которой и являются хранимые процедуры. Часто такой режим применяется, когда вы ставите готовую базу заказчику и не хотите, чтобы исходные тексты разработанных вами хранимых процедур были бы доступны
хранить отдельно в некотором текстовом файле. И это не самое плохое, но вот в случае восстановления БД после серьезной аварии для перекомпиляции потребуются первоначальные исходные тексты всех хранимых процедур. Поэтому защита вещь хорошая, но она усложняет сопровождение и модификацию хранимых процедур.
Однако кроме имени хранимой процедуры все остальные параметры являются необязательными. Процедуры могут быть процедурами или процедурами-функциями. И эти понятия здесь трактуются традиционно, как в языках программирования высокого уровня. Хранимая процедура-функция возвращает значение, которое присваивается переменной, определяющей имя процедуры. Процедура в явном виде не возвращает значение, но в ней может быть использовано ключевое слово OUTPUT, которое определяет, что данный параметр является выходным.
Рассмотрим несколько примеров простейших хранимых процедур.
/* процедура проверки наличия экземпляров данной книги параметры: @ISBN шифр книги процедура возвращает параметр, равный количеству экземпляров Если возвращается ноль, то это значит, что нет свободных экземпляров данной книги в библиотеке. */ CREATE PROCEDURE COUNT_EX (@ISBN varchar(12)) AS /* определим внутреннюю переменную */ DECLARE @TEK_COUNT int /* выполним соответствующий оператор SELECT Будем считать только экземпляры, которые в настоящий момент находятся не на руках у читателей, а в библиотеке */ select @TEK_COUNT = select count(*) FROM EXEMPLAR WHERE ISBN = @ISBN AND READER_ID Is NULL AND EXIST = True /* 0 - ноль означает, что нет ни одного свободного экземпляра данной книги в библиотеке */ RETURN @TEK_COUNT
Хранимая процедура может быть вызвана несколькими способами. Простейший способ — это использование оператора:
EXEC <имя процедуры> <значение_входного_параметра1> … <имя_переменной_для_выходного параметра1> …
При этом все входные и выходные параметры должны быть заданы обязательно и в том порядке, в котором они определены в процедуре.
Например, если мне надо найти число экземпляров книги "Oracle8. Энциклопедия пользователя", которая имеет ISBN 966-7393-08-09, то текст вызова ранее
/*определили две переменные @Ntek – количество экземпляров данной книги в наличие в библиотеке @ISBN – международный шифр книги */ declare @Ntek int DECLARE @ISBN VARCHAR(14) /* Присвоим значение переменной @ISBN */ Select @ISBN = '966-7393-08-09' /* Присвоим переменной @Ntek результаты выполнения хранимой процедуры COUNT_EX */ EXEC @Ntek = COUNT_EX @ISBN
Если у вас определено несколько версий хранимой процедуры, то при вызове вы можете указать номер конкретной версии для исполнения. Так, например, в версии 2 процедуры COUNT_EX последний оператор исполнения этой процедуры имеет вид:
EXEC @Ntek = COUNT_EX;2 @ISBN
Однако если в процедуре определены значения входных параметров по умолчанию, то при запуске процедуры могут быть указаны значения не всех параметров. В этом случае оператор вызова процедуры может быть записан в следующем виде:
EXEC <имя процедуры> <имя_параметра1>=<значение параметра1> … <имя_параметраN>=<значение параметраN> …
Например, создадим процедуру, которая считает количество книг, изданных конкретным издательством в конкретном году. При создании процедуры зададим для года издания по умолчанию значение текущего года.
CREATE PROCEDURE COUNT_BOOKS (@YEARIZD int = Year(GetDate()), @PUBLICH varchar(20)) /* процедура подсчета количества книг конкретного издательства, изданных в конкретом году параметры: @YEARIZD int год издания @PUBLICH название издательства */ AS DECLARE @TEK_Count int Select @TEK_count = Select COUNT(ISBN) From BOOKS Where YEARIZD = @YEARIZD AND PUBLICH =@PUBLICH /* одновременно с исполнением оператора Select мы присваиваем результаты его работы определенной ранее переменной @TEK_Count */ /* при формировании результата работы нашей процедуры мы должны учесть, что в нашей библиотеке, возможно, нет ни одной книги некоторого издательства для заданного года. Результат выполнения запроса SELECT в этом случае будет иметь неопределенное значение, но анализировать все-таки лучше числовые значения. Поэтому в качестве возвращаемого значения мы используем результаты работы специальной встроенной функции Transact SQL COALESCE (n1,n2,…,nm), которая возвращает первое конкретное, то есть не равное NULL, значение из списка значений n1,n2,…,nm. */ Return COALESCE (@TEK_Count,0)
Теперь вызовем эту процедуру, для этого подготовим переменную, куда можно поместить результаты выполнения процедуры.
declare @N int Exec @N = COUNT_BOOKS @PUBLICH = 'Питер'
В переменной @N мы получим количество книг в нашей библиотеке, изданных издательством "Питер" в текущем году. Мы можем обратиться к этой процедуре и задав все параметры:
Exec @N = COUNT_BOOKS @PUBLICH = 'BHW', @YEARIZD = 1999
Тогда получим количество книг, изданных издательством "BHW" в 1999 году и присутствующих в нашей библиотеке.
Если мы задаем параметры по именам, то нам необязательно задавать их в том порядке, в котором они описаны при создании процедуры.
Каждая хранимая процедура является объектом БД. Она имеет уникальное имя и уникальный внутренний номер в системном каталоге. При изменении текста хранимой процедуры мы должны сначала уничтожить данную процедуру как объект, хранимый в БД, и только после этого записать на ее место новую. Следует отметить, что при
Для того чтобы автоматизировать процесс уничтожения старой процедуры и замены ее на новую, в начале текста хранимой процедуры можно выполнить проверку наличия объекта типа "хранимая процедура" с данным именем в системном каталоге и при наличии описания данного объекта удалить его из системного каталога. В этом случае текст хранимой процедуры предваряется специальным оператором проверки и может иметь, например, следующий вид:
/* проверка существования в системном каталоге объекта с
данным именем и типом, созданного владельцем БД */
if exists (select * from sysobjects where id = object_id('dbo.NEW_BOOKS')
and sysstat 0xf = 4)
/* если объект существует, то сначала его
удалим из системного каталога */
drop procedure dbo.NEW_BOOKS GO
CREATE PROCEDURE NEW_BOOKS
(
@ISBN varchar(12),
@TITL varchar(255),
@AUTOR varchar(30),
@COAUTOR varchar(30),
@YEARIZD int,
@PAGES INT,
@NUM_EXEMPL INT
)
/* процедура ввода новой книги с указанием
количества экземпляров данной книги параметры
@ISBN varchar(12) шифр книги
@TITL varchar(255) название
@AUTOR varchar(30) автор
@COAUTOR varchar(30) соавтор
@YEARIZD int год издания
@PAGES INT количество страниц
@NUM_EXEMPL INT количество экземпляров
*/
AS
/*опишем переменную, в которой будет храниться количество
оставшихся не оприходованных экземпляров книги, т.е. таких,
которым еще не заданы инвентарные номера */
DECLARE @TEK int
/ *вводим данные о книге в таблицу BOOKS */
INSERT INTO BOOKS
VALUES(@ISBN,@TITL,@AUTOR,
@COAUTOR,@YEARIZD,@PAGES)
/* назначение значения текущего счетчика осташихся
к вводу экземпляров*/
SELECT @TEK = @NUM_EXEMPL
/* организуем цикл для ввода новых экземпляров данной книги */
WHILE @TEK>0
/* пока количество оставшихся экземпляров больше нуля */
BEGIN
/* так как для инвентарного номера экземпляра книги мы задали свойство
IDENTITY, то нам не надо вводить инвентарный номер, СУБД сама автоматически
вычислит его, добавив единицу к предыдущему, введет при выполнении оператора
ввода INSERT. Поле, определяющее присутствие экземпляра в библиотеке (EXIST) –
логическое поле, мы введем туда значение TRUE, которое соответствует присутствию
экземпляра книги в библиотеке.
Даты взятия и возврата мы можем не заполнять, тогда по умолчанию СУБД
подставит туда значение, соответствующее 1 января 1900 года, если мы не хотим
хранить такие бессмысленные данные, то можем ввести для обоих полей дата время,
значения текущей даты. */
insert into EXEMPLAR (ISBN,DATA_IN,DATA_OUT,EXIST)
VALUES (@ISBN,GetDate(),GetDate()),TRUE)
/* изменение текущего значения счетчика количества оставшихся экземпляров */
SELECT @TEK = @TEK - 1
End
/* конец цикла ввода данных о экземпляре книги*/
GO
Если мы не использовали инкрементное поле в качестве инвентарного номера экземпляра, то мы могли бы сами назначать инвентарный номер, увеличивая на единицу номер последнего хранимого в библиотеке экземпляра книги. Можно было бы попробовать просто сосчитать количество существующих экземпляров в библиотеке, но мы могли удалить некоторые, и тогда номер нового экземпляра может быть уже использован, и мы не сможем ввести данные, система не позволит нам нарушить уникальность первичного ключа.
Текст процедуры в этом случае будет иметь вид:
/* проверка существования в системном каталоге объекта
с данным именем и типом, созданного владельцем БД */
if exists (select * from sysobjects
where id = object_id('dbo.NEW_BOOKS')
and sysstat 0xf = 4)
/* если объект существует, то сначала
его удалим из системного каталога */
drop procedure dbo.NEW_BOOKS
CREATE PROCEDURE NEW_BOOKS
( @ISBN varchar(12),
@TITL varchar(255),
@AUTOR varchar(30),
@COAUTOR varchar(30),
@YEARIZD int,
@PAGES INT,
@NUM_EXEMPL INT)
/* процедура ввода новой книги с указанием количества
экземпляров данной книги параметры
@ISBN varchar(12) шифр книги
@TITL varchar(255) название
@AUTOR varchar(30) автор
@COAUTOR varchar(30) соавтор
@YEARIZD int год издания
@PAGES INT количество страниц
@NUM_EXEMPL INT количество экземпляров */
AS
DECLARE @TEK int declare @INV int
INSERT INTO BOOKS
VALUES(@ISBN,@TITL,@AUTOR,@COAUTOR,@YEARIZD,@PAGES)
/* назначение значения текущего счетчика осташихся к вводу экземпляров*/
SELECT @TEK = @NUM_EXEMPL
/* определение максимального значения инвентарного номера в библиотеке */
SELECT @INV = SELECT MAX( ID_EXEMPLAR) FROM EXEMPLAR
/* организуем цикл для ввода новых экземпляров данной книги */
WHILE @TEK>0
/* пока количество оставшихся экземпляров больше нуля */
BEGIN
insert into EXEMPLAR (ID_EXEMPLAR,ISBN,DATA_IN,DATA_OUT,EXIST)
VALUES (@INV,@ISBN,GETDATE(),GetDate(), TRUE)
/* изменение текущих значений счетчика и инвентарного номера */
SELECT @TEK = @TEK - 1
SELECT @INV = @INV + 1
End
/* конец цикла ввода данных о экземпляре книги*/
GO
Хранимые процедуры могут вызывать одна другую. Создадим хранимую процедуру, которая возвращает номер читательского билета для конкретного читателя.
if exists (select * from sysobjects
where id = object_id('dbo. CK_READER')
and sysstat 0xf = 4)
/* если объект существует, то сначала его удалим из системного каталога */
drop procedure dbo.CK_READER
/* Процедура возвращает номер читательского билета,
если читатель есть и 0 в противном случае.
В качестве параметров передаем фамилию и дату рождения */
CREATE PROCEDURE CK_READER
( @FIRST_NAME varchar(30),
@BIRTH_DAY varchar(12))
AS
/*опишем переменную, в которой будет храниться
номер читательского билета*/
DECLARE @NUM_READER INT
/* определение наличия читателя */
select @NUM_READER = select NUM_READER from READERS
WHERE FIRST_NAME = @ FIRST_NAME AND
AND convert(varchar(8),BIRTH_DAY,4)=@BIRTH_DAY
RETURN COALESCE(@NUM_READER,0)
Мы здесь использовали функцию преобразования типа данных dataTime в тип данных varchar(8). Это было необходимо сделать для @BIRTH_DAY имеет символьный тип ( varchar ), а поле базы данных BIRTH_DAY имеет тип SmallDateTime.
Хранимые процедуры допускают наличие нескольких выходных параметров. Для этого каждый выходной параметр должен после задания своего типа данных иметь дополнительное ключевое слово OUTPUT. Рассмотрим пример хранимой процедуры с несколькими выходными параметрами.
Создадим процедуру ввода нового читателя, при этом внутри процедуры выполним проверку наличия в нашей картотеке данного читателя, чтобы не назначать ему новый номер читательского билета. При этом выходными параметрами процедуры будут номер читательского билета, признак того, был ли ранее записан читатель с данными характеристиками в нашей библиотеке, а если он был записан, то сколько книг за ним числится.
/* проверка наличия данной процедуры в нашей БД*/
if exists (select * from sysobjects
where id = object_id(N'[dbo].[NEW_READER]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NEW_READER]
GO
/* процедура проверки существования читателя с
заданными значениями вводимых параметров
Процедура возвращает новый номер читательского билета,
если такого читателя не было сообщает старый номер
и количество книг, которое должен читатель в противном случае */
CREATE PROCEDURE NEW_READER
( @NAME_READER varchar(30),
@ADRES varchar(40),
@HOOM_PHONE char(9),
@WORK_PHONE char(9),
@BIRTH_DAY varchar(8),
@NUM_READER int OUTPUT,
/* выходной параметр, определяющий номер читательского билета*/
@Y_N int OUTPUT,
/* выходной параметр, определяющий был ли читатель ранее записан в библиотеку*/
@COUNT_BOOKS int OUTPUT
/* выходной параметр, определяющий количество книг, которое числится за читателем*/
) AS
/* переменная, в которой будет храниться номер читательского билета,
если читатель уже был записан в библиотеку */
DECLARE @N_R int
/* определение наличия читателя */
EXEC @N_R = CK_READER @NAME_READER,@BIRTH_DAY
IF @N_R= 0 Or @N_R Is Null
/* если читатель с заданными характеристиками не найден,
т. е. переменной @N_R присвоено значение нуль или ее значение неопределено,
перейдем к назначению для нового читателя нового номера читательского билета */
BEGIN
/* так как мы номер читательского билета определили как инкрементное поле,
то в операторе ввода мы его не указываем система сама назначит новому читателю
очередной номер */
INSERT INTO READER(NAME_READER,ADRES,HOOM_PHONE,
WORK_PHONE, BIRTH_DAY)
VALUES (@NAME_READER,@ADRES,@HOOM_PHONE,
@WORK_PHONE,Convert(smalldatetime, @BIRTH_DAY,4) )
/* в операторе INSERT мы должны преобразовать символьную переменную @BIRTH_DAY
в тип данных smalldatetime, который определен для поля дата рождения BIRTH_DAY.
Это преобразование мы сделаем с помощью встроенной функции Transact SQL Convert */
/* теперь определим назначенный номер читальского билета */
select @NUM_READER = NUM_READER FROM READER
WHERE NAME_READER = @NAME_READER
AND convert(varchar(8),BIRTH_DAY,4)=@BIRTH_DAY
/* здесь мы снова используем функцию преобразования типа, но в этом случае нам
необходимо преобразовать поле BIRTH_DAY из типа smalldatetime к типу varchar(8),
в котором задан входной параметр @BIRTH_DAY */
Select @Y_N =0
/* присваиваем выходному параметру @Y_N значение 0 (ноль), что соответствует тому,
что данный читатель ранее в нашей библиотеке не был записан */
Select @COUNT_BOOKS = 0
/* присваиваем выходному параметру, хранящему количество книг,
числящихся за читателем значение ноль */
Return 1
END
else
/* если значение переменной @N_R не равно нулю, то читатель с заданными характеристиками
был ранее записан в нашей библиотеке */
BEGIN
/* определение количества книг у читателя с найденным номером читательского билета */
select @COUNT_BOOKS = COUNT(INV_NUMBER) FROM EXEMPLAR WHERE NUM_READER = @N_R
select @Count_books = COALESCE( @COUNT_BOOKS,0)
/* присваиваем выходному параметру @COUNT_BOOKS значение, равное количеству книг,
которые числятся за нашим читателем, если в предыдущем запросе @COUNT_BOOKS было
присвоено неопределенное значение, то мы заменим его на ноль, используя для этого
встроенную функцию COALESCE(@COUNT_BOOKS,0), которая возвращает первое определенное
значение из списка значений, заданных в качестве ее параметров */
Select @Y_N = 1
/* присваиваем выходному параметру @Y_N значение 1, что соответствует тому, что данный
читатель ранее в нашей библиотеке был записан */
Select @NUM_READER = @N_R
/* присваиваем выходному параметру @NUM_READER определенный ранее номер читательского билета */
return 0
end
Теперь посмотрим, как работает наша новая процедура, для этого в режиме интерактивного выполнения запросов (то есть в QueryAnalyzer MS SQL Server 7.0) запишем следующую последовательность команд:
-- пример использования выходных параметров при вызове процедуры -- new reader -- зададим необходимые нам переменные Declare @K int, @N int, @B int exec NEW_READER 'Пушкин В.В.','Лиговский 22-90', '333-55-99','444-66-88','01.06.83',@NUM_READER =@K OUTPUT, @Y_N = @N OUTPUT,@COUNT_BOOKS = @B OUTPUT -- теперь выведем результаты работы нашей процедуры используя ранее -- определенные нами переменные Select 'номер билета',@K,'да-нет',@N,'кол-во книг',@B
Мы получим результат:
Номер билета да-нет кол-во книг 18 0 0
Если же мы снова запустим нашу процедуру с теми же параметрами, то есть повторим выполнение подготовленных выше операторов, то получим уже иной ответ:
Номер билета да-нет кол-во книг 18 1 0
и это означает, что господин Пушкин В. В. уже записан в нашей библиотеке, но он не успел взять ни одной книги, поэтому за ним числится 0 (ноль) книг.
Теперь обратимся к оценке эффективности применения хранимых процедур.
Если рассмотреть этапы выполнения одинакового текста части приложения, содержащего SQL-операторы, самостоятельно на клиенте и в качестве хранимой процедуры, то можно отметить, что на клиенте выполняются все 5 этапов выполнения SQL-операторов, а хранимая процедура может храниться в БД в уже скомпилированном виде, и ее исполнение займет гораздо меньше времени (см. рис. 12.2).
Кроме того, хранимые процедуры, как уже упоминалось, могут быть использованы несколькими приложениями, а встроенные операторы SQL должны быть включены в каждое приложение повторно.
(рис 12.2) Процесс выполнения операторов SQL на клиенте и процесс выполнения хранимой процедурыХранимые процедуры также играют ключевую роль в повышении быстродействия при работе в сети с архитектурой "клиент—сервер".
На рис. 12.3 показан пример выполнения последовательности операторов SQL на клиенте, а на рис. 12.4 показан пример выполнения той же последовательности операторов SQL, оформленных в виде хранимой процедуры. В этом случае клиент обращается к серверу только для выполнения команды запуска хранимой процедуры. Сама хранимая процедура выполняется на сервере. Объем пересылаемой по сети информации резко сокращается во втором случае.
(рис 12.3) Сетевой трафик при выполнении встроенных SQL-операторов
(рис 12.4) Сетевой трафик при выполнении хранимой процедуры на сервереФактически триггер — это специальный вид хранимой процедуры, которую SQL Server вызывает при выполнении операций модификации соответствующих таблиц. Триггер автоматически активизируется при выполнении операции, с которой он связан. Триггеры связываются с одной или несколькими операциями модификации над одной таблицей.
В разных коммерческих СУБД рассматриваются разные триггеры. Так, в MS SQL Server триггеры определены только как постфильтры, то есть такие триггеры, которые выполняются после свершения события.
В СУБД Oracle определены два
Триггеры могут быть эффективно использованы для поддержки
В стандарте SQL1 ни хранимые процедуры, ни триггеры были не определены. Но в добавлении к стандарту
Для
CREATE TRIGGER <имя_триггера>
ON <имя_таблицы>
FOR {[INSERT][, UPDATE] [, DELETE] }
[WITH ENCRIPTING]
AS
SQL-операторы (Тело триггера)
Имя триггера является идентификатором во встроенном языке программирования СУБД и должно удовлетворять соответствующим требованиям.
В параметре FOR задается одна или несколько операций модификации, которые запускают данный триггер.
Параметр WITH ENCRIPTING имеет тот же смысл, что и для хранимых процедур, он скрывает исходный текст тела триггера.
Существует несколько правил, которые ограничивают набор операторов, которые могут быть использованы в теле триггера.
Так, в большинстве СУБД действуют следующие ограничения:
DROP для всех типов базовых объектов БД.ALTER TABLE, ALTER DATABASE.GRAND или REVOKE VIEW ) .Рассмотрим пример триггера, который срабатывает при удалении экземпляра некоторой книги, например, в случае утери этой книги читателем. Что же может делать этот триггер? А он может выполнять следующую проверку: проверять, остался ли еще хоть один экземпляр данной книги в библиотеке, и если это был последний экземпляр книги в библиотеке, то резонно удалить описание книги из предметного каталога, чтобы наши читатели зря не пытались заказать эту книгу.
Текст этого триггера на языке Transact SQL приведен ниже:
/* Проверка существования данного триггера в системном каталоге */
if exists (select * from sysobjects
where id = object_id('dbo.DEL_EXEMP')
and sysstat 0xf = 8)
drop trigger dbo.DEL_EXEMP
GO
CREATE TRIGGER DEL_EXEMP ON dbo.EXEMPLAR
/* мы создаем триггер для таблицы EXEMPLAR */
FOR DELETE
/* только для операции удаления */
AS
/* опишем локальные переменные */
DECLARE @Ntek int /* количество оставшихся экземпляров удаленной книги */
DECLARE @DEL_EX VARCHAR(12) /* шифр удаленного экземпляра*/
Begin
/* по временной системной таблице, содержащей удаленные записи,
определяем шифр книги, соответствующей последнему удаленномуэкземпляру */
SELECT @DEL_EX = ISBN From deleted
/* вызовем хранимую процедуру, которая определит количество экземпляров
книги с заданным шифром */
EXEC @Ntek = COUNT_EX @DEL_EX
/* Если больше нет экземпляров данной книги, то мы удаляем запись о книге
из таблицы BOOKS */
IF @Ntek = 0 DELETE from BOOKS WHERE BOOKS.ISBN = @DEL_EX
END
GO
Возможности операторов
Сформированный таким образом текст SQL-оператора поступает в СУБД, которая должна его скомпилировать и выполнить "на лету", в процессе работы приложения. Если мы снова вернемся к этапам выполнения SQL-операторов, то первые четыре действия, связанные с синтаксическим анализом, семантическим анализом, построением и оптимизацией плана выполнения запроса, выполняются на этапе компиляции. В момент исполнения этого оператора СУБД просто изымает хранимый план выполнения этого оператора и исполняет его.
В случае
Конечно, динамический SQL гораздо менее эффективен в смысле производительности, по сравнению со статическим SQL. Поэтому во всех случаях, когда это возможно, необходимо избегать
Наиболее простой формой EXECUTE IMMEDIATE. Этот оператор имеет следующий синтаксис:
EXECUTE IMMEDIATE <имя_базовой переменной>
Базовая переменная содержит текст SQL оператора.
Однако оператор непосредственного выполнения пригоден для выполнения операций, которые не возвращают результаты. Так же как в
Прежде всего было предложено разделить выполнение SQL-оператора в динамическом SQL на два отдельных этапа. Первый этап называется подготовительным, он фактически включает 4 первых этапа выполнения SQL-операторов, рассмотренные нами ранее: синтаксический и семантический анализ, построение и оптимизация плана выполнения оператора.
Этот этап выполняется оператором PREPARE, синтаксис которого приведен ниже:
PREPARE <имя_оператора> FROM <имя_базовой переменной>
<имя_оператора> - это идентификатор базового языка.
Далее на втором этапе этот определенный на первом этапе оператор может быть выполнен операцией EXECUTE, которая имеет синтаксис:
EXECUTE <имя_оператора>
USING {<список базовых переменных>
DESCRIPTOR <имя_дескриптора>}
Здесь DESCRIPTOR — это некоторая структура, которая описывается на клиенте, но создается и управляется сервером. Дескриптор представляет совокупность элементов данных, принадлежащих СУБД. Программное обеспечение СУБД должно содержать и поддерживать набор операций над дескрипторами. Эта структура была введена в стандарт
В стандарт
ALLOCATE DESCRIPTOR <имя_дескриптора> [WITH MAX <число_элементов>] — оператор связывает имя дескриптора с числом его базовых элементов и обеспечивает выделение памяти под данный дескриптор.DEALLOCATE DESCRIPTOR <имя_дескриптора> SET DESCRIPTOR {COUNT = <имя_базовой переменной> | VALUE <номер элемента> {<имя_ элемента>= <имя_базовой переменной>[,..]}} — оператор занесения в дескриптор описания передаваемых параметров. Описания передаются СУБД, которая их обрабатывает, внося соответствующие изменения в область данных, отведенную под дескриптор.GET DESCRIPTOR { <имя_базовой переменной> = COUNT | VALUE <номер элемента> {<имя_базовой переменной>=<имя_элемента>[,..]} — оператор получения информации из дескрипторов после выполнения запроса.DESCRIBE [ INPUT | OUTPUT] <имя_оператора> USING SQL DESCRIPTOR <имя_дескрипто-ра> — оператор, позволяющий получить описания таблиц результатов запросов ( DESCRIBE OUTPUT ) или входных параметров ( DESCRIBE INPUT ).OPEN <имя_курсора> [USING <список базовых переменных> | USING SQL DESCRIPTOR <имя_дескриптора>] — динамический оператор FETCH <имя_курсора> [USING <список базовых переменных> | USING SQL DESCRIPTOR <имя_дескриптора>] — динамический оператор перемещения по курсору.DEALLOCATE PREPARE <имя_оператора> Следует отметить, что в настоящий момент большинство СУБД реализуют динамический SQL несколько отличными от стандарта способами, однако в ближайшем будущем все поставщики вынуждены будут перейти к стандарту, так как именно это привлекает пользователей и делает переносимым разрабатываемое прикладное программное обеспечение.
Язык SQL, как мы уже видели в лекции 5, предназначен для организации доступа к базам данных. При этом предполагается, что доступ к БД может быть осуществлен в двух режимах: в интерактивном режиме и в режиме выполнения прикладных программ (приложений).
Эта двойственность SQL создает ряд преимуществ:
SQL действительно является языком по работе с базами данных, но в явном виде он не является языком программирования. В нем отсутствуют традиционные операторы, организующие циклы, позволяющие объявить и использовать внутренние переменные, организовать анализ некоторых условий и возможность изменения хода программы в зависимости от выполненного условия. В общем случае можно назвать SQL подъязыком, который служит исключительно для управления базами данных. Для создания приложений, настоящих программ необходимо использовать другие, базовые языки программирования, в которые операторы языка SQL будут встраиваться.
Базовыми языками программирования могут быть языки C, COBOL, PL/1, Pascal.
Существуют два способа применения SQL в прикладных программах:
Процесс выполнения операторов SQL может быть условно разделен на 5 этапов (см. рис. 12.1).
(рис 12.1) Процесс выполнения операторов SQLСледует отметить, что перечисленные этапы отличаются по числу обращений к БД и по процессорному времени, требуемому для их выполнения. Синтаксический анализ проводится очень быстро, он не требует обращения к системным каталогам БД. Семантический анализ уже требует работы с базой метаданных, то есть с системными каталогами БД, поэтому при выполнении этого этапа происходит обращение к системному каталогу и серьезная работа с ним. Этап, связанный с оптимизаций плана запроса, требует работы не только с системным каталогом, но и со статистической информацией о БД, которая характеризует текущее состояние всех отношений, используемых в запросе, их физическое расположение на страницах и сегментах внешней памяти. В силу указанных причин этап оптимизации наиболее трудоемкий и длительный в процессе выполнения запроса. Однако если не проводить этап оптимизации, то стоимость (время) выполнения неоптимизированного запроса может в несколько раз превысить стоимость оптимизированного запроса. Время, потраченное на
Этапы выполнения операторов SQL одни и те же как в интерактивном режиме, так и внутри приложений. Однако при работе с готовым приложением многие этапы СУБД может выполнить заранее.
При объединении операторов SQL c базовым языком программирования должны соблюдаться следующие принципы:
Операторы манипулирования данными не требуют изменения для их встраивания в программный SQL. Однако оператор поиска ( SELECT ) потребовал изменений.
Стандартный оператор SELECT возвращает набор данных, релевантный сформированным условиям запроса. В SELECT должен создавать структуры данных, которые согласуются с базовыми языками программирования. Во встроенном SQL запросы делятся на 2 типа:
Первый тип запроса — однострочный запрос во встроенном SQL вызвал модификацию оператора SQL, которая выглядит следующим образом:
SELECT [{ALL | DISTINCT}]
<список возвращаемых столбцов>
INTO <список переменных базового языка>
FROM <список исходных таблиц>
[WHERE <условия соединения и поиска>]
Мы видим, что во встроенный SELECT добавился новый для нас раздел, содержащий список переменных базового языка. Именно в эти переменные будет помещен результат однострочного запроса, поэтому список переменных базового языка должен быть согласован как по порядку, так и по типу и размеру данных со списком возвращаемых столбцов. По правилам любого языка программирования все базовые переменные предварительно описаны в прикладной программе. Например, если в нашей БД "Библиотека" существует таблица READERS (Читатели), мы можем получить сведения о конкретном читателе.
CREATE TABLE READERS ( READER_ID Smallint(4) PRIMARY KEY, FIRST_NAME char(30) NOT NULL, LAST_NAME char(30) NOT NULL, ADRES char(50) , HOME_PHON char(12) , WORK_PHON char(12) , BIRTH_DAY date CHECK( DateDiff(year, GetDate(), BIRTH_DAY) >=17 ) );
Для этого опишем базовые переменные. Рассмотрим пример для MS SQL SERVER 7.0, используя язык Transact SQL. При описании локальных переменных в языке Transact SQL используется специальный символ @. Комментарии в Transact SQL заключены в парные символы /* комментарий */.
DECLARE @READER_ID int
DECLARE @FIRS_NAME Char(30), @LAST_NAME Char(30), @ADRES Char(50)
DECLARE @HOME_PHON Char(12),@WORK_PHON Char(12)
/* зададим уникальный номер читательского билета */
SET @READER_ID = 4
/* теперь выполним запрос и поместим полученные сведения в определенные
ранее переменные */
SELECT READERS.FIRST_NAME, READERS.LAST_NAME,
READERS.ADRES, READERS.HOME_PHON,
READERS.WORK_PHON
INTO @FIRS_NAME, @LAST_NAME, @ADRES,
@HOME_PHON,@WORK_PHON
FROM READERS WHERE READERS.READER_ID = @READER_ID
В этом простом примере мы имена переменных сделали такими же, как и имена столбцов таблицы READERS, но это необязательно. Однако транслятор различает эти объекты, именно поэтому в диалекте Transact SQL принято локальные переменные предварять специальным символом @. В примере мы использовали квалифицированные имена полей, имена полей, предваряемые именем таблицы. В нашем случае это тоже необязательно, потому что запрос выбирает данные только из одной таблицы.
В нашем примере базовые переменные играют разную роль. Локальная переменная @READER_ID является входной по отношению к запросу. Ей присвоено значение 4, и в запросе это значение используется для фильтрации данных, поэтому эта переменная используется в условии WHERE.
Остальные базовые переменные играют роль выходных переменных, в них СУБД помещает результат выполнения запроса, помещая в них значения соответствующих полей отношения READERS, извлеченные из БД.
Рассмотрим более сложные многострочные запросы.
Для реализации многострочных запросов вводится новое понятие — понятие курсора или указателя набора записей. Для работы с курсором добавляется несколько новых операторов SQL:
DECLARE CURSOR OPEN дает команду СУБД выполнить описанный запрос, создать виртуальный набор строк, который соответствует заданному запросу. Оператор OPEN устанавливает указатель записей (курсор) перед первой строкой виртуального набора строк результата.FETCH продвигает указатель записей на следующую позицию в виртуальном наборе записей. В большинстве коммерческих СУБД оператор перемещения FETCH реализует более широкие функции перемещения, он позволяет перемещать указатель на произвольную запись, вперед и назад, допускает как абсолютную адресацию, так и CLOSE закрывает курсор и прекращает доступ к виртуальному набору записей. Он фактически ликвидирует связь между курсором и результатом выполнения базового запроса. Однако в коммерческих СУБД оператор CLOSE не всегда означает уничтожение виртуального набора записей. Мы коснемся этого далее, когда будем рассматривать работу с курсором в MS SQL Server 7.0.Стандарт определяет следующий синтаксис оператора определения курсора:
DECLARE <имя_курсора> CURSOR FOR <спецификация_курсора> <спецификация курсора>::= <выражение_запроса SELECT>
Имя курсора — это допустимый идентификатор в базовом языке программирования.
В OPEN значения всех базовых переменных, используемых в качестве входных переменных, связанных с условиями фильтрации значений в базовом запросе, должны быть уже заданы.
Определим курсор, который содержит список всех должников нашей библиотеки. Должниками назовем читателей, которые имеют на руках хотя бы одну книгу, срок сдачи которой уже прошел.
DECLARE Debtor_reader_cursor CURSOR FOR SELECT READERS.FIRST_NAME, READERS.LAST_NAME, READERS.ADRES, READERS.HOME_PHON, READERS.WORK_PHON, BOOKS.TITLE FROM READERS,BOOKS,EXEMPLAR WHERE READERS.READER_ID = EXEMPLAR.READER_ID AND BOOKS.ISBN = EXEMPLARE.ISBN AND EXEMPLAR.DATA_OUT > Getdate() ORDER BY READERS.FIRST_NAME
При определении курсора мы снова использовали функцию Transact SQL Getdate(), которая возвращает значение текущей даты. Таким образом, определенный курсор будет создавать набор строк, содержащих перечень должников, с указанием названий книг, которые они не вернули вовремя в библиотеку.
В соответствии со стандартом
DECLARE <имя_курсора> [INSENSITIVE] [SCROLL] CURSOR
FOR <оператор выбора SELECT>
[FOR {READ ONLY | UPDATE [OF <имя_столбца 1> [,...n]]}]
Параметр INSENSITIVE (нечувствительный) определяет режим создания набора строк, соответствующего определяемому курсору, при котором все изменения в исходных таблицах, произведенные после
СУБД более быстро и экономно может обрабатывать такой курсор, поэтому если для вас действительно важно рассмотреть и обработать состояние БД на некоторый конкретный момент времени, то имеет смысл создать "нечувствительный курсор".
Ключевое слово SCROLL определяет, что допустимы любые режимы перемещения по курсору ( FIRST, LAST, ) в операторе FETCH.
Если не указано ключевое слово SCROLL, то считается доступной только стандартное перемещение вперед: спецификация NEXT в операторе FETCH.
Если указана спецификация READ ONLY (только для чтения), то изменения и обновления исходных таблиц не будут выполняться с использованием данного курсора. Курсор с данной спецификацией может быть самым быстрым в обработке, однако если вы не укажите специально спецификацию READ ONLY, то СУБД будет считать, что вы допускаете операции модификации с базовыми таблицами, и в этом случае для обеспечения целостности БД СУБД будет гораздо медленнее обрабатывать ваши операции с курсором.
При использовании параметра UPDATE [OF <имя столбца 1> [,...<имя столбца n>]] мы задаем перечень столбцов, в которых допустимы изменения в процессе нашей работы с курсором. Такое ограничение упростит и ускорит работу СУБД. Если этот параметр не указан, то предполагается, что допустимы изменения всех столбцов курсора.
Вернемся к нашему примеру. Если мы преследуем цель мгновенного слепка БД, дающего сведения о должниках, то применим все параметры, позволяющие ускорить работу с нашим курсором. Тогда оператор описания курсора будет выглядеть следующим образом:
DECLARE Debtor_reader_cursor INSENSITIVE CURSOR FOR SELECT READERS.FIRST_NAME, READERS.LAST_NAME, READERS.ADRES, READERS.HOME_PHON, READERS.WORK_PHON, BOOKS.TITLE FROM READERS,BOOKS,EXEMPLAR WHERE READERS.READER_ID = EXEMPLAR.READER_ID AND BOOKS.ISBN = EXEMPLARE.ISBN AND EXEMPLAR.DATA_OUT > Getdate() ORDER BY READERS.FIRST_NAME FOR READ ONLY
При описании курсора нет ограничений на вид оператора SELECT, который используется для создания базового набора строк, связанного с курсором. В операторе SELECT могут использоваться группировки и встроенные
Оператор
OPEN <имя_курсора> [USING <список базовых переменных>]
Именно оператор DECLARE … CURSOR. При выполнении оператора OPEN СУБД производит семантическую проверку курсора, то есть выполняет этапы со 2 по 5 в алгоритме выполнения запросов (рис. 12.1), поэтому именно здесь СУБД возвращает коды ошибок прикладной программе, сообщающие ей о результатах выполнения базового запроса. Ошибки могут возникнуть в результате неправильного задания имен полей или имен исходных таблиц или при попытке извлечь данные из таблиц, к которым данный пользователь не имеет доступа.
По стандарту СУБД возвращает код завершения операции в специальной системной переменной SQLCODE. В прикладной программе пользователь может анализировать эту переменную, что необходимо делать после выполнения каждого оператора SQL. При неудачном выполнении операции SQLCODE.
В случае удачного завершения выполнения оператора
Однако надо помнить, что СУБД автоматически закрывает все курсоры в случае завершения транзакции ( COMMIT ) или отката транзакции ( ROLLBACK ). После того как курсор закрыт его можно открыть снова, но при этом соответствующий запрос выполнится заново. Поэтому допустимо, что содержимое первого курсора будет не соответствовать его содержимому при повторном открытии, потому что за это время изменилось состояние БД.
После открытия указатель текущей строки установлен перед первой строкой курсора. Стандартно оператор FETCH перемещает указатель текущей строки на следующую строку и присваивает базовым переменным значение столбцов, соответствующее текущей строке.
Простой оператор FETCH имеет следующий синтаксис:
FETCH <имя_курсора> INTO <список переменных базового языка >
Оператор извлечения очередной строки из курсора будет выглядеть следующим образом:
FETCH Debtor_reader_cursor into
@FIRST_NAME, @LAST_NAME,
@ADRES, @HOME_PHON,
@WORK_PHON, @TITLE
Расширенный оператор FETCH имеет следующий синтаксис:
FETCH [NEXT | PRIOR | FIRST | LAST
ABSOLUTE {n | <имя_переменной>}
|RELATIVE{n|<имя_переменной>}] FROM
<имя_курсора>
INTO <список базовых переменных>
Здесь параметр NEXT задает выбор следующей строки после текущей из базового набора строк, связанного с курсором. Параметр FIRST задает перемещение на первую строку набора, а параметр LAST задает перемещение на последнюю строку набора.
Кроме того, в расширенном операторе перемещения допустимо переместиться сразу на заданную строку, при этом допустима как абсолютная адресация, заданием параметра ABSOLUTE, так и RELATIVE. При
Однако для применения расширенного оператора FETCH в соответствии со стандартом SCROLL. Иногда такие курсоры называют в литературе FETCH: одну, которая соответствует стандарту, и другую, которая расширяет стандарт дополнительными возможностями, предоставляемыми только данной СУБД для работы с курсором.
Если вы предполагаете, что ваша БД может быть перенесена на другую платформу, а это надо всегда предусматривать, то лучше пользоваться стандартными возможностями. В этом случае ваше приложение будет более платформенно-не-зависимым и легче будет его перенести на другую СУБД.
Оператор
CLOSE <имя_курсора>
Оператор
В некоторых коммерческих СУБД кроме оператора
DEALLOCATE <имя_курсора>
Здесь оператор
При выполнении оператора DECLARE. После выполнения этой команды невозможно выполнение команды OPEN для данного курсора.
Курсоры в прикладных программах часто используются для последовательного просмотра данных. Если курсор не связан с операцией группировки, то фактически каждая строка курсора соответствует строго только одной строке исходной таблицы, и в этом случае курсор удобно использовать для оперативной корректировки данных. В стандарте определены операции модификации данных, связанные с курсором. Операция удаления строки, связанной с текущим указателем курсора, имеет следующий синтаксис:
DELETE FROM <имя_таблицы> WHERE CURRENT OF <имя курсора>
Если указанный в операторе курсор открыт и установлен на некоторую строку, и курсор определяет изменяемую таблицу, то текущая строка курсора удаляется, а он позиционируется перед следующей строкой. Таблица, указанная в разделе FROM оператора DELETE, должна быть таблицей, указанной в самом внешнем разделе FROM спецификации курсора.
Если нам необходимо прочитать следующую строку курсора, то надо снова выполнить оператор FETCH NEXT.
Аналогично курсор может быть использован для модификации данных. Синтаксис операции позиционной модификации следующий:
UPDATE <имя_таблицы>
SET <имя_столбца1>= {<значение> | NULL}
[{,<имя_столбца_N>= {<значение> | NULL}}...]
WHERE CURRENT OF <имя_курсора>
Одним оператором позиционного обновления могут быть заменены несколько значений столбцов строки таблицы, соответствующей текущей позиции курсора. После выполнения операции модификации позиция курсора не изменяется.
Для того чтобы можно было применять DELETE ) и модификации ( UPDATE ), курсор должен удовлетворять определенным требованиям. Согласно стандарту SQL1, это следующие требования:
FROM запроса SELECT, связанного с определением курсора ( DECLARE CURSOR ORDER BY. Для того чтобы сохранялось взаимно однозначное соответствие строк курсора и исходной таблицы, курсор не должен идентифицировать упорядоченный набор данных.DISTINCT.GROUP BY или HAVING.Использование курсора для операций обновления значительно усложняет работу с подобным курсором со стороны СУБД, поэтому операции, связанные с позиционной модификацией, выполняются гораздо медленнее, чем операции с курсорами, которые используются только для чтения. Именно поэтому рекомендуется обязательно указывать в операторе определения курсора предложение READ ONLY, если вы не собираетесь использовать данный курсор для операций модификации. По умолчанию, если нет дополнительных указаний, СУБД создает курсор с возможностью модификации.
Курсоры — удобное средство для формирования бизнес-логики приложений, но следует помнить, что если вы открываете курсор с возможностью модификации, то СУБД блокирует все строки базовой таблицы, вошедшие в ваш курсор, и тем самым блокируется работа других пользователей с данной таблицей.
Чтобы свести к минимуму количество требуемых блокировок, при работе интерактивных программ следует придерживаться следующих правил:
COMMIT после каждого запроса и как можно скорее после изменений, сделанных программой.SCROLL ), потому что они требуют блокирования всех строк выборки, связанных с FETCH, что минимизирует блокировки других пользователей, работающих параллельно с вами и использующих те же таблицы.READ ONLY.Однако когда мы рассматривали модели "клиент—сервер", применяемые в БД, то определили, что в развитых моделях серверов баз данных большая часть бизнес-логики клиентского приложения выполняется именно на сервере, а не на клиенте. Для этого используются специальные объекты, которые называются -хранимыми процедурами и хранятся в БД, как таблицы и другие базовые объекты.
В связи с этим фактом курсоры, которые могут быть использованы в приложениях, обычно делятся на курсоры сервера и курсоры клиента. Курсор сервера создается и выполняется на сервере, данные, связанные с ним, не пересылаются на компьютер клиента. Курсоры сервера определяются обычно в хранимых процедурах или триггерах.
Курсоры клиента — это те курсоры, которые определяются в прикладных программах, выполняемых на клиенте. Набор строк, связанный с данным курсором, пересылается на клиент и там обрабатывается. Если с курсором связан большой набор данных, то операция пересылки набора строк, связанных с курсором, может занять значительное время и значительные ресурсы сети и клиентского компьютера.
Конечно, курсоры сервера более экономичны и выполняются быстрее. Поэтому последней рекомендацией, связанной с использованием курсоров, будет рекомендация трансформировать логику работы вашего приложения, чтобы как можно чаще вместо курсоров клиента использовать курсоры сервера.
С точки зрения приложений, работающих с БД, хранимые процедуры (
Хранимые процедуры могут быть активизированы не только пользовательскими приложениями, но и триггерами.
Хранимые процедуры пишутся на специальном встроенном языке программирования, они могут включать любые операторы SQL, а также включают некоторый набор операторов, управляющих ходом выполнения программ, которые во многом схожи с подобными операторами процедурно ориентированных языков программирования. В коммерческих СУБД для написания текстов хранимых процедур используются собственные языки программирования, так, в СУБД Oracle для этого используется язык PL /SQL, а в MS SQL Server и System11 фирмы Sybase используется язык Transact SQL. В последних версиях Oracle объявлено использование языка Java для написания хранимых процедур.
Хранимые процедуры являются объектами БД. Каждая хранимая процедура компилируется при первом выполнении, в процессе компиляции строится оптимальный план выполнения процедуры. Описание процедуры совместно с планом ее выполнения хранится в системных таблицах БД.
Для CREATE PROCEDURE.
По умолчанию выполнить хранимую процедуру может только ее владелец, которым является владелец БД, и создатель хранимой процедуры. Однако владелец хранимой процедуры может делегировать права на ее запуск другим пользователям.
Имя хранимой процедуры является идентификатором в языке программирования, на котором она пишется, и должно удовлетворять всем требованиям, которые предъявляются к идентификаторам в данном языке.
В MS SQL Server хранимая процедура создается оператором:
CREATE PROCEDURE]
<имя_процедуры> [;<версия>]
[{@параметр1 тип_данных} [VARYING]
[= <значение_по_умолчанию>] [OUTPUT]] [, .параметрN…]
[ WITH { RECOMPILE
ENCRYPTION
RECOMPILE, ENCRYPTION}]
[FOR REPLICATION] AS Тело процедуры
Здесь необязательное ключевое слово
Ключевое слово
Ключевое слово ENCRYPTION определяет режим, при котором исходный текст хранимой процедуры не сохраняется в БД. Такой режим применяется для того, чтобы сохранить авторское право на интеллектуальную продукцию, которой и являются хранимые процедуры. Часто такой режим применяется, когда вы ставите готовую базу заказчику и не хотите, чтобы исходные тексты разработанных вами хранимых процедур были бы доступны
хранить отдельно в некотором текстовом файле. И это не самое плохое, но вот в случае восстановления БД после серьезной аварии для перекомпиляции потребуются первоначальные исходные тексты всех хранимых процедур. Поэтому защита вещь хорошая, но она усложняет сопровождение и модификацию хранимых процедур.
Однако кроме имени хранимой процедуры все остальные параметры являются необязательными. Процедуры могут быть процедурами или процедурами-функциями. И эти понятия здесь трактуются традиционно, как в языках программирования высокого уровня. Хранимая процедура-функция возвращает значение, которое присваивается переменной, определяющей имя процедуры. Процедура в явном виде не возвращает значение, но в ней может быть использовано ключевое слово OUTPUT, которое определяет, что данный параметр является выходным.
Рассмотрим несколько примеров простейших хранимых процедур.
/* процедура проверки наличия экземпляров данной книги параметры: @ISBN шифр книги процедура возвращает параметр, равный количеству экземпляров Если возвращается ноль, то это значит, что нет свободных экземпляров данной книги в библиотеке. */ CREATE PROCEDURE COUNT_EX (@ISBN varchar(12)) AS /* определим внутреннюю переменную */ DECLARE @TEK_COUNT int /* выполним соответствующий оператор SELECT Будем считать только экземпляры, которые в настоящий момент находятся не на руках у читателей, а в библиотеке */ select @TEK_COUNT = select count(*) FROM EXEMPLAR WHERE ISBN = @ISBN AND READER_ID Is NULL AND EXIST = True /* 0 - ноль означает, что нет ни одного свободного экземпляра данной книги в библиотеке */ RETURN @TEK_COUNT
Хранимая процедура может быть вызвана несколькими способами. Простейший способ — это использование оператора:
EXEC <имя процедуры> <значение_входного_параметра1> … <имя_переменной_для_выходного параметра1> …
При этом все входные и выходные параметры должны быть заданы обязательно и в том порядке, в котором они определены в процедуре.
Например, если мне надо найти число экземпляров книги "Oracle8. Энциклопедия пользователя", которая имеет ISBN 966-7393-08-09, то текст вызова ранее
/*определили две переменные @Ntek – количество экземпляров данной книги в наличие в библиотеке @ISBN – международный шифр книги */ declare @Ntek int DECLARE @ISBN VARCHAR(14) /* Присвоим значение переменной @ISBN */ Select @ISBN = '966-7393-08-09' /* Присвоим переменной @Ntek результаты выполнения хранимой процедуры COUNT_EX */ EXEC @Ntek = COUNT_EX @ISBN
Если у вас определено несколько версий хранимой процедуры, то при вызове вы можете указать номер конкретной версии для исполнения. Так, например, в версии 2 процедуры COUNT_EX последний оператор исполнения этой процедуры имеет вид:
EXEC @Ntek = COUNT_EX;2 @ISBN
Однако если в процедуре определены значения входных параметров по умолчанию, то при запуске процедуры могут быть указаны значения не всех параметров. В этом случае оператор вызова процедуры может быть записан в следующем виде:
EXEC <имя процедуры> <имя_параметра1>=<значение параметра1> … <имя_параметраN>=<значение параметраN> …
Например, создадим процедуру, которая считает количество книг, изданных конкретным издательством в конкретном году. При создании процедуры зададим для года издания по умолчанию значение текущего года.
CREATE PROCEDURE COUNT_BOOKS (@YEARIZD int = Year(GetDate()), @PUBLICH varchar(20)) /* процедура подсчета количества книг конкретного издательства, изданных в конкретом году параметры: @YEARIZD int год издания @PUBLICH название издательства */ AS DECLARE @TEK_Count int Select @TEK_count = Select COUNT(ISBN) From BOOKS Where YEARIZD = @YEARIZD AND PUBLICH =@PUBLICH /* одновременно с исполнением оператора Select мы присваиваем результаты его работы определенной ранее переменной @TEK_Count */ /* при формировании результата работы нашей процедуры мы должны учесть, что в нашей библиотеке, возможно, нет ни одной книги некоторого издательства для заданного года. Результат выполнения запроса SELECT в этом случае будет иметь неопределенное значение, но анализировать все-таки лучше числовые значения. Поэтому в качестве возвращаемого значения мы используем результаты работы специальной встроенной функции Transact SQL COALESCE (n1,n2,…,nm), которая возвращает первое конкретное, то есть не равное NULL, значение из списка значений n1,n2,…,nm. */ Return COALESCE (@TEK_Count,0)
Теперь вызовем эту процедуру, для этого подготовим переменную, куда можно поместить результаты выполнения процедуры.
declare @N int Exec @N = COUNT_BOOKS @PUBLICH = 'Питер'
В переменной @N мы получим количество книг в нашей библиотеке, изданных издательством "Питер" в текущем году. Мы можем обратиться к этой процедуре и задав все параметры:
Exec @N = COUNT_BOOKS @PUBLICH = 'BHW', @YEARIZD = 1999
Тогда получим количество книг, изданных издательством "BHW" в 1999 году и присутствующих в нашей библиотеке.
Если мы задаем параметры по именам, то нам необязательно задавать их в том порядке, в котором они описаны при создании процедуры.
Каждая хранимая процедура является объектом БД. Она имеет уникальное имя и уникальный внутренний номер в системном каталоге. При изменении текста хранимой процедуры мы должны сначала уничтожить данную процедуру как объект, хранимый в БД, и только после этого записать на ее место новую. Следует отметить, что при
Для того чтобы автоматизировать процесс уничтожения старой процедуры и замены ее на новую, в начале текста хранимой процедуры можно выполнить проверку наличия объекта типа "хранимая процедура" с данным именем в системном каталоге и при наличии описания данного объекта удалить его из системного каталога. В этом случае текст хранимой процедуры предваряется специальным оператором проверки и может иметь, например, следующий вид:
/* проверка существования в системном каталоге объекта с
данным именем и типом, созданного владельцем БД */
if exists (select * from sysobjects where id = object_id('dbo.NEW_BOOKS')
and sysstat 0xf = 4)
/* если объект существует, то сначала его
удалим из системного каталога */
drop procedure dbo.NEW_BOOKS GO
CREATE PROCEDURE NEW_BOOKS
(
@ISBN varchar(12),
@TITL varchar(255),
@AUTOR varchar(30),
@COAUTOR varchar(30),
@YEARIZD int,
@PAGES INT,
@NUM_EXEMPL INT
)
/* процедура ввода новой книги с указанием
количества экземпляров данной книги параметры
@ISBN varchar(12) шифр книги
@TITL varchar(255) название
@AUTOR varchar(30) автор
@COAUTOR varchar(30) соавтор
@YEARIZD int год издания
@PAGES INT количество страниц
@NUM_EXEMPL INT количество экземпляров
*/
AS
/*опишем переменную, в которой будет храниться количество
оставшихся не оприходованных экземпляров книги, т.е. таких,
которым еще не заданы инвентарные номера */
DECLARE @TEK int
/ *вводим данные о книге в таблицу BOOKS */
INSERT INTO BOOKS
VALUES(@ISBN,@TITL,@AUTOR,
@COAUTOR,@YEARIZD,@PAGES)
/* назначение значения текущего счетчика осташихся
к вводу экземпляров*/
SELECT @TEK = @NUM_EXEMPL
/* организуем цикл для ввода новых экземпляров данной книги */
WHILE @TEK>0
/* пока количество оставшихся экземпляров больше нуля */
BEGIN
/* так как для инвентарного номера экземпляра книги мы задали свойство
IDENTITY, то нам не надо вводить инвентарный номер, СУБД сама автоматически
вычислит его, добавив единицу к предыдущему, введет при выполнении оператора
ввода INSERT. Поле, определяющее присутствие экземпляра в библиотеке (EXIST) –
логическое поле, мы введем туда значение TRUE, которое соответствует присутствию
экземпляра книги в библиотеке.
Даты взятия и возврата мы можем не заполнять, тогда по умолчанию СУБД
подставит туда значение, соответствующее 1 января 1900 года, если мы не хотим
хранить такие бессмысленные данные, то можем ввести для обоих полей дата время,
значения текущей даты. */
insert into EXEMPLAR (ISBN,DATA_IN,DATA_OUT,EXIST)
VALUES (@ISBN,GetDate(),GetDate()),TRUE)
/* изменение текущего значения счетчика количества оставшихся экземпляров */
SELECT @TEK = @TEK - 1
End
/* конец цикла ввода данных о экземпляре книги*/
GO
Если мы не использовали инкрементное поле в качестве инвентарного номера экземпляра, то мы могли бы сами назначать инвентарный номер, увеличивая на единицу номер последнего хранимого в библиотеке экземпляра книги. Можно было бы попробовать просто сосчитать количество существующих экземпляров в библиотеке, но мы могли удалить некоторые, и тогда номер нового экземпляра может быть уже использован, и мы не сможем ввести данные, система не позволит нам нарушить уникальность первичного ключа.
Текст процедуры в этом случае будет иметь вид:
/* проверка существования в системном каталоге объекта
с данным именем и типом, созданного владельцем БД */
if exists (select * from sysobjects
where id = object_id('dbo.NEW_BOOKS')
and sysstat 0xf = 4)
/* если объект существует, то сначала
его удалим из системного каталога */
drop procedure dbo.NEW_BOOKS
CREATE PROCEDURE NEW_BOOKS
( @ISBN varchar(12),
@TITL varchar(255),
@AUTOR varchar(30),
@COAUTOR varchar(30),
@YEARIZD int,
@PAGES INT,
@NUM_EXEMPL INT)
/* процедура ввода новой книги с указанием количества
экземпляров данной книги параметры
@ISBN varchar(12) шифр книги
@TITL varchar(255) название
@AUTOR varchar(30) автор
@COAUTOR varchar(30) соавтор
@YEARIZD int год издания
@PAGES INT количество страниц
@NUM_EXEMPL INT количество экземпляров */
AS
DECLARE @TEK int declare @INV int
INSERT INTO BOOKS
VALUES(@ISBN,@TITL,@AUTOR,@COAUTOR,@YEARIZD,@PAGES)
/* назначение значения текущего счетчика осташихся к вводу экземпляров*/
SELECT @TEK = @NUM_EXEMPL
/* определение максимального значения инвентарного номера в библиотеке */
SELECT @INV = SELECT MAX( ID_EXEMPLAR) FROM EXEMPLAR
/* организуем цикл для ввода новых экземпляров данной книги */
WHILE @TEK>0
/* пока количество оставшихся экземпляров больше нуля */
BEGIN
insert into EXEMPLAR (ID_EXEMPLAR,ISBN,DATA_IN,DATA_OUT,EXIST)
VALUES (@INV,@ISBN,GETDATE(),GetDate(), TRUE)
/* изменение текущих значений счетчика и инвентарного номера */
SELECT @TEK = @TEK - 1
SELECT @INV = @INV + 1
End
/* конец цикла ввода данных о экземпляре книги*/
GO
Хранимые процедуры могут вызывать одна другую. Создадим хранимую процедуру, которая возвращает номер читательского билета для конкретного читателя.
if exists (select * from sysobjects
where id = object_id('dbo. CK_READER')
and sysstat 0xf = 4)
/* если объект существует, то сначала его удалим из системного каталога */
drop procedure dbo.CK_READER
/* Процедура возвращает номер читательского билета,
если читатель есть и 0 в противном случае.
В качестве параметров передаем фамилию и дату рождения */
CREATE PROCEDURE CK_READER
( @FIRST_NAME varchar(30),
@BIRTH_DAY varchar(12))
AS
/*опишем переменную, в которой будет храниться
номер читательского билета*/
DECLARE @NUM_READER INT
/* определение наличия читателя */
select @NUM_READER = select NUM_READER from READERS
WHERE FIRST_NAME = @ FIRST_NAME AND
AND convert(varchar(8),BIRTH_DAY,4)=@BIRTH_DAY
RETURN COALESCE(@NUM_READER,0)
Мы здесь использовали функцию преобразования типа данных dataTime в тип данных varchar(8). Это было необходимо сделать для @BIRTH_DAY имеет символьный тип ( varchar ), а поле базы данных BIRTH_DAY имеет тип SmallDateTime.
Хранимые процедуры допускают наличие нескольких выходных параметров. Для этого каждый выходной параметр должен после задания своего типа данных иметь дополнительное ключевое слово OUTPUT. Рассмотрим пример хранимой процедуры с несколькими выходными параметрами.
Создадим процедуру ввода нового читателя, при этом внутри процедуры выполним проверку наличия в нашей картотеке данного читателя, чтобы не назначать ему новый номер читательского билета. При этом выходными параметрами процедуры будут номер читательского билета, признак того, был ли ранее записан читатель с данными характеристиками в нашей библиотеке, а если он был записан, то сколько книг за ним числится.
/* проверка наличия данной процедуры в нашей БД*/
if exists (select * from sysobjects
where id = object_id(N'[dbo].[NEW_READER]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NEW_READER]
GO
/* процедура проверки существования читателя с
заданными значениями вводимых параметров
Процедура возвращает новый номер читательского билета,
если такого читателя не было сообщает старый номер
и количество книг, которое должен читатель в противном случае */
CREATE PROCEDURE NEW_READER
( @NAME_READER varchar(30),
@ADRES varchar(40),
@HOOM_PHONE char(9),
@WORK_PHONE char(9),
@BIRTH_DAY varchar(8),
@NUM_READER int OUTPUT,
/* выходной параметр, определяющий номер читательского билета*/
@Y_N int OUTPUT,
/* выходной параметр, определяющий был ли читатель ранее записан в библиотеку*/
@COUNT_BOOKS int OUTPUT
/* выходной параметр, определяющий количество книг, которое числится за читателем*/
) AS
/* переменная, в которой будет храниться номер читательского билета,
если читатель уже был записан в библиотеку */
DECLARE @N_R int
/* определение наличия читателя */
EXEC @N_R = CK_READER @NAME_READER,@BIRTH_DAY
IF @N_R= 0 Or @N_R Is Null
/* если читатель с заданными характеристиками не найден,
т. е. переменной @N_R присвоено значение нуль или ее значение неопределено,
перейдем к назначению для нового читателя нового номера читательского билета */
BEGIN
/* так как мы номер читательского билета определили как инкрементное поле,
то в операторе ввода мы его не указываем система сама назначит новому читателю
очередной номер */
INSERT INTO READER(NAME_READER,ADRES,HOOM_PHONE,
WORK_PHONE, BIRTH_DAY)
VALUES (@NAME_READER,@ADRES,@HOOM_PHONE,
@WORK_PHONE,Convert(smalldatetime, @BIRTH_DAY,4) )
/* в операторе INSERT мы должны преобразовать символьную переменную @BIRTH_DAY
в тип данных smalldatetime, который определен для поля дата рождения BIRTH_DAY.
Это преобразование мы сделаем с помощью встроенной функции Transact SQL Convert */
/* теперь определим назначенный номер читальского билета */
select @NUM_READER = NUM_READER FROM READER
WHERE NAME_READER = @NAME_READER
AND convert(varchar(8),BIRTH_DAY,4)=@BIRTH_DAY
/* здесь мы снова используем функцию преобразования типа, но в этом случае нам
необходимо преобразовать поле BIRTH_DAY из типа smalldatetime к типу varchar(8),
в котором задан входной параметр @BIRTH_DAY */
Select @Y_N =0
/* присваиваем выходному параметру @Y_N значение 0 (ноль), что соответствует тому,
что данный читатель ранее в нашей библиотеке не был записан */
Select @COUNT_BOOKS = 0
/* присваиваем выходному параметру, хранящему количество книг,
числящихся за читателем значение ноль */
Return 1
END
else
/* если значение переменной @N_R не равно нулю, то читатель с заданными характеристиками
был ранее записан в нашей библиотеке */
BEGIN
/* определение количества книг у читателя с найденным номером читательского билета */
select @COUNT_BOOKS = COUNT(INV_NUMBER) FROM EXEMPLAR WHERE NUM_READER = @N_R
select @Count_books = COALESCE( @COUNT_BOOKS,0)
/* присваиваем выходному параметру @COUNT_BOOKS значение, равное количеству книг,
которые числятся за нашим читателем, если в предыдущем запросе @COUNT_BOOKS было
присвоено неопределенное значение, то мы заменим его на ноль, используя для этого
встроенную функцию COALESCE(@COUNT_BOOKS,0), которая возвращает первое определенное
значение из списка значений, заданных в качестве ее параметров */
Select @Y_N = 1
/* присваиваем выходному параметру @Y_N значение 1, что соответствует тому, что данный
читатель ранее в нашей библиотеке был записан */
Select @NUM_READER = @N_R
/* присваиваем выходному параметру @NUM_READER определенный ранее номер читательского билета */
return 0
end
Теперь посмотрим, как работает наша новая процедура, для этого в режиме интерактивного выполнения запросов (то есть в QueryAnalyzer MS SQL Server 7.0) запишем следующую последовательность команд:
-- пример использования выходных параметров при вызове процедуры -- new reader -- зададим необходимые нам переменные Declare @K int, @N int, @B int exec NEW_READER 'Пушкин В.В.','Лиговский 22-90', '333-55-99','444-66-88','01.06.83',@NUM_READER =@K OUTPUT, @Y_N = @N OUTPUT,@COUNT_BOOKS = @B OUTPUT -- теперь выведем результаты работы нашей процедуры используя ранее -- определенные нами переменные Select 'номер билета',@K,'да-нет',@N,'кол-во книг',@B
Мы получим результат:
Номер билета да-нет кол-во книг 18 0 0
Если же мы снова запустим нашу процедуру с теми же параметрами, то есть повторим выполнение подготовленных выше операторов, то получим уже иной ответ:
Номер билета да-нет кол-во книг 18 1 0
и это означает, что господин Пушкин В. В. уже записан в нашей библиотеке, но он не успел взять ни одной книги, поэтому за ним числится 0 (ноль) книг.
Теперь обратимся к оценке эффективности применения хранимых процедур.
Если рассмотреть этапы выполнения одинакового текста части приложения, содержащего SQL-операторы, самостоятельно на клиенте и в качестве хранимой процедуры, то можно отметить, что на клиенте выполняются все 5 этапов выполнения SQL-операторов, а хранимая процедура может храниться в БД в уже скомпилированном виде, и ее исполнение займет гораздо меньше времени (см. рис. 12.2).
Кроме того, хранимые процедуры, как уже упоминалось, могут быть использованы несколькими приложениями, а встроенные операторы SQL должны быть включены в каждое приложение повторно.
(рис 12.2) Процесс выполнения операторов SQL на клиенте и процесс выполнения хранимой процедурыХранимые процедуры также играют ключевую роль в повышении быстродействия при работе в сети с архитектурой "клиент—сервер".
На рис. 12.3 показан пример выполнения последовательности операторов SQL на клиенте, а на рис. 12.4 показан пример выполнения той же последовательности операторов SQL, оформленных в виде хранимой процедуры. В этом случае клиент обращается к серверу только для выполнения команды запуска хранимой процедуры. Сама хранимая процедура выполняется на сервере. Объем пересылаемой по сети информации резко сокращается во втором случае.
(рис 12.3) Сетевой трафик при выполнении встроенных SQL-операторов(рис 12.4) Сетевой трафик при выполнении хранимой процедуры на сервереФактически триггер — это специальный вид хранимой процедуры, которую SQL Server вызывает при выполнении операций модификации соответствующих таблиц. Триггер автоматически активизируется при выполнении операции, с которой он связан. Триггеры связываются с одной или несколькими операциями модификации над одной таблицей.
В разных коммерческих СУБД рассматриваются разные триггеры. Так, в MS SQL Server триггеры определены только как постфильтры, то есть такие триггеры, которые выполняются после свершения события.
В СУБД Oracle определены два
Триггеры могут быть эффективно использованы для поддержки
В стандарте SQL1 ни хранимые процедуры, ни триггеры были не определены. Но в добавлении к стандарту
Для
CREATE TRIGGER <имя_триггера>
ON <имя_таблицы>
FOR {[INSERT][, UPDATE] [, DELETE] }
[WITH ENCRIPTING]
AS
SQL-операторы (Тело триггера)
Имя триггера является идентификатором во встроенном языке программирования СУБД и должно удовлетворять соответствующим требованиям.
В параметре FOR задается одна или несколько операций модификации, которые запускают данный триггер.
Параметр WITH ENCRIPTING имеет тот же смысл, что и для хранимых процедур, он скрывает исходный текст тела триггера.
Существует несколько правил, которые ограничивают набор операторов, которые могут быть использованы в теле триггера.
Так, в большинстве СУБД действуют следующие ограничения:
DROP для всех типов базовых объектов БД.ALTER TABLE, ALTER DATABASE.GRAND или REVOKE VIEW ) .Рассмотрим пример триггера, который срабатывает при удалении экземпляра некоторой книги, например, в случае утери этой книги читателем. Что же может делать этот триггер? А он может выполнять следующую проверку: проверять, остался ли еще хоть один экземпляр данной книги в библиотеке, и если это был последний экземпляр книги в библиотеке, то резонно удалить описание книги из предметного каталога, чтобы наши читатели зря не пытались заказать эту книгу.
Текст этого триггера на языке Transact SQL приведен ниже:
/* Проверка существования данного триггера в системном каталоге */
if exists (select * from sysobjects
where id = object_id('dbo.DEL_EXEMP')
and sysstat 0xf = 8)
drop trigger dbo.DEL_EXEMP
GO
CREATE TRIGGER DEL_EXEMP ON dbo.EXEMPLAR
/* мы создаем триггер для таблицы EXEMPLAR */
FOR DELETE
/* только для операции удаления */
AS
/* опишем локальные переменные */
DECLARE @Ntek int /* количество оставшихся экземпляров удаленной книги */
DECLARE @DEL_EX VARCHAR(12) /* шифр удаленного экземпляра*/
Begin
/* по временной системной таблице, содержащей удаленные записи,
определяем шифр книги, соответствующей последнему удаленномуэкземпляру */
SELECT @DEL_EX = ISBN From deleted
/* вызовем хранимую процедуру, которая определит количество экземпляров
книги с заданным шифром */
EXEC @Ntek = COUNT_EX @DEL_EX
/* Если больше нет экземпляров данной книги, то мы удаляем запись о книге
из таблицы BOOKS */
IF @Ntek = 0 DELETE from BOOKS WHERE BOOKS.ISBN = @DEL_EX
END
GO
Возможности операторов
Сформированный таким образом текст SQL-оператора поступает в СУБД, которая должна его скомпилировать и выполнить "на лету", в процессе работы приложения. Если мы снова вернемся к этапам выполнения SQL-операторов, то первые четыре действия, связанные с синтаксическим анализом, семантическим анализом, построением и оптимизацией плана выполнения запроса, выполняются на этапе компиляции. В момент исполнения этого оператора СУБД просто изымает хранимый план выполнения этого оператора и исполняет его.
В случае
Конечно, динамический SQL гораздо менее эффективен в смысле производительности, по сравнению со статическим SQL. Поэтому во всех случаях, когда это возможно, необходимо избегать
Наиболее простой формой EXECUTE IMMEDIATE. Этот оператор имеет следующий синтаксис:
EXECUTE IMMEDIATE <имя_базовой переменной>
Базовая переменная содержит текст SQL оператора.
Однако оператор непосредственного выполнения пригоден для выполнения операций, которые не возвращают результаты. Так же как в
Прежде всего было предложено разделить выполнение SQL-оператора в динамическом SQL на два отдельных этапа. Первый этап называется подготовительным, он фактически включает 4 первых этапа выполнения SQL-операторов, рассмотренные нами ранее: синтаксический и семантический анализ, построение и оптимизация плана выполнения оператора.
Этот этап выполняется оператором PREPARE, синтаксис которого приведен ниже:
PREPARE <имя_оператора> FROM <имя_базовой переменной>
<имя_оператора> - это идентификатор базового языка.
Далее на втором этапе этот определенный на первом этапе оператор может быть выполнен операцией EXECUTE, которая имеет синтаксис:
EXECUTE <имя_оператора>
USING {<список базовых переменных>
DESCRIPTOR <имя_дескриптора>}
Здесь DESCRIPTOR — это некоторая структура, которая описывается на клиенте, но создается и управляется сервером. Дескриптор представляет совокупность элементов данных, принадлежащих СУБД. Программное обеспечение СУБД должно содержать и поддерживать набор операций над дескрипторами. Эта структура была введена в стандарт
В стандарт
ALLOCATE DESCRIPTOR <имя_дескриптора> [WITH MAX <число_элементов>] — оператор связывает имя дескриптора с числом его базовых элементов и обеспечивает выделение памяти под данный дескриптор.DEALLOCATE DESCRIPTOR <имя_дескриптора> SET DESCRIPTOR {COUNT = <имя_базовой переменной> | VALUE <номер элемента> {<имя_ элемента>= <имя_базовой переменной>[,..]}} — оператор занесения в дескриптор описания передаваемых параметров. Описания передаются СУБД, которая их обрабатывает, внося соответствующие изменения в область данных, отведенную под дескриптор.GET DESCRIPTOR { <имя_базовой переменной> = COUNT | VALUE <номер элемента> {<имя_базовой переменной>=<имя_элемента>[,..]} — оператор получения информации из дескрипторов после выполнения запроса.DESCRIBE [ INPUT | OUTPUT] <имя_оператора> USING SQL DESCRIPTOR <имя_дескрипто-ра> — оператор, позволяющий получить описания таблиц результатов запросов ( DESCRIBE OUTPUT ) или входных параметров ( DESCRIBE INPUT ).OPEN <имя_курсора> [USING <список базовых переменных> | USING SQL DESCRIPTOR <имя_дескриптора>] — динамический оператор FETCH <имя_курсора> [USING <список базовых переменных> | USING SQL DESCRIPTOR <имя_дескриптора>] — динамический оператор перемещения по курсору.DEALLOCATE PREPARE <имя_оператора> Следует отметить, что в настоящий момент большинство СУБД реализуют динамический SQL несколько отличными от стандарта способами, однако в ближайшем будущем все поставщики вынуждены будут перейти к стандарту, так как именно это привлекает пользователей и делает переносимым разрабатываемое прикладное программное обеспечение.
Для получения официальных документов о завершении программы дополнительного профессионального образования (удостоверения о повышении квалификации, дипломов о профессиональной переподготовке и MBA) необходимо предоставить:
Внимание! Вы можете не заказывать доставку бумажной версии официального документы, а скачать его в электронном виде и распечатать самостоятельно. Информация о выданном документе в течение 1 месяца загружается в Федеральную информационную систему «Федеральный реестр сведений о документах об образовании и (или) о квалификации, документах об обучении» - ФИС ФРДО.
Доступ на новый сайт осуществляется с использованием адреса электронной почты, который был указан вами при регистрации на "старом". Мы постарались перенести все ваши данные с прежнего ресурса, однако не исключена вероятность потери части информации.
При возникновении проблемы со входом, воспользуйтесь функцией сброса пароля
Если вы обнаружите несоответствия, пожалуйста, сообщите нам.