Первоначально автор Альберто Массари
Вступление
Одной из новых функций предварительного просмотра СУБД NewSQL NuoDB 1.2 является добавление хранимых процедур SQL к набору инструментов, который есть в распоряжении пользователей. Хотя они не добавляют никаких новых возможностей в ядро базы данных, ту же задачу можно выполнить с помощью клиентского кода, написанного на любом из поддерживаемых языков (C ++, Java, .NET, Python, Perl …), значительная часть Сообщество SQL уже привыкло писать их в основном для повышения производительности и безопасности.
Преимущество в производительности можно увидеть, когда процедура выполняет несколько операторов SQL: на самом деле, при запуске внутри механизма транзакций, обходные пути в сети, необходимые для отправки оператора и извлечения данных, удаляются. С другой стороны, это перемещает нагрузку на процессор, связанную с инструкциями потока управления, с клиента на сервер базы данных, хотя эта дополнительная нагрузка должна быть незначительной. Аспект безопасности часто связан с тем, как устроена база данных: может существовать набор таблиц, реализующих модель данных, которыми администратор БД не желает свободно манипулировать, и чья привилегия UPDATE предоставляется ограниченному числу пользователей. В таких случаях,единственной точкой входа для манипулирования этими таблицами является хранимая процедура, которая может проверять данные и затем выполнять несколько команд INSERT, которые гарантируют согласованность модели данных. Итак, если вы один из пользователей, которые чувствуют необходимость в хранимых процедурах, вы можете создать их в NuoDB следующим образом:
CREATE PROCEDURE myProc(IN p1 INTEGER,
INOUT p2 VARCHAR(256),
OUT p3 DECIMAL(18,2))
RETURNS rows(col1 INTEGER, col2 VARCHAR(256))
LANGUAGE SQL
SECURITY INVOKER
AS
....
END_PROCEDURE
Язык по умолчанию (и единственный), на котором могут быть написаны хранимые процедуры, — это SQL, поэтому модификатор LANGUAGE SQL на самом деле не нужен; но мы работаем над внедрением других языков высокого уровня, чтобы вы могли превратить существующий клиентский код в хранимые процедуры. Следите за новостями! В любом случае не каждый может создать хранимую процедуру: только пользователи, которым администратор предоставил роль администратора базы данных, могут создавать их. Вновь созданная процедура может быть выполнена, изменена или удалена только ее создателем; она должна явно предоставить привилегию EXECUTE и / или ALTER другим пользователям, прежде чем они смогут использовать или изменить ее.
На этом этапе, кто бы ни получил привилегию выполнения в процедуре, сможет получить доступ и управлять объектами, на которые ссылается процедура. То есть, если процедура не создана с модификатором SECURITY INVOKER; в этом случае во время выполнения выполняется проверка, чтобы гарантировать, что текущий пользователь имеет необходимые привилегии. Объявление процедуры определяет контракт между вызывающей стороной и кодом SQL, расположенным между AS и маркером END_PROCEDURE. Вызывающая сторона предоставляет значение для каждого из параметров, помеченных как IN или INOUT, в то время как процедура возвращает вызывающей стороне значения через параметры, объявленные как INOUT или OUT, плюс таблицу в памяти, структура которой определяется модификатором RETURNS. Оба параметра и таблица возврата являются необязательными, поэтому можно объявить процедуру, которая неt принимает параметры и не возвращает никакого значения. Взяв эту процедуру в качестве примера, клиент Java может вызвать ее, используя следующий фрагмент кода:
// prepare the EXECUTE statement
CallableStatement cStmt =
connection.prepareCall("EXECUTE myProc(?, ?, ?)");
// set a value for the IN parameter
cStmt.setInt(1, 10);
// set a value for the INOUT parameter
cStmt.setInt(2, 10);
// register the INOUT parameter to retrieve its changed value
cStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
// register the OUT parameter to retrieve its changed value
cStmt.registerOutParameter(3, java.sql.Types.DECIMAL);
ResultSet resultSet = cStmt.executeQuery();
while(resultSet.next()) {
int col1 = resultSet.getInt("col1");
String col2 = resultSet.getString("col2");
}
// beyond iterating over the returned result set,
// get the changed values
String p2 = cStmt.getString(2);
int p3 = cStmt.getInt(3);
Пример AC # может выглядеть следующим образом:
NuoDbCommand cmd = new NuoDbCommand("myProc", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Prepare();
cmd.Parameters["p1"].Value = 10;
cmd.Parameters["p2"].Value = 10;
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int col1 = reader["col1"];
string col2 = reader["col2"]);
}
}
string p2 = cmd.Parameters["p2"].Value;
int p3 = cmd.Parameters["p3"].Value;
переменные
Изучив интерфейс процедуры, давайте сосредоточимся на том, чем процедура может манипулировать: хотя обычные операторы, заданные удаленным клиентом, могут работать только с полями ссылочных таблиц, хранимые процедуры также могут использовать переменные, ссылаясь на них в любом месте. где буквальная константа может быть использована. Набор предопределенных переменных — это параметры, объявленные в интерфейсе процедуры, но другие можно объявить в теле процедуры просто с помощью инструкции VAR:
VAR v1; VAR v2 INTEGER; VAR v3 = 'initial value'; VAR v4, v5, v6 INT = 0, v7;
Если в объявлении переменной не указан тип данных, при присвоении ему значения проверка не производится.
Манипуляция данными
Большая часть операторов SQL — это те же инструкции DML UPDATE, INSERT, DELETE и EXECUTE / CALL, которые может указывать удаленный клиент. Что касается оператора SELECT, наш выбор состоял в том, чтобы разрешить его в двух разных формах: в качестве правой части оператора присваивания или в качестве курсора цикла FOR.
VAR v1, v2, v3;
v1, v2 = SELECT col1, col2 FROM user.table WHERE col3 = p1;
...
FOR SELECT col1 FROM user.table;
v3 = col1;
END_FOR;
В операторе присваивания переменные, перечисленные в левой части знака равенства, будут присваиваться каждому столбцу, возвращаемому оператором SELECT с другой стороны; v1 получит значение col1, а v2 получит col2. Если SELECT не идентифицирует какую-либо строку, обе переменные будут содержать значение NULL. Если, с другой стороны, SELECT идентифицирует более одной строки, произойдет ошибка времени выполнения. В этом сценарии можно использовать цикл FOR; он будет извлекать строки одну за другой и для каждой строки будет выполнять перечисленные операторы до команды END_FOR. Если эти операторы ссылаются на одно из полей, перечисленных в операторе SELECT, они будут иметь доступ к соответствующему столбцу текущей строки. Операторы процедуры также имеют доступ к новой таблице, которая не поддерживается никаким хранилищем: it ‘s таблица, объявленная в модификаторе RETURNS, которая будет возвращена вызывающей стороне в конце процедуры. Эта таблица может быть заполнена стандартной командой INSERT.
INSERT INTO rows SELECT col1, col2 FROM user.table WHERE col3 = p1; INSERT INTO rows VALUES (v1, v2);
Завершая описание цикла FOR, его также можно использовать для перебора курсора, возвращаемого двумя другими инструкциями: EXECUTE и INSERT. В случае FOR EXECUTE цикл будет перебирать строки таблицы, которые вызванная процедура объявляет в модификаторе RETURNS; в случае FOR INSERT цикл будет перебирать таблицу в памяти, которая содержит значения идентификаторов, сгенерированных командой INSERT.
FOR INSERT INTO user.table VALUES (v1);
v3 = id;
END_FOR;
Управление потоком
Команда IF может использоваться для управления потоком команд; условие теста может быть любым допустимым логическим тестом:
IF (v1 > v2 + 4) ... ELSE ... END_IF; IF (CHARACTER_LENGTH(p2) < v1) ... END_IF;
С помощью команды WHILE один и тот же набор инструкций может быть выполнен несколько раз. Просто будьте уверены, что условие выхода может быть выполнено рано или поздно!
VAR index INTEGER = 0;
WHILE (index < 100)
...
index = index + 1;
END_WHILE;
Ранний выход
Операторы внутри цикла WHILE или FOR могут включать оператор BREAK, чтобы сразу перейти к первой инструкции после окончания ближайшего цикла, не ожидая естественного условия выхода. Вместо этого команда RETURN может быть помещена в любую позицию внутри тела процедуры, и она немедленно завершит процедуру с кодом успеха. Команда THROW вместо этого вызовет сбой процедуры, откатив любую операцию DML, которую процедура ранее вызывала, и сообщив вызывающей стороне сообщение об ошибке, указанное в качестве аргумента команды THROW.
IF (p1 < 0)
THROW 'index out of bounds';
END_IF;
IF (p1 = 0)
RETURN;
END_IF;
VAR index INTEGER = 0, maxLoops INTEGER = 100;
WHILE (index < p1)
IF (index > maxLoops)
BREAK;
END_IF;
index = index +1;
END_WHILE;
Обработка исключений
Если инструкция внутри тела процедуры не выполняется (например, INSERT, который вставляет дублирующее значение в первичный ключ, или присвоение, которое указывает значение вне допустимого диапазона целевого типа данных), вся процедура завершается неудачно и выполняет откат текущей транзакции. , Если автор ошибки считает такую ошибку исправимой, он может заключить инструкцию в блок TRY / CATCH и выполнить необходимые операции восстановления. Если, проверяя строку error_message, сбой не соответствует ожидаемому, его можно перебросить с помощью инструкции THROW.
/* example code, don't try this at home! */
VAR tmpID INT = 1;
WHILE (TRUE)
TRY
INSERT INTO user.table (ID, COL1) VALUES (tmpID, v1);
BREAK;
CATCH(error_message)
IF (error_message CONTAINING 'duplicate')
tmpID = tmpID + 1;
ELSE
THROW error_message;
END_IF;
END_TRY;
END_WHILE;
Выводы
Чтобы протестировать новую функцию, мы изменили код теста DBT-2 (доступный по адресу https://github.com/nuodb/dbt2 ), добавив также версию, в которой используются хранимые процедуры. Вот пример того, как выглядит процедура new_order:
CREATE PROCEDURE new_order(tmp_w_id INT, tmp_d_id INT, tmp_c_id INT,
tmp_o_all_local INT, tmp_o_ol_cnt INT,
ol_i_id1 INT, ol_supply_w_id1 INT, ol_quantity1 INT,
ol_i_id2 INT, ol_supply_w_id2 INT, ol_quantity2 INT,
ol_i_id3 INT, ol_supply_w_id3 INT, ol_quantity3 INT,
ol_i_id4 INT, ol_supply_w_id4 INT, ol_quantity4 INT,
ol_i_id5 INT, ol_supply_w_id5 INT, ol_quantity5 INT,
ol_i_id6 INT, ol_supply_w_id6 INT, ol_quantity6 INT,
ol_i_id7 INT, ol_supply_w_id7 INT, ol_quantity7 INT,
ol_i_id8 INT, ol_supply_w_id8 INT, ol_quantity8 INT,
ol_i_id9 INT, ol_supply_w_id9 INT, ol_quantity9 INT,
ol_i_id10 INT, ol_supply_w_id10 INT, ol_quantity10 INT,
ol_i_id11 INT, ol_supply_w_id11 INT, ol_quantity11 INT,
ol_i_id12 INT, ol_supply_w_id12 INT, ol_quantity12 INT,
ol_i_id13 INT, ol_supply_w_id13 INT, ol_quantity13 INT,
ol_i_id14 INT, ol_supply_w_id14 INT, ol_quantity14 INT,
ol_i_id15 INT, ol_supply_w_id15 INT, ol_quantity15 INT)
AS
VAR out_c_credit, out_c_last, out_d_next_o_id;
VAR out_w_tax, out_d_tax, out_c_discount;
VAR tmp_i_name, tmp_i_data, tmp_i_id, tmp_i_price;
VAR tmp_ol_supply_w_id, tmp_ol_quantity, tmp_ol_amount;
VAR tmp_s_quantity, tmp_total_amount;
VAR o_id = 0;
out_w_tax = SELECT w_tax
FROM warehouse
WHERE w_id = tmp_w_id;
out_d_tax, out_d_next_o_id = SELECT d_tax, d_next_o_id
FROM district
WHERE d_w_id = tmp_w_id
AND d_id = tmp_d_id;
o_id=out_d_next_o_id;
UPDATE district
SET d_next_o_id = d_next_o_id + 1
WHERE d_w_id = tmp_w_id
AND d_id = tmp_d_id;
out_c_discount, out_c_last, out_c_credit = SELECT c_discount, c_last, c_credit
FROM customer
WHERE c_w_id = tmp_w_id
AND c_d_id = tmp_d_id
AND c_id = tmp_c_id;
INSERT INTO new_order (no_o_id, no_d_id, no_w_id)
VALUES (out_d_next_o_id, tmp_d_id, tmp_w_id);
INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_carrier_id,
o_ol_cnt, o_all_local)
VALUES (out_d_next_o_id, tmp_d_id, tmp_w_id, tmp_c_id,
now(), NULL, tmp_o_ol_cnt, tmp_o_all_local);
tmp_total_amount = 0;
VAR counter = 0;
WHILE (counter < 15)
IF (tmp_o_ol_cnt > counter )
tmp_i_id = CASE counter
WHEN 0 THEN ol_i_id1
WHEN 1 THEN ol_i_id2
WHEN 2 THEN ol_i_id3
WHEN 3 THEN ol_i_id4
WHEN 4 THEN ol_i_id5
WHEN 5 THEN ol_i_id6
WHEN 6 THEN ol_i_id7
WHEN 7 THEN ol_i_id8
WHEN 8 THEN ol_i_id9
WHEN 9 THEN ol_i_id10
WHEN 10 THEN ol_i_id11
WHEN 11 THEN ol_i_id12
WHEN 12 THEN ol_i_id13
WHEN 13 THEN ol_i_id14
WHEN 14 THEN ol_i_id15
END;
tmp_ol_supply_w_id = CASE counter
WHEN 0 THEN ol_supply_w_id1
WHEN 1 THEN ol_supply_w_id2
WHEN 2 THEN ol_supply_w_id3
WHEN 3 THEN ol_supply_w_id4
WHEN 4 THEN ol_supply_w_id5
WHEN 5 THEN ol_supply_w_id6
WHEN 6 THEN ol_supply_w_id7
WHEN 7 THEN ol_supply_w_id8
WHEN 8 THEN ol_supply_w_id9
WHEN 9 THEN ol_supply_w_id10
WHEN 10 THEN ol_supply_w_id11
WHEN 11 THEN ol_supply_w_id12
WHEN 12 THEN ol_supply_w_id13
WHEN 13 THEN ol_supply_w_id14
WHEN 14 THEN ol_supply_w_id15
END;
tmp_ol_quantity = CASE counter
WHEN 0 THEN ol_quantity1
WHEN 1 THEN ol_quantity2
WHEN 2 THEN ol_quantity3
WHEN 3 THEN ol_quantity4
WHEN 4 THEN ol_quantity5
WHEN 5 THEN ol_quantity6
WHEN 6 THEN ol_quantity7
WHEN 7 THEN ol_quantity8
WHEN 8 THEN ol_quantity9
WHEN 9 THEN ol_quantity10
WHEN 10 THEN ol_quantity11
WHEN 11 THEN ol_quantity12
WHEN 12 THEN ol_quantity13
WHEN 13 THEN ol_quantity14
WHEN 14 THEN ol_quantity15
END;
tmp_i_price, tmp_i_name, tmp_i_data = SELECT i_price, i_name, i_data
FROM item
WHERE i_id = tmp_i_id;
IF (tmp_i_price > 0 )
tmp_ol_amount = tmp_i_price * tmp_ol_quantity;
call new_order_2(tmp_w_id, tmp_d_id, tmp_i_id, tmp_ol_quantity, tmp_i_price,
tmp_i_name, tmp_i_data, out_d_next_o_id, tmp_ol_amount,
tmp_ol_supply_w_id, counter + 1, tmp_s_quantity);
tmp_total_amount = tmp_total_amount + tmp_ol_amount;
END_IF;
ELSE
BREAK;
END_IF;
counter = counter + 1;
END_WHILE;
END_PROCEDURE