ПБД (9) - Лекция №7 - SQL (продолжение)

2012-11-21T06:17:04Z

109.252.52.180: /* Рекурсия */ поправка

== SQL ==

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

Описание процедуры:
<syntaxhighlight lang="sql">
create procedure Proc1 (<in, out, inout>, имя тип)
declare имя тип -- объявление процедуры
begin
-- объявление переменных
set a = 10;
set b = NULL;
set c = (select count(*) from таблица);

-- условия
if a > 10 then a = 4 elseif a < 10 then a = 16 else a = 9000 endif;

-- цикл
while условие do
-- тело цикла
end while;
end;
</syntaxhighlight>

Вызов процедуры:
<syntaxhighlight lang="sql">
call someProc(10, 'abc');
</syntaxhighlight>

==== Функции ====

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

Важно, что <code>return</code> не возвращает управление.

<syntaxhighlight lang="sql">
create function func(a int, b char(2)) returns int
declare ...
begin
-- тело функции
end;
</syntaxhighlight>

Функции вызываются из запросов. Функции могут быть:

* скалярные - возвращают одно значение (обращение к полю):
<syntaxhighlight lang="sql">
select a, func1(a) from T
</syntaxhighlight>

* табличные - возвращают набор записей (обращение к таблице);
<syntaxhighlight lang="sql">
select a, b from func2('...')
</syntaxhighlight>

==== Cursor ====

Это итератор по строкам результата запроса (как QSqlRecord в QSqlQueryModel):
<syntaxhighlight lang="sql">
declare C cursor for
select name, year from someTable
where city = 'Москва'
begin
open C; -- выполнение запроса
l: loop
fetch from C into ... -- проход по строкам
if состояние then leave l endif;
end loop;
close C;
end;
</syntaxhighlight>

==== Перехват исключений в где-то ====

<syntaxhighlight lang="sql">
declare <undo, exit, continue> handler
for состояние1, состояние2, состояние3
-- действие
</syntaxhighlight>

==== Перехват исключений в Microsoft SQL Server ====

<syntaxhighlight lang="sql">
-- ловля исключений
begin try
-- какие-нибудь действия
end try;

-- блок обработчиков
begin catch
select error_number(), error_message() ...
end catch;
</syntaxhighlight>

Вызов исключений:
<syntaxhighlight lang="sql">
raiserror(код сообщения, серьёзность, состояние)
</syntaxhighlight>

=== Расширения SQL(99) ===

==== Рекурсия ====

Рекурсия выполняет первую итерацию, добавляет её результаты во вторую, выполняет вторую... и так далее, пока будут просходить изменения.

<syntaxhighlight highlight="12" lang="sql">
-- просто таблица
Road(fr, to, len);

-- рекурсия
with Recursive R(f, t, l)
as(
-- база рекурсии
select fr, to, len from Road
union
-- индукция рекурсии
select r1.fr, r2.t, r1.len + r2.l
from Road r1, R, r2 -- итерационный вызов R
where r1.to = r2.f
);

-- теперь можно запросить эту рекурсию
select * from R;
</syntaxhighlight>

Чтобы рекурсия не выполнялась вечно, должна быть соблюдена ''монотонность'' - на каждом шаге итерации вычисляемое значение должно только пополняться, из него не должны исчезать ранее определённые кортежи. Чтобы это выполнялось, запрещается в вычисляемой части использовать <code>DISTINCT</code>, <code>GROUP BY</code>, <code>EXCEPT</code>, <code>INTERSECT</code> и другие функции агрегирования.

==== Преобразование ====

<code>PIVOT</code> - преобразование столбца в строку

Исходная таблица:
{| class="wikitable"
|+ Tab
! year !! mounth !! cnt
|- align="center"
| 2007 || Jan || 10
|- align="center"
| 2008 || Feb || 20
|}

Получится:
<syntaxhighlight lang="sql">
select year, Jan, Feb ...
from (select year, mounth, cnt from Tab) t -- t - это псевдоним подзапроса
PIVOT(sum(cnt) for mounth in (Jan, Feb ...)) p -- p - это псевдоним PIVOT'а
</syntaxhighlight>

{| class="wikitable"
! year !! Jan !! Feb !! ...
|- align="center"
| 2007 || 100 || 250 || ...
|- align="center"
| 2008 || ... || ... || ...
|}

==== Ранжирование ====

<syntaxhighlight lang="sql">
select
row_number() over (order by name) as N1,
rank() over (order by name) as N2,
dense_rank() over (order by name) as N3,
ntile(3) over (order by name) as N4,
name
from Tab;
</syntaxhighlight>

{| class="wikitable"
|+ Tab
! N1 !! N2 !! N3 !! N4 !! name
|- align="center"
| 1 2 || 1 2 || 1 2 || 1 1 || A B
|- align="center"
| 3 4 || 3 3 || 3 3 || 1 2 || C C
|- align="center"
| 5 6 || 5 5 || 4 4 || 2 3 || D D
|- align="center"
| 7 || 7 || 5 || 3 || E
|}

==== DDL-триггеры ====

Триггеры на изменение схемы данных.

<syntaxhighlight lang="sql">
create trigger имя on <database, all server>
for событие CREATE_TABLE
after ...
as
-- тело события
...
eventdata(); -- возвращает XML
</syntaxhighlight>

==== Сложные типы данных ====

Объявление:
<syntaxhighlight lang="sql">
create teble tab(
ia int,
ab int array[3], -- массив
mc int multiset, -- мультимножество
r row(r1 int, r2 char(3)); -- структура
);
</syntaxhighlight>

Добавление значений:
<syntaxhighlight lang="sql">
insert into tab
values(
10,
array[1, 2, 3],
multiset(1, 1, 5, 7, 7)
(15, 'abc');
</syntaxhighlight>

Получение значений:
<syntaxhighlight lang="sql">
select
ia,
ab[1], ab[2],
r.r1, r.r2
from tab;
</syntaxhighlight>

Для работы со структурами есть множество встроенных функций.

===== Создание нового типа данных =====

Они же пользовательские типы данных (UDT).

<syntaxhighlight lang="sql">
create type Addr as
(
city char(10),
str char(20) defailt ''
)

-- объявление метода
method fulladdr() returns char(30);

-- определение метода
create method fulladdr() returns char(30)
for Addr
begin
-- туловко метода
end;
</syntaxhighlight>

[[Категория:Постреляционные базы данных (9 семестр)]]
[[Категория:Конспекты лекций и семинаров]]

Кафедра ИУ5 МГТУ им. Н.Э.Баумана, студенческое сообщество - Вклад [ru]

ПБД (9) - Лекция №7 - SQL (продолжение)