END FOR

DO

DO

END IF

ELSE

END IF

AS

AS

SELECT SId, SUM(Qty)

FROM INVOICE

GROUP BY SId

Теперь, используя это представление, найдем имена искомых поставщиков:

SELECT SName

FROM SALOR S, V1

WHERE S.SId = V1.SId AND Qty = (SELECT MAX(Qty) FROM V1)

или, что то же самое,

SELECT SName

FROM SALOR S INNER JOIN V1 ON S.SId = V1.SId

WHERE Qty = (SELECT MAX(Qty) FROM V1)

Поскольку созданное представление нужно только для данного запроса, после выполнения запроса представление следует удалить:

DROP VIEW V1

2. Получить отчет в виде: имя поставщика, название товара, суммарное количество данного товара, поставленного данным поставщиком, суммарное количество данного товара, поставленного всеми поставщиками.

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

SELECT SName, AName, SUM(I.Qty) AS Common, T.Qty AS Total

FROM SALOR S, ARTICLE A, INVOICE I,

(SELECT ACode, SUM(Qty) FROM INVOICE GROUP BY ACode) AS T(ACode, Qty)

WHERE S.SId = I.SId AND A.ACode = I.ACode AND T.ACode = A.ACode

GROUP BY SName, AName, T.Qty

ORDER BY SName, AName

Поскольку в списке вывода в SELECT упоминается еще и суммарное количество товара, не включенное в агрегатную функцию, это имя колонки также должно быть включено в список группирования GROUP BY.

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

CREATE VIEW T(ACode, Qty)

SELECT ACode, SUM(Qty)

FROM INVOICE

GROUP BY ACode;

SELECT SName, AName, SUM(I.Qty) AS Common, T.Qty AS Total

FROM SALOR S, ARTICLE A, INVOICE I, T

WHERE S.SId = I.SId AND A.ACode = I.ACode AND T.ACode = A.ACode

GROUP BY SName, AName, T.Qty

ORDER BY SName, AName;

DROP VIEW T;

В данном примере для разделения предложений SQL использован символ «;». Если все три предложения SQL выполнять вместе, необходимо явно указать, что символом – разделителем предложений является символ «;». Это делается средствами той среды, в которой выполняются предложения SQL.

3. Для каждого поставщика получить его имя, суммарное количество поставленных им товаров с кодом A01, суммарное количество поставленных им товаров с кодом A02 и суммарное количество всех поставленных им товаров.

SELECT SName, Coalesce(S1.Qty, 0) AS "Total of A01",

Coalesce(S2.Qty,0) AS "Total of A02", Com.Qty AS Total

FROM SALOR S,

(SELECT SId, SUM(Qty) FROM INVOICE WHERE ACode = 'A01' GROUP BY SId)

AS S1(SId, Qty),

(SELECT SId, SUM(Qty) FROM INVOICE WHERE ACode = 'A02' GROUP BY SId)

AS S2(SId, Qty),

(SELECT SId, SUM(Qty) FROM INVOICE GROUP BY SId) AS Com(SId, Qty)

WHERE S.SId = S1.SId AND S.SId = S2.SId AND S.SId = Com.SId

Этот запрос можно реализовать с помощью выражения CASE:

SELECT SName,

SUM(CASE WHEN ACode = 'A01' THEN Qty ELSE 0 END) AS "Total of A01",

SUM(CASE WHEN ACode = 'A02' THEN Qty ELSE 0 END) AS "Total of A02",

SUM(Qty) AS Total

FROM SALOR S INNER JOIN INVOICE I ON S.SId = I.SId

GROUP BY SName

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

Данный запрос можно реализовать двумя способами:

а) С помощью вложенных табличных выражений:

SELECT WName, AName, Art.Qty AS SubTotal, Com.Qty AS GrandTotal

FROM WAREHOUSE W, ARTICLE A,

(SELECT WId, ACode, SUM(Qty) FROM INVOICE GROUP BY WId, ACode)

AS Art(WId, ACode, Qty),

(SELECT WId, SUM(Qty) FROM INVOICE GROUP BY WId) AS Com(WId, Qty)

WHERE W.WId = Art.WId AND A.ACode = Art.ACode AND W.WId = Com.WId

б) С помощью скалярного подзапроса в списке вывода в SELECT:

SELECT WName, AName, SUM(Qty) AS SubTotal,

(SELECT SUM(Qty) FROM INVOICE I WHERE W.WId = I.WId )AS GrandTotal

FROM WAREHOUSE W, ARTICLE A, INVOICE I

WHERE W.WId = I.WId AND A.ACode = I.ACode)

GROUP BY WName, AName, W.WId

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

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

SELECT SName, AName, Art.Qty AS SubTotal, Com.Qty AS GrandTotal,

DEC(100.00 * Art.Qty / Com.Qty, 4, 2) AS "Part in %"

FROM SALOR S, ARTICLE A,

(SELECT SId, ACode, SUM(Qty) FROM INVOICE GROUP BY SId, ACode)

AS Art(SId, ACode, Qty),

(SELECT SId, SUM(Qty) FROM INVOICE GROUP BY SId) AS Com(SId, Qty)

WHERE S.SId = Art.SId AND A.ACode = Art.ACode AND S.SId = Com.SId

ORDER BY 1, 4 DESC

I. И еще несколько примеров написания запросов из документации [10]

Используются таблицы из базы данных Sample.

1. Получить всю информацию, хранящуюся в таблице EMPLOYEE.

SELECT * FROMEMPLOYEE

2. Соединить таблицы EMP_ACT и EMPLOYEE, выбрать все колонки из таблицы EMP_ACT и добавить фамилию сотрудника (LastName) из таблицы EMPLOYEE к каждой строке результата.

SELECTEMP_ACT.*, LastName

FROMEMP_ACT, EMPLOYEE

WHEREEMP_ACT.EmpNo = EMPLOYEE.EmpNo

3. Соединить таблицы EMPLOYEE и DEPARTMENT, выбрать номер сотрудника (EmpNo), фамилию (LastName), номер отдела (WorkDept в таблице EMPLOYEE или DeptNo в таблице DEPARTMENT) и название отдела (DeptName) для всех сотрудников, родившихся (Birthdate) ранее 1930 года.

SELECTEmpNo, LastName, WorkDept, DeptName

FROMEMPLOYEE, DEPARTMENT

WHEREWorkDept = DeptNo AND YEAR(Birthdate)< 1930

4. Выбрать должность (JOB) и минимальный и максимальный оклады (Salary) для каждой группы строк с одинаковым кодом должности из таблицы EMPLOYEE, но только для тех групп, которые содержат более одной строки и имеют максимальный оклад, не меньший, чем 27000.

SELECTJob, MIN(Salary), MAX(Salary)

FROMEMPLOYEE

GROUP BYJob

HAVING COUNT(*)> 1 AND MAX(Salary)>= 27000

5. Выбрать все строки из таблицы EMP_ACT для сотрудников (EmpNo) из отдела (WorkDept) ‘E11’ (номера отделов, в которых работают сотрудники, находятся в таблице EMPLOYEE).

SELECT * FROMEMP_ACT

WHEREEmpNo IN

(SELECTEmpNo FROMEMPLOYEE

WHEREWorkDept = ’E11’)

6. Из таблицы EMPLOYEE выбрать номер отдела (WorkDept) и максимальный оклад в отделе (Salary) для всех отделов, у которых максимальный оклад меньше среднего оклада всех сотрудников.

SELECTWorkDept, MAX(Salary)

FROMEMPLOYEE

GROUP BYWorkDept

HAVING MAX(Salary) < (SELECT AVG(Salary)

FROMEMPLOYEE)

Подзапрос, включенный в конструкцию HAVING, выполнится в данном примере только один раз.

7. Используя таблицу EMPLOYEE, выбрать номер отдела (WorkDept) и максимальный оклад в отделе (Salary) для всех отделов, у которых максимальный оклад меньше среднего оклада по всем отделам.

SELECTWorkDept, MAX(Salary)

FROMEMPLOYEE EMP_COR

GROUP BYWorkDept

HAVING MAX(Salary) < (SELECT AVG(Salary)

FROMEMPLOYEE

WHERE NOTWorkDept = EMP_COR. WorkDept)

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

8. Определить номера сотрудников, занимающих должности SALESREP, и их оклады, представленные вместе со средним окладом и общим количеством сотрудников в отделах, в которых работают данные сотрудники.

Этот запрос, прежде всего, должен создать вложенное табличное выражение (DINFO), для того чтобы получить колонки AvgSalary и EmpCount, а также колонку DeptNo, которая используется в конструкции WHERE.

SELECT THIS_EMP.EmpNo, THIS_EMP.Salary, DINFO.AvgSalary, DINFO.EmpCount

FROM EMPLOYEE THIS_EMP,

(SELECT OTHERS.WorkDept AS DeptNo,

AVG(OTHERS.Salary) AS AvgSalary,

COUNT(*) AS EmpCount

FROM EMPLOYEE OTHERS

GROUP BY OTHERS.WorkDept

) AS DINFO

WHERE THIS_EMP.Job = 'SALESREP'

AND THIS_EMP.WorkDept = DINFO.DeptNo

Использование в данном примере вложенного табличного выражения избавляет от необходимости создания обычного представления DINFO.

9. Вывести средний уровень образования и зарплату сотрудников для пяти случайных групп сотрудников.

Этот запрос требует использования вложенного табличного выражения для присваивания каждому сотруднику некоторого случайного значения, так чтобы в дальнейшем это значение можно было использовать в конструкции GROUP BY.

SELECT RandID, AVG(EdLevel), AVG(Salary)

FROM (SELECT EdLevel, Salary, INTEGER(RAND() * 5) AS RandID

FROM EMPLOYEE

) AS EMPRAND

GROUP BY RandID

10. Выполнить запрос к таблице EMP_ACT и вернуть те номера проектов, которые имеют сотрудников, чья зарплата входит в первую десятку наибольших зарплат по всем сотрудникам.

SELECTEMP_ACT.EmpNo,ProjNo

FROMEMP_ACT

WHEREEMP_ACT.EmpNo IN

(SELECTEMPLOYEE.EmpNo

FROMEMPLOYEE

ORDER BYSalary DESC

FETCH FIRST10 ROWS ONLY)

1. Краткая характеристика языка SQL PL DB2® UDB

SQL PL представляет собой высокоуровневый язык программирования, имеющий простой синтаксис и обычный набор управляющих предложений, таких как IF, ELSE, WHILE, FOR, ITERATE, а также некоторые другие предложения.

Подмножество языка SQL PL, используемое при создании триггеров и функций, определено как встроенный (inline) язык SQL PL. Слово «inline» подчеркивает тот факт, что функции, написанные с использованием встроенного SQL PL, реализуются путем встраивания (внедрения) тела функции в запрос, использующий эту функцию.

Составное динамическое предложение позволяет объединить несколько предложений SQL в небольшой логически атомарный блок, в котором могут быть объявлены переменные и некоторые элементы обработки условий. Эти предложения компилируются DB2 как одно предложение SQL и могут содержать элементы SQL PL. Только относительно небольшое количество конструкций языка SQL PL может быть использовано в составном динамическом предложении. Такое предложение эффективно использовать в тех случаях, когда обрабатывается достаточно большой поток данных с использованием простой управляющей логики. Для более сложной обработки, требующей использования вложенных управляющих конструкций или обработки исключительных ситуаций, лучше использовать хранимые процедуры SQL.

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

Составное динамическое предложение SQL

Составное динамическое предложение SQL имеет следующий синтаксис:

[ метка : ] BEGIN ATOMIC

объявление_SQL_переменной_или_условия ;

…

процедурное_предложение_SQL ;

…

END [ метка ]

метка – определяет метку составного предложения. Если указана начальная метка (перед фразой BEGIN), она может быть использована для уточнения SQL переменных в составном предложении, а также в предложении LEAVE. Конечная метка (после фразы END) может быть указана только в том случае, если указана начальная метка, и должна совпадать с начальной меткой. Метка может быть указана только в том случае, если составное динамическое предложение используется в определении триггера или функции.

ATOMIC– указывает, что если при выполнении составного динамического предложения возникает ошибка, выполнение составного предложения завершается, и все выполнившиеся к моменту появления ошибки предложения SQL, включенные в составное динамическое предложение, будут откачены.

Объявление SQL переменной определяет локальную переменную в составном динамическом предложении и имеет следующий вид:

DECLARE имя_SQL_переменной [ , … ] тип_данных [ DEFAULT значение ]

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

Тип данных – указывает тип данных переменной.

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

В одном предложении DECLARE можно объявить несколько переменных одного и того же типа.

Объявление условия определяет имя условия и соответствующее значение SQLSTATE и имеет следующий вид:

DECLARE имя_условия CONDITION FOR [ SQLSTATE [ VALUE ] ] строковая_константа

Имя условия задает имя условия, на которое можно ссылаться только в том составном предложении, в котором оно определено.

FOR SQLSTATEстроковая_константа – определяет значение SQLSTATE, ассоциированное с условием. Строковая константа должна иметь длину 5 символов и не должна иметь значение ’00000’.

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

Процедурное предложение SQL может быть представлено одним из следующих предложений:

• предложения ЯМД SQL (INSERT, DELETE, UPDATE)

• предложения формирования запросов (VALUES, SELECT),

• управляющие предложения языка SQL PL (FOR, IF, ITERATE, LEAVE, WHILE),

• CALL,

• SET переменная,

• GET DIAGNOSTICS,

• MERGE,

• SIGNAL.

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

Управляющие предложения

1. Предложение IF осуществляет выбор на основании проверки некоторых условий и имеет следующий синтаксис:

IF условие THEN процедурное_предложение_SQL ; . . .

[ ELSEIF условие THEN процедурное_предложение_ SQL ; . . . ]

[ ELSE процедурное_предложение_SQL ; . . . ]

условие – определяет условие, в соответствии с которым осуществляется выбор той или иной группы процедурных предложений SQL. Если результат вычисления условия – истина (true), выполняется процедурное предложение SQL, указанное после следующего за условием ключевого слова THEN; если же результат вычисления условия – ложь (false) или не определен (unknown), выполнение предложения IF продолжается с вычисления следующего условия, указанного конструкцией ELSEIF (если она включена в предложение), и так до тех пор, пока не будет получено значение результата истина (true) или не будет достигнута фраза ELSE предложения.

Пример:

. . .

IFrating = 1 THEN

UPDATEemployee SETsalary = salary * 1.10, bonus = 1000

WHEREempno = employee_number;

ELSEIFrating = 2 THEN

UPDATEemployee SETsalary = salary * 1.05, bonus = 500

WHEREempno = employee_number;

UPDATEemployee SETsalary = salary * 1.03, bonus = 0

WHEREempno = employee_number;

2. Предложение FOR позволяет организовать циклическое выполнение группы предложений SQL PL для каждой строки некоторой таблицы. Предложение имеет следующий синтаксис:

[ метка: ] FOR имя_цикла

AS предложение_SELECT

процедурное_предложение_SQL ;

. . .

END FOR [ метка ]

Пояснения:

метка – определяет метку предложения FOR. Если указана начальная метка (перед фразой FOR), она может быть использована в теле цикла в предложениях LEAVE и ITERATE. Конечная метка (после фразы END FOR) может быть указана только в том случае, если указана начальная метка, и должна совпадать с начальной меткой.

имя_цикла – определяет метку для неявного составного предложения, сгенерированного для реализации предложения FOR. Эта метка должна удовлетворять правилам задания меток составных предложений за тем исключением, что она не может быть использована в предложениях ITERATE и LEAVE в теле цикла FOR. Имя_цикла используется для уточнения имен колонок, возвращаемых указанным в FOR предложением SELECT.

Процедурные предложения SQL, указанные в теле цикла, выполняются для каждой строки таблицы, полученной в результате выполнения предложения SELECT. Значения колонок каждой строки доступны в теле цикла непосредственно через имена колонок.

Пример:

. . .

FORvl AS SELECTfirstnme, midinit, lastname FROMemployee

SETfullname = lastname || ’,’ || firstnme ||’ ’ || midinit;

INSERT INTOtnames VALUES(fullname);

3. Предложение WHILE позволяет организовать циклическое выполнение группы предложений SQL PL, пока некоторое условие сохраняет значение истина. Предложение имеет следующий синтаксис:

[ метка: ] WHILE условие DO

процедурное_предложение_SQL;

. . .

END WHILE [ метка ]

Пояснения:

метка – определяет метку предложения WHILE. Если указана начальная метка (перед фразой WHILE), она может быть использована в теле цикла в предложениях LEAVE и ITERATE. Конечная метка (после фразы END WHILE) может быть указана только в том случае, если указана начальная метка, и должна совпадать с начальной меткой.

условие – определяет условие, которое вычисляется перед каждой итерацией цикла. Если результат вычисления условия – истина, тело цикла выполняется.

4. ПредложениеITERATE позволяет начать новую итерацию указанного цикла:

ITERATE метка

Предложения, записанные после ITERATE, не выполняются, и управление передается на заголовок цикла, помеченного указанной меткой.

5. Предложение LEAVE позволяет прервать выполнение указанного цикла:

LEAVE метка

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

Предложение CALL

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

CALL имя_процедуры [ ( список_аргументов) ]

Имя процедуры определяет процедуру, которая должна быть вызвана.

Список аргументов содержит аргументы, перечисленные через запятую. Процедура может не иметь аргументов.

В качестве аргумента может быть указано выражение или значение NULL, n-ый аргумент в списке аргументов соответствует n-ому параметру в определении процедуры, созданному с помощью предложения CREATE PROCEDURE, и должен быть совместим с ним. Так, если параметр в определении процедуры объявлен как входной (IN), соответствующий аргумент должен допускать возможность присваивания его значения параметру. Если параметр в определении процедуры объявлен как выходной (OUT), соответствующим аргументом должно быть имя переменной или маркер параметра, представленный символом «?» (вопросительный знак). Если же параметр в определении процедуры определен как INOUT, должны выполняться требования, определенные и для IN параметров, и для OUT параметров.

Например, если некоторая процедура была определена с помощью следующего предложения:

CREATE PROCEDUREPARTS_ON_HAND (INPARTNUM INTEGER,

OUTCOST DECIMAL(7,2),

INOUTQUANTITY INTEGER)

. . .

Вызов данной процедуры может иметь следующий вид:

CALL PARTS_ON_HAND (2, ?, ?)

Процедуры, вызываемые из составного динамического предложения, не должны выполнять предложения COMMIT или ROLLBACK.

Предложение SET переменная

Предложение SET имеет много вариантов использования в языке SQL PL. Здесь рассматривается предложение SET, используемое для установки значения переменной.

Предложение SET может быть использовано следующим образом:

SET целевая_переменная = значение [, … ]

или

SET (целевая_переменная, … ) = (список_значений ) [, … ]

Здесь целевая переменная указывает переменную, которой присваивается значение. В качестве целевой переменной может быть указано имя переменной SQL, объявленной ранее; имя колонки целевой таблицы триггера (если предложение SET используется в определении триггера – CREATE TRIGGER); имя параметра процедуры (если предложение SET используется в определении процедуры – CREATE PROCEDURE).

Значение определяет новое значение, которое должно быть присвоено целевой переменной, и может быть задано одним из трех способов: выражение, NULL или DEFAULT.

Выражение записывается в соответствии с ранее представленными правилами. Если предложение SET используется в определении триггера, выражение может включать ссылки на переменные передачи, указанные с помощью ключевых слов OLD и NEW (см. ниже описание предложения CREATE TRIGGER).

NULL– указывает пустое значение.

DEFAULT– указывает, что должно использоваться значение по умолчанию. Если целевая переменная представляет имя колонки, получаемое значение зависит от того, как эта колонка была определена при создании соответствующей таблицы. Если целевая переменная представляет переменную SQL, получаемое значение определяется из объявления переменной.

Во втором случае список значений задается в виде отдельных значений, перечисленных через запятую, или с помощью подзапроса, возвращающего только одну строку; список значений и строка, возвращаемая подзапросом, должны иметь столько же значений, сколько целевых переменных указано в предложении SET. Если подзапрос возвращает пустую строку, целевым переменным присваивается NULL значение.

Если предложение SET с подзапросом используется в триггере, подзапрос может содержать ссылки на переменные передачи, указанные в конструкциях NEW и OLD.

С помощью одного предложения SET можно присвоить значения нескольким переменным.

Примеры:

1. Присвоить переменной SQL p_com значение NULL:

SETp_com = NULL

2. Увеличить значение переменной SQL p_sal на 10%:

SETp_sal = p_sal + (p_sal * .10)

3. Присвоить переменным SQL p_sal и p_com средние значения колонок SALARY и COMM:

SET(NEW_VAR.SALARY,NEW_VAR.COMM) =

(SELECT AVG(SALARY), AVG(COMM) FROMEMPLOYEE)

Предложение GET DIAGNOSTICS

Предложение GET DIAGNOSTICS позволяет получить информацию о ранее выполнившемся предложении SQL. Конкретный формат предложения GET DIAGNOSTICS зависит от того, какой результат выполнения предшествующего предложения SQL требуется получить.

1. Если необходимо получить числовой результат выполнения предшествующего предложения SQL, предложение GET DIAGNOSTICS имеет следующий вид:

GET DIAGNOSTICSимя_SQL_переменной = тип_результата

Здесь имя SQL переменной называет целочисленную переменную, которой присваивается результат выполнения предшествующего предложения SQL,

тип результата – определяет, что именно надо получить; может быть задан следующим образом:

ROW COUNT– предложение GET DIAGNOSTICS выполняется после предложений SQL DELETE, INSERT или UPDATE и возвращает количество строк, обработанных предшествующим предложением SQL;

DB2_RETURN_STATUS – предложение GET DIAGNOSTICS выполняется после предложения CALL вызова хранимой процедуры и возвращает код завершения, возвращаемый хранимой процедурой.

В обоих случаях, если предшествующее GET DIAGNOSTICS предложение SQL не соответствует указанным, возвращаемое значение не имеет смысла и может быть любым.

2. Если необходимо получить текст сообщения об ошибке или предупреждения, возвращаемые предшествующим предложением SQL, предложение GET DIAGNOSTICS записывается следующим образом:

GET DIAGNOSTICS EXCEPTION 1имя_SQL_переменной = тип_сообщения

Здесь имя переменной SQL называет строковую переменную, которой присваивается сообщение,

тип сообщения – указывает, что именно надо получить; может быть задан следующим образом:

MESSAGE_TEXT – предложение GET DIAGNOSTICS возвращает сообщение об ошибке или предупреждение, полученное при выполнении предшествующего предложения SQL. Текст сообщения возвращается на языке сервера базы данных, где выполнялось соответствующее предложение SQL. Если предшествующее предложение SQL завершилось с нулевым значением SQLCODE, GET DIAGNOSTICS возвращает пустую строка.

DB2_TOKEN_STRING – предложение GET DIAGNOSTICS возвращает маркеры (tokens) сообщений об ошибке или предупреждении, полученные при выполнении предшествующего предложения SQL. Если предшествующее предложение завершилось с нулевым значением SQLCODE, или SQLCODE не имеет маркера, GET DIAGNOSTICS возвращает пустую строка.

Такую форму предложения GET DIAGNOSTICS можно использовать только в хранимых процедурах.

Пример: определить, сколько строк было модифицировано при выполнении предложения UPDATE:

…

UPDATECORPDATA.PROJECT SETPRSTAFF = PRSTAFF + 1.5

WHEREDEPTNO = deptnbr;

GET DIAGNOSTICSrcount = ROW_COUNT;

...

Предложение MERGE

Предложение MERGE обновляет целевой объект (таблицу или представление), используя данные из некоторого источника данных. Строки целевого объекта, соответствующие источнику данных, могут быть удалены или модифицированы, а строки, отсутствующие в целевом объекте, могут быть вставлены.

Предложение MERGE имеет следующий синтаксис:

MERGE INTO целевой_объект [ [ AS ] корреляционное_имя [ ( имя_колонки, …) ] ]

USING источник ON условие_поиска

WHEN условие_соответствия THEN операция

. . .

[ ELSE IGNORE ]

Условие соответствия задается в виде:

[ NOT ] MATCHED [ AND условие_поиска ]

Операция задается в виде предложений SQL INSERT, DELETE, UPDATE, которые записываются обычным образом, за тем исключением, что в них отсутствует имя таблицы, или предложения SIGNAL.

Целевой объект задает имя таблицы или представления, для которых выполняются операции вставки, удаления и модификации, определяемые предложением MERGE, с учетом всех ограничений, которые накладываются на модифицируемые объекты базы данных. При необходимости, имя целевого объекта может быть заменено корреляционным именем, которое записывается и используется точно так же, как и корреляционные имена в запросах (см. описание предложения SELECT).

USINGисточник – указывает множество строк результирующей таблицы, которая должна быть соединена с целевой таблицей. Если в результирующей таблице нет строк, генерируется предупреждение «не найдено» (SQLSTATE 02000).

ONусловие_поиска – указывает, какие строки из источника должны быть использованы операциями, указанными в предложении. Каждое имя колонки в условии поиска, за исключением тех, которые указаны в подзапросах, должно именовать колонку целевого объекта или источника. Если условие поиска включает подзапрос, в котором указана целевая таблица предложения MERGE, подзапрос будет полностью выполнен, прежде чем какие-либо строки в целевой таблице будут модифицированы или вставлены. Условие поиска применяется к каждой строке целевой таблицы и таблицы источника. Для тех строк таблицы источника, для которых условие поиска истинно, выполняются указанные в предложении операции удаления и модификации. Для тех строк таблицы источника, для которых условие поиска не истинно, выполняются указанные в предложении операции вставки.

WHENусловие_соответствия – указывает условие, при котором выполняется указанная операция. Условия соответствия вычисляются в порядке их записи. Строки, для которых условие соответствия истинно, не рассматриваются в последующих условиях. Rows for which the matching-condition evaluates to true are not considered in subsequent matching conditions.

MATCHED– указывает, что операция должна выполняться только для тех строк, для которых условие поиска, указанное в конструкции ON, истинно. В этом случае, после THEN могут быть указаны только операции UPDATE, DELETE и SIGNAL.

NOT MATCHED– указывает, что операция должна выполняться только для тех строк, для которых условие поиска, указанное в конструкции ON, ложно или не определено. В этом случае, после THEN могут быть указаны только операции INSERT и SIGNAL.

ANDусловие_поиска – определяет дополнительное условие поиска, которое должно быть применено к отобранным строкам, для того чтобы выполнить операции, указанные после THEN.

ELSE IGNORE– указывает, что никакие действия не должны выполняться для тех строк, для которых условие соответствие не истинно.

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

Пример 1. Пусть определена таблица ACTIVITIES с колонками ActID (PK) – код работы и Descr – описание работы. Кроме этой таблицы, имеется таблица ARCHIVE, имеющая такую же структуру. Если в таблице ACTIVITIES описание некоторой работы изменено, или вставлена новая работа, соответствующие изменения должны быть внесены и в таблицу ARCHIVE.

MERGE INTOArchive Ar

USING(SELECTActID, Descr FROMActivities) Ac

ON(Ar.ActID = Ac.ActID)

WHEN MATCHED THEN UPDATE SETDescr = Ac.Descr

WHEN NOT MATCHED THEN INSERT(ActID, Descr) VALUES(Ac.ActID, Ac.Descr)

Пример 2. Пусть в базе данных необходимо хранить следующую информацию о назначенных работах: код работы (ActID), ее описание (Descr), дата завершения работы (EDate) и дата модификации информации о работе (MDate). Пусть все работы распределены по группам, и для каждой группы работ определена своя таблица с первичным ключом ActID (например, таблица Activities_GroupA содержит все работы, относящиеся к группе А). Колонка EDate в таблице объявлена как NULL колонка: для некоторых работ могут быть не назначены даты окончания.

Пусть также имеется архивная таблица Archive, в которой собрана информация о работах всех групп. По сравнению с основными таблицами, в архивную таблицу добавлена колонка Group – группа работ, и первичный ключ определяется двумя колонками – ActID и Group. Кроме того, в архивной таблице колонка EDate определена как обязательная (NOT NULL), а для колонки MDate определено значение по умолчанию CURRENT TIMESTAMP.

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

MERGE INTOArchive Ar

USING(SELECTActID, Descr, EDate, MDate FROMActivities_GroupA) Ac

ON(Ar.ActID = Ac.ActID) ANDAr.Group = ’A’

WHEN MATCHED ANDAc.EDate IS NULL THEN

SIGNAL SQLSTATE’70001’

SET MESSAGE_TEXT= Ac.ActID CONCAT’ cannot be modified. Reason: Date is not known’

WHEN MATCHED ANDAc.EDate < CURRENT DATE THEN DELETE

WHEN MATCHED ANDAr.MDate < Ac.MDate THEN

UPDATE SET(Descr, EDate, MDate) = (Ac.Descr, Ac.EDate, DEFAULT)

WHEN NOT MATCHED ANDAc.EDate IS NULL THEN

SIGNAL SQLSTATE’70002’

SET MESSAGE_TEXT= ac.ActID CONCAT’ cannot be inserted. Reason: Date is not known’

WHEN NOT MATCHED ANDAc.EDate >= CURRENT DATE THEN

INSERT(Group, ActID, Descr, EDate)

VALUES(’A’, Ac.ActID, Ac.Descr, Ac.EDate)