firedac存储过程返回多个结果

firedac存储过程

 

unit DB.storedproc;
// cxg 2024-12-12
// 存储过程
{$i def.inc}

interface

uses
  // mormot------
  mormot.core.variants,
  // my-------
  DB.firedac,
  sys.global, DB.firedacpool, dataset.Serialize,
  // firedac-------
  firedac.comp.Client, firedac.comp.dataset, firedac.stan.Param,
  // system-------
  Classes, SysUtils, DB;

type
  { Tsp }

  Tsp = record
    dbid: string; // 数据库帐套号
    ctxt: Tcontext; // 通讯上下文
    procName: string; // 存储过程名
    inParam: Pjson; // 入参
    // 执行存储过程
    procedure Open;
    // 给input参数赋值
    procedure onInParam(sp: TfdStoredProc; Param: Pjson);
    // 序列output参数
    procedure onOutParam(sp: TfdStoredProc; var Param: Tjson);
  end;

implementation

{ Tsp }

procedure Tsp.Open;
var
  DB: tdb;
  pool: tdbpool;
  jo: Tjson;
  i: Integer;
begin
  jo.InitObject([]);
  try
    try
      pool := GetDBPool(dbid);
      DB := pool.Lock;
      DB.sp.Close;
      db.sp.FetchOptions.AutoClose := false;  //must be set
      DB.sp.Params.Clear;
      DB.sp.StoredProcName := procName;
      DB.sp.Prepare;
      onInParam(DB.sp, inParam);   //处理入参
      DB.sp.Execute;               //执行存储过程
      onOutParam(DB.sp, jo);       //处理出参
      DB.sp.Open;
      db.fdm.close;
      db.fdm.Data := db.sp.Data;
      jo.AddValue('dataset0', _json(DB.fdm.tojsonarraystring));
      i := 0;
      while True do   //返回多个数据集
      begin
        db.sp.NextRecordSet;
        if db.sp.Active then
        begin
          db.fdm.Close;
          db.fdm.Data := db.sp.Data;
          Inc(i);
          jo.AddValue('dataset' + i.ToString, _json(DB.fdm.tojsonarraystring));
        end
        else Break;
      end;
      send(ctxt, jo.toJSON);
    except
      on E: Exception do
      begin
        send(ctxt, error(E.Message));
        WriteLog('db.storedproc.open()' + E.Message);
      end;
    end;
  finally
    db.sp.FetchOptions.AutoClose := true;
    pool.Unlock(DB);
  end;
end;

procedure Tsp.onInParam(sp: TfdStoredProc; Param: Pjson);
var
  i: integer;
  p: Tfdparam;
  pname: string;
begin
  try
    for i := 0 to sp.Params.Count - 1 do
    begin
      p := sp.Params[i];
      pname := p.Name;
      if sametext('return_value', pname) then
        continue;
      if p.ParamType in [ptUnknown, ptOutput, ptResult, ptInputOutput] then
        continue;
      case p.DataType of
        ftstring, ftwidestring:
          p.AsWideString := Param.s[pname];
        ftLargeint:
          p.AsLargeInt := Param.i[pname];
        ftInteger:
          p.AsInteger := Param.i[pname];
        ftboolean:
          p.AsBoolean := Param.b[pname];
        ftfloat, ftCurrency:
          p.AsFloat := Param.d[pname];
        ftDateTime:
          p.AsDateTime := Param.d[pname];
      end;
    end;
  except
    on E: Exception do
    begin
      send(ctxt, error(E.Message));
      WriteLog('db.storedproc.onInParam()' + E.Message);
    end;
  end;
end;

procedure Tsp.onOutParam(sp: TfdStoredProc; var Param: Tjson);
var
  p: Tfdparam;
  i: integer;
  pname: string;
begin
  try
    for i := 0 to sp.Params.Count - 1 do
    begin
      p := sp.Params[i];
      pname := p.Name;
      if sametext(pname, 'return_value') then
        continue;
      if p.ParamType in [ptUnknown, ptInput, ptResult] then
        continue;
      case p.DataType of
        ftstring, ftwidestring:
          Param.s[pname] := p.AsWideString;
        ftLargeint:
          Param.i[pname] := p.AsLargeInt;
        ftInteger:
          Param.i[pname] := p.AsInteger;
        ftboolean:
          Param.b[pname] := p.AsBoolean;
        ftfloat, ftCurrency, ftDateTime:
          Param.d[pname] := p.AsFloat;
      end;
    end;
  except
    on E: Exception do
    begin
      send(ctxt, error(E.Message));
      WriteLog('db.storedproc.onOutParam()' + E.Message);
    end;
  end;
end;

end.

 

json入参

{
    "procname":"sp_9",
    "inparam":{"goodsid":"100036"}
}