變相解決Unidac無法向Postgresql傳遊標RefCursor的問題

標籤：

不說了,先上代碼.

PG:

--建立t1表CREATE TABLE public.t1 (  f1 INTEGER NOT NULL,  f2 VARCHAR(20),  f3 VARCHAR(20)[][] [],  CONSTRAINT t1_pkey PRIMARY KEY(f1)) WITH (oids = false);--建立以t1表行為元素的數組,用來代替REFCURSORCREATE 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;

 

變相解決Unidac無法向Postgresql傳遊標RefCursor的問題