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

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;