ПБД (9) - Лекция №7 - SQL (продолжение): различия между версиями
ILobster (обсуждение | вклад) м (→Cursor) |
ILobster (обсуждение | вклад) |
||
Строка 203: | Строка 203: | ||
ia int, | ia int, | ||
ab int array[3], -- массив | ab int array[3], -- массив | ||
mc int multiset, | mc int multiset, -- мультимножество | ||
r row(r1 int, r2 char(3)); -- структура | r row(r1 int, r2 char(3)); -- структура | ||
); | ); |
Версия от 17:44, 31 октября 2012
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;
Вызов процедуры:
call someProc(10, 'abc');
Функции
Функции похожи на хранимые процедуры, но отличаются:
- обязана возвращать значение;
- все параметры только входящие.
Важно, что return
не возвращает управление.
create function func(a int, b char(2)) returns int
declare ...
begin
-- тело функции
end;
Функции вызываются из запросов. Функции могут быть:
- скалярные - возвращают одно значение (обращение к полю):
select a, func1(a) from T
- табличные - возвращают набор записей (обращение к таблице);
select a, b from func2('...')
Cursor
Это итератор по строкам результата запроса (как QSqlRecord в QSqlQueryModel):
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;
Перехват исключений в где-то
declare <undo, exit, continue> handler
for состояние1, состояние2, состояние3
-- действие
Перехват исключений в Microsoft SQL Server
-- ловля исключений
begin try
-- какие-нибудь действия
end try;
-- блок обработчиков
begin catch
select error_number(), error_message() ...
end catch;
Вызов исключений:
raiserror(код сообщения, серьёзность, состояние)
Расширения SQL(99)
Рекурсия
Рекурсия выполняет первую итерацию, добавляет её результаты во вторую, выполняет вторую... и так далее, пока будут просходить изменения.
-- просто таблица
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;
Чтобы рекурсия не выполнялась вечно, должна быть соблюдена монотонность - на каждом шаге итерации вычисляемое выражение должно только выполняться, из него не должны исчезать ранее определённые кортежи. Чтобы это выполнялось, запрещается в вычисляемой части использовать DISTINCT
, GROUP BY
, EXCEPT
, INTERSECT
и другие функции агрегирования.
Преобразование
PIVOT
- преобразование столбца в строку
Исходная таблица:
year | mounth | cnt |
---|---|---|
2007 | Jan | 10 |
2008 | Feb | 20 |
Получится:
select year, Jan, Feb ...
from (select year, mounth, cnt from Tab) t -- t - это псевдоним подзапроса
PIVOT(sum(cnt) for mounth in (Jan, Feb ...)) p -- p - это псевдоним PIVOT'а
year | Jan | Feb | ... |
---|---|---|---|
2007 | 100 | 250 | ... |
2008 | ... | ... | ... |
Ранжирование
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;
N1 | N2 | N3 | N4 | name |
---|---|---|---|---|
1 2 |
1 2 |
1 2 |
1 1 |
A B |
3 4 |
3 3 |
3 3 |
1 2 |
C C |
5 6 |
5 5 |
4 4 |
2 3 |
D D |
7 | 7 | 5 | 3 | E |
DDL-триггеры
Триггеры на изменение схемы данных.
create trigger имя on <database, all server>
for событие CREATE_TABLE
after ...
as
-- тело события
...
eventdata(); -- возвращает XML
Сложные типы данных
Объявление:
create teble tab(
ia int,
ab int array[3], -- массив
mc int multiset, -- мультимножество
r row(r1 int, r2 char(3)); -- структура
);
Добавление значений:
insert into tab
values(
10,
array[1, 2, 3],
multiset(1, 1, 5, 7, 7)
(15, 'abc');
Получение значений:
select
ia,
ab[1], ab[2],
r.r1, r.r2
from tab;
Для работы со структурами есть множество встроенных функций.
Создание нового типа данных
Они же пользовательские типы данных (UDT).
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;