Для специальности 5В073200 «Стандартизация, сертификация и метрология»


Лабораторная работа № 6. Хранимые процедуры и триггеры



страница10/10
Дата06.06.2016
Размер1.01 Mb.
ТипЛекция
1   2   3   4   5   6   7   8   9   10

Лабораторная работа № 6. Хранимые процедуры и триггеры.



1 Цель работы: научить студентов создавать хранимые процедуры, функции и триггеры к базе данных скриптом и в графической среде СУБД MS SQL Server 2008.
2 Задание на лабораторную работу
Выполните упражнения, приведенные в п.3.1.-3.3.

Создайте процедуры, позволяющие выполнить следующие действия:



  1. Отчислить/Зачислить студента.

  2. Увеличить суммы стипендий всех студентов на 15%.

  3. Ставить студентам оценки за различные виды работ(практика, контрольная, семестровая, курсовой проект, экзамен) по различным предметам.

  4. Найти неуспевающих студентов.

  5. Объединить две группы в одну.

  6. Закрепление преподавателя по предмету за определенными группами, у которых преподаватель ведет предмет (Ввод информации в таблицу Study).

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

  8. Теоретически в БД можно ошибочно внести оценку студенту по предмету, который он не изучает вовсе. Задача: разработать триггер, контролирующий соответствие информации об оценках по предметам (таблицы Progress и Subject) с информацией о предметах изучаемых студентами(таблица Study).

  9. Создать триггер, который бы журналировал действия определенного пользователя БД производимые над какой-либо таблицей в определенный промежуток времени.

  10. Теоретически в БД можно ошибочно ввести стипендию студенту, который закрыл сессию с удовлетворительными оценками. Задача: разработать триггер, контролирующий оценки полученные студентом и наличие его стипендии.

  11. Создать функцию, возвращающую количество студентов в конкретной группе.

  12. Создать функцию, возвращающую количество грантников на конкретной специальности.


3 Методические указания к выполнению лабораторной работы
3.1 Использование процедур

Команды SQL (CREATE TABLE, INSERT, UPDATE, SELECT) дают возможность сообщить базе данных, что делать, но не как делать. Сообщить ядру базы данных, каким образом следует обрабатывать данные, можно посредством составления процедур. Хранимые процедуры – это набор операторов SQL, созданный для удобства использования в программах. Сохраненную процедуру использовать проще, чем каждый раз записывать весь набор входящих в нее операторов SQL. Сохраненные процедуры можно вкладывать одну в другую (уровень вложенности не может превышать 16).

Сохраненная процедура может возвращать значения, выполнять сравнения вводимых пользователем значений с заранее установленными условиями, вычислить какие-либо результаты и т.п.
Некоторые из преимуществ использования сохраненных процедур:


  • операторы процедуры уже сохранены в базе данных;

  • операторы процедуры уже проверены и находятся в готовом для использования виде;

  • при использовании процедур результат получается быстрее;

  • возможность сохранения процедур позволяет использовать модульное программирование;

  • сохраненные процедуры могут вызывать другие процедуры;

  • сохраненные процедуры могут вызываться другими программами.

В SQL Server процедуры создаются с помощью оператора следующего вида:

CREATE PROCEDURE имя_процедуры

[ [ ( ] @имя_параметра

ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ) [, МАСШТАБ ])

[=DEFAULT][OUTPUT] ]

[, @ИМЯ_ПАРАМЕТРА

ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ) [, МАСШТАБ ])

[=DEFAULT][OUTPUT] ]

[WITH RECOMPILE]

AS операторы SQL
Сохраненные процедуры используются следующим образом:

EXECUTE [ @ = ] имя_процедуры

[ [ @ имя_параметра =] значение |

[ @ имя_параметра = ] @ переменная [ OUTPUT ] ]

[WITH RECOMPILE]

Например, мы хотим увеличить на единицу значения номеров курсов (в поле Grup_COURSE) в таблице GRUPPA:



CREATE PROCEDURE new_course

AS UPDATE GRUPPA SET Grup_COURSE = Grup_COURSE +1

Проверим работу процедуры: EXEC new_course



SELECT *

FROM GRUPPA

Вернем таблицу GRUPPA в первоначальное состояние: CREATE PROCEDURE old_course AS UPDATE GRUPPA SET Grup_COURSE = Grup_COURSE -1

Проверим работу процедуры:

EXEC old_course
SELECT *

FROM GRUPPA Пример процедуры, которую можно использовать для добавления новых групп:

CREATE PROCEDURE new_gruppa

( @Grup_ID int ,

@Grup_NAME char (9),

@Grup_KOLSTUD int,

@Grup_COURSE int )

AS

INSERT INTO Gruppa

VALUES (@Grup_NAME, @Grup_KOLSTUD, @Grup_COURSE);

Столбец Grup_ID таблицы GRUPPA имеет тип identity, поэтому определяется только при вводе.


Работа этой процедуры проверяется следующим образом:

EXEC new_gruppa 18, 'ECT-04-5', 25, 1

Обратите внимание, что при вводе данных система автоматически присваивает полю Grup_ID очередное значение независимо от того, какое значение определил пользователь.

Простая процедура по использованию оператора SELECT:

CREATE PROCEDURE spisok_stud AS SELECT * from Students
Процедура для добавления преподавателя:

CREATE PROCEDURE new_teacher

( @Teach_ID bigint,

@Teach_FAM char (20), @Teach_IMA char (10),

@Teach_OTCH char (15),

@Teach_POSITION char (18),

@Teach_STEPEN char (12),

@Chair_ID integer)

AS

INSERT INTO Teacher

VALUES(@Teach_ID,@Teach_FAM,@Teach_IMA, @Teach_OTCH,@Teach_POSITION,@Teach_STEPEN, @Chair_ID)
Работа этой процедуры проверяется следующим образом:

EXEC new_teacher <список параметров>
Процедура для добавления нового предмета:

СREATE PROCEDURE new_subject

( @subj_name char(20),

@lection_hours int,

@practice_hours int,

@labor_hours int)

AS

declare @subj_id int

declare @total_hours int

IF not exists (select subj_name from subject

WHERE subj_name=@subj_name)

begin

SET @subj_id = (select max(subj_id) from subject)+1

SET @total_hours = @lection_hours + @practice_hours + @labor_hours

INSERT INTO subject

VALUES (@subj_id, @subj_name, @total_hours, @lection_hours, @practice_hours, @labor_hours)

end
Работа этой процедуры проверяется следующим образом:

EXEC new_subject '3',20,20,30

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


Процедура добавления нового судента:

CREATE PROCEDURE new_stud

(@stud_id int,

@stud_fam char(20),

@stud_ima char(20),

@stud_otch char(20),

@stud_date datetime,

@stud_addr char(20),

@grup_name char(20),

@grup_kurs int,

@stip smallmoney,

@spec char(20))

AS

declare @grup_id int;

declare @spec_id int;

declare @kolvo int;

INSERT INTO gruppa

VALUES(@grup_name,0,@grup_kurs);

INSERT INTO spec

VALUES(@spec);

SELECT @grup_id=(SELECT grup_id FROM gruppa WHERE grup_name=@grup_name);

SELECT @spec_id=(SELECT id_spec FROM spec WHERE nazv=@spec);

INSERT INTO students

VALUES(@stud_id,@stud_fam,@stud_ima,@stud_otch,@stud_date,@stud_addr,@grup_id,@stud_id,@stip,@spec_id);

SELECT @kolvo=(select count(stud_id) from students where grup_id=@grup_id);

UPDATE gruppa

SET grup_kolstud=@kolvo WHERE grup_id=@grup_id
Работа этой процедуры проверяется следующим образом:

EXEC new_stud 65785,'Иванов','Иван','Иванович', '08/05/89', 'Almaty', 'BVT-77-7',2,1000,'ВЧиПО'


    1. Функции

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

Существующее многообразие встроенных функций SQL Server можно условно разделить на несколько категорий:

- математические функции;

- строковые функции;

- функции для работы с датами;

- статистические функции;

- криптографические функции;

- ранжирующие функции;

- функции приведения типов;

- системные функции.
Каждая функция имеет ряд свойств, определяющих возможность использования ее в вычисляемых столбцах.

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

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

Для создания функции следует использовать оператор CREATE FUNCTION следующего формата:



CREATE FUNCTION имя_функции

([ < @имя_параметра> AS <тип_данных> [=<значение_по_умолчанию>]])

RETURNS <возвращаемый_тип_данных>

AS

операторы SQL
Функция определения возраста студента (например, мы ходим узнать возраст студента по id_stud):
CREATE FUNCTION GetStudentAge(@uid int)

RETURNS varchar(200)

AS

BEGIN

declare @age datetime

SELECT @age=Stud_DATE

FROM Students WHERE Stud_ID=@uid

SET @age=GETDATE()-@age;

RETURN convert(varchar(20),YEAR(@age)-1900)+' лет, '+convert(varchar(20),MONTH(@age)-1)+' месяцев и '+convert(varchar(20),DAY(@age)-1)+' дней'

END
Данную пользовательскую функцию можно использовать в следующем запросе:
SELECT Stud_FAM as [Фамилия], Stud_IMA as [Имя], Stud_DATE as [Дата рождения], dbo.GetStudentAge(Stud_ID) as [Возраст]

FROM Students

ORDER BY Stud_DATE ASC
Т.о., создавая пользовательские функции, можно значительно упростить сложность запросов.
3.3 Триггеры

Триггер – это откомпилированная процедура, используемая для выполнения действий, инициируемых происходящими в базе данных событиями. Триггер представляет собой сохраненную в базе данных процедуру, которая запускается тогда, когда в отношении таблицы выполняются определенные действия. Триггер может выполняться до или после операторов INSERT, UPDATE или DELETE. Наиболее распространенное применение триггеров – это проверка сложных критериев в базе данных. Триггер - особый инструмент SQL-сервера, поддерживающий целостность данных в БД .

Рассмотрим простой пример. Стипендия студента не может превышать 15000. Определим триггер, который не позволял бы вводить сумму стипендии больше 15000. Для создания триггера с помощью утилиты SQL Server Enterprise Manager необходимо выбрать таблицу Progress в списке объектов базы данных, после чего выполнить команду контекстного меню Аll tasks (Все задачи) - Manage Triggers. Эти действия приведут к открытию диалогового окна свойств триггера.


CREATE TRIGGER tri_stip

ON stud

FOR INSERT, UPDATE

AS

/*описываются локальные переменные*/

DECLARE @stip smallmoney

/* определяется информация о вставляемых записях*/

SELECT @stip = U.stud_stip

FROM Inserted U

IF @stip >15000

BEGIN

/*Команда ROLLBACK используется для того, чтобы отменить модификацию данных в случае, если база заблокирована*/

ROLLBACK TRAN

RAISERROR ('Стипендия студента не может превышать 15000', 16,10)

END
Определим триггер tri_ins_progress для таблицы Progress, который будет запускаться каждый раз, когда запись вставляется в таблицу Progress или модифицируется. Если экзамен или зачет сданы не в срок (например, после 15-го числа месяца), то запись не принимается:

CREATE TRIGGER tri_ins_progress

ON Progress

FOR INSERT, UPDATE

AS

/*описываются локальные переменные*/

DECLARE @nDayOfMonth TINYINT

/* определяется информация о вставляемых записях*/

SELECT @nDayOfMonth = DATEPART (Day, I.Pr_DATE)

FROM Progress P, Inserted I

WHERE P.Stud_ID = I.Stud_ID AND P.Ocenka = I.Ocenka

/*проверяется условие вставки записи*/

/*и при необходимости сообщается об ошибке*/

IF @ nDayOfMonth >15

BEGIN

/*Команда ROLLBACK используется для того, чтобы отменить

модификацию данных в случае, если база заблокирована*/

ROLLBACK TRAN

RAISERROR ('Вводить оценки, полученные до 15-го числа', 16,10)

END
Попробуйте ввести в таблицу Progress данные об оценках, полученных

студентами позже 15-го числа.

Триггер можно удалить следующим образом: DROP TRIGGER имя_триггера

или из контекстного меню Аll tasks (Все задачи) - Manage Triggers выбором имени триггера из списка.



Триггер на добавление студента:

CREATE TRIGGER tr_ins_stud

ON Students

FOR INSERT

AS

DECLARE @grup integer

SELECT @grup=I.Grup_ID

FROM Inserted I

UPDATE Gruppa

SET Grup_KOLSTUD = Grup_KOLSTUD+1

WHERE Gruppa.Grup_ID=@grup
Пусть нам необходимо знать, кто (пользователь) и когда производил изменения оценок студентов. Для этого необходимо создать таблицу журнала и собственно тригер.
Создайте таблицу для журналирования изменений

CREATE TABLE journ (

mod_oper CHAR(20), /* Тип производимой операции */

mod_datetime DATEtime, /*Дата изменеия */

mod_user VARCHAR(30), /*Пользователь БД */

mod_id INTEGEr, /* id студента чья оценка была изменена*/

mod_ocen integer /* Измененая оценка*/

old_ocen integer

)
Тригер, регистрирующий изменения оценки:

CREATE TRIGGER treg

ON Progress

FOR Update

AS

DECLARE @id int, @ocen int, @old_ocen int

SELECT @old_ocen=P.ocenka,@id = P.stud_id , @ocen = U.ocenka

FROM Progress P, Inserted U

INSERT INTO journ VALUES('Обновлена',Current_timestamp,Current_USER,@id, @ocen,@old_ocen);
Данный тригер будет вносить информацию о изменениях оценок студентов.
Контрольные вопросы

  1. Может ли сохраненная процедура вызывать другую сохраненную процедуру?

  2. В чем преимущества использования процедур?

  3. В чем преимущества использования функций?

  4. Каковы различия между процедурами и функциями?

  5. Когда выполняются триггеры – до или после выполнения команд INSERT, UPDATE и DELETE?

  6. Можно ли изменить триггер?




Поделитесь с Вашими друзьями:
1   2   3   4   5   6   7   8   9   10


База данных защищена авторским правом ©uverenniy.ru 2019
обратиться к администрации

    Главная страница