变相解决Unidac无法向Postgresql传游标RefCursor的问题

CREATE TABLE public.t1 ( f1 INTEGER NOT NULL, f2 VARCHAR(20), f3 VARCHAR(20)[][] [], CONSTRAINT t1_pkey PRIMARY KEY(f1) ) WITH (oids = false); --建立以t1表行为元素的数组,用来代替REFCURSOR CREATE OR REPLACE FUNCTION public.fun1 ( p1 public.t1 [] ) RETURNS varchar [] [] AS $body$ DECLARE row1 public.t1%rowtype; arr1 VARCHAR [ ]; BEGIN FOREACH row1 in array p1 loop arr1 := array_append(arr1,row1.f2) ; end LOOP; RETURN arr1; END; $body$ LANGUAGE ‘plpgsql‘

Delphi:

//把整个表的表字段传到PG中Array结构的string中,当然也可以挑选字段来传,不用的字段,直接增加个","就行.
function DataSeRecordToArrayString(ACds: TDataSet): string;
var
  I: Integer;
  sRecord: string;
begin
  for I := 0 to ACds.FieldCount - 1 do
  begin
    if ACds.Fields[I].IsNull then
      Result := Result + ‘,‘
    else
    begin
      sRecord := ACds.Fields[I].AsString;
      if ACds.Fields[I].DataType in [ftBlob, ftObject, ftDataSet, ftGraphic] then
        Result := Result + ‘,‘ + sRecord
      else
      begin
        //处理转义字符 如果是正常文本 可以注释掉以加速

        if sRecord.contains(‘"‘) then
          sRecord := sRecord.Replace(‘"‘, ‘\"\"‘, [rfReplaceAll]);
        if sRecord.IndexOfAny([‘,‘, ‘ ‘, ‘{‘, ‘"‘]) >= 0 then
          sRecord := ‘\"‘ + sRecord + ‘\"‘;
        Result := Result + ‘,‘ + sRecord;
      end;
    end;
  end;
  Result := ‘{"(‘ + Result.Substring(1) + ‘)"}‘;
end;
//测试
procedure TForm1.Button1Click(Sender: TObject);
var
  s, s1, s2: string;
begin
  UniQuery1.SQL.Text := ‘select * from t1‘;
  UniQuery1.Open;
  UniQuery1.First;
  s := DataSeRecordToArrayString(UniQuery1);
  UniQuery1.Next;
  s1 := DataSeRecordToArrayString(UniQuery1);
  UniQuery1.Next;
  s2 := DataSeRecordToArrayString(UniQuery1);
  with UniStoredProc1 do
  begin
    Close;
    ParamByName(‘p1‘).AsString := Format(‘{%s,%s,%s}‘, [s, s1, s2]);
    try
      ExecProc;
    except
      on E: Exception do
      begin
        ShowMessage(E.Message);
        Exit;
      end;
    end;
    ShowMessage(Fields[0].AsString);
  end;
end;

标签：