Базы данных Transact-SQL
Базы данных и спецификации SQL. Назначение языка Transact-SQL и типы данных. Запросы на выборку данных, вставку, удаление и изменение данных. Использование курсоров. Расширения Transact-SQL. Триггеры. Хранимые процедуры. Элементы синтаксиса программы.
| Рубрика | Программирование, компьютеры и кибернетика |
| Вид | контрольная работа |
| Язык | русский |
| Дата добавления | 05.05.2013 |
| Размер файла | 35,7 K |
Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
Размещено на http://www.allbest.ru/
Введение
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
1. Назначение языка TransactSQL и типы данных
Transact-SQL (T-SQL) - процедурное расширение языка SQL компанией Microsoft (для Microsoft SQL Server) и Sybase (для Sybase ASE).
SQL был расширен такими дополнительными возможностями как:
управляющие операторы,
локальные и глобальные переменные,
различные дополнительные функции для обработки строк, дат, математики и т.п.,
поддержка аутентификации Microsoft Windows
Язык Transact-SQL является ключом к использованию MS SQL Server. Все приложения, взаимодействующие с экземпляром MS SQL Server, независимо от их реализации и пользовательского интерфейса, отправляют серверу инструкции Transact-SQL.
В SQL Server у каждого столбца, локальной переменной, выражения и параметра есть определенный тип данных. Тип данных - атрибут, определяющий, какого рода данные могут храниться в объекте: целые числа, символы, данные денежного типа, метки времени и даты, двоичные строки и так далее.
SQL Server предоставляет набор системных типов данных, определяющих все типы данных, которые могут использоваться в нем. Можно также определять собственные типы данных в Transact-SQL или Microsoft.NET Framework. Псевдонимы типов данных основываются на системных типах.
Как и в языках программирования, в 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).
Точность, масштаб и длина результата зависят от точности, масштаба и длины входных выражений. Типы данных в SQL Server объединены в следующие категории.
|
Точные числа |
Символьные строки в Юникоде |
|
|
Приблизительные числа |
Двоичные данные |
|
|
Дата и время |
Прочие типы данных |
|
|
Символьные строки |
Для использования русских символов (не ASCII кодировки) используются типы данных с приставкой «n» (nchar, nvarchar, ntext), которые кодируют символы двумя байтами. Иначе говоря, для работы с Unicode используются типы данных с «n».
Для данных переменной длины используются типы данных с приставкой «var». Типы данных без приставки «var» имеют фиксированную длину области памяти, неиспользованная часть которой заполняется пробелами или нулями.
Запросы на выборку данных, вставку, удаление и изменение данных в TransactSQL.
В языках SQL выборка данных из таблиц осуществляется с помощью команды SELECT:
SELECT [ALL или DISTINCT] <названия колонок или *> FROM <название таблицы>
По умолчанию в команде SELECT используется параметр ALL, который можно не указывать. Если в команде указать параметр DISTINCT, то в результат попадут только уникальные (неповторяющиеся) записи из выборки.
Для того чтобы изменить имена объектов в командах к SQL-серверу, используется команда AS. Использование этой команды помогает сокращать длину строки запроса, а так же получать результат в более удобочитаемом виде.
Для сортировки данных в выборке используется команда ORDER BY, но следует учесть, что эта команда не сортирует данные типа text, ntext и image. По умолчанию сортировка производится по возрастанию, поэтому параметр ASC в этом случае можно не указывать:
Для того чтобы ограничить количество строк в результате запроса, используется команда TOP:
Внутри запроса можно проводить вычисления над полученными данными. Для этого используются функции агрегирования:
AVG(колонка) - среднее значение колонки;
COUNT(колонка) - количество не NULL элементов колонки;
COUNT(*) - количество элементов запроса;
MAX(колонка) - максимальное значение в колонке;
MIN(колонка) - минимальное значение в колонке;
SUM(колонка) - сумма значений в колонке.
2. Курсоры. Использование курсоров
Курсоры подразделяются на типы: статические, динамические, последовательные и ключевые курсоры. Принципы управления курсором: создание и открытие курсора, считывание данных, закрытие курсора.
Запрос к реляционной базе данных обычно возвращает несколько рядов (записей) данных, но приложение за один раз обрабатывает лишь одну запись. Даже если оно имеет дело одновременно с несколькими рядами (например, выводит данные в форме электронных таблиц), их количество по-прежнему ограничено. Кроме того, при модификации, удалении или добавлении данных рабочей единицей является ряд. В этой ситуации на первый план выступает концепция курсора, и в таком контексте курсор - указатель на ряд.
Курсор в SQL - это область в памяти базы данных, которая предназначена для хранения последнего оператора SQL. Если текущий оператор - запрос к базе данных, в памяти сохраняется и строка данных запроса, называемая текущим значением, или текущей строкой курсора. Указанная область в памяти поименована и доступна для прикладных программ.
Обычно курсоры используются для выбора из базы данных некоторого подмножества хранимой в ней информации. В каждый момент времени прикладной программой может быть проверена одна строка курсора. Курсоры часто применяются в операторах SQL, встроенных в написанные на языках процедурного типа прикладные программы. Некоторые из них неявно создаются сервером базы данных, в то время как другие определяются программистами.
В соответствии со стандартом SQL при работе с курсорами можно выделить следующие основные действия:
создание или объявление курсора;
открытие курсора, т.е. наполнение его данными, которые сохраняются в многоуровневой памяти;
выборка из курсора и изменение с его помощью строк данных;
закрытие курсора, после чего он становится недоступным для пользовательских программ;
освобождение курсора, т.е. удаление курсора как объекта, поскольку его закрытие необязательно освобождает ассоциированную с ним память.
Операторы работы с курсорами.
Управление курсором реализуется путем выполнения следующих команд:
DECLARE - создание или объявление курсора;
OPEN - открытие курсора, т.е. наполнение его данными;
FETCH - выборка из курсора и изменение строк данных с помощью курсора;
CLOSE - закрытие курсора;
DEALLOCATE - освобождение курсора, т.е. удаление курсора как объекта.
В стандарте SQL для создания курсора предусмотрена следующая команда:
<создание_курсора>:=
DECLARE имя_курсора
[INSENSITIVE] [SCROLL] CURSOR
FOR SELECT_оператор
[FOR {READ_ONLY | UPDATE
[OF имя_столбца[,… n]]}]
При использовании ключевого слова INSENSITIVE будет создан статический курсор. Изменения данных не разрешаются, кроме того, не отображаются изменения, сделанные другими пользователями. Если ключевое слово INSENSITIVE отсутствует, создается динамический курсор.
При указании ключевого слова SCROLL созданный курсор можно прокручивать в любом направлении, что позволяет применять любые команды выборки. Если этот аргумент опускается, то курсор окажется последовательным, т.е. его просмотр будет возможен только в одном направлении - от начала к концу.
SELECT-оператор задает тело запроса SELECT, с помощью которого определяется результирующий набор строк курсора.
При указании аргумента FOR READ_ONLY создается курсор «только для чтения», и никакие модификации данных не разрешаются. Он отличается от статического, хотя последний также не позволяет менять данные. В качестве курсора «только для чтения» может быть объявлен динамический курсор, что позволит отображать изменения, сделанные другим пользователем.
Создание курсора с аргументом FOR UPDATE позволяет выполнять в курсоре изменение данных либо в указанных столбцах, либо, при отсутствии аргумента OF имя_столбца, во всех столбцах.
3. Расширения TransactSQL
В новой версии Microsoft SQL Server 2005 язык Transact-SQL переработан для обеспечения соответствия стандарту ANSI и расширения его функциональных возможностей. Он стал более логичным и завершенным.
Одним из усовершенствований Transact-SQL является включение полной поддержки технологии IntelliSense, что обеспечивает интерактивную подсказку параметров и интеллектуальное завершение для всех команд, редактируемых в среде SQL Server.
Другое новшество относится к оператору TOP. В SQL Server 2000 для этого оператора использовались фиксированные константы - TOP 5 (верхние 5 строк). А в SQL 2005 к оператору TOP можно привязывать любое выражение в пределах правил Transact-SQL, включая использование переменного или скалярного подзапроса. Оператор TOP поддерживают предложения INSERT, UPDATE и DELETE.
В SQL 2005 усовершенствована обработка аварийного прерывания: язык Transact-SQL дополнен новыми предложениями Try…Catch…Finally. Их использование позволяет отслеживать ошибки, вызвавшие прерывание, без потери контекста транзакции, что обеспечивает полное ее восстановление. Обработка прерывания возможна и в SQL Server 2000, но без сохранения детального контекста прерванной транзакции, что делает невозможным ее полное восстановление.
Кроме того, значительной переработке подверглись инструкции DDL (Data Definition Language, язык определения данных). Например, для того чтобы создать любой объект, необходимо воспользоваться только операторами языка DDL, а не использовать хранимые процедуры, как было до MS SQL 2005.
А также появились новые операторы языка манипулирования данными (Data Manipulation Language, DML) - самый используемый модуль команд языка Transact-SQL. В девяноста случаях из ста применяется именно он. В языке появилось много новых конструкций: CTE-выражения, PIVOT, UNPIVOT, TOP и многие другие.
В последней версии SQL Server 2012 появились новые решения высокого уровня доступности и аварийного восстановления, реализованные с использованием кластеров AlwaysOn и групп доступности, типа хранилища в памяти xVelocity, которое обеспечивает исключительно высокую производительность обработки запросов, быстрого просмотра данных и масштабируемой бизнес-аналитики с использованием Power View и табличного моделирования в службах Analysis Services, а также новых возможностей управления данными с помощью служб Data Quality Services.
4. Триггеры
Триггеры являются одной из разновидностей хранимых процедур. Их исполнение происходит при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML). Триггеры используются для проверки целостности данных, а также для отката транзакций.
Триггер - это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных. Применение триггеров большей частью весьма удобно для пользователей базы данных. И все же их использование часто связано с дополнительными затратами ресурсов на операции ввода / вывода. В том случае, когда тех же результатов (с гораздо меньшими непроизводительными затратами ресурсов) можно добиться с помощью хранимых процедур или прикладных программ, применение триггеров нецелесообразно.
Триггеры - особый инструмент SQL-сервера, используемый для поддержания целостности данных в базе данных. С помощью ограничений целостности, правил и значений по умолчанию не всегда можно добиться нужного уровня функциональности. Часто требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность. Кроме того, иногда необходимо отслеживать изменения значений таблицы, чтобы нужным образом изменить связанные данные. Триггеры можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответствии с правилами, стандартными значениями и т.д.
С помощью триггеров достигаются следующие цели:
проверка корректности введенных данных и выполнение сложных ограничений целостности данных, которые трудно, если вообще возможно, поддерживать с помощью ограничений целостности, установленных для таблицы;
выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновлении таблицы, реализованном определенным образом;
накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;
поддержка репликации.
Основной формат команды CREATE TRIGGER показан ниже:
<Определение_триггера>:=
CREATE TRIGGER имя_триггера
BEFORE | AFTER <триггерное_событие>
ON <имя_таблицы>
[REFERENCING
<список_старых_или_новых_псевдонимов>]
[FOR EACH {ROW | STATEMENT}]
[WHEN (условие_триггера)]
<тело_триггера>
Триггерные события состоят из вставки, удаления и обновления строк в таблице. В последнем случае для триггерного события можно указать конкретные имена столбцов таблицы. Время запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанных с ним событий) или AFTER (после их выполнения).
5. Хранимые процедуры
Хранимые процедуры - основное средство программирования серверной логики. Они представляют собой откомпилированный модуль, написанный на языке Transact-SQL. В коде хранимой процедуры можно использовать не только операции выборки и модификации данных, но и логику ветвления, переменные, вызовы других процедур и некоторые другие средства, характерные для языков программирования высокого уровня. Код процедуры синтаксически анализируется при компиляции, а оптимизированный план выполнения создается при первом вызове процедуры. В отличие от запросов, хранимые процедуры имеют возможность возвращать несколько наборов записей, а также значения.
Для вызова процедуры клиентская программа или другая процедура должны указать имя выполняемой процедуры и передать ей набор входных параметров.
Хранимые процедуры используются в проектах Access везде, где могут использоваться предложения SQL. Они могут служить:
в качестве источников записей в формах, отчетах, активных страницах;
в качестве источников строк для полей со списком.
Однако нужно помнить, что хранимые процедуры возвращают необновляемый набор записей.
Хранимая процедура может содержать одну инструкцию SQL, и в этом случае для ее создания можно использовать графический режим Конструктора запросов. Если же она состоит из нескольких инструкций SQL, тогда она создается и изменяется в редакторе SQL.
6. Элементы синтаксиса
Индификаторы
Идентификаторы - это специальные символы, которые используются с переменными для идентифицирования их типа или для группировки слов в переменную. Типы идентификаторов:
@- идентификатор локальной переменной (пользовательской).
@@- идентификатор глобальной переменной (встроенной).
#- идентификатор локальной таблицы или процедуры.
##- идентификатор глобальной таблицы или процедуры.
[] - идентификатор группировки слов в переменную.
Директивы сценария
Директивы сценария - это специфические команды, которые используются только в 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 существуют специальные команды, которые позволяют управлять потоком выполнения сценария, прерывая его или направляя в нужную логику.
Блок группировки - структура, объединяющая список выражений в один логический блок (BEGIN … END).
Блок условия - структура, проверяющая выполнения определенного условия (IF … ELSE).
Блок цикла - структура, организующая повторение выполнения логического блока (WHILE … BREAK … CONTINUE).
Переход - команда, выполняющая переход потока выполнения сценария на указанную метку (GOTO).
Задержка - команда, задерживающая выполнение сценария (WAITFOR)
Вызов ошибки - команда, генерирующая ошибку выполнения сценария (RAISERROR).
7. Динамический SQL
Динамическое конструирование выражений
Обычно базы данных Transact-SQL создаются и заполняются с помощью сценариев (скриптов) - хотя визуальный редактор прост в обращении, но им никогда быстро и без недочетов не создашь большую базу данных и не заполнишь ее данными.
Сценарий - это одно или более выражений, объединенных в логический блок, которые автоматизируют работу администратора.
Обычно сценарии пишутся как универсальное средство для выполнения стандартных задач, поэтому в них применяется динамическое конструирование логики - в запросы и команды вставляются переменные, а не конкретные названия объектов, что позволяет быстро изменять параметры скрипта.
Динамические запросы
Для большей гибкости часто статические запросы заменяются запросами, формируемыми динамически. Недостаток динамического SQL в том, что динамические запросы, разумеется, не могут быть проверены на этапе компиляции. Если, например, используемой в запросе таблицы не существует, то при работе выполнении операции OPEN будет выброшено исключение.
Классическая задача, требующая применения динамического конструирования SQL-запросов, - отчёты в интерфейсах, где пользователь может выбрать разные условия, по которым следует сформировать отчёт.
8. Сортировка данных
Параметрическое определение порядка сортировки данных.
Применение оператора IF…ELSE для исполнения заранее запрограммированного запроса.
Пожалуй, большинству программистов сразу приходит в голову воспользоваться оператором IF…ELSE для исполнения одного или нескольких заранее запрограммированных запросов. Предположим, к примеру, что требуется сформировать отсортированный список поставщиков из таблицы Shippers учебной базы данных Northwind. При этом код будет передавать столбец, по которому следует отсортировать результат, в хранимую процедуру в качестве параметра. При таком решении хранимая процедура, скорее всего, будет выглядеть примерно так:
CREATE PROC GetSortedShippers
@OrdSeq AS int
AS
IF @OrdSeq = 1
SELECT * FROM Shippers ORDER BY ShipperID
ELSE IF @OrdSeq = 2
SELECT * FROM Shippers ORDER BY CompanyName
ELSE IF @OrdSeq = 3
SELECT * FROM Shippers ORDER BY Phone
Преимущества этого варианта решения заключаются в том, что код прост и понятен в силу своей прямолинейности, а оптимизатор запросов SQL Server может заблаговременно построить и оптимизировать план исполнения каждого предложения SELECT, что обеспечит максимальную производительность. Основной недостаток этого решения заключается в том, что требуется поддерживать несколько отдельных запросов SELECT (в данном примере три предложения) при изменении требований к отчету.
Использование названий столбцов в качестве параметров
Другой подход к решению заключается в использовании в роли параметра названий столбцов.
А.: Использование в качестве параметра названия столбца.
CREATE PROC GetSortedShippers
@ColName AS sysname
AS
SELECT *
FROM Shippers
ORDER BY
CASE @ColName
WHEN `ShipperID` THEN ShipperID
WHEN `CompanyName` THEN CompanyName
WHEN `Phone` THEN Phone
ELSE NULL
END
Код показывает видоизмененную хранимую процедуру GetSortedShippers. Выражение CASE определяет, какой столбец SQL Server использует в операторе ORDER BY на основании значения переданного параметра. Обратите внимание на то, что выражение в операторе ORDER BY не входит в список выходных данных SELECT. В соответствии со стандартом ANSI SQL-92 не разрешается использовать выражение в операторе ORDER BY, если это выражение не указано в списке SELECT, однако в стандарте ANSI SQL-99 такое ограничение было снято. Заметьте, что в SQL Server всегда позволялось это делать.
Теперь испытаем новую хранимую процедуру, передав ей в качестве параметра название столбца ShipperID:
EXEC GetSortedShippers `ShipperID`
Пока все идет хорошо. Но если попытаться запустить эту процедуру, указав в качестве параметра столбец CompanyName, она не будет работать:
EXEC GetSortedShippers `CompanyName`
Прочитав внимательно полученное сообщение об ошибке:
Server: Msg 245, Level 16, State 1, Procedure GetSortedShippers, Line 5
Syntax error converting the nvarchar value `Speedy
Express`to a column of data type int.
Мы понимаем, что SQL Server попытался преобразовать значение `Speedy Express` (которое относится к типу данных nvarchar) в целочисленное значение типа данных integer. Разумеется, сделать это оказалось невозможно. Ошибка произошла из-за того, что выражение CASE определяет тот тип данных, который получит результат выполнения этого выражения, в соответствии с правилами приоритетов типов данных, Data Type Precedence. В соответствии с этими правилами, о которых можно прочитать в SQL Server Books Online (BOL), у типа данных int более высокий приоритет, чем у типа данных nvarchar. Код хранимой процедуры заставил SQL Server сортировать выходные данные в соответствии со столбцом CompanyName, который относится к типу данных nvarchar. Приведенное в этой процедуре выражение CASE может возвращать либо столбец ShipperID с типом данных int, либо CompanyName с типом данных nvarchar, либо столбец Phone с типом данных nvarchar. Поскольку тип данных int имеет самый высокий приоритет, то и результат выполнения выражения CASE должен иметь тип данных int.
Чтобы избежать этой ошибки преобразования типов данных, можно попробовать трансформировать столбец ShipperID в тип данных varchar. В результате этого шага тип данных nvarchar будет обладать наивысшим приоритетом среди возвращаемых этим запросом типов данных.
Скорректированная хранимая процедура GetSortedShippers:
В.: Использование в качестве параметра названия столбца.
ALTER PROC GetSortedShippers
@ColName AS sysname
AS
SELECT *
FROM Shippers
ORDER BY
CASE @ColName
WHEN `ShipperID` THEN CAST (ShipperID AS varchar(11))
WHEN `CompanyName` THEN CompanyName
WHEN `Phone` THEN Phone
ELSE NULL
END
Если теперь запустить эту хранимую процедуру, указав в качестве параметра любое название столбца из трех возможных, то будет получен результат, который выглядит вполне правдоподобно. Создается впечатление, что указанный столбец был использован для определения порядка сортировки выходных данных запроса.
Однако в используемой таблице содержатся сведения всего лишь о трех поставщиках с идентификаторами 1, 2 и 3. Предположим, что в эту таблицу добавили еще семь поставщиков:
С.: Добавление нескольких строк в таблицу поставщиков Shippers.
INSERT INTO Shippers VALUES (`Shipper4`, `(111) 555-5555`)
INSERT INTO Shippers VALUES (`Shipper5`, `(111) 666-6666`)
INSERT INTO Shippers VALUES (`Shipper6`, `(111) 777-7777`)
INSERT INTO Shippers VALUES (`Shipper7`, `(111) 888-8888`)
INSERT INTO Shippers VALUES (`Shipper8`, `(111) 999-9999`)
INSERT INTO Shippers VALUES (`Shipper9`, `(111) 111-2222`)
INSERT INTO Shippers VALUES (`Shipper10`, `(111) 222-3333`)
Столбец ShipperID обладает свойством IDENTITY, так что SQL Server автоматически генерирует значения для этого столбца). Еще раз запустим нашу хранимую процедуру, указав в качестве параметра столбец ShipperID:
EXEC GetSortedShippers `ShipperID
В результате получим выходные данные, представленные в таблице №1:
Таблица 1
|
ShipperID |
CompanyName |
Phone |
|
|
1 |
Speedy Express |
(495) 111-1111 |
|
|
10 |
Shipper10 |
(495) 222-3333 |
|
|
2 |
United Package |
(495) 222-2222 |
|
|
3 |
Federal Shipping |
(495) 444-4444 |
|
|
4 |
Shipper4 |
(495) 555-5555 |
|
|
5 |
Shipper5 |
(495) 666-666 |
|
|
6 |
Shipper6 |
(495) 777-7777 |
|
|
7 |
Shipper7 |
(495) 888-8888 |
|
|
8 |
Shipper8 |
(495) 999-9999 |
|
|
9 |
Shipper9 |
(495) 111-2222 |
Запись, относящаяся к поставщику Shipper10, явно находится не на месте. Это произошло оттого, что код произвел сортировку на основании символьного представления данных, а не числового. В символьной строке 10 предшествует 2, поскольку 10 начинается с символа 1. Для преодоления возникшей трудности можно дополнить значения идентификаторов в столбце ShipperID нулевыми головными цифрами и знаками, чтобы сделать их все одинаковой длины. Тогда сортировка на основе символов даст такие же результаты, как и сортировка на основе цифр. Модифицированная таким образом хранимая процедура выглядит следующим образом:
D.: Использование в качестве параметра названия столбца.
ALTER PROC GetSortedShippers
@ColName AS sysname
AS
SELECT *
FROM Shippers
ORDER BY
CASE @ColName
WHEN `ShipperID` THEN CASE SIGN(ShipperID)
WHEN -1 THEN `-`
WHEN 0 THEN `+`
WHEN 1 THEN `+`
ELSE NULL
END +
RIGHT (REPLICATE(`0`, 10) +
CAST (ABS(ShipperID) AS varchar(10)), 10)
WHEN `CompanyName` THEN CompanyName
WHEN `Phone` THEN Phone
ELSE NULL
END
Десять нулей ставятся впереди абсолютного значения идентификатора в столбце ShipperID, и из полученного значения код использует только 10 стоящих справа знаков. Функция SIGN() определяет, следует ли ставить знак (+) для положительных значений, или же поставить знак (-) для отрицательных значений. Таким образом, результат всегда будет состоять из 11 символов, включая знак + или -, головные нули и абсолютное значение идентификатора из столбца ShipperID. Если отрицательных идентификаторов поставщиков не может быть, то использовать знак не обязательно. При сортировке знак - размещается впереди знака +, так что предложенное решение работает и в случае отрицательных значений идентификаторов.
Так, же можно последовать примеру решения Ричарда Ромли. Он разбил оператор ORDER BY на три отдельных выражения CASE, каждое из которых обрабатывает свой столбец данных. Тем самым Ричард избежал проблем, связанных с тем, что выражение CASE может возвращать значение только одного типа данных. Если воспользоваться этой конструкцией, SQL Server будет возвращать нужный тип данных для каждого выражения CASE без каких-либо преобразований типов данных.
E.: Использование в качестве параметра названия столбца, подход Ричарда Ромли.
ALTER PROC GetSortedShippers
@ColName AS sysname
AS
SELECT *
FROM Shippers
ORDER BY
CASE @ColName WHEN `ShipperID` THEN ShipperID ELSE NULL END,
CASE @ColName WHEN `CompanyName` THEN CompanyName ELSE NULL END,
CASE @ColName WHEN `Phone` THEN Phone ELSE NULL END
Однако при этом надо иметь в виду, что индекс сможет оптимизировать операцию сортировки только в том случае, когда в указанном столбце не выполняется никаких вычислений.
Заключение
Transact-SQL, является самым распространенным и очень мощным инструментом в разработке баз данных. Программист освоивший Transact-SQL может создать различные базы данных и их объекты, предоставлять пользователям доступ к серверу, базам данных и их объектам, манипулировать данными, оптимизировать работу сервера и многое другое.
база запрос курсор триггер
Размещено на Allbest.ru
...Подобные документы
Создание баз данных с помощью Transact-SQL. Специализированные типы данных. Обеспечение целостности ссылок. Преимущества хранимых процедур. Синтаксис запроса на создания триггера. Фиксированные серверные роли. Предоставление прав на объекты в базе данных.
лабораторная работа [2,2 M], добавлен 12.09.2012Использование баз данных менеджерами автосалонов для повышения качества и скорости обслуживания клиентов. Создание запросов на добавление, удаление, обновление данных. Запросы перекрестный, на выборку. Кнопочная форма базы данных с практичным интерфейсом.
курсовая работа [1,6 M], добавлен 10.02.2014Функции базы данных Access: организация, добавление и изменение информации, связывание таблиц, макросы, модули, средства печати. Элементы базы данных: запросы, таблицы, формы и отчеты. Виды запросов: на выборку, перекрестные, на изменение, с параметрами.
реферат [2,1 M], добавлен 16.05.2014СУБД - многопользовательские системы управления базой данных, специализирующиеся на управлении массивом информации. Запросы на выборку и изменение данных, формирование отчетов по запросам выборки. Схема базы данных. Программа по управлению базой данных.
реферат [1,9 M], добавлен 27.12.2013Схема взаимодействия подразделений предприятия. Выбор и обоснование технологии проектирования базы данных. Описание объектов базы данных. Разработка запросов на выборку, изменение, обновление и удаление данных. Интерфейсы взаимодействия с базой данных.
курсовая работа [1,4 M], добавлен 25.05.2023Разработка информационной системы, выбор языка программирования, физическое описание базы данных, выбор типа и описание таблиц базы данных. Техническое проектирование, ограничения и значения по умолчанию, представления, хранимые процедуры и триггеры.
курсовая работа [519,8 K], добавлен 25.05.2010Структура таблицы и типы данных. Ввод данных в ячейки таблицы. Создание запросов на выборку, удаление, обновление и добавление записей, на создание таблицы. Основное различие между отчетами и формами, их назначение. Создание отчетов для базы данных.
курсовая работа [1,9 M], добавлен 17.06.2014Ограничения, присутствующие в предметной области. Проектирование инфологической модели данных. Описание основных сущностей и их атрибутов. Логический и физический уровни модели данных. Реализация базы данных: представления, триггеры, хранимые процедуры.
курсовая работа [1,7 M], добавлен 10.02.2013Создание таблиц базы данных с помощью MS Access "Страны Азии". Форма базы данных и запросы к выборкам данных. Модификация структуры таблиц, создания связей между главными таблицами, редактирование данных и проектирование форм для реальной базы данных.
контрольная работа [723,9 K], добавлен 25.11.2012Системы управления базами данных: сущность и характеристика. Типы данных и свойства полей СУБД Access. Объекты базы данных: таблицы, схемы данных, формы, запросы, отчеты. Разработка и проектирование базы данных "Продажи книг" в среде Microsoft Access.
курсовая работа [1,8 M], добавлен 04.02.2013Цели восстановления данных. Обеспечение отказоустойчивости, предупреждение неисправностей в работе. Параметры, необходимые для планирования сроков восстановительных работ. Создание устройства резервного копирования баз данных с помощью Transact-SQL.
презентация [247,6 K], добавлен 10.11.2013Процесс разработки базы данных для хранения и обработки информации. Ключи, индексы, триггеры, хранимые процедуры. Разработка пользовательского интерфейса и базы данных. Основные инструментальные средства для разработки клиентской и серверной частей.
дипломная работа [225,0 K], добавлен 18.05.2013Синтаксис, типы данных, используемые в базе данных MySQL. Создание и удаление базы данных, создание таблицы и удаление таблицы, изменение ее свойств. Переименование, вставка и удаление столбцов, изменение их свойств. Обновление и поиск записей в таблице.
лабораторная работа [641,7 K], добавлен 04.03.2010Определение последовательности восстановления данных. Просмотр содержимого устройства резервного копирования средствами Enterprise Manager. Восстановление БД при повреждении диска. Команды Transact-SQL. Восстановление БД на другом экземпляре SQL Server.
презентация [83,2 K], добавлен 10.11.2013Создание базы данных. Поиск, изменение и удаление записей. Обработка и обмен данными. Проектирование базы данных. Определение формул для вычисляемой части базы. Редактирование полей и записей. Формы представления информации, содержащейся в базе данных.
курсовая работа [67,0 K], добавлен 23.02.2009Понятия основных компонентов базы данных Access. Таблицы, отчеты, макросы и модули, форма, запросы к базе и их виды. Типы данных. Создание базы данных "Кадры". Создание таблицы в режиме конструктора. Использование мастера подстановок для создания связей.
курсовая работа [818,0 K], добавлен 10.03.2016Понятие базы данных, модели данных. Классификация баз данных. Системы управления базами данных. Этапы, подходы к проектированию базы данных. Разработка базы данных, которая позволит автоматизировать ведение документации, необходимой для деятельности ДЮСШ.
курсовая работа [1,7 M], добавлен 04.06.2015Что такое базы данных, визуализация информации базы. Структура и свойства простейшей базы данных. Характеристика определений, типов данных, безопасность, специфика формирования баз данных. Подходы к проектированию технического задания. Работа с таблицами.
презентация [4,3 M], добавлен 12.11.2010Реляционная модель базы данных в текстовом виде. Код, с помощью которого были созданы и заполнены таблицы. Разработка схемы базы данных справочника селекционера. Создание запросов, их основные виды. Процедуры на выбор данных, добавление и удаление.
методичка [1,1 M], добавлен 20.05.2014Сущности и функциональные зависимости базы данных. Атрибуты и связи. Таблицы базы данных. Построение ER-диаграммы. Организация ввода и корректировки данных. Реляционная схема базы данных. Реализация запросов, получение отчетов. Защита базы данных.
курсовая работа [2,4 M], добавлен 06.02.2016
