Zugriff auf Daten mit gespeicherten Prozeduren

Eine der "Best Practices" ist der Zugriff auf Daten über gespeicherte Prozeduren. Ich verstehe, warum dieses Szenario gut ist. Meine Motivation ist Split-Datenbank und Anwendungslogik (die Tabellen können mich ändern, wenn das Verhalten der gespeicherten Prozeduren gleich ist), Verteidigung für SQL-Injektion (Benutzer können nicht ausführen "select * from some_tables", sie können nur gespeicherte Prozeduren aufrufen), und Sicherheit (in gespeicherten Prozedurkann "alles" sein, die sicher sein, dass Benutzer nicht auswählen/einfügen / aktualisieren / löschen Daten, die nicht für sie ist ).

Was ich nicht weiß, ist, wie man mit dynamischen Filtern auf Daten zugreift.

Ich verwende MSSQL 2005.

Wenn ich Tabelle habe:

CREATE TABLE tblProduct (
   ProductID uniqueidentifier -- PK
   , IDProductType uniqueidentifier -- FK to another table
   , ProductName nvarchar(255) -- name of product
   , ProductCode nvarchar(50) -- code of product for quick search
   , Weight decimal(18,4)
   , Volume decimal(18,4)
)

dann sollte ich 4 gespeicherte Prozeduren erstellen ( erstellen / lesen / aktualisieren / löschen ).

Die gespeicherte Prozedur für "create" ist einfach.

CREATE PROC Insert_Product ( @ProductID uniqueidentifier, @IDProductType uniqueidentifier, ... etc ... ) AS BEGIN
   INSERT INTO tblProduct ( ProductID, IDProductType, ... etc .. ) VALUES ( @ProductID, @IDProductType, ... etc ... )
END

Die gespeicherte Prozedur für "löschen" ist auch einfach.

CREATE PROC Delete_Product ( @ProductID uniqueidentifier, @IDProductType uniqueidentifier, ... etc ... ) AS BEGIN
    DELETE tblProduct WHERE ProductID = @ProductID AND IDProductType = @IDProductType AND ... etc ...
END

Die gespeicherte Prozedur für "update" ist ähnlich wie für "löschen", aber ich bin mir nicht sicher, ob dies der richtige Weg ist, wie man es tut. Ich denke, dass die Aktualisierung aller Spalten nicht effizient ist.

CREATE PROC Update_Product( @ProductID uniqueidentifier, @Original_ProductID uniqueidentifier, @IDProductType uniqueidentifier, @Original_IDProductType uniqueidentifier, ... etc ... ) AS BEGIN
   UPDATE tblProduct SET ProductID = @ProductID, IDProductType = @IDProductType, ... etc ...
      WHERE ProductID = @Original_ProductID AND IDProductType = @Original_IDProductType AND ... etc ...
END

Und die letzte - gespeicherte Prozedur für "Lesen" ist für mich ein kleines Mysterium. Wie werden Filterwerte für komplexe Bedingungen übergeben? Ich habe ein paar Vorschläge:

Verwenden von XML-Parameter für die Übergabe, wo Bedingung:

CREATE PROC Read_Product ( @WhereCondition XML ) AS BEGIN
    DECLARE @SELECT nvarchar(4000)
    SET @SELECT = 'SELECT ProductID, IDProductType, ProductName, ProductCode, Weight, Volume FROM tblProduct'

    DECLARE @WHERE nvarchar(4000)
    SET @WHERE = dbo.CreateSqlWherecondition( @WhereCondition ) --dbo.CreateSqlWherecondition is some function which returns text with WHERE condition from passed XML

    DECLARE @LEN_SELECT int
    SET @LEN_SELECT = LEN( @SELECT )
    DECLARE @LEN_WHERE int
    SET @LEN_WHERE = LEN( @WHERE )
    DECLARE @LEN_TOTAL int
    SET @LEN_TOTAL = @LEN_SELECT + @LEN_WHERE
    IF @LEN_TOTAL > 4000 BEGIN
        -- RAISE SOME CONCRETE ERROR, BECAUSE DYNAMIC SQL ACCEPTS MAX 4000 chars
    END

    DECLARE @SQL nvarchar(4000)
    SET @SQL = @SELECT + @WHERE

    EXEC sp_execsql @SQL
END

Aber, Ich denke, die Begrenzung von "4000" Zeichen für eine Abfrage ist hässlich.

Der nächste Vorschlag ist die Verwendung von Filtertabellen für jede Spalte. Fügen Sie Filterwerte in die Filtertabelle ein und rufen Sie dann die gespeicherte Prozedur mit der ID der Filter auf:

CREATE TABLE tblFilter (
   PKID uniqueidentifier -- PK
   , IDFilter uniqueidentifier -- identification of filter
   , FilterType tinyint -- 0 = ignore, 1 = equals, 2 = not equals, 3 = greater than, etc ...
   , BitValue bit , TinyIntValue tinyint , SmallIntValue smallint, IntValue int
   , BigIntValue bigint, DecimalValue decimal(19,4), NVarCharValue nvarchar(4000)
   , GuidValue uniqueidentifier, etc ... )

CREATE TABLE Read_Product ( @Filter_ProductID uniqueidentifier, @Filter_IDProductType uniqueidentifier, @Filter_ProductName uniqueidentifier, ... etc ... ) AS BEGIN
   SELECT ProductID, IDProductType, ProductName, ProductCode, Weight, Volume
   FROM tblProduct
   WHERE ( @Filter_ProductID IS NULL
            OR ( ( ProductID IN ( SELECT GuidValue FROM tblFilter WHERE IDFilter = @Filter_ProductID AND FilterType = 1 ) AND NOT ( ProductID IN ( SELECT GuidValue FROM tblFilter WHERE IDFilter = @Filter_ProductID AND FilterType = 2 ) )
      AND ( @Filter_IDProductType IS NULL
            OR ( ( IDProductType IN ( SELECT GuidValue FROM tblFilter WHERE IDFilter = @Filter_IDProductType AND FilterType = 1 ) AND NOT ( IDProductType IN ( SELECT GuidValue FROM tblFilter WHERE IDFilter = @Filter_IDProductType AND FilterType = 2 ) )
      AND ( @Filter_ProductName IS NULL OR ( ... etc ... ) ) 
END

Aber dieser Vorschlag ist wenig kompliziert, denke ich.

Gibt es einige "bewährte Verfahren", um diese Art von gespeicherten Prozeduren zu tun?

Antwort auf "Zugriff auf Daten mit gespeicherten Prozeduren " 6 von antworten

Zum Lesen von Daten benötigen Sie keine gespeicherte Prozedur für die Sicherheit oder zum Trennen von Logik, Sie können Ansichten verwenden.

Nur nur in der Ansicht auswählen.

Sie können die angezeigten Datensätze einschränken, Feldnamen ändern, viele Tabellen in einer logischen "Tabelle" verknüpfen usw.

Zuerst: Für Ihre Löschroutine sollte Ihre where-Klausel nur den Primärschlüssel enthalten.

Sekunde: Versuchen Sie für Ihre Updateroutine nicht zu optimieren, bevor Sie Arbeitscode haben. Versuchen Sie nicht, die Sendebedingungen zu optimieren, bis Sie Ihre Anwendung profilieren und sehen können, wo sich die Engpässe befinden. Ich kann Ihnen mit Sicherheit sagen, dass das Aktualisieren einer Spalte einer Zeile und das Aktualisieren aller Spalten einer Zeile in der Geschwindigkeit nahezu identisch sind. Was in einem DBMS Zeit braucht, ist (1) den Datenträgerblock zu finden, in dem Sie die Daten schreiben, und (2) andere Schreiber auszusperren, damit Ihr Schreiben konsistent ist. Schließlich ist das Schreiben des Codes, der zum Aktualisieren nur der Spalten erforderlich ist, die geändert werden müssen, in der Regel schwieriger und schwieriger zu verwalten. Wenn Sie wirklich wählerisch werden wollten, müssten Sie die Geschwindigkeit vergleichen, um herauszufinden, welche Spalten sich geändert haben, und mit der Aktualisierung jeder Spalte. Wenn Sie sie alle aktualisieren, müssen Sie keine sendemittellesen.

Drittens: Ich neige dazu, eine gespeicherte Prozedur für jeden Abrufpfad zu schreiben. In Ihrem Beispiel würde ich einen nach Primärschlüssel, einen nach jedem Fremdschlüssel und dann einen für jeden neuen Zugriffspfad hinzufügen, wie ich sie in der Anwendung benötigt habe. Seien Sie agil; Schreiben Sie keinen Code, den Sie nicht benötigen. Ich bin auch mit der Verwendung von Ansichten anstelle von gespeicherten Prozeduren einverstanden, sie können jedoch eine gespeicherte Prozedur verwenden, um mehrere Resultsets zurückzugeben (in einer Version von MSSQL) oder Zeilen in Spalten zu ändern, was nützlich sein kann.

Wenn Sie z. B. 7 Zeilen nach Primärschlüssel erhalten müssen, haben Sie einige Optionen. Sie können die gespeicherte Prozedur aufrufen, die sieben mal eine Zeile nach Primärschlüssel abruft. Dies kann schnell genug sein, wenn Sie die Verbindung zwischen allen Anrufen geöffnet halten. Wenn Sie wissen, dass Sie nie mehr als eine bestimmte Anzahl (z. B. 10) IDs gleichzeitig benötigen, können Sie eine gespeicherte Prozedur schreiben, die eine where-Klausel wie "und ID in (arg1, arg2, arg3...)" enthält, und sicherstellen, dass nicht verwendete Argumente auf NULL gesetzt sind. Wenn Sie sich entscheiden, dass Sie dynamisches SQL generieren müssen, würde ich mich nicht um eine gespeicherte Prozedur kümmern, da TSQL genauso einfach einen Fehler machen kann wie jede andere Sprache. Außerdem profitieren Sie nicht von der Verwendung der Datenbank für Die String-Manipulation – es ist fast immer Ihr Engpass, so dass es keinen Sinn macht, der DB mehr Arbeit als nötig zu geben.

In SQL 2005 unterstützt es nvarchar(max), das ein Limit von 2G hat, aber praktisch alle Zeichenfolgenoperationen bei normalem nvarchar akzeptiert. Sie können testen, ob dies in das passen kann, was Sie im ersten Ansatz benötigen.

Mein Vorschlag ist, dass Sie nicht versuchen, eine gespeicherte Prozedur zu erstellen, die alles tut, was Sie jetzt tun müssen oder jemals tun müssen. Wenn Sie eine Zeile basierend auf dem Primärschlüssel der Tabelle abrufen müssen, schreiben Sie dazu eine gespeicherte Prozedur. Wenn Sie nach allen Zeilen suchen müssen, die eine Reihe von Kriterien erfüllen, dann finden Sie heraus, welche Kriterien diese Kriterien sein könnten, und schreiben Sie dazu eine gespeicherte Prozedur.

Wenn Sie versuchen, Software zu schreiben, die jedes mögliche Problem und nicht einen bestimmten Satz von Problemen löst, werden Sie in der Regel bei der Bereitstellung von etwas Nützlichem fehlschlagen.

Ihre ausgewählte gespeicherte Prozedur kann wie folgt durchgeführt werden, um nur ein gespeichertes Proc, aber eine beliebige Anzahl von verschiedenen Elementen in der where-Klausel zu benötigen. Pass in einem oder einer Kombination der Parameter und Sie erhalten ALLE Elemente, die übereinstimmen - so brauchen Sie nur einen gespeicherten proc.

Create sp_ProductSelect
(
 @ProductID int = null,
 @IDProductType int = null,
 @ProductName varchar(50) = null,
 @ProductCode varchar(10) = null,
 ...
 @Volume int = null
)
AS
SELECT ProductID, IDProductType, ProductName, ProductCode, Weight, Volume FROM tblProduct'  
Where
  ((@ProductID is null) or (ProductID = @ProductID)) AND
  ((@ProductName is null) or (ProductName = @ProductName)) AND
  ...
  ((@Volume is null) or (Volume= @Volume))

Ich bin nicht damit einverstanden, dass das Erstellen von gespeicherten Prozeduren einfügen/Aktualisieren/Auswählen eine "bewährte Methode" ist. Wenn die gesamte Anwendung nicht in SPs geschrieben ist, verwenden Sie eine Datenbankebene in Ihrer Anwendung, um diese CRUD-Aktivitäten zu verarbeiten. Besser noch, verwenden Sie eine ORM-Technologie, um sie für Sie zu behandeln.