《SQLServer 2005存储过程的基本操作》由会员分享，可在线阅读，更多相关《SQLServer 2005存储过程的基本操作（56页珍藏版）》请在金锄头文库上搜索。
1、第十讲 存储过程2复习 索引是以表列为基础建立的数据库对象，记录了索引列在数据表中的 物理存储位置，实现了表中数据的逻辑排序。 聚集索引（唯一索引）表中数据行的物理存储顺序与索引顺序 完全相同，而非聚集索引表中的数据行不按非聚集索引键的顺序排 序和存储。 使用索引的意义：v可以提高查询速度；v可以保证数据记录的唯一性；v减少查询中分组和排序的时间；v加速表与表之间的连接。3建立索引的原则 适合建立索引的列： v主键列自动建立聚集索引。 v外键列应建立索引。 v在经常查询的字段上最好建立索引。 vorder by 子句中使用的列。 不适合建立索引的列： v对于那些查询中很少涉及的列、重复值比较多
2、的列不要 建索引。 v对于定义为text，Image和Bit数据类型的列上不要建立 索引。4存储过程v 例：创建视图，查看“计算机系”的班级名称。 经管系 create view xb_bjmcasselect 班级名称from 班级where 系部代码=（select 系部代码from 系部where 系部名称=经管系）计算机系create view xb_bjmcasselect 班级名称from 班级where 系部代码=（select 系部代码from 系部where 系部名称=计算机系）商务技术系create view xb_bjmcasselect 班级名称from 班级where
3、 系部代码=（select 系部代码from 系部where 系部名称=商务技术系）5问题：v有没有一种好的办法，不用为每个系部都创建一个视图来查看该系部的信息，而是通过一种通用的方法来实现呢？存储过程 PROCEDURE6存储过程 教学目标：v 1、明确存储过程的概念及作用v 2、学会存储过程的基本操作v 3、学会存储过程的基本操作（创建和执行）7存储过程 1、存储过程的概念v存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编 译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用执 行。存储过程在第一次执行时进行编译，然后将编译好的代码保存在 高速缓存中以便以后调用，这样可
4、以提高代码的执行效率。一个过程 一旦被创建，就可以通过该过程的名称或者必要的参数进行调用。 存储过程同其它编程语言中的过程相似，有如下特点：v接收输入参数并以输出参数的形式将多个值返回至调用过程或批处理 。v包含执行数据库操作（包括调用其它过程）的编程语句。v向调用过程或批处理返回状态值，以表明成功或失败以及失败的原因 。8存储过程 存储过程的优点:v安全机制：只给用户访问存储过程的权限，而不授予用户访问表 和视图的权限。v改良了执行性能：在第一次执行时进行编译，将编译好的代码保存在 高速缓存中以便以后调用，以后执行无需编译。这样可以提高代码的 执行效率。v减少网络流量：存储过程存在于服务器上
5、，调用时，只需传递执行存 储过程的执行命令和返回结果。v模块化的程序设计：增强了代码的可重用性，提高了开发效率。v简化复杂语句：可以在单个存储过程中执行一系列SQL语句；可以在一个的存储过程内引用其它的存储过程，可以简化一系列复杂语句。9存储过程 2、存储过程的类型v用户定义的存储过程：用户定义的存储过程是用户根据需要，为完成某一特定功能，在自己的普通数据库中创建的存储过程。v系统存储过程：系统存储过程存储在master数据库中，以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQL Server提供帮助，为用户查看数据库对象提供方便。v扩展存储过程：扩展存储过程以xp_为前缀，它
6、是关系数据库引擎的开放式数据服务层的一部分，可以使用户在动态链接库(DLL)文件所包含的函数中实现逻辑，从而扩展了T-SQL的功能，并且可以象调用T-SQL过程那样从T-SQL语句调用这些函数。10存储过程的操作 简单存储过程的操作包括：v创建存储过程v执行存储过程v修改存储过程v删除存储过程11存储过程的基本操作-创建 创建存储过程时，需要注意下列事项：v1、只能在当前数据库中创建存储过程。v2、数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。v3、存储过程是数据库对象，其名称必须遵守标识符命名规则。v4、不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合
7、到单个批处理中。v5、创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。12创建不带参数的存储过程 使用SQL语句创建不带参数的存储过程语法格式如下：CREATE PROC EDURE 存储过程名;numberWITH recompile| encryption| recompile, encryptionAS SQL语句 说明： v存储过程名必须符合标识符规则，且对于数据库及其所有者必须唯一 。 vnumber：该参数是可选的整数，用来对同名的过程分组，以便用一 条DROP PROCEDURE 语句
8、即可将同组的过程一起删除。 RECOMPILE ：SQL 不会缓存该过程的计划，每次执行该过程时都要 重新编译。 vENCRYPTION ：所创建的存储过程的内容会被加密, 可防止将过程 作为 SQL Server 复制的一部分发布。13问题1：创建存储过程proc_jsjxbj，显示计算机系的班级名称。 创建不带参数的存储过程SELECT 班级名称 FROM 班级 WHERE 系部代码 = (SELECT系部代码 FROM 系部WHERE系部名称=计算机系)GO（1）先在查询编辑器中编写实现存储过程功能的T-SQL语句。14（3）单击“分析”按钮，进行语法检查；语法无误后，单击“执行”按钮，
9、 创建该存储过程。 创建不带参数的存储过程CREATE PROC proc_jsjxbj AS SELECT 班级名称 FROM 班级 WHERE 系部代码 = (SELECT系部代码 FROM 系部WHERE系部名称=计算机系) GO（2）调试上述SQL语句正确后，再在查询编辑器中输入其完整的创建存储过程的代码，创建存储过程。15执行不带参数的存储过程 执行不带参数的存储过程的语法如下：v EXECUTE 存储过程名 说明：如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略，可以使用存储过程的名字执行该存储过程。 例：在查询分析器中执行存储过程proc_jsjxbj。 执行方式如
10、下: vproc_jsjxbjvEXEC proc_jsjxbjvEXECUTE proc_jsjxbj16创建带输入参数的存储过程 问题2: 能否创建一个存储过程proc_bjmc，通过接收可变的系部名称，显示指定系部的班级名称呢？ 使用SQL语句创建带输入参数的存储过程的语法格式如下：CREATE PROC EDURE 存储过程名; number 参数1 数据类型WITH recompile|encryption|remcompile,encryptionAS SQL语句17创建带输入参数的存储过程（3）单击“分析”按钮，进行语法检查；语法无误后，单击“执行”按钮， 创建该存储过程。SEL
11、ECT 班级名称 FROM 班级 WHERE 系部代码 = (SELECT系部代码 FROM 系部WHERE系部名称=计算机系)CREATE PROC proc_bjmc xbmc varchar(30) AS SELECT 班级名称 FROM 班级 WHERE 系部代码 = (SELECT系部代码 FROM 系部WHERE系部名称 xbmc ） GO（1）以一个“具体系部”为基础编写出T-SQL语句。（2）调试该语句正确后，以变量代替“具体系部”，再创建存储过程。18执行带输入参数的存储过程 执行带参数的存储过程，可以采用以下两种方式：EXEC 存储过程名 值| 参数名称= 值v按位置传递：
12、在调用存储过程时，直接给出参数值。如果多于一个参数，给出的参数值要与定义参数的顺序一致。u如：EXEC proc_bjmc 商务技术系v使用参数名称传递：在调用存储过程时，按“参数名=参数值”的形式给出参数值。如果参数多于一个时，给出参数的顺序可以与定义参数的顺序不一致。u如：EXEC proc_bjmc xbmc=计算机系19 问题：v对于带输入参数的存储过程，能否在执行时在不指定系部名称的情况下显示默认系部的班级名称，又能接收新的系部名称，显示新系部的信息呢？20创建输入参数带默认值的存储过程 创建输入参数带默认值的存储过程的语法格式如下：CREATE PROC 存储过程名参数1 数据类型
13、 =默认值AS SQL语句 问题3：创建存储过程proc_bjmc_xb，实现默认显示计算机系的班级，同时在执行时还能接收新的系部名称，显示新系部名称的班级信息。21创建输入参数带默认值的存储过程CREATE PROC proc_bjmc_xbxbmc varchar(30)=计算机系 ASSELECT 班级名称 FROM 班级WHERE 系部代码=(SELECT 系部代码FROM 系部WHERE 系部名称=xbmc)GO22执行输入参数带默认值的存储过程 执行输入参数带默认值的存储过程:v如果不为参数赋值,则参数使用默认值v如果为参数赋新值,则将新值传递给参数v如：uEXEC proc_bj
14、mc_xbuEXEC proc_bjmc_xb xbmc=商务技术系23巩固提高 任务1_1：创建存储过程proc_stu，显示student表中所 有学生的信息（思考）。 USE student GO CREATE PROC proc_stu AS SELECT * FROM 学生 GO EXEC proc_stu24巩固提高 任务1_2：创建存储过程proc_stu_xh，查询用户指定学号的 学生信息。（思考）USE student GO CREATE PROC proc_stu_xh xh char(12) AS SELECT * FROM 学生 WHERE 学号=xh GO EXEC
15、proc_ stu_xh 05010100100125巩固提高 任务1_3：创建存储过程proc_stu_xh，既能显示默认学号的 学生信息，又能查询用户指定学号的学生信息。（思考）USE student GO CREATE PROC proc_stu_xh xh char(12)=050101001001 AS SELECT * FROM 学生 WHERE 学号=xh GO EXEC proc_ stu_xh EXEC proc_ stu_xh 05010200100126问题：v存储过程中可以带一个输入参数，能否带多个输入参数呢？27创建带2个输入参数的存储过程 创建带多个输入参数的存储过
16、程的语法格式如下： CREATE PROC dbo.存储过程名 参数1 数据类型 =默认值，参数2 数据类型 =默认 值AS SQL语句 提示: v创建带多个输入参数的存储过程时同样可以为参数指定默认值.v执行带多个参数的存储过程时:u如果不为参数赋值，则参数使用默认值;u如果为参数赋新值，则将新值传递给参数u如果加上参数名称，则参数的顺序可以颠倒，否则只能按指定 的顺序28创建带2个输入参数的存储过程 问题4：存储过程proc_xmkc，查询某个学生、某门课程 的成绩。CREATE PROC proc_xmkc xm char(8)=李冰,kc char(20)=SQL AS SELECT 姓名,成绩
