教学思路SQL之入门习题《学生成绩》 七.存储过程基础知识

 如果我们学习了ADO.Net或者实际做过一个与数据库进行数据交互的应用程序，那就非常好理解今天我们所教授的知识点---SQL中的存储过程，存储过程是实际项目开发过程中最常用的处理数据库数据的方式,这是最基本的数据库操作，必须熟练掌握，既然它这么重要，下面我们就开始学习存储过程，有关它的好处和实质，我们放到学习完语法和实例后让大家一起发现式的总结，这样更能体会出存储过程的优势和方便。

       首先我们来学习 定义一个最简单的存储过程语法，"[ ]"内的语句可以省略：

       从定义存储过程的语法中我们可以发现这里也使用了"create 数据库对象类型  对象名称" 这样的语法，存储过程在数据库对象类型用Proc或Procedure关键字来表示，as关键字后是实际的存储过程要执行的sql语句，下面我们来看一个具体的简单实例，本讲所用到的数据库仍然为前六讲使用的《成绩管理0902》的数据库：

         我们来分析一下上面的存储过程，第一个行，use 成绩管理0902  为指定在数据库“成绩管理0902”中创建存储过程；go关键字加入其中是因为大多数的非表的create语句不能与其他任何代码共享批处理，而且存储过程应该个批处理的第一条执行语句；第三行create Proc Proc_Selstudent 意思为创建一个名字叫做“Proc_Selstudent”的存储过程，从存储过程的命名方式可以很清晰的看出前缀Proc说明是存储过程，Selstudent说明这个存储过程存储了查询student表的结果集的语句，as后面写明了这个sql语句--查询出表student中所有的记录，就目前来看，我们这个存储过程只有一条查询语句，实际上存储过程是可以包含多条T_sql语句来完成你想要完成的一条业务逻辑。

         写完此存储过程后，我们选中语句按F5编译存储过程，提示“命令已成功完成。”后，你的存储过程将保存在“成绩管理0902”库--可编程性--存储过程下，现在如果我们想完成查询所有学生信息的功能，除了执行“select * from student”此语句外，还可以用我们刚刚定义好的“Proc_Selstudent ”存储过程来完成这样的效果， 执行存储过程的方法为 Exec或Execute   Proc_Selstudent ，效果与传统的select * from student一致，从这点我们就能够初步总结出存储过程的定义了。

           存储过程是为了完成特定的业务功能，所封装了可重用的T-SQL语句的模块的集合，经编译后存储在数据库中。下面我们考虑一下问什么说可重用，我们相信一下如果我想完成一个业务需要由多条sql语句组成，同时有可能在这些语句中加入参数、流控制或返回值才能完成结果，这些复杂的语句如果每次我用的时候用传统的方法我们需要每次再书写一次，而如果我们把这些复杂的语句封装在一个存储过程里，每次想执行这个效果时，我们只需要用exec加存储过程的名字， 执行存储过程就可以了，方便了我们调用，而且一个存储过程编译后，下次再需要执行时，无需再次编译，可以在内存的过程缓存中找到第一次编译此存储过程时的sql Server2005开发的一个执行计划方案，这个方案反映用户想要的结果集中使用哪个索引返回数据最快，加快了执行语句的性能和速度，现在同学们可能会想到如果我修改了这个存储过程的内容了，这个已经存储好的执行计划方案可能会影响我们想要得到的效果，这时我们只需要重写编译这个存储过程就可以了，此处涉及到了优化存储过程的知识，下一节我们将来学习。

         使用 Drop proc Proc_Selstudent  删除存储过程，使用 Alter proc关键字 修改存储过程，下面的例子就是将存储过程修改为查询年龄大于0的学生信息。

          dbo为存储过程所属的框架的名字，在书写存储过程时，最好显式的指明其框架的名字， 有关框架的知识，将会在后面的教学中讲解到，现在我们就把它理解为类似于c#中的命名空间。需要记住的是修改后的存储过程将完全替换原有的存储过程，但是保留了存储过程的任何权限和依赖关系。这里所说的权限指的是，存储过程对操作数据库对象的所特有的安全机制优势，SQL Server允许授予某个用户对表或视图的某种操作进行限制，比如我们可以禁止用户wf对student表的select操作进行限制，但是可以允许用户wf利用存储过程对这个表进行有条件的访问，这样我们就可以使用存储过程对其所引用的表或视图进行封装保护，只让用户得到授权允许的操作。下面的例子就是限制了用户wf对student表的查询权限，但是提供给他一个利用存储过程对表student的操作权限。

           依赖关系指的是，存储过程中可以包含（调用）其他存储过程，这时同学就会想到，存储过程里可以包含又不能包含什么呢？首先存储过程可以包含复杂的sql语句，之所以说是复杂，原因就是可以引用多个数据库对象，如多个表、视图、存储过程，如果这些引用对象之间存在判读条件时，还有使用流控制、循环结构、错误捕获以及函数、参数、返回值等成员。不能包括在create proc定义中的语句的我们用列表的形式展示给大家：

1.create default      --创建默认值列

create default address
as '地址不详'
sp_bindefault 'address' ,'student.address'     --让student表中address字段的值都绑定为‘地址不详’

2.create 或 alter function --创建或修改用户自定义函数

3.create 或 alter proc      --创建或修改存储过程

4.set parseonly {on|off} --当 SET PARSEONLY 为 ON 时，SQL Server 只分析语句。当 SET PARSEONLY 为 OFF 时，SQL Server 编译并执行语句。 

5.set showplan_text       --当 SET SHOWPLAN_TEXT 为 ON 时，SQL Server 将返回每个 Transact-SQL 语句的执行信息，但不执行语句。将该选项设置为 ON 以后，将返回有关所有后续 SQL Server 语句的执行计划信息，直到将该选项设置为 OFF 为止。

6.set showplan_all          --SET SHOWPLAN_ALL 的设置是在执行或运行时设置，而不是在分析时设置。

7.set showplan_xml        --如果 SET SHOWPLAN_XML 为 ON，则 SQL Server 将在不执行语句的情况下返回每个语句的执行计划信息。

8.use 关键字

9.create schema             --在当前数据库中创建架构。

10.create 或alter　trigger   --创建或修改触发器

11.create 或alter　view      --创建或修改视图

           看了这么多的不允许，对于初学者，我们只需要知道存储过程应该引用已经定义好的对象就可以了，下面我们将存储过程加难，加入参数。 参数在sql中用的非常多，它就像程序中的变量一样，可以存储一个结果，待用户使用，同时参数又分为输入和输出两种类型。

          请大家分析这样的例子，“根据用户输入的学生编号，输出对应的学生姓名”，在这样需求中有两个不确定的因素来影响是查询的结果，一个是用户输入的学生编号，一个是输出用户的学生姓名，所以输入参数我们应该定义学生的编号，而查询的结果我们应该定义为输出参数，将上面的存储过程修改为如上效果的代码为：

     从上面的例子中，我们来总结一下存储过程中定义参数的信息有：

          第一行使用declare定义一个变量，第二行执行存储过程，执行时要注意的是，参数的顺序（如果明确的用@parameter_name=value语句提供参数值，可以不考虑参数顺序）、“，”分隔、output关键字，经过第二行的执行，现在查询后的将被存储在定义的变量里，然后我们查询这个变量的值，得到结果。

        我们再把存储过程改变一下：

     第8行将直接查询出编号为3的学生姓名，原因是第3行，我们给参数加入了=3的默认值，当我们第8行改为：exec Proc_Selstudent @returnname output,5 将查出编号为5的学生姓名，这时我们就应该能发现，如果我们的入参有默认值，执行存储过程省略了输入入参的值时，将得到默认值的结果，需要注意的是，如果想省略入参的值，这个入参的定义位置应该在最后。

     现在我们来总结一下有关执行有参数存储过程的注意点，当调用存储过程是，我们必须给定义中未提供默认值的输入参数指定输入值，如果我们采用直接写值方式指定参数的值必须按照参数声明的顺序输入；如果显示的写明参数名=值这样的写法时，可以不考虑顺序问题；如果我们在执行存储过程时，想要多个参数的其中一个参数使用其默认值时，我们可以用default关键字来解决这样的问题如“exec proc proctest  1,default,2”；当最后的输入参数有默认值时，调用时恰好想使用默认值，我们就可以省略给此输入参数赋值；我们输出参数必须写明output关键字，否则sql server不会产生运行时错误，但可能会碰到最可怕的情况--术语“非预期的结果”。

       接下来请同学们来定义一个存储过程，要求是：创建一个根据学生的姓名修改学生年龄的存储过程，修改后显示该条学生的信息.

        通过这节课的学生有关存储过程的基本操作都教授给了大家，下一节我们将要学习有关存储过程中的难点知识，包括给存储加密、错误信息处理、优化存储过程、以及存储过程的分类，最后我们总结出存储过程的优点。 

本文转自叶子文文博客51CTO博客，原文链接http://blog.51cto.com/leafwf/185771如需转载请自行联系原作者

叶子文文