Лабораторная работа №7_БД. Лабораторная работа 7 Освоение программирования с помощью встроенного языка Transact sql

• Название: Лабораторная работа 7 Освоение программирования с помощью встроенного языка Transact sql
• Дата: 26.04.2022
• Размер: 50.14 Kb.
• Имя файла: Лабораторная работа №7_БД.docx
• Тип: Лабораторная работа; #497553

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

Освоение программирования с помощью встроенного языка Transact SQL

Цель работы – знакомство с основными приципами программирования в MS SQL Server средствами встроенного языка Transact SQL.

Содержание работы:

1. Изучение правил написания программ на Transact SQL.

2. Изучение правил построения идентификаторов, правил объявления переменных и их типов.

3. Изучение работы с циклами и ветвлениями.

4. Изучение работы с переменными типа Table и Cursor.

5. Проработка всех примеров, анализ результатов их выполнения.

6. Выполнение индивидуальных заданий по вариантам.

Пояснения к выполнению работы

Для освоения программирования используем пример базы данных c названием DB_Books.

Создание базы данных

CREATE DATABASE DB_BOOKS

 

USE DB_BOOKS

CREATE TABLE Authors(

Code_author INT PRIMARY KEY NOT NULL, -- Кодавтора

Name_author CHAR(30), -- Фамилияавтора

Birthday DATETIME -- Датарождения

)

 

CREATE TABLE Publishing_house(

Code_publish INT PRIMARY KEY NOT NULL, -- Кодиздательства

Publish CHAR(30), -- Название издательства

City CHAR(20) -- Город издательства

)

 

CREATE TABLE Books(

Code_book INT PRIMARY KEY NOT NULL, -- Кодкниги

Title_book CHAR(40), -- Названиекниги

Code_author INT FOREIGN KEY REFERENCES Authors(Code_author),

Pages INT,

Code_publish INT FOREIGN KEY REFERENCES Publishing_house(Code_publish)

)

 

CREATE TABLE Deliveries(

Code_delivery INT PRIMARY KEY NOT NULL, -- Коддоставщика

Name_delivery CHAR(30), -- Наименованиедоставщика

Name_company CHAR(20), -- Наименованиекомпании

Address_company VARCHAR(100), -- Адрес

Phone BIGINT, -- Телефон

INN CHAR(13) -- ИНН

)

 

CREATE TABLE Purchases(

Code_purchase INT PRIMARY KEY NOT NULL, -- Кодпродажи

Code_book INT FOREIGN KEY REFERENCES Books(Code_book),

Date_order SMALLDATETIME, -- Дата

Code_delivery INT FOREIGN KEY REFERENCES Deliveries(Code_delivery),

Type_purchase BIT, -- Типпродажи

Cost FLOAT, -- Цена

Amount INT -- Количество

При выполнении примеров и заданий обращайте внимание на соответствие названий БД, таблиц и других объектов проекта.

1. Специальные знаки и простейшие операторы в Transact SQL

Знак	Назначение	Знак	Назначение
*	Знак умножения	» »	В них заключают строковые значения, если SET QUOTED_IDENTIFIER OFF
-	Знак вычитания	‘ ’	В них заключают строковые значения*
%	Остаток от деления двух чисел	<>	Не равно
+	Знак сложения или конкатенации (объединение двух строк в одну)	[ ]	Аналог кавычек, в них можнозаключать названия идентификаторов, если в их названиях встречаются пробелы
=	Знак равенства или сравнения	!<	Не менее чем
⇐	Меньше или равно	!>	Не более чем
>=	Больше или равно	>	Больше
!=	Не равно	<	Меньше
@	Ставится перед именем переменной	.	Разделяет родительские и подчиненные объекты
@@	Указывает на системные функции	/	Знак деления
–	Однострочный комментарий или комментарий с текущей позиции и до конца строки	/* */	Многострочный комментарий

2. Идентификаторы

Идентификаторы – это имена объектов, на которые можно ссылаться в программе, написанной на языке Transact SQL. Первый символ может состоять из букв английского алфавита или «_», »@», »#». В качестве остальных символов идентификатора могут быть дополнительно использованы цифры и символ «$». Имя идентификатора не должно совпадать с зарезервированным словом (среда SQL Server Management Studio подсвечивает зарезервированные слова синим цветом).

Иногда в качестве идентификатора удобно использовать словосочетание. Можно воспользоваться заменой пробела на нижнее подчеркивание, а можно использовать ограничители идентификаторов - квадратные скобки или одинарные кавычки. Здесь надо обратить внимание на параметр QUOTED_IDENTIFIER.

Для ограничителей идентификаторов при установленном параметре

SET QUOTED_IDENTIFIER ON

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

Если использовать установленный параметр в режиме

SET QUOTED_IDENTIFIER OFF

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

Переменные используются для сохранения промежуточных данных в хранимых процедурах и функциях. Все переменные считаются локальными. Имя переменной должно начинаться с @.

3. Объявление переменных

Синтаксис в обозначениях MS SQL Server:

DECLARE @имя_переменной1 тип_переменной, …, @имя_переменнойN тип_переменной

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

DECLARE @имя_переменной1 тип_переменной(размер),

…,

@имя_переменнойN тип_переменной(размер)

Пример:

DECLARE @a INT, @b NUMERIC(10,2)

DECLARE @str CHAR(20)

4. Присвоение значений переменным и вывод значений на экран

Присвоение с помощью SET – обычное присвоение, синтаксис:

SET @имя_переменной = значение

Пример:

DECLARE @a INT, @b NUMERIC(10,2)

SET @a = 20

SET @b = (@a+@a)/15

SELECT @b -- вывод на экран результата

Присвоение с помощью SELECT – помещение результата запроса в переменную. Если в результате выполнения запроса не будет возвращено ни одной строки, то значение переменной не меняется, т.е. остается старым.

Пример:

DECLARE @a INT

SELECT @a = COUNT(*) FROM Authors

Пример:

DECLARE @str CHAR(30)

SELECT @str = name_author FROM Authors

В данном примере в переменную поместится последнее значение из результата запроса.

Сочетание ключевых слов SET и SELECT

Пример:

DECLARE @a INT

SET @a = (SELECT COUNT(*) FROM Authors)

5. Работа с датой и временем

Оператор SET DATEFORMAT dmy | ymd | mdy задает порядок следования компонентов даты.

Пример:

SET DATEFORMAT dmy -- задает порядок следования компонентов даты.

DECLARE @d DateTime -- объявляем переменную типа DateTime

SET @d = '30.10.1821 0:00:00' -- устанавливаем значение переменной d

SET @d = @d+1 -- прибавляем один день

SELECT @d -- выводим значение переменной d в таблице

6. Создание временной таблицы через переменную типа TABLE

Объявляется через DECLARE с указанием в скобках столбцов таблицы, их типов, размеров, значений по умолчанию, а также индексов типа PRIMARY KEY или UNIQUE.

Пример:

/*Объявляем таблицу*/

DECLARE @mytable TABLE ( -- Объявление переменной типа TABLE

id INT, -- Тип целых значений

myname CHAR(20) DEFAULT 'Введите имя' -- Тип значений и в ячейке выводится сообщение

)

INSERT INTO @mytable(id) VALUES (1) -- Добавляемстроку

SELECT * FROM @mytable -- Выводим значение

Пример:

/*Объявляемтаблицу*/

DECLARE @mytable TABLE(

id INT,

myname CHAR(20) DEFAULT 'Введите имя'

)

INSERT @mytable

SELECT

Code_publish,

City

FROM Publishing_house

SELECT * FROM @mytable

7. Преобразование типов переменных

Функция CAST возвращает значение, преобразованное к указанному типу:

CAST(@переменная или значение AS требуемый_тип_данных)

Пример:

DECLARE @d DateTime, @str CHAR(20) -- объявляем переменные типа DateTime и char

SET @d = '31.01.2005 13:23:15' -- устанавливаем значение переменной d

SET @str = CAST(@d AS CHAR(20)) -- устанавливаем значение str, приводим к формату char

SELECT @str -- выводит значение переменной

Функция CONVERT возвращает значение, преобразованное к указанному типу по заданному формату. Изучить дополнительно, по желанию.

8. Операторские скобки

BEGIN

/* В них нельзя помещать команды, изменяющие структуры объектов БД.

Операторские скобки должны содержать хотя бы один оператор.

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

*/

END

9. Условная конструкция IF

Синтаксис:

IF условие

Набор операторов1

ELSE

Набор операторов2

Пример:

DECLARE @a INT -- объявляет переменную типа INT

DECLARE @str CHAR(40) -- объявляет переменную типа CHAR

SET @a = (SELECT COUNT(name_author) FROM Authors) -- устанавливаетзначение a, равноеколичеству

-- записейвстолбце name_author

IF @a > 10

BEGIN

SET @str = 'Количество авторов больше 10' -- если а>10, то выводится сообщение "Количество авторов больше 10"

SELECT @str -- вывод значения переменной str

END

ELSE

BEGIN

SET @str = ' Количество авторов = ' + str(@a) -- иначе количество авторов равно значению переменной а

SELECT @str -- вывод значения переменной str

END

10. Цикл WHILE

Синтаксис:

WHILE Условие

Набор операторов1

BREAK

Набор опреторов2

CONTINUE

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

Пример:

DECLARE @a INT -- объявляет переменную типа INT

SET @a = 1 -- устанавливает значение а =1

WHILE @a < 100 -- цикл работает пока значение переменной а<100

BEGIN

PRINT @a -- вывод на экран значения переменной

IF (@a>40) AND (@a<50) -- если значение переменной а>40 и a <50

BREAK -- выход и выполнение 1-й команды за циклом

ELSE -- иначе

SET @a = @a+rand()*10 -- устанавливаем значение а равное переменной а + случайное число из диапазона (0, 1),

-- умноженное на 10

CONTINUE -- запускаем цикл заново, не дожидаясь выполнения всех команд в теле

END

PRINT @a -- вывод на экран значения переменной

11. Объявление курсора

CURSOR – это набор строк, являющийся результатом выполнения запроса. В один момент времени доступна лишь одна строка (текущая), по курсору можно передвигаться и получать доступ к элементарным данным. При объявлении курсора создается временная копия данных, которая сохраняется в БД tempdb.

Динамический курсор – данные в курсоре могут быть изменены.

Статический курсор – данные в курсоре не меняются.

Стандартный способ объявления курсора, синтаксис в обозначениях MS SQL Server:

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FOR select_statement

[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

Примеры объявления курсоров

1. Объявляем курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является динамическим.

DECLARE MyCursor1 CURSOR FOR (SELECT * FROM Authors)

2. Объявляем курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является статическим.

DECLARE MyCursor1 INSENSITIVE CURSOR FOR (SELECT * FROM Authors)

3. Объявляем курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно в любом направлении. Курсор является динамическим.

DECLARE MyCursor1 SCROLL CURSOR FOR (SELECT * FROM Authors)

4. Объявляем курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно в любом направлении. Курсор является статическим.

DECLARE MyCursor1 INSENSITIVE SCROLL CURSOR FOR (SELECT * FROM Authors)

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

DECLARE MyCursor1 CURSOR FOR (SELECT * FROM Authors) FOR READ ONLY

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

DECLARE MyCursor1 CURSOR FOR (SELECT * FROM Authors) FOR UPDATE

12. Операторы для работы с курсором

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

Синтаксис оператора OPEN в обозначениях MS SQL Server:

OPEN { { [ GLOBAL ] cursor_name} | cursor_variable_name }

Пример:

DECLARE MyCursor1 CURSOR FOR (SELECT * FROM Authors)

OPEN MyCursor1

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

Синтаксис оператора CLOSE в обозначениях MS SQL Server:

CLOSE { { [ GLOBAL ] cursor_name} | cursor_variable_name}

Пример:

DECLARE MyCursor1 CURSOR FOR (SELECT * FROM Authors)

OPEN MyCursor1

--здесь операторы работы с курсором

CLOSE MyCursor1

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

Синтаксис оператора DEALLOCATE в обозначениях MS SQL Server:

DEALLOCATE { { [ GLOBAL ] cursor_name} | @cursor_variable_name }

Пример:

DECLARE MyCursor1 CURSOR FOR (SELECT * FROM Authors)

OPEN MyCursor1

--здесь операторы работы с курсором

CLOSE MyCursor1

DEALLOCATE MyCursor1

FETCH – оператор движения по записям курсора и извлечения данных текущей записи в указанные переменные. Синтаксис оператора FETCH в обозначениях MS SQL Server:

FETCH

[ [ NEXT | PRIOR | FIRST | LAST

| ABSOLUTE { n | @nvar }

| RELATIVE { n | @nvar }

]

FROM

]

{ { [ GLOBAL ] cursor_name} | @cursor_variable_name }

[ INTO @variable_name [1 ,..., n ] ]

Пример:

DECLARE MyCursor1 SCROLL CURSOR FOR (SELECT * FROM Authors)

DECLARE @i BIGINT, @s CHAR(20), @d smalldatetime

OPEN MyCursor1

FETCH FIRST FROM MyCursor1 INTO @i, @s, @d

PRINT @i

PRINT @s

PRINT @d

CLOSE MyCursor1

DEALLOCATE MyCursor1

@@FETCH_STATUS – данная функция определяет признак конца или начала текущего курсора. Функция принимает одно из следующих значений:

• 
  0 – находимся в пределах курсора, не в конце;
  
• 
  1 – попытка выйти за пределы первой записи вверх (в никуда);
  
• 
  2 – попытка выйти за пределы последней записи вниз(в никуда).
  

Пример:

DECLARE MyCursor1 SCROLL CURSOR FOR (SELECT * FROM Authors)

DECLARE @i BIGINT, @s CHAR(20), @d smalldatetime -- объявляемпеременные

OPEN MyCursor1 -- открываем MyCursor1

FETCH FIRST FROM MyCursor1 INTO @i, @s, @d -- устанавливаемкурсорнапервуюстрокутаблицы Authors

 

WHILE @@FETCH_STATUS = 0 -- пока находимся в пределах таблицы

BEGIN

FETCH NEXT FROM MyCursor1 INTO @i, @s, @d -- записываем данные из текущей строки в переменные

PRINT @i -- выводим code_author в переменную i

PRINT @s -- выводим name_author в переменную s

PRINT @d -- выводим birthday в переменную d

END

CLOSE MyCursor1 -- закрываем курсор

DEALLOCATE MyCursor1 -- освобождаем память

13. Пример создания и запуска процедуры с курсором

-- пример создания процедуры

USE DB_book;

GO

IF OBJECT_ID ('Book','P') IS NOT NULL -- если уже существует объект (процедура) с именем 'Book',

DROP PROCEDURE Book; -- удаляемэтотобъект

GO

CREATE PROCEDURE Book -- создаем процедуру Book, возвращающую значение переменной cur1

@cur1 CURSOR VARYING OUTPUT -- параметры курсора в хранимой процедуре обязательно должны быть

-- CURSOR VARYING OUTPUT

AS -- далее - телопроцедуры

SET @cur1 = CURSOR FOR -- объявляем переменную типа курсор

SELECT name_author FROM Authors; -- выводимвкурсорзначениязаписи name_author

OPEN @cur1; -- открываем переменную

GO

 

--пример запуска процедуры с курсором

USE DB_book;

GO

DECLARE @MyCursor CURSOR; -- объявляемкурсор

DECLARE @a CHAR(30); -- объявляем переменную @a

EXEC book @cur1 =@MyCursor OUTPUT; -- запускаем процедуру и выводим возвращаемые значения

-- в объявленный курсор

 

FETCH NEXT FROM @MyCursor INTO @a; -- устанавливаем курсор на первую запись таблицы и выводим

-- значение в переменную @a

 

WHILE (@@FETCH_STATUS =0) -- пробегаем по всем строкам таблицы

BEGIN

PRINT @a; -- выводим на экран значение переменной @a

FETCH NEXT FROM @MyCursor INTO @a; -- устанавливаем курсор на следующую запись таблицы и выводим

-- значение в переменную @a

END;

CLOSE @MyCursor; -- закрываем курсор

DEALLOCATE @MyCursor; -- освобождаем память

GO

14. Встроенные функции

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

• 
  математические функции;
  
• 
  строковые функции;
  
• 
  функции для работы с датой и временем;
  
• 
  функции конфигурирования;
  
• 
  функции системы безопасности;
  
• 
  функции управления метаданными;
  
• 
  статистические функции.
  

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

Краткий обзор строковых функций

Название функций	Действие, выполняемое функцией
ASCII	Возвращает код ASCII левого символа строки
CHAR	По коду ASCII возвращает символ
CHARINDEX	Определяет порядковый номер символа, с которого начинается вхождение подстроки в строку
DIFFERENCE	Возвращает показатель совпадения строк
LEFT	Возвращает указанное число символов с начала строки
LEN	Возвращает длину строки
LOWER	Переводит все символы строки в нижний регистр
LTRIM	Удаляет пробелы в начале строки
NCHAR	Возвращает по коду символ Unicode
PATINDEX	Выполняет поиск подстроки в строке по указанному шаблону
REPLACE	Заменяет вхождения подстроки на указанное значение
QUOTENAME	Конвертирует строку в формат Unicode
REPLICATE	Выполняет тиражирование строки определенное число раз
REVERSE	Возвращает строку, символы которой записаны в обратном порядке
RIGHT	Возвращает указанное число символов с конца строки
RTRIM	Удаляет пробелы в конце строки
SOUNDEX	Возвращает код звучания строки
SPACE	Возвращает указанное число пробелов
STR	Выполняет конвертирование значения числового типа в символьный формат
STUFF	Удаляет указанное число символов, заменяя новой подстрокой
SUBSTRING	Возвращает для строки подстроку указанной длины с заданного символа
UNICODE	Возвращает Unicode-код левого символа строки
UPPER	Переводит все символы строки в верхний регистр

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

Краткий обзор математических функций

Название функций	Действие, выполняемое функцией
ABS	Вычисляет абсолютное значение числа
ACOS	Вычисляет арккосинус
ASIN	Вычисляет арксинус
ATAN	Вычисляет арктангенс
ATN2	Вычисляет арктангенс с учетом квадратов
CEILING	Выполняет округление вверх
COS	Вычисляет косинус угла
COT	Переводит все символы строки в нижний регистр
DEGREES	Преобразует значение угла из радиан в градусы
EXP	Возвращает экспоненту
FLOOR	Выполняет округление вниз
LOG	Вычисляет натуральный логарифм
LOG10	Вычисляет десятичный логарифм
PI	Возвращает значение «пи»
POWER	Возводит число в степень
RADIANS	Преобразует значение угла из градуса в радианы
RAND	Возвращает случайное число
ROUND	Выполняет округление с заданной точностью
SIGN	Определяет знак числа
SIN	Вычисляет синус угла
SQUARE	Выполняет возведение числа в квадрат
SQRT	Извлекает квадратный корень
TAN	Возвращает тангенс угла

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

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

DATEADD	Добавляет к дате указанное значение дней, месяцев, часов и т.д.
DATEDIFF	Возвращает разницу между указанными частями двух дат
DATENAME	Выделяет из даты указанную часть и возвращает ее в символьном формате
DATEPART	Выделяет из даты указанную часть и возвращает ее в числовом формате
DAY	Возвращает число из указанной даты
GETDATE	Возвращает текущее системное время
ISDATE	Проверяет правильность выражения на соответствие одному из возможных форматов ввода даты
MONTH	Возвращает значение месяца из указанной даты
YEAR	Возвращает значение года из указанной даты
MINUTE	Возвращает значение минут из указанной даты/времени
HOUR	Возвращает значение часов из указанной даты/времени
SECOND	Возвращает значение секунд из указанной даты/времени

Варианты заданий к лабораторной работе №7

Общие сведения

Для получения более подробной информации о работе тех или иных операторов или функций можно запустить утилиту Books Online из состава MS SQL Server и в разделе «Указатель» набрать искомый ключевой элемент.

Специальные знаки и простейшие операторы в Transact SQL

Пример. Проверить работу описанной установки SET QUOTED_IDENTIFIER

Напишем следующий запрос (объявим строковую переменную и значение её заключим в двойные кавычки)

DECLARE @A CHAR(30)

SET @A = "ПРИВЕТ"

Если предварительно было установлено SET QUOTED_IDENTIFIER ON , то запрос завершится ошибкой. Чтобы запрос сработал нужно выставить SET QUOTED_IDENTIFIER OFF:

SET QUOTED_IDENTIFIER OFF

DECLARE @A CHAR(30)

SET @A = "ПРИВЕТ"

Задание.

1. Проверить работу описанной установки SET DATEFORMAT.

Объявление переменных

Пример. Объявить переменную Perem1 типа денежный, а переменную Perem2 типа число с целой частью равной 8 и дробной частью равной 2.

DECLARE @a MONEY, @b NUMERIC(10,2)

DECLARE @str CHAR(20)

Money - тип данных, представляющие денежные (валютные) значения. Диапазон От -922 337 203 685 477,5808 до 922 337 203 685 477,5807 (8 байт). Numeric(n,m) - тип данных с фиксированной запятой (n - общее число десятичных разрядов, m - количество знаков после запятой).

Задания.

2. Объявить переменную Perem1 типа строка длиной 100, а переменную Perem2 типа длинное целое.

3. Объявить переменную Perem1 типа динамическая строка с максимальной длиной 1000, а переменную Perem2 типа целое число.

4. Объявить переменную Perem1 типа строка длиной 30, а переменную Perem2 типа число с целой частью равной 10 и дробной частью равной 3.

5. Объявить переменную Perem1 типа дата/ время, а переменную Perem2 типа число в диапазоне от 0 до 255.

Присвоение значений переменным и вывод значений на экран

Задания.

6. Подсчитать среднюю цену закупленных книг (с помощью запроса SELECT) и умножить ее на значение 123,34, которое необходимо сохранить в отдельной переменной, вывести значение переменной на экран.

7. Подсчитать суммарную цену всех закупок книг, результат поместить в переменную, вывести значение переменной на экран.

8. Подсчитать количество книг в справочнике книг, результат поместить в переменную, вывести значение переменной на экран.

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

Сочетание ключевых слов SET и SELECT

10. Подсчитать количество поставщиков книг, результат поместить в переменную.

11. Подсчитать сумму закупок книг, результат поместить в переменную.

12. Подсчитать среднюю цену в таблице покупок книг, результат поместить в переменную.

13. Подсчитать максимальную стоимость книг в закупке, результат поместить в переменную.

Работа с датой и временем

14. Определить переменную Date1 типа дата/время. Присвоить ей значение даты 31.12.2006 в формате dd.mm.yyyy.

15. Определить переменную Date1 типа дата/время. Присвоить ей значение даты 31.12.2006 в формате mm.dd.yyyy.

16. Определить переменную Date1 типа дата/время. Присвоить ей значение даты 31.12.2006 в формате yyyy.mm.dd. Создание временной таблицы через переменную типа TABLE

Использование локальных переменных

Пример. Создать локальную таблицу с названием TEMP и полями типа дата/время, длинное целое, строка. Добавить в нее две записи с данными и вывести результат на экран.

DECLARE @temp TABLE (

id INT,

DATE DATETIME,

num BIGINT DEFAULT 0,

myname CHAR(50) DEFAULT 'Введите имя'

)

INSERT INTO @temp(id,DATE) VALUES (2,'25.11.2001')

INSERT INTO @temp(id,DATE, myname) VALUES (3,'26.11.2001', 'Кирилл')

SELECT * FROM @temp

Задания.

17. Создать локальную таблицу с названием TEMP и полями типа длинное целое, строка и значением по умолчанию «введите что-нибудь», денежный. Добавить в нее две записи с данными и вывести результат на экран.

18. Создать локальную таблицу с названием TEMP и полями типа целое, динамическая строка, бит со значением по умолчанию «1». Добавить в нее две записи с данными и вывести результат на экран.

19. Создать локальную таблицу с названием TEMP и полями типа дата/время, длинное целое, строка. Добавить в нее две записи с данными и вывести результат на экран.

20. Создать локальную таблицу с названием TEMP и полями типа дата/время, длинное целое с автонаращиванием, динамическая строка. Добавить в нее две записи с данными и вывести результат на экран.

Преобразование типов переменных

Пример. Объявить переменные типа BIT, NVARCHAR, DATETIME. Присвоить значения, соответствующие типам. Выполнить преобразование переменных типа BIT, NVARCHAR, DATETIME в FLOAT, INT, BIGINT соответственно и вывести результат на экран.

DECLARE @d BIT, @str NVARCHAR(30),@dat DATETIME

SET @d = 'True'

SET @dat = '31.01.2005 13:23:15'

SET @str = '1111111111'

SELECT CAST(@d AS FLOAT) AS float1

SELECT CAST(@dat AS BIGINT) AS bigint1

SELECT CAST(@str AS INT) AS int1

Задания.

21. Объявить переменные типа FLOAT, CHAR, TINYINT. Присвоить значения, соответствующие типам. Выполнить преобразование переменных типа FLOAT, CHAR, TINYINT в INT, DATETIME, BIT соответственно и вывести результат на экран.

22. Объявить переменные типа INT, DATETIME, BIT. Присвоить значения, соответствующие типам. Выполнить преобразование переменных типа INT, DATETIME, BIT в FLOAT, CHAR, TINYINT соответственно и вывести результат на экран.

23. Объявить переменные типа NUMERIC, VARCHAR, DATETIME. Присвоить значения, соответствующие типам. Выполнить преобразование переменных типа NUMERIC, VARCHAR, DATETIME в FLOAT, CHAR, BIGINT соответственно и вывести результат на экран.

Условная конструкция IF

Задания.

24. Подсчитать количество поставщиков в таблице Deliveries. Если их в таблице от 2 до 5, то ничего не сообщать, в противном случае вывести сообщение вида «В таблице … поставщиков» (вместо многоточия поставить точное количество поставщиков).

25. Подсчитать сумму закупок книг в таблице покупок. Если полученная сумма в диапозоне от 1000 до 5000, то ничего не сообщать, в противном случае вывести сообщение вида «Сумма закупок = …» (вместо многоточия поставить точную сумму).

26. Подсчитать среднюю стоимость закупки книг в таблице покупок. Если полученная стоимость в диапозоне от 1000 до 5000, то ничего не сообщать, в противном случае вывести сообщение вида «Средняя стоимость закупки = …» (вместо многоточия поставить точную среднюю стоимость).

27. Определить минимальную стоимость закупки книг в таблице покупок. Если полученная стоимость в диапозоне от 200 до 300, то ничего не сообщать, в противном случае вывести сообщение вида «Минимальная стоимость закупки = …» (вместо многоточия поставить точную стоимость).

Цикл WHILE

Пример. Определить количество записей в таблице Authors. Пока записей меньше 100, делать в цикле добавление записи в таблицу с автоматическим наращиванием значения ключевого поля, а вместо имени автора ставить значение „Автор не известен“.

USE DB_book

DECLARE @a INT

DECLARE @b INT

SET @a=(SELECT COUNT(Authors.Code_author) FROM authors)

SET @b=@a+1

WHILE @b < 100

BEGIN

INSERT INTO Authors(code_author, name_author) VALUES (@b, 'Автор неизвестен')

SET @b=@b+1 -- цикл работает пока значение переменной b<100

END

 

SELECT * FROM Authors

Задания.

28. Определить количество записей в таблице издательств. Пока записей меньше 20, делать в цикле добавление записи в таблицу с автоматическим наращиванием значения ключевого поля, а вместо названия издательства ставить значение „не известно“.

29. Определить количество записей в таблице поставщиков. Пока записей меньше 17, делать в цикле добавление записи в таблицу с автоматическим наращиванием значения ключевого поля, а вместо названия поставщика ставить значение „не известен“.

Объявление курсора

Пример. Создать статический курсор по данным таблицы Books с полями Code_book, Title_book.

DECLARE MyCursor1 INSENSITIVE SCROLL CURSOR FOR (SELECT Code_book, Title_book FROM Books)

OPEN MyCursor1

FETCH FIRST FROM MyCursor1

CLOSE MyCursor1

DEALLOCATE MyCursor1

Задания.

30. Создать динамический курсор по данным таблицы поставщиков (таблица Deliveries) с полями Name_delivery, Name_company.

31. Создать статический курсор по данным таблицы Books и Authors с полями Code_book, Title_book, Name_author.

32. Создать статический курсор по данным таблицы Books и Publishing_house с полями Code_book, Title_book, Publish.

Операторы для работы с курсором

Пример. Создать динамический курсор для чтения по данным таблицы Deliveries с полями Code_delivery, Name_delivery. Вывести данные 3-й записи.

DECLARE MyCursor1 SCROLL CURSOR FOR (SELECT Code_delivery, Name_delivery FROM Deliveries)

DECLARE @i BIGINT, @s CHAR(30)

OPEN MyCursor1

FETCH FIRST FROM MyCursor1 INTO @i, @s

FETCH NEXT FROM MyCursor1 INTO @i, @s

FETCH NEXT FROM MyCursor1 INTO @i, @s

PRINT @i

PRINT @s

CLOSE MyCursor1

DEALLOCATE MyCursor1

Задания.

33. Сделать текущей БД db_books. Поместить в курсор данные таблицы Purchases. Перебрать все записи таблицы Purchases. Просуммировать значения произведений полей Cost и Amount и результат сохранить в переменной Sum_table, которую после суммирования вывести на экран. Закрыть и удалить из памяти курсор.

34. Объявить статический курсор по данным таблиц Authors и Books. Вывести данные 5-й записи.

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

Базовый текст дан в отдельном файле по вариантам. Для выполнения этого блока заданий в начале программы, которую вы создаете, объявите переменную типа varchar и присвойте ей в качестве значения строку с любым базовым текстом, который будет анализироваться и/или справляться в заданиях.

Пример. Удалить в тексте лишние пробелы. Лишними считаются те, которые идут непосредственно за пробелом. Подсчитать количество исправлений.

DECLARE @text VARCHAR(100) -- Объявляем переменную типа динамической строки длинною до 100 символов

DECLARE @i INT -- Счетчик цикла

DECLARE @c CHAR (1) -- Отдельный символ строки

DECLARE @ctemp CHAR(1) -- Предыдущий по отношению к @c символ строки

DECLARE @a INT -- Количество лишних пробелов

SET @a=0;

SET @text = ' съешь еще этих мягких французских булок с изюмом '

SET @ctemp = SUBSTRING (@text,1, 1 ) -- Полагаем @ctemp равным первому символу строки

SET @i = 2

While @i < len (@text)+1

BEGIN

SET @c = SUBSTRING (@text,@i, 1 )

-- Если два соседние символа - пробелы, удаляем один из них

IF ((@ctemp= ' ') AND (@c= ' '))

BEGIN

SET @text = (SELECT stuff (@text,@i-1,1,''))

SET @a=@a+1

END

SET @ctemp = @c

SET @i=@i+1

END

SELECT @text

SELECT @a

Задания.

35. Подсчитать количество встреч каждой из следующих букв: «а», «в», «и», «п» в базовом тексте.

36. Подсчитать доли процентов встречи следующих букв: «е», «о», если суммарный процент встречаемости всех этих букв равен 100% или процент встречаемости е% + о% равен 100%.

37. По правилам оформления машинописных текстов перед знаками .,!?:; пробелы не ставятся, но обязательно ставятся после этих знаков. Удалите лишние пробелы. Подсчитать количество исправлений.

38. По правилам оформления машинописных текстов перед знаками .,!?:; пробелы не ставятся, но обязательно ставятся после этих знаков. Расставьте недостающие пробелы. Подсчитать количество исправлений.

39. Найти из исходного текста второе предложение и вернуть его в переменную Perem, а также вывести на экран весь исходный текст и найденное предложение.

40. Удалить из базового текста 2, 4, 6, 8 слова.

41. Удалить из базового текста 3, 5, 7, 10 слова.

42. Вставить в базовый текст вместо букв «а» - «АА».

43. Вставить в базовый текст вместо букв «е» и «о» - «ББ». 44. Поменять местами первое и последнее слова в базовом тексте.

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

Пример. Вывести на экран название текущего месяца и текущее время. Записать в таблицу Purchases в поле Date_order одинаковую дату поступления, которая равна 12.03.2000.

SELECT DATENAME(MONTH,GETDATE())+ ', '+ CAST(DATEPART(HOUR,GETDATE())AS CHAR(4))+

':' + CAST (DATEPART(MINUTE,GETDATE())AS CHAR(4))+':'+ CAST (DATEPART(SECOND,GETDATE())AS CHAR(4))

UPDATE PURCHASES

SET DATE_ORDER = '12.03.2000'

Задания.

45. Разобрать на отдельные составляющие текущую дату и время и вывести значения на экран в следующем порядке (вместо многоточий): «Сегодня: День = …, Месяц = …, Год = …, Часов = …, Минут = …, Секунд= …»

46. В исходный текст, сохраненный в переменной Perem, после слова » время » вставить текущее время. Результат сохранить в той же переменной Perem и вывести на экран.