База данных "Банковские вклады"

 

Рис.8

Группировка:

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

          Запрос. Получить список договоров и общую сумму счетов по каждому договору (Рис.9)

select id_dogovor, sum(summa) as dogovor_sum

from Dogovor, Schet

group by id_dogovor

 

Рис.9

 

 

Выборка из нескольких таблиц:

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

          Запрос. Выбрать все поля из таблицы Клиенты и столбец id_договора из таблицы Договор (Рис. 10)

select Klient.*, id_dogovor

from Klient, Dogovor

where Klient.id_klient=Dogovor.id_klient

Рис.10

 

Объединение запросов:

         Для объединения результатов двух и более запросов нужно использовать ключевое слово UNION. Объединяемые запросы должны иметь одинаковое количество и тип полей. Параметр ORDER BY можно указывать только в последнем запросе.

        Запрос. Выбрать клиентов, которые положили свои счета на вклад «До востребования» или сумма счета у которых больше 6000 (рис. 11)

 

select FIO from Klient

where id_klient in (select id_klient from Dogovor

where id_vklad=(select id_vklad from Vkladu

where name='До востребования'))

union

select FIO from Klient

where id_klient in (select id_klient from Dogovor

where id_schet in (select id_schet from Schet

where summa > 6000))

 

Рис. 11

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

Представления – это объекты базы данных, которые можно рассматривать как виртуальные таблицы. На самом деле хранится только формулировка команды SELECT, с помощью которой производится выборка данных из реальных таблиц[3].

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

Представления могут быть обновляемыми (т.е., представлять возможность не только чтения, но и изменения данных в  исходных таблицах) и необновляемыми. Представление будет обновляемым  только в том случае, если его  структура такова, что SQL server может точно определить, в какие строки каких таблиц нужно поместить измененные данные. Необновляемыми будут, например, представления, содержащие итоговые данные и группировки.

Для создания представлений используется команда CREATE VIEW.

Краткий формат этой команды:

CREATE VIEW имя_представления  AS

Команда_SELECT

 

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

 

Представление. Создать представление, которое будет выводить список клиентов (их фамилии, адреса и телефон), договора которых курирует оператор с фамилией Понедельников (рис.12)

 

create view Klientu

as

select FIO, adress, telefon

from Klient

where id_klient in (select id_klient from Zakluchenie_dogovora

where id_operator=(select id_operator from Operator

where fam = 'Понедельников'))

 

         Для просмотра представления следует выполнить команду

select*from Klientu

 

Рис. 12

 

         Представление.  Создать представление, которое  будет выводить фамилию и телефон операторов, которые курируют больше одного договора (Рис. 13)

 

create view Operatoru

as

select fam, telefon

from Operator

where 1<(select count (distinct id_dogovor) From Dogovor

where id_operator=Operator.id_operator)

 

Рис.13

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

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

Структура хранимой процедуры следующая:

CREATE PROC[EDURE] имя_процедуры  [параметры]

AS

Код процедуры

 

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

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

 

Объявление переменных имеет вид 

DECLARE имя_переменной тип_переменной [(длина)]

 

Блок операторов заключается  в команды BEGIN … END

 

Оператор присвоения выгладит довольно странно:

SELECT переменная=значение

Зато с помощью такого синтаксиса при выполнении команды SELECT можно  сохранять значения в переменных.

Альтернативный формат оператора присвоения:

SET переменная=значение

 

Условный оператор выглядит так:

IF условие

Оператор1

[ELSE

Оператор2]

 

Цикл по счетчику отсутствует, есть только цикл по условию

WHILE условие

Оператор

 

Для прерывания цикла  используется команда BREAK.

Для прерывания итерации цикла используется команда CONTINUE.

Оператор печати имеет вид PRINT выражение

Выход из процедуры: RETURN [код_завершения]

Команда 

RAISERROR сообщение, уровень_опасности,  код_состояния 

применяется для вывода сообщений  об ошибках и прочих предупреждений в стандартной для SQL server форме.

 

Выражение CASE применяется для селективного выбора на основании нескольких опций:

CASE выражение

WHEN вариант1 THEN выражение1

WHEN вариант2 THEN выражение2

…

ELSE выражениеN

END

        Для удаления хранимой процедуры используется команда:

DROP PROCEDURE имя_процедуры

 

         Процедура.  Создадим процедуру которая в качестве параметра получает фамилию клиента и выводит наименование, срок и проценты выбранного им вклада (Рис. 14)

 

CREATE PROC oneproc

  @k VARCHAR(20)  

AS

SELECT name as ‘Наименование’, srok as ‘Срок’, procentu as ‘Проценты’ FROM  Vkladu

where id_vklad=(select id_vklad from Dogovor

 

         Для запуска этой процедуры  нужно выполнить команду

exec oneproc ‘Чехов’

 

Рис. 14

Процедура. Создать процедуру которая выводит конкретную информацию о запрашиваемом  клиенте (Рис. 15).

 

create proc Customer @k varchar(50) as

select Klient.id_klient, Klient.adress, Klient.tel, Dogovor.id_dogovor, Dogovor.data_open

from Klient inner join Dogovor on Klient.id_klient=Dogovor.id_klient

where FIO=@k

 

Рис. 15

 

2.7. CCL. Курсоры

          CCL (Cursor Control language) – язык управления курсорами, составная часть SQL.

Как вы уже поняли, команды  манипулирования данными  SELECT, UPDATE, DELETE работают сразу с группами строк. Эти группы, вплоть до отдельных строк, можно выбрать с помощью опции WHERE. Что же делать в том случае, если требуется перебрать строки некоторой таблицы последовательно, одну за другой? Для этого в языке SQL существует такое понятие, как курсор. Курсор (current set of record) – это временный набор строк, которые можно перебирать последовательно, с первой до последней.

Для работы с курсорами существуют следующие команды.

 

Объявление курсора:

DECLARE имя_курсора CURSOR FOR SELECT текст_запроса

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

         Открытие курсора:

OPEN имя_курсора

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

 

Чтение следующей строки из курсора:

FETCH имя_курсора INTO список_переменных

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

Глобальная переменная @@FETCH_STATUS принимает ненулевое значение, если строк в курсоре больше нет. Если же набор строк еще не исчерпан, то @@FETCH_STATUS равна нулю, и оператор FETCH перепишет значения полей из текущей строки в переменные.

 

Закрытие курсора:

CLOSE имя_курсора

 

Для удаления курсора из памяти используется команда

DEALLOCATE имя_курсора

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.8. Триггеры

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

Существует 3 типа триггеров: INSERT, UPDATE и DELETE. Правила работы с триггерами следующие:

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

 

Краткий формат триггера (более подробно смотрите в Book Online):

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

     ON имя_таблицы

FOR INSERT | UPDATE | DELETE

     AS

Код_триггера

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

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

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

IGNOGE – игнорировать,

RESTRICT – запрещать,

CASCADE – каскадная обработка,

SET DEFAULT – назначать значения  по умолчанию,

SET NULL – назначать NULL-значения.

Политика IGNORE означает, что мы не предусматриваем  никаких проверок и ограничений.

Политика RESTRICT действует, когда мы применяем ограничения внешних ключей.

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

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

Политика SET NULL похожа на предыдущую, только мы назначаем NULL-значения.

 

Триггер.  Создать триггер, запрещающий изменять или удалять данные из таблицы Клиенты (Рис. 18)

 

create trigger stop on Klient

for delete, update as

print 'Удаление и изменение  данных запрещено!'

rollback transaction

 

Рис. 18

 

Триггер. Создать триггер, разрешающий добавлять и редактировать данные во всех таблицах только пользователю «Администратор» (Рис. 20)

 

create trigger openstop on Dogovor

for insert, update

as

print 'Пользователь'+current_user

if current_user<>'Администратор'

begin

print 'добавление и редактирование запрещено'

rollback Transaction

end

else print 'добавление и редактирование разрешено'