17232

Создания хранимых процедур и функций с использованием средства СУБД SQL Server и языковых конструкций Transact-SQL

Лабораторная работа

Информатика, кибернетика и программирование

Лабораторная работа № 2. Создания хранимых процедур и функций с использованием средства СУБД SQL Server и языковых конструкций TransactSQL. Цель работы Изучение возможностей программирования элементов поддержки БД под управлением СУБД SQL Server. Задание на лабораторн

Русский

2013-06-30

79.5 KB

13 чел.

Лабораторная работа № 2.

Создания хранимых процедур и функций с использованием средства СУБД SQL Server и языковых конструкций Transact-SQL.

Цель работы

Изучение возможностей программирования элементов поддержки БД под управлением СУБД SQL Server.

Задание на лабораторную работу

  1.  Ознакомиться с методическими указаниями по выполнению лабораторной работы.
    1.  Создание и модификация хранимой процедуры.
    2.  Создание и модификация функции пользователя.
  2.  Выполнить задания для самостоятельной работы.
    1.  Создать процедуры в соответствии с заданием.
    2.  Создать функции в соответствии с заданием.
  3.  Оформить отчет о выполнении лабораторной работе.

Методические рекомендации по выполнению работы

(1)

Создание и модификация хранимой процедуры

Выберите в дереве Проводника пункт Stored Procedures и, активизируя всплывающее меню, дайте команду New Stored Procedure. В появившемся окне Stored Procedure Properties в рабочую область выводится шаблон текста процедуры:

CREATE PROCEDURE [имя процедуры] [Описание входных переменных]

[Описание локальных переменных]

AS

Задайте имя процедуры и введите текст запроса (после ключевого слова AS).

Пример создания хранимой процедуры.

Создать процедуру для обновления таблицы ИЗДЕЛИЕ, имеющую два параметра: наименование и цена изделия. Первый параметр должен использоваться для идентификации редактируемой строки таблицы, второй – для занесения значения в соответствующее поле.

/*Задаем имя процедуры и параметр – в виде переменной и ее типа:*/

CREATE PROCEDURE update_izdelie @name1 CHAR(20), @name2 int 

AS 

/* Запрос на обновление таблицы использует параметр процедуры для спецификации условия выборки редактируемых строк. (Во избежание конфликта с зарезервированными словами языка имена полей рассматриваемой таблицы ограничены квадратными скобками, хотя в данном случае это необязательно) */

UPDATE IZDELIE 

SET [CENA] = @name2

WHERE [NAIMENOVANIE] = @name1

По окончании ввода текста проверьте синтаксис и сохраните процедуру (аналогичные действия описаны выше в п. «Создание и модификация триггера»). Имя процедуры появится в списке процедур в правой рабочей панели основного окна ЕМ.

Для модификации процедуры выберите ее из списка в правой панели ЕМ и инициируйте команду всплывающего меню Properties. Появится окно Stored Procedure Properties.

Вызов процедуры

Хранимую процедуру можно вызвать на выполнение из окна Query утилиты QA. Синтаксис команды вызова:

EXEC имя–процедуры список–значений– параметров

(2)

Создание и модификация пользовательской функции

Выберите в дереве Проводника пункт User Defined Function и, активизируя всплывающее меню, дайте команду New User Defined Function. В появившемся окне User Defined Function Properties в рабочую область выводится шаблон текста функции.

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

В SQL Server имеются следующие классы функций пользователя:

  •  Scalarфункции возвращают обычное скалярное значение, каждая может включать множество команд, объединяемых в один блок с помощью конструкции BEGIN...END;
  •  Inlineфункции содержат всего одну команду SELECT и возвращают пользователю набор данных в виде значения типа данных TABLE;
  •  Multi-statementфункции также возвращают пользователю значение типа данных TABLE, содержащее набор данных, однако в теле функции находится множество команд SQL (INSERT, UPDATE и т.д.). С их помощью и формируется набор данных, который должен быть возвращен после выполнения функции.

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

Функции Scalar

Создание и изменение функции данного типа выполняется с помощью команды:

{CREATE | ALTER } FUNCTION [владелец.]имя_функции

( [ { @имя_параметра скаляр_тип_данных

   [=default]}])

RETURNS скаляр_тип_данных

[WITH {ENCRYPTION | SCHEMABINDING}]

AS

BEGIN

<тело_функции>

RETURN скаляр_выражение

END

Дополнительные параметры, с которыми может быть создана функция, указываются посредством ключевого слова WITH. С помощью ключевое слово ENCRYPTION код команды, используемый для создания функции, будет зашифрован.

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

Когда в ходе выполнения кода функции встречается ключевое слово RETURN, выполнение функции завершается и как результат ее вычисления возвращается значение, указанное непосредственно после слова RETURN. В теле функции разрешается использование множества команд RETURN, которые могут возвращать различные значения. Единственное условие – тип данных возвращаемого значения должен совпадать с типом данных, указанным после ключевого слова RETURNS.

Например. Создать функцию скалярного типа для вычисления суммарного количества поставляемого товара, поступившего за определенную дату (2005 год). Владелец функции – пользователь с именем dbo.

CREATE FUNCTION

   dbo.summ(@data DATETIME)

RETURNS int

AS

BEGIN

DECLARE @c int

SELECT @c = SUM(Количество)

    FROM Поставка a INNER JOIN Заказ b ON a.Номер_заказа = b.Номер_заказа

    WHERE дата_заказа = @data

RETURN (@c)

END

Вызов функции можно осуществить следующим образом

DECLARE @kol int

SET @kol=dbo.samm ('02.11.05')

/*просмотр результата*/

SELECT @kol

Функции Inline

Создание и изменение функции этого типа выполняется с помощью команды:

{CREATE | ALTER } FUNCTION [владелец.]имя_функции

( [ { @имя_параметра скаляр_тип_данных

   [=default]})

RETURNS TABLE

[ WITH {ENCRYPTION | SCHEMABINDING}

AS

RETURN [(] SELECT_оператор [)]

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

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

Возвращаемое функцией значение типа TABLE может быть использовано непосредственно в запросе, т.е. в разделе FROM.

Например. Создать функцию табличного типа для определения двух наименований товара с наибольшим количеством поставок.

CREATE FUNCTION dbo.itog()

RETURNS TABLE

AS

RETURN (SELECT TOP 2 Наименование

  FROM Изделие a INNER JOIN Поставка b 

  ON а.Код_модели=b.Код_модели

  ORDER BY Количество DESC)

Вызов функции можно осуществить следующим образом

SELECT Наименование FROM dbo.itog()

Функции Multi-statement

Создание и изменение функций типа Multi-statement выполняется с помощью следующей команды:

{CREATE | ALTER }FUNCTION [владелец.]имя_функции

( [ { @имя_параметра скаляр_тип_данных

   [=default]}])

RETURNS @имя_параметра TABLE

   <определение_таблицы>

[WITH {ENCRYPTION | SCHEMABINDING}

AS

BEGIN

<тело_функции>

RETURN

END

Использование большей части параметров рассматривалось при описании предыдущих функций.

Набор возвращаемых данных должен формироваться с помощью команд INSERT, выполняемых в теле функции. Кроме того, в теле функции допускается использование различных конструкций языка SQL, которые могут контролировать значения, размещаемые в выходном наборе строк. При работе с командой INSERT требуется явно указать имя того объекта, куда необходимо вставить строки (переменная типа TABLE).

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

Например. Создать функцию (типа multi-statement), которая для некоторого сотрудника выводит список всех его подчиненных (подчиненных как непосредственно ему, так и опосредствованно через других сотрудников).

Список сотрудников с указанием каждого руководителя представлен в таблице emp_mgr со следующей структурой (у директора организации начальника нет – null):

emp

(сотрудник)

mgr

(руководитель)

a

null

b

a

c

a

d

a

e

f

f

b

g

b

i

c

k

d

CREATE FUNCTION fn_findReports(@id_emp CHAR(2))

RETURNS @report TABLE(empid CHAR(2) PRIMARY KEY, mgrid CHAR(2))

AS

BEGIN

   DECLARE @r INT

   DECLARE @t TABLE(empid CHAR(2) PRIMARY KEY, mgrid CHAR(2),

                    pr INT DEFAULT 0)

INSERT INTO @t SELECT emp,mgr,0 FROM emp_mgr WHERE emp=@id_emp

SET @r=@@ROWCOUNT /*функция возвращает количество строк*/

WHILE @r>0

BEGIN

   UPDATE @t SET pr=1 WHERE pr=0

   INSERT INTO @t SELECT e.emp, e.mgr,0 FROM emp_mgr e, @t t

                  WHERE e.mgr=t.empid AND t.pr=1

   SET @r=@@ROWCOUNT /*функция возвращает количество строк*/

   UPDATE @t SET pr=2 WHERE pr=1

END

INSERT INTO @report SELECT empid, mgrid FROM @t

RETURN

END

Определим список подчиненных сотрудника ‘b’:

SELECT * FROM fn_findReports('b')

Оператор возвратит следующие значения:

emp

mgr

b

a

e

f

f

b

g

b

Задания на лабораторную работу

(I)

  1.  Создать хранимую процедуру для добавления строки в таблицу ПОСТАВКА, с занесением кода модели, номера заказа в соответствии с параметрами, (в процедуру передаются название модели и имя заказчика), а также количества поставляемых изделий (передавать как константу).

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

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

(II)

  1.  Создать скалярнозначную функцию пользователя для вычисления суммарного количества поставок заданного товара.
  2.  Создать однострочную табличнозначную функцию пользователя для определения заказчиков, не поставляющих заданный товар.
  3.  Создать многострочную табличнозначную функцию пользователя для расчета процента поставок каждого изделия относительно общего количества поставок. Для выполнения расчетов можно воспользоваться следующей последовательностью действий:
    1.  SUM(Количество) (поставка) = A;
    2.  SUM(Количество) GROUP BY(Код_модели) (поставка) = (B1,…, Bk) – внести в табличную переменную;
    3.   - внести в возвращаемую табличную переменную.

Функция должна вернуть название изделия и соответствующий процент Ci.

(3)

Оформление отчета

  1.  Титульный лист оформляется согласно традиционным требованиям, включая Номер работы, Номер группы и ФИО студента, а также кто принимал данную работу.
  2.  Содержание должно включать Тему лабораторной работы и Ход ее выполнения.
  3.  Ход выполнения работы должен содержать все тексты хранимых процедур и функций реализованные в лабораторной работе.
  4.  Выводы

Контрольные вопросы

  1.  Понятие хранимой процедуры.
  2.  Типы хранимых процедур.
  3.  Описание входных и выходных параметров хранимой процедуре.
  4.  Отличия в использовании триггеров и хранимых процедур и функций.
  5.  Табличные переменные.
  6.  Типы функций пользователя.
  7.  Возможности использования функций.
  8.  Использования функций для создания вычисляемых полей в таблицах.

СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ

  1.  Дейт К. Введение в системы баз данных.: Пер. с англ. – К: Диалектика, 1998. –    6-е издание – 848 с.
  2.  Вейскас Дж. Эффективная работа с Access.: Пер. с англ. – СПб.: Питер, 1997.
  3.  Шкарина Л. Язык SQL: учебный курс. – СПб.: Питер, 2001.
  4.  Тихомиров Ю. MS SQL Server в подлиннике. – СПб.: БХВ, 2000.


 

А также другие работы, которые могут Вас заинтересовать

12463. Подготовка специалистов в области высокопроизводительных вычислений на базе межуниверситетской инновационной учебно-исследовательской лаборатории InterUniLab 66 KB
  Подготовка специалистов в области высокопроизводительных вычислений на базе межуниверситетской инновационной учебноисследовательской лаборатории InterUniLab А.С. Абрамова Н.А. Шехунова А.В. Бухановский Аннотация Рассматриваются особенности разработки учебномето
12464. Основы работы с программой MathCad 479 KB
  Основы работы с программой MathCad MathCad 14.0 программа помогающая выполнять различные вычисления математические операции. Спомощью нее можно узнать значение функции в конкретных точках построить график функции вычислять всевозможные формулы решать нелинейные уравн
12465. Технические каналы утечки речевой конфиденциальной информации 96.12 KB
  Цель: закрепление знаний о технических каналах утечки речевой конфиденциальной информации и выработка практических навыков работы с контрольноизмерительной аппаратурой регистрирующей акустические и виброакустические колебания в различных средах их распространения...
12466. Методологія системного аналізу і системного моделювання 48.5 KB
  Методологія системного аналізу і системного моделювання Завдання: Ознайомитися з теоретичним матеріалом. Скласти конспект за планом: поняття системи основна властивість системи; найважливіші характеристики системи визначення; зміст і резул
12467. Прямі методи розв’язання систем лінійних алгебраїчних рівнянь. Метод Гаусса та LU-розкладу 56.5 KB
  Лабораторна робота №1 Прямі методи розв’язання систем лінійних алгебраїчних рівнянь. Метод Гаусса та LUрозкладу. Мета роботи: ознайомитися з методами розв‘язання систем лінійних алгебраїчних рівнянь. Розглянути особливості реалізації прямих методів розв‘язання ...
12468. Проектирование металлического моста под железную дорогу 627.76 KB
  Полная длина моста определяется по заданному отверстию моста с учетом количества пролетов в схеме моста и конструктивных параметров опор (тип устоя, толщина промежуточной опоры и т.д.).
12469. Розв’язання функціональних рівнянь з однією змінною 372.82 KB
  Лабораторна робота №3 Розв’язання функціональних рівнянь з однією змінною Мета роботи: ознайомитися з методами розв‘язання рівнянь з однією змінною розглянути реалізацію цих методів у середовищі MatLab. Задачі лабораторної роботи: реалізувати один з методів у ві
12470. Розв‘язання систем нелінійних рівнянь. Метод Ньютона 87.49 KB
  Лабораторна робота №4 Чисельні методи Лабораторна робота №4 Розв‘язання систем нелінійних рівнянь. Метод Ньютона. Мета роботи: познайомитися з методами розв‘язання
12471. Інтерполяційні поліноми Лагранжа. Сплайн-інтерполяція 86.49 KB
  Лабораторна робота №5 Інтерполяційні поліноми Лагранжа. Сплайнінтерполяція. Мета роботи: познайомитися з методами інтерполяції складних функцій реалізувати заданий за варіантом метод інтерполяції у середовищі МatLAB. Завдання до виконання роботи: Доповнити сис...