Практическая работа 1. Проектирование базы данных
 Скачать 2.91 Mb.
|
|
функции трех классов: - Scalar – возвращают обычное скалярное значение; - Inline – возвращают таблицу динамической структуры, создаваемую единственной командой тела функции SELECT; - Multi – statement – возвращает обычную таблицу заданной структуры, при этом количество команд в теле функции не ограничивается. 1. Создание функций Команда: CREATE FUNCTION (Transact-SQL) создает определяемую пользователем функцию в SQL Server. Определяемая пользователем функция представляет собой подпрограмму Transact-SQL или среды CLR, которая принимает параметры, выполняет действия, такие как сложные вычисления, а затем возвращает результат этих действий в виде значения. Возвращаемое значение может быть скалярным значением или таблицей. При помощи этой инструкции можно создать подпрограмму, которую можно повторно использовать следующими способами: В инструкциях Transact-SQL, например SELECT. В приложениях, вызывающих функцию. В определении другой пользовательской функции. Для параметризации представления или улучшения функциональности индексированного представления. Для определения столбца таблицы. Для определения ограничения CHECK на столбец. Для замены хранимой процедуры. Синтаксис создания скалярной функции. 160 Аргументы schema_name - Имя схемы, к которой принадлежит определяемая пользователем функция. function_name - Имя определяемой пользователем функции. Имена функций должны удовлетворять правилам построения идентификаторов и должны быть уникальными в пределах базы данных и схемы. @parameter_name - Аргумент пользовательской функции. Может быть объявлен один или несколько аргументов. Для функций допускается не более 2 100 параметров. При выполнении функции значение каждого из объявленных параметров должно быть указано пользователем, если для них не определены значения по умолчанию. [ type_schema_name. ] parameter_data_type - Тип данных параметра [ =default ] - Значение по умолчанию для аргумента. Если определено значение default, то функция выполняется даже в том случае, если для данного аргумента значение не указано. READONLY - Указывает, что параметр не может быть обновлен или изменен при определении функции. Если тип параметра является определяемым пользователем табличным типом, то должно быть указано ключевое слово READONLY. return_data_type - Возвращаемое значение скалярной функции, определяемой пользователем. function_body - Указывает серию инструкций Transact-SQL scalar_expression - Указывает скалярное значение, возвращаемое скалярной функцией. Предложение EXECUTE AS - Указывает контекст безопасности, в котором выполняется определяемая пользователем функция. Иными словами, есть возможность управлять тем, какую учетную запись пользователя SQL Server использует при определении разрешений на объекты базы данных, на которые ссылается функция. Синтаксис функции, которая возвращает табличное значение: 161 Аргументы: TABLE - Указывает, что возвращаемым значением функции с табличным значением, является таблица. Функциям с табличным значением, могут передаваться только константы и @local_variables. Во встроенных функциях с табличным значением возвращаемое значение TABLE определяется при использовании единственной инструкции SELECT. Встроенные функции не имеют соответствующих возвращаемых переменных. select_stmt - Одиночная инструкция SELECT, определяющая возвращаемое значение встроенной функции с табличным значением. В функциях допустимы следующие инструкции. Инструкции присваивания. Инструкции управления потоком, за исключением инструкций TRY...CATCH. Инструкции DECLARE, объявляющие локальные переменные и локальные курсоры. Инструкции SELECT, которые содержат списки выбора с выражениями, присваивающими значения локальным переменным. Операции над локальными курсорами, которые объявляются, открываются, закрываются и освобождаются в теле функции. Допустимы только те инструкции FETCH, которые предложением INTO присваивают значения локальным переменным. Инструкции FETCH, возвращающие данные клиенту, недопустимы. Инструкции INSERT, UPDATE и DELETE, которые изменяют локальные табличные переменные. Инструкции EXECUTE, вызывающие расширенные хранимые процедуры. Ограничения Определяемые пользователем функция не может выполнять действия, изменяющие состояние базы данных. Определяемые пользователем функции не могут содержать предложение OUTPUT INTO, целью которого является таблица. Определяемые пользователем функции могут быть вложенными, то есть из одной функции может быть вызвана другая. Вложенность определяемых пользователем функций не может превышать 32 уровней. Пример 1. Применение скалярной определяемой пользователем функции, вычисляющей месяц. 162 В следующем примере показано создание определяемой пользовательской функции ISOweek , которая получает в качестве аргумента дату и вычисляет номер месяца, а затем по номеру определяет название месяца. Для правильной работы этой функции перед ее вызовом должна быть выполнена инструкция SET DATEFIRST 1 . Следующий пример также показывает использование предложения EXECUTE AS для указания контекста безопасности, в котором может быть выполнена хранимая процедура. В этом примере параметр CALLER указывает, что процедура будет выполнена в контексте пользователя, который ее вызывает. В начале, прежде чем создавать функцию, будет выполнена проверка на наличие в базе данных функции под таким же именем. Если функция с таким именем уже была создана ранее, то она будет удалена с помощью команды DROP FUNCTION и создана заново CREATE FUNCTION. Откройте sql-редактор. Создайте новый запрос. USE University; GO IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL DROP FUNCTION dbo.ISOweek; GO CREATE FUNCTION dbo.ISOweek (@DATE date) RETURNS CHAR(15) WITH EXECUTE AS CALLER AS BEGIN DECLARE @man int; DECLARE @ISOweek char(15); SET @man= MONTH(@DATE) IF (@man=1) SET @ISOweek='Январь'; IF (@man=2) SET @ISOweek='Февраль'; IF (@man=3) SET @ISOweek='Март'; IF (@man=4) SET @ISOweek='Апрель'; IF (@man=5) SET @ISOweek='Май'; IF (@man=6) SET @ISOweek='Июнь'; IF (@man=7) SET @ISOweek='Июль'; IF (@man=8) SET @ISOweek='Август'; IF (@man=9) SET @ISOweek='Сентябрь'; IF (@man=10) SET @ISOweek='Октябрь'; IF (@man=11) SET @ISOweek='Ноябрь'; IF (@man=12) SET @ISOweek='Декабрь'; RETURN(@ISOweek); END; Для того чтобы увидеть результат, выведем на экран вычисление значение функции от произвольной даты, например '12.04.2004'. для этого ниже в sql-редакторе пишем: GO SET DATEFIRST 1; 163 SELECT dbo.ISOweek('12.04.2004') AS 'Месяц'; Ниже приводится результирующий набор. Просмотреть все функции, которые пользователь может создавать, откройте в окне обозревателя объектов в базе данных University группу Программирование/Функции/Скалярные функции. Пример 2. Создание пользовательской функции с табличным значением. Например, создадим пользовательскую функцию, которая будет разрешена к использованию всеми пользователями с правами роли «dekan» (созданной в лаб.работе №2). Функция будет возвращать результат в виде таблицы – вывод на всех кафедрах суммирующей зарплаты по каждой должности всех преподавателей. При этом функция имеет один параметр @storeid, с помощью которого введем ограничение на вычисление, а именно зарплата должна быть больше 100. Создайте новый запрос: USE University; GO IF OBJECT_ID (N'ufn_SalesByStore', N'IF') IS NOT NULL DROP FUNCTION dekan.ufn_SalesByStore; GO CREATE FUNCTION dekan.ufn_SalesByStore(@storeid int) RETURNS TABLE AS RETURN ( SELECT d.Name_kafedru AS "Кафедра", t.Dolgnost AS "Должность", 164 SUM(t.Salary + t.RISE) AS "Сумма зарплаты" FROM KAFEDRA d, TEACHER t WHERE d.KOD_kafedru =t.KOD_kafedru and t.salary>@storeid GROUP BY d.Name_kafedru, t.Dolgnost ); Для вызова этой функции нужно выполнить следующий запрос: GO SELECT * from dekan.ufn_SalesByStore(100); Ниже приводится результирующий набор. Самостоятельно создайте запрос: Запрос 8. Создать пользовательскую функцию, которая будет возвращать результат в виде таблицы, а именно выводить всех учащихся студентов по кафедрам с указанием курса. При этом функция имеет один параметр @city, с помощью которого введем ограничение на вычисление, а именно город проживания должен быть Ростов-на- дону. 2. Создание хранимых процедур вMicrosoft SQL Server Хранимые процедуры представляют собой набор команд, состоящий из одного или нескольких операторов SQL или функций и сохраняемый в базе данных в откомпилированном виде. Типы хранимых процедур Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры - полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется. Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные.. Создание, изменение хранимых процедур Создание хранимой процедуры предполагает решение следующих задач: планирование прав доступа. При создании хранимой процедуры следует учитывать, что 165 она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь; определение параметров хранимой процедуры, хранимые процедуры могут обладать входными и выходными параметрами; разработка кода хранимой процедуры. Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур. Синтаксис оператора создания новой или изменения имеющейся хранимой процедуры в обозначениях MS SQL Server: Рассмотрим параметры данной команды. Используя префиксы sp_, #, ##, создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно. Для передачи входных и выходных данных в создаваемой хранимой процедуре имена параметров должны начинаться с символа @. В одной хранимой процедуре можно задать множество параметров @parameter, разделенных запятыми. В теле процедуры не должны применяться локальные переменные, чьи имена совпадают с именами параметров этой процедуры. Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры. Указание ключевого слова OUTPUT предписывает серверу при выходе из хранимой процедуры присвоить текущее значение параметра локальной переменной, которая была указана при вызове процедуры в качестве значения параметра. Ключевое слово VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR. Оно определяет, что выходным параметром будет результирующее множество. Ключевое слово DEFAULT представляет собой значение, которое будет принимать соответствующий параметр по умолчанию. Таким образом, при вызове процедуры можно не указывать явно значение соответствующего параметра. 166 Ключевое слово AS размещается в начале собственно тела хранимой процедуры. В теле процедуры могут применяться практически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры. Выход из хранимой процедуры можно осуществить посредством команды RETURN. Удаление хранимой процедуры DROP PROCEDURE {имя_процедуры} [,...n] Выполнение хранимой процедуры Для выполнения хранимой процедуры используется команда: [[ EXEC [ UTE] имя_процедуры [;номер] [[@имя_параметра=]{ значение | @имя_переменной} [OUTPUT ]|[DEFAULT ]][,...n] Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера. Использование ключевого слова OUTPUT при вызове процедуры разрешается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT. Когда же при вызове процедуры для параметра указывается ключевое слово DEFAULT, то будет использовано значение по умолчанию. Естественно, указанное слово DEFAULT разрешается только для тех параметров, для которых определено значение по умолчанию. Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры. Присвоить параметру значение по умолчанию, просто пропустив его при перечислении, нельзя. Если же требуется опустить параметры, для которых определено значение по умолчанию, достаточно явного указания имен параметров при вызове хранимой процедуры. Более того, таким способом можно перечислять параметры и их значения в произвольном порядке. Отметим, что при вызове процедуры указываются либо имена параметров со значениями, либо только значения без имени параметра. Их комбинирование не допускается. Использование RETURN в хранимой процедуре Позволяет выйти из процедуры в любой точке по указанному условию, а также позволяет передать результат выполнения процедуры числом, по которому можно судить о качестве и правильности выполнения процедуры. Примеры создания процедур Пример 3. Создание процедуры без параметров. Процедура вычисляет кол-во всех ассистентов: CREATE PROCEDURE Count_Assistent AS 167 Select count(DOLGNOST) from TEACHER where DOLGNOST='Ассистент' Go Создайте данную процедуру к базе данных University через утилиту SQL server Management Studio. Выполните запрос. Посмотрите созданную процедуру в разделе Программирование/Хранимые процедуры базы данных University. Запустите ее с помощью команды: EXECUTE Count_Assistent Проверьте результат. Количество ассистентов = 6. Пример 4. Создание процедуры c входным параметром. Например, нужно посчитать кол-во ассистентов, зарплата у которых не более заданного параметра @Sum_salary: CREATE PROCEDURE Count_Assistent_Salary @Sum_salary as Int AS Select count(DOLGNOST) from TEACHER WHERE DOLGNOST='Ассистент' and SALARY>=@Sum_salary Go Создайте данную процедуру. Запустите ее с помощью команды EXEC Count_Assistent_Salary 1500 Проверьте результат. Самостоятельно создать запрос: Запрос 8. Создать процедуру Count_Assistent_Salary_Title c входными параметрами @Sum_salary целого типа, @Title строка с длиной 15 символов, которая определяет кол-во преподавателей, должность которых совпадает с параметром @Title и зарплата у которых не менее заданного параметра @Sum_salary. 168 Создайте данную процедуру. Запустите ее с помощью команды EXECUTE Count_Assistent_Salary_Title 1300, '%нт%' Проверьте результат. Пример 6. Создание процедуры c входными параметрами и выходным параметром: CREATE PROCEDURE Count_Assistent_Itogo @Sum_salary Int, @Title Char(15) , @Itogo Int OUTPUT AS Select @Itogo = count(DOLGNOST) from TEACHER WHERE SALARY<=@Sum_salary AND DOLGNOST LIKE @Title Go Создайте данную процедуру. Запустите с помощью набора команд: Declare @q As int EXEC Count_Assistent_Itogo 2000, '%тент%', @q output select @q Проверьте результат. Пример 7. Создание процедуры c входными параметрами и RETURN. Пусть процедура проверяет, если номер студента равен параметру @param и одновременно его фамилия 'Петрова', то выдать 1, иначе 2: CREATE PROCEDURE checkname @param int AS IF (SELECT SUTFNAME FROM STUDENT WHERE STUDENT_ID = @param) = 'Петрова' RETURN 1 ELSE RETURN 2 Создайте данную процедуру. Запустите ее с помощью команд: DECLARE @return_status int EXECUTE @return_status = checkname 15 SELECT 'Return Status' = @return_status Проверьте результат. Пример 8. Создание процедуры без параметров для уменьшения значения стипендии в таблице Student на 50 грн: |