firedac儲存過程

firedac儲存過程

unit DB.storedproc;
// cxg 2024-12-7
// 儲存過程
{$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;
begin
  jo.InitObject([]);
  try
    try
      pool := GetDBPool(dbid);
      DB := pool.Lock;
      DB.sp.Close;
      DB.sp.Params.Clear;
      DB.sp.StoredProcName := procName;
      DB.sp.Prepare;
      onInParam(DB.sp, inParam);   //處理入參
      DB.sp.Execute;               //執行儲存過程
      onOutParam(DB.sp, jo);       //處理出參
      DB.sp.active := True;
      jo.AddValue('dataset', _json(DB.sp.tojsonarraystring));
      send(ctxt, jo.toJSON);
    except
      on E: Exception do
      begin
        send(ctxt, error(E.Message));
        WriteLog('db.storedproc.open()' + E.Message);
      end;
    end;
  finally
    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"}
}