Лабораторная работа 1 создание баз данных (БД) в microsoft sql server
 Скачать 1.1 Mb.
|
|
Лабораторная работа №3 ОСВОЕНИЕ ПРОГРАММИРОВАНИЯ С ПОМОЩЬЮ ВСТРОЕННОГО ЯЗЫКА TRANSACT SQL В MICROSOFT SQL SERVER Цель работы –знакомство с основными приципами программирования вMS SQL Server средствами встроенного языка Transact SQL. Содержание работы: Знакомство с правилами обозначения синтаксиса команд в справоч- ной системе MS SQL Server (утилита Books Online). Изучение правил написания программ на Transact SQL. Изучение правил построения идентификаторов, правил объявления переменных и их типов. Изучение работы с циклами и ветвлениями. Изучение работы с переменными типа Table и Cursor. Проработка всех примеров, анализ результатов их выполнения. Выполнение индивидуальных заданий по вариантам. Пояснения к выполнению работы Для освоения программирования используем пример базы данных c названием DB_Books, которая была создана в лабораторной работе №1. При выполнении примеров и заданий обращайте внимание на соответствие названий БД, таблиц и других объектов проекта. 20 Специальные знаки и простейшие операторы в Transact SQL
Идентификаторы –это имена объектов,на которые можно ссылаться впрограмме, написанной на языке Transact SQL. Первый символ может со-стоять из букв английского алфавита или “_”, “@”, “#”. Остальные до-полнительно из цифр и «$». Имя идентификатора не должно совпадать с зарезервированным сло-вом. Для ограничителей идентификаторов при установленном параметре SET QUOTED_IDENTIFIER ON можно использовать как квадратные скобки, так и одинарные кавычки, а строковые значения только в одинарных кавычках (режим по умолчанию). Если использовать установленный параметр в режиме SET QUOTED_IDENTIFIER OFF, то в качестве ограничителей идентификаторов можно использовать только квадратные скобки, а строковые значения указываются в одинарных или двойных кавычках. Переменные используются для сохранения промежуточных данных в хра-нимых процедурах и функциях. Все переменные считаются локальными. Имя переменной должно начинаться с @. 21 Объявление переменных Синтаксис в обозначениях MS SQL Server: DECLARE @имя_переменной1 тип_переменной, …, @имя_переменнойN тип_переменной Если тип переменной предполагает указание размера, то используется следующий сиснтаксис для объявления переменных: DECLARE @имя_переменной1 тип_переменной (размер), …, @имя_переменнойN тип_переменной(размер) Пример_:_DECLARE_@a_INTSELECT_@a_=_COUNT(*)_FROM_AuthorsПример'>Пример_:_DECLARE_@a_INT,_@b_numeric(10,2)SET_@a_=_20SET_@b_=_(@a+@a)/15SELECT_@b_--вывод_на_экран_результатаПрисвоение_с_помощью_SELECT'>Пример_:_DECLARE_@a_INT,_@b_numeric(10,2)DECLARE_@str_CHAR(20)Присвоение_значений_переменным_и_вывод_значений_на_экран_Присвоение_с_помощью_SET'>Пример: DECLARE @a INT, @b numeric(10,2) DECLARE @str CHAR(20) Присвоение значений переменным и вывод значений на экран Присвоение с помощью 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 FROM Authors В данном примере в переменную поместится последнее значение из результата запроса. Сочетание ключевых слов SET и SELECT Пример: DECLARE @a INT SET @a = (SELECT COUNT(*) FROM Authors) 22 Работа с датой и временем Оператор SET DATEFORMAT dmy | ymd | mdy задает порядок следо-вания компонентов даты. Пример: SET DATEFORMAT dmy DECLARE @d DateTime SET @d = ’31.01.2005 13:23:15’ SET @d = @d+1 SELECT @d Создание временной таблицы через переменную типа TABLE Объявляется через DECLARE с указанием в скобках столбцов табли-цы, их типов, размеров, значений по умолчанию, а также индексов типа PRIMARY KEY или UNIQUE. Пример: DECLARE @mytable 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 Преобразование типов переменных Функция CAST возвращает значение, преобразованное к указанному типу: CAST(@переменная или значение AS требуемый_тип_данных) Пример: DECLARE @d DateTime, @str char(20) SET @d = ’31.01.2005 13:23:15’ SET @str = CAST(@d AS Char(20)) SELECT 2str Функция CONVERT возвращает значение, преобразованное к указан-ному типу по заданному формату. Изучить дополнительно, по желанию. 23 Операторские скобки BEGIN /* в них нельзя помещать команды, изменяющие структуры объектов БД. Операторские скобки должны содержать хотя бы один оператор. Тре-буются для конструкций поливариантных ветвлений, условных и цикличе-ских конструкций */ END Условная конструкция IF Синтаксис: IF условие Набор операторов1 ELSE Набор операторов2 Пример: DECLARE @a INT DECLARE @str CHAR(30) SET @a = (SELECT COUNT(*) FROM Authors) IF @a >10 BEGIN SET @str = 'Количество авторов больше 10' SELECT @str END ELSE BEGIN SET @str = 'Количество авторов = ' + str(@a) SELECT @str END Цикл WHILE Синтаксис: WHILE Условие Набор операторов1 BREAK Набор опреторов2 CONTINUE Конструкции BREAK и CONTINUE являются необязательными. Цикл можно принудительно остановить, если в его теле выполнить ко-манду BREAK. Если же нужно начать цикл заново, не дожидаясь выпол-нения всех команд в теле, необходимо выполнить команду CONTINUE. 24 Пример: DECLARE @a INT SET @a = 1 WHILE @a <100 BEGIN PRINT @a -- вывод на экран значения переменной IF (@a>40) AND (@a<50) BREAK --выход и выполнение 1-й команды за циклом ELSE SET @a = @a+rand()*10 CONTINUE END PRINT @a Объявление курсора CURSOR – это набор строк, являющийся результатом выполнения за-проса. В один момент времени доступна лишь одна строка (текущая), по курсору можно передвигаться и получать доступ к элементарным данным. При объявлении курсора создается временная копия данных, которая со-храняется в БД tempdb. Динамический курсор – данные в курсоре могут быть изменены. Статический курсор – данные в курсоре не меняются. Стандартный способ объявления курсора, синтаксис в обозначениях MS SQL Server: DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] Примеры объявления курсоров: DECLARE MyCursor1 CURSOR FOR (select * from Authors) /*объявили курсор с названием MyCursor1, который содержит всю инфор-мацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является динамическим.*/ DECLARE MyCursor1 INSENSITIVE CURSOR FOR (select * from Authors) /*объявили курсор с названием MyCursor1, который содержит всю инфор-мацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является статическим.*/ DECLARE MyCursor1 SCROLL CURSOR FOR (select * from Authors) 25 /*объявили курсор с названием MyCursor1, который содержит всю инфор-мацию об авторах, двигаться по нему можно в любом направлении. Курсор является динамическим.*/ DECLARE MyCursor1 INSENSITIVE SCROLL CURSOR FOR (select * from Authors) /*объявили курсор с названием MyCursor1, который содержит всю инфор-мацию об авторах, двигаться по нему можно в любом направлении. Курсор является статическим.*/ DECLARE MyCursor1 CURSOR FOR (select * from Authors) FOR READ ONLY /*объявили курсор с названием MyCursor1, который содержит всю инфор-мацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является динамическим. Данные доступны только для чтения.*/ DECLARE MyCursor1 CURSOR FOR (select * from Authors) FOR UPDATE /*объявили курсор с названием MyCursor1, который содержит всю инфор-мацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является динамическим. Данные курсора можно ме-нять.*/ Операторы для работы с курсором Прежде чем обратиться к данным курсора, его нужно после объявления открыть. Синтаксис оператора 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 --здесь операторы работы с курсором 26 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 [ ,...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 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||