ПБД (9) - Лекция №7 - SQL (продолжение): различия между версиями

Материал из Кафедра ИУ5 МГТУ им. Н.Э.Баумана, студенческое сообщество
Перейти к навигации Перейти к поиску
(→‎Рекурсия: поправка)
 
Строка 126: Строка 126:
</syntaxhighlight>
</syntaxhighlight>


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


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

Текущая версия от 09:17, 21 ноября 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 - преобразование столбца в строку

Исходная таблица:

Tab
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;
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;