存储过程入门

  前段时间忙着写代码，总算闲了一点了。以前做的东西，涉及到数据库的很少，只有在大四兼职的时候做过一个项目，涉及到数据库，用的是MySQL，基本只是用来存储数据，然后直接用代码写SQL语句操作了，这次基本上就是在和数据库打交道，学了很多数据库的东西。其中重要的是储存过程。

  以前做数据库的项目,需求经常变化,导致代码也经常要改,刚开始都是直接把SQL语句写在代码里,散落在代码各处,改起来很是痛苦.后来想了办法,把SQL语句放在XML文件里,改起来稍微容易点了,当还是痛苦.而且SQL语句直接写在客户端里,很不安全.现在看来,如果用存储过程会好很多.

存储过程的好处:

　　1.速度快。在存储过程创建的时候，数据库已经对其进行了解析优化。

    2.可执行复杂操作.可在存储过程中定义变量,执行IF条件语句等各种和程序差不多的功能.可执行多条SQL语句

    3.可移植性,可维护性更好.由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响.

    4.便于分工.如何分工好的话，可以将写代码的程序员和数据库操作员分工,只要程序员和数据库操作员定义好输入输出后,程序员可以完全不懂SQL语句也能写代码。应用程序和数据库的编码工作可以分别独立进行,并行工作。

    5.安全性。它们可以防止某些类型的 SQL 插入攻击，黑客的初级攻击手段。当然这只是其中一方面了，存储过程在服务器端,攻击者无法知道存储过程的具体逻辑,否则在客户端就可以通过反汇编,网页源代码了解其逻辑。

  下面以SQL Server 2005来看下存储过程的基本操作吧。
  首先,假设数据库中有张表叫TestTable,其结构如图：

  1. 创建存储过程.在"Programmability" 下 有个节点叫 "Stroed Procedures"(存储过程),点右键,弹出菜单,选"New Stored Procedure"(新建存储过程). 如图:

  2. IDE会建立一个SQL文件，其内容如下：

1. CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
2.     -- Add the parameters for the stored procedure here
3.     <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
4.     <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
5. AS
6. BEGIN
7.     -- SET NOCOUNT ON added to prevent extra result sets from
8.     -- interfering with SELECT statements.
9.     SET NOCOUNT ON;
10.     -- Insert statements for procedure here
11.     SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
12. END
13. GO

  这很好理解，我们先写一个简单的存储过程吧。将其内容改为：

1. CREATE PROCEDURE [sp_SelectAllTest]
2. AS
3. BEGIN
4.     -- SET NOCOUNT ON added to prevent extra result sets from
5.     -- interfering with SELECT statements.
6.     SET NOCOUNT ON;
7.     -- Insert statements for procedure here
8.     SELECT * from TestTable;
9. END
10. GO

  

  这SQL语句是最简单的了。

  然后点 IDE上 的"Execute"(执行)按钮，也就是执行改SQL脚本,就会创建了一个存储过程,在"Programmability" 下节点 "Stroed Procedures"(存储过程),点右键,弹出菜单，选"Refresh",如图：

  新加入的存储过程就会在列表中显示。

  3.在IDE中执行存储过程。在”Stored Procedure“节点下选中刚加入的存储过程，点右键，选”Execute Stored Procedure"(执行存储过程），会弹出对话框，直接选“OK”（确定），就会吧该存储过程的结果显示出来。

基本的就是这样了，来看学习一个稍微复杂一点的存储过程吧。里面有输入参数，默认参数，有IF语句，如下：

1. Create PROCEDURE [sp_SelectTestByID]
2.     (
3.         @ID int = null
4.     )
5. AS
6. BEGIN
7.     -- SET NOCOUNT ON added to prevent extra result sets from
8.     -- interfering with SELECT statements.
9.     SET NOCOUNT ON;
10.     -- Insert statements for procedure here
11.     if @id is null
12.         select * from TestTable
13.     else
14.         SELECT * from TestTable where id=@id;
15. END
16. GO

  这也很好懂，自己加入，执行一下看吧。

  那么我们在代码里如何调用存储过程呢？看C#代码：

1.             SqlConnection connection = new SqlConnection(this.connectionString);
2.             connection.Open();
3.             SqlCommand command = new SqlCommand("sp_SelectTestByID", connection); //存储过程名
4.             command.CommandType = CommandType.StoredProcedure;
5.             SqlParameter[] parameters= new SqlParameter[1];
6.             parameters[0] = new SqlParameter();
7.             parameters[0].ParameterName = "@ID";      //输入参数名
8.             parameters[0].SqlDbType = SqlDbType.Int;  //输入参数类型
9.             parameters[0].Value = 1                   //输入参数值
10.             command.Parameters.AddRange(parameters);         
11.             SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);