《SQLServer2014数据库应用教程》课件-第9章存储过程

第9章存储过程SQLServer2014

数据库应用教程本章要点管理存储过程创建带参数的存储过程创建存储过程3211

创建存储过程

存储过程，英文简称为StoredProcedure，是在数据库系统中的一组SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个处理单元，其中可以包含程序流、逻辑以及对数据库的查询。本任务要求创建一个简单的存储过程pr_News，旨在介绍存储过程从创建到执行的基本过程，重点是掌握存储过程创建的语法结构。步骤如下：（1）打开创建存储过程的窗体。（2）编写创建存储过程的代码，重点是了解创建存储过程的常规语句。（3）执行存储过程，验证代码的正确性。1.1主要步骤实现过程请参考视频1.2用命令创建存储过程创建存储过程的命令很简单，下面就是标准的创建命令：CREATE{PROC|PROCEDURE}[SCHEMA_name.]procedure_name[{@parameter}data_type[=default][OUTPUT]][,…n][WITH<procedure_option>[,…n]]AS[BEGIN]{<sql_statement>}[END]有关说明如下：（1）CREATEPROCEDURE：创建存储过程的命令。（2）procedure_name：存储过程名，是为存储过程所起的名字，一般以procedure_或pr_开头，不能超过128个字符。（3）每个存储过程中最多设定1024个参数。（4）@parameterdata_type[=default][OUTPUT]:参数设置。参数需要在名称前使用“@”符号声明，每一个存储过程的参数仅为该程序内部使用。参数的类型可以是除IMAGE外的所有数据类型。[=default]是为这个参数设定的默认值。[OUTPUT]声明该参数是输出参数。1.3存储过程的分类系统存储过程是SQLServer2014系统自带的、已经编译好的存储过程，使用时直接调用即可。系统存储过程以sp_开头。系统存储过程本地存储过程是由用户根据需要自行设计并创建的存储过程，通常完成某一特定功能。一般我们所说的存储过程指的是本地存储过程。本地存储过程2扩展存储过程是指用户可以使用外部程序语言编写的存储过程，扩展存储过程的名称以xp_开头。扩展存储过程311.4存储过程的优点存储过程建立之后，其中的语句已经编译并且储存到数据库中，调用时不必重新编译，相对于每次单独执行SQL语句的先编译再执行，效率大大提高。执行效率更高。需要执行的语句改为存储过程后，就成为数据表访问的一个安全机制。当一个数据表没有设置权限，而对该数据表的操作又需要进行权限控制时，可以使用存储过程做为一个存取通道，不同的用户可通过不同的存储过程访问数据表。安全性更好2可以根据传入参数的不同重复使用同一个存储过程，使存储过程更加灵活地实现相应功能存储过程可以设置参数31可以将重复的数据库操作代码进行封装并定义在存储过程中，

这消除了不必要地重复编写相同的代码、降低了代码不一致性，代码的重用性对于数据库中的更改，只有存储过程是必须更新的，而应用程序保持独立，并且不必知道对数据库布局、关系或进程的任何更改的情况。易维护性542

创建带参数的存储过程本次任务中创建的存储过程可以根据用户输入的职工姓名来查询职工的性别和职称级别信息。用户输入的职工姓名通过存储过程中的输入参数代入语句中，并输出相应结果。本任务主要步骤如下：（1）定义存储过程。（2）调用存储过程。2.1实现步骤实现过程请参考视频3

管理存储过程本次任务以之前任务所创建的“pr_File”存储过程为对象，对其执行修改存储过程、删除存储过程、对存储过程更名等操作。具体任务是将该存储过程的功能由显示职工性别和职称信息，改为显示性别和电话信息，并将其更名为“pr_File1”，最后将该存储过程删除。本任务主要步骤如下：（1）修改存储过程。（2）更名存储过程。（2）删除存储过程。3.1实现步骤实现过程请参考视频3.2修改存储过程存储过程的修改除了可以通过打开语句模板实现外，也可以直接在查询编辑器中进行。修改存储过程的基本语句如下：ALTERPROCEDURE存储过程名[{@参数1数据类型}[=默认值][OUTPUT]，……，{@参数n数据类型}[=默认值][OUTPUT]]ASSQL语句……语句中各参数含义与CREATEPROCEDURE语句相同3.3删除存储过程删除存储过程同样可以使