代码之家  ›  专栏  ›  技术社区  ›  TcKs

使用存储过程访问数据

  •  4
  • TcKs  · 技术社区  · 16 年前

    “最佳实践”之一是通过存储过程访问数据。我理解为什么这种情况很好。 我的动机是拆分数据库和应用程序逻辑(如果存储过程的行为相同,可以更改表)、SQL注入防御(用户不能从某些表中执行“select*”,只能调用存储过程)和安全性(存储过程中可以是安全的“任何东西”,用户不能选择/插入t/更新/删除数据,但不适用于它们)。

    我不知道如何使用动态过滤器访问数据。

    我正在使用MSSQL2005。

    如果我有桌子:

    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)
    )
    

    然后我应该创建4个存储过程(创建/读取/更新/删除)。

    “create”的存储过程很简单。

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

    “delete”的存储过程也很简单。

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

    “update”的存储过程与“delete”的存储过程类似,但我不确定这是正确的方法,也不知道如何执行。我认为更新所有列是不有效的。

    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
    

    “read”的最后一个存储过程对我来说有点神秘。复杂条件下如何传递过滤值?我有一些建议:

    使用XML参数传递Where条件:

    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
    

    但是,我认为一个查询中“4000”字符的限制是丑陋的。

    下一个建议是对每一列使用筛选表。将筛选值插入筛选表,然后使用筛选ID调用存储过程:

    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
    

    但我认为这个建议有点复杂。

    是否有一些“最佳实践”来执行这种类型的存储过程?

    6 回复  |  直到 16 年前
        1
  •  5
  •   community wiki 2 revs Joseph Bui    16 年前

    首先:对于删除例程,WHERE子句应该只包含主键。

    第二:对于您的更新例程,在您有工作代码之前不要尝试优化。事实上,在分析应用程序并查看瓶颈所在之前,不要尝试优化。我可以肯定地告诉您,更新一行中的一列和更新一行中的所有列的速度几乎相同。在DBMS中需要花费的时间是:(1)找到将在其中写入数据的磁盘块;(2)锁定其他写入程序,以便您的写入保持一致。最后,编写只更新需要更改的列所需的代码通常会比较困难,也很难维护。如果你真的想变得挑剔,你就必须比较找出哪些列发生了变化的速度,而不仅仅是更新每一列。如果你把它们全部更新了,你就不必去读它们了。

    第三:我倾向于为每个检索路径编写一个存储过程。在您的示例中,我将按主键制作一个,按每个外键制作一个,然后根据应用程序中的需要为每个新的访问路径添加一个。敏捷;不要写你不需要的代码。我也同意使用视图而不是存储过程,但是,您可以使用存储过程返回多个结果集(在某些版本的MSSQL中),或者将行更改为列,这很有用。

    例如,如果您需要按主键获取7行,那么您有一些选项。可以调用按主键获取一行的存储过程七次。如果保持所有呼叫之间的连接打开,这可能足够快。如果您知道一次最多需要一定数量的ID(比如10个),那么可以编写一个存储过程,其中包含一个where子句,如“and id in(arg1,arg2,arg3…)”,并确保将未使用的参数设置为空。如果您决定需要生成动态SQL,我就不必为存储过程操心了,因为TSQL和其他语言一样容易出错。此外,使用数据库进行字符串操作没有任何好处——它几乎总是您的瓶颈,因此没有必要给DB更多的工作。

        2
  •  6
  •   Peter    16 年前

    对于读取数据,不需要存储过程来实现安全性,也不需要分离逻辑,可以使用视图。

    只允许在视图上选择。

    您可以限制显示的记录、更改字段名、将多个表联接到一个逻辑“表”中等。

        3
  •  3
  •   Terry G Lorber    16 年前

    我不同意创建插入/更新/选择存储过程是“最佳实践”。除非您的整个应用程序是用SPS编写的,否则请在应用程序中使用数据库层来处理这些CRUD活动。更好的是,使用ORM技术为您处理它们。

        4
  •  2
  •   Tom H zenazn    16 年前

    我的建议是,不要尝试创建一个存储过程,该存储过程可以执行现在或将来可能需要执行的所有操作。如果需要基于表的主键检索行,则编写一个存储过程来执行此操作。如果需要搜索满足一组条件的所有行,请找出这些条件可能是什么,然后编写一个存储过程来执行此操作。

    如果您试图编写能够解决所有可能问题的软件,而不是一组特定的问题,那么通常无法提供任何有用的东西。

        5
  •  2
  •   Mauro    16 年前

    您的select存储过程可以按如下方式执行,只需要一个存储过程,而在where子句中需要任意数量的不同项。传递任何一个或多个参数组合,您将得到所有匹配的项,因此您只需要一个存储过程。

    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))
    
        6
  •  0
  •   TimeSpace Traveller    16 年前

    在SQL2005中,它支持nvarchar(max),它的限制是2g,但实际上可以接受正常nvarchar上的所有字符串操作。您可能需要测试这是否符合您在第一种方法中所需要的。