Назначение и примеры

CASE—

Ключевые слова

оператор выбора

USE — оператор

переключения базы

Часто эта конструкция используется для выполнения одного оператора UPDATE.

Пример 14.8. Модификация колонки может происходить по контролю его

минимального значения: пока наименьшая оценка менее шести баллов, будем их

увеличивать на одну единицу.

while (select min(Оценка)from Успеваемость)<=5

update Успеваемость set Оценка=Оценка+1

where Оценка<5

go

Ключевое слово CASE используется для оценки списка условий и возврата одного из

нескольких возможных результатов. Возвращаемый результат зависит от того, какое

условие совпадает с другим указанным условием или является истинным. Наиболее

распространенным применением CASE является замена кодового или сокращенного

значения на более понятное значение и упорядочивание значений.

Простой синтаксис:

CASE входное_выражение

WHEN выражение_для_when THEN результирующее_выражение

[WHEN выражение_для_when THEN результирующее_выражение...n]

[ELSE выражение_для_else]

END

Поисковый формат:

CASE

WHEN Булево_выражение THEN результирующее_выражение

[WHEN Булево_выражение THEN результирующее_выражение...n]

[ELSE результирующее_выражение_для_else]

END

Пример 14.9. Пропишем оценки текстом: ―два‖, ―три‖, и т.д.

select 'оценка'= case Оценка

when 2 then 'два'

when 3 then 'три'

when 4 then 'четыре'

else 'пять'

end

from Успеваемость

order by Оценка

go

Пример 14.10. Приведем оценки к шкале: ―неуд.‖, ―уд.‖, ―хор.‖ и ―отл.‖

select 'диапазон оценок'=

case

when Оценка between 0 and 3 then 'неуд.'

when Оценка between 4 and 6 then 'уд.'

when Оценка between 7 and 8 then 'хор.'

else 'отл.'

end

from Успеваемость

order by Оценка desc

go

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

команды.

данных;

GО — оператор

конца пакета

команд.

GОТО — оператор

перехода;

129

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

и др.) обеспечивают выполнение наиболее распространенных алгоритмов.

Пользовательские функции (function) являются самостоятельными объектами базы

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

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

Функция должна в обязательном порядке явно вернуть какое-то значение, может

использовать или не использовать входные параметры, в ней можно определять значения по

умолчанию. В теле функции (кроме функций Inline) разрешается создавать циклы,

использовать команды ветвления, работать с транзакциями, объявлять переменные и т. д.

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

CREATE FUNCTION. Однако для создания функций разных типов существует разный

синтаксис этой команды.

ВНИМАНИЕ! В приведенных примерах комментарием «--вызов» отделены сами

функции от команд демонстрации их работы.

Функции создаются в объекте Programmability ® Functions ® New ê. В контекстном

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

– Scalar. Функции этого типа возвращают обычное скалярное значение и являются

наиболее привычными. Функция Scalar может содержать множество команд, объединяемых

конструкцией BEGIN…END в одно целое. На рисунке 14.21 показано окно с текстом

функции примера 14.11. Каждая новая пользовательская функция запускается на

выполнение, в окне сообщений должно быть зафиксировано ее успешное выполнение. Далее

необходимо обновить объект Functions базы данных, увидеть в списке вновь созданную

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

Пример 14.11. Функция "количество студентов по году рождения"

create Function kol_year(@god int = 1987)

returns smallint

AS

begin

declare @y int

set @y=(select count(Фамилия) from Студенты

where year([Дата рождения])=@god)

return @y

end

--вызов

select r=dbo.kol_year(1983)

Рис. 14.21. Разработка пользовательских функций в базе данных в SQL Server 2005

130

 

– Inline. Функции этого типа всегда возвращают значения типа данных table,

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

всего из одной команды SELECT.

Замечание. Функции, возвращающие значения типа данных table (Inline и Multi-

Scalar), могут вызываться непосредственно в разделе FROM при работе с запросами

SELECT, INSERT, DELETE и UPDATE.

Пример 14.12. Функция "список студентов по курсу (вывод курса прописью)"

create Function kurs(@VvodKurs varchar(3))

returns table

AS

return (SELECT Фамилия, 'Курс'=

case Курс

when 'I' then 'первый'

when 'II' then 'второй'

else 'старший'

end

FROM dbo.Студенты

WHERE Курс=@VvodKurs)

--вызов

select * from dbo.kurs('I')

– Multi-Scalar. Это табличная функция, которая возвращает значение типа данных

table, но в теле функции находится множество команд SQL. Требуется задать структуру

возвращаемого значения (как у CREATE TABLE) и формировать возвращаемые данные

командой INSERT.

Пример 14.13. "отчисление" для вывода списка отчисленных студентов

Create FUNCTION otchislenie ()

RETURNS @info table(f char(20))

AS

BEGIN

declare @vv table(fio char(20),vivod char(10))

insert @vv select Фамилия, 'отчислить' from Успеваемость where (Оценка<4)

insert @info select fio from @vv

return

END

--вызов

select * from dbo.otchislenie()

Хранимые процедуры (stored procedures) - являются механизмом, с помощью

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

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

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

Хранимые процедуры используют для добавления записей в таблицу, создания таблицы и

вставки данных с помощью оператора SELECT (insert into), удаления и обновления данных.

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

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

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

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

 функции работы с датой и временем;

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

- функции метаданных;

- функции подсистемы безопасности;

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

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

131

1.

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

- функции для работы с типами данных image, text и ntext.

2. оператор SELECT:

- с вычисляемыми полями;

- с операторами сравнения;

- с фразой BETWEEN;

- с фразой IN;

- с фразой LIKE;

- с сортировкой по алфавиту;

- с фразой TOP;

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

- с фразой GROUP BY;

- ñ ôðàçîé HAVING;

- ñ èñïîëüçîâàíèåì ôóíêöèé äëÿ ðàáîòû ñî ñòðîêàìè;

3. управляющие конструкции и циклы:

- IF... ELSE (IF EXISTS...);

- BEGIN... END;

- WHILE;

- CASE;

4. локальные и глобальные переменные;

5. возвращать код завершения (RETURN).

Через хранимые процедуры осуществляются правила ограничения целостности

данных и алгоритмы обработки данных. Располагаясь на сервере, по сети передается только

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

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

допустимости и устойчивости к ошибкам.

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

только выбирается объект Programmability ® Stored Procedures ® New ê.

Пример 14.14. процедура с входным и выходным параметром

(подсчитываем и выводим количество студентов определенного года рождения).

CREATE PROCEDURE СколькоВГод @VvodYear int, @Rezult int output

AS

SELECT @Rezult=Count(Фамилия)

FROM dbo.Студенты

group by YEAR([Дата рождения])

having YEAR([Дата рождения])=@VvodYear

GO

--вызов

declare @Rez int

exec СколькоВГод 1987, @Rez output

select Всего=@Rez

Пример 14.15. процедура с параметром по умолчанию (получаем список

студентов определенного года рождения с выводом фамилии и даты рождения).

CREATE PROCEDURE Год @VvodYear int=2011

AS

SELECT Фамилия, [Дата рождения], Год=YEAR([Дата рождения])

FROM dbo.Студенты

WHERE YEAR([Дата рождения])=@VvodYear

GO

--вызов

exec Год @vvodyear=1983

Пример 14.16. процедура с параметром по умолчанию («переводим»

студентов одного пола в новую группу (по умолчанию, женский пол переведем в

группу 108999)).

CREATE PROC UpDefault

@tip varchar(3)='жен', @gr varchar(6)= '108999'

AS

UPDATE Студенты SET Группа=@gr WHERE Пол=@tip

--вызов

EXEC UpDefault

EXEC UpDefault 'муж', '107666'

Пример 14.17. процедура для возврата значения.

На рисунке 14.22 продемонстрирован запрос с вызовом хранимой процедурой

dbo.checka, в котором объявляется переменная @ret и в нее помещается возвращаемое

хранимой процедурой значение (1 или 99), производится ее анализ (конструкция if) и

результат выводится в окно сообщений.

CREATE PROCEDURE checka @pr nvarchar(6)

AS

IF (SELECT Count(Фамилия) FROM Студенты

WHERE Группа=@pr)<30

RETURN 1

ELSE

RETURN 99

--вызов

Declare @ret int

EXEC @ret=checka '108811'

if(@ret=1) Print 'В группе еще менее 30 человек'

Рис. 14.22. Запрос для вызова хранимой процедуры в базе данных в SQL Server 2005

Дата добавления: 2015-05-09