unidac存储过程

unidac存储过程

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

interface

uses
  //mormot------
  mormot.core.variants,
  //my-------
  db.Unidac,
  sys.global, DB.unidacpool, dataset.Serialize,
  //unidac-------
  uni,
  //system-------
  Classes, SysUtils, DB;

type
  { Tsp }

  Tsp = record
    dbid: string;       //数据库帐套号
    ctxt: Tcontext;     //通讯上下文
    procName: string;   //存储过程名
    inParam: Pjson;//入参
    //执行存储过程
    procedure Open;
    //给input参数赋值
    procedure onInParam(sp: TUniStoredProc; param: Pjson);
    //序列output参数
    procedure onOutParam(sp: TUniStoredProc; 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.Params.Clear;
      DB.sp.StoredProcName := procName;
      DB.sp.active := True;         //自动生成存储过程的参数
      onInParam(DB.sp, inparam);    //处理入参
      DB.sp.Execute;                //执行存储过程
      onOutParam(DB.sp, jo);        //处理出参
      i := 0;
      repeat                        //返回多个数据集
        jo.AddValue('dataset' + i.ToString, _json(DB.sp.tojsonarraystring));
        Inc(i);
      until not DB.sp.OpenNext;
      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: TUniStoredProc; param: Pjson);
var
  i: integer;
  p: Tuniparam;
  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: TUniStoredProc; var param: Tjson);
var
  p: Tuniparam;
  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"}
}