Задания для самостоятельной работы

1. Создайте таблицу User_SP для хранения списка хранимых процедур в БД BookShop. Таблица должна содержать следующие поля:

• Имя SP

• Владелец

• Описание

• Входные параметры

• Выходные параметры

• Другие результаты

• Вызывается из (список вызывающих SP)

• Вызывает (список вызываемых SP)

• История обновлений (с момента создания):

Создайте набор SP, предназаначенных для вставки, удаления и модификации таблицы User_SP.

Указаие: При удалении функции список параметров должен быть специфицирован (PostgreSQL поддерживает перегрузку функций). При удалении функции должны быть удалены все связанные с ней объекты.

2. Создайте SP, предназаначенныe для генерации в БД BookShop следующих отчетов:

Роль	Отчеты
Директор	Отчет по продажам: показывает тенденции изменения спроса за последние 6 месяцев для книг, объем продаж которых за указанный период изменился более, чем на 100%.
Закупщик	Отчет по поставщикам: показывает тенденции изменения цен с начала текущего года для поставщиков, объемы поставок которых за указанный период не уменьшились.

II. Pl/pgSql-функции

2.3 Создание pl/pgSql-функции

Недостатком SQL-функций является отсутствие средств управления программой. Процедурный язык PL/pgSQL дает возможность использовать в SQL-запросах стандартные методы программирования, такие как выполнение циклов и анализ значений переменных.

PL/pgSQL-функции имеют модульную структуру:

CREATE FUNCTION имя(параметры) RETURNS тип AS $$

[<< метка_основного_блока>>]

DECLARE /* объявления * /

…

BEGIN /* основной блок * /

… ;

[<< метка_вложенного_блока>>]

DECLARE /* объявления * /

… ;

BEGIN /* вложенный блок * /

… ;

END [метка_вложенного_блока];

… ;

END [метка_основного_блока] ;

$$ LANGUAGE plpgsql;

Объявления переменных

Синтаксис выражения объявления переменных имеет вид:

имя [ CONSTANT ] тип [ NOT NULL ] [ { DEFAULT | := } выражение ];

Примеры:

user_id integer DEFAULT 0;

price numeric(5.2);

name varchar (32) :='foo’;

/* переменная row имеет тип строки таблицы table */

row table%ROWTYPE;

/* переменная field имеет тип столбца column таблицы table */

field table.column%TYPE;

/* переменная х типа запись (строка неопределенной струкутры */

х RECORD;;

Операторы управления программой

Внутри PL/pgSQL-функции можно использовать любые инструкции SQL, а также операторы управления программой, к которым относятся:

• Операторы выхода из функции, возвращающей скалярное значение или набор значений

RETURN, RETURN NEXT, RETURN QUERY

• Условные операторы

IF...ELSE

CASE ... WHEN

• Циклические операторы

LOOP  безусловный цикл

WHILE  цикл с предусловием

FOR  цикл со счетчиком

EXIT  выход из цикла

CONTINUE  переход на следующую итерацию

• Вывод сообщений и сообщений об ошибках

RAISE

Примеры:

/* Удаление неоплаченных заказов из таблицы Заказы БД BookShop */

CREATE FUNCTION clear_unpaid_orders ( )

RETURNS integer AS

$$

DECLARE

n integer := 0;

BEGIN

-- определяем количество неоплаченных заказов и записываем в n i

SELECT count(*) INTO n FROM “Заказы” WHERE “Оплачен” = false;

-- если таких заказов нет, завершаем вызов функции

IF n =0 THEN RETURN 0;

END IF;

-- в противном случае  удаляем

DELETE FROM “Заказы” WHERE “Оплачен” = false;

-- и выводим сообщение о количестве удаленных срок

RAISE NOTICE 'Удалено % строк', n;

RETURN n;

END;

$$

LANGUAGE plpgsql;