17232

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

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

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

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

Русский

2013-06-30

79.5 KB

15 чел.

Лабораторная работа № 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.


 

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

82263. Язык социально-гуманитарных наук. Языковая картина мира и «языковые игры» 34.44 KB
  проблемы природы языка принципов и законов его функционирования начинают изучаться лингвистами логиками психологами и философами. Таким образом для языкознания важными вопросами становятся вопросы семантики а также проблемы взаимосвязи языка и мышления языка и предметного мира. Так швейцарский лингвист Фердинанд де Соссюр 18571913 указывает на то что предметом изучения лингвистики становится имманентная реальность языка. Также проблемы языка в первую очередь выдвигаются в логике.
82264. Интерпритация как придание смысла, значения высказываниям, текстам, явлениям, событиям 40.1 KB
  Это внешняя сторона интерпретации. Выделяя к качестве предмета изучения исторического познания текст мы не должны сводить процедуру интерпретации к набору грамматических языковых игр Л. Объективный план интерпретации как операции мышления представлен с одной стороны предметом исследования а с другой операциональным или формально логическим каркасом своего рода алгоритмом системой стандартных шагов правит принципов и приемов субъекта познания в ходе познавательной деятельности. Общепризнанным каноном процесса интерпретации в...
82265. Вера и знание, достоверность и сомнение. Диалектика веры и сомнения в процессе познания 32.72 KB
  В социальногуманитарных науках знание всегда сочетается с верой и сомнением так как вера ориентирована на преувеличение роли абсолютного момента в знании а сомнение роли относительного в нем. Вера присутствует в социальногуманитарных науках прежде всего в силу незавершенности познания социальных явлений как допущение возможности соответствия социальной реальности и его отражения в знании. Она также может присутствовать в социальногуманитарных науках: как вера ученогогуманитария в Бога ученый привносит в науку свою веру как его...
82266. Конструктивная роль веры как условия «бытия среди людей» (Л.Витгенштейн) Вера и верования 31.72 KB
  Витгенштейн Вера и верования. Вера возникает как необходимое следствие бытия среди людей утверждает Витгенштейн имеет социальнокоммуникативную природу. Вера субъективная уверенность. Вера и знание имеют различные основания противоположно направленные.
82267. Вера и понимание в контексте коммуникаций. Вера и истина. Типы обоснования веры и знания. Соотношение веры и истины 36.66 KB
  Типы обоснования веры и знания. Одной из основных предпосылок философскометодологического анализа социальногуманитарного знания является рассмотрение научного познания в контексте культуры его связь с историческими особенностями и ценностными установками общества. Тема веры достоверности сомнения оказывается одной из фундаментальных в самых разных областях и на разных этапах научного познания. Соотношение различных духовноценностных установок веры и научного знания поразному влияло на развитие науки.
82268. Натуралистическая исследовательская программа 38.77 KB
  Сегодня вопрос об исследовательской программе или близком к ней понятии парадигмы в социальных науках сталкивается с двумя трудностями: 1 избрания масштаба исследования; 2 многообразия исследовательских программ господствующего сегодня в социальногуманитарных науках. Какие исследовательские программы парадигмы можно выделять 1 Классическая философия были ориентирована на природу и изучающие ее науки на следующую отсюда натуралистическую парадигму. Последователи натуралистической исследовательской программы полагают: либо предмет наук...
82269. Антинатуралистическая исследовательская программа и ее общенаучное значение 36.58 KB
  Природа остается в качестве предпосылки деятельности человека но культур центризмом не схватывается оставляя место натурализму Другой причиной жизненности натуралистической исследовательской программы является вызванное объективными социальными изменениями крушение классических рационалистических установок. Она по существу указала на границы натуралистической программы. Натуралистическая и антинатуралистическая программы направлены на изучение одного и того же объекта но в соответствии со своей методологией исследовательской программой...
82270. Применение натуралистической и антинатуралистической исследовательских программ ва социально –гуманитарных науках 33.52 KB
  В них присутствуют: натуралистическая парадигма общества основные варианты: механицизм физикализм биологизм географический детерминизм демографический детерминизм общество понимается как жестко-детерминированная система обусловленная влиянием определенных природных факторов климата полезных ископаемых территории и т. оно рассматривается с редукционистских позиций; антинатуралистическая парадигма общества основные варианты: социологизм экономизм психологизм антипсихологизм общество понимается как...
82271. Проблема разделения социальных и гуманитарных наук пол предмету, по методу, по предмету и методу одновременно, по исследовательским программам 34.01 KB
  В настоящее время считается что естественные науки и социально-гуманитарные науки имеют как общие так и различные характеристики. Естественные и социально-гуманитарные науки обладают всеми признаками науки как особого феномена познание нового наличие эмпирического и теоретического уровней оформленность в понятиях и т. Вместе с тем социально-гуманитарные науки отличаются от естественно-математических и технических наук по следующим основаниям: по объекту исследования естественные науки изучают природную реальность т. то что существует...