 |
Возвращение данных из хранимой процедуры
|
|
|
|
Нередко хранимые процедуры используются для выполнения тех или иных модификаций данных в таблицах базы данных. Однако они также позволяют, и возвращать в вызывающую конструкцию данные, получаемые в ходе выполнения команд процедуры. Эта возможность позволяет использовать процедуры для автоматизации выполнения сложных вычислений. Пользователь подает на вход процедуры набор значений, а в ответ ему возвращается результат вычисления. Конечно, подобное поведение более характерно для функций.
В распоряжении пользователей имеются следующие варианты возвращения данных из хранимой процедуры.
- стандартный набор строк (recordset). Этот вариант подразумевает выполнение в теле хранимой процедуры команды SELECT, которая подготовит набор данных и вернет его непосредственно в соединение. Отметим, каждая процедура может формировать множество наборов строк, которые могут иметь разную структуру. Напомним, что функции пользователя также могут возвращать стандартный набор строк, однако в отличие от процедур они возвращают всего один набор строк.
- использование параметров. Ранее уже говорилось, что параметры хранимой процедуры могут использоваться для возвращения данных. Для этого достаточно при создании хранимой процедуры для соответствующего параметра указать ключевое слово OUTPUT. Если при вызове хранимой процедуры для параметра также будет указано ключевое слово OUTPUT, то после завершения значение, которое в ходе работы процедуры принял параметр, будет передано из процедуры.
- использование кода завершения. После завершения работы процедуры сервер присваивает ей код завершения. По умолчанию этот код равен 0. Однако можно вернуть любой другой код завершения, который будет свидетельствовать о тех или иных ситуациях, обнаруженных в ходе выполнения процедуры. Например, если пользователь вызывает процедуру для выполнения вставки строки, то процедура предварительно может выполнить некоторые проверки на корректность вводимых данных и в случае обнаружения каких-либо несоответствий вернуть тот или иной код завершения. Анализируя полученный код завершения, можно понять причину отмены операции вставки. Для установки кода завершения используется команда RETURN n. Когда в теле процедуры встречается команда RETURN, сервер завершает выполнение процедуры и устанавливает код завершения, указанный с помощью параметра п.
|
|
|
- использование определяемых пользователем ошибок. В SQL Server имеется механизм, позволяющий пользователям создавать свои собственные сообщения об ошибках. С помощью команды RAISEROOR можно заставить сервер передать в текущее соединение сообщение о любой ошибке, в том числе и об определяемой пользователем. Для обнаружения ошибки применяют специальные средства. Практически все технологии доступа к данным (ADBC, ADO, OLE-DB, DB-Library и т.д.) имеют средства обнаружения ошибок.
Пример 12.
Для выполнения процедуры, созданной в примере 9 наберите:
DECLARE @theError int
EXECUTE @theError=ErrorSP
SELECT @theError AS 'Return Value'
Если во второй панели сетки отображается 0, значит команда выполнена успешно.
Удаление хранимой процедуры
Удаление хранимой процедуры средствами Transact-SQL выполняется при помощи следующей команды: DROP PROCEDURE {procedure} [,...n].
Процедура будет немедленно удалена. Это означает, что будет удалена запись о ее имени из таблицы sysobjects и текст кода процедуры из таблицы syscomments.
Рассмотрим пример:
Пример 10.
Drop procedure SimpleMine4
Задание на лабораторную работу
Выполнить примеры 1-10. Создать функцию пользователя и хранимые процедуры.
Оформить отчет, содержащий цель, ход выполнения работы и выводы.
|
|
|
Контрольные вопросы
1. Какие типы функций поддерживает SQL Server?
2. Какие существуют классы пользовательских функций?
3. Какие существуют ограничения на пользовательские функции?
4. Переменные каких типов данных нельзя передавать в пользовательскую функцию?
5. Чем отличается Inline функция от Multi-statement функции?
6. При помощи какой команды можно удалить пользовательскую функцию?
Лабораторная работа № 5.
МЕТАДАННЫЕ
Цель работы
- Закрепление теоретических знаний по метаданным.
- Приобретение практических навыков по работе с метаданными таблиц и базы данных.
Теоретическая часть
Общие сведения
Представления информационных схем являются частью стандарта SQL-92. Этот стандарт определяет ряд представлений, которые призваны предоставлять информацию о базе данных. Например, есть представление по имени TABLES, которое предоставляет информацию о таблицах базы данных. Запрос к этому представлению, можно сделать точно так же, как и к любому другому представлению.
Пример 1.
SELECT * FROM pubs.INFORMATION_SCHEMA.TABLES
Этот запрос возвратит информацию обо всех таблицах и представлениях в базе данных pubs:
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
------------- ------------ ---------- ----------
pubs dbo authors BASE TABLE
pubs dbo discounts BASE TABLE
...
pubs dbo titleview VIEW
Кроме обычного набора таблиц могут быть отображены несколько системных, которые используются для репликации. Это такие таблицы, как sysarticles и syspublications. TABLE_CATALOG – это имя базы данных, а TABLE_SCHEMA – владелец объекта. Обратите внимание, что владельцем представления INFORMATION_SCHEMA является dbo.
Другим интересным представлением является COLUMNS.
Пример 2.
SELECT TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM pubs.INFORMATION_SCHEMA.columns
WHERE table_name = 'authors'
Возвратит информацию обо всех столбцах таблицы authors:
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH
-------- ------- ------- ------- ------- -------
pubs dbo authors au_id varchar 11
pubs dbo authors au_lname varchar 40
pubs dbo authors au_fname varchar 20
pubs dbo authors phone char 12
pubs dbo authors address varchar 40
pubs dbo authors city varchar 20
pubs dbo authors state char 2
pubs dbo authors zip char 5
pubs dbo authors contract bit NULL
На практике, можно получить немногим больше информации, чем из этого представления, результаты работы которого представлены выше. Представление выводит по одной строке для каждого столбца таблицы в порядке его позиции; показывает допустимость null -значений; видна точность числовых значений; можно увидеть значения по умолчанию; какой используется набор символов; тип сортировки, его порядок и любую другую информацию относительно определяемого пользователем типа данных. Ниже представлен список всех представлений INFORMATION_SCHEMA:
|
|
|
§ CHECK_CONSTRAINTS - предоставляет информацию обо всех ограничениях в базе данных;
§ COLUMN_DOMAIN_USAGE - показывает, какая колонка и в какой таблице является определяемым пользователем типом данных
§ COLUMN_PRIVILEGES выводит по одной строке для каждой привилегии уровня столбца, предоставленной текущему пользователю;
§ COLUMNS выводит по одной строке для каждого столбца в каждой таблице или представлении базы данных;
§ CONSTRAINT_COLUMN_USAGE выводит по одной строке для каждого столбца, которому сопоставлено ограничение;
§ CONSTRAINT_TABLE_USAGE выводит по одной строке для каждой таблицы, которая имеет ограничения;
§ DOMAIN_CONSTRAINTS выводит определяемые пользователем типы данных, которым сопоставлены правила;
§ DOMAINS выводит определяемый пользователем тип данных;
§ KEY_COLUMN_USAGE выводит по одной строке для каждого столбца, который определен как ключ;
§ PARAMETERS выводит по одной строке для каждого параметра в хранимой процедуре или определяемой пользователем функции;
§ REFERENTIAL_CONSTRAINTS выводит по одной строке для каждого ограничения внешнего ключа;
§ ROUTINES выводит по одной строке для каждой хранимой процедуры или определяемой пользователем функции;
§ ROUTINE_COLUMNS выводит по одной строке для каждого столбца, возвращаемого любыми функциями табличного типа (table-valued functions);
§ SCHEMATA выводит по одной строке для каждой базы данных;
§ TABLE_CONSTRAINTS выводит по одной строке для каждого ограничения, определенного в текущей базе данных;
§ TABLE_PRIVILEGES выводит по одной строке для каждой привилегии уровня таблицы, предоставленной текущему пользователю;
§ TABLES выводит по одной строке для каждой таблицы или представления в текущей базе данных;
|
|
|
§ VIEW_COLUMN_USAGE выводит по одной строке для каждого столбца в представлении, включая базовые таблицы этого столбца, когда это возможно;
§ VIEW_TABLE_USAGE выводит по одной строке для каждой таблицы, используемой в представлении;
§ VIEWS выводит по одной строке для каждого представления;
Функции метаданных. SQL Server также имеет ряд функций, которые возвращают информацию об объектах в базе данных. В качестве наглядного примера, рассмотрим использование функции ColumnProperty. Выполним следующий запрос в базу данных Northwind.
Пример 3.
SELECT COLUMNPROPERTY (OBJECT_ID('Categories'), 'CategoryID','IsIdentity')
В результате исполнения этого запроса возвращается 1, которая указывает, что столбец CategoryID является Identity. Существуют также другие, дополнительные функции, которые призваны выдавать информацию об Identity столбцах. С помощью функции ColumnProperty можно проверять довольно большой набор свойств, включая: допустимость null -значений, точность, масштаб и т.д. Многие из этих свойств также доступны в представлениях информационной схемы, но некоторых там нет. В Books Online имеется полный список функций метаданных.
В представленном выше запросе, использована ещё одна функция метаданных – OBJECT_ID. Многие из функций метаданных принимают в качестве указателя на объект только его идентификатор (ID). В таких случаях, удобно использование этой функции, которая как раз и возвращает необходимый ID объекта, указанного в её параметре по имени. Обратной для этой функции является функция OBJECT_NAME, которая возвращает по ID объекта его имя.
Другая удобная функция – ObjectProperty, которая работает аналогично ColumnProperty, но имеет намного больше свойств, которые можно проверить. Например, следующий запрос покажет таблицы, которые имеют Identity, кластерные индексы и первичные ключи.
Пример 4.
SELECT TABLE_NAME,
IDNTY = ObjectProperty(OBJECT_ID(TABLE_NAME), 'TableHasIdentity'),
CLSTRD = ObjectProperty(OBJECT_ID(TABLE_NAME), 'TableHasClustIndex'),
PK = ObjectProperty(OBJECT_ID(TABLE_NAME), 'TableHasPrimaryKey')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'
Так же можно сделать проверку того, действительно ли первичный ключ является кластерным индексом. Этот способ удобен для проектов, в которых разработчики могут создавать собственные таблицы.
|
|
|
