最近接到一个项目,不是很大,但客户要求数据库用oracle,而我们的系统是在sql server下做的,我们的系统在程序中嵌入了大量的SQL语句,如果全部手动翻译几乎是不可能的,我们的系统基于delphi的midas三层架构,幸好系统设计时将所有的SQL(包括客户端和中间层)都是通过中间层一个统一的接口来执行的,我想,如果在这个接口中将需要执行的SQL进行语法转换,就可以直接支持oracle了,但这种转换也算是一种高技术含量的活,想请教一下各位见多识广的大虾,有没有现成的第三方组件可以完成这个工作?例如,我传一个SQL Server规范的SQL给组件,组件返回一个支持Oracle规范的SQL给我就行了。感谢~!
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs;Type TSqlFuncInfo = record
Name: string;
ArgCount: integer;
Args: array [0..10] of string[100];
end;//最终需要调用的函数
function ConvertSQLToOracle(ASQL: string): string;//从SQL中提取函数信息
function GetFirstFuncInfo(ASQL, AFuncName: string; var ABegin, AEnd: integer; var AFunInfo: TSqlFuncInfo): boolean;//将原来的函数替换成新的函数(包括参数)
function ReplaceFunctionByNew(ASQL: string; ABegin, AEnd: integer; AFuncInf, ANewFuncInf: TSqlFuncInfo): string;//转换SQl Server中以 = 表示的列别名,例如: Abc = Field1 转换为 Field1 Abc
function ConvertFieldAlias(ASQL: string): string;implementationfunction ReplaceCommons(ASQL: string): string;
begin
Result := StringReplace(ASQL, ' #', ' T_', [rfReplaceAll]); //临时表的名称
Result := StringReplace(Result, ' as ', ' ', [rfReplaceAll]); //别名去掉as
end;function ConvertTopNWord(ASQL: string): string;
var
n, n2, i, topN: integer;
sTmp: string;
begin
Result := ASQL;
n := pos(' top ', Result);
if n <= 0 then
Exit;
i := n + 4;
sTmp := '';
while Result[i] = ' ' do
inc(i);
while not (Result[i] in [' ', '(']) do
begin
sTmp := sTmp + Result[i];
inc(i);
end;
n2 := i - 1;
if sTmp <> '' then
topN := strtoint(sTmp)
else
topN := -1;
Delete(Result, n + 1, n2 - n); //删除原来的 top N,只留一个空格
n := pos(' where ', Result);
if n > 0 then
Result := Copy(Result, 1, n - 1) + ' where (' + copy(Result, n +6, length(Result)) + ') And ROWNUM <= ' + inttostr(TopN)
else
Result := Result + ' Where ROWNUM <= ' + inttostr(TopN);
end;//转换SQl Server中以 = 表示的列别名,例如: Abc = Field1 转换为 Field1 Abc
function ConvertFieldAlias(ASQL: string): string;
var
st, n, k, j, p1, p2, p3, p4, p5: integer;
sTmp, sPttr, Word1, Word2, Word3, Word4, Word5: string;
b: boolean; function _Check: boolean;
begin
Result := false;
if (pos('select', Word1) > 0) or (pos('select', Word3) > 0) then
Result := true
else
begin
k := p3;
while k <= p4 do
begin
if ASQL[k] = ',' then
begin
Result := true;
Exit;
end;
Inc(k);
end; end;
end;
begin
Result := '';
sTmp := '';
//最近5个单词
Word1 := '';
Word2 := '';
Word3 := '';
Word4 := '';
Word5 := '';
n := 1;
//最近5个单词的尾部位置
p1 := 1;
p2 := 1;
p3 := 1;
p4 := 1;
p5 := 1;
b := false;
st := 0;
//扫描
while n <= Length(ASQL) do
begin
if ASQL[n] = '(' then
Inc(st)
else
if ASQL[n] = ')' then
Dec(st); if (ASQL[n] in [' ', '=', #13, #10, ',']) and (st = 0) then
begin
Word1 := Word2;
Word2 := Word3;
Word3 := Word4;
Word4 := Word5;
Word5 := sTmp;
sTmp := '';
p1 := p2;
p2 := p3;
p3 := p4;
p4 := p5;
p5 := n; while (n <= Length(ASQL)) and (ASQL[n] in [' ', ',', #13, #10]) do
begin
/// Result := Result + ASQL[n];
Inc(n);
end; if b then
begin
b := false;
if _Check then
begin
sPttr := Word5 + ' ' + Word4;
j := (P5-1) - (P4 - Length(Word4)) + 1; //原串的长度
while Length(sPttr) < j do
sPttr := sPttr +' ';
j := 1;
for k := (P4 - Length(Word4)) to P5 - 1 do
begin
ASQL[k] := sPttr[j];
Inc(j);
end;
// Result := Result + Word5 + ' ' + Word4;
end;
end;
if ASQL[n] = '=' then
b := true; while (n <= Length(ASQL)) and (ASQL[n] in [' ', '=', ',', #13, #10]) do
begin
// Result := Result + ASQL[n];
Inc(n);
end;
end;
// Result := Result + ASQL[n];
sTmp := sTmp + ASQL[n]; Inc(n);
end;
Result := ASQL;
end;
function GetFirstFuncInfo(ASQL, AFuncName: string; var ABegin, AEnd: integer; var AFunInfo: TSqlFuncInfo): boolean;
var
n, n2, k, ArgBegin, ArgEnd, st, ArgCount: integer;
sArgs: array [0..10] of string;
sTmp: string;
begin
Result := false;
ArgBegin := -1; //参数开始
ArgEnd := -1; //参数结束
AFunInfo.Name := AFuncName;
AFuncName := LowerCase(AFuncName);
n := pos(AFuncName, ASQL);
if n <= 0 then
Exit;
n2 := n;
while (n2 <= Length(ASQL)) and (ASQL[n2] <> '(') do
Inc(n2);
if n2 > Length(ASQL) then
Exit;
ArgBegin := n2 + 1;
st := 0;
n2 := n2 + 1;
while (n2 <= Length(ASQL)) do
begin
if ASQL[n2] = '(' then
Inc(st) //状态机
else
if ASQL[n2] = ')' then
if st = 0 then
begin
ArgEnd := n2 - 1;
Break;
end
else
Dec(st);
Inc(n2);
end;
if ArgEnd = -1 then //参数不正确
Exit; ABegin := n;
AEnd := n2; sTmp := '';
ArgCount := 0;
k := ArgBegin; //参数开始
st := 0; //状态机,用来处理函数嵌套的情况
while k <= Argend do //分离参数
begin
if (ASQL[k] = ',') and (st = 0) then
begin
sArgs[ArgCount] := sTmp;
Inc(ArgCount);
sTmp := '';
end
else
sTmp := sTmp + ASQL[k]; if ASQL[k]= '(' then
Inc(st)
else
if ASQL[k] = ')' then
Dec(st); Inc(k);
end; if sTmp <> '' then
begin //最后一个参数
sArgs[ArgCount] := sTmp;
Inc(ArgCount);
end; if ArgCount > 10 then
ArgCount := 10;
AFunInfo.ArgCount := ArgCount;
for n := 0 to Argcount - 1 do
AFunInfo.Args[n] := sArgs[n];
Result := true;
end;//将原来的函数替换成新的函数(包括参数)
function ReplaceFunctionByNew(ASQL: string; ABegin, AEnd: integer; AFuncInf, ANewFuncInf: TSqlFuncInfo): string;
var
sNewFunc, sArgs: string;
i: integer;
begin
//构造新函数的字串
sArgs := '';
for i := 0 to ANewFuncInf.ArgCount - 1 do
sArgs := sArgs + ANewFuncInf.Args[i] + ',';
if sArgs <> '' then
Delete(sArgs, length(sArgs), 1);
sNewFunc := ANewFuncInf.Name + '('+ sArgs + ')';
//替换原来的函数, ABegin 和 AEnd 是原来函数的起始位置
Result := Copy(ASQL, 1, ABegin - 1) + sNewFunc + Copy(ASQL, AEnd + 1, length(ASQL));
end;//替换参数一致,只是名称不同的函数
function ReplaceSameArgsFunctions(ASQL: string; AFuncName, ANewFuncName: string): string;
begin
AFuncName := lowercase(AFuncName);
Result := StringReplace(ASQL, AFuncName+'(', ANewFuncName + '(', [rfReplaceAll]);
Result := StringReplace(Result, AFuncName+' (', ANewFuncName + '(', [rfReplaceAll]); //考虑函数名和括号之间有一个空格的情况
end;function Convert_GetDate(ASQL: string): string;
var
AFunInfo, AFuncInfoNew: TSqlFuncInfo;
ABegin, AEnd: integer;
sPttr: string;
begin
Result := ASQL;
while GetFirstFuncInfo(Result, 'getdate', ABegin, AEnd, AFunInfo) do
begin
Result := Copy(Result, 1, ABegin - 1) + 'SYSTIMESTAMP' + Copy(Result, AEnd + 1, Length(Result));
end;
end;//替换right函数
function Convert_Right(ASQL: string): string;
var
AFunInfo, AFuncInfoNew: TSqlFuncInfo;
ABegin, AEnd, i: integer;
sPttr: string;
begin
Result := ASQL;
while GetFirstFuncInfo(Result, 'right', ABegin, AEnd, AFunInfo) do
begin
if AFunInfo.ArgCount < 2 then
Break;
AFuncInfoNew.Name := 'SUBSTR';
AFuncInfoNew.ArgCount := 3;
AFuncInfoNew.Args[0] := AFunInfo.Args[0];
AFuncInfoNew.Args[1] := ' -' + trim(AFunInfo.Args[1]);
AFuncInfoNew.Args[2] := AFunInfo.Args[1];
Result := ReplaceFunctionByNew(Result, ABegin, AEnd, AFunInfo, AFuncInfoNew);
end;
end;//替换Left函数
function Convert_Left(ASQL: string): string;
var
AFunInfo, AFuncInfoNew: TSqlFuncInfo;
ABegin, AEnd, i: integer;
sPttr: string;
begin
Result := ASQL;
while GetFirstFuncInfo(Result, 'left', ABegin, AEnd, AFunInfo) do
begin
if AFunInfo.ArgCount < 2 then
Break;
AFuncInfoNew.Name := 'SUBSTR';
AFuncInfoNew.ArgCount := 3;
AFuncInfoNew.Args[0] := AFunInfo.Args[0];
AFuncInfoNew.Args[1] := ' 1';
AFuncInfoNew.Args[2] := AFunInfo.Args[1];
Result := ReplaceFunctionByNew(Result, ABegin, AEnd, AFunInfo, AFuncInfoNew);
end;
end;//替换Len函数
function Convert_Len(ASQL: string): string;
var
AFunInfo, AFuncInfoNew: TSqlFuncInfo;
ABegin, AEnd, i: integer;
sPttr: string;
begin
Result := ASQL;
while GetFirstFuncInfo(Result, 'len', ABegin, AEnd, AFunInfo) do
begin
if AFunInfo.ArgCount < 1 then
Break;
AFuncInfoNew.Name := 'LENGTH';
AFuncInfoNew.ArgCount := 1;
AFuncInfoNew.Args[0] := AFunInfo.Args[0];
Result := ReplaceFunctionByNew(Result, ABegin, AEnd, AFunInfo, AFuncInfoNew);
end;
end;//替换IsNull函数
function Convert_IsNull(ASQL: string): string;
var
AFunInfo, AFuncInfoNew: TSqlFuncInfo;
ABegin, AEnd, i: integer;
sPttr: string;
begin
Result := ASQL;
while GetFirstFuncInfo(Result, 'isnull', ABegin, AEnd, AFunInfo) do
begin
if AFunInfo.ArgCount < 3 then
Break;
AFuncInfoNew.Name := 'NVL';
AFuncInfoNew.ArgCount := AFunInfo.ArgCount;
AFuncInfoNew.Args[0] := AFunInfo.Args[0];
AFuncInfoNew.Args[1] := AFunInfo.Args[1];
AFuncInfoNew.Args[2] := AFunInfo.Args[2];
Result := ReplaceFunctionByNew(Result, ABegin, AEnd, AFunInfo, AFuncInfoNew);
end;
end;//替换SubString函数
function Convert_SubString(ASQL: string): string;
var
AFunInfo, AFuncInfoNew: TSqlFuncInfo;
ABegin, AEnd, i: integer;
sPttr: string;
begin
Result := ASQL;
while GetFirstFuncInfo(Result, 'substring', ABegin, AEnd, AFunInfo) do
begin
if AFunInfo.ArgCount < 3 then
Break;
AFuncInfoNew.Name := 'SUBSTR';
AFuncInfoNew.ArgCount := AFunInfo.ArgCount;
AFuncInfoNew.Args[0] := AFunInfo.Args[0];
AFuncInfoNew.Args[1] := AFunInfo.Args[1];
AFuncInfoNew.Args[2] := AFunInfo.Args[2];
Result := ReplaceFunctionByNew(Result, ABegin, AEnd, AFunInfo, AFuncInfoNew);
end;
end;//替换DateDiff函数
function Convert_DateDiff(ASQL: string): string;
var
AFunInfo: TSqlFuncInfo;
ABegin, AEnd, i: integer;
sPttr: string;
begin
Result := ASQL;
while GetFirstFuncInfo(Result, 'datediff', ABegin, AEnd, AFunInfo) do
begin
if AFunInfo.ArgCount < 3 then
Break;
if AFunInfo.Args[0] = 'month' then
begin
// AFuncInfoNew.Name := 'TRUNC';
sPttr := 'TRUNC(MONTHS_BETWEEN(' + AFunInfo.Args[2] + ' - ' + AFunInfo.Args[1] + '))';
end
else
begin
// AFuncInfoNew.Name := 'EXTRACT';
sPttr := 'EXTRACT(' + AFunInfo.Args[0] + ' from ' + AFunInfo.Args[2] + ' - ' + AFunInfo.Args[1] + ')';
end;
Result := Copy(Result, 1, ABegin - 1) + sPttr + Copy(Result, AEnd + 1, Length(Result));
// Result := ReplaceFunctionByNew(Result, ABegin, AEnd, AFunInfo, AFuncInfoNew);
end;
end;function ConvertFunctions(ASQL: string): string;
begin
Result := ReplaceSameArgsFunctions(ASQL, 'isnull', 'NVL');
Result := ReplaceSameArgsFunctions(Result, 'substring', 'SUBSTR');
//先替换标准写法的参数相同的函数,对于非标准写法,例如函数名和括号间有空格,后再调用专门的函数处理
if pos('len(', Result) > 0 then
Result := StringReplace(Result, 'len(', 'LENGTH(', [rfReplaceAll]);
if pos('getdate()', Result) > 0 then
Result := StringReplace(Result, 'getdate()', 'SYSTIMESTAMP', [rfReplaceAll]); if pos('len', Result) > 0 then
Result := Convert_Len(Result); if pos('getdate', Result) > 0 then
Result := Convert_GetDate(Result); if pos('right', Result) > 0 then
Result := Convert_Right(Result); if pos('left', Result) > 0 then
Result := Convert_Left(Result); if pos('isnull', Result) > 0 then
Result := Convert_IsNull(Result); if pos('substring', Result) > 0 then
Result := Convert_SubString(Result); if pos('datediff', Result) > 0 then
Result := Convert_DateDiff(Result); Result := ConvertFieldAlias(Result);
end;function ConvertSQLToOracle(ASQL: string): string;
begin
Result := ReplaceCommons(LowerCase(ASQL)); //替换基本的字符
if pos(' top ', Result) > 0 then //转换top N 子句
Result := ConvertTopNWord(Result); Result := ConvertFunctions(Result); //替换函数
end;end.
right('asdfads', 32) , len('adfa', 23), DateDiff(day, GetDate(), D2 + 33) from
oitm where IsNull(ItemCode, '') LIke '343%' and ItemCode in
(Select Code as Code2 From (select Code From table2 as B) as #t1)Oracel:
select itemcode code, discription+'adfs' name2 , SUBSTR(SUBSTR('dfsdf' + codebar, -10,10), 1, 6),
SUBSTR('asdfads', -32, 32) , LENGTH('adfa', 23), EXTRACT(day from d2 + 33 - SYSTIMESTAMP) from
oitm where ( NVL(itemcode, '') like '343%' and itemcode in
(select code code2 from (select code from table2 b) T_t1)
) And ROWNUM <= 100