Основы Transact-SQL. Семь смертных грехов программиста на T-SQL Использование Multi-statement UDF

SQL (Structured Query Language) - это универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных (язык структурированных запросов).

SQL в его исходном виде является информационно-логическим языком, а не языком программирования, но вместе SQL предусматривает возможность его процедурных расширений, с учётом которых язык уже вполне может рассматриваться в качестве языка программирования.

В настоящее время широко распространенны следующие спецификации SQL:

Базы данных и спецификации SQL
Тип базы данных Спецификация SQL
Microsoft SQL Transact-SQL
Microsoft Jet/Access Jet SQL
MySQL SQL/PSM (SQL/Persistent Stored Module)
Oracle PL/SQL (Procedural Language/SQL)
IBM DB2 SQL PL (SQL Procedural Language)
InterBase/Firebird PSQL (Procedural SQL)

В данной статье будет рассмотрена спецификация Transact-SQL, которая используется серверами Microsoft SQL. А так как база у всех спецификаций SQL одинаковая, то большинство команд и сценариев с легкостью переносятся на другие типы SQL.

Определение

Transact-SQL - это процедурное расширение языка SQL компаний Microsoft. SQL был расширен такими дополнительными возможностями как:

  • управляющие операторы,
  • локальные и глобальные переменные,
  • различные дополнительные функции для обработки строк, дат, математики и т.п.,
  • поддержка аутентификации Microsoft Windows

Язык Transact-SQL является ключом к использованию SQL Server. Все приложения, взаимодействующие с экземпляром SQL Server, независимо от их реализации и пользовательского интерфейса, отправляют серверу инструкции Transact-SQL.

Опытная база данных

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

Итак, чтобы создать базу данных и заполнить ее значениями, необходимо открыть консоль выполнения команд и запросов SQL сервера и выполнить следующий сценарий:

Создание базы данных USE master CREATE DATABASE TestDatabase GO -- Создание таблиц USE TestDatabase CREATE TABLE Users (UserID int PRIMARY KEY, UserName nvarchar(40), UserSurname nvarchar(40), DepartmentID int, PositionID int) CREATE TABLE Departments (DepartmentID int PRIMARY KEY, DepartmentName nvarchar(40)) CREATE TABLE Positions (PositionID int PRIMARY KEY, PositionName nvarchar(40), BaseSalary money) CREATE TABLE (CustomerID int PRIMARY KEY, CustomerName nvarchar(40), CustomerAddress nvarchar(255)) CREATE TABLE (OrderID int PRIMARY KEY, CustomerID int, UserID int, text) GO -- Заполнение таблиц USE TestDatabase INSERT Users VALUES (1, "Ivan", "Petrov", 1, 1) INSERT Users VALUES (2, "Ivan", "Sidorov", 1, 2) INSERT Users VALUES (3, "Petr", "Ivanov", 1, 2) INSERT Users VALUES (4, "Nikolay", "Petrov", 1, 3) INSERT Users VALUES (5, "Nikolay", "Ivanov", 2, 1) INSERT Users VALUES (6, "Sergey", "Sidorov", 2, 3) INSERT Users VALUES (7, "Andrey", "Bukin", 2, 2) INSERT Users VALUES (8, "Viktor", "Rybakov", 4, 1) INSERT Departments VALUES (1, "Production") INSERT Departments VALUES (2, "Distribution") INSERT Departments VALUES (3, "Purchasing") INSERT Positions VALUES (1, "Manager", 1000) INSERT Positions VALUES (2, "Senior analyst", 650) INSERT VALUES (1, "Alex Company", "606443, Russia, Bor, Lenina str., 15") INSERT VALUES (2, "Potrovka", "115516, Moscow, Promyshlennaya str., 1") INSERT VALUES (1, 1, 1, "Special parts") GO

Примечание. В Microsoft SQL Server 2000 запросы выполняются в приложении Query Analyzer. В Microsoft SQL Server 2005 запросы выполняются в SQL Server Management Studio.

В результате работы сценария на SQL сервере будет создана база данных TestDatabase с пятью пользовательскими таблицами: Users, Departments, Positions, Local Customers, Local Orders.

Users
UserID UserName UserSurname DepartmentID PositionID
1 Ivan Petrov 1 1
2 Ivan Sidorov 1 2
3 Petr Ivanov 1 3
4 Nikolay Petrov 1 3
5 Nikolay Ivanov 2 1
6 Sergey Sidorov 2 3
7 Andrey Bukin 2 3
8 Viktor Rybakov 4 1
Positions
PositionID PositionName BaseSalary
1 Manager 1000
2 Senior analyst 650
3 Analyst 400
Local Orders
OrderID CustomerID UserID Description
1 1 1 Special parts
Departments
DepartmentID DepartmentName
1 Production
2 Distribution
3 Purchasing
Local Customers
CustomerID CustomerName CustomerAddress
1 Alex Company 606443, Russia, Bor, Lenina str., 15
2 Potrovka 115516, Moscow, Promyshlennaya str., 1

Элементы синтаксиса

Директивы сценария

Директивы сценария - это специфические команды, которые используются только в MS SQL. Эти команды помогают серверу определять правила работы со скриптом и транзакциями. Типичные представители: GO - сигнализирует SQL-серверу об окончании сценария, EXEC (или EXECUTE) - выполняет процедуру или скалярную функцию.

Комментарии

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

  • -- - строковый комментарий исключает из выполнения только одну строку, перед которой стоят два минуса.
  • /* */ - блоковый комментарий исключает из выполнения целый блок команд, заключенный в указанную конструкцию.

Типы данных

Как и в языках программирования, в SQL существуют различные типы данных для хранения переменных:

  • Числа - для хранения числовых переменных (int, tinyint, smallint, bigint, numeric, decimal, money, smallmoney, float, real).
  • Даты - для хранения даты и времени (datetime, smalldatetime).
  • Символы - для хранения символьных данных (char, nchar, varchar, nvarchar).
  • Двоичные - для хранения бинарных данных (binary, varbinary, bit).
  • Большеобъемные - типы данных для хранения больших бинарных данных (text, ntext, image).
  • Специальные - указатели (cursor), 16-байтовое шестнадцатиричное число, которое используется для GUID (uniqueidentifier), штамп изменения строки (timestamp), версия строки (rowversion), таблицы (table).

Примечание. Для использования русских символов (не ASCII кодировки) испольюзуются типы данных с приставкой "n" (nchar, nvarchar, ntext), которые кодируют символы двумя байтами. Иначе говоря, для работы с Unicode используются типы данных с "n".

Примечание. Для данных переменной длины используются типы данных с приставкой "var". Типы данных без приставки "var" имеют фиксированную длину области памяти, неиспользованная часть которой заполняется пробелами или нулями.

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

Идентификаторы - это специальные символы, которые используются с переменными для идентифицирования их типа или для группировки слов в переменную. Типы идентификаторов:

  • @ - идентификатор локальной переменной (пользовательской).
  • @@ - идентификатор глобальной переменной (встроенной).
  • # - идентификатор локальной таблицы или процедуры.
  • ## - идентификатор глобальной таблицы или процедуры.
  • - идентификатор группировки слов в переменную.

Переменные

Переменные используются в сценариях и для хранения временных данных. Чтобы работать с переменной, ее нужно объявить, притом объявление должно быть осуществлено в той транзакции, в которой выполняется команда, использующая эту переменную. Иначе говоря, после завершения транзакции, то есть после команды GO, переменная уничтожается.

Объявление переменной выполняется командой DECLARE, задание значения переменной осуществляется либо командой SET, либо SELECT:

USE TestDatabase -- Объявление переменных DECLARE @EmpID int, @EmpName varchar(40) -- Задание значения переменной @EmpID SET @EmpID = 1 -- Задание значения переменной @EmpName SELECT @EmpName = UserName FROM Users WHERE UserID = @EmpID -- Вывод переменной @EmpName в результат запроса SELECT @EmpName AS GO

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

Операторы

Операторы - это специальные команды, предназначенные для выполнения простых операций над переменными:

  • Арифметические операторы: "*" - умножить, "/" - делить, "%" - модуль от деления, "+" - сложить, "-" - вычесть, "()" - скобки.
  • Операторы сравнения: "=" - равно, ">" - больше, "<" - меньше, ">=" - больше или равно, "<=" меньше или равно, "<>" - не равно.
  • Операторы соединения: "+" - соединение строк.
  • Логические операторы: "AND" - и, "OR" - или, "NOT" - не.

Cистемные функции

Спецификация Transact-SQl значительно расширяет стандартные возможности SQL благодаря встроенным функциям:

  • Агрегативные функции- функции, которые работают с коллекциями значений и выдают одно значение. Типичные представители: AVG - среднее значение колонки, SUM - сумма колонки, MAX - максимальное значение колонки, COUNT - количество элементов колонки.
  • Скалярные функции- это функции, которые возвращают одно значение, работая со скалярными данными или вообще без входных данных. Типичные представители: DATEDIFF - разница между датами, ABS - модуль числа, DB_NAME - имя базы данных, USER_NAME - имя текущего пользователя, LEFT - часть строки слева.
  • Функции-указатели- функции, которые используются как ссылки на другие данные. Типичные представители: OPENXML - указатель на источник данных в виде XML-структуры, OPENQUERY - указатель на источник данных в виде другого запроса.

Примечание. Полный список функций можно найти в справке к SQL серверу.

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

USE TestDatabase -- Использование агрегативной функции для подсчета средней зарплаты SELECT AVG(BaseSalary) AS FROM Positions GO -- Использование скалярной функции для получения имени базы данных SELECT DB_NAME() AS GO -- Использование скалярной функции для получения имени текущего пользователя DECLARE @MyUser char(30) SET @MyUser = USER_NAME() SELECT "The current user""s database username is: "+ @MyUser GO -- Использование функции-указателя для получения данных с другого сервера SELECT * FROM OPENQUERY(OracleSvr, "SELECT name, id FROM owner.titles") GO

Выражения

Выражение - это комбинация символов и операторов, которая получает на вход скалярную величину, а на выходе дает другую величину или исполняет какое-то действие. В Transact-SQL выражения делятся на 3 типа: DDL, DCL и DML.

  • DDL (Data Definition Language)- используются для создания объектов в базе данных. Основные представители данного класса: CREATE - создание объектов, ALTER - изменение объектов, DROP - удаление объектов.
  • DCL (Data Control Language)- предназначены для назначения прав на объекты базы данных. Основные представители данного класса: GRANT - разрешение на объект, DENY - запрет на объект, REVOKE - отмена разрешений и запретов на объект.
  • DML (Data Manipulation Language)- используются для запросов и изменения данных. Основные представители данного класса: SELECT - выборка данных, INSERT - вставка данных, UPDATE - изменение данных, DELETE - удаление данных.

USE TestDatabase -- Использование DDL CREATE TABLE TempUsers (UserID int, UserName nvarchar(40), DepartmentID int) GO -- Использование DCL GRANT SELECT ON Users TO public GO -- Использование DML SELECT UserID, UserName + " " + UserSurname AS FROM Users GO -- Использование DDL DROP TABLE TempUsers GO

Управление выполнением сценария

В Transact-SQL существуют специальные команды, которые позволяют управлять потоком выполнения сценария, прерывая его или направляя в нужную логику.

  • Блок группировки - структура, объединяющая список выражений в один логический блок (BEGIN … END).
  • Блок условия - структура, проверяющая выполнения определенного условия (IF … ELSE).
  • Блок цикла - структура, организующая повторение выполнения логического блока (WHILE … BREAK … CONTINUE).
  • Переход - команда, выполняющая переход потока выполнения сценария на указанную метку (GOTO).
  • Задержка - команда, задерживающая выполнение сценария (WAITFOR)
  • Вызов ошибки - команда, генерирующая ошибку выполнения сценария (RAISERROR)

Динамическое конструирование выражений

Итак, поняв основы Transact-SQL и попрактиковавшись на простых примерах, можно перейти к более сложным структурам. Обычно базы данных создаются и заполняются с помощью сценариев (скриптов) - хотя визуальный редактор прост в обращении, но им никогда быстро и без недочетов не создашь большую базу данных и не заполнишь ее данными. Если вспомнить начало статьи, то опытная база данных как раз создавалась и заполнялась с помощью сценария. Сценарий - это одно или более выражений, объединенных в логический блок, которые автоматизируют работу администратора.

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

USE master -- Задание динамических данных DECLARE @dbname varchar(30), @tablename varchar(30), @column varchar(30) SET @dbname = "TestDatabase" SET @tablename = "Positions" SET @column = "BaseSalary" -- Использование динамических данных EXECUTE ("USE " + @dbname + " SELECT AVG(" + @column + ") AS FROM " + @tablename) GO

Выборка данных

В языках SQL выборка данных из таблиц осуществляется с помощью команды SELECT:

SELECT <названия колонок или *> FROM <название таблицы>

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

Для того, чтобы изменить имена объектов в командах к SQL-серверу, используется команда AS. Использование этой команды помогает сокращать длину строки запроса, а так же получать результат в более удобочитаемом виде.

Выбрать все записи из таблицы Local Customers SELECT * FROM -- Выбрать уникальные записи колонки UserName из таблицы Users SELECT DISTINCT UserName FROM Users

UserName
Andrey
Ivan
Nikolay
Petr
Sergey
Viktor

Фильтрация данных осуществляется с помощью команды WHERE, в которой используются следующие операторы и команды сравнения: =, <, >, <=, >=, <>, LIKE, NOT LIKE, AND, OR, NOT, BETWEEN, NOT BETWEEN, IN, NOT IN, IS NULL, IS NOT NULL. В общем виде команда SELECT с фильтром выглядит так:

SELECT <названия колонок или *> FROM <название таблицы> WHERE <условие>

В строке сравнения разрешается использовать подстановочные символы:

  • % - любое количество символов;
  • _ - один символ;
  • - любой символ, указанный в скобках;
  • [^] - любой символ, не указанный в скобках.
-- Выбрать все записи из таблицы Users, где DepartmentID = 1 SELECT * FROM Users WHERE DepartmentID = 1
UserID UserName UserSurname DepartmentID PositionID
1 Ivan Petrov 1 1
2 Ivan Sidorov 1 2
3 Petr Ivanov 1 2
4 Nikolay Petrov 1 3
-- Выбрать все записи из таблицы Users, у кого в имени есть буква A SELECT * FROM Users WHERE UserName LIKE "%a%"
UserID UserName UserSurname DepartmentID PositionID
1 Ivan Petrov 1 1
2 Ivan Sidorov 1 2
4 Nikolay Petrov 1 3
5 Nikolay Ivanov 2 1
7 Andrey Bukin 2 2
-- Выбрать все записи из таблицы Users, у кого в имени вторая буква не V SELECT * FROM Users WHERE UserName LIKE "_[^v]%"

Фильтрация позволяет использовать подзапросы, то есть конструировать запрос из нескольких подзапросов:

Выбрать записи колонки PositionID из таблицы Positions, где BaseSalary < 600 SELECT PositionID FROM Positions WHERE BaseSalary < 600 -- Выбрать все записи из таблицы Users, у кого имя Ivan или Andrey SELECT * FROM Users WHERE UserName IN ("Ivan", "Andrey")

Для сортировки данных в выборке используется командаORDER BY, но следует учесть, что эта команда не сортирует данные типа text, ntext и image. По умолчанию сортировка производится по возрастанию, поэтому параметр ASC в этом случае можно не указывать:

SELECT <названия колонок или *> FROM <название таблицы> WHERE <условие> ORDER BY <названия колонок>

Для того, чтобы ограничить количество строк в результате запроса, используется командаTOP:

SELECT TOP [количество строк] <названия колонок или *> FROM <название таблицы> WHERE <условие> ORDER BY <названия колонок>

Внутри запроса можно проводить вычисления над полученными данными. Для этого используюся функции агрегирования:

  • AVG(колонка) - среднее значение колонки;
  • COUNT(колонка) - количество не NULL элементов колонки;
  • COUNT(*) - количество элементов запроса;
  • MAX(колонка) - максимальное значение в колонке;
  • MIN(колонка) - минимальное значение в колонке;
  • SUM(колонка) - сумма значений в колонке.

Примеры использования команд ORDER, TOP и функций агрегирования:

Выбрать 3 первые уникальные записи колонки UserName из таблицы Users, -- отсортированных по возрастанию UserName SELECT DISTINCT TOP 3 UserName FROM Users ORDER BY UserName -- Найти величину максимального оклада в организации SELECT MAX(BaseSalary) FROM Positions -- Найти сотрудников, у кого максимальный оклад в организации SELECT * FROM Users WHERE PositionID IN (SELECT PositionID FROM Positions WHERE BaseSalary IN (SELECT MAX(BaseSalary) FROM Positions))

Группировка данных

SQL позволяет производить группировку данных по определенным полям таблицы. Чтобы сгруппировать данные по какому-нибудь параметру, в SQL-запросе необходимо написать команду GROUP BY, в которой указать имя колонки, по которой производится группировка. Колонки, упомянутые в команде GROUP BY, должны присутствовать в команде SELECT, а так же команда SELECT должна содержать функцию агрегирования, которая будет применена к сгруппированным данным.

Найти количество работников в каждом отделе (сгруппировать работников по -- идентификатору отделов и сосчитать количество записей в каждой группе) SELECT DepartmentID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID

DepartmentID Number of users
1 4
2 3
4 1

Чтобы отфильтровать строки в запросе с группировкой применяется специальная команда HAVING, в которой указывается условие фильтрации. Колонки, по которым производится фильтрация, должны присутствовать в команде GROUP BY. Команда HAVING может использоваться и без GROUP BY, в этом случае она работает аналогично команде WHERE, но она разрешает применять в условиях фильтрации только функции агрегирования.

Найти количество работников в первом отделе (сгруппировать работников по -- идентификатору отделов, сосчитать количество записей в каждой группе и -- вывести в результат только отдел с идентификатором равным 1) SELECT DepartmentID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID HAVING DepartmentID = 1 -- Найти количество работников с определенной должностью в каждом отделе -- (сгруппировать работников по идентификатору должностей и отделов и -- сосчитать количество записей в каждой группе), а также сосчитать -- количество работников в каждом отделе и общее количество работников SELECT DepartmentID, PositionID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID, PositionID WITH ROLLUP

DepartmentID PositionID Number of users
1 1 1
1 2 2
1 3 1
1 NULL 4
2 1 1
2 2 1
2 3 1
2 NULL 3
4 1 1
4 NULL 1
NULL NULL 8

Команда группировки также может дополняться оператором WITH CUBE, который дополняет формирует всевозможные комбинации из группируемых колонок: если есть N колонок, то получится 2^N комбинаций.

Найти количество работников с определенной должностью в каждом отделе -- (сгруппировать работников по идентификатору должностей и отделов и -- сосчитать количество записей в каждой группе), а также сосчитать -- количество работников по каждой должности, по каждому отделу и -- общее количество работников SELECT DepartmentID, PositionID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID, PositionID WITH CUBE

DepartmentID PositionID Number of users
1 1 1
1 2 2
1 3 1
1 NULL 4
2 1 1
2 2 1
2 3 1
2 NULL 3
4 1 1
4 NULL 1
NULL NULL 8
NULL 1 3
NULL 2 3
NULL 3 2

Функция агрегирования GROUPING позволяет определить, была ли запись добавлена командами ROLLUP и CUBE, или это запись получена из источника данных.

Найти количество работников в каждом отделе (сгруппировать работников по -- идентификатору отделов и сосчитать количество записей в каждой группе) -- а так же пометить дополнительные строки, несуществующие в источнике данных SELECT DepartmentID, COUNT(UserID) AS "Number of users", GROUPING(DepartmentID) AS "Added row" FROM Users GROUP BY DepartmentID WITH ROLLUP

DepartmentID Number of users Added row
1 4 0
2 3 0
4 1 0
NULL 8 1

Еще одна команда группировки COMPUTE позволяет группировать данные и выводить по ним отчет в разные таблицы. То есть команда GROUP BY с операторами ROLLUP и CUBE группирует данные и дописывает в таблицу дополнительны строки с отчетом, а команда COMPUTE группирует данные, разрывая исходную таблицу на несколько подтаблиц, а также формирует подтаблицы с отчетами. Команда COMPUTE может использоваться в двух режимах:

  • как простая функция агрегирования, выводящая результат в отдельную таблицу;
  • с параметром BY как команда группировки, разрезающая таблицу на несколько подтаблиц

Команда COMPUTE с параметром BY может использоваться только совместно с командой ORDER BY, причем столбцы сортировки должны совпадать со столбцами группировки.

Вывести таблицу пользователей компании, а также посчитать их количество SELECT * FROM Users COMPUTE COUNT(UserID)

UserID UserName UserSurname DepartmentID PositionID
1 Ivan Petrov 1 1
2 Ivan Sidorov 1 2
3 Petr Ivanov 1 2
4 Nikolay Petrov 1 3
5 Nikolay Ivanov 2 1
6 Sergey Sidorov 2 3
7 Andrey Bukin 2 2
8 Viktor Rybakov 4 1
cnt
8
-- Найти количество работников в каждом отделе (сгруппировать работников по -- идентификатору отделов и сосчитать количество записей в каждой группе) SELECT * FROM Users ORDER BY DepartmentID COMPUTE COUNT(UserID) BY DepartmentID
UserID UserName UserSurname DepartmentID PositionID
1 Ivan Petrov 1 1
2 Ivan Sidorov 1 2
3 Petr Ivanov 1 2
4 Nikolay Petrov 1 3
cnt
4
UserID UserName UserSurname DepartmentID PositionID
5 Nikolay Ivanov 2 1
6 Sergey Sidorov 2 3
7 Andrey Bukin 2 2
cnt
3
UserID UserName UserSurname DepartmentID PositionID
8 Viktor Rybakov 4 1
cnt
1

Соединение таблиц

Самые важные и нужные запросы в SQL - это с запросы с соединением таблиц, когда выборка осуществляется сразу из нескольких источников. Такие запросы более сложны в написании, но и более удобны в обработке, так как часто выдают в программу уже готовый результат, который остается только вывести на экран.

Соединять таблицы в SQL можно двумя способами: вертикально и горизонтально.

Вертикальное соединение осуществляется командой UNION, которая в конец первой таблицы допишет вторую таблицую. При таком соединении количество колонок соединяемых таблиц должно быть одинаковым, а сами колонки должны иметь одинаковые названия и типы данных. При соединении одинаковые строки, встречающиеся в обоих таблицах, будут удалены, если в команде не указан параметр ALL.

Найти всех пользователей с именем Ivan и соединить результат с -- результатом от запроса "Найти всех пользователей с фамилией Petrov" -- дублирующие записи исключить SELECT * FROM Users WHERE UserName = "Ivan" UNION SELECT * FROM Users WHERE UserSurname = "Petrov"

UserID UserName UserSurname DepartmentID PositionID
1 Ivan Petrov 1 1
2 Ivan Sidorov 1 2
4 Nikolay Petrov 1 3
-- Найти всех пользователей с именем Ivan и соединить результат с -- результатом от запроса "Найти всех пользователей с фамилией Petrov" -- дублирующие записи сохранить SELECT * FROM Users WHERE UserName = "Ivan" UNION ALL SELECT * FROM Users WHERE UserSurname = "Petrov"
UserID UserName UserSurname DepartmentID PositionID
1 Ivan Petrov 1 1
2 Ivan Sidorov 1 2
1 Ivan Petrov 1 1
4 Nikolay Petrov 1 3

Горизонтальное соединение производится путем сцепки нескольких таблиц по ключевым колонкам. Самое простое горизонтальное соединение выполняется с помощью команды INNER JOIN, которая сцепляет таблицы, выбирая строки по ключевому полю, которое встречается в обоих таблицах.

SELECT <названия колонок или *> FROM <таблица_1> INNER JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Чтобы выполнить сцепление по всем полям левой таблицы, независимо, есть ли такие записи в правой таблице, необходимо использовать команду LEFT JOIN. Эта команда соединяет таблицы, выбирая все строки из левой таблицы, а отсутствующие данные правой таблицы заполняются значением NULL.

SELECT <названия колонок или *> FROM <таблица_1> LEFT JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Команда RIGHT JOIN аналогична предыдущей, разница заключается лишь в том, что она соединяет таблицы, выбирая все строки из правой таблицы, а отсутствующие данные левой таблицы заполняются значением NULL.

SELECT <названия колонок или *> FROM <таблица_1> RIGHT JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

Команда FULL JOIN объединяет в себе левое и правое сцепление, то есть она соединяет таблицы, выбирая строки из обоих таблиц, а отсутствующие данные заполняются значением NULL.

SELECT <названия колонок или *> FROM <таблица_1> FULL JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

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

SELECT <названия колонок или *> FROM <таблица_1> CROSS JOIN таблица_2

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

SELECT * FROM Users INNER JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID UserName UserSurname DepartmentID PositionID DepartmentID DepartmentName
1 Ivan Petrov 1 1 1 Production
2 Ivan Sidorov 1 2 1 Production
3 Petr Ivanov 1 2 1 Production
4 Nikolay Petrov 1 3 1 Production
5 Nikolay Ivanov 2 1 2 Distribution
6 Sergey Sidorov 2 3 2 Distribution
7 Andrey Bukin 2 2 2 Distribution
SELECT * FROM Users LEFT JOIN Departments ON Users.DepartmentID = Departments.DepartmentID
UserID UserName UserSurname DepartmentID PositionID DepartmentID DepartmentName
1 Ivan Petrov 1 1 1 Production
2 Ivan Sidorov 1 2 1 Production
3 Petr Ivanov 1 2 1 Production
4 Nikolay Petrov 1 3 1 Production
5 Nikolay Ivanov 2 1 2 Distribution
6 Sergey Sidorov 2 3 2 Distribution
7 Andrey Bukin 2 2 2 Distribution
8 Viktor Rybakov 4 1 NULL NULL
SELECT * FROM Users RIGHT JOIN Departments ON Users.DepartmentID = Departments.DepartmentID
UserID UserName UserSurname DepartmentID PositionID DepartmentID DepartmentName
1 Ivan Petrov 1 1 1 Production
2 Ivan Sidorov 1 2 1 Production
3 Petr Ivanov 1 2 1 Production
4 Nikolay Petrov 1 3 1 Production
5 Nikolay Ivanov 2 1 2 Distribution
6 Sergey Sidorov 2 3 2 Distribution
7 Andrey Bukin 2 2 2 Distribution
NULL NULL NULL NULL NULL 3 Purchasing
SELECT * FROM Users FULL JOIN Departments ON Users.DepartmentID = Departments.DepartmentID
UserID UserName UserSurname DepartmentID PositionID DepartmentID DepartmentName
1 Ivan Petrov 1 1 1 Production
2 Ivan Sidorov 1 2 1 Production
3 Petr Ivanov 1 2 1 Production
4 Nikolay Petrov 1 3 1 Production
5 Nikolay Ivanov 2 1 2 Distribution
6 Sergey Sidorov 2 3 2 Distribution
7 Andrey Bukin 2 2 2 Distribution
NULL NULL NULL NULL NULL 3 Purchasing
8 Viktor Rybakov 4 1 NULL NULL
SELECT * FROM Users CROSS JOIN Departments
UserID UserName UserSurname DepartmentID PositionID DepartmentID DepartmentName
1 Ivan Petrov 1 1 1 Production
2 Ivan Sidorov 1 2 1 Production
3 Petr Ivanov 1 2 1 Production
4 Nikolay Petrov 1 3 1 Production
5 Nikolay Ivanov 2 1 1 Production
6 Sergey Sidorov 2 3 1 Production
7 Andrey Bukin 2 2 1 Production
8 Viktor Rybakov 4 1 1 Production
1 Ivan Petrov 1 1 2 Distribution
2 Ivan Sidorov 1 2 2 Distribution
3 Petr Ivanov 1 2 2 Distribution
4 Nikolay Petrov 1 3 2 Distribution
5 Nikolay Ivanov 2 1 2 Distribution
6 Sergey Sidorov 2 3 2 Distribution
7 Andrey Bukin 2 2 2 Distribution
8 Viktor Rybakov 4 1 2 Distribution
1 Ivan Petrov 1 1 3 Purchasing
2 Ivan Sidorov 1 2 3 Purchasing
3 Petr Ivanov 1 2 3 Purchasing
4 Nikolay Petrov 1 3 3 Purchasing
5 Nikolay Ivanov 2 1 3 Purchasing
6 Sergey Sidorov 2 3 3 Purchasing
7 Andrey Bukin 2 2 3 Purchasing
8 Viktor Rybakov 4 1 3 Purchasing
SELECT dpt.DepartmentName AS "Department", usr.UserName + " " + usr.UserSurname AS "User name", pos.PositionName AS "Position" FROM Users AS usr LEFT JOIN Departments AS dpt ON usr.DepartmentID = dpt.DepartmentID LEFT JOIN Positions AS pos ON usr.PositionID = pos.PositionID ORDER BY dpt.DepartmentID, pos.PositionID
Department User name Position
NULL Viktor Rybakov Manager
Production Ivan Petrov Manager
Production Ivan Sidorov Senior analyst
Production Petr Ivanov Senior analyst
Production Nikolay Petrov Analyst
Distribution Nikolay Ivanov Manager
Distribution Andrey Bukin Senior analyst
Distribution Sergey Sidorov Analyst

Изменение данных

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

За транзакции в Transact-SQL отвечает структура BEGIN TRANSACTION ... COMMIТ TRANSACTION. Эту структуру использовать необязательно, но тогда все команды сценария являются необратимыми, то есть нельзя сделать "откат" к предыдущему состоянию. Полная структура блока транзакций:

BEGIN TRANSACTION [имя транзакции] [операции] COMMIТ TRANSACTION [имя транзакции] или ROLLBACK TRANSACTION [имя транзакции]

Ниже приведен пример использования этого блока:

Утановить всем сотрудникам новый оклад BEGIN TRANSACTION TR1 UPDATE Positions SET BaseSalary = 2500000000000000 IF @@ERROR <> 0 BEGIN RAISERROR("Error, transaction not completed!",16,-1) ROLLBACK TRANSACTION TR1 END ELSE COMMIT TRANSACTION TR1

Для вставки данных в таблицы SQL-сервера используется команда INSERT INTO:

INSERT INTO [название таблицы] (колонки) VALUES ([значения колонок])

Вторая часть комнады является необязательной для MS SQL Server 2003, но MS JET SQL без этого слова будет выдавать ошибку синтаксиса. Вставка обычно производиться целострочно, то есть в комнаде указываются все колонки таблицы и значения, которые нужно в них занести. Если же колонка имеет значение по умолчанию или разрешает пустое значения, то в команде вставки эту колонку можно не указывать. Команда INSERT INTO также разрешает указывать вносимые данные не по порядку следования колонок, но в этом случае нужно обозначить используемый порядок колонок.

В таблицу Users вставить строку с данными UserID = 9, UserName = "Nikolay", -- UserSurname = "Gryzlov", DepartmentID = 4, PositionID = 2. INSERT INTO Users VALUES (9, "Nikolay", "Gryzlov", 4, 2) -- В таблицу Users вставить строку с данными UserID = 10, UserName = "Nikolay", -- UserSurname = "Kozin", DepartmentID - значение по умолчанию, PositionID - не указано. INSERT Users VALUES (10, "Nikolay", "Kozin", DEFAULT, NULL) -- В таблицу Users вставить строку с данными UserName = "Angrey", UserSurname = "Medvedev", -- UserID = 11, остальные значения по умолчанию INSERT INTO Users (UserName, UserSurname, UserID) VALUES ("Angrey", "Medvedev", 11)

Для того, чтобы изменить значение ячейки таблицы, используется команда UPDATE:

UPDATE [название таблицы] SET [имя колонки]=[значение колонок] WHERE [условие]

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

Установить всем должностям зарплату в 2000 единиц. UPDATE Positions SET BaseSalary = 2000 -- Должностям с идентификатором 1 установить зарплату в 2500 единиц. UPDATE Positions SET BaseSalary = 2500 WHERE PositionID = 1 -- Должностям с идентификатором 2 уменьшить зарплату на 30%. UPDATE Positions SET BaseSalary = BaseSalary * 0.7 WHERE PositionID = 2 -- Установить всем должностям зарплату, равную (30 000 разделить на количество -- сотрудников в организации) UPDATE Positions SET BaseSalary = 30000 / (SELECT COUNT(UserID) FROM Users)

Удаление данных производится командой DELETE:

DELETE FROM [название таблицы] WHERE [условие]

Удаление данных обычно производится по какому-то критерию. Так как удаление данных - это достаточно опасная операция, то перед выполнением такой команды лучше всего произвести тестовую выборку командой SELECT, которая выведет в результат те данные, которые будут стерты. Если это то, что требуется, тогда можно смело заменять SELECT на DELETE и выполнять удаление данных.

Удалить пользователя с идентификатором 10 -- В режиме отладки рекомедуется использовать команду SELECT, -- чтобы знать, какие данные будут стерты: -- SELECT UserID FROM Users WHERE UserID = 10 DELETE FROM Users WHERE UserID = 10 -- Удалить всех польователей отдела Production DELETE Users FROM Users INNER JOIN Departments ON Users.DepartmentID = Departments.DepartmentID WHERE Departments.DepartmentName = "Production" -- Удалить всех пользователей DELETE FROM Users

Примечание! В примере для фильтрации данных применено сцепление таблиц. Хотя в команде перечисляются несколько таблиц, удаление данных будет произведено только из той таблицы, которая указана после слова DELETE.

Более быстрая команда для очистки таблицы - это TRUNCATE TABLE.

TRUNCATE TABLE [название таблицы]

Пример удаления всех данных:

Очистить таблицу Users TRUNCATE TABLE Users

Transact-SQL позволяет использовать временные таблицы, то есть таблицы, которые создаются в памяти сервера на время работы пользователя с базой данных. Временные таблицы могут иметь любое имя, но начинаться обязаны с символа #.

Создать временную таблицу #TempTable, в которую скопировать содержание -- колонки UserName таблицы Users SELECT UserName INTO #TempTable FROM Users -- Выбрать все записи временной таблицы #TempTable SELECT * FROM #TempTable

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

Хранимые процедуры и функции представляют собой набор SQL-операторов, которые можно сохранять на сервере. Если сценарий сохранен на сервере, то клиентам не придется повторно задавать одни и те же отдельные операторы, вместо этого они смогут обращаться к хранимой процедуре. Ситуации, когда хранимые процедуры особенно полезны:

  • Многочисленные клиентские приложения написаны на разных языках или работают на различных платформах, но должны выполнять одинаковые операции с базами данных.
  • Безопасность играет первостепенную роль. Хранимые процедуры используются для всех стандартных операций, что обеспечивает совместимость и безопасность среды, а процедуры гарантируют надлежащую регистрацию каждой операции. При таком типе установки приложения и пользователи не получают непосредственный доступ к таблицам базы данных и могут выполнять только конкретные хранимые процедуры.
  • Необходимо снизить сетевой трафик между клиентом и сервером. Объем пересылаемой информации между сервером и клиентом существенно снижается, но увеличивается нагрузка на систему сервера баз данных, так как в этом случае на стороне сервера выполняется большая часть работы по обработке данных.

Пример создания хранимой процедуры и хранимой функции:

Создание функции обновления зарплат CREATE PROCEDURE usp_UpdateSalary AS UPDATE Positions SET BaseSalary = 2000 GO -- Создание функции получения имени пользователя CREATE FUNCTION usf_GetName (@UserID int) RETURNS varchar(255) BEGIN IF @UserID IS NULL SET @UserID = 1 RETURN (SELECT UserName + " " + UserSurname FROM Users WHERE UserID = @UserID) END GO -- Обновление зарплат EXEC TestDatabase.dbo.usp_UpdateSalary -- Получение имени пользователя с идентификатором 2 SELECT TestDatabase.dbo.usf_GetName(2)

Итак, хранимые процедуры и функции дают следующие преимущества:

  • производительность;
  • общая логика для всез запросов;
  • уменьшение трафика;
  • безопасность - доступ пользователю дается не к таблице, а к процедуре;

Производительность

Для увеличения производительности, то есть для быстрого выполнения запросов, следует помнить некоторые правила составления строк запросов:

  • Избегать NOT - команды отрицания выполняются в несколько этапов, что увеличивает нагрузку на сервер.
  • Избегать LIKE - этот оператор сравнения применяет более мягкие шаблоны сравнения, чем оператор =, что увеличивает необходимое число этапов фильтрации.
  • Применять точные шаблоны поиска - применение подстановочных символов увеличивает время выполнения запроса, так как для проверки всех вариантов подстановки требуется дополнительные ресурсы сервера.
  • Избегать ORDER - команда сортировки требует упорядочивания строк таблицы вывода, что задерживает получение результата.

Виталий Бочкарев

Transact-SQL (так же называется T-SQL ) это база данных (database ) процедурный язык программирования принадлежащее монопольно Microsoft и используется в SQL Server .

Процедурный язык был создан для расширения возможностей SQL с возможностью хорошо интегрировать с SQL . Добавлены некоторые функции, как локальные переменные и обработка строк/данных. Эти функции делают язык Turing-complete (**).

Они так же используются для записи процедур хранения: Фрагмент кода находящийся на сервере управляет сложными бизнес правилами, которые сложно или невозможно управлять операциями на основе набора (pure set-based operations).

A Turing Complete system means a system in which a program can be written that will find an answer (although with no guarantees regarding runtime or memory).

2- Обзор Transact-SQL

T-SQL организован блоками команд, один блок команд можно вложить в другой блок команд, блок команд начинающийся с BEGIN и заканчивающийся на END , в блоке имеется много конманд, и команды отделены друг от друга точкой запятой(;).

Структура блока:

BEGIN -- Declare variables -- T-SQL Statements END;

3- Начать с SQL Server Management Studio

В данной статье я покажу вам программирование SQL Server , на визуальном инструменте SQL Server Management Studio .

Это иллюстрация SQL Server Management Studio при открытии. Есть некоторые примеры database , когда вы полностью устанавливаете SQLServer .

Или вы можете создать learningsql , маленькую базу данных использующуюся в некоторых статьях по рукодству пользования SQLServer на сайт .

Нажать на правую мышь на database , выбрать "New Query" чтобы открыть окно для этого database .

Вы готовы программировать database с SQL Server .

Ниже показывается легкий блок команд, посчитать сумму 2 чисел:

Begin -- Declaring a variable Declare @v_Result Int; -- Declaring a variable with a value of 50 Declare @v_a Int = 50; -- Declaring a variable with a value of 100 Declare @v_b Int = 100; -- Print out Console (For developer). -- Using Cast to convert Int to String -- Using + operator to concatenate 2 string Print "v_a= " + Cast(@v_a as varchar(15)); -- Print out Console Print "v_b= " + Cast(@v_b as varchar(15)); -- Sum Set @v_Result = @v_a + @v_b; -- Print out Console Print "v_Result= " + Cast(@v_Result as varchar(15)); End;

Нажмите на знак чтобы запустить блок команд, и смотрите результат на SQL Server Management Studio :

4- Базовые команды Transact-SQL

4.1- Команда веток If-elsif-else

Синтаксис:

IF THEN Job 1; END IF;

BEGIN -- Declare a variable DECLARE @v_Option integer; DECLARE @v_Action varchar(30); SET @v_Option = 2; IF @v_Option = 1 SET @v_Action = "Run"; ELSE IF @v_Option = 2 BEGIN PRINT "In block else if @v_Option = 2"; SET @v_Action = "Backup"; END; ELSE IF @v_Option = 3 SET @v_Action = "Stop"; ELSE SET @v_Action = "Invalid"; -- Logging PRINT "@v_Action= " + @v_Action; END;

Результаты запуска примера:

4.2- Цикл WHILE

В цикле WHILE вы можете использовать BREAK чтобы выйти из цикла.
Используйте команду CONTINUE чтобы пропустить команды в блоке WHILE и ниже, чтобы начать новый цикл.

< @y WHILE (@x < @y) BEGIN SET @step = @step + 1; -- Every time loop execute, x increases by 1. SET @x = @x + 1; -- Every time loop execute, x decreases by 2. SET @y = @y - 2; PRINT "Step =" + CAST(@step AS varchar(10)); PRINT "@x =" + CAST(@x AS varchar(10)) + " / @y = " + CAST(@y AS varchar(10)); END; -- Write log PRINT "x,y = " + CAST(@x AS varchar(10)) + ", " + CAST(@y AS varchar(10)); END;

Результаты запуска примера:

BEGIN -- Declaring 2 variables x and y DECLARE @x integer = 0; DECLARE @y integer = 10; -- Step DECLARE @step integer = 0; -- While @x < @y WHILE (@x < @y) BEGIN SET @step = @step + 1; -- Every time the loop execute, x increases by 1 SET @x = @x + 1; -- Every time the loop execute, y decreases by 1 SET @y = @y - 2; PRINT "Step =" + CAST(@step AS varchar(10)); PRINT "@x =" + CAST(@x AS varchar(10)) + " / @y = " + CAST(@y AS varchar(10)); -- If @x > 2 then exit the loop -- (Although conditions in the WHILE is still true). IF @x > 2 BREAK; END; -- Write log PRINT "x,y = " + CAST(@x AS varchar(10)) + ", " + CAST(@y AS varchar(10)); END;

Результаты запуска примера:

Команда CONTINUE позволяет пропустить команды находящиеся ниже (в цикле), чтобы начать новый цикл.

BEGIN -- Declaring 2 variables x and y. DECLARE @x integer = 0; DECLARE @y integer = 10; -- Step DECLARE @step integer = 0; -- While @x < @y WHILE (@x < @y) BEGIN SET @step = @step + 1; -- Every time the loop execute, x increases by 1 SET @x = @x + 1; -- Every time the loop execute, x decreases by 2 SET @y = @y - 2; -- If @x < 3 , then skip the statements below -- And continue new step IF @x < 3 CONTINUE; -- If @x < 3 the statements below "CONTINUE" will not be run. PRINT "Step =" + CAST(@step AS varchar(10)); PRINT "@x =" + CAST(@x AS varchar(10)) + " / @y = " + CAST(@y AS varchar(10)); END; -- Write log PRINT "x,y = " + CAST(@x AS varchar(10)) + ", " + CAST(@y AS varchar(10)); END;

5- Прикрепить данные запроса в переменную

Переменным могут присвоить значение из запроса. Смотрите иллюстрированный пример ниже:

Assign_Value_Example

BEGIN -- Declaring a variable @v_Emp_ID DECLARE @v_Emp_ID integer = 1; DECLARE @v_First_Name varchar(30); DECLARE @v_Last_Name varchar(30); DECLARE @v_Dept_ID integer; -- Assgin values to variables SELECT @v_First_Name = emp.First_Name, @v_Last_Name = emp.Last_Name, @v_Dept_Id = emp.Dept_Id FROM Employee Emp WHERE Emp.Emp_ID = @v_Emp_Id; -- Print out values PRINT "@v_First_Name = " + @v_First_Name; PRINT "@v_Last_Name = " + @v_Last_Name; PRINT "@v_Dept_Id = " + CAST(@v_Dept_ID AS varchar(15)); END;

Результаты запуска примера:

6- Особенные виды данных в T-SQL

6.1- Виды данных TABLE (Неявный вид)

T-SQL позволяет вам объявит переменные с видом данных TABLE .

Синтаксис:

Define a variable of type TABLE. -- NOTE: The constraints can also participate in declaration (See example). Declare @v_variable_name TABLE (Column1 DataType1, Column2 DataType2);

Declare a variable of type TABLE. Declare @v_Table TABLE (First_Name Varchar(30), Last_Name Varchar(30), Dept_ID Integer, Salary Float); -- The constraints can also participate in declaration: Declare @v_table TABLE (Product_ID Integer IDENTITY(1,1) PRIMARY KEY, Product_Name DataType2 NOT NULL Default ("Unknown"), Price Money CHECK (Price < 10.0));

Пример: Вставить данные в переменные вида TABLE .

Вы так же можете обновить Update на переменных вида TABLE :

Delete на переменных вида TABLE :

Query данных на переменных вида TABLE :

BEGIN DECLARE @v_Emp_ID integer = 1; -- Declare a variable of type TABLE. DECLARE @v_Table TABLE (First_Name varchar(30), Last_Name varchar(30), Dept_Id integer, Salary float DEFAULT 1000); -- Using INSERT INTO statement to insert data into @v_Table. INSERT INTO @v_Table (First_name, Last_Name, Dept_ID) SELECT emp.First_Name, emp.Last_Name, emp.Dept_Id FROM Employee Emp WHERE Emp.Emp_ID < 4; -- Update @v_Table UPDATE @v_Table SET Salary = Salary + 100 WHERE First_name = "Susan"; -- Query @v_Table. SELECT * FROM @v_Table; END;

Результаты запуска примера:

6.2- Вид данных TABLE (Явный вид)

T-SQL позволяет вам объявить переменные вида TABLE косвенным образом. Название переменной начинается с # .

BEGIN -- Using SELECT INTO statement to insert data into #v_My_Table. SELECT emp.First_Name, emp.Last_Name, emp.Dept_Id, 1000 Salary INTO #v_My_Table FROM Employee Emp WHERE Emp.Emp_ID < 4; -- Update #v_My_Table UPDATE #v_My_Table SET Salary = Salary + 100 WHERE First_name = "Susan"; -- Query #v_My_Table. SELECT * FROM #v_My_Table; END;

Результаты запуска примера:

7- Курсор (Cursor)

7.1- Что такое курсор?

Cursor это структурированный вид переменной, который позволяет вам обрабатывать данные в нескольких строках. Количество линий зависит от команды запроса данных. В процессе обработки, вы манипулируете cursor через каждую строку данных. Эта строка данных определяется курсором. Передвигая курсор, вы можете иметь доступ ко всем строкам данных.

7.2- Объявить курсор

Синтаксис

ISO Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] [;] -- Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]

7.3- Пример с курсором

USE learningsql; BEGIN -- -- Declare a variable: DECLARE @v_Emp_ID integer; DECLARE @v_First_Name varchar(50); DECLARE @v_Last_Name varchar(50); DECLARE @v_Count integer; -- Declare a CURSOR. DECLARE My_Cursor CURSOR FOR SELECT Emp.EMP_ID, Emp.FIRST_NAME, Emp.LAST_NAME FROM Employee Emp WHERE Emp.EMP_ID < 3; -- Open Cursor OPEN My_Cursor; -- Move the cursor to the first record. -- And assign column values to variables. FETCH NEXT FROM My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name; -- The FETCH statement was successful. (@@FETCH_STATUS = 0) WHILE @@FETCH_STATUS = 0 BEGIN PRINT "First Name = "+ @v_First_Name+" / Last Name = "+ @v_Last_Name; -- Move to the next record. -- And assign column values to the variables FETCH NEXT FROM My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name; END -- Close Cursor. CLOSE My_Cursor; DEALLOCATE My_Cursor; END;

Результаты запуска примера:

7.4- Пример использования курсора (Объявление вида переменной)

USE learningsql; BEGIN -- -- Declare a variable: DECLARE @v_Emp_ID integer; DECLARE @v_First_Name varchar(50); DECLARE @v_Last_Name varchar(50); -- Declaring a cursor variable. DECLARE @My_Cursor CURSOR; -- Set Select statement for CURSOR variable. Set @My_Cursor = CURSOR FOR SELECT Emp.EMP_ID, Emp.FIRST_NAME, Emp.LAST_NAME FROM Employee Emp WHERE Emp.EMP_ID < 3; -- Open Cursor OPEN @My_Cursor; -- Move the cursor to the first line. -- And assign column values to the variables. FETCH NEXT FROM @My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name; -- The FETCH statement was successful. (@@FETCH_STATUS = 0) WHILE @@FETCH_STATUS = 0 BEGIN PRINT "First Name = "+ @v_First_Name+" / Last Name = "+ @v_Last_Name; -- Move to the next record. -- And assign column values to the variables. FETCH NEXT FROM @My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name; END -- Close Cursor. CLOSE @My_Cursor; DEALLOCATE @My_Cursor; END;

The results run the example:

8- Обработка исключения

При программировании T-SQL могут появиться некоторые ошибки в вашем коде, например ошибка при делении на 0. Или ошибка когда вы вставляете запись, но она дублирует значение с первичным ключом, ... Вам нужно исправить эти ситуации.

Смотрите простой пример, как исправить ошибку при делении на 0.

TryCatch_Example

USE learningsql; BEGIN -- -- Declare a variable: DECLARE @v_a float = 20; DECLARE @v_b float = 0; DECLARE @v_c float; DECLARE @v_Error_Number integer; -- Use BEGIN TRY .. END TRY to trap errors. -- If an error occurs in this block -- It will jump to block BEGIN CATCH .. END CATCH. BEGIN TRY --- PRINT "@v_a = " + CAST(@v_a AS varchar(15)); PRINT "@v_b = " + CAST(@v_b AS varchar(15)); -- Divide by 0 error, occurring here. SET @v_c = @v_a / @v_b; -- Below this line will not be running. -- Program jump to block BEGIN CATCH .. END CATCH PRINT "@v_c= " + CAST(@v_c AS varchar(15)); END TRY -- BEGIN CATCH .. END CATCH must be placed immediately behind BEGIN TRY .. END TRY. BEGIN CATCH -- Error Number. SET @v_Error_Number = ERROR_NUMBER(); -- Print out error number: PRINT "Error Number: " + CAST(@v_Error_Number AS varchar(15)); -- Error message: PRINT "Error Message: " + ERROR_MESSAGE(); -- The severity of the error: PRINT "Error Severity: " + CAST(ERROR_SEVERITY() AS varchar(15)); -- Error State: PRINT "Error State: " + CAST(ERROR_STATE() AS varchar(15)); -- Line Number: PRINT "Error Line: " + CAST(ERROR_LINE() AS varchar(15)); -- Name of procedure (or function, or trigger). PRINT "Error Procedure: " + ERROR_PROCEDURE(); END CATCH; END;

Pезультат

Информация ошибки:

Функция Описание
ERROR_NUMBER() Возвращает номер ошибки.
ERROR_MESSAGE() Возвращает полностью сообщение об ошибке. Сообщение включает значения предоставленные параметрам, как длина, название объекта, или время.
ERROR_SEVERITY() Возвращает степень серьезности ошибки.
ERROR_STATE() Возвращает статус ошибки.
ERROR_LINE() Возвращает номер строки кода совершившая ошибку
ERROR_PROCEDURE() Возвращает название stored procedure или trigger , где произошла ошибка.

9- Функция (Function)

Как procedure (процедура), function (функция) являются командами T-SQL , выполняющими определенную роль. В отличии от процедуры, функция возвращает значение сразу при вызове.
Функция так же может быть сохранена в database в виде Store procedure .

Синтаксис создания function (Функции).

Function_name: -- argument: -- mode: INPUT, OUTPUT, default INPUT -- datatype: CREATE FUNCTION ([ @argument1 datatype1 , @argument2 datatype2 , ... ]) RETURNS datatype AS BEGIN -- Declare variables -- Statements -- Return value END;

Function with parameters CREATE FUNCTION Sum_Ab(a Integer, b Integer) RETURNS Integer AS Begin return a + b; End; -- Function without parameters CREATE FUNCTION Get_Current_Datetime() RETURNS Date AS Begin return CURRENT_TIMESTAMP; End;

Отмена функции (Drop function):

Drop Function DROP FUNCTION ; -- For example: DROP FUNCTION My_Function;

Пример создания функции:

Пример создания вашей первой function (функции) с SQL Server :

  1. Создать функцию (Function)
  2. Компилировать данную функцию
  3. Запуск функции

Check the existence of the function -- If it did exist, should drop it in order to create a new one. IF OBJECT_ID(N"dbo.My_Sum", N"FN") IS NOT NULL DROP FUNCTION My_Sum; GO CREATE FUNCTION My_Sum (@p_a float, @p_b float) RETURNS float AS BEGIN -- Declaring a variable type of Float DECLARE @v_C float; -- Assign value for v_C SET @V_C = @p_A + @p_B; -- Return value. RETURN @v_C; END;

Нажмите на знак чтобы компилировать функцию.

Функция, которую вы создали, является простой функцией возвращающая скалярное значение (Scalar-value). Вы можете увидеть как она создается на SQLServer Management Studio :

Можете протестировать функцию, нажав на правую мышь, выберите:

  • Script function as -> SELECT to -> New Query Editor Window

Откроется тестовое окно, вы можете поменять параметры значений:

Поменять параметры значений и нажать на запуск.

Функции могут участвовать в команде SELECT .

SELECT acc.account_id, acc.cust_id, acc.avail_balance, acc.pending_balance, dbo.MY_SUM(acc.avail_balance, acc.pending_balance) balance FROM account acc;

Результаты запроса SQL:

10- Процедура (Procedure)

Группа команд T-SQL , выполняющие определенные функции могут быть собраны в процедуре (procedure), чтобы увеличить возможность выполнения, общего пользования, безопасности, безопасности данных, и полезность в развитии.

Процедуры могут быть сохранены в database как объект в database , готовый для переиспользования. Процедура в данный момент называется Store procedure . Чтобы выполнить Store procedure , сразу после сохранения Store procedure , они компилируются в p-code поэтому могут повысить возможность выполненния.

Процедуры не возвращают значения напрямую как функции (function ). Но могут иметь 0 или более параметров на выход.

Синтаксис создания процедуры:

Procedure_name: -- argument: -- mode: input type: INPUT or OUTPUT, default is INPUT -- datatype: -- Note: The procedure parameters can put in an (), or unnecessary. CREATE PROCEDURE [ argument1 datatype1 , argument2 datatype2 , ... ] AS BEGIN -- Declare variables. -- Statements .. END; -- OR: CREATE PROCEDURE ([ argument1 datatype1 , argument2 datatype2 , ... ]) AS BEGIN -- Declare variables. -- Statements .. END;

Procedure without parameters. CREATE Procedure Do_Something AS Begin -- Declare variables here. Declare @v_a Integer; -- Do something here -- .... End; -- Procedure with parameters -- 1 input parameter and 2 output parameters CREATE Procedure Do_Something (@p_Param1 Varchar(20), @v_Param2 Varchar(50) OUTPUT) AS Begin -- Declare variables Declare @v_a Integer; -- Do something here. -- ... End;

Отменить процедуру (Drop procedure):

Drop Procedure: DROP PROCEDURE

Шаги для выполнения процедуры:

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

Get_Employee_Infos

Drop procedure Get_Employee_Infos if it already exists. -- (To enable recreate) IF OBJECT_ID(N"dbo.Get_Employee_Infos", N"P") IS NOT NULL DROP PROCEDURE Get_Employee_Infos; GO -- Procedure with input parameter: p_Emp_Id -- And output: v_First_Name, v_Last_Name, v_Dept_Id. CREATE PROCEDURE Get_Employee_Infos (@p_Emp_Id integer , @v_First_Name varchar(50) OUTPUT , @v_Last_Name varchar(50) OUTPUT , @v_Dept_Id integer OUTPUT) AS BEGIN -- Use the Print command to print out a string (for programmers). -- Use Cast to convert Integer to string (Varchar). -- Use the + operator to concatenate two strings. PRINT "Parameter @p_Emp_Id = " + CAST(@p_Emp_ID AS varchar(15)); -- -- Query data from the table and assign values to variables. -- SELECT @v_First_Name = Emp.First_Name, @v_Last_Name = Emp.Last_Name, @v_Dept_Id = Emp.Dept_Id FROM Employee Emp WHERE Emp.Emp_Id = @p_Emp_Id; -- -- Log (For developers). -- PRINT "Found Record!"; PRINT " @v_First_Name= " + @v_First_Name; PRINT " @v_Last_Name= " + @v_Last_Name; PRINT " @v_Dept_Id= " + CAST(@v_Dept_Id AS varchar(15)); END;

  • Mark a savepoint in transaction: save transaction name_of_savepoint
  • @@trancount variable: shows the number of transactions is being executed (has not been finished with rollback or commit) in the current connection.
    1. Команда rollback tran + tên_của_savepoint помогает отменить(rollback) транзакцию до соответствующей позиции savepoint (без эффекта завершения транзакции), блокировки (locks) будут разблокированы (unlock) при выполнении манипуляций rollback определенных частей.
    2. При объявлении явной транзакции, нужно удостовериться что она может быть отменена (rollback ) или зафиксирована в явном виде (commit ), если нет, транзакция будет продолжать существовать и занимать ресурсы, предотвращая выпонения других транзакций .
    3. Команда rollback помогает отменить только транзакции в базе данных (insert , delete , update ). Другие команды, например прикрепить, не будут зависеть от команды rollback .

    Transaction_Example1

    BEGIN -- In this example the accounts ACCOUNT_ID = 1, 2 actually exists in DB -- In fact you can write statements to check before the start of transaction -- -- account A (Already guarantees exist in DB) DECLARE @Account_Id_A integer = 1; -- account B (Already guarantees exist in DB) DECLARE @Account_Id_B integer = 2; -- Amount DECLARE @Amount float = 10; -- Bank DECLARE @Execute_Branch_Id integer = 1; -- Write out transaction Count. -- In fact, at this time there is no transaction yet PRINT "@@TranCount = " + CAST(@@Trancount AS varchar(5)); PRINT "Begin transaction"; -- Begin transaction BEGIN TRAN; -- Error trapping. BEGIN TRY -- -- Subtract $10 from account A UPDATE Account SET AVAIL_BALANCE = AVAIL_BALANCE - @Amount WHERE Account_Id = @Account_Id_A; -- -- Insert transaction info into Acc_Transaction table. INSERT INTO ACC_TRANSACTION (TXN_DATE, FUNDS_AVAIL_DATE, TXN_TYPE_CD, ACCOUNT_ID, AMOUNT, EXECUTION_BRANCH_ID) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, "CDT", @Account_Id_A, , @Execute_Branch_Id); -- -- Add $10 to Account B. UPDATE Account SET AVAIL_BALANCE = AVAIL_BALANCE + @Amount WHERE Account_Id = @Account_Id_B; -- -- Insert transaction info into Acc_Transaction table. INSERT INTO ACC_TRANSACTION (TXN_DATE, FUNDS_AVAIL_DATE, TXN_TYPE_CD, ACCOUNT_ID, AMOUNT, EXECUTION_BRANCH_ID) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, "CDT", @Account_Id_B, @Amount, @Execute_Branch_Id); -- Commit transaction IF @@Trancount > 0 PRINT "Commit Transaction"; COMMIT TRAN; END TRY -- If there are errors Catch block will be execute. BEGIN CATCH PRINT "Error: " + ERROR_MESSAGE(); PRINT "Error --> Rollback Transaction"; IF @@Trancount > 0 ROLLBACK TRAN; END CATCH; END;

    Результаты запуска примера:

    12- Trigger

    Статья про Trigger отделена, вы можете посмотреть инструкцию по ссылке:

    • TODO Link!

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

    Программирование баз данных, а если говорить конкретней, то баз данных под управлением Microsoft SQL Server, стало очень популярно, именно поэтому я решил сделать своего рода мини справочник, в котором можно найти все основные моменты с пояснениями и, конечно же, с примерами.

    Для того чтобы потренироваться писать SQL запросы или создавать объекты базы данных, можете использовать бесплатную редакцию SQL Server Express, на момент составления справочника последней версией является Microsoft SQL Server 2014 Express .

    Описание справочника

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

    Так как охватить абсолютно все просто невозможно, поэтому не удивляйтесь, если Вы что-то здесь не обнаружили. Еще раз повторю, что данный справочник создан для начинающих программистов на Transact-SQL, а также для простых админов, которым периодически требуется выгружать какие-то данные с SQL сервера.

    Для детального изучения языка T-SQL рекомендую почитать мою книгу «Путь программиста T-SQL. Самоучитель по языку Transact-SQL », в которой я максимально подробно, с большим количеством примеров рассказываю о языке T-SQL.

    Transact-SQL справочник для начинающих

    База данных

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

    Создание

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

    CREATE DATABASE test

    где, test это название базы данных.

    Подробней о создании базы данных на SQL сервере, мы разговаривали в материале Как создать базу данных в MS Sql 2008

    Удаление

    Если Вам необходимо удалить базу данных, то можете использовать запрос:

    DROP DATABASE test

    Изменение

    Для изменений параметров базы данных можно использовать графический интерфейс Management Studio, в котором все параметры подробно описаны, а можно посылать запросы ALTER DATABASE, например, для включения автоматического сжатия базы данных test, используем следующий запрос

    ALTER DATABASE test SET AUTO_SHRINK ON; --А для выключения ALTER DATABASE test SET AUTO_SHRINK OFF;

    Надеюсь понятно, ALTER DATABASE команда на изменение, test название изменяемой базы данных, SET команда, указывающая на то, что мы будем изменять параметры базы данных, AUTO_SHRINK непосредственно сам параметр, ON/OFF значение параметра.

    Типы данных

    Самые распространенные и часто используемые

    Точные числа

    • tinyint — 1 байт
    • smallint — 2 байта
    • int — 4 байта
    • bigint — 8 байт
    • numeric и decimal (тип с фиксированной точностью и масштабом )
    • money — 8 байт
    • smallmoney — 4 байт

    Приблизительные числа

    • float [ (n) ] – размер зависит от n (n может быть от 1 до 53, по умолчанию 53)
    • real — 4 байта

    Дата и время

    • date – дата
    • time — время
    • datetime — дата, включающая время дня с долями секунды в 24-часовом формате.

    Символьные строки

    • char [ (n) ] – строка с фиксированной длиной, где n длина строки (от 1 до 8000). Размер при хранении составляет n байт.
    • varchar [ (n | max) ] — строка с фиксированной длиной, где n длина строки (от 1 до 8000). Если указать max, то, максимальный размер при хранении составит 2^31-1 байт (2 ГБ), а при указании n то фактическая длина введенных данных плюс 2 байта.
    • text – строковые данные переменной длины, максимальный размер 2 147 483 647 байт (2 ГБ).
    • nchar [ (n) ] — строка с фиксированной длиной в Юникоде, где n длина строки (от 1 до 4000). Размер при хранении составляет удвоенное значение n в байтах
    • nvarchar [ (n | max) ] — строка с фиксированной длиной в Юникоде, где n длина строки (от 1 до 4000). При указании max, максимальный размер при хранении составит 2^31-1 байт (2 ГБ), а если n, то удвоенная фактическая длина введенных данных плюс 2 байта.
    • ntext — строковые данные переменной длины, с максимальной длиной строки 1 073 741 823 байт.

    Двоичные данные

    • binary [ (n) ] — двоичные данные с фиксированной длиной, размером n байт, где n значение от 1 до 8000. Размер при хранении составляет n байт.
    • varbinary [ (n | max) ] — двоичные данные с переменной длиной, где n может иметь значение от 1 до 8000. Если указать max то максимальный размер при хранении составит 2^31-1 байт(2 ГБ). При указании n то размер хранения это фактическая длина введенных данных плюс 2 байта.
    • image — двоичные данные переменной длины, размером от 0 до 2^31 – 1 (2 147 483 647) байт.

    Другие

    • xml –хранение xml данных. Подробно рассматривали в материале Transact-sql – работа с xml , а если Вы вообще не знаете что такое XML, то об это мы разговаривали в статье Основы XML для начинающих .
    • table – хранение результирующего набора строк.

    Таблицы

    Примеров создания таблиц на этом сайте достаточно, так как практически в каждой статье связанной с SQL я привожу пример создания тестовой таблицы, но для закрепления знаний, давайте создадим, модифицируем и удалим тестовую таблицу. Как раз посмотрим на то, как задаются типы данных полей в таблицах на Transact-SQL.

    Создание

    CREATE TABLE test_table( IDENTITY(1,1) NOT NULL,--идентификатор, целое число int, не разрешены значения NULL (50) NULL, --ФИО, строка длиной 50 символов, значения NULL разрешены NULL, --сумма, приблизительное числовое значение, значения NULL разрешены NULL, --дата и время, значения NULL разрешены (100) NULL --строка длиной 100 символов, значения NULL разрешены) ON GO

    Добавление колонки

    ALTER TABLE test_table ADD prosto_pole numeric(18, 0) NULL
    • test_table — это название таблицы;
    • add — команда на добавление;
    • prosto_pole – название колонки;
    • pole numeric(18, 0) – тип данных новой колонки;
    • NULL – параметр означающий что в данном поле можно хранить значение NULL.

    Изменение типа данных

    Давайте изменим, тип данных нового поля, которое мы только что создали (prosto_pole) с numeric(18, 0) на bigint и увеличим длину поля comment до 300 символов.

    ALTER TABLE test_table ALTER COLUMN prosto_pole bigint; ALTER TABLE test_table ALTER COLUMN comment varchar(300);

    Примечание! SQL сервер не сможет выполнить изменение типа данных, если преобразование значений в этих полях невозможно, в этом случае придется удалять колонку, со всем данными, и добавлять заново или очищать все данные в этом поле.

    Удаление колонки

    Для удаления определенной колонки используем команду drop, например, для удаления поля prosto_pole используем следующий запрос

    ALTER TABLE test_table DROP COLUMN prosto_pole

    Удаление таблицы

    Для того чтобы удалить таблицу, напишем вот такой простой запрос, где test_table и есть таблица для удаления

    DROP TABLE test_table

    Представления

    Очень полезным объектом в базе данных является представление (VIEW) или по-нашему просто вьюха. Если кто не знает, то представление, это своего рода хранимый запрос, к которому можно обращаться также как и к таблице. Давайте создадим представление на основе тестовой таблицы test_table, и допустим, что очень часто нам требуется писать запрос, например, по условию сумма больше 1000, поэтому для того чтобы каждый раз не писать этот запрос мы один раз напишем представление, и впоследствии будем обращаться уже к нему.

    Создание

    CREATE VIEW test_view AS SELECT id, fio, comment FROM test_table WHERE summa > 1000 GO

    Пример обращения к представлению :

    SELECT * FROM test_view

    Изменение

    ALTER VIEW test_view AS SELECT id, fio, comment FROM test_table WHERE summa > 1500 GO

    Удаление

    DROP VIEW test_view

    Системные представления

    В СУБД MS SQL Server есть таки системные объекты, которые могут предоставить иногда достаточно полезную информацию, например системные представления. Сейчас мы разберем парочку таких представлений. Обращаться к ним можно также как и к обычным представлениям (например, select * from название представление )

    • sys.all_objects – содержит все объекты базы данных, включая такие параметры как: название, тип, дата создания и другие.
    • sys.all_columns – возвращает все колонки таблиц с подробными их характеристиками.
    • sys.all_views – возвращает все представления базы данных.
    • sys.tables – все таблицы базы данных.
    • sys.triggers – все триггеры базы данных.
    • sys.databases – все базы данных на сервере.
    • sys.sysprocesses – активные процессы, сессии в базе данных.

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

    Функции

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

    Создание

    CREATE FUNCTION test_function (@par1 bigint, @par2 float) RETURNS varchar(300) AS BEGIN DECLARE @rezult varchar(300) SELECT @rezult=comment FROM test_table WHERE id = @par1 AND summa > @par2 RETURN @rezult END
    • CREATE FUNCTION – команда на создание объекта функция;
    • test_function – название новой функции;
    • @par1 и @par2 – входящие параметры;
    • RETURNS varchar(300) – тип возвращаемого результата;
    • DECLARE @rezult varchar(300) – объявление переменной с типом varchar(300);
    • Инструкция select в нашем случае и есть действия функции;
    • RETURN @rezult – возвращаем результат;
    • BEGIN и END – соответственно начала и конец кода функции.

    Пример использования ее в запросе :

    SELECT test_function(1, 20)

    Изменение

    ALTER FUNCTION test_function (@par1 bigint, @par2 float) RETURNS varchar(300) AS BEGIN DECLARE @rezult varchar(300) SELECT @rezult=comment FROM test_table_new WHERE id = @par1 AND summa >= @par2 RETURN @rezult END

    Удаление

    DROP FUNCTION test_function

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

    Помимо того, что SQL сервер позволяет создавать пользовательские функции, он также предоставляет возможность использовать встроенные функции, которые за Вас уже написали разработчики СУБД. Их очень много, поэтому самые распространенные я разбил на группы и попытался их кратко описать.

    Системные функции

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

    • @@VERSION – возвращает версию SQL сервера;
    • @@SERVERNAME – возвращает имя сервера;
    • SUSER_NAME() – имя входа пользователя на сервер, другими словами, под каким логином работает тот или иной пользователь;
    • user_name() – имя пользователя базы данных;
    • @@SERVICENAME – название сервиса СУБД;
    • @@IDENTITY — последний вставленный в таблицу идентификатор;
    • db_name() — имя текущей базы данных;
    • db_id() – идентификатор базы данных.

    Агрегатные функции

    Функции, которые вычисляют какое-то значение на основе набора (группы) значений. Если при вызове этих функций нужно указать колонку для вывода результата, то необходимо выполнить группировку данных (group by) по данному полю. Подробно данную конструкцию мы рассматривали в статье Transact-SQL группировка данных group by

    • avg – возвращает среднее значение;
    • count – количество значений;
    • max – максимальное значение;
    • min – минимальное значение;
    • sum – сумма значений.

    Пример использования :

    SELECT COUNT(*) as count, SUM(summa) as sum, MAX(id) as max, MIN(id) as min, AVG(summa) as avg FROM test_table

    Строковые функции

    Данный вид функций соответственно работает со строками.

    Left (строковое выражение , количество символов ) – возвращает указанное число символов строки начиная слева.

    Пример

    SELECT LEFT("Пример по работе функции left", 10) --Результат "Пример по"

    Right (строковое выражение , количество символов ) – возвращает указанное число символов строки начиная справа

    Пример

    SELECT Right("Пример по работе функции Right", 10) -- Результат "кции Right"

    Len (строка ) – возвращает длину строки.

    Пример

    SELECT len("Пример по работе функции len") --Результат 28

    Lower (строка ) – возвращает строку, в которой все символы приведены к нижнему регистру.

    Пример

    SELECT lower("Пример по работе функции lower") --Результат "пример по работе функции lower"

    Upper (строка ) — возвращает строку, в которой все символы приведены к верхнему регистру.

    Пример

    SELECT Upper("Пример по работе функции Upper") --Результат "ПРИМЕР ПО РАБОТЕ ФУНКЦИИ UPPER"

    Ltrim (строка ) – возвращает строку, в которой все начальные пробелы удалены.

    Пример

    SELECT ltrim(" Пример по работе функции ltrim") --Результат "Пример по работе функции ltrim"

    Rtrim (строка ) – возвращает строку, в которой все пробелы справа удалены

    Пример

    SELECT Rtrim (" Пример по работе функции Rtrim ") -- Результат" Пример по работе функции Rtrim"

    Replace (строка , что ищем , на что заменяем ) – заменяет в строковом выражении все вхождения указанные во втором параметре, символами указанным в третьем параметре.

    Пример

    SELECT Replace ("Пример по работе функции Replace", "по работе", "ЗАМЕНА") -- Результат "Пример ЗАМЕНА функции Replace"

    Replicate (строка , количество повторений ) – повторяет строку (первый параметр) столько раз, сколько указанно во втором параметре.

    Пример

    SELECT Replicate ("Пример Replicate ", 3) -- Результат "Пример Replicate Пример Replicate Пример Replicate "

    Reverse (строка ) – возвращает все в обратном порядке.

    Пример

    SELECT Reverse ("Пример по работе функции Reverse") -- Результат "esreveR иицкнуф етобар оп ремирП"

    Space (число пробелов ) – возвращает строку в виде указанного количества пробелов.

    Пример

    SELECT Space(10) -- Результат " "

    Substring (строка , начальная позиция , сколько символов ) – возвращает строку, длиной в число указанное в третьем параметре, начиная с символа указанного во втором параметре.

    Пример

    SELECT Substring("Пример по работе функции Substring", 11, 14) -- Результат "работе функции"

    Математические функции

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

    Пример

    SELECT Round(10.4569, 2) -- Результат "10.4600"

    Floor (число ) – возвращает целое число, округленное в меньшую сторону.

    Пример

    SELECT Floor(10.4569) -- Результат "10"

    Ceiling (число ) – возвращает целое число, округленное в большую сторону.

    Пример

    SELECT Ceiling (10.4569) -- Результат "11"

    Power (число , степень ) — возвращает число возведенное в степень указанную во втором параметре.

    Пример

    SELECT Power(5,2) -- Результат "25"

    Square (число ) – возвращает числовое значение, возведенное в квадрат

    Пример

    SELECT Square(5) -- Результат "25"

    Abs (число ) – возвращает абсолютное положительное значение

    Пример

    SELECT Abs(-5) -- Результат "5"

    Log (число ) – натуральный логарифм с плавающей запятой.

    Пример

    SELECT Log(5) -- Результат "1,6094379124341"

    Pi – число пи.

    Пример

    SELECT Pi() -- Результат "3,14159265358979"

    Rand – возвращает случайное число с плавающей запятой от 0 до 1

    Пример

    SELECT rand() -- Результат "0,713273187517105"

    Функции даты и времени

    Getdate() – возвращает текущую дату и время

    Пример

    SELECT Getdate() -- Результат "2014-10-24 16:36:23.683"

    Day (дата ) – возвращает день из даты.

    Пример

    SELECT Day(Getdate()) -- Результат "24"

    Month (дата) – возвращает номер месяца из даты.

    Пример

    SELECT Month(Getdate()) -- Результат "10"

    Year (дата ) –возвращает год из даты

    Пример

    SELECT year(Getdate()) -- Результат "2014"

    DATEPART (раздел даты , дата ) – возвращает из даты указанный раздел (DD,MM,YYYY и др.)

    Пример

    SELECT DATEPART(MM,GETDATE()) -- Результат "10"

    Isdate (дата ) – проверяет введенное выражение, является ли оно датой

    Пример

    SELECT Isdate(GETDATE()) -- Результат "1"

    Функции преобразование

    Cast (выражение as тип данных ) – функция для преобразования одного типа в другой. В примере мы преобразуем тип float в int

    Пример

    SELECT CAST(10.54 as int) --результат 10

    Convert – (тип данных, выражение, формат даты ) – функция для преобразования одного типа данных в другой. Очень часто ее используют для преобразования даты, используя при этом третий необязательный параметр — формат даты.

    Пример

    SELECT GETDATE(), CONVERT(DATE, GETDATE(), 104) --Результат --2014-10-24 15:20:45.270 – без преобразования; --2014-10-24 после преобразования.

    Табличные функции

    Создаются, для того чтобы получать из них данные как из таблиц, но после различного рода вычислений. Подробно о табличных функциях мы разговаривали в материале Transact-sql – Табличные функции и временные таблицы

    Создание

    --название нашей функции CREATE FUNCTION fun_test_tabl (--входящие параметры и их тип @id INT) --возвращающее значение, т.е. таблица RETURNS TABLE AS --сразу возвращаем результат RETURN (--сам запрос или какие то вычисления SELECT * FROM test_table where id = @id) GO

    Изменение

    --название нашей функции ALTER FUNCTION fun_test_tabl (--входящие параметры и их тип @id INT) --возвращающее значение, т.е. таблица RETURNS TABLE AS --сразу возвращаем результат RETURN (--сам запрос или какие то вычисления SELECT * FROM test_table where id = @id and summa > 100) GO

    Удаление

    DROP FUNCTION fun_test_tabl

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

    Пример обращения к этой функции

    SELECT * FROM fun_test_tabl(1)

    Процедуры

    Процедуры – это набор SQL инструкций, которые компилируются один раз, и могут принимать, как и функции, различные параметры. Используются для упрощения расчетов, выполнения групповых действий.

    Создание

    CREATE PROCEDURE sp_test_procedure (@id INT) AS --объявляем переменные DECLARE @sum FLOAT --SQL инструкции SET @sum = 100 UPDATE test_table SET summa = summa + @sum WHERE id = @id GO

    Изменение

    ALTER PROCEDURE sp_test_procedure (@id int) AS --объявляем переменные DECLARE @sum float --SQL инструкции SET @sum = 500 UPDATE test_table SET summa = summa + @sum WHERE id = @id GO

    Удаление

    DROP PROCEDURE sp_test_procedure

    Вызов процедуры

    Можно вызывать по разному, например:

    EXECUTE sp_test_procedure 1 --или EXEC sp_test_procedure 1

    Где, EXECUTE и EXEC вызов процедуры, sp_test_procedure соответственно название нашей процедуры, 1 значение параметра

    Системные процедуры

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

    Их огромное множество, поэтому приведу всего несколько примеров.

    sp_configure – процедура для отображения и внесения изменений в конфигурацию ядра СУБД. Первый параметр название параметра конфигурации, второй параметр значение.

    Пример

    Изменяем значение параметра EXEC sp_configure "Ad Hoc Distributed Queries",1 reconfigure --применяем EXEC sp_configure --просто просматриваем значения всех параметров

    где, ‘Ad Hoc Distributed Queries’ — это название параметра, 1 соответственно значение, на которое мы хотим изменить, reconfigure применят введенное значение.

    На практике мы применяли эту процедуру в материале Межбазовый запрос на Transact-SQL

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

    Пример

    EXECUTE sp_executesql N"SELECT * FROM test_table WHERE id = @id", N"@id int", @id = 1

    Где, первый параметр — sql инструкция (строка в Юникоде), второй — определение всех параметров встроенных в sql инструкцию, третий — значение параметров.

    sp_help – возвращает подробные сведения о любом объекте базы данных.

    Пример

    EXECUTE sp_help "test_table"

    sp_rename – переименовывает объект в базе данных. Можно использовать для переименования таблиц, индексов, название колонок в таблицах, Не рекомендуется использовать эту процедуру для переименования пользовательских процедур, триггеров, функций.

    Пример переименования таблицы

    EXEC sp_rename "test_table", "test_table_new"

    где, первым параметром идет объект со старым названием, а второй параметр — это новое название объекта.

    Пример переименования столбца в таблице

    EXEC sp_rename "test_table.summa", "summa_new", "COLUMN"

    Третьим параметром указывается, что переименовывается колонка.

    Триггеры

    Триггер – это обычная процедура, но вызывается она событием, а не пользователем. Событие, например, может быть вставка новой строки в таблицу (insert), обновление данных в таблице (update) или удаление данных из таблицы (delete).

    Создание

    CREATE TRIGGER trg_test_table_update ON test_table for UPDATE --можно также delete, insert AS BEGIN --sql инструкции в случае UPDATE END GO

    Изменение

    ALTER TRIGGER trg_test_table_update ON test_table for insert --можно также delete, update AS BEGIN --sql инструкции в случае insert END GO

    Удаление

    DROP TRIGGER trg_test_table_update

    Включение/Отключение

    --отключение DISABLE TRIGGER trg_test_table_update ON test_table; --включение ENABLE TRIGGER trg_test_table_update ON test_table;

    О триггерах мы разговаривали в статье — Как создать триггер на Transact-SQL .

    Индексы

    Это объект базы данных, который повышает производительность поиска данных, за счет сортировки данных по определенному полю. Если провести аналогию то, например, искать определенную информацию в книге намного легче и быстрей по его оглавлению, чем, если бы этого оглавления не было. В СУБД MS SQL Server существует следующие типы индексов:

    Кластеризованный индекс — при таком индексе строки в таблице сортируются с заданным ключом, т.е. указанным полем. Данный тип индексов у таблицы в MS SQL сервере может быть только один и, начиная с MS SQL 2000, он автоматически создается при указании в таблице первичного ключа (PRIMARY KEY).

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

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

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

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

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

    Columnstore index доступен начиная с 2012 версии SQL сервера в редакциях Enterprise, Developer и Evaluation.

    Создание

    Кластеризованного индекса

    CREATE CLUSTERED INDEX idx_clus_one ON test_table(id) GO

    Где, CREATE CLUSTERED INDEX — это инструкция к созданию кластеризованного индекса, idx_clus_one название индекса, test_table(id) соответственно таблица и ключевое поле для сортировки.

    Некластеризованного индекса

    CREATE INDEX idx_no_clus ON test_table(summa) GO

    Columnstore index

    CREATE columnstore INDEX idx_columnstore ON test_table(date_create) GO

    Отключение

    --отключение ALTER INDEX idx_no_clus ON test_table DISABLE --включение, перестроение ALTER INDEX idx_no_clus ON test_table REBUILD

    Удаление

    DROP INDEX idx_no_clus ON test_table GO

    Курсоры

    Курсор — это своего рода тип данных, который используется в основном в процедурах и триггерах. Он представляет собой обычный набор данных, т.е. результат выполнения запроса.

    Пример (все это в коде процедуры)

    Объявляем переменные DECLARE @id BIGINT DECLARE @fio VARCHAR(100) DECLARE @summa FLOAT --объявляем курсор DECLARE test_cur CURSOR FOR SELECT id, fio, summa FROM test_table --открываем курсор OPEN test_cur --считываем данные первой строки в курсоре --и записываем их в переменные FETCH NEXT FROM test_cur INTO @id, @fio, @summa --запускаем цикл до тех пор, пока не закончатся строки в курсоре WHILE @@FETCH_STATUS = 0 BEGIN --на каждую итерацию цикла можем выполнять sql инструкции --..................SQL инструкции................. --считываем следующую строку курсора FETCH NEXT FROM test_cur INTO @id, @fio, @summa END --закрываем курсор CLOSE test_cur DEALLOCATE test_cur

    Подробно о курсорах мы разговаривали в материале Использование курсоров и циклов в Transact-SQL .

    Запросы DML

    DML (Data Manipulation Language ) – это операторы SQL, с помощью которых осуществляется манипуляция данными. К ним относятся select, update, insert, delete.

    SELECT

    Пример

    SELECT * FROM test_table

    UPDATE

    Используется для обновления данных

    Пример

    Обновятся все строки в таблице UPDATE test_table SET summa=500 --обновятся только строки, у которых id больше 10 UPDATE test_table SET summa=100 WHERE id > 10

    INSERT

    Оператор на добавление данных

    Добавление одной строки INSERT INTO test_table (fio, summa, date_create, comment) VALUES ("ФИО",100, "26.10.2014", "тестовая запись") --массовое добавление на основе запроса INSERT INTO test_table SELECT fio, summa, date_create, comment FROM test_table

    DELETE

    С помощью этого оператора можно удалить данные.

    Пример

    Очищение всей таблицы DELETE test_table --удаление только строк попавших под условие DELETE test_table WHERE summa > 100

    Вот и все, справочник закончился! Надеюсь, он Вам хоть как-то помог. Удачи!

    Последнее обновление: 24.06.2017

    SQL Server является одной из наиболее популярных систем управления базами данных (СУБД) в мире. Данная СУБД подходит для самых различных проектов: от небольших приложений до больших высоконагруженных проектов.

    SQL Server был создан компанией Microsoft. Первая версия вышла в 1987 году. А текущей версией является версия 16, которая вышла в 2016 году и которая будет использоваться в текущем руководстве.

    SQL Server долгое время был исключительно системой управления базами данных для Windows, однако начиная с версии 16 эта система доступна и на Linux.

    SQL Server характеризуется такими особенностями как:

      Производительность. SQL Server работает очень быстро.

      Надежность и безопасность. SQL Server предоставляет шифрование данных.

      Простота. С данной СУБД относительно легко работать и вести администрирование.

    Центральным аспектом в MS SQL Server, как и в любой СУБД, является база данных. База данных представляет хранилище данных, организованных определенным способом. Нередко физически база данных представляет файл на жестком диске, хотя такое соответствие необязательно. Для хранения и администрирования баз данных применяются системы управления базами данных (database management system) или СУБД (DBMS). И как раз MS SQL Server является одной из такой СУБД.

    Для организации баз данных MS SQL Server использует реляционную модель. Эта модель баз данных была разработана еще в 1970 году Эдгаром Коддом. А на сегодняшний день она фактически является стандартом для организации баз данных.

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

    Для идентификации каждой строки в рамках таблицы применяется первичный ключ (primary key). В качестве первичного ключа может выступать один или несколько столбцов. Используя первичный ключ, мы можем ссылаться на определенную строку в таблице. Соответственно две строки не могут иметь один и тот же первичный ключ.

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

    Для взаимодействия с базой данных применяется язык SQL (Structured Query Language). Клиент (например, внешняя программа) отправляет запрос на языке SQL посредством специального API. СУБД должным образом интерпретирует и выполняет запрос, а затем посылает клиенту результат выполнения.

    Изначально язык SQL был разработан в компании IBM для системы баз данных, которая называлась System/R. При этом сам язык назывался SEQUEL (Structured English Query Language). Хотя в итоге ни база данных, ни сам язык не были впоследствии официально опубликованы, по традиции сам термин SQL нередко произносят как "сиквел".

    В 1979 году компания Relational Software Inc. разработала первую систему управления баз данных, которая называлась Oracle и которая использовала язык SQL. В связи с успехом данного продукта компания была переименована в Oracle.

    Впоследствии стали появляться другие системы баз данных, которые использовали SQL. В итоге в 1989 году Американский Национальный Институт Стандартов (ANSI) кодифицировал язык и опубликовал его первый стандарт. После этого стандарт периодически обновлялся и дополнялся. Последнее его обновление состоялось в 2011 году. Но несмотря на наличие стандарта нередко производители СУБД используют свои собственные реализации языка SQL, которые немного отличаются друг от друга.

    Выделяются две разновидности языка SQL: PL-SQL и T-SQL. PL-SQL используется в таких СУБД как Oracle и MySQL. T-SQL (Transact-SQL) применяется в SQL Server. Собственно поэтому в рамках текущего руководства будет рассматриваться именно T-SQL.

    В зависимости от задачи, которую выполняет команда T-SQL, он может принадлежать к одному из следующих типов:

      DDL (Data Definition Language / Язык определения данных). К этому типу относятся различные команды, которые создают базу данных, таблицы, индексы, хранимые процедуры и т.д. В общем определяют данные.

      В частности, к этому типу мы можем отнести следующие команды:

      • CREATE : создает объекты базы данных (саму базу даных, таблицы, индексы и т.д.)

        ALTER : изменяет объекты базы данных

        DROP : удаляет объекты базы данных

        TRUNCATE : удаляет все данные из таблиц

      DML (Data Manipulation Language / Язык манипуляции данными). К этому типу относят команды на выбору данных, их обновление, добавление, удаление - в общем все те команды, с помощью которыми мы можем управлять данными.

      К этому типу относятся следующие команды:

      • SELECT : извлекает данные из БД

        UPDATE : обновляет данные

        INSERT : добавляет новые данные

        DELETE : удаляет данные

      DCL (Data Control Language / Язык управления доступа к данным). К этому типу относят команды, которые управляют правами по доступу к данным. В частности, это следующие команды:

      • GRANT : предоставляет права для доступа к данным

        REVOKE : отзывает права на доступ к данным

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

    И начать хотелось бы с того, что на этом сайте мы с Вами уже достаточно много материала посвятили языку SQL и в частности его расширению Transact-SQL (как Вы понимаете T-SQL это сокращение от Transact-SQL ). И даже составили небольшой справочник для начинающих по данному языку и, конечно же, рассмотрели множество примеров, но как таковое программирование на T-SQL там, например, переменные, условные конструкции, комментарии мы затрагивали, но не заостряли на этом внимания. Но так как у нас сайт для начинающих программистов я решил посвятить этот материал именно этим основам.

    Язык программирования T-SQL

    Transact-SQL (T-SQL ) – расширение языка SQL от компании Microsoft и используется в SQL Server для программирования баз данных.

    SQL Server включает много конструкций, компонентов, функций которые расширяют возможности языка SQL стандарта ANSI, в том числе и классическое программирование, которое отличается от обычного написания запросов.

    И сегодня мы с Вами рассмотрим ту часть основ языка T-SQL, которая подразумевает написание кода для реализации некого функционала (например, в процедуре или функции ), а не просто какого-то запроса к базе данных.

    Примечание! Код я буду писать в окне запроса среды SQL Server Management Studio, о том, как установить SQL Server и Management Studio в редакции Express мы с Вами разговаривали вот .

    Переменные в T-SQL

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

    Существует две разновидности переменных в T-SQL — это локальные и глобальные. Локальные переменные существуют только в пределах сеанса, во время которого они были созданы, а глобальные используются для получения информации о SQL сервере или какой-то меняющейся информации в базе данных.

    Локальные переменные объявляются с помощью ключевого слова DECLARE и начинаются со знака @ . Как и во многих языках программирования, переменные в T-SQL должны иметь свой тип данных. Типов данных в SQL сервере достаточно много мы их подробно рассмотрели в справочнике, который я упоминал чуть выше.

    Для присвоения значения переменной можно использовать команды SET или Select .

    Как я уже сказал, глобальные переменные нужны для того, чтобы получать какую-либо информацию о сервере или о базе данных, например, к глобальным переменным в SQL Server относятся:

    • @@ROWCOUNT – хранит количество записей, обработанных предыдущей командой;
    • @@ERROR – возвращает код ошибки для последней команды;
    • @@SERVERNAME — имя локального SQL сервера;
    • @@VERSION — номер версии SQL Server;
    • @@IDENTITY — последнее значение счетчика, используемое в операции вставки (insert ).

    Теперь для примера давайте создадим две переменной с типом данных INT, присвоим им значения, первой с помощью команды SET, а второй с помощью команды Select, затем просто выведем на экран эти значения, а также выведем и значение переменной @@VERSION, т.е. узнаем версию SQL сервера.

    DECLARE @TestVar1 INT DECLARE @TestVar2 INT SET @TestVar1 = 1 SELECT @TestVar2 = 2 SELECT @TestVar1 AS [Переменная 1], @TestVar2 AS [Переменная 2], @@VERSION AS [Версия SQL Server]

    Пакеты

    Пакет в T-SQL — это команды или инструкции SQL, которые объединены в одну группу и при этом SQL сервер будет компилировать, и выполнять их как одно целое.

    Для того чтобы дать понять SQL серверу, что Вы передаете пакет команд необходимо указывать ключевое слово GO после всех команд, которые Вы хотите объединить в пакет.

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

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


    Т.е. мы видим, что у нас вышла ошибка, связанная с тем, что переменная @TestVar1 у нас не объявлена.

    Условные конструкции

    Эти конструкции подразумевают ветвление, т.е. в зависимости от выполнения или невыполнения определенных условий инструкции T-SQL будут менять свое направление.

    IF…ELSE

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

    DECLARE @TestVar1 INT DECLARE @TestVar2 VARCHAR(20) SET @TestVar1 = 5 IF @TestVar1 > 0 SET @TestVar2 = "Больше 0" ELSE SET @TestVar2 = "Меньше 0" SELECT @TestVar2 AS [Значение TestVar1]

    IF EXISTS

    Данная конструкция позволяет определить наличие записей определенных условием. Например, мы хотим знать есть ли в таблице те или иные записи и при обнаружении первого совпадения обработка команды прекращается. По сути это то же самое, что и COUNT(*) > 0.

    К примеру, мы хотим проверить есть ли записи со значение id >=0 в таблице test_table, и на основе этого мы будем принимать решение, как действовать дальше


    DECLARE @TestVar VARCHAR(20) IF EXISTS(SELECT * FROM test_table WHERE id > = 0) SET @TestVar = "Записи есть" ELSE SET @TestVar = "Записей нет" SELECT @TestVar AS [Наличие записей]

    CASE

    Данная конструкция используется совместно с оператором select и предназначена она для замены многократного использования конструкции IF. Она полезна в тех случаях, когда необходимо проверять переменную (или поле ) на наличие определенных значений.


    DECLARE @TestVar1 INT DECLARE @TestVar2 VARCHAR(20) SET @TestVar1 = 1 SELECT @TestVar2 = CASE @TestVar1 WHEN 1 THEN "Один" WHEN 2 THEN "Два" ELSE "Неизвестное" END SELECT @TestVar2 AS [Число]

    BEGIN…END

    Эта конструкция необходима для создания блока команд, т.е. например, если бы мы хотели выполнить не одну команду после блока IF, а несколько, то нам бы пришлось писать все команды внутри блока BEGIN…END.

    Давайте модифицируем наш предыдущий пример (про IF EXISTS ) так, чтобы при наличии записей id > = 0 в таблице test_table, мы помимо присвоения значения переменной @TestVar, выполним еще и update, т.е. обновление неких данных в этой же таблице, а также выведем количество строк, которые мы обновили, используя глобальную переменную @@ROWCOUNT.


    DECLARE @TestVar1 VARCHAR(20) DECLARE @TestVar2 INT SET @TestVar2 = 0 IF EXISTS(SELECT * FROM test_table WHERE id > = 0) BEGIN SET @TestVar1 = "Записи есть" UPDATE test_table SET column1 = 5 WHERE id > = 0 SET @TestVar2 = @@ROWCOUNT END ELSE SET @TestVar1 = "Записей нет" SELECT @TestVar1 AS [Наличие записей], @TestVar2 AS [Затронуто строк:]

    Циклы T-SQL

    Если говорить в общем о циклах, то они нужны для многократного повторения выполнения команд. В языке T-SQL есть один цикл WHILE с предусловием , это означает, что команды начнутся, и будут повторяться до тех пор, пока выполняется условие перед началом цикла, также выполнение цикла можно контролировать с помощью ключевых слов BREAK и CONTINUE .


    DECLARE @Cnt INT = 1, @result INT = 0, @CountRow INT SELECT @CountRow = COUNT(*) FROM test_table WHILE @Cnt <= @CountRow BEGIN SET @Cnt += 1 SET @result += 1 IF @Cnt = 20 BREAK ELSE CONTINUE END SELECT @result AS [Количество выполнений цикла:]

    В данном примере мы сначала, конечно же, объявляем переменные (Cnt и result мы сразу инициализируем, таким способом можно задавать значения переменных, начиная с SQL Server 2008 ). Затем узнаем, сколько строк в таблице test_table и после этого проверяем, если количество строк в таблице больше или равно нашему счетчику, то входим в наш тестовый цикл. В цикле мы увеличиваем значение счетчика, записываем результат и снова проверяем, если наш счетчик достиг уже значения 20, то мы его принудительно завершим, если нет, то пусть работает дальше, до того как значение счетчика станет больше или равно количеству строк в таблице или до 20, если в таблице строк больше.

    Комментарии

    Они нужны для того, чтобы пояснять, делать заметки в коде, так как если код большой и сложный, то через некоторое время Вы можете просто забыть, почему именно так и для чего Вы написали тот или иной участок кода. В языке T-SQL бывают однострочные (—Текст) и многострочные комментарии (/*Текст*/).


    Команды T-SQL

    GOTO

    С помощью этой команды можно перемещаться по коду к указанной метке. Например, ее можно использовать тогда когда Вы хотите сделать своего рода цикл, но без while.


    DECLARE @Cnt INT = 0 Metka: --Устанавливаем метку SET @Cnt += 1 --Прибавляем к переменной 1 if @Cnt < 10 GOTO Metka --Если значение меньше 10, то переходим к метке SELECT @Cnt AS [Значение Cnt =]

    WAITFOR

    Команда может приостановить выполнение кода на время или до наступления заданного времени. Параметр DELAY делает паузу заданной длины, а TIME приостанавливает процесс до указанного времени. Значение параметров задается в формате hh:mi:ss


    DECLARE @TimeStart time, @TimeEnd time SET @TimeStart = CONVERT (time, GETDATE())--Узнаем время WAITFOR DELAY "00:00:05"--Пауза на 5 секунд SET @TimeEnd = CONVERT (time, GETDATE())--Снова узнаем время --Узнаем, сколько прошло времени в секундах SELECT DATEDIFF(ss, @TimeStart, @TimeEnd) AS [Прошло Секунд:]

    RETURN

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


    DECLARE @Cnt INT = 1, @result varchar(15) /*Если значение Cnt меньше 0, то следующие команды не выполнятся, и Вы не увидите колонку [Результат:]*/ IF @Cnt < 0 RETURN SET @result = "Cnt больше 0" SELECT @result AS [Результат:]

    PRINT

    Для передачи служебного сообщения можно использовать команду PRINT . В Management Studio это сообщение отобразится на вкладке «Сообщения» (Messages ).


    DECLARE @Cnt INT = 10, @TestVar varchar(100) IF @Cnt > 0 SET @TestVar = "Значение переменной Cnt больше 0 и равняется " + CAST(@Cnt AS VARCHAR(10)) ElSE SET @TestVar = "Значение переменной Cnt меньше 0 и равняется " + CAST(@Cnt AS VARCHAR(10)) PRINT @TestVar

    Транзакции

    Транзакция – это команда или блок команд, которые успешно завершаются или отменяются как единое целое. Другими словами, если одна команда или инструкция внутри транзакции завершилась с ошибкой, то все что было отработано перед ней, также отменяется, даже если предыдущие команды завершились успешно.

    Этот механизм необходим для того, чтобы обеспечить целостность данных, т.е. допустим, у Вас есть процедура, которая перечисляет деньги с одного счета на другой, но может возникнуть ситуация при которой деньги снялись со счета, но не поступили на другой счет. К примеру, SQL инструкция, которая осуществляет снятие денег, отработала, а при выполнении инструкции, которая зачисляет деньги, возникла ошибка, другими словами, деньги снялись и просто потерялись. Чтобы этого не допускать, все SQL инструкции пишут внутри транзакции и тогда если наступит такая ситуация все изменения будут отменены, т.е. деньги вернутся на счет обратно.

    Узнаем что у нас в таблице (id = IDENTITY) SELECT * FROM test_table --Начинаем транзакцию BEGIN TRAN --Сначала обновим все данные UPDATE test_table SET column1 = column1 - 5 --Затем просто добавим строки с новыми значениями INSERT INTO test_table SELECT column1 FROM test_table --Если ошибка, то все отменяем IF @@error != 0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRAN --Смотрим что получилось SELECT * FROM test_table

    В этом примере, если бы у нас в момент добавления данных (INSERT) возникла ошибка, то UPDATE бы отменился.

    Обработка ошибок — конструкция TRY…CATCH

    В процессе выполнения T-SQL кода может возникнуть непредвиденная ситуация, т.е. ошибка, которую необходимо обработать. В SQL сервере, начиная с SQL Server 2005, существует такая конструкция как TRY…CATCH , которая может отследить ошибку.


    BEGIN TRY DECLARE @TestVar1 INT = 10, @TestVar2 INT = 0, @result INT SET @result = @TestVar1 / @TestVar2 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS [Номер ошибки], ERROR_MESSAGE() AS [Описание ошибки] END CATCH

    В этом примере возникла ситуация что происходит деление на ноль (как Вы знаете делить на 0 нельзя ) и так как наш блок кода был помещен в конструкцию TRY у нас возникло исключение, при котором мы просто получаем номер ошибки и ее описание.

    Я думаю для основ этого достаточно, если Вы хотите более подробно изучить все конструкции языка T-SQL, то рекомендую прочитать мою книгу «Путь программиста T-SQL », в которой уже более подробно рассмотрен язык T-SQL, у меня все, удачи!