Системные переменные, функции и хранимые процедуры

Пример

select Object_id ('Сотрудники') -- вывод идентификатора таблицы

Dbcc Showcontig (629577281) -- вывод данных о фрагментации таблицы

Справочная информация выводится в виде строк:

Pages/Extents Scanned ‑ число страниц/экстентов в таблице или индексе.

Extents Switches ‑ число переключателей между экстентами. Если это чис­ло превышает число всех экстентов, то экстенты фрагментированы.

Avg Pages per Extent ‑ среднее число страниц на один экстент.

Scan Density [Best Count: Actual Count] ‑ плотность сканирования в процентах. Чем она выше, тем лучше. Дополнительно указывается идеаль­ное и текущее число экстентов.

Logical/Extent Scan Fragmentation ‑ степень логической/физической фраг­мен­тации данных. При отсутствии фрагментации степень нулевая.

Avg. Bytes free per Pages ‑ среднее свободного пространства (в байтах) на странице размером 8 Кбайт.

Avg. Page density (full) ‑ процент среднего заполнения страницы. При боль­­шом проценте заполнения нужно перестроить данные на странице.

4.3.6.11. Курсоры

Курсорыпозволяют создать временный результирующий набор записей (строк) из пред­став­ле­ния или запроса с воз­мож­­ностью доступа к любой отдельной за­пи­си этого набора.

Курсоры Transact-SQL бывают четырех типов:

1. Cтатические (Static) ‑ сервер сохраняет результирующий набор систем­ной базы данных tempdb на основе моментального снимка (копий) исполь­зуемых таблиц. Курсоры открываются только для чтения и из­ме­не­ния, произведенные в базе данных после открытия, не учитываются.

2. Ключевые (Keyset) ‑ результирующий набор ключей состоит из ссылок на записи и создается в систем­ной базе данных tempdb толь­ко при открытии курсора и далее не изменяется. Учитываются изменения в базе данных после открытия курсора, а удаления и вставки новых записей ‑ нет.

3. Последовательные (Fast_Forward) ‑результирующий набор не со­х­раняется на сервере набора данных и запоминается только номер те­ку­щей строки. Допускается переход только к следующей строке курсора, ко­то­рая нахо­ди­т­ся сервером. Все изменения в базе будут учиты­вать­ся.

4. Динамические (Dynamic) ‑ аналогичны последовательным курсо­рам, но до­пус­кают доступ к любой отдельной строке.

Схема работы с курсором: объявление, открытие, выборка данных из курсора командой Fetch в цикле обработки курсора, закрытие и осво­бож­дение курсора.

Команды работы с курсором (имя курсора может задаваться кон­стан­той или переменной типа курсора).

Declare <имя курсора> Cursor[Local | Global] [Forward_Only | Scroll]

[Static | Keyset | Dynamic | Fast_Forward] --типы курсоров

[Read_Only | Scroll_Locks | Optimistic] [Type_Warning]

For <Select...> For Update [Of <имя столбца>,...] ‑ объяв­ле­ние курсора.

Рассмотрим параметры этой команды.

Local/Global ‑ локальный/глобальный курсор, удаляемый/сохраняемый пос­ле завершения транзакции. Допускаются одноименные локальный и глобальный курсоры. В этом случае в остальных операторах по умолчанию используется локальный, а глобальный уточняется параметром Global.

Forward_Only/Scroll ‑ выборка следующей/любой строки.

Read Only ‑ только чтение данных из курсора.

Scroll_Locks ‑ блокирует все строки курсора.

Update [Of <имя колонки>,...] ‑ допускается корректировка всего курсора или только указанных столбцов.

Optimistic ‑ при изменении данных в курсоре, которые уже были изменены вне его, выводится сообщение об ошибке, и операция откатывается.

Type_Warning ‑ вывод сообщения при преобразовании типа курсора.

Open/Close/Deallocate [Clobal] Cursor <имя курсора> ‑ открытие/зак­ры­тие /ос­вобождение курсора. Пос­ле освобождения курсора открыть его уже невозможно. Закрытие и удаление курсора выполняются автоматически при окончании работы хранимой процедуры.

Fetch[ Next | Prior | First | Last | Absolute n | Relative k] [Global]

<имя курсора> Into <@X1> [, <@X2>]... ‑ чтение следующей (Next), предыдущей (Prior), первой (First), последней (Last) записи в переменные @X1, @X2... соот­вет­ственно. Значение N (константа или переменная) задает номер читаемой строки (если N<0, то отсчет ведется от конца набора). Значение K задает число пропуcкаемых записей вперед или назад (К<0) от текущей записи. Признаком окончания строк в запросе является сохранение значений пе­ременных неизменными или значение системной переменной Fetch_Status<>0.

Update/Delete <имя таблицы>

[Set {<имя столбца>={Default | Null | <выражение>}},...] --для Update

Where Current Of <имя курсора> ‑ корректировка/удаление строки табли­цы, соот­вет­ствующей текущей строке курсора.

Пример хранимой процедуры c курсором (п. 4.3.7).

CREATE PROCEDURE [Подразделения] AS

Declare @kp Integer --объявление переменной

Declare @np Nvarchar (30) --объявление переменной

--объявление курсора с запросом вывода таблицы Подразделения

Declare zapros Cursor For Select* from Подразделения For Read Only

Open zapros --открытие курсора, запрос выполняется и формируется набор

Set Nocount On --блокировка вывода числа обработанных записей

While 1>0 --заголовок бесконечного цикла чтения набора

Begin --начало блока тела цикла

Fetch zapros Into @kp, @np --чтение очередной записи набора

If @@Fetch_Status<>0 break --окончание набора и выход из цикла

Select @kp, @np --вывод полей записи на экран

End --конец тела цикла

Close zapros Deallocate zapros --закрытие и удаление курсора

4.3.6.12. Транзакции и блокировки

Транзакция - это логическая единица обработки базы данных, пере­во­дящая базу данных из одного его целостного состояния в другое.

Требования ACID к транзакциям:

1. Атомарность (Atomicity) ‑ будут зафиксированы либо все изме­не­ния, выполненные в транзакции, либо восстановлено исходное состояние базы данных на начало выполнения транзакции.

2. Согласованность (Consistency) ‑ контроль правил и ограничений целостности базы данных. При их нарушении производится откат на начало транзакции.

3. Изолированность (Isolation) ‑ изменения, выполняемые одной транз­акцией, не должны зависеть от изменений, выполняемых в другой транзакции.

4. Устойчивость (Durability) ‑ физическая фиксация всех необхо­ди­­мых изменений в базе данных при успешном завершении транзакции.

Тран­зак­ция может состоять из группы операторов SQL, которая начинается с команды Begin Tran[<имя транзакции>] (начать тран­закцию) и закан­чи­вается оператором Commit Tran[<имя тран­зак­ции>] (завершить транзакцию: зафиксировать в базе все изме­не­ния, произ­ве­ден­ные после начала тран­зак­ции). При ава­рий­ном завершении транзакции (например, отклю­чи­ли элек­тро­питание) база останется в рабочем сос­тоянии на момент начала тран­закции. Если внутри транзакции указать оператор Rollback Tran[<имя транзакции или точки сохранения>], то все изменения отменяются, и база восста­нав­ли­ва­ется на момент начала тран­­закции (откат назад) или точки сохранения. Точка сохранения (аналог поименованной транзакции) задается в виде Save Transaction[<имя точ­ки>]. Все транзакции сох­раняются в журнале тран­зак­ции, который ис­поль­­зу­ется для вос­ста­нов­ле­ния базы данных из архивной копии.

Изоляция транзакций от других пользователей определяется опе­ра­­то­ром Set Transaction Isolation <метод изоляции>. Существуют сле­ду­ющие методы (уровни) изоляции: Read Commited/ UnCommited (поз­во­ляет выполнять/игнорировать блокировку другим пользователям одной и той же сроки или страницы), Repeatable Read (монопольно блокирует строки), Serializable (аналогичен Repeatable Read, но предотвращает чтение уже не существующих записей).

Взаимные блокировки различных процессов иногда могут пол­ностью заблокировать работу всей ба­зы данных. Просмотреть инфор­ма­цию о блокировках можно хранимой про­це­ду­рой sp_lock или Enterpise Manager (выбор идентификатора нужного процес­са из группы Current Activity папки Management). Удаление про­цесса, вызвавшего блокировку, возможно командой Kill Process из кон­текст­ного меню для этого процесса или командой Kill <имя процесса>.

@@Error- код последней системной ошибки (0 - ошибок нет).

@@Cursor_Row - число строк, связанных с курсором после его открытия.

@@Rowcount - число строк, на которые повлиял последний запрос.

@@Fetch_Status - состояние последнего выполненного оператора Fetch.

Avg(x),Sum(x), Min(x),Count(*) - среднее, сумма, минимум, максимум, число значений по столбцам X по всей таблице или по группе строк.

Ascii(n), Char(c) ‑ символ с кодом N, код символа C соответственно.

Col_Length(s,t) ‑ длина столбца S таблицы T.

Coalesce(x1,...) ‑ возвращает первое выражение, отличное от Null.

Cast(x As t) ‑ возвращает преобразованное к типу T значение X.­

Convert(t [(l)],x[,f]) ‑ возвращает преобразованное к типу T (с длиной L) значение X. Пара­метр F задает формат даты: 4 (дд.мм.гг), 104 (дд.мм.гггг), 8 (чч:мм:cc).

Charindex(s1,s2) ‑ возвращает начальную позицию строки S1 в S2.

Lower(s)/Upper(s) ­­­‑ преобразование букв стро­ки S в строчные/заглавные.

Ltrim/Rtrim/ - строка S без левых/правых пробелов.

Rand(n) - случайное число от 0 до N (1).

Round(n,t) ‑ округление числа N с точностью до T знаков.

Replicate(s,n), Len(s) ‑ строка S повторяется N раз, длина строки S.

Reverse(s) ‑ возвращает строку символов из строки S в обратном порядке.

Space(n) ‑ возвращает строку из пробелов в количестве N.

Str(x[,l[,d]]) - число X преобразуется в строку длиной L и дробной частью длиной D.

Stuff(s1,n,l,s2) ‑ строка S2 заменяет подстроку длиной L, начиная с символа под номером N, в строке S1.

Substring(s,n,l) ‑ возвращается подстрока строки S длиной L, начиная с символа под номером N (отсчет символов начинается с единицы).

Isnull(x,y) ‑ возвращает значение Y, если X=Null.

Getdate() ‑ текущая и время.

Day(d), Month(d), Year(d) ‑ возвращает номер дня, месяца и года даты D.

DataLength(x) ‑ длина выражения X.

DateAdd(p,n,d) - увеличение даты D на N периодов типа P: yy (год), q (квар­­тал), m (месяц), d (день), wk (неделя), hh (час), mi (минута), s (се­кун­да), ms (миллисекунда).

Datediff(p,d1,d2) - число периодов типа P между датами D1 и D2 (D1<D2).

Datename/Datepart(p, d) ‑ часть (P) даты D в виде строки/числа.

Object_id(‘имя объекта’) ‑ идентификационный номер объекта базы.

Host_name/Hоst_id ()‑ имя/идентификационный номер компьютера­‑сервера

User - имя текущего пользователя базы данных.

Дата добавления: 2014-12-10