Хранимые процедуры

Предметом этой главы является один из наиболее мощных инструментов, предлагаемых разработчикам приложений баз данных InterBase для реализации бизнес-логики Хранимые процедуры (англ, stoied proceduies) позволяют реализовать значительную часть логики приложения на уровне базы данных и таким образом повысить производительность всего приложения, централизовать обработку данных и уменьшить количество кода, необходимого для выполнения поставленных задач Практически любое достаточно сложное приложение баз данных не обходится без использования хранимых процедур.
Помимо этих широко известных преимуществ использования хранимых процедур, общих для большинства реляционных СУБД, хранимые процедуры InterBase могут играть роль практически полноценных наборов данных, что позволяет использовать возвращаемые ими результаты в обычных SQL-запросах.
Часто начинающие разработчики представляют себе хранимые процедуры просто как набор специфических SQL-запросов, которые что-то делают внутри базы данных, причем бытует мнение, что работать с хранимыми процедурами намного сложнее, чем реализовать ту же функциональность в клиентском приложении, на языке высокого уровня
Так что же такое хранимые процедуры в InterBase?
Хранимая процедура (ХП) - это часть метаданных базы данных, представляющая собой откомпилированную во внутреннее представление InterBase подпрограмму, написанную на специальном языке, компилятор которого встроен в ядро сервера InteiBase
Хранимую процедуру можно вызывать из клиентских приложений, из триггеров и других хранимых процедур. Хранимая процедура выполняется внутри серверного процесса и может манипулировать данными в базе данных, а также возвращать вызвавшему ее клиенту (т е триггеру, ХП, приложению) результаты своего выполнения
Основой мощных возможностей, заложенных в ХП, является процедурный язык программирования, имеющий в своем составе как модифицированные предложения обычного SQL, такие, как INSERT, UPDATE и SELECT, так и средства организации ветвлений и циклов (IF, WHILE), а также средства обработки ошибок и исключительных ситуаций Язык хранимых процедур позволяет реализовать сложные алгоритмы работы с данными, а благодаря ориентированности на работу с реляционными данными ХП получаются значительно компактнее аналогичных процедур на традиционных языках.
Надо отметить, что и для триггеров используется этот же язык программирования, за исключением ряда особенностей и ограничений. Отличия подмножества языка, используемого в триггерах, от языка ХП подробно рассмотрены в главе "Триггеры" (ч 1).

Пример простой хранимой процедуры

Настало время создать первую хранимую процедуру и на ее примере изучить процесс создания хранимых процедур. Но для начала следует сказать несколько слов о том, как работать с хранимыми процедурами Дело в том, что своей славой малопонятного и неудобного инструмента ХП обязаны чрезвычайно бедным стандартным средствам разработки и отладки хранимых процедур. В документации по InterBase рекомендуется создавать процедуры с помощью файлов SQL-скриптов, содержащих текст ХП, которые подаются на вход интерпретатору isql, и таким образом производить создание и модификацию ХП Если в этом SQL-скрипте на этапе компиляции текста процедуры в BLR (о BLR см главу "Структура базы данных InterBase" (ч. 4)) возникнет ошибка, то isql выведет сообщение о том, на какой строке файла SQL-скрипта возникла эта ошибка. Исправляйте ошибку и повторяйте все сначала. Про отладку в современном понимании этого слова, т. е. о трассировке выполнения, с возможностью посмотреть промежуточные значения переменных, речь вообще не идет. Очевидно, что такой подход не способствует росту привлекательности хранимых процедур в глазах разработчика
Однако помимо стандартного минималистского подхода к разработке ХП <_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Синтаксис хранимых процедур описывается следующим образом:

CREATE PROCEDURE name
[ (param datatype [, param datatype ...]) ]
)]
AS
;
< procedure_body> = []
< block>
< vanable_declaration_list> =
DECLARE VARIABLE var datatype;

=
BEGIN
< compound_statement>
[< compound_statement> ...]
END
< compound_statement> = ( statement;}

Выглядит довольно объемно и может быть даже громоздко, но на самом деле все очень просто Для того чтобы постепенно освоить синтаксис, давайте будем рассматривать постепенно усложняющиеся примеры.
Итак, вот пример очень простой хранимой процедуры, которая принимает на входе два числа, складывает их и возвращает полученный результат:

CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
second_arg DOUBLE PRECISION)
RETURNS (Result DOUBLE PRECISION)
AS
BEGIN
Result=first_arg+second_arg;
SUSPEND;
END

Как видите, все просто: после команды CREATE PROCEDURE указывается имя вновь создаваемой процедуры (которое должно быть уникальным в пределах базы данных) - в данном случае SP_Add, затем в скобках через запятую перечисляются входные параметры ХП - first_arg и second_arg - с указанием их типов.
Список входных параметров является необязательной частью оператора CREATE PROCEDURE - бывают случаи, когда все данные для своей работы процедура получает посредством запросов к таблицам внутри тела процедуры.

В хранимых процедурах используются любые скалярные типы данных InteiBase He предусмотрено применение массивов и типов, определяемых пользователем, - доменов

Далее идет ключевое слово RETURNS, после которого в скобках перечисляются возвращаемые параметры с указанием их типов - в данном случае только один - Result.
Если процедура не должна возвращать параметры, то слово RETURNS и список возвращаемых параметров отсутствуют.
После RETURNSQ указано ключевое слово AS. До ключевого слова AS идет заголовок, а после него - течо процедуры.
Тело хранимой процедуры представляет собой перечень описаний ее внутренних (локальных) переменных (если они есть, подробнее рассмотрим ниже), разделяемый точкой с запятой (;), и блок операторов, заключенный в операторные скобки BEGIN END. В данном случае тело ХП очень простое - мы просю складываем два входных аргумента и присваиваем их результат выходному, а затем вызываем команду SUSPEND. Чуть позже мы разъясним суть действия этой команды, а пока лишь отметим, что она нужна для передачи возвращаемых параметров туда, откуда была вызвана хранимая процедура.

Разделители в хранимых процедурах

Обратите внимание, что оператор внутри процедуры заканчивается точкой с запятой (;). Как известно, точка с запятой является стандартным разделителем команд в SQL - она является сигналом интерпретатору SQL, что текст команды введен полностью и надо начинать его обрабатывать. Не получится ли так, что, обнаружив точку с запятой в середине ХП, интерпретатор SQL сочтет, что команда введена полностью и попытается выполнить часть хранимой процедуры? Это предположение не лишено смысла. Действительно, если создать файл, в который записать вышеприведенный пример, добавить команду соединения с базы данных и попытаться выполнить этот SQL-скрипт с помощью интерпретатора isql, то будет возвращена ошибка, связанная с неожиданным, по мнению интерпретатора, окончанием команды создания хранимой процедуры. Если создавать хранимые процедуры с помощью файлов SQL-скриптов, без использования специализированных инструментов разработчика InterBase, то необходимо перед каждой командой создания ХП (то же относи 1ся и к триггерам) менять разделитель команд скрипта на другой символ, отличный от точки с запятой, а после текста ХП восстанавливать его обратно. Команда isql, изменяющая разделитель предложений SQL, выглядит так:

SET TERM

Для типичного случая создания хранимой процедуры это выглядит так:

SET TERM ^;
CREATE PROCEDURE some_procedure
... . .
END
^
SET TERM ;^

Вызов хранимой процедуры

Но вернемся к нашей хранимой процедуре. Теперь, когда она создана, ее надо как-то вызвать, передать ей параметры и получить возвращаемые результаты. Это сделать очень просто - достаточно написать SQL-запрос следующего вида:

SELECT *
FROM Sp_add(181.35, 23.09)

Этот запрос вернет нам одну строку, содержащую всего одно поле Result, в котором будет находиться сумма чисел 181.35 и 23.09 т. е. 204.44.
Таким образом, нашу процедуру можно использовать в обычных SQL- запросах, выполняющихся как в клиентских программах, так и в других ХП или триггерах. Такое использование нашей процедуры стало возможным из-за применения команды SUSPEND в конце хранимой процедуры.
Дело в том, что в InterBase (и во всех его клонах) существуют два типа хранимых процедур: процедуры-выборки (selectable procedures) и исполняемые процедуры (executable procedures). Отличие в работе этих двух видов ХП заключается в том, что процедуры-выборки обычно возвращают множество наборов выходных параметров, сгруппированных построчно, которые имеют вид набора данных, а исполняемые процедуры мог)т либо вообще не возвращать параметры, либо возвращать только один набор выходных параметров, перечисленных в Returns, где одну строку параметров. Процедуры-выборки вызываются в запросах SELECT, а исполняемые процедуры - с помощью команды EXECUTE PROCEDURE.
Оба вида хранимых процедур имеют одинаковый синтаксис создания и формально ничем не отличаются, поэтому любая исполнимая процедура может быть вызвана в SELECT-запросе и любая процедура-выборка - с помощью EXECUTE PROCEDURE. Вопрос в том, как поведут себя ХП при разных типах вызова. Другими словами, разница заключается в проектировании процедуры для определенного типа вызова. То есть процедура-выборка специально создается для вызова из запроса SELECT, а исполняемая процедура - для вызова с использованием EXECUTE PROCEDURE. Давайте рассмотрим, в чем же заключаются отличия при проектировании этих двух видов ХП.
Для того чтобы понять, как работает процедура-выборка, придется немного углубиться в теорию. Давайте представим себе обычный SQL-запрос вида SELECT ID, NAME FROM Table_example. В результате его выполнения мы получаем на выходе таблицу, состоящую из двух столбцов (ID и NAME) и некоторого количества строк (равного количеству строк в таблице Table_example). Возвращаемая в результате этого запроса таблица называется также набором данных SQL Задумаемся же, как формируется набор данных во время выполнения этого запроса Сервер, получив запрос, определяет, к каким таблицам он относится, затем выясняет, какое подмножество записей из этих таблиц необходимо включить в результат запроса. Далее сервер считывает каждую запись, удовлетворяющую результатам запроса, выбирает из нее нужные поля (в нашем случае это ID и NAME) и отсылает их клиенту. Затем процесс повторяется снова - и так для каждой отобранной записи.
Все это отступление нужно для того, чтобы уважаемый читатель понял, что все наборы данных SQL формируются построчно, в том числе и в хранимых процедурах! И основное отличие процедур-выборок от исполняемых процедур в том, что первые спроектированы для возвращения множества строк, а вторые - только для одной. Поэтому они и применяются по-разному: процедура-выборка вызывается при помощи команды SELECT, которая "требует" от процедуры отдать все записи, которая она может вернуть. Исполняемая процедура вызывается с помощью EXECUTE PROCEDURE, которая "вынимает" из ХП только одну строку, а остальные (даже если они есть!) игнорирует.
Давайте рассмотрим пример процедуры-выборки, чтобы было понятнее. Для > прощения создадим хранимую процедуру, которая работает точно так же, как запрос SELECT ID, NAME FROM Table_Example, т е она просто делает выборку полей ID и NAME из всей таблицы. Вот этот пример:

CREATE PROCEDURE Simple_Select_SP
RETURNS (
procID INTEGER,
procNAME VARCHAR(80))
AS
BEGIN
FOR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
BEGIN
SUSPEND;
END
END

Давайте разберем действия этой процедуры, названной Simple_Select_SP. Как видите, она не имеет входных параметров и имеет два выходных параметра - ID и NAME. Самое интересное, конечно, заключено в теле процедуры. Здесь использована конструкция FOR SELECT:

FOR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
BEGIN

/*что-то делаем с переменными procID и procName*/

END

Этот кусочек кода означает следующее: для каждой строки, выбранной из таблицы Table_example, поместить выбранные значения в переменные procID и procName, а затем произвести какие-то действия с этими переменными.
Вы можете сделать удивленное лицо и спросить: "Переменные? Какие еще переменные 9 " Это нечто вроде сюрприза этой главы - то, что в хранимых процедурах мы можем использовать переменные. В языке ХП можно объявлять как собственные локальные переменные внутри процедуры, так и использовать входные и выходные параметры в качестве переменных.
Для того чтобы объявить локальную переменную в хранимой процедуре, необходимо поместить ее описание после ключевого слова AS и до первого слова BEGIN Описание локальной переменной выглядит так:

DECLARE VARIABLE ;

Например, чтобы объявить целочисленную локальную переменную Mylnt, нужно вставить между AS и BEGIN следующее описание

DECLARE VARIABLE Mylnt INTEGER;

Переменные в нашем примере начинаются с двоеточия. Это сделано потому, что обращение к ним идет внутри SQL-команды FOR SELECT, поэтому для различения полей в таблицах, которые используются в SELECT, и переменных необходимо предварять последние двоеточием. Ведь переменные могут иметь точно такое же название, как и поля в таблицах!
Но двоеточие перед именем переменной необходимо использовать только внутри SQL-запросов. Вне текстов обращение к переменной делается без двоеточия, например:

procName="Some name";

Но вернемся к телу нашей процедуры. Предложение FOR SELECT возвращает данные не в виде таблицы - набора данных, а по одной строчке. Каждое возвращаемое поле должно быть помещено в свою переменную: ID => procID, NAME => procName. В части DO эти переменные посылаются клиенту, вызвавшем) процед>р>, с помощью команды SUSPEND
Таким образом, команда FOR SELECT... DO организует цикл по записям, выбираемым в части SELECT этой команды. В теле цикла, образуемого частью DO, выполняется передача очередной сформированной записи клиенту с помощью команды SUSPEND.
Итак, процедура-выборка предназначена для возвращения одной или более строк, для чего внутри тела ХП организуется цикл, заполняющий результирующие параметры-переменные. И в конце тела этого цикла обязательно стоит команда SUSPEND, которая вернет очередную строку данных клиенту.

Циклы и операторы ветвления

Помимо команды FOR SELECT... DO, организующей цикл по записям какой-либо выборки, существует другой вид цикла - WHILE...DO, который позволяет организовать цикл на основе проверки любых условий. Вот пример ХП, использующей цикл WHILE.. DO. Эта процедура возвращает квадраты целых чисел от 0 до 99:

CREATE PROCEDJRE QUAD
RETURNS (QUADRAT INTEGER)
AS
DECLARE VARIABLE I INTEGER;
BEGIN
I = 1;
WHILE (i<100) DO
BEGIN
QUADRAT= I*I;
I=I+1;
SUSPEND;
END
END

В результате выполнения запроса SELECT FROM QUAD мы получим таблицу, содержащую один столбец QUADRAT, в котором будут квадраты целых чисел от 1 до 99
Помимо перебора результатов SQL-выборки и классического цикла, в языке хранимых процедур используется оператор IF...THEN..ELSE, позволяющий организовать ветвление в зависимости от выполнения каких-либо \словий Его синтаксис похож на большинство операторов ветвления в языках программирования высокого уровня, вроде Паскаля и Си.
Давайте рассмотрим более сложный пример хранимой процедуры, которая делает следующее.

1. Вычисляет среднюю цену в таблице Table_example (см. глава "Таблицы Первичные ключи и генераторы")
2. Далее для каждой записи в таблице делает след>ющ)ю проверку, если существующая цена (PRICE) больше средней цены, то устанавливает цену, равную величине средней цены, плюс задаваемый фиксированный процент
3. Если существующая цена меньше или равна средней цене, то устанавливает цену, равную прежней цене, плюс половина разницы между прежней и средней ценой.
4. Возвращает все измененные строки в таблице.

Для начала определим имя ХП, а также входные и выходные параметры Все это прописывается в заголовке хранимой процедуры

CREATE PROCEDURE IncreasePrices (
Percent2lncrease DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR(SO), new_price DOUBLE
PRECISION) AS

Процедура будет называться IncreasePrices, у нее один входной параметр Peiceni21nciease, имеющий тип DOUBLE PRECISION, и 3 выходных параметра - ID, NAME и new_pnce. Обратите внимание, что первые два выходных параметра имеют такие же имена, как и поля в таблице Table_example, с которой мы собираемся работать Это допускается правилами языка хранимых процедур.
Теперь мы должны объявить локальную переменную, которая будет использоваться для хранения среднего значения Эго объявление будет выглядеть следующим образом:

DECLARE VARIABLE avg_price DOUBLE PRECISION;

Теперь перейдем к телу хранимой процедуры Откроем тело ХП ключевым словом BEGIN.
Сначала нам необходимо выполнить первый шаг нашего алгоритма - вычислить среднюю цену. Для этого мы воспользуемся запросом следующего вида:

SELECT AVG(Price_l)
FROM Table_Example
INTO:avg_price,-

Этот запрос использует агрегатную функцию AVG, которая возвращает среднее значение поля PRICE_1 среди отобранных строк запроса - в нашем случае среднее значение PRICE_1 по всей таблице Table_example. Возвращаемое запросом значение помещается в переменную avg_price. Обратите внимание, что переменная avg_pnce предваряется двоеточием -для того, чтобы отличить ее от полей, используемых в запросе.
Особенностью данного запроса является то, что он всегда возвращает строго одну-единственную запись. Такие запросы называются singleton-запросами И только такие выборки можно использовать в хранимых процедурах. Если запрос возвращает более одной строки, то его необходимо оформить в виде конструкции FOR SELECT...DO, которая организует цикл для обработки каждой возвращаемой строки
Итак, мы получили среднее значение цены. Теперь необходимо пройтись по всей таблице, сравнить значение цены в каждой записи со средней ценой и предпринять соответствующие действия
С начала opганизуем перебор каждой записи из таблицы Table_example

FOR
SELECT ID, NAME, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*_здесь оОрсшатыьаем каждую запись*/
END

При выполнении этой конструкции из таблицы Table_example построчно будут выниматься данные и значения полей в каждой строке будут присвоены переменным ID, NAME и new_pnce. Вы, конечно, помните, что эти переменные объявлены как выходные параметры, но беспокоиться, что выбранные данные будут возвращены как результаты, не стоит: тот факт, что выходным параметрам что-либо присвоено, не означает, что вызывающий ХП клиент немедленно получит эти значения! Передача параметров осуществляется только при исполнении команды SUSPEND, а до этого мы можем использовать выходные параметры в качестве обычных переменных - в нашем примере мы именно так и делаем с параметром new_price.
Итак, внутри тела цикла BEGIN.. .END мы можем обработать значения каждой строки. Как вы помните, нам необходимо выяснить, как существующая цена соотносится со средней, и предпринять соответствующие действия. Эту процедуру сравнения мы реализуем с помощью оператора IF:

IF (new_price > avg_price) THEN /*если существующая цена больше средней цены*/
BEGIN
/*то установим новую цену, равную величине средней цены, плюс фиксированный процент */
new_price = (avg_price + avg_price*(Percent2Increase/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
ELSE
BEGIN
/* Если существующая цена меньше или равна средней цене, то установим цену, равную прежней цене, плюс половина разницы между прежней и средней ценой */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = .ID;
END

Как видите, получилось достаточно большая конструкция IF, в которой трудно было бы разобраться, если бы не комментарии, заключенные в символы /**/.
Для того чтобы изменить цену в соответствии с вычисленной разницей, мы воспользуемся оператором UPDATE, который позволяет модифицировать существующие записи - одну или несколько. Для того чтобы однозначно указать, в какой записи нужно изменять цену, мы используем в условии WHERE поле первичного ключа, сравнивая его со значением переменной, в которой хранится значение ID для текущей записи: ID=:ID. Обратите внимание, что переменная ID предваряется двоеточием.
После выполнения конструкции IF...THEN...ELSE в переменных ID, NAME и new_price находятся данные, которые мы должны возвратить клиент\, вызвавшему процедуру. Для этого после IF необходимо вставить команду SUSPEND, которая перешлет данные туда, откуда вызвали ХП На время пересылки действие процедуры будет приостановлено, а когда от ХП потребуется новая запись, то она будет вновь продолжена, - и так будет продолжаться до тех пор, пока FOR SELECT...DO не переберет все записи своего запроса.
Надо отметить, что помимо команды SUSPEND, которая только приостанавливает действие хранимой процедуры, существует команда EXIT, которая прекращает хранимую процедуру после передачи строки. Однако командой EXIT пользуются достаточно редко, поскольку она нужна в основном для того, чтобы прервать цикл при достижении какого-либо условия
При этом в случае, когда процедура вызывалась оператором SELECT и завершена по EXIT, последняя извлеченная строка не будет возвращена. То есть, если вам нужно прервать процедуру и все-таки >получить эту строку, надо воспользоваться последовательностью

SUSPEND;
EXIT;

Основное назначение EXIT - получение singleton-наборов данных, возвращаемых параметров путем вызова через EXECUTE PROCEDURE. В этом случае устанавливаются значения выходных параметров, но из них не формируется набор данных SQL, и выполнение процедуры завершается.
Давайте запишем текст нашей хранимой процедуры полностью, чтобы иметь возможность охватить ее логику одним взглядом:

CREATE PROCEDURE IncreasePrices (
Percent2Increase DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR(80),
new_price DOUBLE PRECISION) AS
DECLARE VARIABLE avg_price DOUBLE PRECISION;
BEGIN
SELECT AVG(Price_l)
FROM Table_Example
INTO:avg_price;
FOR
SELECT ID, NAME, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*здесь обрабатываем каждую запись*/
IF (new_pnce > avg_price) THEN /*если существующая цена больше средней цены*/
BEGIN
/*установим новую цену, равную величине средней цены, плюс фиксированный процент */
new_price = (avg_price + avg_price*(Percent2lncrease/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
ELSE
BEGIN
/* Если существующая цена меньше или равна средней цене, то устанавливает цену, равную прежней цене, плюс половина разницы между прежней и средней ценой */
new_price = (new_price + ((avg_price - new_price)/2));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
SUSPEND;
END
END

Данный пример хранимой процедуры иллюстрирует применение основных конструкций языка хранимых процедур и триггеров. Далее мы рассмотрим способы применения хранимых процедур для решения некоторых часто возникающих задач.

Рекурсивные хранимые процедуры

Хранимые процедуры InterBase могут быть рекурсивными. Это означает, что из хранимой процедуры можно вызвать саму себя. Допускается до 1000 уровней вложенности хранимых процедур, однако надо помнить о том, что свободные ресурсы на сервере могут закончиться раньше, чем будет достигнута максимальная вложенность ХП.
Одно из распространенных применений хранимых процедур - это обработка древовидных структур, хранящихся в базе данных. Деревья часто используются в задачах состава изделия, складских, кадровых и в других распространенных приложениях.
Давайте рассмотрим пример хранимой процедуры, которая выбирает все товары определенного типа, начиная с определенного уровня вложенности.
Пусть у нас есть следующая постановка задачи: имеем справочник товаров с иерархической структурой такого вида:

Товары
- Бытовая техника
- Холодильники
- Трехкамерные
- Двухкамерные
- Однокамерные
- Стиральные машины
- Вертикальные
- Фронтальные
- Классические
- Узкие
- Компьютерная техника
....

Эта структура справочника категорий товаров может иметь ветки различной глубины. а также нарастать со временем. Наша задача - обеспечить выборку всех конечных элементов из справочника с "разворачивание полного имени", начиная с любого узла. Например, если мы выбираем узел "Стиральные машины", то нам надо получить следующие категории:

Стиральные машины - Вертикальные
Стиральные машины - Фронтальные Классические
Стиральные машины - Фронтальные Узкие

Определим структуру таблиц для хранения информации справочника товаров. Используем упрощенную схему для организации дерева в одной таблице:

CREATE TABLE GoodsTree
(ID_GOOD INTEGER NOT NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR(80),
constraint pkGooci primary key (ID_GOOD));

Создаем одну таблицу GoodsTree, в которой всего 3 поля: ID_GOOD - умн кальный идентификатор категории, ID_PARENT_GOOD - идентификатор кшс гории-родителя для данной категории и GOOD_NAME - наименование катсш- рии. Чтобы обеспечить целостность данных в этой таблице, наложим на эту таблиц} ограничение внешнего ключа:

ALTER TABLE GoodsTree
ADD CONSTRAINT FK_goodstree
FOREIGN KEY (ID_PARENT_GOOD)
REFERENCES GOODSTPEE (ID__GOOD)

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

ID_GOOD 1 2 3 4 5 6 7 8 9 10 11 12

ID_PARENT_GOOD 0 1 1 2 2 4 4 4 5 5 10 10

GOOD_NAME GOODS Бытовая техника Компьютеры и комплектующие Холодильники Стиральные машины Трехкамерные Двухкамерные Однокамерные Вертикальные Фронтальные Узкие Классические

Теперь, когда у нас есть место для хранения данных, мы можем приступить к созданию хранимой процедуры, выполняющей вывод всех "окончательных" категорий товаров в "развернутом" виде - например, для категории "Трехкамерные" полное имя категории будет выглядеть как "Бытовая техника Холодильники Трехкамерные".
В хранимых процедурах, обрабатывающих древообразные структуры, сложилась своя терминология. Каждый элемент дерева называются узлом; а отношения между ссылающимися друг на друга узлами называется отношениями родитель-потомок. Узлы, находящиеся на самом конце дерева и не имеющие потомков, называются "листьями".
У кашей хранимой процедуры входным параметром будет идентификатор категории, начиная с которого мы должны будем начать развертку. Хранимая процедура будет иметь следующий вид:

CREATE PROCEDURE GETFULLNAME (ID_GOOD2SHOW INTEGER)
RETURNS (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
AS
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);
BEGIN
/*0рганизуем внешний цикл FOR SELECT по непосредственным потомкам товара с ID_GOOD=ID_GOOD2SHOW */
FOR SELECT gtl.id_good, gtl.good_name
FROM GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
DO
BEGIN
/"Проверка с помощью функции EXISTS, которая возвращает TRUE, если запрос в скобках вернет хотя бы одну строку. Если у найденного узла с ID_PARENT_GOOD = ID_CHILD_GOOD нет потомков, то он является "листом" дерева и попадает в результаты */
IF (NOT EXISTS(
SELECT * FROM GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
THEN
BEGIN
/* Передаем "лист" дерева в результаты */
SUSPEND;
END
ELSE
/* Для узлов, у которых есть потомки*/
BEGIN
/*сохраняем имя узла-родителя во временной переменной */
CURR_CHILD_NAME=full_goods_name;
/* рекурсивно запускаем эту процедуру */
FOR
SELECT ID_CHILD_GOOD, full_goods_name
FROM GETFULLNAME (:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
DO BEGIN
/*добавляем лмя узла-родителя к найденном., имени потомка с помощью операции конкатенации строк || */
full_goods_name=CURR_CHILD_NAME| " " | f ull_goods_name,-
SUSPEND; /* возвращаем полное имя товара*/
END
END
END
END

Если мы выполним данную процедуру с входным параметром ID_GOOD2SHOW= 1, то получим следующее:

Как видите, с помощью рекурсивной хранимой процедуры мы прошлись по всему дереву категорий и вывели полное наименование категорий-"листьев", которые находятся на самых кончиках ветвей.

Заключение

На этом закончим рассмотрение основных возможностей языка хранимых процедур. Очевидно, что полностью освоить разработку хранимых процедур при чтении одной главы невозможно, однако здесь мы постарались представить и объяснить основные концепции, связанные с хранимыми процедурами. Описанные конструкции и приемы проектирования ХП могут быть применены в большинстве приложений баз данных
Часть важных вопросов, связанных с разработкой хранимых процедур, будет раскрыта в следующей главе - "Расширенные возможности языка хранимых процедур InterBase", которая посвящена обработке исключений, разрешению ошибочных ситуаций в хранимых процедурах и работе с массивами.

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

Типы хранимых процедур

В SQL Server имеется несколько типов хранимых процедур .

• Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_ , хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
• Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
• Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа # . Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ## . Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Создание, изменение и удаление хранимых процедур

Создание хранимой процедуры предполагает решение следующих задач:

• определение типа создаваемой хранимой процедуры : временная или пользовательская. Кроме этого, можно создать свою собственную системную хранимую процедуру , назначив ей имя с префиксом sp_ и поместив ее в системную базу данных. Такая процедура будет доступна в контексте любой базы данных локального сервера;
• планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь;
• определение параметров хранимой процедуры . Подобно процедурам, входящим в состав большинства языков программирования, хранимые процедуры могут обладать входными и выходными параметрами ;
• разработка кода хранимой процедуры . Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур .

Создание новой и изменение имеющейся хранимой процедуры осуществляется с помощью следующей команды:

<определение_процедуры>::= {CREATE | ALTER } имя_процедуры [;номер] [{@имя_параметра тип_данных } [=default] ][,...n] AS sql_оператор [...n]

Рассмотрим параметры данной команды.

Используя префиксы sp_ , # , ## , создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

Номер в имени – это идентификационный номер хранимой процедуры , однозначно определяющий ее в группе процедур. Для удобства управления процедурами логически однотипные хранимые процедуры можно группировать, присваивая им одинаковые имена, но разные идентификационные номера.

Для передачи входных и выходных данных в создаваемой хранимой процедуре могут использоваться параметры , имена которых, как и имена локальных переменных, должны начинаться с символа @ . В одной хранимой процедуре можно задать множество параметров , разделенных запятыми. В теле процедуры не должны применяться локальные переменные, чьи имена совпадают с именами параметров этой процедуры.

Для определения типа данных, который будет иметь соответствующий параметр хранимой процедуры , годятся любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры , т.е. с указанием ключевого слова OUTPUT .

Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры . Однако это вовсе не означает, что параметр не подходит для передачи значений в хранимую процедуру . Указание ключевого слова OUTPUT предписывает серверу при выходе из хранимой процедуры присвоить текущее значение параметра локальной переменной, которая была указана при вызове процедуры в качестве значения параметра . Отметим, что при указании ключевого слова OUTPUT значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Не разрешается использование любых выражений или констант, допустимое для обычных параметров .

Ключевое слово VARYING применяется совместно с

Специалисты компании «Доктор Веб» обнаружили в магазине приложений Google Play более сотни вредоносных программных продуктов, выдающих себя за известное или полезное ПО и используемых злоумышленниками в различных мошеннических схемах.
В частности, вирусными аналитиками «Доктор Веб» были выявлены вредоносные приложения, замаскированные под официальные клиентские программы крупных торговых сетей, букмекерских контор и популярных интернет-сервисов. Подобного рода софт в изобилии представлен в Google Play и активно используется киберпреступниками для подписки обманным путём владельцев Android-смартфонов и планшетов на дорогостоящие мобильные премиум-услуги.

Схема работы мошенников довольно простая: у потенциальной жертвы запрашивается номер мобильного телефона, якобы необходимый для получения кода и вымышленного выигрыша. Однако на самом деле этот код используется для подтверждения подписки на платную услугу. Если же заражённое устройство подключено к Интернету, то после ввода номера телефона владелец Android-гаджета подписывается на премиум-услугу автоматически. Компания Google оперативно удаляет подобного рода приложения из Google Play, однако предприимчивые мошенники продолжают добавлять их в каталог почти каждый день.
«Для отъёма денег у владельцев мобильных устройств и получения иной выгоды злоумышленники применяют всевозможные уловки, а также изобретают всё новые мошеннические схемы. Именно поэтому устанавливать приложения даже из магазина Google Play необходимо с осторожностью. Стоит обращать внимание на имя разработчика, дату публикации интересующей программы, а также отзывы других пользователей. Эти простые действия помогут снизить риск заражения смартфонов и планшетов», - говорится в информационном сообщении антивирусного вендора.

От

Объединённая компания Связной | Евросеть сообщила о том, что принадлежащая ей сеть Cstore впервые в России запускает программу trade-in для портативных компьютеров Apple MacBook.
Инициатива охватывает все магазины Cstore по всей России. Чтобы воспользоваться услугой, покупателю необходимо предоставить свой MacBook на быструю оценку в любой салон Cstore. Сотрудники магазина проверят технические параметры лэптопа с использованием специального программного обеспечения, а также проведут его внешний осмотр. После этого будет названа возможная скидка на новое устройство - она может достигать 100 %.

Сдав MacBook по программе trade-in, можно приобрести любую технику Apple, в частности, планшет iPad, смартфон iPhone, часы Apple Watch, компьютер iMac и, разумеется, новый MacBook.
«Мы первыми в России запускаем программу trade-in в категории MacBook. Сstore постоянно внедряет новые сервисы для удобства клиентов, и мы ожидаем большую популярность этой услуги. Особенно учитывая, что trade-in смартфонов показывает стремительный рост - в 65 раз год к году», - заявляет руководство проекта.
Кроме того, сеть Cstore представила инициативу Back to school, позволяющую приобрести MacBook со скидкой 11 %, а iPad Pro - со скидкой 9 %. В акции смогут принять участие студенты и преподаватели вузов.

От

Более 2 миллионов чешских зрителей теперь пользуются HbbTV, причем наибольшее число пользователей проживает в столице, Праге.
Согласно выводам исследования, проведенного национальной коммерческой телевещательной компанией FTV Prima, стоит обратить внимание на то, что в настоящее время в Чешской Республике 1,06 млн. приемников подключены к HbbTV, и это вдвое больше, чем год назад.
Также отмечается, что исследование, в котором участвовало 60 тысяч домашних хозяйств, показало, что в среднем 2.06 зрителей пользовались каждым из приемников. Это означает, что Red Button ("Красная кнопка"), рассматриваемая в стране как точка входа в HbbTV, использовалась более чем 2 миллионами зрителей.
По данным национальной передающей компании CRa, на долю Праги в настоящее время приходится 20,5% активных устройств HbbTV, за столицей следуют Южная Моравия (11,75%) и Центральная Богемия (10,7%).

От

Ассоциация HbbTV – глобальный инициативный проект, занимающийся разработкой открытых стандартов для доставки на подключаемые телевизоры и сет-топ-боксы контента через широковещательные и широкополосные сети, сообщает о публикации новой версии набора для проведения тестов на соответствие.
Это второй и наиболее важный из трёх ежегодных выпусков набора. Выпуск носит название v2018-2 и включает в себя 75 новых утверждённых тестов и 107 исправленных по итогам изучения отзывов пользователей и переутверждённых тестов.

«Свежий выпуск иллюстрирует приверженность Ассоциации HbbTV делу обеспечения максимальной надёжности и многогранности предоставляемых зрителю услуг в условиях современной развитой телевизионной среды», – говорит председатель правления Ассоциации HbbTV Винсент Гривет.
Выпуск v2018-2 включает в себя различные отдельные тестовые возможности для всей спецификации 2.0.1 и в особенности таких её наиболее ярких особенностей, как DASH, HTML5 и субтитры; таким образом, он позволяет проверять на соответствие устройства, совместимые с 2.0.1. В целом в состав тестового набора HbbTV теперь входит без малого 2 000 тестов.
Тестовый набор доступен на безоплатной основе всем членам HbbTV, а также может использоваться организациями, не являющимися членами Ассоциации, через один из зарегистрированных тестовых центров HbbTV.

От

Facebook сообщила, что закрыла сотням тысяч недействующих приложений доступ к Platform API, который позволяет работать с данными пользователей социальной сети. В мае компания заявила, что до 1 августа разработчики и предприятия должны повторно отправить свои приложения на внутреннее рассмотрение. Оно включало проверку подлинности и заключение новых соглашений касательно сбора пользовательских данных.

Целью компании было убедиться, что приложения соответствуют политике конфиденциальности Facebook и новым правилам, введённым после скандала с Cambridge Analytica, когда сторонний разработчик собирал данные пользователей и продавал их другой фирме. Теперь социальная сеть вычислила недействующие приложения, а также приложения, авторы которых проигнорировали требование о повторной проверке, и закрыла им доступ к Platform API.

«Мы рекомендуем разработчикам, чьи приложения по-прежнему используются, но не были отправлены на повторную проверку, исправить ситуацию сейчас же, - написал вице-президент по партнёрству в области продуктов Айм Арчибонг (Ime Archibong). - Но чтобы все используемые на данный момент приложения смогли пройти проверку, мы создадим специальную очередь. Когда нам потребуется дополнительная информация, разработчик сможет предоставить её в течение ограниченного времени. Если он этого не сделает, мы запретим приложению, требующему утверждения, доступ к API».
Facebook заверила, что разработчик не лишится доступа к API во время ожидания или прохождения проверки, если его программное обеспечение соответствует новым правилам.

Стандарт HbbTV, или же гибридное эфирно-широкополосное телевидение, уже используется для вещания в странах Европы, а также рассматривается в качестве потенциального стандарта для использования в США. Данный стандарт позволяет запускать потоковый контент из интернета, нажав красную кнопочку при настройке на любой из телеканалов. В 2016 году – HbbTV 2.0, в которой будет реализована поддержка Ultra HD и устройств категории «второй экран».

В группе говорят, что сегодня в мире насчитывается более 25 миллионов смарт-телевизоров и сет-топ-боксов, поддерживающих стандарт HbbTV. В процесс внедрения стандарта вовлечены такие компании, как LG, Samsung, Sony и TP Vision (Philips).

Итак, давайте разбираться, что же из себя представляет HbbTV 2.0 и, как говорится, с чем его едят.

1. Функциональные особенности

1.1. Что нового в HbbTV 2.0?

• HTML5 и связанные технологии.
• HEVC видео.
• Поддержка субтитров для контента, получаемого из широкополосных сетей.
• Запуск приложения для экрана-спутника.
• Связь «приложение к приложению».
• MPEG DASH.
• Вставка рекламных блоков в VoD-контент.
• Синхронизация приложений и контента.
• Доставка контента через вещательный канал для трансляции не в режиме реального времени.
• Многопотоковая синхронизация.
• DRM в CAM.
• Синхронизация приложений и контента между устройствами.
• Удалённый запуск HbbTV приложения.
• Поддержка клавиатуры и мыши.
• Решение конфликтов ключевых запросов.
• Кеширование объектных каруселей.

1.2 Работает ли HbbTV 2.0 со вторым экраном? Если да, то каким образом?

Да. HbbTV 2.0 поддерживает четыре варианта работы со вторым экраном:

• Приложение на втором экране может запускать HbbTV приложение на телевизоре: это позволяет зрителю использовать свой планшет или смартфон для того, чтобы просматривать видео-контент и запускать HbbTV приложение, чтобы смотреть этот контент на экране телевизора. При таком варианте используется технология DIAL – как и в случае с Netflix и Chromecast.
• Приложение на телевизоре запускает приложение на втором экране: это позволяет, к примеру, таким популярным телешоу, как «Голос», запускать при помощи соответствующей службы HbbTV 2.0 приложение «Голос» на планшете, дабы обеспечить зрителю более глубокое погружение в происходящее и возможность интерактивной связи.
• Связь между приложением на втором экране и приложением на телевизоре.
• Синхронизация приложения или медиа на втором экране с медиа на телевизоре: это позволяет зрителю без проблем и прерываний переключаться в процессе просмотра с одного устройства на другое.

1.3. Что нового появится в модификации 2.0 из того, что не поддерживалось в модификации 1.5?

• Улучшено взаимодействие с вторым экраном (описано в пункте 1.2).
• Службы доступа, сочетающие аудио из широкополосных сетей с видео из эфира. Это позволит пользователям выбирать дополнительные языки или чистую аудио-дорожку.
• Включение VOD-услуги (доступ к контенту из эфира, записанному на внутреннее хранилище ресивера).
• Дополнительные широкие возможности для пользователя, основанные на HTML5.
• Возможность непрерывного просмотра видео-контента при переходе между телевизором, смартфоном, ПК и планшетом.
• Инновационные приложения-спутники, которые расширяют возможности телезрителя, добавляя детальную информацию о программе, голосование, а также прочие функции.
• Стандартизированная доставка Ultra HD контента в компрессии HEVC из широкополосной сети.
• Улучшенная доступность служб с более качественной поддержкой субтитров на различных языках.
• Поддержка новых рекламных моделей.
• Поддержка защиты личных данных пользователя.

1.4. Кто планирует использовать новую функциональность?

Общий набор функциональных особенностей HbbTV 2.0 формировался при участии различных сторон, включая вещателей, производителей и операторов. Несколько общенациональных организаций уже подтвердили планы использовать HbbTV 2.0. Ожидается, что первый проект масштабного внедрения HbbTV 2.0 проведёт британская компания Freeview Connect.

2. Внедрение и доступность

2.1. Является ли стандарт HbbTV 2.0 обратно-совместимым с модификацией 1.1. и 1.5?

Да. Стандарт HbbTV 2.0 был разработан таким образом, чтобы обеспечить стабильную работу существующих приложений HbbTV 1.1 и 1.5 на ресиверах HbbTV 2.0.

2.2. Когда ресиверы HbbTV 2.0 появятся в розничной продаже?

Появление в продаже первых HbbTV 2.0 ресиверов ожидается в 2016 году.

2.3. Есть ли / будет ли возможность обновления ресиверов стандарта 1.1 / 1.5 до версии 2.0 через обновление программного обеспечения?

Да, обновление существующих ресиверов HbbTV 1.1 или 1.5 возможно, это можно будет сделать при помощи соответствующей HbbTV 2.0 прошивки. Впрочем, на практике обновление телевизоров обычно ограничено исправлением выявленных «багов» и не добавляет в них новые функции. На управляемых сет-топ-боксах также будет возможно такое обновление, однако, лишь при условии достаточного количества памяти, необходимого для корректной работы HbbTV 2.0

2.4. Будет ли режим сертификации для стандарта 2.0? Если да, то когда?

В самое ближайшее время Ассоциация HbbTV запускает тендер на разработку комплекта для тестирования HbbTV 2.0. Ориентировочно комплект для тестирования будет готов в 2016 году, что позволит начать выпуск HbbTV 2.0 совместимых продуктов и услуг.

2.5. Будут ли опубликованы Профили новой спецификации? Или же во всех телевизорах/сет-топ-боксах должна будет быть внедрена новая спецификация полностью?

Никаких профилей не будет. Во всех телевизорах/сет-топ-боксах будет необходимо внедрить все составляющие элементы спецификации. Отдельные части данной спецификации относятся исключительно к определённым моделям телевизоров и сет-топ-боксов – в зависимости от их возможностей. Некоторые примеры включают HEVC.

3. Публикация и сроки

3.1. Будут ли технические характеристики HbbTV 2.0 опубликованы Европейским институтом телекоммуникационных стандартов (ETSI)? Если да, то под каким номером?

HbbTV передаст HbbTV 2.0 в ETSI. Что случится после, зависит от результатов процессов, инициированных ETSI. Мы надеемся, что характеристики будут опубликованы под номером TS 102 796 V1.3.1.

3.2. Когда можно ожидать начала имплементации HbbTV 2.0 в отрасли? Следует ли производителям ждать до тех пор, когда ETSI опубликует документацию?

Отрасль имплементирует HbbTV 2.0, не дожидаясь времени публикации технической спецификации в ETSI.

4. Защита личных данных

4.1. Предусматривает ли HbbTV 2.0 добавление или изменение чего-либо в сфере защиты личных данных?

В HbbTV 2.0 добавлено множество функций, относящихся к сфере защиты личных данных.

• Требование о включении в телевизор опции, дающей пользователю возможность определять, разрешать ли устройству отслеживать его действия (основано на пункте «Не отслеживать» спецификации W3C).
• Телевизоры должны либо сами блокировать куки от третьей стороны, либо дать пользователю возможность включать этот режим.
• В телевизорах рекомендуется предлагать возможность блокирования отслеживания web-сайтов с предоставлением пользователю возможности контролировать этот процесс.
• Телевизоры дают пользователю возможность блокировать куки.

5. Общие сведения / Фон

5.1. Сколько в мире сегодня насчитывается HbbTV совместимых устройств?

По состоянию на сегодня на руках у пользователей во всём мире насчитывается более 25 миллионов HbbTV ресиверов.

5.2. Какая доля HbbTV совместимых устройств в общем количестве новых продаваемых устройств?

В большинстве стран Европы почти все продаваемые подключаемые телевизоры являются HbbTV совместимыми.

5.3. В каких странах используется стандарт HbbTV?

5.4. Какие компании и отраслевые сектора являются главными движущими силами в вопросе внедрения HbbTV 2.0?

• Вещатели и операторы: BBC, Eutelsat, IRT, ITV, SES
• Производители: LG, Samsung, Sony, TP Vision
• Технологические компании: Access, Cisco, Digital TV Group, Digital TV Labs, Dolby, Ericsson, Espial, Fraunhofer FOKUS, HTTV, Nagra, Opera, Quadrille, Qualcomm, Strategy and Technology, Tara Systems, Telecom ParisTech, TNO, Viaccess-Orca.