firedac的TFDStoredProc动态创建并调用存储过程

 

1）中间件执行存储过程

sp.Close;
sp.StoredProcName := procName;
sp.Prepare;  // 生成存储过程的参数列表，无任何OUTPUT的存储过程，也会自动生成一个@ReturnValue的返回值参数
params := TFDParams.Create;
try
  UnpackFDParams(inParams, params);  // 还原客户端上传的参数
  for i:=0 to params.Count -1 do
    for h:=0 to d.procOpen.ParamCount-1 do
      if sp.Params[h].Name = params[i].Name then
      begin
         sp.Params[h].Value := params[i].Value;  // 给存储过程的参数赋值
         Break;
      end;
finally
  params.Free;
end;
sp.open;
2）客户端调用远程方法执行存储过程

procedure TfrmMain.Button2Click(Sender: TObject);
begin
  cds2.Params.Clear;
  cds2.Params.CreateParam(ftString,'@workno',ptInput).Value :='1';  // 参数前面记得要加@
  cds2.Data := ProcOpen('9999','3',PackageParams(cds2.Params));
end;

3）带OUTPUT的存储过程

function TServerMethods1.spOpenOut(const accountNo, spName: WideString; inParams: OleVariant): OleVariant;
var
d: TfrmDB;
params: TFDParams;
i: Integer;
param: TFDParam;
begin
Result := null;
if (accountNo = '') or (spName = '') then
Exit;
d := GetDBPool(accountNo).Lock;
if not Assigned(d) then
Exit;
try
try
d.procOpen.Close;
d.procOpen.StoredProcName := spName;
d.procOpen.Prepare;
params := TFDParams.Create;
try
UnpackFDParams(inParams, params);
for i := 0 to params.Count - 1 do
begin
param := d.procOpen.FindParam(params[i].Name);
if not Assigned(param) then
Continue;
param.value := params[i].value;
end;
finally
params.Free;
end;

Result := VarArrayCreate([0, 1], varVariant);
Result[0] := d.dspProcOpen.Data; // 查询数据集
Result[1] := PackageFDParams(d.procOpen.params); // OUTPUT参数

except
on e: Exception do
begin
Result := null;
Log.WriteLog('TServerMethods1.spOpenOut ' + e.Message);
Exit;
end;
end;
finally
d.procOpen.Close;
GetDBPool(accountNo).Unlock(d);
end;
end;