Язык sql Выражения, операторы, переменные, управляющие конструкции, преобразование типов в языке sql.
Специальные знаки и простейшие операторы в Transact SQL
Знак |
Назначение |
Знак |
Назначение |
* |
Знак умножения |
" " |
В них заключают строковые значения, если SET QUOTED_IDENTIFIER OFF |
- |
Знак вычитания |
‘ ’ |
В них заключают строковые значения |
% |
Остаток от деления двух чисел |
<> |
Не равно |
+ |
Знак сложения или конкатенации (объединение двух строк в одну) |
[ ] |
Аналог кавычек, в них можно заключать названия идентификаторов, если в их названиях встречаются пробелы |
= |
Знак равенства или сравнения |
!< |
Не менее чем |
<= |
Меньше или равно |
!> |
Не более чем |
>= |
Больше или равно |
> |
Больше |
!= |
Не равно |
< |
Меньше |
@ |
Ставится перед именем переменной |
. |
Разделяет родительские и подчиненные объекты |
@@ |
Указывает на системные функции |
/ |
Знак деления |
-- |
Однострочный комментарий или комментарий с текущей позиции и до конца строки |
/* */ |
Многострочный комментарий |
Функция POWER - возводит число в степень
Константы
В стандарте определены следующие константы.
Для числовых типов данных определены константы в виде последовательности цифр с необязательным заданием знака числа и десятичной точкой. То есть правильными будут константы:
213 -314 612.716 + 551.702
Константы с плавающей запятой задаются, как и в большинстве языков программирования, путем задания мантиссы и порядка, разделенных символом E, например:
2.9E-4 -134.235E7 0.54267E18
При установке параметра
SET QUOTED_IDENTIFIER ON
строковые константы должны быть заключены в одинарные кавычки (режим по умолчанию).
Если использовать установленный параметр в режиме
SET QUOTED_IDENTIFIER OFF
строковые константы должны быть заключены в одинарных или двойных кавычках.
Переменные.
Локальная переменная Transact-SQL представляет собой объект, содержащий некоторое значение определенного типа.
Прежде чем использовать какую-либо переменную, ее следует объявить. Объявление переменной выполняется командой DECLARE, имеющей следующий формат:
DECLARE {@имя_переменной [AS] тип_данных }
[,...n]
DECLARE @d DateTime, @str As char(20)
Инструкция DECLARE также присваивает созданной переменной значение NULL.
Областью видимости переменной называют диапазон инструкций Transact-SQL, которые могут к ней обращаться. Переменные имеют локальную область видимости и доступны только внутри пакета или процедуры, где они объявлены. Пакет обозначается инструкцией GO Transact-SQL (см. далее).
Например, следующий сценарий содержит синтаксическую ошибку, поскольку переменная объявлена в одном пакете, а используется в другом:
DECLARE @MyVariable int;
SET @MyVariable = 1;
-- Конец пакета.
GO
-- @MyVariable не существует.
-- Этот оператор SELECT выдаст ошибку, так как ссылается на несуществующую переменную @MyVariable.
SELECT EmployeeID, NationalIDNumber, Title
FROM HumanResources.Employee
WHERE EmployeeID = @MyVariable;
В следующем примере вложенная область видимости, созданная для выполнения процедуры sp_executesql, не имеет доступа к переменной, объявленной в более высокой области видимости, и возвращает ошибку:
DECLARE @MyVariable int;
SET @MyVariable = 1;
EXECUTE sp_executesql N'SELECT @MyVariable'; -- ошибка
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@MyVariable1".
Примечание: Системная процедура sp_executesql выполняет инструкцию Transact-SQL или пакет инструкций, которые могут выполняться много раз или создаваться динамически.
Синтаксис:
sp_executesql [ @stmt = ] stmt
где: [ @stmt = ] stmt строка в Юникоде, содержащая инструкцию или пакет Transact-SQL. Аргумент stmt должен представлять собой константу в Юникоде или переменную в этом же формате.
Присвоение значений переменным и вывод значений
Значения переменной можно присвоить посредством команд SET и SELECT.
Присвоение с помощью SET – обычное присвоение, синтаксис:
SET @имя_переменной = значение.
Использование SET для присваивания значения локальной переменной:
Пример:
DECLARE @a INT
SET @a=10
Пример:
DECLARE @a INT, @b numeric(10,2)
SET @a = 20
SET @b = (@a+@a)/15
SELECT @b --вывод результата
Результат: 2.00 !!!!!!!!!!!!!!!!!!!!!!!!!!!!! Использование @a INT
Присвоение с помощью SELECT (Для присвоения значений переменным рекомендуется использовать SET @local_variable вместо SELECT @local_variable).
Присвоение с помощью SELECT – помещение результата запроса в переменную. SELECT @local_variable обычно используется для возвращения одиночного значения в переменную.
Синтаксис:
SELECT { @local_variable = expression } [ ,...n ] [ ; ]
@local_variable - объявленная переменная, которой должно быть присвоено значение.
Имя переменной любого типа кроме cursor, text, ntext, image или table. Имена переменных должны начинаться с одного символа @.
expression - любое допустимое выражение. Включает в себя скалярный вложенный запрос.
Одна инструкция SELECT может инициализировать несколько локальных переменных.
Пример:
USE AdventureWorks ;
GO
DECLARE @var1 nvarchar(30);
SELECT @var1 = 'Generic Name';
SELECT @var1 = Name
FROM Sales.Store
WHERE CustomerID = 2 ;
SELECT @var1 AS 'Company Name';
Результат:
Этот же запрос без использования переменной @var1
SELECT Name
FROM Sales.Store
WHERE CustomerID = 2 ;
Однако если аргумент expression является именем столбца, может вернуться несколько значений. Если инструкция SELECT возвращает более одного значения, переменной присваивается последнее возвращенное значение.
Если инструкция SELECT не возвращает ни одной строки, переменная сохраняет свое текущее значение.
В следующем примере переменной @var1 присвоено значение Generic Name. Запрос к таблице Store не возвращает строк, потому что в ней отсутствует значение, указанное для CustomerID. Переменная сохраняет значение Generic Name.
USE AdventureWorks ;
GO
DECLARE @var1 nvarchar(30);
SELECT @var1 = 'Generic Name';
SELECT @var1 = Name
FROM Sales.Store
WHERE CustomerID = 1000 ;
SELECT @var1 AS 'Company Name';
Результат:
Company Name
---------------------------------
Generic Name
Если аргумент expression является скалярным вложенным запросом, который не возвращает значений, переменная принимает значение NULL.
В следующем примере вложенный запрос используется для присвоения значения @var1. Так как значение, заданное для CustomerID, не существует, вложенный запрос не возвращает значение, и переменная принимает значение NULL.
USE AdventureWorks ;
GO
DECLARE @var1 nvarchar(30)
SELECT @var1 = 'Generic Name'
SELECT @var1 = (SELECT Name
FROM Sales.Store
WHERE CustomerID = 1000)
SELECT @var1 AS 'Company Name'
Результат:
Company Name
---------------------------------
NULL
Сочетание ключевых слов SET и SELECT
Пример:
DECLARE @a INT
SET @a = (SELECT COUNT(*) FROM Authors)
Пример:
DECLARE @empname AS NVARCHAR(61);
SET @empname = (SELECT firstname + N' ' + lastname
FROM HR.Employees
WHERE empid = 3);
SELECT @empname AS empname;
GO
Инструкция SET может обрабатывать только одну переменную в каждый момент времени.
Пример:
DECLARE @firstname AS NVARCHAR(20), @lastname AS NVARCHAR(40);
SET @firstname = (SELECT firstname
FROM HR.Employees
WHERE empid = 3);
SET @lastname = (SELECT lastname
FROM HR.Employees
WHERE empid = 3);
SELECT @firstname AS firstname, @lastname AS lastname;
GO
Результат:
Поддерживается нестандартная инструкция присваивания.
Пример:
DECLARE @firstname AS NVARCHAR(20), @lastname AS NVARCHAR(40);
SELECT
@firstname = firstname,
@lastname = lastname
FROM HR.Employees
WHERE empid = 3;
SELECT @firstname AS firstname, @lastname AS lastname;
GO
Когда инструкция SELECT – присваивание заканчивается, значения переменных равны значениям строки, к которой SQL сервер обратился последней.
Скалярный запрос завершается аварийно, если возвращается больше одного значения.
Пример:
DECLARE @empname AS NVARCHAR(61);
SET @empname = (SELECT firstname + N' ' + lastname
FROM HR.Employees
WHERE mgrid = 2);
SELECT @empname AS empname;
Выдается ошибка:
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Пакеты
Пакет — это одна или несколько инструкций T-SQL, отправляемых клиентским приложением SQL Server для выполнения как единого целого. Пакет проходит синтаксический анализ, разрешение имен (проверку наличия объектов и столбцов, на которые есть ссылки, проверку прав доступа) и оптимизацию как единый блок.
Следует помнить разницу между транзакциями и пакетами.
Транзакция — это неделимый рабочий блок.
Пакет может содержать несколько транзакций, и транзакция может быть представлена частями в разных пакетах. Когда транзакция отменяется или откатывается в ходе выполнения, SQL Server удаляет ту часть действий, которые были выполнены с момента запуска транзакции, независимо от того, где находится начало пакета.
Клиентские прикладные интерфейсы API, например, ADO.NET, имеют методы для предоставления программе SQL Server пакета программного кода для выполнения. Утилиты SQL Server, такие как SQL Server Management Studio, SQLCMD и OSQL, предоставляют клиентскую команду go, обозначающую окончание пакета. Имейте в виду, что команда GO — это клиентская, а не серверная команда T-SQL.
Пакет как единица синтаксического анализа
Пакет — это набор команд, которые подвергаются синтаксическому анализу и выполняются как единый блок. Если синтаксическая проверка будет успешна, SQL Server затем попытается выполнить пакет. В случае синтаксической ошибки в пакете весь пакет не передается SQL Server для выполнения.
Например, в следующем программном коде три пакета, во втором из них есть синтаксическая ошибка (во втором запросе fom вместо FROM):
PRINT 'First batch';
USE TSQLFundamentals2008;
GO
-- Пакет с ошибкой
PRINT 'Second batch';
SELECT custid FROM Sales.Customers;
SELECT orderid FOM Sales.Orders;
GO
PRINT 'Third batch';
SELECT empid FROM HR.Employees;
GO
Поскольку во втором пакете есть синтаксическая ошибка, весь пакет не передается SQL Server для выполнения. Первый и третий пакеты проходят синтаксическую проверку и, следовательно, передаются для исполнения. Этот программный код формирует следующий результат, показывающий, что второй пакет целиком не был выполнен:
First batch
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'Sales'.
Third batch
Пакеты и переменные
Переменные локальны по отношению к пакету, в котором определены. Если сослаться на переменную, определенную в другом пакете, то получите ошибку, сообщающую о том, что переменная не определена.
Например, в следующем программном коде в одном пакете переменная объявлена и выведена на печать, а другом пакете делается попытка напечатать эту переменную.
DECLARE @i AS INT;
SET @i = 10;
--Успешная попытка
PRINT @i;
GO
--Неудачная попытка
PRINT @i;
Ссылка на переменную в первой инструкции print корректна, т. к. она появляется в том же пакете, где объявлена переменная, а вторая ссылка не корректна. Таким образом, первая инструкция print вернет значение переменной (10), а вторая завершится аварийно.
Результат:
10
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@i".
Инструкции, которые не могут комбинироваться в одном пакете
Следующие инструкции не могут комбинироваться с другими инструкциями в одном и той же пакете: create default, create function, create procedure, create rulE, create schema, create trigger и create vieW.
Например, в следующем программном коде есть инструкция if, за которой идет инструкция create view в том же самом пакете, и, следовательно, она не допустима.
IF OBJECT_ID('Sales.MyView', 'V') IS NOT NULL
DROP VIEW Sales.MyView;
CREATE VIEW Sales.MyView AS
SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY YEAR(orderdate);
GO--один пакет
Попытка выполнить этот программный код приведет к сообщению об ошибке:
Msg 111, Level 15, State 1, Line 3
‘CREATE VIEW’ must be the first statement in a query batch.
Для решения этой проблемы можно включить инструкцию if и create view в разные пакеты, добавив команду GO после инструкции if.
IF OBJECT_ID('Sales.MyView', 'V') IS NOT NULL
DROP VIEW Sales.MyView;
GO -- первый пакет
CREATE VIEW Sales.MyView AS
SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY YEAR(orderdate);
GO --второй пакет
Пакет как единица разрешения имен
Пакет — это блок разрешения имен, т. е. проверка наличия объектов и столбцов выполняется на уровне пакета. Об этом следует помнить, проектируя границы пакета.
Когда вы применяете к объекту изменения схемы и пытаетесь в том же пакете манипулировать данными объекта, SQL Server может все еще не знать об изменениях схемы и аварийно завершить инструкцию обработки данных с ошибкой разрешения имен.
Пример: создание в базе данных tempdb таблицы Т1, содержащей один столбец col1:
USE tempdb;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1(col1 INT);
GO
--вставить в Т1 столбец со12 и запрос с новым столбцом в том же пакете
ALTER TABLE dbo.T1 ADD col2 INT;
SELECT col1, col2 FROM dbo.T1;
GO
Несмотря на то, что программный код может показаться совершенно корректным, пакет завершится аварийно на стадии разрешения имен с сообщением об ошибке:
Msg 207, Level 16, State 1, Line 2 Invalid column name 'col2'
Во время разрешения имен в инструкции SELECT у таблицы Т1 был только один столбец, и ссылка на столбец со12 вызвала ошибку.
Правильный пример:
ALTER TABLE dbo.T1 ADD col2 INT;
GO
SELECT col1, col2 FROM dbo.T1;
GO
Вариант GO n
В клиентских средствах SQL Server 2005 команда GO была усовершенствована, у нее появилась возможность поддержки аргумента, указывающего, сколько раз нужно повторить выполнение пакета.
Новый вариант применяется, если вы хотите повторить пакет.
Пример:
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1(col1 INT IDENTITY);
GO
SET NOCOUNT on;
GO
--Execute batch 100 times
INSERT INTO dbo.T1 DEFAULT VALUES;
GO 100
Сообщение:
Beginning execution loop
Batch execution completed 100 times.
Выполнилось 100 вставок.
Инструкция Transact-SQL не может располагаться на той же строке, что и команда GO. Однако строка с командой GO может содержать комментарии.
При использовании команды GO нужно соблюдать требования, предъявляемые к пакетам.
Например, при любом вызове хранимой процедуры после первой инструкции пакета нужно использовать ключевое слово EXECUTE.
Системная процедура sp_who предоставляет сведения о текущих пользователях, сеансах и процессах в экземпляре компонента Microsoft SQL Server Database Engine.
SELECT @@VERSION1;
-- Ошибка: должно быть EXEC sp_who, если не первый оператор
sp_who
GO
Сообщение об ошибке:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'sp_who'.
Правильный пример:
SELECT @@VERSION ;
exec sp_who
GO
Результат:
Команда GO — клиентская, а не серверная команда T-SQL.
Выражения
Выражение — это сочетание идентификаторов, значений и операторов, которое SQL Server 2005 может вычислить для получения результата.
Выражение может быть:
константой
функцией
именем столбца
переменной
вложенным запросом
функцией CASE, NULLIF или COALESCE2
Выражение также может быть построено из комбинаций этих сущностей, соединенных операторами.
Выражение также может быть вычислением, например (price * 1.5) или (price + sales_tax).
В выражениях символы и значения типа datetime необходимо заключать в одинарные кавычки.
В следующей инструкции SELECT символьная константа B%, используемая в качестве шаблона для предложения LIKE, должна быть заключена в одинарные кавычки:
SELECT LastName, FirstName
FROM AdventureWorks.Person.Contact
WHERE LastName LIKE 'B%'
В следующей инструкции SELECT значение даты заключено в одинарные кавычки.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.HireDate
FROM Person.Contact c JOIN HumanResources.Employee e
ON c.ContactID = e.EmployeeID
WHERE e.HireDate = 'July 1, 2003';
GO