|
1引言
当应用MSSQLServer创建一个应用程序时，Transaction-SQL是一种主要的编程语言。若运用SQL语言来进行编程，有两种方法。其一是，在本地存储Transaction-SQL程序，并创建应用程序向SQLServer发送命令来对结果进行处理。其二是，可以把部分用Transaction-SQL编写的程序作为存储过程存储在SQLServer中，并创建应用程序来调用存储过程，返回结果，然后对数据结果进行处理。 本文介绍的是第二种方法，即使用存储过程访问数据库。 2存储过程简介 2.1存储过程概念、优点 存储过程（StoredProcedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中。存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者决定；能够返回状态值给调用者，指明调用是成功或是失败；包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。 存储过程具有以下优点： ①存储过程允许标准组件式编程 存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。 ②存储过程能够实现较快的执行速度 如果某一操作包含大量的SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。而批处理的SQL语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。 ③存储过程能够减少网络流量 对于同一个针对数据库对象的操作（如查询、修改），如果这一操作所涉及到的SQL语句被组织成一个存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL语句，从而大大增加了网络流量，降低网络负载。 ④存储过程可被作为一种安全机制来充分利用 系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。存储过程隐藏了数据库模式和编程细节，当使用客户端SQL代码查询一个数据库时，你需要了解所查询的表和列的详情，这会将数据库的模式呈现在客户连接和客户面前，存储过程则不允许客户端了解细节，客户或连接掌握的唯一信息就是它们要调用的存储过程名，从这个角度看存储过程提供了一个数据安全层。 2.2存储过程类型 SQLServer支持以下五种类型的存储过程： 系统存储过程：此类存储过程内置于SQLServer，不能对其任意修改，它们提供有关数据库模式、对象名、约束、数据类型以及许可等等方面的信息。 局部存储过程：此类存储过程由数据库管理员或SQLServer开发人员编写，是本文讨论的重点。 临时存储过程：此类存储过程由数据库管理员和SQLServer开发人员编写，但是只存在于连接期间。 远程存储过程：此类存储过程存在于远程服务器中，并且可以被一个初始服务器引用，远程存储过程用于分布式应用程序中。 扩展存储过程：此类存储过程在功能上类似于局部存储过程，但它们可以引用SQlServer外部的函数。 2.3SQLServer处理存储过程 存储过程的处理分为两个阶段。在第一阶段，存储过程创建后首先由SQLServer数据库引擎解析，这以后会发生两件事。其一，SQLServer将该过程的定义、名称和代码保存到数据库中，同时，SQLServer还会把代码交付给查询优化器，以确定代码的最佳执行规划；其次，代码被编译并被置于过程缓存中，只有在客户连接显示调用重编译时或执行规划不再存在于高速缓存中时，高速缓存中的执行规划才被刷新。如图1所示。 图1SQLServer处理存储过程阶段一 在第二阶段，当代码中引用了此存储过程名时，查询规划被检索，过程代码此时在调用它的每个连接的上下文中执行，任何结果集或返回值都会返回给每一个连接。如图2所示。 图2SQLServer处理存储过程阶段二 2.4存储过程中的错误处理 在存储过程执行过程中，如果遇到错误，SQLServer会返回错误代码和错误信息。SQLServer有近3800个预定义错误代码，存储在master数据库的sysmessages表中。每一个错误代码都有相应的精确的级别。错误定义的级别从0到25。20以上的错误代表重大错误，意味着该错误会导致存储进程立刻终止，并且所有的连接都要重新初始化。非关键性错误只是禁止当前运行的有错误的代码，并继续执行剩余的代码。所有的错误代码都有预定义的错误信息。可以根据错误代码获得错误信息，然后返回给存储过程的调用程序，利于程序编写者处理错误。 3在VS.net中调用存储过程 下面以Northwind数据库为例介绍在VisualStudio.net集成开发环境中如何调用存储过程(C#)。 3.1创建存储过程 根据客户端的需求创建实现一定功能的存储过程，可以使用SQlServer企业管理器中的创建存储过程向导或者直接编写SQL语句来创建存储过程。 List.1存储过程清单 [1]CREATEPROCEDUREcount_CustomerID [2]@CustomerIDnchar(5), [3]@numintOUTPUT [4]AS [5]SELECT@num=(SELECTCOUNT(*)FROMordersWHERECustomerID=@CustomerID) [6]Return [7]GO 以上SQL语句创建一个名为count_CustomerID的存储过程，这个存储过程有一个输入参数（参数名称：CustomerID；类型：nchar(5)；意义：标识商品的ID值）和一个输出参数（参数名称：num；类型：int；意义：某一商品的订单数量）。该存储过程实现的功能：根据客户端调用存储过程时传入的商品ID值，然后计算此商品的订单数量，将结果作为输出参数返回给客户端的调用程序。 注：创建存储过程的语法详见SQLServer联机丛书。 3.2设计应用程序 数据库和存储过程设计好之后，就可以开始设计应用程序了。首先要做的是连接数据库，可以通过SqlConnection对象(命名空间：System.Data.SqlClient)、OleDbConnection对象(命名空间：System.Data.OleDb)、OdbcConnection对象(命名空间：System.Data.Odbc)等方法连接数据库。本文介绍使用SqlConnection对象连接数据库。 List.2调用存储过程 //添加命名空间 UsingSystem.Data.SqlClient; //使用SqlConnection对象连接数据库 stringConnStr; ConnStr="datasource=local;initialcatalog=Northwind;userid=user;password=pw"; SqlConnectionconn=newSqlConnection(ConnStr); //使用SqlCommand调用存储过程 SqlCommandcmd=newSqlCommand(CustOrdersOrders,conn);//创建SqlCommand对象 conn.CommandType=CommandType.StoredProcedure; SqlParameterCustID=newSqlParameter("@CustomerID",SqlDbType.Char,5); CustID.value=“ALFKI”;//对输入参数赋值 cmd.Parameters.Add(CustID); SqlParameterCount=newSqlParameter("@num",SqlDbType.int,4); cmd.Parameters.Add(Count); Count.Direction=ParameterDirection.Output;//指定参数为输出参数 //开始执行存储过程 Conn.Open();//打开数据库连接 cmd.ExecuteNonQuery();//执行查询 conn.Close(); //变量sum的值就是存储过程返回的指定商品的订单数量 intsum=Convert.ToInt32(cmd.Parameters["@total"].Value.ToString()); 4存储过程的其他功能 4.1扩展存储过程 在编写数据访问的程序时，有时需要实现一些SQLServer本身不能实现的功能，如访问本地资源等，这时就可以使用扩展存储过程。扩展存储过程是以动态链接库的形式实现的，在存储过程中使用存储在master数据库中的系统存储过程sp_addextendedproc将已经编写好的动态链接库(.dll文件)中的函数声明为扩展存储过程，声明之后就可以调用此函数，从而实现所要实现的一些功能。 具体语法略。 4.2调用Com组件 Com，即组件对象模型，是一种以组件为发布单元的对象模型，这种模型是各种软件组件可以使用统一的方式进行交互，不依赖于任何特定的语言和操作系统。Com的这种优点使得越来越多的编程开发者采用这种编程方式。SQLServer中的存储过程也支持Com这种编程方式。 根据需求编写Com组件，发布组件之后，使用存储在master数据库中的系统存储过程sp_OACreate将Com对象实例化，之后就可以使用Com对象中的函数。 具体语法略。 5优化存储过程 如果一个应用程序所用的存储过程较多，书写又没有一定的规范，将会影响以后的系统维护和较大存储过程逻辑的难以理解，另外如果数据库的数据量大或对存储过程的性能要求很高，就会遇到优化的问题，否则速度有可能很慢。一个经过优化过的存储过程要比一个性能差的存储过程的效率甚至高几百倍。 在编写存储过程时应该注意以下几点 ①尽量避免大事务操作，提高系统并发能力； ②尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接,访问这个临时表； ③尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作； ④注意where语句的写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小； ⑤不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引； ⑥尽量使用exists代替selectcount(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。 ⑦尽量使用“>=”，不要使用“>”； ⑧注意一些or子句和union子句之间的替换； ⑨注意表之间连接的数据类型，避免不同类型数据之间的连接； 此外，还要正确、高效地使用索引和操作临时表；当然合理的算法使用也是优化存储过程，提高程序执行效率的有效手段。 6存储过程与数据安全 从用户对敏感数据的访问角度，通过向用户授予对存储过程（而不是基础表）的访问权限，就可以控制对特定数据的访问，实现对敏感数据的保护。 存储过程还可以帮助解决代码安全问题。可以防止某些类型的SQL插入攻击-主要是一些使用运算符（如AND或OR）将命令附加到有效输入参数值的攻击。在应用程序受到攻击时，存储过程还可以隐藏业务规则的实现。 另外，使用存储过程使您可以使用ADO.NET中提供的SqlParameter类指定存储过程参数的数据类型。这为验证用户提供的值类型（作为深层次防御性策略的一部分）提供了一个简单方法。 使用存储过程增强数据安全性时值得注意的是，糟糕的安全性或编码做法仍然会受到攻击。对SQLServer角色创建和分配如果不加注意将导致访问到不应看到的数据。同时，存储过程不能防止所有的SQL插入代码攻击（例如，将数据操作语言(DML)附加到输入参数）。 另外，无论SQL语句位于代码还是位于存储过程中，使用参数进行数据类型验证都不是万无一失的。所有用户提供的数据（尤其是文本数据）在传递到数据库之前都应受到附加的验证。 7结束语 本文简单介绍了存储过程的概念、特点和应用方法，使大家对存储过程有了初步了解。存储过程的广泛应用和它访问数据的机制使得存储过程成为当前提倡的三层结构系统开发模型中数据访问层所不可或缺的元素。希望对使用数据库开发应用程序的程序开发者有一定的借鉴和指导作用。 |
|
|
来自： WindySky > 《sql server》
