Summe mit Stored Procedure

[Hansa]

Hi,

ich muß 3 Spalten einer Table aufaddieren. In einer Zeile ! Nicht untereinander. Also 3 Felder jedes Datensatzes als Summe brauche ich. Hierzu wollte ich eine Stored Procedure in der DB anlegen, die den Wert zurückliefert. Irgendwie bin ich jetzt aber mit den Input/Output-Parametern / Variablen durcheinander geraten. Hat jemand ein kleines Beispiel ?

[MrSpock]

Hallo Hansa,

das hört sich wie ein typischer Anwendungsfall der COMPUTED BY Klausel an:

SQL-Code:

CREATE TABLE test
  (zahl1 INTEGER,
   zahl2 INTEGER,
   zahl3 INTEGER,
   dieSumme COMPUTED BY (zahl1+zahl2+zahl3))

Dann bräuchtest du keine Stored Procedure.

[Hansa]

Stimmt ! So gehts auch. Aber momentan mit diesem Effekt : Beim Insert wird der Wert nicht berechnet. Bei einem Update dagegen schon. Wieso das ?

Noch eine prinzipielle Frage : Wird die Spalte physikalisch in der DB angelegt oder nur logisch ? Obiger Effekt deutet eher darauf hin, daß sie tatsächlich in der DB vorhanden ist. Dann hätte ich eine unnötige Redundanz. Und das führt (wie hier) zu seltsamen Nebeneffekten.

In diesem Fall wäre das Computed by schon geeignet, wenn es denn fehlerfrei funktioniert. Zwischenzeitlich habe ich aber auch eine Stored Procedure. Die macht, was ich will, aber ich komme von Delphi aus nicht an den ermittelten Wert. Es müßte doch mit "select * from Proc" gehen, oder nicht ? Nur, wohin muß das überhaupt ?

[Robert_G]

Warum nicht einfach das Ganze in einer Abfrage berechnen?
Ich finde es ziemlich sinnlos bei jedem UPDATE/INSERT das olle Feld zu berechnen.
Könnte ein neuer Eintrag in der Liste werden: "Wie bremse ich am einfachsten meine DB aus" :mrgreen:

[Leuselator]

Wie Robert_G schon fragt, warum nicht:

SQL-Code:

SELECT SUM(Feld1) As Summe1
     , SUM(Feld2) As Summe2
     , SUM(Feld3) As Summe3
     , SUM(Feld1)+SUM(Feld2)+SUM(Feld3) As SummeGesamt
  FROM Tabelle

bzw. auf Satzebene:

SQL-Code:

SELECT *
     , Feld1+Feld2+Feld3 AS FeldSumme
  FROM Tabelle

:?:
Gruß

[Hansa]

Aha, jo. 8) Hätte ich auch selber drauf kommen können. Ein extra Feld mitschleppen wollte ich nämlich eigentlich nicht. Jetzt braucht ihr mir nur noch zu sagen, warum ich über Stored Procedure den Wert nicht erhalte. :-D

[Leuselator]

DB?

[Hansa]

IB :mrgreen:

[Lemmy]

Hi Hansa,

SQL-Code:

CREATE PROCEDURE Summe 
RETURNS (Spalte1 NUMERIC(18), Spalte2 NUMERIC(18), Spalte2 NUMERIC(18))
AS
BEGIN
  FOR
    SELECT SUM(Saplte1), SUM(Spalte2), SUM(Spalte3) FROM Tabelle INTO Spalte1, Spalte2, Spalte3
  DO
   SUSPEND;
END^

So sollte es gehen, allerdings ohne Gewehr, da heute Sonntag und ich keine FB zur Hand habe....

Grüße
Lemmy

[Robert_G]

Mal eine Frage eines PL/SQL'lers, dessen Prozeduren nur über Umwege Ergnismengen ausspucken:
Wozu muss hier, auf Teufel komm raus, eine SP benutzt werden, wenn auch ein simples SELECT-Statement den Job erledigen kann?
Das ist jetzt schon das x'te Mal, dass ich mich das Frage...

[Hansa]

Robert, die Wege sind manchmal unergründlich. Das mußt Du noch lernen :lol: Schumi gewinnt angeblich nur deshalb, weil er die nächste Schikane schon im Kopf hat und die anderen erst dann, wenn sie sie sehen. 8) Ähmm ja, und so mache ich das halt auch. :mrgreen:

Orientiere Dich bei Antworten eventuell auch am Titel des Threads. Der Spezialfall scheint gelöst (weil noch nicht getestet) , aber die im Kontext aufgetauchte Frage noch nicht.

[Robert_G]

Zitat:

Zitat von Dizzy

...Orientiere Dich bei Antworten eventuell auch am Titel des Threads...

Solange ich bei der Frage nach einem deutschen SQL-Editor nicht mit einem Wörterbuch kontere, kann es ja nicht sooo falsch sein. :mrgreen:

Auch wenn Hansa explizit nach einer Lösung in einer SP gefragt hat, bezweifle ich immer noch den Sinn einer SP (vor allem aus Performance-Gründen!!!).
Ich glaube kaum, dass durch die SP noch ein vernünftiger Query-Plan erstellt werden kann -> die Zugriffe werden also nicht mehr optimal ablaufen.

[Hansa]

Ja, genau so ist es ! Der Kleinkram hier ist wohl mit den AS zu erledigen. Aber an die Summe (so oder so) komme ich nicht ran. 8)

[Robert_G]

Eins von beiden würde auch bei dir laufen:

Delphi-Quellcode:

With ADOQuery Do
Begin
  SQL.Text :=
    'SELECT SUM(Feld1) AS Summe1' + #10 +
    '      ,SUM(Feld2) AS Summe2' + #10 +
    '      ,SUM(Feld3) AS Summe3' + #10 +
    '      ,SUM(Feld1) + SUM(Feld2) + SUM(Feld3) AS SummeGesamt' + #10 +
    'FROM   Tabelle';
  Open;
  With IrgendeineListBox.Items Do
    While not Eof Do
    Begin
      Add(FieldbyName('Summe1').AsString);
      Next;
    End;
  //...
End;
With OracleQuery Do
Begin
  SQL.Text :=
    'SELECT SUM(Feld1) AS Summe1' + #10 +
    '      ,SUM(Feld2) AS Summe2' + #10 +
    '      ,SUM(Feld3) AS Summe3' + #10 +
    '      ,SUM(Feld1) + SUM(Feld2) + SUM(Feld3) AS SummeGesamt' + #10 +
    'FROM   Tabelle';
  Execute;
  With IrgendeineListBox.Items Do
    While not Eof Do
    Begin
      Add(FieldAsString('Summe1'));
      Next;
    End;
End;

Edit1+2: Tippfehler...

[Lemmy]

Guten Morgen,

Zitat:

Zitat von Robert_G

Auch wenn Hansa explizit nach einer Lösung in einer SP gefragt hat, bezweifle ich immer noch den Sinn einer SP (vor allem aus Performance-Gründen!!!).
Ich glaube kaum, dass durch die SP noch ein vernünftiger Query-Plan erstellt werden kann -> die Zugriffe werden also nicht mehr optimal ablaufen.

mal ganz langsam mit den jungen Pferden.... Eine SP ist in IB/FB IMMER *) schneller als eine "normale" SQL:

1. Eine SP bearbeitet die Daten direkt auf dem Server. Wenn wir von einem "Normalzustand" ausgehen, dass ein Server in einer Firma immer die größte Maschine ist (viel Speicher, schnelle HDD,....) dann wird dort die Ausführung schneller ablaufen als im Programmcode auf dem Client.
2. Es werden nur noch die Daten übers Netz gejagt, die der Client wirklich anfordert und nicht mehr die große Datenmenge, die er bracuht um das Ergebnis zu erhalten (ich stimme zu, dass dieser Punkt bei dem gefragten SQL-Statement nicht wirklich zieht).
3. Wenn Berechnungen usw. auf den SQL-Server ausgelagert werden, ist eine evtl. notwendige Fehlerbehebung mit einem einfachen SQL-Statement möglich. Es muss nicht mehr das Client-Programm gepacht werden (Neucompilierung, verteilen des Codes im Netzwerk), sondern die Business-Rules können mit einer DDL-SQL einfach geändert bzw. gepatcht werden und das im laufenden Betrieb.
4. Der wichtigste Punkt: EIne SP wird sozusagen "compiliert" in der Datenbank abgespeichert, so dass verschiedene Schritte, die das DMS beim Ausführen einer "normalen" SQL machen muss, einfach nicht mehr notwendig sind. Dadurch ergibt sich eine Zeitersparnis!

Es gibt also einige Punkte, die den Einsatz einer SP sinnvoll machen.

Grüße
Lemmy

P.S.: Welche Frage ist denn im Kontext dieses Threads aufgetacuht???


*) Keine Regel ohne Ausnahme!

[Robert_G]

:shock:
Hast du dir das genau überlegt?
Wenn ich ein SELECT an den Server schicke, bekomme ich nur die Ergebnissse zurück, nicht mehr.
Wenn sich das Statement zwischen zwei Aufrufen nicht ändert (solange sie zeitlich nicht zu weit auseinander liegen), muss es nicht neu geparst werden, in Oracle wird es auch kein 2. Mal komplett ausgeführt, es werden nur die Werte im Cache neu gefiltert.
Das Ganze setzt natürlich voraus, dass IB/FB über einen vernünftigen Optimizer verfügt, der den Query-Plan erstellt und einem entsprechenden Cache-System. (ich habe keinen Plan von IB/FB :roll: )
Wenn Hansa die Daten nur in dieser Form haben will, wäre eine SP sinnlos & langsamer.
Will er noch mehr mit den Daten anstellen, bevor er sie im Client benutzt, wäre natürliche eine SP angebracht.

[Lemmy]

Hi,

Zitat:

Zitat von Robert_G

Hast du dir das genau überlegt?
Wenn ich ein SELECT an den Server schicke, bekomme ich nur die Ergebnissse zurück, nicht mehr.

Klar, die SP von Hansa ist jetzt nicht wirklich der Brüller, so dass dort eine Begrenzung der Datenmenge nicht stattfindet, da SUM schon eine eingebaute Funktion ist. Sobald er aber die Ergebnismenge wieder in eine andere Tabelle zurückschreiben will (z.B. Logbuch), dann spart er sich mit dem Einsatz einer entsprechenden SP den Datentransfer!

Zitat:

Zitat von Robert_G

Wenn sich das Statement zwischen zwei Aufrufen nicht ändert (solange sie zeitlich nicht zu weit auseinander liegen), muss es nicht neu geparst werden, in Oracle wird es auch kein 2. Mal komplett ausgeführt, es werden nur die Werte im Cache neu gefiltert.

Das mag in Oracle sein, bei IB/FB kann ich mir dieses Verhalten gerade nicht vorstellen. Ich gehe davon aus, dass bei jedem SQL-Statement der komplette Ablauf durchgegangen wird.

Zitat:

Zitat von Robert_G

Das Ganze setzt natürlich voraus, dass IB/FB über einen vernünftigen Optimizer verfügt, der den Query-Plan erstellt und einem entsprechenden Cache-System. (ich habe keinen Plan von IB/FB :roll: )

1 ja, 2 ja, 3 nein

Zitat:

Zitat von Robert_G

Wenn Hansa die Daten nur in dieser Form haben will, wäre eine SP sinnlos & langsamer.
Will er noch mehr mit den Daten anstellen, bevor er sie im Client benutzt, wäre natürliche eine SP angebracht.

Wie schon gesagt auf keinen Fall langsamer, da sich IB/FB einige Schritte beim Ausführen einer SP spart. Aber, selbst wenn eine SP langsamer wäre würde ich diese einem normalen SQL-Statement vorziehen, da sie sich wesentlich leichter ändern lässt als das Client-Programm, vorallem wenn es sich beim Kunden befindet!

Grüße
Lemmy

[Leuselator]

folgendes aus dem Kopf, also nicht getestet.
Die Syntax könnte dann z.B. in etwa so sein (MSSQL - IB kein Plan):

SQL-Code:

DECLARE PROCEDURE SUMMIERE
( S0 int OUTPUT
, S1 int OUTPUT
, S2 int OUTPUT
, S3 int OUTPUT
)
AS
BEGIN
  SELECT S0 = SUM(Feld1)
       , S1 = SUM(Feld2)
       , S2 = SUM(Feld3)
       , S3 = SUM(Feld1)+SUM(Feld2)+SUM(Feld3)
    FROM Tabelle
END

in Delphi:

Delphi-Quellcode:

function GetSummenFromDb(var Sum1: Integer;
                         var Sum2: Integer;
                         var Sum3: Integer;
                         var Sum4: Integer;
                             Con : TADOConnection):Boolean;
var 
  arySumme := TADOQuery;
  i : Integer;
begin
  Result := True;
  arySumme := TADOQuery.Create(nil);
  with arySumme do try
    Connection    := Con;
    SQL.Add('EXECUTE SUMMIERE :S0,:S1,:S2,:S3');
    for i := 0 to 3 do begin
      Parameters.Add;
      Parameters[i].Name        := 'S'+;
      Parameters[i].DataType    := ftInteger;
      Parameters[i].Size        := -1;
      Parameters[i].Value       := NULL;
    end;
    try
      arySumme.ExecSQL;
      S0 := Parameters[0].Value;
      S1 := Parameters[1].Value;
      S2 := Parameters[2].Value;
      S3 := Parameters[3].Value;
    except
      Result := False;
    end;
  finally
    FreeAndNil(arySumme);
  end;
end;

Zum philosophischen Streit:
ich habe in meinem aktuellen Projekt auch SP's benutzt, wo es nur ging (selbst für einfache Abfragen), da ich in Delphi dann nur 'Execute Procedurename Parameterliste' als SQL-Text zu verwenden brauche. Ändert sich was an der DB-Struktur, brauche ich diese Änderungen nur in den SP's auf dem DB-Server nachzuvollziehen, und muß den Client-Code nicht mehr anfassen - gerade bei komplexen DB-Modellen ein Segen. Da MSSQL auch Query's aus SP's cached, denke ich, dass es kaum Performanceunterschiede geben sollte.

Gruß

[Hansa]

Das Thema ist immer noch nicht ganz geschlossen. :shock: Ich brauche eine Stored Procedure für folgenden Zweck : Umsatz 0%, 7%, 16% sind in einer Tabelle gespeichert. Ich brauche an diversen Stellen die Summe. Jetzt weiß ich nicht mehr, wie ich an das Ergebnis komme.

Die stored Procedure funktioniert. In IBexpert wird alles richtig angezeigt. In Delphi komme ich aber nicht weiter. Wie komme ich da an das errechnete Ergebnis wieder dran ?