Программная начинка базы данных

Современные БД хранят в себе не только таблицы с данными, но и содержат программную логику, которую выбранная таблица обрабатывает.

Разберем пример, подсчитаем, сколько экземпляров каждой книги было продано:

--с использованием подзапросов
SELECT Title_ID, Title, (
   SELECT IsNull(Sum(Qty),0)
   FROM Sales
   WHERE Title-ID=Titles.Title_ID)
   AS Quantity
FROM Titles
--с использованием join
SELECT Titles.Title_ID, Title, IsNull(Sum(Qty),0)
FROM Titles LEFT JOIN Sales ON Titles_Title.ID=Sales.Title_ID
GROUP BY Titles.Title_ID, Title

Могут часто возникать ситуации, когда мне нужно будет знать число проданных книг, и каждый раз считать, используя верхний код, неудобно.

Можно наш запрос отладить и сохранить в БД для дальнейшего использования. Это удобно, чтобы каждый раз не писать запрос.

Использование программной начинки — VIEW

Усложним пример выше, найдем книги, в названии которых есть буква ‘A’, которых было продано более 5 штук:

--запрос с применением join
SELECT Titles.Title_ID, Title, IsNull(Sum(Qty),0)
FROM Titles LEFT JOIN Sales ON Titles_Title.ID=Sales.Title_ID
WHERE Title LIKE '%a%'
GROUP BY Titles.Title_ID, Title
HAVING IsNull(Sum(Qty),0)>5
--с использованием програмной начинки
SELECT *
FROM MyView1
WHERE Title LIKE '%a%' AND Quattity>5

Во втором случае (код выше) предыдущему запросу мы дали имя MyView1, т.е. сохранили его в БД. Как сохранить:

--используем конструкцию CREATE VIEW ... AS
CREATE VIEW MyView1 AS
  SELECT Title_ID, Title, (
     SELECT IsNull(Sum(Qty),0)
     FROM Sales
     WHERE Title-ID=Titles.Title_ID)
     AS Quantity
  FROM Titles

Если использовать VIEW, то выбранный select сохраняется в БД. И к нему можно обращаться как к готовой таблице.

В качестве VIEW можно сохранить только 1 select. Нельзя сохранить update или несколько select. При этом при усложнении запроса, можно использовать несколько view или вложенные view. В итоге очень сложный запрос будет состоять из множества простых.

Использование программной начинки — PROCEDURE

Второй способ заключается в том, что мы сохраняем запрос select не как view, а как хранимую процедуру. Пример:

CREATE PROCEDURE MyView1 AS
  SELECT Title_ID, Title, (
     SELECT IsNull(Sum(Qty),0)
     FROM Sales
     WHERE Title-ID=Titles.Title_ID)
     AS Quantity
  FROM Titles

Здесь наш запрос сохранился в виде подпрограммы, как в языках программирования. И сервер не может этот запрос встроить в больший запрос, он просто компилирует его как подпрограмму и может передать ей управление.

Если в будущем мне надо будет усложнить запрос, это усложнение будет внутри процедуры, т.е. надо переписать всю процедуру, внести все усложнения внутри нее. Это сложно.

ИТОГ: использование VIEW удобно для разработчика, когда мы развиваем свой продукт, а PROCEDURE — для конечного пользователя.

В процедурах можно использовать параметры для задания доп. возможностей.

---