03.09.2011 Используйте PL/SQL!

Разработчики информационных систем, работающих с использованием базы данных Oracle, часто пишут клиентские приложения совсем не задумываясь об эффективности, генерируя просто ужасный код как по критерию скорости выполнения, так и по ненужной сложности самого кода. При этом такие разработчики часто не знают или не хотят знать про серверный язык программирования Oracle PL/SQL. А многие идут еще дальше, полностью абстрагируясь от базы данных, генерируя всевозможные маппинги к таблицам базы данных даже не задумываясь о том, надо ли это делать в данном конкретном приложении.

В этой заметке я приведу пример такого ужасного кода на Delphi с использованием Direct oracle Access (DOA). Впрочем это не значит, что ужасный код генерят только разработчики на Delphi, в равной степени это относится и к другим средам разработки.

В качестве примера приведем простую задачу. Есть некоторая таблица базы данных с именем PROXIES, в которую необходимо добавлять записи при помощи клиентской функции. В случае, если запись с ключевыми параметрами уже существует (пусть это будут поля TYPE_ID,HOST,PORT) запись добавлять не надо. Задача сводится к проверке существования записи и вставке новой, если записи с такими ключевыми полями нет в таблице. Все очень просто. Как решают такую задачу? Вот один из вариантов решения:

function AddProxy(Host: string; TypeId,Port,Timeout: integer): integer;
var Q: TOracleQuery;
Q1: TOracleQuery;
begin
Q:=TOracleQuery.Create(nil);
Q1:=TOracleQuery.Create(nil);
try
Q.Session:=Data.OracleSession;
Q1.Session:=Data.OracleSession;
Q.SQL.Text:='select id from proxies where host='+Host+' and type_id='+IntToStr(TypeId)+' and port='+IntToStr(Port);
Q.Execute;
if not Q.Eof then
begin
Result:=0;
Exit;
end;
Result:=NextId; // Некоторая внешняя функция, получающая новый ИД из последовательности
Q1.SQL.Text:='insert into proxies (id,type_id,host,port,timeout) values ('+IntToStr(Result)+','+IntToStr(TypeId)+','''+Host+''','+IntToStr(Port)+','+IntToStr(Timeout)+')';
Q1.Session.Commit;
finally
Q1.Free;
Q.Free;
end;

Код приведенный выше, вполне работоспособен, но он ужасен. Ужасен тем, что в этом коде не используются переменные Oracle, в результате сервер Oracle будет при каждом выполнении этой функции выполнять полный разбор двух SQL запросов (на поиск и вставку записи).

Вот более правильный вариант, который должен был бы написать более продвинутый разработчик:

function AddProxy(Host: string; TypeId,Port,Timeout: integer): integer;
var Q: TOracleQuery;
Q1: TOracleQuery;
id: integer;
begin
Q:=TOracleQuery.Create(nil);
Q1:=TOracleQuery.Create(nil);
try
Q.Session:=Data.OracleSession;
Q1.Session:=Data.OracleSession;
Q.DeclareVariable('HOST',otString);
Q.DeclareVariable('TYPE_ID',otInteger);
Q.DeclareVariable('PORT',otInteger);
Q.SQL.Text:='select id from proxies where host=:HOST and type_id=:TYPE_ID and port=:PORT';
Q.SetVariable('HOST',Host);
Q.SetVariable('TYPE_ID',TypeId);
Q.SetVariable('PORT',Port);
Q.Execute;
if not Q.Eof then
begin
Result:=0;
Exit;
end;
Result:=NextId;
Q1.DeclareVariable('ID',otInteger);
Q1.DeclareVariable('TYPE_ID',otInteger);
Q1.DeclareVariable('HOST',otString);
Q1.DeclareVariable('PORT',otInteger);
Q1.DeclareVariable('TIMEOUT',otInteger);
Q1.SetVariable('ID',Result);
Q1.SetVariable('TYPE_ID',TypeId);
Q1.SetVariable('HOST',Host);
Q1.SetVariable('PORT',Port);
Q1.SetVariable('TIMEOUT',Timeout);
Q1.SQL.Text:='insert into proxies (id,type_id,host,port,timeout) values (:ID,:TYPE_ID,:HOST,:PORT,:TIMEOUT)';
Q1.Execute;
Q1.Session.Commit;
finally
Q1.Free;
Q.Free;
end;
end;

Этот код уже не так плох, как предыдущий вариант. Здесь используются переменные, в результате чего вы не будете напрягать сервер Oracle постоянными жесткими разборами ваших запросов. Однако этот код не оптимален. Он стал более громоздким и менее читаемым. Почему бы в этот момент не вспомнить про замечательный язык программирования oracle PL/SQL, который просто создан для решения подобных задач?

Напишем функцию на PL/SQL в составе PL/SQL пакета PROXY:

FUNCTION ADD_PROXY(pnTYPE IN NUMBER,
psHOST IN VARCHAR2,
pnPORT IN NUMBER,
pnTIMEOUT IN NUMBER
) RETURN NUMBER
IS
lnID NUMBER;
BEGIN
SELECT id
INTO lnID
FROM PROXIES
WHERE TYPE_ID=pnTYPE
AND HOST=psHOST
AND PORT=pnPORT;
RETURN NULL;
EXCEPTION
WHEN no_data_found
THEN
lnID:=NEXT_ID;
INSERT
INTO PROXIES (ID,TYPE_ID,HOST,PORT,TIMEOUT)
VALUES (lnID,pnTYPE,psHOST,pnPORT,pnTIMEOuT);
RETURN lnID;
END ADD_PROXY;

На клиенте вы должны написать такую простую функцию:

function TProxy.AddProxy(Host: string; TypeId,Port,Timeout: integer): integer;
var P: TOraclePackage;
begin
P:=TOraclePackage.Create(nil);
try
P.Session:=Data.OracleSession;
Result:=P.CallIntegerFunction('PROXY.ADD_PROXY',[TypeId,Host,Port,Timeout]);
P.Session.Commit;
finally
P.Free;
end;
end;

Фактически на клиенте вы делаете простейший вызов серверной функции на PL/SQL. Посмотрите еще раз на эту PL/SQL функцию. Она очень проста, легко читается и выполняется максимально эффективно. Она легко может быть использована в разных клиентах, и это преимущество тем весомее, чем сложнее логика, реализуемая подобными функциями.

Пишите эффективный и правильный код! В этом случае вам может быть и не придется полностью переписывать ваши приложения или резко наращивать мощь серверов ваших клиентов, которые как правило начинают не справляться с выполнением ненужной работы, и используйте по максимуму в ваших приложениях PL/SQL!

www.foxbase.ru