SQL_第11章_存储过程

第11章存储过程的创建和使用11.1存储过程的定义11.2存储过程的优点11.3存储过程的创建11.4重新编译存储过程11.5自动执行存储过程11.6查看、修改和删除存储过程11.7扩展存储过程返回目录2存储过程（procedure）类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数，也可以返回结果intsum(inta,intb){ints;s=a+b;returns;}存储过程相当于C语言中的函数什么是存储过程3存储过程------------------------单个SELECT语句SELECT语句块SELECT语句与逻辑控制语句可以包含什么是存储过程存储过程可以包含数据操纵语句、变量、逻辑控制语句等411.1存储过程的定义存储过程是一系列预先编辑好的、能实现特定数据操作功能的SQL代码集，它与特定的数据库相关联，存储在SQLServer服务器上。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。本章首页5执行下列语句，结果是什么？：CREATEPROCEDUREproc1ASselect*fromstudentselect*fromteachergo通过执行该存储过程，可以得到其包含语句的执行结果：EXECUTE/EXECproc1利用这些语句创建存储过程：创建存储过程，名为proc1AS关键字后定义本存储过程所执行的操作存储过程的创建必须在一个独立的批处理中，所以一般在语句结尾处跟GO6存储过程的分类存储过程分为三类：系统存储过程用户自定义存储过程扩展存储过程7系统存储过程系统存储过程是指安装SQLServer时由系统创建的存储过程，类似C语言中的系统函数。其大多是一些常用的系统操作。系统存储过程存储在master数据库中，其前缀为sp_。如：execsp_renamedb‘JWGL’,’学生管理数据库’执行系统存储过程sp_renamedb，将已有数据库JWGL重命名为学生管理数据库8常用的系统存储过程系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程。sp_password添加或修改登录帐户的密码。sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。9EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstudentEXECsp_helpstudentEXECsp_helpconstraintstudentEXECsp_helpindexbookEXECsp_helptext'student_view1'EXECsp_stored_procedures常用的系统存储过程4-2修改数据库的名称(单用户访问)列出当前系统中的数据库当前数据库中查询的对象的列表返回某个表列的信息查看表student的信息查看表student的约束查看表book的索引查看视图的语句文本查看当前数据库中的存储过程演示：常用的存储过程1011.7扩展存储过程11.7.1扩展存储过程概述扩展存储过程使你得以使用编程语言创建自己的外部例程。扩展存储过程与通常的存储过程其实有很大差别。存储过程是一系列预编译的Transact-SQL语句，而扩展存储过程是对动态链接库函数的调用。SQLServer提供了一些扩展存储过程，扩展存储过程也可以由开发人员来编写。自行编写的扩展存储过程在使用之前必须添加（注册）到SQLServer中。可以使用C++之类的编程语言编写自己的DLL，然后以扩展存储过程的模式将它添加到SQLServer中。11扩展存储过程扩展存储过程的作用不止是操作数据库，其大多是SQLServer以外的操作的调用。可以使用C++之类的编程语言编写自己的DLL，然后以扩展存储过程的模式将它添加到SQLServer中。扩展存储过程以xp_开头。系统为用户准备了常用操作的扩展存储过程，用户也可以自定义扩展存储过程。如：execxp_cmdshell'mkdird:\sqldata‘,no_output执行扩展存储过程xp_cmdshell，执行dos语句创建路径文件夹。12usemastergoifexists(select*fromsysdatabaseswherename='JWGL')dropdatabaseJWGLgoexecxp_cmdshell‘mkdird:\sqldata’,no_outputcreatedatabaseJWGLonprimary(name='JWGL_data',size=5,maxsize=100,filegrowth=15%,filename='d:\sqlData\JWGL_data.mdf')logon(name='JWGL_log',size=5,maxsize=100,filegrowth=5,filename='d:\sqlData\JWGL_log.ldf')goexecxp_cmdshell'dird:\sqldata'13用户自定义存储过程由用户为完成某一特定功能而编写的存储过程。包含了完成该功能所执行的T-SQL语句及所使用的变量等。用户自定义存储过程为本章学习的重点。1411.2存储过程的优点存储过程是一种把重复的任务操作封装起来的一种方法，支持用户提供参数，可以返回、修改值，允许多个用户使用相同的代码，完成相同的数据操作。它提供了一种集中且一致的实现数据完整性逻辑的方法。存储过程用于实现频繁使用的查询、业务规则、被其它过程使用的公共例行程序。存储过程具有以下优点：•存储过程提供了处理复杂任务的能力存储过程提供了许多标准SQL语言所没有的高级特性，它通过传递参数和执行逻辑表达式，能够使用十分复杂的SQL语句处理复杂任务。•增强代码的重用性和共享性（模块化程序设计）每一个存储过程都是为了实现一个特定的功能而编写的模块，模块可以在系统中重复地调用，也可以被多个有访问权限的用户访问。所以，存储过程可以增强代码的重用性和共享性，加快应用系统的开发速度，减少工作量，提高开发的质量和效率。15•减少网络数据流量存储过程是与数据库一起存放在服务器中并在服务器上运行的。应用系统调用存储过程时只有触发执行存储过程的命令和执行结束返回的结果在网络中传输。用户端不需要将数据库中的数据通过网络传输到本地进行计算，再将计算结果通过网络传送到服务器。所以，使用存储过程可以减少网络中数据流量。•加快系统运行速度第一次执行后的存储过程会在缓冲区中创建查询树，第二次执行时就不用进行预编译，从而加快了系统运行速度。另外，由于存储过程是在服务器上运行，分担了用户端的数据处理工作，也加快了应用系统的处理速度。•加强系统安全性SQLServer可以不授予用户某些表、视图的访问权限，但授予用户执行存储过程的权限，通过存储过程来对这些表或视图进行访问操作。这样，既可以保证用户能够通过存储过程操作数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。本章首页1611.3存储过程的创建11.3.1使用T-SQL语句创建存储过程11.3.2创建带输入参数的存储过程11.3.3创建带输出参数的存储过程本章首页1711.3.1使用T-SQL语句创建存储过程1.创建不带参数存储过程语法创建一个不带参数存储过程的语法如下：CREATEPROC[EDURE]存储过程名ASSQL语句和C语言的函数一样，参数可选参数分为输入参数、输出参数输入参数允许有默认值182.创建步骤一般来说，创建一个存储过程应按照以下步骤进行：•①编写SQL语句。•②测试SQL语句是否正确，并能实现功能要求。•③若得到的结果数据符合预期要求，则按照存储过程的语法，创建该建存储过程。•④执行该存储过程，验证其正确性。193.存储过程创建示例【例11-1】创建一存储过程，要求该存储过程返回学生姓名、所学课程名称和任课教师。CREATEPROCEDUREproc_StuCouTea_nameASSELECTstudent_name,course_name,teacher_nameFROMstudenta,student_courseb,coursec,teacher_course_classd,teachereWHEREa.student_id=b.student_idANDb.course_id=c.course_idANDc.course_id=d.course_idANDd.teacher_id=e.teacher_id执行以上脚本，便可创建存储过程proc_StuCouTea_name。如要执行该存储过程，可在查询分析器中执行如下语句：EXECproc_StuCouTea_name20[例]创建存储过程proc_area，根据三角形三边，计算三角形面积。createprocedureproc_areaasdeclare@afloat,@bfloat,@cfloatdeclare@pfloat,@sfloatselect@a=2,@b=4,@c=5print'输入的三边为：'+convert(varchar(5),@a)+'、‘+convert(varchar(5),@b)+'、'+convert(varchar(5),@c)if@a+@b@cand@a+@c@band@b+@c@abeginset@p=(@a+@b+@c)/2set@s=sqrt(@p*(@p-@a)*(@p-@b)*(@p-@c))print'该三角形的面积为：'+convert(varchar(8),@s)endelseprint'输入三边不能构成一个三角形！'214.创建存储过程的注意事项在创建存储过程的时候，需要注意如下几点：•①每个存储过程应该完成一项单独的工作。•②为防止别的用户看到自己所编写的存储过程的脚本，创建存储过程时可以使用参数WITHENCRYPTION。•③一般存储过程都是在服务器上创建和测试，在客户机上使用时，还应该进行测试。本节首页2211.3.2创建带输入参数的存储过程回顾我们刚才创建的求三角形面积的存储过程proc_area:执行该存储过程：可以看出，存储过程proc_area的功能就是计算边长为2、4、5的三角形的面积。由于用户无法参与过程内运行的语句，该存储过程只能执行固定的操作，其实用性大大降低了。createprocedureproc_areaasdeclare@afloat,@bfloat,@cfloatdeclare@pfloat,@sfloatselect@a=2,@b=4,@c=5print'输入的三边为：'+convert(varchar(5),@a)+'、‘+convert(varchar(5),@b)+'、'+convert(varchar(5),@c)if@a+@b@cand@a+@c@band@b+@c@abeginset@p=(@a+@b+@c)/2set@s=sqrt(@p*(@p-@a)*(@p-@b)*(@p-@c))print'该三角形的面积为：'+convert(varchar(8),@s)endelseprint'输入三边不能构成一个三角形！'23带输入参数的存储过程允许用户通过输入参数，参与到过程中执行的命令中，我们可以把语句运行的关键数值设置成输入参数，由用户在调用存储过程的时候定义该值，从而达到控制存储过程功能的目的。