GROUP BY P.NrecordBook,StName,IDGroup) a

INNER JOIN

(SELECT IDGroup,AVG(Mark) Bmark

FROM Progress P

INNER JOIN Student s

on P.NRecordBook=S.NRecordBook

GROUP BY IDGroup) b

ON a.IDGroup=b.IDGroup

WHERE Amark=Bmark

Результат реализации запроса:

Задание 25

Вывести имена студентов отличников и название предметов, по которым они сдавали экзамены.

Задание 26

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

Подзапрос в команде INSERT

Задача.

Добавить в таблицу Student записи из таблицы Student1.

Решение:

INSERT INTO Student

SELECT NRecordBook, Sname, CodeGroup

FROM Student1;

Или, если порядок столбцов в обеих таблицах одинаковый,

INSERT INTO Student

SELECT *

FROM Student_1;

Подзапрос в команде UPDATE

Пример 75

Задача.

Внести в столбец MarkAVG таблицы Student среднюю оценку студента. Предварительно нужно создать такой столбец в таблице Student с помощью команды:

alter table Student

add MarkAVG decimal(3,2)

Решение:

UPDATE Student

SET MarkAVG =

(SELECT AVG(Mark)

FROM Progress

GROUP BY NRecordBook

HAVING Student. NRecordBook= Progress. NRecordBook);

Просмотрим результат с помощью запроса:

Select StName ФИО, MarkAVG [Средняя оценка]

from Student

Результат реализации запроса:

Подзапрос в команде DELETE

Пример 76

Задача.

Удалить из таблицыStudent студентов тех групп, которые встречаются в таблице Progress меньше 5 раз.

Решение:

DELETE FROM Student

WHERE IDGroup IN

(SELECT IDGroup

FROM Progress Pr, Student St

WHERE Pr. NRecordBook= St.NRecordBook

GROUP BY IDGroup

HAVING Count(IDGroup)< 5);

Объединение двух или более запросов с помощью UNION

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

Основные правила при реализации операций над множествами

· запросы, соединяемые оператором UNION должны иметь одинаковое количество столбцов в предложении SELECT;

· возвращаемый комбинированный результат будет иметь заголовки столбцов первого предложения SELECT;

· тип данных каждого столбца должен быть совместим с типом данных соответствующего столбца другого запроса;

· по умолчанию режимом вывода для UNION является DISTINCT.

Пример 77

Задача.

Получить все записи о студентах, фамилии которых начинаются на букву 'М' или 'И'.

Решение.

SELECT NRecordBook,StName

FROM Student

WHERE SUBSTRING(StName,1,1)= 'М'

UNION

SELECT NRecordBook,StName

FROM Student

WHERE SUBSTRING [7] (StName,1,1)= 'И'

Результат выполнения запроса.

Результатом выполнения этой операции будет вывод только уникальных имен студентов, ни одно имя не будет повторено дважды.

select StName

FROM Student

WHERE StName='Иванов И.И.'

UNION all

select StName

FROM Student

WHERE SUBSTRING(NRecordBook,6,1)='1'

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

Результат выполнения запроса.

Предложение ORDER BY в операциях над множествами может стоять только в последнем предложении запроса, при этом вместо имен столбцов используются их номера из предложения SELECT.

Пример 78

Задача.

Вывести всех студентов, у которых есть или отличные или хорошие оценки.

Решение.

SELECT StName ФИО, Mark Оценка

FROM Student s, Progress p

WHERE s.NRecordBook=p.NRecordBook

AND Mark=5

UNION

SELECT StName,Mark

FROM Student s,Progress p

WHERE s.NRecordBook=p.NRecordBook

AND Mark=4

ORDER BY 1

Результат выполнения запроса.

 

Задание 27

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

Вопросы для самоконтроля к лабораторной работе № 5

1. Что такое подзапрос?

2. В каких командах SQL могут быть использованы подзапросы?

3. Какой порядок выполнения подзапроса?

4. Когда невозможно использовать подзапрос?

5. Какой порядок выполнения коррелированных подзапросов?

6. В каких случаях подзапросы могут передать несколько строк в главное предложение?

7. Сколько раз выполняется основной запрос при реализации коррелированного подзапроса?

8. Где расположен подзапрос в логическом выражении предложения основного запроса: до оператора сравнения, после оператора сравнения?

9. Какие предложения допустимые для основного запроса не могут использовать в подзапросе?

10. Какие существуют типы подзапросов?

11. В каких командах SQL могут быть использованы подзапросы?

12. В каких предложениях SELECT могут быть использованы подзапросы?

13. Какие предложения SELECT нельзя использовать в подзапросах?

14. Где должен находиться подзапрос?

15. Какие операторы сравнения работают с несколькими строками?

16. Какие операторы сравнения работают только с одной строкой?

17. В каких подзапросах вложенная команда SELECT выполняется первой?

18. Какие подзапросы называются однострочными?

19. Какой принцип работы коррелированного подзапроса?

20. Какие требования предъявляются к таблицам, над которыми выполняются операторы union?

 

 

 

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

Цель занятия: Знакомство с объектами СХЕМЫ БАЗЫ ДАННЫХ. Создание и использование представлений.

Представления

Представление – это шаблон, через который можно просматривать базу данных. Его можно также назвать виртуальным отношением, которое не существует на самом деле, но которое динамически воспроизводится на основании запроса к одной или нескольким таблиц или к другим (или другому) представлениям. Представления не хранят каких-либо данных. В словаре данных хранится только предложение SELECT того или иного представления.

Синтаксис команды:

CREATE VIEW <имя представления>

[(<имя столбца> [,…n ])

[WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA}]

AS

< команда SELECT>

[WITH CHECK OPTION];

WITH CHECK OPTION – ограничивает действие команд INSERT и UPDATE. При задании этого предложения они разрешены только в том случае, если они создают строки, которые потом видны в представлении.

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

При создании представления SQL не осуществляет контроль за типом переменных, то есть представление будет создано без генерации сообщения об ошибке, но при попытке осуществить запрос будет сгенерировано сообщение об ошибке.

Ранее отмечалось, что опция WITH CHECK OPTION может ограничить действие команд INSERT и UPDATE, однако существуют и другие ограничения на использовании команд DML.

Команда DELETE запрещена, если представление содержит:

· условие соединения

· групповые функции или предложение GROUP BY

· предложение DISTINCT

· столбец со свойством IDENT I TY

Команда UPDATE запрещена всегда, когда запрещена команда DELETE, а также в случае, когда столбцы содержат выражения.

Команда INSERT запрещена всегда, когда запрещена команда UPDATE, а также тогда, когда какой-либо столбец таблицы NOT NULL не содержится в представлении.

После успешного завершения команды CREATE VIEW выдается сообщение

View created (Представление создано).

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

· ограничения доступа к базе данных;

· упрощения запросов;

· сокрытия схемы базы данных.

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

Пример 79

Задача.

Создать представление, предотвращающее ввод информации в таблицу Student.

Решение.

Как уже отмечалось выше, для реализации подобного требования достаточно исключить из представления хотя бы один атрибут с признаком NOT NULL, например IDGroup.

CREATE VIEW VStudentNOINSERT

AS

SELECT

NRecordBook,

INN,

StName,

SPasport,

NPasport,

DataPasport,

NameDeptPasport

FROM Student

Попытка ввести данные через созданное представление

INSERT INTO VStudentNOINSERT

VALUES('050008','1111111117','Ипатов В.И.','8701','192304','01.06.2002', 'УВД г.Ухты');

спровоцирует сообщение об ошибке (если при создании Вы запретили неопределённые значения в столбце IDGroup):

Server: Msg 515

Cannot insert the value NULL into column 'IDGroup', table 'Student.dbo.Student'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Пример 80

Задача.

Создать представление, разрешающее просмотр, ввод и редактирование только данных о студентах группы ИСТ-03 (IDGroup=1) в таблице Student.

Решение.

Создадим представление VStudentNOINSERTIST03, используя опцию WITH CHECK OPTION

CREATE VIEW VStudentNOINSERTIST03

AS

SELECT

NRecordBook,

INN,

StName,

IDGroup,

SPasport,

NPasport,

DataPasport,

NameDeptPasport

FROM Student

WHERE IDGroup =1

WITH CHECK OPTION

Теперь попытка ввести через представление VStudentNOINSERTIST03 данных о студенте группы АИС-03 (IDGroup=2)

INSERT INTO VStudentNOINSERTIST03

VALUES('050007','1111111118','Васин В.И.', 2, '8701', '192314', '01.06.2002', 'УВД г.Ухты');

Приведет к появлению следующего сообщения об ошибке:

Server: Msg 550

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

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

Пример 81

Задача.

Создать представление для вывода всей данных об успеваемости студентов.

Решение.

Приведенное ниже представление не только значительно уменьшит сложность реализуемых через него к базе данных запросов, но и позволит пользователю апеллировать привычными для него понятиями: Название дисциплины, Имя студента и т.п. Кроме того, оно полностью скрывает истинную структуру базы данных.

CREATE VIEW VPROGRESS

AS

SELECT

NameGroup [Группа],

S.NRecordBook [№ зачетки],

StName [Имя студента ],

NameSubject[Название дисциплины],

NameReport[Вид отчетности],

NTerm[№ семестра],

PIN [Персонифицированный номер преподавателя],

Mark[Оценка]

FROM Progress P INNER JOIN Student S

ON P.NRecordBook=S.NRecordBook

INNER JOIN SGroup SG

ON S.IDGroup=SG.IDGroup

INNER JOIN Subject SB

ON P.IDSubject=SB. IDSubject

INNER JOIN Report R

ON P.IDReport=R.IDReport

Реализация запроса

SELECT *

FROM VProgress;

к представлениюVProgress позволит получить следующий результат:

Результат выполнения запроса.

Пример 82

Задача.

Вывести успеваемость студентов по дисциплине СУБД в шестом семестре.

Решение.

SELECT *

FROM VProgress

WHERE [Название дисциплины]='СУБД'

AND [№ семестра]=6

Нетрудно представить, как бы выглядел этот запрос, обращенный непосредственно к таблицам

SELECT

NameGroup [Группа],

S.NRecordBook [№ зачетки],

StName [Имя студента ],

NameSubject[Название предмета],

NameReport[Вид отчетности],

NTerm[№ семестра],

PIN [Персонифицированный номер преподавателя],

Mark[Оценка]

FROM Progress P INNER JOIN Student S

ON P.NRecordBook=S.NRecordBook

INNER JOIN SGroup SG

ON S.IDGroup=SG.IDGroup

INNER JOIN Subject SB

ON P.IDSubject=SB. IDSubject

INNER JOIN Report R

ON P.IDReport=R.IDReport

WHERE NameSubject='СУБД'

AND NTerm =6

Результат выполнения запроса.

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

Пример 83

Задача.

Вывести средний балл по каждой группе студентов в первом семестре.

Решение.

SELECT Группа, AVG(Оценка)

FROM VProgress

WHERE [№ семестра]=6

GROUP BY Группа

Результат выполнения запроса.

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

Пример 84

Задача.

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

Решение.

CREATE VIEW VPROGRESSTEACHER

AS

SELECT Группа,

[№ зачетки],

[Имя студента],

[Название дисциплины],

[Вид отчетности],

[№ семестра],

[Персонифицированный номер преподавателя],

TeacherName [Имя преподавателя],

Оценка

FROM VProgress P INNER JOIN Teacher T

ON P.[Персонифицированный номер преподавателя]=T.PIN

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

Задание 28

Создать представление для просмотра базы данных с целью определения успеваемости студентов группы АИС-03.

Задание 29

Создать представление для вывода ведомостей успеваемости студентов.

Задание 30

Создать представление для вывода информации о предметах, изучаемых в 3 семестре студентами специальности ИСТ.

Вопросы для самоконтроля к лабораторной работе № 6

1. Что такое представление?

2. С какой целью создаются представления?

3. В чем отличие простых и сложных представлений?

4. Какое предложение, включенное в команду Create View, позволяет создавать представление в отсутствии базовых таблиц?

5. Какие предложения не может содержать запрос, определяющий представления?

6. Какие представления позволяют выполнять все без исключения операции DML?

7. Когда через представления нельзя выполнить операции удаления?

8. Как реализуется запрос на выполнение операции DML при создании представления?

9. Когда в представлении нельзя выполнить операцию добавления, но можно выполнить операцию удаления?

10. Допускается ли изменение представления?

11. В каких случаях нельзя изменить данные в представлении?

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

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

Цель занятия: Знакомство с объектами СХЕМЫ БАЗЫ ДАННЫХ. Создание и использование хранимых процедур.

Хранимые процедуры

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

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

При создании процедур необходимо придерживаться следующих правил: во время выполнения хранимой процедуры все объекты, на которые она ссылается, должны присутствовать в базе данных. В хранимых процедурах нельзя применять операторы создания объектов: CREATE PROCEDURE, CREATE TRIGGER, CREATE VIEW.

Прежде, чем выполнить хранимую процедуру SQL Server 2000 генерирует для нее план исполнения, выполняет оптимизацию хранимой процедуры и компилирует ее. В дальнейшем этот план и откомпилированный код кэшируются. При повторном вызове процедуры, сервер уже использует готовый план и откомпилированный код процедуры, экономя за счет этого ресурсы (см. Рисунок 4). Недостатком данного метода является то, что процедура может быть оптимизирована «не на тех данных», и план запуска оптимальный на начальном наборе данных, может быть не оптимален на всех последующих. В этих случаях используется принудительная перекомпиляция при каждом запуске процедуры.

Существует несколько типов хранимых процедур, реализуемых на SQL Server 2000

1. Системные хранимые процедуры

2. Пользовательские хранимые процедуры

3. Локальные временные хранимые процедуры

4. Глобальные временные хранимые процедуры.

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

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

Локальные и глобальные временные хранимые процедуры хранятся в базе данных tempdb. Имя локальных процедур начинается с #, имя глобальных процедур - ##. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором они созданы, глобальные – из любого соединения сервера, на котором была создана эта процедура. Так как эти процедуры временные, то они удаляются при перезапуске или остановке сервера, при закрытии соединения, в контексте которого они были созданы.

Созданию хранимой процедуры должны предшествовать работы, связанные с определением типа создаваемой процедуры, планированием прав доступа и определением параметров.

 

 

Разработка

Добавляется к sysobjects sysdepends syscomments

Рисунок 4

Синтаксис команды:

CREATE PROC[EDURE ] имя_процедуры [; число ]

[ { @параметр тип_данных }

[ VARYING ] [ = значение_по_умолчанию ] [ OUTPUT ] ] [,...n ]

[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ]

AS оператор_SQL [...n ]

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

VARYING – определяет, что выходным параметром будет результирующее множество (используется совместно с параметром OUTPUT).

RECOMPILE – предписывает выполнять перекомпиляцию кода процедуры при ее запуске.

ENCRUPTION – предписывает серверу выполнить кодирование процедуры, сделав ее содержимое недоступным для пользователя. Автору процедуры при этом надо позаботиться о наличии у него исходного текста процедуры.

AS – индикатор начала собственно кода процедуры.

Вызов процедуры (исключение составляют триггеры) осуществляется по команде EXEC.

Синтаксис команды EXEC

EXEC <Имя процедуры> [<список параметров>]

Однако прежде, чем приступить к созданию хранимых процедур познакомимся с некоторыми операторами Transact-SQL и основными понятиями, связанными с его реализацией

Во-первых, познакомимся с понятием сценарий (SCRIPT), представляющим собой последовательность действий, выполняемых целиком и сохраненных в файле. Примером сценария может быть любая команда языка DDL или DML

Во-вторых – с понятием пакет (BATH), который есть совокупность процедур, функций, переменных и операторов языка SQL, которые сгруппированы вместе и хранятся в виде единого программного блока. Все операторы внутри пакета комбинируются в единый план исполнения (execution plane) и пока все операторы не будут успешно проанализированы синтаксическим анализатором, ни один из операторов пакета не будет выполнен. Если некоторый оператор в пакете вызвал ошибку на этапе выполнения программы, то выполняются все операторы до ошибочного. Пакеты отделяются друг от друга с помощью оператора GO, который не является командой T-SQL, это директива и на сервер не пересылается. Оператор GO должен писаться в отдельной строке и ничего кроме комментариев не должно следовать за ним. Все операторы от начала сценария до GO компилируются в один пакет и пересылаются на сервер отдельно от других пакетов.

Оператор USE

Синтаксис оператора USE

С помощью оператора USE устанавливается текущая база данных.

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

Оператор DECLARE

Синтаксис оператора DECLARE

DECLARE @<имя переменной><тип переменной>[,…]

DECLARE @IPIN int

Одним оператором DECLARE может быть объявлена как одна, так и несколько переменных. Объявленное значение переменной не определено (NULL) пока ей не будет присвоено значение. Присвоение переменной того или иного значения осуществляется либо с использованием оператора SET, либо SELECT.

Операторы SET и SELECT

Синтаксис оператора SET

SET <имя переменной>=<значение переменной>

Синтаксис оператора SELECT

SELECT <имя переменной> = <значение переменной>

Пример 85

Задание.

Присвоить значения переменным, используя оператор SET

Решение.

declare @AvgMark int

declare @TotalSumma decimal

declare @MSumma decimal

SET @AvgMark=4

SET @TotalSumma=@MSumma*1.8

SET @AvgMark=(Select AVG(Mark) FROM Progress)

Различия между использованием SET и SELECT при присвоении значения переменной практически не существует. Когда применять тот или иной оператор решать пользователю. Однако, поскольку, при присвоении переменной значения полученного из запроса, код запроса несколько короче при использовании SELECT, то его в этом случае чаще всего и применяют. То есть, SET используют для присвоения известного значения переменной, SELECT – для присвоения переменной результата запроса.

 

Use Student

DECLARE @AvgMark Numeric (5,2)

SET @AvgMark= AVG(Mark) FROM Progress

SELECT @AvgMark

SQL Server 2000 выдаёт ошибку:

Incorrect syntax near the keyword 'FROM'.

Но!!!

Use Student

DECLARE @AvgMark Numeric (5,2)

SET @AvgMark=(SELECT AVG(Mark) FROM Progress)

SELECT @AvgMark

Результат выполнения запроса.

Наиболее часто применимый код

Use student

DECLARE @AvgMark Numeric (5,2)

SELECT @AvgMark= AVG(Mark) FROM Progress

SELECT @AvgMark

 

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

· @@IDENTITY

· @@ERROR

Функция @@IDENTITY

Функция @@IDENTITY - возвращает автоматически сгенерированное последним оператором значение. Необходима в тех случаях, когда применяются столбцы с уникальным сгенерированным значением и существует ссылка на них в дочерних таблицах. Хранит только последнее вставленное значение, предыдущее значение не будет сохранено, если его предварительно не записали в переменную. Если при выполнении последней команды INSERT ни один вставленный столбец не будет иметь уникального значения, то @@IDENTITY будет иметь признак NULL.

Функция @@ERROR

Функция @@ERROR содержит код ошибки последнего выполняемого в текущем подключении оператора T-SQL. Код ошибки есть целое число (Integer). Если ошибка отсутствует, то значение переменной равно 0. Жизненный цикл переменной ограничивается одним оператором. Если необходимо проверить ошибку в конкретном операторе, то это следует делать в операторе, непосредственно следующем за данным, либо сохранить значение системной переменной для дальнейшего использования

Перечень ошибок хранится в базовой таблице SYSMESSAGES базы данных MASTER. Для добавления собственного списка ошибок используют хранимую процедуру sp_addmessage.

Пример 86

Задание.

Создать хранимую процедуру, которая создаёт список студентов и их номеров зачёток и упорядочивает их по имени.

Решение:

CREATE PROCEDURE NameStudent

as

SELECT StName ФИО,NRecordBook [№ зачётки]

FROM Student

ORDER BY StName DESC

EXEC NameStudent

Результат выполнения запроса:

 

Объявление параметров

При объявлении параметров указывается следующая информация

· Имя

· Тип данных

· Значение по умолчанию

· Направление (входная или выходная).

Если параметру не присвоено значение по умолчанию, то начальное значение должно быть присвоено при вызове процедуры. Если значение задано по умолчанию, то у пользователя остается право его изменить

Пример 87

Задание.

Создать хранимую процедуру, которая выводит содержимое таблицы Student для заданной группы, по умолчанию выводятся сведения о студентах группы АИС-03.

Решение:

USE STUDENT

GO

CREATE PROC PStudent

@NameGroup VARCHAR (20)='АИС-03'

AS

SELECT *

FROM Student ST

Inner JOIN SGroup SG

ON ST. IDGroup=SG. IDGroup

WHERE NameGroup=@NameGroup

 

При вызове EXEC PStudent имеем,

продолжение таблицы

Пример 88

Задание.

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

Решение:

USE STUDENT

GO

CREATE PROC PStudent

@NameGroup VARCHAR (20) output

AS

SELECT *

FROM Student ST

Inner JOIN SGroup SG

ON ST. IDGroup=SG. IDGroup

WHERE NameGroup =@NameGroup

При вызове процедуры с помощью оператора EXEC PStudent, будет выдано сообщение об ошибке.

Server: Msg 201, Level 16, State 4, Procedure PSTUDENT, Line 0

Procedure 'PSTUDENT' expects parameter '@NameGroup', which was not supplied.

При вызове EXEC PStudent 'ИСТ-03' имеем,

Результат выполнения запроса:

продолжение таблицы

Пример 89

Задание.

Создать хранимую процедуру, которая вводит данные в таблицу Student.

Решение:

USE Student

GO

create proc PStudentINSERT

@NRecordBook varchar(6),

@INN varchar(10),

@StName varchar(35),

@IDGroup Int,

@SPasport varchar(4),

@NPasport varchar(6),

@DataPasport DateTime,

@NameDeptPasport varchar(35)

AS

INSERT INTO Student

(NRecordBook,INN,StName,IDGroup,SPasport,NPasport,

DataPasport,NameDeptPasport)

VALUES

(@NRecordBook,@INN,@StName,@IDGroup,@SPasport,@NPasport,

@DataPasport,@NameDeptPasport)

Обратимся к вновь созданной процедуре:

EXEC PStudentINSERT

'050008',

'1111111110',

'Ипатов В.И.',

3,

'8701',

'192320',

'01.11.2002',

'ГОВД г.Ухты'

Результат выполнения процедуры PStudentTINSERT можно увидеть, обратившись к процедуре PStudentView, созданной ниже (см. Пример 90)

EXEC PStudentView

Результат выполнения запроса:

продолжение таблицы

 

Пример 90

Задание.

Создать хранимую процедуру, выводящую для просмотра содержимое таблицы Student.

Решение:

USE Student

GO

CREATE PROC PStudentView

AS

SELECT *

FROM STUDENT

Изменение хранимых процедур

Изменение хранимой процедуры, осуществляется аналогично изменению любого объекта базы данных с помощью оператора ALTER TABLE. Различие между использованием ALTER PROC и CREATE PROC заключается в том, что

ALTER PROC – подразумевает, что хранимая процедура уже существует, CREATE – нет.

ALTER PROC – оставляет неизменными все установленные для хранимой процедуры права доступа.

ALTER PROC – сохраняет ID объекта и позволяет сохранять зависимости между процедурами.

ALTER PROC – оставляет неизменной любую информацию об отношениях с другими объектами, которые могут вызвать изменяемую процедуру (см. Рисунок 5).

Синтаксис команды ALTER TABLE полностью аналогичен синтаксису команды CREATE TABLE. С той разницей, что опция CREATE заменена на опцию ALTER.

Рисунок 5

Удаление хранимой процедуры

Синтаксис команды

DROP PROCEDURE| PROC <имя процедуры>

Пример 91

Задача.

Удалить процедуру PStudent.

Решение.

DROP PROC PStudent

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

Цель занятия: Знакомство с объектами СХЕМЫ БАЗЫ ДАННЫХ. Создание и использование триггеров.

Триггер – это специальный тип хранимой процедуры, которая автоматически выполняется при возникновении некоторого события (попытке выполнить операции удаления, добавления, редактирования). Как и любой объект базы данных триггер создается с помощью команды CREATE.

Cинтаксис команды создания триггера

CREATE TRIGGER <имя триггера>

ON <имя представления или таблицы>

[WITH INCRYPTION]

{{{FOR|AFTER}<[DELETE][,][INSERT] [,][UPDATE]>}|INSTEAD OF}

[WITH APPEND]

[NOT FOR REPLICATION]

AS

<SQL –ОПЕРАТОР

….

….

….>,

где

ON - имя объекта, для которого триггер используется

WITH INCRYPTION – кодирует текст представления.

WITH APPEND – используется для совместимости с версией 6.5

NOT FOR REPLICATION – меняет правила запуска триггера. Такой триггер не будет стартовать при выполнении над таблицей операций связанных с репликацией данных.

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

Таблица может иметь произвольное количество триггеров любых типов (INSERT, UPDATE, DELETE). По умолчанию триггер выполняется, когда изменение данных завершено; если же указать опцию INSTEAD OF, то создаётся триггер, выполняющийся вместо изменения данных.

Изменить триггер можно с помощью оператора ALTER TRIGGER.

В Microsoft SQL Server 2000 используются два типа триггеров AFTER (после) и INSTEAD OF (вместо), а также три их типа: INSERT, UPDATE, DELETE. Триггеры вставки INSERT – стартуют каждый раз при добавлении в таблицу новой записи при этом создается таблица INSERTED. Триггеры удаления DELETE – стартуют каждый раз при удалении из таблицы записи и, как следствие этого, создается таблица DELETED. Триггер правки UPDATE – стартует при внесении изменений в существующие записи таблицы, так как при правке выполняются две операции удаления и вставки, то и служебных таблиц создается две INSERTED и DELETED.

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

По умолчанию все триггеры (INSERT, UPDATE, DELETE) срабатывают после выполнения оператора изменения данных – это триггеры AFTER. Триггеры АFTER не используются для представлений. Кроме того, в SQL Server 2000 используются триггеры INSTEAD OF, которые выполняются вместо оператора предполагаемого изменения данных. Триггеры всегда составляют часть транзакции. Если триггер (или другая часть транзакции) терпит неудачу, то транзакция отменяется.

Существует как минимум три причины, по которым используются триггеры.

Во-первых, триггер может быть запущен независимо от разрешившего его приложения для отмены подозрительных действий. Например, один из приведенных ниже триггеров (см. Пример 93) выполнит откат транзакции при попытке обновить данные в столбце оценка после окончания сессии.

Во-вторых, триггер может контролировать ограничения целостности. В прошлом триггеры были единственным средством обеспечения ссылочной целостности. Начиная с SQL Server 7, появилась возможность использовать средства декларативной ссылочной целостности, которые более надежны и предпочтительнее. Отметим три случая, когда триггер все-таки может иметь место:

· по деловому регламенту требуется ссылаться на данные из отдельной таблицы;

· в соответствии с регламентом необходимо проверять дельту (разницу между величинами до и после модификации);

· возникает необходимость в написании собственных сообщений об ошибках.

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

Пример 92

Задача.

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

Решение.

CREATE TRIGGER ProgressTerm

ON PROGRESS

FOR INSERT, UPDATE, DELETE

AS

IF EXISTS

(SELECT 'TRUE'

FROM Progress

WHERE (DATEPART(mm,getDATE())<>'01' AND NTerm %2=1)

OR (DATEPART(mm,getDATE())<>'06' AND NTerm %2=0))

BEGIN

RAISERROR('Нельзя исправлять оценку!!!',20,1)

/*-- Откат транзакции в случае возникновения ошибки*/

ROLLBACK TRAN

END

Теперь любая попытка вставить или изменить данные в период отличный от оговоренного, например попытка 14 декабря выполнить действия

UPDATE Progress SET mark=2 WHERE NRecordBook='050001'

INSERT INTO progress VALUES ('050001',1,2,1,4,5)

вызовет сообщение:

Server: Msg 50000

Нельзя исправлять оценку!!!

Пример 93

Задача.

Создать триггер, запрещающий изменять записи для нечетного семестра всегда, кроме января, для четных семестров всегда кроме июня.

Решение.

CREATE TRIGGER ProgressTerm

ON PROGRESS

FOR INSERT, UPDATE, DELETE

AS

IF EXISTS

(SELECT 'TRUE'

FROM progress

WHERE (DATEPART(mm,getDATE())<>'01' AND NTerm %2=1)

OR (DATEPART(mm,getDATE())<>'06' AND NTerm %2=0))

BEGIN

RAISERROR('Сессия завершена! Правка запрещена!!!',16,1)

/*-- Откат транзакции в случае возникновения ошибки*/

ROLLBACK TRAN

END

Теперь попытка ввода или редактирования данных в период между сессиями:

update Progress SET mark=2 WHERE NRecordBook='050001'

INSERT INTO Progress

VALUES ('050001',1,2,1,4,5)

UPDATE Progress SET mark=2

WHERE NRecordBook='050001'

потерпит неудачу и будет выдано сообщение

Server: Msg 50000

Сессия завершена! Правка запрещена!!!

Пример 94

Задача.

Написать триггер, удаляющий строки в таблице Progress относящиеся к записям удаляемым из отношения Student.

Решение

CREATE TRIGGER StudentProgress

ON Student

FOR INSERT, UPDATE, DELETE

AS

DECLARE @COUNT int

SELECT @COUNT=COUNT(*) FROM DELETED

-- Проверяем удалялись ли из главной таблицы Student какие-либо записи.

-- Если да, то удаление необходимо выполнить и из зависимой таблицы

IF @COUNT>0

BEGIN

DELETE FROM PROGRESS