Что называется последовательностью инструкций и процедурных расширений языка transact sql

1. Хранимые процедуры

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

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

Как
серверные программы хранимые процедуры
имеют ряд преимуществ.

• Хранимые
  процедуры хранятся в компилированном
  виде, поэтому выполняются быстрее, чем
  пакеты или запросы.

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

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

Управление
хранимыми процедурами

Хранимые
процедуры управляются посредством
инструкций языка определения данных
(DDL) CREATE, ALTER и DROP.

Общий
синтаксис T-SQL
кода для создания хранимой процедуры
имеет следующий вид:

CREATE
PROC | PROCEDURE <procedure_name>
[ <@parameter>
<data_type> [ = <default> ] [ OUT | OUTPUT ] ] [ ,…n
]
AS
[ BEGIN ] <sql_statements> [ END ]

<procedure_option>
::=
[ ENCRYPTION ]
[ RECOMPILE ]
[
EXECUTE_AS_Clause ]

Структура
этого оператора соответствует основному
синтаксису CREATE <Object Туре> <Object Name>,
лежащему в основе любого оператора
CREATE. Единственная отличительная
особенность состоит в том, что в нем
допускается использовать ключевое
слово PROCEDURE или PROC. Оба эти варианта
являются допустимыми: PROC
является лишь сокращением от PROCEDURE.

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

Процедуры
могут иметь любое число входных параметров
(@parametr)
заданного типа данных (data_type),
которые используются внутри процедуры
как локальные переменные. При выполнении
процедуры для каждого из объявленных
формальных параметров должны быть
переданы фактические значения. Или же
для входного параметра может быть
определено значение по умолчанию
(default),
которое должно быть константой или
равняться NULL. В этом случае процедуру
можно выполнить без указания значения
соответствующего аргумента. Применение
входных параметров необязательно.

Можно
также указать выходные параметры
(помеченные как OUTPUT),
позволяющие хранимой процедуре вернуть
одно или несколько скалярных значений
в подпрограмму, из которой она была
вызвана. При создании процедур можно
задать три параметра. При создании
процедуры с параметром ENCRYPTION
SQL
Server
шифрует определение процедуры. При
задании параметра RECOMPILE
SQL
Server
перекомпилирует хранимую процедуру
при каждом ее запуске. Параметр EXECUTE
AS
определяет контекст безопасности для
процедуры.

В
конце определения хранимой процедуры
вслед за ключевым словом AS должно быть
приведено непосредственно тело процедуры
(sql_statements)
в виде кода из одной или нескольких
инструкций языка T-SQL.

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

Пример
хранимой процедуры без параметров

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

Команда
EXECUTE
или сокращенно EXEC
выполняет указанную хранимую процедуру.

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

Хранимая
процедура предоставляет определенные
процедурные возможности (а если она
применяется в инфраструктуре .NET, такие
возможности становятся весьма
значительными), а также обеспечивает
повышение производительности, но в
большинстве обстоятельств хранимая
процедура не позволяет добиться многого,
если не предусмотрена возможность
передать ей некоторые данные, указывающие
на то, какие действия должны быть
выполнены с ее помощью. В частности
основная проблема, связанная с предыдущей
хранимой процедурой (spr_getOrders),
состоит в ее статичности. Если пользователям
потребуется информация о заказах за
другой период времени, то эта процедура
им не поможет. Поэтому необходимо
предусмотреть возможность передачи в
нее соответствующих входных параметров,
которые позволили бы динамически
изменять период выборки.

Параметры,
передаваемые хранимой процедуре,
перечисляются через запятую в инструкции
CREATE
(ALTER)
PROCEDURE
непосредственно после ее имени. При
объявлении входного параметра необходимо
указать имя параметра, тип данных и
возможно значение по умолчанию. В общем
случае объявление входного параметра
имеет следующий вид:

@parameter_name
[AS]
datatype
[= default|NULL]

Правила
определения входных параметров во
многом аналогичны объявлению локальных
переменных. Каждый из параметров должен
начинаться с символа @. Для хранимой
процедуры он является локальной
переменной. Как и все локальные переменные,
параметры должны объявляться с допустимыми
встроенными или определяемыми
пользователями типами данных СУБД SQL
Server.

Значительные
различия между объявлениями параметров
хранимых процедур и объявлениями
переменных начинают впервые обнаруживаться,
когда дело касается значений, заданных
по умолчанию. Прежде всего, при
инициализации переменным всегда
присваиваются NULL-значения, а на параметры
это правило не распространяется. В
действительности, если в объявлении
параметра не предусмотрено заданное
по умолчанию значение, то подразумевается,
что этот параметр должен быть обязательным
и что при вызове хранимой процедуры
должно быть указано его начальное
значение. Чтобы задать предусмотренное
по умолчанию значение, необходимо
добавить знак равенства (=) после
обозначения типа данных, а затем указать
применяемое по умолчанию значение.
Благодаря этому пользователи получают
возможность при вызове хранимой процедуры
принимать решение о том, следует ли
задать другое значение параметра или
воспользоваться значением, предусмотренным
по умолчанию.

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

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

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

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

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

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

Любая
вызываемая на выполнение хранимая
процедура возвращает значение, независимо
от того, предусмотрен ли в ней возврат
значения или нет. По умолчанию после
успешного завершения процедуры СУБД
SQL Server
автоматически возвращает значение,
равное нулю.

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

RETURN
[<Целое число>]

Возвращаемые
значения предназначены исключительно
для указания на успешное или неудачное
завершение хранимой процедуры и позволяют
даже обозначить степень или характер
успеха или неудачи. Использование
возвращаемого значения для возврата
фактических данных, таких как
идентификационное значение или данные
о количестве строк, затронутых хранимой
процедурой, рассматривается как
недопустимая практика программирования.
Возвращаемое значение 0 указывает на
успешное выполнение процедуры и
установлено по умолчанию. Компания
Microsoft
зарезервировала значения от -99 до -1 для
служебного пользования. Разработчикам
для возвращения состояния ошибки
пользователю рекомендуется использовать
значения -100 и меньше.

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

При
вызове хранимой процедуры, если ожидается
выходное значение, команда EXEC
должна использовать целочисленную
переменную:

EXEC
@локальная_переменная = имя_хранимой_процедуры;

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Примечание. Для тестовой базы данных рекомендуется использовать систему контейнеризации и контейнер с MySQL сервером. Большинство запросов, описанных в статье можно выполнять непосредственно в командной строке контейнера с небольшой поправкой: в MySQL нет директивы GO, а команды должны заканчиваться точкой с запятой, то есть символом «;». Или, как вариант, можно поставить контейнер с Microsoft SQL сервером. Подробности описаны в статье Использование Docker для MySQL сервера.

• Введение
• Определение
• Опытная база данных
• Элементы синтаксиса
  • Директивы сценария
  • Комментарии
  • Типы данных
  • Идентификаторы
  • Переменные
  • Операторы
  • Cистемные функции
  • Выражения
  • Управление выполнением сценария
  • Динамическое конструирование выражений
• Выборка данных
• Группировка данных
• Соединение таблиц
• Изменение данных
• Хранимые процедуры и функции
• Производительность

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 [Local Customers] (CustomerID int PRIMARY KEY,
 CustomerName nvarchar(40), CustomerAddress nvarchar(255))
CREATE TABLE [Local Orders] (OrderID int PRIMARY KEY,
 CustomerID int, UserID int, [Description] 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 [Local Customers] VALUES (1, 'Alex Company', '606443, Russia, Bor, Lenina str., 15')
INSERT [Local Customers] VALUES (2, 'Potrovka', '115516, Moscow, Promyshlennaya str., 1')
INSERT [Local Orders] 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.

Директивы сценария — это специфические команды, которые используются только в 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 [Employee Name]
GO

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

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

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

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

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

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

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

Пример:

USE TestDatabase
-- Использование агрегативной функции для подсчета средней зарплаты
SELECT AVG(BaseSalary) AS [Average salary] FROM Positions
GO
-- Использование скалярной функции для получения имени базы данных
SELECT DB_NAME() AS [Database name]
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 [User Full Name] 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 [Average salary] FROM ' + @tablename)
GO

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

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

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

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

Пример:

-- Выбрать все записи из таблицы Local Customers
SELECT * FROM [Local Customers]

CustomerID	CustomerName	CustomerAddress
1	Alex Company	606443, Russia, Bor, Lenina str., 15′)
2	Potrovka	115516, Moscow, Promyshlennaya str., 1

-- Выбрать записи колонки DepartmentName из таблицы Departments
-- и назвать результирующую колонку Department Name
SELECT DepartmentName AS 'Department Name' FROM Departments

Department Name
Production
Distribution
Purchasing

-- Выбрать уникальные записи колонки 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 [ALL или DISTINCT] <названия колонок или *> 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]%'

UserID	UserName	UserSurname	DepartmentID	PositionID
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

-- Выбрать записи колонок UserName и UserSurname из таблицы Users, у кого
-- PositionID между 2 и 3, результирующую колонку назвать Full name.
SELECT [UserName] + ' ' + [UserSurname] AS 'Full name' FROM Users
 WHERE PositionID BETWEEN 2 AND 3

Full name
Ivan Sidorov
Petr Ivanov
Nikolay Petrov
Sergey Sidorov
Andrey Bukin

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

-- Выбрать записи колонки PositionID из таблицы Positions, где BaseSalary < 600
SELECT PositionID FROM Positions WHERE BaseSalary < 600

PositionID
3

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

UserID	UserName	UserSurname	DepartmentID	PositionID
4	Nikolay	Petrov	1	3
6	Sergey	Sidorov	2	3

-- Выбрать все записи из таблицы Users, у кого имя Ivan или Andrey
SELECT * FROM Users WHERE UserName IN ('Ivan', 'Andrey')

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
7	Andrey	Bukin	2	2

-- Сосчитать суммарную зарплату отдела с идентификатором 1
DECLARE @DepID int
SET @DepID = 1
SELECT DepartmentName AS 'Department name',
   (SELECT SUM(Positions.BaseSalary) FROM Positions
      INNER JOIN Users ON Users.PositionID = Positions.PositionID
      WHERE Users.DepartmentID = @DepID
   ) AS 'Summary salary'
 FROM Departments
 WHERE DepartmentID = @DepID

Department name	Summary salary
Production	2700.0000

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

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

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

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

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

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

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

-- Выбрать 3 первые уникальные записи колонки UserName из таблицы Users,
-- отсортированных по возрастанию UserName
SELECT DISTINCT TOP 3 UserName FROM Users ORDER BY UserName

UserName
Andrey
Ivan
Nikolay

-- Выбрать 15 процентов строк из таблицы Users
SELECT TOP 15 PERCENT * FROM Users

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2

-- Найти величину максимального оклада в организации
SELECT MAX(BaseSalary) FROM Positions

(No column name)
1000.0000

-- Найти должности, у которых максимальный оклад в организации
	SELECT * FROM Positions
	 WHERE BaseSalary IN (SELECT MAX(BaseSalary) FROM Positions)

PositionID	PositionName	BaseSalary
1	Manager	1000.0000

-- Найти сотрудников, у кого максимальный оклад в организации  
SELECT * FROM Users
 WHERE PositionID IN (SELECT PositionID FROM Positions
 WHERE BaseSalary IN (SELECT MAX(BaseSalary) FROM Positions))

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
5	Nikolay	Ivanov	2	1
8	Viktor	Rybakov	4	1

-- Найти количество сотрудников, у кого максимальный оклад в организации  
SELECT COUNT(*) FROM Users
 WHERE PositionID IN (SELECT PositionID FROM Positions
 WHERE BaseSalary IN (SELECT MAX(BaseSalary) FROM Positions))

(No column name)
3

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

DepartmentID	Number of users
1	4

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

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

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

-- Найти количество работников с определенной должностью в каждом отделе
-- (сгруппировать работников по идентификатору должностей и отделов и
-- сосчитать количество записей в каждой группе), а также сосчитать
-- количество работников в каждом отделе и общее количество работников
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 [ALL или DISTINCT] <названия колонок или *> FROM <таблица_1>
	 INNER JOIN таблица_2 ON таблица_1.ключевое_поле = таблица_2.ключевое_поле

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

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

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

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

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

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

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

SELECT [ALL или DISTINCT] <названия колонок или *> 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 — команда сортировки требует упорядочивания строк таблицы вывода, что задерживает получение результата.

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

Лекция 14 Хранимый код. Триггеры

14.1. Процедурные расширения языка SQL

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

К сожалению, на настоящий момент ситуация такова, что каждая СУБД поддерживает свой собственный язык процедурного расширения SQL, что усложняет задачи переносимости программного обеспечения. Поэтому те примеры, которые будут приведены в этой лекции, используют процедурный язык PL/SQL, поддерживаемый Oracle, и работают только в среде этой СУБД. Из других процедурных расширений наиболее близок к PL/SQL язык СУБД PostgreSQL, который называется PLG/SQL. Используемое в Microsoft SQL Server процедурное расширение Transact-SQL по синтаксическим конструкциям отличается от PL/SQL, но по семантике является близким. Во всяком случае, понимание логики разработки хранимого кода на PL/SQL поможет легко освоить и любое другое процедурное расширение.

Для дальнейшего изложения вначале необходимо привести минимальные сведения по конструкциям языка PL/SQL. По синтаксису он наиболее близок языку программирования ADA, конструкции его очень логичны. Основной программной единицей является блок – совокупность операторов, заключенная в операторные скобки BEGIN … END. При выполнении процедурных действий в блоке, как правило, необходимы переменные для хранения промежуточных значений. Объявление переменных предшествует блоку и образует секцию объявления, которая начинается ключевым словом DECLARE. Для переменных поддерживаются те же типы, что и для столбцов таблиц Oracle. Имеется возможность указать тип переменной, явно ссылаясь на определенный столбец или таблицу. Например:

DECLARE

x INTEGER;

fio students.name_st%type;

s subjects%type;

BEGIN

. . .

END

В приведенном примере для переменной x тип указан явно, переменная fio имеет такой же тип, как столбец name_st таблицы students, переменная s имеет тип запись (RECORD), структура которой идентична строке таблицы subjects (содержит два поля cod_sub и name_sub).

Операторы языка PL/SQL

1. Оператор присваивания

переменная:= выражение;

2. Условный оператор

IF условие THEN

оператор1; оператор2;

…

[ELSIF условие THEN

оператор3; оператор4;

…]

[ELSE

оператор5; оператор6;

…]

END IF;

3. Операторы цикла

Бесконечный цикл, условие выхода задается в теле цикла

LOOP

оператор1; оператор2;

…

EXIT WHEN условие выхода из цикла;

END LOOP;

Цикл с предусловием

WHILE условие LOOP

оператор1; оператор2;

…

END LOOP;

Цикл с параметром

FOR параметр IN (множество значений) LOOP

оператор1; оператор2; …

END LOOP;

Множество значений параметра обычно задается в виде диапазона

(начальное_значение .. конечное_значение)

5. Оператор безусловного перехода:

GOTO метка;

…

метка: оператор;
6. Оператор возврата из процедуры/функции

RETURN;

RETURN выражение; (только функции)

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

Однострочный комментарий обозначается так:

— далее следует текст комментария до конца строки

Многострочный комментарий обозначается аналогично языку С:

/* текст комментария может располагаться где угодно и занимать сколько угодно строк */

8. Средства для обработки исключительных ситуаций

Общий подход к обработке исключительных ситуаций состоит в том, что для каждой ситуации определяется ее обработчик и при возникновении ситуации выполняется код обработчика. В PL/SQL предусмотрена возможность как обрабатывать стандартные ситуации, так и вводить собственные исключительные ситуации. Предусматриваются также средства генерации исключительных ситуаций.

Для стандартных исключительных ситуаций существует большое число предустановленных имен. Пользовательская исключительная ситуация вводится объявлением переменной типа EXCEPTION. Генерация исключений из программы выполняется в PL/SQL оператором RAISE.

Обработчик исключений в PL/SQL, общий для всех исключений, составляет отдельную часть блока PL/SQL, начинающуюся со слова EXCEPTION и содержащую набор операторов WHEN, распознающих типы исключений и задающих действия, выполняемые по каждому типу (возможны любые действия, которые можно запрограммировать средствами PL/SQL).

Например:

DECLARE

err EXCEPTION;

BEGIN

INSERT INTO … VALUES … ;

SELECT … INTO …;

IF … THEN

RAISE err;

…

EXCEPTION

WHEN DUPLICATE_KEYS THEN

…

WHEN TOO_MANY_ROWS THEN

…

WHEN err THEN

…

WHEN OTHERS THEN

…

END;

В приведенном примере имеется два предопределенных имени для стандартных исключительных ситуаций, возникающих в процессе выполнения команд INSERT и SELECT …INTO и предопределенное имя OTHERS, предназначенное для обозначения любой другой исключительной ситуации, которую процедура отдельно не обрабатывает. Переменная err предназначена для возбуждения пользовательской исключительной ситуации при помощи оператора RAISE.

Как видим из приведенного примера, блок PL/SQL может содержать команды языка SQL, которые органично сочетаются с операторами языка высокого уровня. Эта тема заслуживает отдельного обсуждения, поскольку механизм встраивания команд в язык высокого регламентируется стандартом SQL и практически одинаков во всех СУБД.

14.2. Использование команд SQL в процедурном расширении. Курсоры

Команды INSERT, DELETE и UPDATE используются в программе на PL/SQL в качестве отдельных операторов наряду с другими операторами языка. В данных командах разрешено использовать переменные программы везде, где по правилам SQL используются константы, что делает данные команды более гибкими, чем при их использовании в интерактивном режиме. Для обработки исключительных ситуаций, которые могут возникнуть в случае, когда какая-либо из этих команд нарушает целостность данных, существует большое количество стандартных предопределенных имен. Например, приведенная в предыдущем примере ситуация DUPLICATE_KEYS возникает при нарушении ограничения уникальности (и в первичном ключе в том числе).

Проблема возникает при встраивании в процедурный язык оператора SELECT. Результатом оператора SELECT является множество строк, а процедурный язык ориентирован в основном на обработку последовательностей. Для преодоления этого противоречия в стандарт SQL введен механизм курсора, который реализован и в PL/SQL Oracle. Курсор представляет собой результат выборки из базы данных, который предназначен для дальнейшей построчной обработки.

Различают неявный и явный курсоры. Неявный курсор можно использовать только в том случае, если запрос на выборку возврашает ровно одну строку. Тогда этот результат можно поместить в обычные переменные, используя расширенный синтаксис команды SELECT:

SELECT список_выражений INTO список_переменных … остальная часть оператора SELECT

Количество переменных в списке и их типы должны в точности соответствовать списку выражений оператора SELECT.

SELECT mark INTO m FROM marks WHERE cod_st=c_st AND cod_sub=c_s

Значения переменных c_st и c_s задаются заранее. Если существуют студент и предмет с такими значениями кодов, запрос вернет ровно одно значение оценки и разместит его в переменной с именем m.

При выполнении команды SELECT … INTO … в различных случаях ее применения могут возникнуть две разные исключительные ситуации:

• TOO_MANY_ROWS возникает в том случае, если запрос SELECT вместо одной строки возвращает несколько строк (в этом случае возвращаемые данные невозможно разместить в заданном списке переменных)
• NO_DATA_FOUND возникает в том случае, если запрос SELECT вообще не возвращает данных. Тогда переменные в списке не могут получить никаких значений.

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

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

DECLARE CURSOR имя_курсора IS SELECT …далее идет запрос на выборку

Например:

DECLARE CURSOR cur IS

SELECT name_st FROM students WHERE name_st LIKE ‘A%’

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

имя_курсора CURSOR FOR SELECT ….

Все остальные операции с курсором соответствуют стандарту.

Объявление курсора не является выполнимым оператором. Выборка, заданная в объявлении курсора, выполняется только при его открытии.

Например:

OPEN CURSOR cur

После открытия курсора можно последовательно выбирать строки курсора, используя оператор FETCH. Например:

FETCH cur INTO fio

Переменная fio должна быть предварительно объявлена, например, так:

fio students.name_st%type;

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

LOOP

FETCH cur INTO fio;

…

EXIT WHEN NOT cur%FOUND;

END LOOP;

или

FETCH cur INTO fio;

WHILE cur%FOUND LOOP

FETCH cur INTO fio;

…

END LOOP;

После того, как выбраны все нужные строки, курсор должен быть закрыт. Например:

CLOSE cur

Цикл по курсору

Некоторые СУБД, в том числе Oracle, поддерживают цикл с параметром по курсору;

FOR параметр IN имя_курсора LOOP

…

END LOOP;

Использование такого цикла не требует операций открытия и закрытия курсора – они выполняются неявно. Параметр цикла не требуется объявлять в секции DECLARE, его тип определяется автоматически как RECORD, а имена полей записи соответствуют именам в объявлении курсора.

Например:

FOR cur_rec IN cur LOOP

… cur_rec.name_st…

END LOOP;

Из этих объяснений понятно, что использование цикла по курсору – очень простой и удобный способ обработки курсора.

Приведенных сведений уже достаточно, чтобы перейти к практическому применению языка PL/SQL. Его основное назначение – разработка хранимых процедур и функций, а также триггеров базы данных.

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

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

Преимущества и недостатки хранимого кода

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

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

Основным недостатком хранимого кода является его непереносимость между различными СУБД. В силу этого, для масштабируемых информационных систем или систем, предназначенных для многократного тиражирования, часто используется трехзвенная архитектура системы с переносом значительной части бизнес-логики на уровень сервера приложений. Практикуется и такой вариант: серверная часть тиражируемой информационной системы разрабатывается сразу для нескольких самых распространенных СУБД (например, вариант для Oracle, PostgreSQL и Microsoft SQL Server).

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

Создание хранимых процедур и функций

В Oracle традиционно основным языком хранимых процедур является язык PL/SQL, но поддерживаются и процедуры на других языках, прежде всего — на языках C++ и Java. В последнем случае хранимая процедура или функция называется внешней. В рамках нашего курса рассмотрим основной вариант – хранимая процедура на PL/SQL.

Хранимая процедура создается оператором SQL

CREATE [OR REPLACE] PROCEDURE имя[(список_параметров)]

AS

блок PL/SQL

Необязательная конструкция OR REPLACE позволяет заменять процедуру с таким же именем. Это очень удобно в процессе отладки.

Аналогично создается хранимая функция:

CREATE [OR REPLACE] FUNCTION имя[(список_параметров)]

RETURN тип_результата, возвращаемого функцией

AS

блок PL/SQL, обязательно содержащий оператор

RETURN выражение

В списке параметров должен быть описан режим использования каждого параметра: IN (только входной – используется по умолчанию), OUT (только выходной), INOUT (и входной, и выходной). Режим использования указывается после имени параметра. Типы параметров, как и типы переменных, можно указывать явно или с помощью ссылки на соответствующий столбец или таблицу.

При описании локальных переменных подпрограммы разрешено опускать ключевое слово DECLARE.

Удалить процедуру или функцию можно при помощи команды DROP.

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

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

Первая из процедур демонстрирует применение неявного курсора и предназначена для изменения телефона студента.

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

Для фиксации всех перечисленных выше случаев в процедуру добавлен выходной параметр result, который после завершения процедуры возвращает код ошибки (0 – благополучное завершение процедуры) и может быть обработан клиентским приложением.

create procedure changephone(fiostud students.name_st%type,

newphone students.phone%type,

result out number)

as

oldphone students.phone%type; — старый телефон

begin

select phone into oldphone from students where name_st=fiostud;

if oldphone!=newphone then

update students set phone=newphone where name_st=fiostud;

result:=0;

else

result:=1; — старый и новый номера совпали

end if;

exception

when NO_DATA_FOUND then — нет такого студента

result:=2;

when TOO_MANY_ROWS then

result:=3; — есть однофамильцы

when OtherS then

result:=4; — непредвиденная исключительная ситуация

end;

Выполнив команду создания данной процедурв в SQL*Plus, мы получим сообщение «Процедура создана». В случае, если в процедуре обнаружены синтаксические ошибки, выдается другое сообщение «Процедура создана с ошибками компиляции». Получить информацию об обнаруженных ошибках можно с помощью запроса к представлению словаря Oracle user_errors:

SELECT line, text FROM user_errors

Хранимая процедура запускается на выполнение по команде из клиентского приложения. Чтобы запустить ее на выполнение из SQL*Plus, необходимо поместить ее в блок PL/SQL, перед которым объявить переменную для выходного параметра:

var e number;

begin

changephone(‘Иванов’, ‘555555’, :e);

end;

Проверить значение переменной е можно при помощи команды:
PRINT e

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

CREATE Function getphone (fiostud students.name_st%type)

RETURN varchar

as

cursor c is

select phone from students

where name_st= fiostud; — телефоны всех студентов с заданной фамилией

res varchar(50); — строка результата

ph students.phone%type; — переменная для команды fetch

BEGIN

open c;

res:=»;

loop – цикл для извлечения данных из курсора

fetch c into ph;

EXIT WHEN NOT c%found;

res:=res||ph||‘ ‘;

end loop;

RETURN res ;

END;

Если использовать цикл по курсору, тело функции получится короче:

BEGIN

res:=»;

for crec in c loop

res:=res||crec.phone||’ ‘;

end loop;

RETURN res ;

END;

После создания функции проверить ее работоспособность можно совсем просто:

SELECT getphone(‘Иванов’) FROM dual

14.4. Триггеры

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

Особенности триггеров

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

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

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

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

• уникальное имя триггера (задание параметров не требуется, поскольку триггер – процедура без параметров);
• активизирующее действие — команда, которая вызывает запуск триггера, такими командами являются INSERT, DELETE, UPDATE;
• время активизации — выполнение триггера до (BEFORE) или после (AFTER) выполнения активизирующего действия;
• область действия — выполнение триггера либо один раз для каждого оператора модификации таблицы, либо для каждой строки (в последнем случае следует добавить фразу FOR EACH ROW);
• условие активизации — необязательное дополнительное условие, которое должно выполняться для запуска триггера (фраза WHEN);
• тело триггера – действия, выполняемые триггером (блок PL/SQL).

На каждое событие может быть создано и несколько триггеров. Однотипные триггеры выполняются в порядке их создания.

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

Команды SQL для работы с триггерами

Триггер создается при помощи команды SQL:

CREATE [OR REPLACE] TRIGGER имя_триггера

время_активизации активизирующая_команда ON имя_таблицы

[FOR EACH ROW]

[WHEN дополнительное условие запуска триггера]

AS

Блок PL/SQL

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

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

:new – новое значение строки, применяется для команд INSERT и UPDATE

:old – старое значение строки (до модификации), применяется для команд DELETE и UPDATE

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

DROP TRIGGER имя_триггера

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

Для временного отключения триггера в Oracle можно применить команду:

ALTER TRIGGER имя_триггера DISABLE

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

ALTER TRIGGER имя_триггера ENABLE

Примеры триггеров

1. Триггер на вставку нового студента

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

Вместо этого имеется специальный объект SEQUENCE, который предназначен для формирования последовательных целых чисел. Для обращения к значениям последовательности в выражении SQL используются псевдостолбцы currval и nextval. Currval возвращает текущее значение. NextVal инкрементирует текущее значение и возвращает результат, при этом он становится текущим значением. Триггер на вставку берет из последовательности очередное значение и помещает его в новую строку, используя предопределенную переменную :new.

Например, создадим последовательность для формирования кодов студентов:

CREATE SEQUENCE stud_seq

Теперь создадим триггер на вставку новой строки в таблицу students:

create trigger st_keys

before insert on students

for each row

begin

select stud_seq.nextval into :new.cod_st from dual;

end;
Аналогичный триггер можно написать и на таблицу subjects, поскольку при добавлении нового предмета его код должен формироваться также автоматически.

2. Триггеры на удаление студента

Триггер на удаление должен предусмотреть перенос удаляемых данных в архивную базу данных. При создании таблиц нашей базы данных вместе с удалением студента было предусмотрено и каскадное удаление оценок студента, поэтому мы можем написать аналогичные триггеры BEFORE DELETE на таблицы students и marks, используя возможность каскадирования триггеров. При удалении студента одна единственная команда удаления, полученная сервером, например:

DELETE FROM students WHERE cod_st=125

вызовет выполнение двух команд удаления (из таблиц marks и students) вместе с двумя триггерами, сохраняющими удаляемые данные в архиве.

Предположим, что уже созданы таблицы archive_students и archive_marks. Создадим триггер на удаление из таблицы students:

create trigger st_del

before DELetE on students

for each row

begin

INSERT INTO archive_students

VALUES(:OLD.cod_st, :OLD.name_st, :OLD.born, :OLD.phone);

end;

Триггер на удаление из таблицы оценок выглядит аналогично, фраза

for each row

вызовет его срабатывание при удалении каждой оценки студента.

3. Триггер на изменение оценки

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

В нашей демонстрационной базе данных, очевидно, имеет смысл контролировать изменение уже выставленной оценки. Поэтому создадим специальную таблицу change_mark_log (журнал изменений оценок), которая будет содержать столбы:

• name_user (имя пользователя, изменившего оценку)
• date_change (дата изменения оценки)
• cod_st (код студента)
• cod_sub (код предмета)
• old_mark (старая оценка)
• new_mark (новая оценка)

Теперь создадим триггер на обновление:

create trigger mark_change

AFTER UPDATE on marks (mark)

for each row

begin

IF :OLD.mark<> :NEW.mark THEN

INSERT INTO change_mark_log

VALUES(user, sysdate, :OLD.cod_st, :OLD.cod_sub, :OLD.mark, :NEW.mark);

END IF;

end;

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

UPDATE marks SET mark=3 WHERE mark=2

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

1. SQL

часть II

2. Хранимые процедуры

Хранимая процедура – это
последовательность
компилированных операторов
Transact-SQL, выполняемых в виде
пакета и хранящихся в системной
базе данных SQL Server.

3. Хранимые процедуры

— это набор операторов SQL и другие
логические конструкции, например
операторы If и WHERE.
— Этот набор операторов SQL-сервер компилирует в
единый план выполнения. Этот план выполнения
сохраняется в КЭШе процедур при первом выполнении
хранимой процедуры. Затем этот план можно повторно
использовать без рекомпиляции при каждом вызове
процедуры.

4. Хранимые процедуры

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

5. Применение хранимых процедур

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

6. Хранимые процедуры

• могут содержать как входные, так и
выходные параметры;
• могут содержать инструкции,
которые управляют потоком кода,
например IF и WHILE

7. Первоначальное выполнение хранимой процедуры:

1. Лексический анализ разбивает ХП на отдельные компоненты.
2. Разрешение ссылок – компоненты,ссылающиеся на объекты
БД, сопоставляются с этими объектами.
3. В таблице syscomments сохраняется исходный текст ХП, а в
sysobjects – ее название.
4. Создается предварительный план выполнения запроса
(нормализованный план или дерево запроса) и сохраняется в
sysprocedures.

8.

5. Дерево запроса считывается и окончательно
оптимизируется.
При последующих вызовах ХП выполняется только шаг 5.
ХП могут принимать аргументы при запуске и возвращать
значения.
План выполнения ХП после первого выполнения хранится
в быстродействующем процедурном кэше.

9. Преимущества использования ХП

• Выполняются быстрее, чем последовательность отдельных
операторов;
• Необходимые для выполнения операторы уже содержатся в
БД;
• Они находятся в исполняемом формате;
• Поддерживается модульное программирование;

10. Преимущества использования ХП (продолжение):

• Могут вызывать другие ХП и функции;
• Могут быть вызваны из прикладных программ других типов;
• Уменьшается нагрузка на сеть, т.к. для запуска достаточно
передать иногда только имя ХП.

11. Создание и запуск ХП.

• CREATE PROC имя-процедуры
[параметры]
AS
Запрос SQL
• Запуск
EXEC имя-процедуры
[параметры]

12. Пример 1 – процедура без параметров

Вывести, когда и какой предмет сдавал студент Иванов ВВ.
CREATE PROC my_proc1
AS
SELECT Предмет.Название,Ведомость.Дата
FROM Студенты INNER JOIN (Предметы INNER JOIN
Ведомость ON
Предметы.КодПредмета=Ведомость.КодПредмета) ON
Студент.НомерЗачетки=Ведомость.НомерЗачетки
WHERE Студент.ФИО=‘Иванов ВВ’;
Запуск:
EXEC my_proc1

13. Пример 2 – процедура с входным параметром

Тоже, только для любого студента
CREATE PROC my_proc2
@F varchar(20)
AS
SELECT Предмет.Название,Ведомость.Дата
FROM Студенты INNER JOIN (Предметы INNER JOIN
Ведомость ON
Предметы.КодПредмета=Ведомость.КодПредмета) ON
Студент.НомерЗачетки=Ведомость.НомерЗачетки
WHERE Студент.ФИО= @F;
Запуск:
EXEC my_proc2 ‘Сидоров ВВ’ или
EXEC my_proc2 @F= ‘Сидоров ВВ’

14. Пример 3 – процедура с входными параметрами

Для некоторого студента снизить оплату на 5%.
CREATE PROC my_proc3
@N varchar(5), @P float
AS
UPDATE Оплата
SET Сумма= Сумма*(1- @P )
WHERE Оплата.НомерЗачетки=@N;
Запуск:
EXEC my_proc3 ‘ИС230’, 0.05 или
EXEC my_proc3 @N= ‘ИС230’, @P= 0.05

15. Пример 4 – процедура с входными параметрами и значениями по умолчанию

CREATE PROC my_proc4
@N varchar(5)= ‘ИС230’,
@P float=0.05
AS
UPDATE Оплата
SET Сумма= Сумма*(1- @P )
WHERE Оплата.НомерЗачетки=@N;
Запуск:
EXEC my_proc4 ‘ИС450’, 0.1 или
EXEC my_proc4 @N=‘ИС450’, @P = 0.1
EXEC my_proc4 @P = 0.1
EXEC my_proc4

16. Пример 5 – процедура с входными и выходными параметрами

Вывести общую сумму, уплаченную студентами за конкретный
месяц
CREATE PROC my_proc5
@m int,
@S float output
AS
SELECT @S=SUM(Оплата.Сумма)
FROM Оплата
GROUP BY Month(Оплата.Дата)
HAVING Month(Оплата.Дата)= @m;
Запуск:
DECLARE @SS float
EXEC my_proc5 10, @SS output
SELECT @SS

17. Для чего в основном используют хранимые процедуры?

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

18. Для чего в основном используют хранимые процедуры?

Для чего в основном используют хранимые
• Для упрощения управления
изменениями. Если таблицы,
процедуры?
имена столбцов, деловые правила (или что-то подобное)
изменяются, обновлять приходиться только код хранимой
процедуры и ничего больше.
• Побочным эффектом этого является повышение
безопасности.
• Ограничение доступа к основным данным только через
хранимые процедуры снижает вероятность повреждения
данных (случайного или преднамеренного).
• Поскольку хранимые процедуры обычно сохраняются в
компилированном виде, СУБД тратит меньше времени на
обработку их команд. Это приводит к повышению
производительности.

19. Для чего в основном используют хранимые процедуры?

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

20.

• Использование хранимых процедур для всех
повторяющихся действий в базе данных
является хорошим стилем программирования.

21. Триггеры

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

22. Триггер

• всегда связан с конкретной таблицей и
• выполняется при совершении над ней операций
INSERT, UPDATE, DELETE или их комбинаций, с
которыми он должен быть связан;
• таблица может иметь любое количество любых
триггеров.

23. Триггеры

• Все производимые Т модификации рассматриваются как
одна транзакция (срабатывает как транзакция).
• Создает Т только владелец БД.
• При выполнении команд модификации данных сервер
создает две таблицы:inserted и deleted, где хранятся списки
соответствующих строк. Они связаны только с Т их
создавшим, никакой другой Т не имеет к ним доступа.

24. Триггеры чаще всего используются для:

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

25. Триггеры чаще всего используются для:

• Для выполнения дополнительной проверки и отмены
введения данных (например, чтобы удостоверится, что
разрешенная для клиента сумма кредита не превышена, в
противном случае операция блокируется);
• Для подсчета значений вычисляемых полей или
обновления меток даты/времени.

26.

• В разных коммерческих СУБД рассматриваются разные
триггеры. Так, в MS SQL Server триггеры определены
только как постфильтры, то есть такие триггеры,
которые выполняются после свершения события.
• В СУБД Oracle определены два типа триггеров:
триггеры, которые могут быть запущены перед
реализацией операции модификации, они называются
BEFORE-триггерами, и триггеры, которые
активизируются после выполнения соответствующей
модификации, аналогично триггерам MS SQL Server, —
они называются AFTER-тригерами.

27.

• Триггеры могут быть эффективно
использованы для поддержки семантической
целостности БД, однако приоритет их ниже,
чем приоритет правил-ограничений
(constraints), задаваемых на уровне описания
таблиц и на уровне связей между таблицами.
При написании триггеров всегда надо помнить
об этом, при нарушении правил целостности
по связям (DRI declarative Referential Integrity)
триггер просто может никогда не сработать.

28. Предложения SQL для триггеров

• CREATE TRIGGER создает триггер
• ALTER TRIGGER изменяет триггер
• DROP TRIGGER удаляет триггер

29. Создание триггера

CREATE TRIGGER имя_триггера
ON имя таблицы или представления
FOR {INSERT, UPDATE, DELETE}
[WITH ENCRYPTION]
AS оператор_SQL;
Необязательный параметр WITH ENCRYPTION не
позволяет увидеть код триггера в таблице syscomments

30.

• По умолчанию триггер выполняется после
изменения данных, но если указать параметр
INSEAD OF, то создается триггер,
выполняющийся вместо изменения данных.

31. Изменение триггера

• ALTER TRIGGER имеет тот же
синтаксис, что и CREATE TRIGGER, и
применяется для изменения текста имеющегося
триггера, например, если мы не хотим, чтобы
триггер реагировал на выполнение обновления,
следует из его текста в предложении ALTER
TRIGGER удалить UPDATE

32. Удаление триггера

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

33. Триггеры

По умолчанию триггер выполняется после изменения
данных, но если указать параметр
INSTEAD
OF, то такой триггер будет выполняться вместо
изменения данных.
В Т можно использовать функцию @@ROWCOUNT,
которая возвращает количество строк обработанных
последней командой.

34. Пример 1

Создать триггер, который будет при каждом добавлении или
изменении данных таблицы «Преподаватели» возвращать
сообщение о количестве измененных записей.
CREATE TRIGGER tr_ПРЕП
ON Преподаватели
FOR INSERT, UPDATE
AS raiserror (‘Произведено изменений
таблицы’,0,1 @@rowcount)
RETURN

35. Запуск

INSERT (Табельный Номер, ФИО, Должность)
VALUES (’33п28’,’Иванов ВВ’,’доцент’)

36. Пример 2

Триггер срабатывает при удалении экземпляра некоторой книги,
например, в случае утери. Он проверяет остался ли хотя бы один
экземпляр этой книги, если нет, то книга удаляется из каталога.
CREATE TRIGGER DEL_EXEMP ON Экземпляры
FOR DELETE
AS
DECLARE @Ntek int (кол-во оставшихся экземпляров книги)
DECLARE @DEL_EX varchar(12) (шифр удаленного экземпляра)
BEGIN
SELECT @DEL_EX=ISBN FROM deleted
EXEC @Ntek = COUNT_EX @DEL_EX (вызов ХП, определяющей кол-во экземпляров книги с зад.
шифром)
IF @Ntek =0 DELETE FROM Книги WHERE Книги.ISBN=
@DEL_EX END
GO

37. Транзакция

• — это последовательность операций, производимых
над БД, рассматриваемая СУБД как единое целое, и
переводящая ее из одного непротиворечивого
(согласованного) состояния в другое
непротиворечивое (согласованное) состояние.
• Каждая Т начинается при целостном состоянии БД
и сохраняет это состояние после своего
завершения.

38. Транзакция

Инициализация Т м.б. вызвана как пользователем
так и прикладной программой.
Т особенно важны для многопользовательских
СУБД.

39. Проектирование транзакции заключается в определении:

• данных, используемых Т,
• функциональных характеристик Т,
• выходных данных, формируемых Т,
• степени важности и интенсивности
использования.

40. Свойства транзакций

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

41. Свойства транзакций

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

42. ФИКСАЦИЯ ИЛИ ОТКАТ?

• Если все операторы выполнены успешно и не произошло
никаких сбоев программного или аппаратного
обеспечения, то ФИКСАЦИЯ, т.е. все результаты
транзакции становятся постоянными.
• Если в процессе выполнения транзакции случилось нечто,
что ее нормальное завершение становится невозможным,
то БД возвращается в свое исходное состояние, т.е.
ОТКАТ.

43. Модель транзакций

Стандартом ANSI/ISO SQL определена модель
транзакций и функции операторов
COMMIT — фиксация
ROLLBACK — откат

44. Транзакция завершается одним из 4-х возможных вариантов:

1. Оператор COMMIT означает успешное завершение
транзакции; его использование делает постоянными
изменения, внесенные в БД в текущей транзакции.
2. Оператор ROLLBACK прерывает транзакцию, отменяя
изменения, сделанные в БД в рамках этой транзакции;
новая транзакция начинается непосредственно после
использования ROLLBACK.

45. Транзакция завершается одним из 4-х возможных вариантов:

3. Успешное завершение программы, в которой была
инициирована текущая транзакция, означает успешное
ее завершение (как будто был использован оператор
COMMIT).
4. Ошибочное завершение программы прерывает
транзакцию (как будто был использован оператор
ROLLBACK).

46. ПРИМЕР

В таблице Ведомость изменить значение поля Оценка на 0, если
записано значение NULL.
BEGIN TRAN update_оценка
UPDATE Ведомость SET оценка=0 WHERE оценка IS
NULL
COMMIT TRAN update_оценка
GO

47. Журнал транзакций

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

48. Журнал транзакций (продолжение)

Используется стратегия «упреждающей» записи (протокол WAL) –
запись об изменении любого объекта БД должна попасть во
внешнюю память журнала раньше, чем измененный объект
попадет во внешнюю память основной части БД.

49. Проблемы параллельного выполнения транзакций.

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

50. Феномен «грязное чтение»

51. Феномен фантомов

52. Феномен неповторяемого чтения

53. Как бороться с проблемами? Сериализация транзакций.

Выработать процедуру согласованного
выполнения Т:
• В ходе выполнения Т пользователь видит
только согласованные данные,
• При параллельном выполнении 2-х Т
СУБД гарантирует принцип независимого
выполнения Т

54. Сериализация транзакций.

Самый популярный механизм
реализации сериализации Т –
механизм блокировок.

55. Блокировки

Чтобы запретить нескольким пользователям одновременно
изменять данные в базе и считывать «грязные» данные
используется блокировка.
Блокирование обеспечивает логическую целостность
транзакций и данных.
Уровни гранулярности — блокируемые ресурсы включают
запись данных, страницу данных, экстент, таблицу или всю
БД. Самый низкий уровень гранулярности – блокировка
записи.

56. Режимы блокировок:

Разделяемый
Обновления
Монопольный
Намерения
Схемы
Массового обновления

57. Разделяемая блокировка

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

58. Блокировка обновления

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

59. Блокировка намерения Монопольная блокировка

Применяется для модификации данных. При
этом никакая другая транзакция не может
читать или изменять этот ресурс.

60. Блокировка намерения

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

61. Блокировка массового обновления

Применяется при копировании больших объемов
данных в таблицу с указанием TABLOCK.

62. Индексы

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

63. Файлы

• Индексированный файл – это основной файл,
содержащий данные отношения, для которого создан
индексный файл.
• Индексный файл – это файл, в котором каждая
запись состоит из двух значений: данных поля по
которому индексировали и указателя, который
связывает с соответствующим кортежем
индексированного файла

64. Индекс

• представляет собой физический объект базы
данных, имеющий структуру b-дерева и
используемый оптимизатором для ускорения
доступа к данным по сравнению с линейным
просмотром таблиц. Дополнительное
преимущество индексов заключается в том, что
они используются для обеспечения
уникальности ключа, — это означает, что
конкретные значения ключа в столбце не
должны повторяться. В SQL Server существуют
индексы двух типов: кластерные и
некластерные.

65. Кластерный индекс

• Создание кластерного индекса приводит к физической сортировке
данных.
• У каждой таблицы может быть лишь один кластерный индекс.
• Один элемент индекса соответствует странице данных. Страница
представляет собой физический блок для хранения данных объемом 8
Кбайт. После того как сервер определит страницу (посредством
перебора индекса или просмотра таблицы), он находит на странице
нужную запись.
• Кластерный индекс позволяет выйти на первую запись результата и
организовать последующий перебор таблицы (вместо повторного
перемещения по индексу) в поисках остальных записей без возврата
на уровень индекса, кластерные индексы отлично подходят для
выборки интервалов данных. К этой категории относятся внешние
ключи (объединения), списки имен и любые другие совокупности
данных с последовательными ключами.

66. Некластерные индексы

• позволяют индексировать данные на основании значения ключа в
каждой записи.
• SQL Server позволяет создать до 249 некластерных индексов.
• Один элемент индекса приходится на одну запись. Когда запись
будет идентифицирована, к ней происходит непосредственное
обращение. Каждой записи в таблице назначается идентификатор.
Поскольку в некластерном индексе каждый указатель соответствует
одной записи, а также потому, что их указатели имеют больший
размер, чем у кластерных индексов (поскольку содержат
идентификатор записи, а не просто идентификатор страницы),
некластерные индексы обычно занимают намного больше места.

67. Полнотекстовый индекс

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

68. Индексы (создание)

CREATE INDEX имя_индекса
ON имя_таблицы (столбцы для индексации)

69. Индексы. Что следует учитывать:

• Индексы повышают производительность операций
выборки, но ухудшают производительность операцийдействий;
• Для хранения данных индекса требуется много места на
диске;
• Не все данные подходят для индексации. Данные,
неявляющиеся по своей сути уникальными (ГРУППА а
таблице СТУДЕНТ), не дадут большого эффекта;

70. Индексы. Что следует учитывать:

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