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

Бенин

Порто-Ново

112620

11167000

Африка

Болгария

София

110910

7153784

Европа

Боливия

Сукре

1098580

10985059

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

Ботсвана

Габороне

600370

2209208

Африка

Бразилия

Бразилиа

8511965

206081432

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

Буркина-Фасо

Уагадугу

274200

19034397

Африка

Бутан

Тхимпху

47000

784000

Азия

Великобритания

Лондон

244820

65341183

Европа

Венгрия

Будапешт

93030

9830485

Европа

Венесуэла

Каракас

912050

31028637

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

Восточный Тимор

Дили

14874

1167242

Азия

Вьетнам

Ханой

329560

91713300

Азия

Пример 1: Напишите функцию для вывода столицы данной страны, и вызовите ее:

CREATE FUNCTION Пример1 (

@Страна AS VARCHAR(50)

)

RETURNS VARCHAR(50) AS

BEGIN

DECLARE @S AS VARCHAR(50) SELECT

@S = Столица

FROM

Страны

END

WHERE

Название = @Страна RETURN @S

SELECT dbo.Пример1('Австрия')

Пример 2: Напишите функцию для перевода площади в тыс. кв. км., и вызовите ее:

CREATE FUNCTION Пример2 (

@Площадь AS FLOAT

)

RETURNS FLOAT AS

BEGIN

DECLARE @P AS FLOAT

SET @P = ROUND(@Площадь / 1000, 2) RETURN @P

END

SELECT

Название, Столица, Континент, Население,

dbo.Пример2(Площадь) AS [Площадь тыс.кв.км]

FROM

Страны

Пример 3: Напишите функцию для вычисления плотности населения, и вызовите ее:

CREATE FUNCTION Пример3 (

@Население AS INT, @Площадь AS FLOAT

)

RETURNS FLOAT AS

BEGIN

DECLARE @P AS FLOAT

SET @P = ROUND(CAST(@Население AS FLOAT) / @Площадь, 2) RETURN @P

END

SELECT

Название, Столица, Континент, Население, Площадь,

dbo.Пример3(Население, Площадь) AS Плотность

FROM

Страны

ORDER BY

Плотность DESC

Пример 4: Напишите функцию для поиска страны второй по площади, и вызовите ее: CREATE FUNCTION Пример4()

RETURNS VARCHAR(50) AS

BEGIN

DECLARE @P AS VARCHAR(50) DECLARE @M1 AS FLOAT DECLARE @M2 AS FLOAT

SELECT

@M1 = MAX(Площадь)

FROM

Страны

SELECT

@M2 = MAX(Площадь)

FROM

Страны

WHERE

Площадь < @M1

SELECT

@P = Название

FROM

Страны

WHERE

Площадь = @M2

RETURN @P

END

SELECT

dbo.Пример4() AS [Второй по площади страна]

Пример 5: Напишите функцию для поиска страны с минимальной площадью в задан- ной части света, и вызовите ее. Если часть света не указана, выбрать Европу:

CREATE FUNCTION Пример5 (

@Конт AS VARCHAR(50) = 'Европа'

)

RETURNS VARCHAR(50) AS

BEGIN

DECLARE @P AS VARCHAR(50) DECLARE @M AS FLOAT

SELECT

@M = MIN(Площадь)

FROM

Страны

WHERE

Континент = @Конт

SELECT

@P = Название

FROM

Страны

WHERE

Континент = @Конт AND

Площадь = @M

RETURN @P

END

SELECT

dbo.Пример5('Азия') AS [Наименьшая по площади страна в Азии]

SELECT

dbo.Пример5(DEFAULT) AS [Наименьшая по площади страна в Европе]

Пример 6: Напишите функцию для замены букв в заданном слове от второй до пред- последней на точку, и примените ее для названия страны:

CREATE FUNCTION Пример6 (

@A AS VARCHAR(50)

)

RETURNS VARCHAR(50) AS

BEGIN

RETURN LEFT(@A, 1) + REPLICATE('.', LEN(@A) - 2) + RIGHT(@A, 1)

END

SELECT

dbo.Пример6(Название) AS [Скрытое название]

,Столица

,Континент

,Площадь

,Население

FROM

Страны

Пример 7: Напишите функцию, которая возвращает количество стран, содержащих в названии заданную букву:

CREATE FUNCTION Пример7 (

@C AS CHAR(1)

)

RETURNS INT AS

BEGIN

DECLARE @K AS INT

SELECT

@K = COUNT(*)

FROM

Страны

WHERE

CHARINDEX(@C, Название) > 0

RETURN @K

END

Пример 8: Напишите функцию для вывода списка стран с населением больше задан- ного числа, и вызовите ее:

CREATE FUNCTION Пример8 (

@N AS INT

)

RETURNS TABLE AS

RETURN (

SELECT

Название

,Столица

,Площадь

,Население

,Континент

FROM

Страны

SELECT

*

WHERE

Население > @N

)

FROM

dbo.Пример8(100000000)

Пример 9: Напишите функцию для вывода списка стран с площадью в интервале за- данных значений, и вызовите ее:

CREATE FUNCTION Пример9

(

@A AS FLOAT, @B AS FLOAT

)

RETURNS TABLE AS

RETURN (

SELECT

Название

,Столица

,Площадь

,Население

,Континент

FROM

Страны

SELECT

*

WHERE

Площадь BETWEEN @A AND @B

)

FROM

dbo.Пример9(1000, 10000)

Пример 10: Напишите функцию для возврата таблицы с названием страны и плотно- стью населения, и вызовите ее:

CREATE FUNCTION Пример10()

RETURNS @Ст_Плот TABLE

(

Название VARCHAR(50), Плотность FLOAT

)

AS BEGIN

INSERT

@Ст_Плот SELECT

Название

, CAST(Население AS FLOAT) / Площадь AS Плотность

FROM

Страны

RETURN

END

SELECT

Название

,Плотность

FROM

dbo.Пример10()

Пример 11: Удалите функцию из примера 10:

DROP FUNCTION Пример10

Задание

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

Напишите функцию для перевода населения в млн. чел. и вызовите ее.

Напишите функцию для вычисления плотности населения заданной части света и вызовите ее.

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

Напишите функцию для поиска страны с максимальным населением в заданной ча- сти света и вызовите ее. Если часть света не указана, выбрать Азию.

Напишите функцию для замены букв в заданном слове от третьей до предпослед- ней на “тест” и примените ее для столицы страны.

Напишите функцию, которая возвращает количество стран, не содержащих в назва- нии заданную букву.

Напишите функцию для возврата списка стран с площадью меньше заданного числа и вызовите ее.

Напишите функцию для возврата списка стран с населением в интервале заданных значений и вызовите ее.

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

Напишите функцию IsPalindrom(P) целого типа, возвращающую 1, если целый па- раметр P (P > 0) является палиндромом, и 0 в противном случае.

Напишите функцию Quarter(x, y) целого типа, определяющую номер координатной четверти, содержащей точку с ненулевыми вещественными координатами (x, y).

Напишите функцию IsPrime(N) целого типа, возвращающую 1, если целый пара- метр N (N > 1) является простым числом, и 0 в противном случае.

Напишите код для удаления созданных вами функций.

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

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

Цель работы

Изучение создания хранимых процедур.

Изучение передачи входных параметров.

Изучение передачи выходных параметров.

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

Изучение удаления хранимых процедур.

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

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

обрабатывают входные параметры и возвращают значения в виде выходных парамет- ров;

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

возвращают сведения об успешном или неуспешном завершении.

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

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

Хранимая процедура создается с помощью команды CREATE PROCEDURE или CREATE PROC, которая имеет следующий упрощенный вид:

CREATE {PROC | PROCEDURE} <название>

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

[BEGIN]

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

При создании процедуры после команды CREATE указывается тип создаваемого объ- екта с помощью ключевого слова PROCEDURE или его сокращенного варианта PROC.

Названия процедур должны соответствовать требованиям, предъявляемым к идентифи- каторам, и должны быть уникальными в базе данных. При этом не следует пользоваться пре- фиксом «sp_». Этим префиксом в SQL Server обозначаются системные процедуры.

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

Ключевое слово OUT (можно использовать и OUTPUT) показывает, что параметр про- цедуры является выходным.

Для выполнения хранимой процедуры используется ключевое слово EXECUTE (или EXEC). Процедуру также можно вызывать и выполнять без ключевого слова, если она явля- ется первой инструкцией. Синтаксис команды EXECUTE имеет следующий вид:

EXECUTE [<@статус возврата>=] <название процедуры> [<@параметр>=] <значе- ние>| <@переменная> [OUTPUT] | [DEFAULT]

В отличии от вызова функций, при вызове хранимых процедур с указанием названия параметра ([<@параметр>=] <значение>), последовательность параметров можно не соблю- дать.

Для выходных параметров при вызове указывается ключевое слово OUTPUT.

Если для параметра указано значение по умолчанию, можно его использовать с помо- щью ключевого слова DEFAULT.

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

DROP PROC | PROCEDURE [IF EXISTS] <название хранимой процедуры>

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

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

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