2. Типы данных, управляющие конструкции языка и функции Transact-sql.

Типы данных и преобразование типов.

Встроенные и определённые пользователем типы данных хранятся в текущей базе данных в таблице systypes и могут быть просмотрены командой

SELECT * FROM systypes

1. Бинарные: требуется указывать числа в 16-ричном виде, например 0хFF. binary(n) – n определяет количество байт для хранения (до 8000 байт); под каждое значение будет выделено указанное количество байт вне зависимости от фактических размеров данных. varbinary(n) – n определяет максимальное количество байт для хранения (до 8000 байт); если массив данных m<n, то фактически отводится m+4 байта, если m>n, то данные усекаются до размера поля. image – хранятся бинарные данные размером до 2³¹-1 байт (~2 Гбайт), память выделяется страницами по 8000 байт. Использование этого поля оправдано, если требуется хранить очень длинные данные. Специальных функции отображения содержимого полей этого типа SQL Server не имеет. Пример: DECLARE @VA binary(10), @VB varbinary(10) SELECT @VA = 0xFF, @VB= 0xAC SELECT 'Значение переменной VA: ' = @VA, 'Значение переменной VB: ' = @VB SELECT 'Длина VA: ' = DATALENGTH(@VA), 'Длина VB: ' = DATALENGTH(@VB)

2. Символьные: интерпретируются в зависимости от того, какая кодовая страница была установлена при инсталляции сервера. char(n) – n<=8000, ASCII символы (1 байт), резервируется всегда n символов, если размер данных меньше, строка дополняется пробелами справа. varchar(n) – то же что и char(n), но резервируется максимум n символов. Если размер данных m<n, то дополнения пробелами не происходит. nchar(n) – n<=4000, Unicode символы (2 байта), остальное как в char(n). nvarchar(n) – n<=4000, Unicode символы (2 байта), остальное как в varchar(n). Примеры: DECLARE @name nvarchar(25) SET @name = N'Д''Артаньян' SELECT @name -- Что получится в каждом из столбцов? SELECT 1+1,'1'+'1','1'+'2'+3 Типичные функции для работы со строками: LEN(…) – возвращает длину строки в символах; LTRIM(…) и RTRIM(…) – удаляет пробелы в начале и в конце строки SUBSTRING(…) – возвращает подстроку из строки REPLACE(…) – заменяет подстроку указанным значением

3. Текстовые: позволяют хранить достаточно большие объёмы текстовой информации text – хранение ASCII текстовых блоков размером <=2³¹-1 символов (~2 Гбайт), выделяемых постранично. ntext – хранение Unicode текстовых блоков размером <=2³⁰-1 символов (~1 Гбайт), выделяемых постранично. Некоторые функции для работы с текстовыми полями: SUBSTRING(…) – возвращает подстроку текстового поля; READTEXT(…) – считывает данные из текстового поля; DATALENGTH(…) – возвращает количество байт, занимаемых данными;

4. Целочисленные: следует учитывать, что результат вычисления выражения приводится к типу данных, имеющих максимальный размер из всех участвующих в выражении. Результатом деления целого на целое будет целое с отброшенной дробной частью. tinyint – диапазон данных от 0 до 255 (длина поля 1 байт). smallint – диапазон данных от -2¹⁵ до 2¹⁵-1 (длина поля 2 байта). int или integer – диапазон данных от -2³¹ до 2³¹-1 (длина поля 4 байта). bigint – диапазон данных от -2⁶³ до 2⁶³-1 (длина поля 8 байт).

5. Нецелочисленные: с фиксированной и плавающей точкой. dec или decimal [(p[,s])] или numeric [(p[,s])] – диапазон от –(10³⁸-1) до (10³⁸-1). Содержит фиксированное количество знаков до и после точки: p – общее число знаков, s – число знаков после точки (0 <= s <= p <= 38). Длина поля от 5 до 17 байт. float [(n)] – число в виде мантиссы и порядка. Максимальный диапазон от -1.79³⁰⁸ до 1.79³⁰⁸. Значение n определяет количество бит, используемых для хранения мантиссы (n<=53). Длина поля 4 или 8 байт. double precision – частный случай float(53). real – частный случай float(24). Диапазон данных от -3.4³⁸ до 3.4³⁸. Примеры: DECLARE @VR real SET @VR=1.23E4 SELECT @VR, DATALENGTH(@VR) Не рекомендуется указывать нецелочисленные типы данных в конструкциях WHERE и для построения индексов или первичных ключей, так как скорость обработки таких полей существенно ниже чем целочисленных Преобразование типов данных из числа в строку: DECLARE @VR float, @VS varchar(20) SET @VR=3.14 SET @VS=STR(@VR,4,2)+'15' – простая функция преобразования типов SELECT @VS SET @VS=CAST($123.45 AS varchar(10)) – универсальная функция преобразования SELECT @VS SELECT CAST ('$54321' AS money) Взаимозаменяемой для функции CAST является функция CONVERT Некоторые функции для работы с числовыми типами: ISNUMERIC(…) – проверяет, имеет ли выражение числовой тип данных (1, если да); RAND() – вычисляет случайное число с плавающей точкой в диапазоне [0…1]; POWER(…) – возведение числа в степень (SELECT power(2,8) ); PI(…) – возвращает значение Пи.

6. Даты и времени: типы данных позволяют одновременно хранить время и дату. datetime – В первых 4 байтах хранится смещение относительно 1 января 1753 г. (до этого летоисчисление шло по Григорианскому и Юлианскому календарям) до 31.12.9999. Последние 4 байта – время после полуночи с точностью 3.33 мс. smalldatetime – диапазон от 1.01.1900 до 6.06.2079 с точностью до 1 мин. Дополнительные возможности для ввода и вывода дат предоставляет команда SET DATEFORMAT xxx, где xxx может быть: mdy, dmy, ymd, ydm, myd, dym.Примеры: SET LANGUAGE 'русский' DECLARE @DV datetime SET @DV='21 октябрь 2003 23:19' SELECT @DV Функция ISDATE(<выражение>) возвращает 1, если <выражение> может быть правильно конвертировано в дату, и 0 в противном случае. Некоторые функции для работы с датой и временем: GETDATE() – возвращает текущее системное время; YEAR(…) – возвращает год из указанной даты; DATEADD(…) – добавляет к дате указанный временной интервал SELECT year(getdate()) DECLARE @Str1 char(10) IF DAY(GETDATE())<15 SET @Str1='первая' ELSE SET @Str1='вторая' SELECT 'Сейчас '+RTRIM(@Str1)+' половина месяца'

7. Денежные: поддерживается точность 4 знака после десятичной точки. money – диапазон от -922 337 203 685 477.5808 до [phone] 477.5807, длина 8 байт. smallmoney – диапазон от -214748.3648 до +214748.3647, длина 4 байта. Примеры:CREATE TABLE MyMoney ( ID bigint IDENTITY (1,1) PRIMARY KEY, Value money NULL ) INSERT MyMoney VALUES ($127.35) SELECT * FROM MyMoney Специальные: bit–данныепринимаютзначения0 / 1 / NULL.Память выделяется побайтно. timestamp – счетчик-идентификатор записей, уникальный в пределах одной базы данных (не имеет ничего общего с меткой времени). Длина 8 байт. Не может использоваться для объявления переменных. Не должен использоваться в составе первичного ключа. uniqueidentifier – глобально уникальный идентификатор записи (GUID). Идентификатор уникален в масштабе планеты. Представляет собой 16-байтовую последовательность, составляющуюся с помощью функции NEWID() из MAC-адреса сетевой карты и внутреннего таймера процессора. sysname – предназначен для хранения имён объектов баз данных SQL Server – столбцов, таблиц, индексов, представлений, хранимых процедур и др. sql_variant – позволяет в одном и том же столбце хранить значения любого другого доступного типа данных (за исключением text, ntext, image, timestamp, cursor, table и самого sql_variant) Пример: DECLARE @Var1 int, @Var2 nvarchar(15), @Var3 datetime, @VA sql_variant SET @Var1=10 SET @Var2='Просто строка' SET @Var3='23.08.1969' SET @VA=@Var1+5 SELECT @VA SET @VA=@Var2 SELECT @VA SET @VA=@Var3 SELECT @VA Функция SQL_VARIANT_PROPERTY возвращает информацию о природе данных, хранящихся под типом sql_variant. cursor – ссылка на объект базы данных – курсор. Подробнее о курсорах см. далее. table – временная таблица (массив). Может использоваться только для переменных и значений, возвращаемых функциями пользователя. Пример:DECLARE @VarTable TABLE ( Col1 int NOT NULL IDENTITY (1,1) PRIMARY KEY, Col2 nvarchar(15) ) INSERT INTO @VarTable (Col2) VALUES ('Первая строка') SELECT * FROM @VarTable

8. Пользовательские типы данных: также возможно создавать в MS SQL Server. Для этих целей предусмотрена специальная хранимая процедура sp_addtype. Первым параметром при вызове этой процедуры указывается имя пользовательского типа, вторым – имя системного типа, на основе которого строится пользовательский. Третий параметр указывает, разрешены или запрещены значения NULL. Пример: sp_addtype nvc15, ’nvarchar(15)’, NONULL

Управляющие конструкции Transact-SQL

Их весьма мало. К ним относятся:

1. Блок BEGIN … END. Выполняет группировку двух и более TSQL команд. Используется в конструкциях ветвлений, условий и циклов. Допускаются вложенные друг в друга блоки.

2. Конструкция IF … ELSE. Переход по условию. Допускается в каждой ветви исполнять одну команду или использовать блок BEGIN…END. Пример:

PRINT GetDate()

IF Day(GetDate())=1 AND Month(GetDate())=1

BEGIN

PRINT 'Сегодня Новый Год!'

IF CURRENT_USER='dbo'

PRINT 'Администратор СУБД - это судьба...'

END

ELSE PRINT 'Сегодня точно не Новый Год.'

3. Конструкция CASE … END. Реализует множественное ветвление. Особенностью конструкции является то, что её можно использовать непосредственно в выражениях, например, при выполнении запроса. Пример:

SELECT au_lname, au_fname,

CASE state

WHEN ‘CA’ THEN ‘Калифорния’

WHEN ‘UT’ THEN ‘Юта’

ELSE state

END FROM authors

WHERE au_lname LIKE ’O%’

4. Конструкция COALESCE возвращает первое значение, не равное NULL, из перечня аргументов функции. Пример:

DECLARE @I1 int, @C1 char, @C2 char, @C3 char

SET @I1 = 10

SET @C1 = 'A'

SET @C2 = @C1 + @C3

PRINT COALESCE(@C2,@I1)

Так как конкатенацией любой строки со значением NULL является NULL, то результатом выполнения этой последовательности команд будет значение 10.

5. Конструкция WHILE… . Организует циклы в TSQL. Это единственный способ организовать циклы в этом языке. Цикл можно принудительно остановить, если в его теле выполнить команду BREAK, и перезапустить из любого места внутри тела цикла с помощью команды CONTINUE.

DECLARE @I1 int

SET @I1 = 1

WHILE @I1 < 8

BEGIN

PRINT 'Квадрат числа '+Str(@I1)+' есть '+Str(Square(@I1))

SET @I1 = @I1 + 1

END

Дополнительные предикаты.

К уже отмеченным ранее предикатам условных конструкций в TSQL добавляются операторы ALL и ANY. C помощью ALL выполняется сравнение скалярного выражения со всеми значениями, возвращаемыми подзапросом. Если логическое условие выполняется для всех возвращаемых подзапросом значений, тогда результат выполнения условия TRUE.

IF 1 = ALL (SELECT contract FROM authors) PRINT ‘Все авторы подписали контракт’

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

IF 0 = ANY (SELECT contract FROM authors) PRINT ‘Не все авторы подписали контракт’

Комментарии.

Для переменных TSQL не определено понятие «массив», следовательно все операции, характерные для него, следует выполнять во временных или постоянных таблицах.