Лабораторная работа: Знакомства с MS SQL Server

Ключевое слово AS необязательно.

При объявлении переменной можно ее инициализировать:

DECLARE <@название> AS <тип> = <значение>

Объявленным переменным можно присвоить различные значения с помощью опера- тора присваивания SET. Переменным должны присваиваться значения того типа данных, с каким они были объявлены. Упрощенный синтаксис команды имеет следующий вид:

SET <@название> = <значение>

Переменным можно присваивать скалярный результат выполнения запросов: SET <@название> = (SELECT <значение> FROM <таблица>)

Неинициализированные переменные имеют значение NULL, их нельзя использовать в выражениях.

Переменным можно присваивать значения с помощью команды SELECT:

SELECT <@переменная1> = <столбец1>, …, <@переменнаяN> = <столбецN> FROM <таблица>)

Значения переменных можно вывести с помощью команды PRINT. Синтаксис команды имеет следующий вид:

PRINT <сообщение>

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

Значения переменных можно вывести с помощью команды SELECT. Синтаксис ко- манды имеет следующий вид:

SELECT <@переменная1> [AS псевдоним1], …, <@переменнаяN> [AS псевдонимN]

Для выполнения команды в зависимости от условия используется управляющая ко- манда IF ... ELSE … . Инструкция, следующая за ключевым словом IF и его условием, выпол- няется только в том случае, если логическое выражение возвращает TRUE. Необязательное ключевое слово ELSE представляет другую инструкцию, которая выполняется, если условие IF не удовлетворяется и логическое выражение возвращает FALSE. Упрощенный синтаксис команды имеет следующий вид:

IF <условие> [BEGIN]

<команды> [END]

[ ELSE [BEGIN]

<команды> [END]

]Условие должно возвращать только TRUE (ИСТИНА) или FALSE (ЛОЖЬ).

Если в блоке более чем одна команда, использование [BEGIN] … [END] обязательно.

Для выполнения повторяющихся операций применяется цикл WHILE. Упрощенный синтаксис команды имеет следующий вид:

WHILE <условие> [BEGIN]

<команды| BREAK | CONTINUE > [END]

Команда BREAK приводит к выходу из цикла и вызывает инструкции, следующие за ключевым словом END, обозначающим конец цикла.

Команда CONTINUE пропускает все команды после себя до конца цикла и переводит цикл на следующий шаг.

Практическая часть

Таблица Ученики:

Пример 1: Даны числа a и b. Найти и вывести их сумму:

DECLARE @a INT, @b INT, @c INT SET @a = 5

SET @b = 10

SET @c = @a + @b PRINT @c

Пример 2: В таблице «Ученики» найти разницу между наибольшими баллами среди лицеистов и гимназистов:

DECLARE @licey FLOAT, @gimn FLOAT, @diff FLOAT SET @licey = (

SELECT

MAX(Баллы)

FROM

Ученики

WHERE

Школа = 'Лицей'

)

SET @gimn = (

SELECT

MAX(Баллы)

FROM

Ученики

WHERE

Школа = 'Гимназия'

)

SET @diff = ABS(@licey - @gimn) PRINT @diff

Пример 3: В таблице «Ученики» найти разницу между наибольшими и наименьшими баллами:

DECLARE @maxp FLOAT, @minp FLOAT, @diff FLOAT SELECT

@maxp = MAX(Баллы), @minp = MIN(Баллы)

FROM

Ученики

SET @diff = @maxp - @minp PRINT @diff

Пример 4: Дано случайное целое число меньше 1000. Вывести его квадрат: DECLARE @a INT = RAND() * 1000, @b INT

SET @b = SQUARE(@a) PRINT @b

Пример 5: Даны случайные целые числа a и b. Найти наибольшие из них: DECLARE @a INT = RAND() * 100, @b INT = RAND() * 100

IF @a > @b

PRINT '@a = ' + CAST(@a AS VARCHAR(3))

ELSE

PRINT '@b = ' + CAST(@b AS VARCHAR(3))

Пример 6: Дано случайное целое число a. Проверить, делится ли данное число на 3: DECLARE @a INT = RAND() * 100

IF @a % 3 = 0

PRINT CAST(@a AS VARCHAR(3)) + ' делится на 3'

ELSE

PRINT CAST(@a AS VARCHAR(3)) + ' не делится на 3'

Пример 7: Дано случайное целое число N (N < 1000). Если оно является степенью числа 5, то вывести «Да», если не является - вывести «Нет»:

DECLARE @a INT = RAND() * 1000 WHILE @a % 3 = 0

SET @a = @a / 3 IF @a = 1

PRINT 'Да'

ELSE

PRINT 'Нет'

Пример 8: Даны случайные целые числа a и b. Найти наибольший общий делитель (НОД):

DECLARE @a INT = RAND() * 1000, @b INT = RAND() * 1000 PRINT '@a = ' + CAST(@a AS VARCHAR(4))

PRINT '@b = ' + CAST(@b AS VARCHAR(4))

WHILE @a != @b BEGIN

IF @a > @b

SET @a = @a - @b

END

ELSE

SET @b = @b - @a

PRINT 'НОД = ' + CAST(@a AS VARCHAR(4))

Пример 9: Даны два целых числа A и B (A < B). Найти сумму всех целых чисел от A до B включительно:

DECLARE @a INT = 5, @b INT = 10, @s INT = 0

WHILE @a <= @b BEGIN

SET @s = @s + @a SET @a = @a + 1

END

PRINT 'Сумма = ' + CAST(@s AS VARCHAR(5))

Пример 10: Дано случайное целое число N (N < 100). Найти квадрат данного числа, используя для его вычисления следующую формулу:

??2 = 1 + 3 + 5 + ? + (2 • ?? ? 1)

После добавления к сумме каждого слагаемого выводить текущее значение суммы (в результате будут выведены квадраты всех целых чисел от 1 до N):

DECLARE @N INT = RAND() * 10, @M INT = 1, @S INT = 0 WHILE @M <= 2 * @N - 1

BEGIN

SET @S = @S + @M PRINT @S

SET @M = @M + 2

END

Пример 11: Даны случайные целые числа A и B (A < B). Вывести все целые числа от A до B включительно; при этом число A должно выводиться 1 раз, число A + 1 должно выво- диться 2 раза и т.д.:

DECLARE @A INT = RAND() * 5, @C INT = 1 DECLARE @B INT = @A + RAND() * 5

PRINT '@A = ' + CAST(@A AS CHAR(1)) + ', @B = ' + CAST(@B AS CHAR(1)) WHILE @A <= @B

BEGIN

PRINT REPLICATE(@A, @C) SET @A = @A + 1

SET @C = @C + 1

END

Пример 12: Напечатать те из двузначных чисел, которые делятся на 4, но не делятся на 6: DECLARE @A INT = 10

WHILE @A < 100 BEGIN

IF (@A % 4 = 0) AND (@A % 6 != 0) PRINT @A

SET @A = @A + 1

END

Пример 13: Даны два целых числа D (день) и M (месяц), определяющие правильную дату невисокосного года. Вывести значения D и M для даты, следующей за указанной:

DECLARE @D INT = 31, @M INT = 12 SET @D = CASE

END SET @M = CASE

END

WHEN @M IN (1, 3, 5, 7, 8, 10, 12) AND @D = 31 THEN 1

WHEN @M IN (4, 6, 9, 11) AND @D = 30 THEN 1 WHEN @M = 2 AND @D = 29 THEN 1

ELSE @D + 1

WHEN @D = 1 AND @M = 12 THEN 1 WHEN @D = 1 AND @M < 12 THEN @M + 1 ELSE @M

PRINT CAST(@D AS VARCHAR(2)) + '/' + CAST(@M AS VARCHAR(2))

Пример 14: Вывести слово «Нижневартовск» на экран столько раз, сколько в нем букв: DECLARE @L INT, @N CHAR(13) = 'Нижневартовск'

SET @L = LEN(@N)

WHILE @L > 0 BEGIN

PRINT @N

SET @L = @L - 1

END

Пример 15: Напишите код для вывода на экран с помощью цикла: НижневартовскксвотравенжиН

Нижневартовс свотравенжиН Нижневартов вотравенжиН Нижневарто отравенжиН Нижневарт травенжиН Нижневар равенжиН

Нижнева авенжиН

Нижнев венжиН

Нижне енжиН

Нижн нжиН

Ниж жиН

Ни иН

Н Н

Ни иН

Ниж жиН

Нижн нжиН

Нижне енжиН

Нижнев венжиН

Нижнева авенжиН

Нижневар равенжиН Нижневарт травенжиН Нижневарто отравенжиН Нижневартов вотравенжиН Нижневартовс свотравенжиН НижневартовскксвотравенжиН

DECLARE @L INT, @M INT, @N CHAR(13)

SET @N = 'Нижневартовск' SET @L = LEN(@N)

SET @M = @L WHILE @L > 0 BEGIN

PRINT LEFT(@N, @L) + SPACE(2 * (@M - @L)) + RIGHT(REVERSE(@N), @L) SET @L = @L - 1

END

SET @L = 2 WHILE @L <= @M BEGIN

PRINT LEFT(@N, @L) + SPACE(2 * (@M - @L)) + RIGHT(REVERSE(@N), @L) SET @L = @L + 1

END

Задание

Даны числа A и B. Найти и вывести их произведение.

В таблице «Ученики» найти разницу между средними баллами лицеистов и гимна- зистов.

В таблице «Ученики» проверить на четность количество строк.

Дано четырехзначное число. Вывести сумму его цифр.

Даны случайные целые числа a, b и c. Найти наименьшее из них.

Дано случайное целое число a. Проверить, делится ли данное число на 11.

Дано случайное целое число N (N < 1000). Если оно является степенью числа 3, то вывести «Да», если не является - вывести «Нет».

Даны случайные целые числа a и b. Найти наименьший общий кратный (НОК).

Даны два целых числа A и B (A<B). Найти сумму квадратов всех целых чисел от A до B включительно.

Найти первое натуральное число, которое при делении на 2, 3, 4, 5, и 6 дает остаток 1, но делится на 7.

Вывести свою фамилию на экран столько раз, сколько в нем букв.

Напишите код для вывода на экран с помощью цикла:

Н

иНи жиНиж нжиНижн енжиНижне венжиНижнев авенжиНижнева

равенжиНижневар травенжиНижневарт отравенжиНижневарто вотравенжиНижневартов свотравенжиНижневартовс ксвотравенжиНижневартовск

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

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

Цель работы

Изучение скалярных функций.

Изучение функции INLINE.

Изучение функции MULTI-STATEMENT.

Изучение удаления пользовательских функций.

Теоретическая часть

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

Пользовательскую функцию можно использовать следующими способами:

В инструкциях Transact-SQL, например, SELECT.

В приложениях, вызывающих функцию.

В определении другой пользовательской функции.

Для определения столбца таблицы.

Для определения ограничения CHECK на столбец.

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

Пользовательские функции не могут возвращать несколько результирующих наборов.

Пользовательские функции не могут использовать динамический SQL и временные таблицы. Табличные переменные разрешены к использованию.

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

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

CREATE FUNCTION <название> (

[{<@параметр> [AS] <тип> [= <значение по умолчанию>]}]

)

RETURNS <тип возврата> [AS]

BEGIN

<команды>

RETURN <значение> END

Значение, переменная или выражение после ключевого слова RETURN имеет такой же тип, который указан после ключевого слова RETURNS.

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

SELECT <владелец>.<функция>(<параметры>)

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

Имя параметра, как и имя переменных, использует знак @ как первый символ.

Для INLINE функций ключевого слова RETURNS указывается тип TABLE без указания списка столбцов. Тело такой функции представляет собой единственный оператор SELECT, который начинается сразу после ключевого слово RETURN. Упрощенный синтаксис создания пользовательской функции INLINE имеет следующий вид:

CREATE FUNCTION <название> (

[{<@параметр> [AS] <тип> [= <значение по умолчанию>]}]

)

RETURNS TABLE AS

RETURN ( SELECT

<список столбцов> FROM

<таблица> WHERE

<условие>

)

В MULTI-STATEMENT функциях после ключевого слова RETURNS указывается тип TABLE с определением столбцов и их типов данных. Упрощенный синтаксис создания поль- зовательской MULTI-STATEMENT функции имеет следующий вид:

CREATE FUNCTION <название> (

[{<@параметр> [AS] <тип> [= <значение по умолчанию>]}]

)

RETURNS <@таблица> TABLE (<определение таблицы>) AS

BEGIN

<команды> RETURN END

Для MULTI-STATEMENT функций оператор RETURN не имеет аргумента. Значение возвращаемой переменной функции возвращается как значение функции.

Для удаления пользовательских функций используется команда DROP FUNCTION. Упрощенный синтаксис имеет следующий вид:

DROP FUNCTION [IF EXISTS] <название функции>

Ключевые слова IF EXISTS удаляют функцию только в том случае, если она уже суще- ствует.

Практическая часть

Дана таблица Страны:

Южная Америка

Афганистан

Кабул

647500

29822848

Азия

Бангладеш

Дакка

144000

160221000

Азия

Бахрейн

Манама

701

1397000

Азия

Белиз

Бельмопан

22966

377968

Северная Америка

Белоруссия

Минск

207595

9498400

Европа

Бельгия

Брюссель

30528

11250585

Европа