Информатика и выч. техника \ Информационные технологии в менеджменте

Запросы с подзапросами. Функции определяемые пользователем. Курсоры: Лабораторная работа № 6 по курсу «Информационные технологии в менеджменте», страница 2

Запрос. Получал ли ученик Иванов хотя бы одну оценку за указанный период.

Пользовательские функции.

Виды

·  скалярной функции

Упрощенная команда создания скалярной функции

create function имясхемы.имя функции(переменная-параметр тип[,переменная-параметр тип[,…..] Returns тип результата

As

begin

return выражение

end

·  табличная

Упрощенная команда создания табличной функции

create function имясхемы.имя функции(переменная-параметр тип[,переменная-параметр тип[,…..] Returns Table

As

return select выражение

Задание.16

·  Создайте скалярную пользовательскую функцию с именем Con_data_str c параметром типа datetime и результатом типа varchar(12), которая преобразует дату типа datetime в строковый формат с помощью встроенной функции CONVERT(varchar(12), выражение типа datetime, 101)

·  Откройте окно редактора запросов

·  Создайте запрос на создание функции

·  Выполните его

·  Проверьте, что функция создана. Для этого откройте папку Programmpility->functions->Scalar_valued  папки  БД школы.

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

·  Создайте запрос. Используйте в условии отбора функцию GetDate().

·  Проверьте результаты

·  Результата не получен, так как поле Data определено на типе datetime, которе помимо даты включает и время.

·  Измените запрос и преобразуйте обе даты в запроса в строку с помощью функции Con_data_str.

·  Вновь выполните запрос. Результат должен быть получен.

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

create function dbo.Ichenic(@date datetime) Returns varchar(12)

As

begin

return CONVERT(varchar(12),@date,  101)

end

Задание 17. Создайте таблицу успеваемости школьника по всем дисциплинам его класса. В таблицу включите поля: название дисциплины, средний балл по этой дисциплине. Создание таблицы выполните посредством табличной функции.

·  Создайте с помощью дизайнера запрос на формирование табеля ученика согласно запросу следующим образом;

·  Добавьте в запрос необходимые таблицы. Проверьте правильность связей.

·  Установите в запросе правила группировки. Для этого в контекстном меню окна дизайнера выберите команду AddGroupBy.

·  Выберите группировку только по столбцам Advencement.idD, Desceplene.Name. Для столбца оценок  установите в ячейке группировки функцию avg. Из остальных столбцов удалите группировку. Проверьте текст запроса. Закройте окно дизайнера.

·  Установите условие отбора по id ученика. Пока выберем данные по ученику с id=1(в функции это будет параметр).

·  Выполните ОК. Выполните запрос.

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

·  Выполните запрос на создание. Удостоверьтесь в его создании в папке Programmpility->functions->Table _valued.

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

Пример вызова функции.

select *

from  dbo.TabelUspev(1)

Пример запроса на создание функции

create function dbo.TabelUspev(@id_piple int) Returns Table

As

Return (SELECT Advencement.idD,   Desceplene.Name AS Дисциплина, AVG(Advencement.oc) AS Оценка

FROM  Advencement INNER JOIN

piple ON Advencement.id = piple.id

INNER JOIN Desceplene ON Desceplene.idDisciplene = Advencement.idD

WHERE (piple.id = @id_piple)

GROUP BY Advencement.idD,Desceplene.Name )

Создание курсора для приложения

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

·  Создайте сценарий на выполнение запроса и отладьте его

-- переменные для считывания из курсора

declare @name char(10), @ball real

--таблица формируемая из курсора

declare @t table (nameD char(10), ball real)

--определение курсора

declare cur_tabel cursor

local forward_only static

--стандартный запрос на формирование данных для курсора

for SELECT   Desceplene.Name AS ‘Дисциплина’, AVG(Advencement.oc) AS ‘Оценка’

FROM  Advencement INNER JOIN

piple ON Advencement.id = piple.id

INNER JOIN Desceplene ON Desceplene.idDisciplene = Advencement.idD

WHERE (piple.id = 1)

GROUP BY Advencement.idD,Desceplene.Name

--открытие курсора

open cur_tabel

--перемещение курсора на запись

fetch next from cur_tabel

-- перемещение по записям курсора

--@@fetch_status –функция возвращает

----- 0 если последняя команда fetch выполнилась успешно

------1 если выборка за пределами курсора

------2 обращение к удаленной или измененной строке курсора

while @@fetch_status=0

begin

--считывание из курсора значений столбцов и запись их в переменные

fetch next  from cur_tabel into @name, @ball

--запись в таблицу

insert into @t(nameD,ball) values(@name,@ball)

end

close cur_tabel

deallocate cur_tabel

--отображение данных курсора через таблицу

select * from @t

·  Сохраните запрос

·  Выполните запрос

Самостоятельная работа.

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

1.  Транзакции и блокировки

2.  Триггеры

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

Скачать файл