Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
PAGE 3
14 Курсори
Повернені курсори SQL доцільно застосовувати тоді, коли маємо порівняно великі команди, тоді команда виклику функції буде коротшою.
Ім’я порталу, який використовується для курсора, може бути задане програмістом або генерується автоматично. Для вказання назви порталу слід присвоїти рядок refcursor змінній перед його відкриттям. У прикладі нижче показаний один із способів виклику курсора:
CREATE OR REPLACE FUNCTION p_kurs(refcursor, integer) RETURNS refcursor AS
$$
DECLARE
kod ALIAS FOR $2;
BEGIN
OPEN $1 FOR SELECT * FROM gazpr WHERE kod_g > kod;
RETURN $1;
END;
$$
LANGUAGE plpgsql;
BEGIN;
SELECT p_kurs('funccursor', 2);
Для перегляду поверненого запиту можна застосовувати команди FETCH та MOVE мови SQL. Приклади:
FETCH ALL IN funccursor; -- решта записів від поточного
MOVE first FROM funccursor; -- на 1-й запис
MOVE prior FROM funccursor;-- на один запис назад (до переду)
Програма PL/pgSQL дозволяє створювати та використовувати курсори декількох типів. Розглянемо один із них порівняно найбільш вживаний.
Оголошення курсора разом із курсорною змінною відбувається в програмному блоці DECLARE, змінна має тип REFCURSOR. Оголошення має такий вигляд:
ім’я_курсора [[ NO ] SCROLL ] CURSOR [( аргументи ) ] FOR запит;
FOR можна замінити на IS для сумісності з Oracle. Якщо SCROLL вказаний, то курсор буде здатний до прокрутки назад. Якщо вказано NO SCROLL, то зворотна вибірка буде відхилена, якщо ні одна специфікація не вказана, то дозвіл на читання вибірки назад буде залежати від запиту. Аргументи, перелік яких дається через кому, визначають імена, які повинні бути замінені значеннями параметрів запиту. Фактичні значення для заміни цих імен буде уточнено після відкриття курсора.
Відкриття курсора. Перед використанням курсора його необхідно відкрити, це еквівалентно дії команди SQL DECLARE CURSOR. Pl/pgSQL має три форми відкриття, дві з яких незв’язані з курсорними змінними, а третя використовує зв’язану змінну курсора.
Курсор не може бути відкритим під час оголошення. Він повинен бути оголошений як незв’язана змінна курсора (тобто, як проста змінна типу REFCURSOR). Запит повинен бути SELECT або інший, який повертає рядки (наприклад, EXPLAIN). Запит PL/pgSQL такий же, як і інші команди SQL: в PL/pgSQL імена змінних замінюються, і план запиту кешується для можливого повторного використання. Варіанти SCROLL і NO SCROLL мають те ж значення, що й для зв’язаного курсора. Команда відкриття курсора має такий вигляд:
OPEN ім’я_курсора [(змінні аргументів)];
Використання курсора дозволено після його відкриття командою:
FETCH [напрямок {FROM | IN}] курсор INTO ціль;
FETCH витягує наступний рядок з курсора в ціль, яка може бути рядком змінних, записом або розділеним комами списком простих змінних, так же як і в SELECT INTO. Як і в SELECT INTO, спеціальна змінна може бути перевірена на наявність – FOUND. Напрямок може бути будь-яким із варіантів дозволених командою SQL FETCH, крім тих, які можуть видавати більше, ніж однин рядок, а саме: NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, або BACKWARD. Напрямок NEXT опускається. Приклади:
Переміщення курсора забезпечує команда:
MOVE [напрямок {FROM | IN}] ім’я_курсора;
MOVE переміщує курсор без отримання будь-яких даних. MOVE працює так же, як у команді FETCH, за винятком того, що курсор не повертає рядків. Як і в SELECT INTO та FETCH, відбувається перевірка чи існує рядок, на який потрібно перейти. Напрямок може бути будь-яким із дозволених командою SQL FETCH, а саме: NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, ALL, FORWARD [count | ALL], або BACKWARD [count | ALL]. Напрямок NEXT приймається за замовчуванням.
Закритя курсора:
CLOSE ім’я_курсора;
CLOSE закриває портал основного відкритого курсора. Це забезпечує звільнення курсорної змінної чи інших ресурсів.
Приклад перший, спочатку видамо запит про підрахунок кількості транспортованого газу всіма підприємствами заданого газопроводу:
SELECT pidpr.naz_p, SUM(oblik.kilk) FROM pidpr
INNER JOIN oblik ON pidpr.kod_p = oblik.kod_p
WHERE oblik.kod_g=2
GROUP BY pidpr.naz_p
ORDER BY pidpr.naz_p;
Гадячгазпром 7
Львівгазпром 4
Нехай складемо програму із застосуванням курсора для підрахунку кількості транспортованого газу всіма підприємствами заданого газопроводу. Як видно, курсор відрізняється від запиту тим, що його можна читати у потрібному порядку, застосувавши команду FETCH. Приклад:
CREATE OR REPLACE FUNCTION kurs_zvit(integer)
RETURNS text AS
$$
DECLARE
name_p pidpr.naz_p %TYPE; kilkist oblik.kilk %TYPE; name_g gazpr.naz_g %TYPE;
slovo TEXT; kod ALIAS FOR $1;
kursor CURSOR FOR
SELECT pidpr.naz_p, SUM(oblik.kilk) FROM pidpr
INNER JOIN oblik ON pidpr.kod_p = oblik.kod_p
WHERE oblik.kod_g=kod
GROUP BY pidpr.naz_p
ORDER BY pidpr.naz_p;
BEGIN
SELECT INTO name_g naz_g FROM gazpr WHERE kod_g = kod;
slovo:='Кількість транспортованого газу по газопроводу '||name_g||E'\n'||E'\n';
slovo:=slovo ||'Підприємство Кількість'||E'\n';
OPEN kursor;
LOOP
FETCH kursor INTO name_p, kilkist;
exit when not found;
slovo:=slovo||name_p||' '||TO_CHAR(kilkist,'99.9')||E'\n';
END LOOP;
CLOSE kursor;
RETURN slovo;
END;
$$
LANGUAGE plpgsql;
select kurs_zvit(2);
Кількість транспортованого газу по газопроводу Єлецьк-Орел
Підприємство Кількість
Гадячгазпром 7.0
Львівгазпром 4.0
Приклад другий, спочатку видамо запит про сумарну кількість газу транспортованим усіма газопроводами і підприємствами:
SELECT oblik.kod_g, gazpr.naz_g, oblik.kod_p, pidpr.naz_p, sum(oblik.kilk) AS kilkist FROM oblik
INNER JOIN gazpr ON(oblik.kod_g=gazpr.kod_g)
INNER JOIN pidpr ON(oblik.kod_p=pidpr.kod_p)
GROUP BY oblik.kod_g, gazpr.naz_g, oblik.kod_p, pidpr.naz_p
ORDER BY gazpr.naz_g;
4 Гадяч-Париж 2 Гадячгазпром 5
2 Єлецьк-Орел 2 Гадячгазпром 7
2 Єлецьк-Орел 3 Львівгазпром 4
1 Україна 2 Гадячгазпром 3
3 Уренгой-Помари-Ужгород 2 Гадячгазпром 3
3 Уренгой-Помари-Ужгород 3 Львівгазпром 5
Нехай складемо програму, яка застосує курсор про сумарну кількість газу транспортованими всіма газопроводами і підприємствами. Як видно, курсор відрізняється від запиту тим, що його можна читати у потрібному порядку. Приклад:
CREATE OR REPLACE FUNCTION kurs_rozpod() RETURNS text AS
$BODY$
DECLARE
slovo TEXT; s oblik.kilk %TYPE;
i integer;
kursor CURSOR FOR
SELECT oblik.kod_g, gazpr.naz_g, oblik.kod_p, pidpr.naz_p, sum(oblik.kilk) AS kilkist FROM oblik
INNER JOIN gazpr ON(oblik.kod_g=gazpr.kod_g)
INNER JOIN pidpr ON(oblik.kod_p=pidpr.kod_p)
GROUP BY oblik.kod_g, gazpr.naz_g, oblik.kod_p, pidpr.naz_p
ORDER BY gazpr.naz_g;
nazvap pidpr.naz_p %TYPE; kilkist oblik.kilk %TYPE; nazvag gazpr.naz_g %TYPE;
kodp pidpr.kod_p %TYPE; kodg gazpr.kod_g %TYPE;
BEGIN
slovo:=repeat(' ', 12)||'Результати виконання курсора'||E'\n';
slovo:=slovo ||'Газопровід'||repeat(' ', 15)||'Підприємство'||' Кількість'|| E'\n';
OPEN kursor;
LOOP
FETCH next FROM kursor INTO kodg, nazvag, kodp, nazvap, kilkist;
exit when not found;
slovo:=slovo || rpad(nazvag,25)||rpad(nazvap,15)||rpad(TO_CHAR(kilkist,'99'),16)||E'\n';
END LOOP;
CLOSE kursor;
RETURN slovo;
END;
$BODY$
LANGUAGE plpgsql;
SELECT kurs_rozpod();
Результати виконання курсора
Газопровід Підприємство Кількість
Гадяч-Париж Гадячгазпром 5
Єлецьк-Орел Гадячгазпром 7
Єлецьк-Орел Львівгазпром 4
Україна Гадячгазпром 3
Уренгой-Помари-Ужгород Гадячгазпром 3
Уренгой-Помари-Ужгород Львівгазпром 5