《SQL Server 2005数据库案例教程》-严波-电子教案 第11章存储过程.ppt

第十一章　 存储过程 课程内容回顾 事务的概念 事务的特性:原子性、一致性、隔离性和持久性 (ACID) 事务可以分为如下类型： 显式事务 隐式事务 自动提交事务 索引的好处和分类 聚集索引和非聚集索引。 视图的好处 本章目标 了解存储过程的优点 掌握常用的系统存储过程 掌握如何创建存储过程 掌握如何调用存储过程 存储过程介绍 存储过程中的语句 存储过程中的语句 存储过程的优点 存储过程的优点: 允许模块化程序设计 只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次 允许更快执行 存储过程将比Transact-SQL批代码的执行要快 减少网络流量 存储过程存储在后端数据库中不需要通过网络传输 可作为安全机制使用 即使对于没有直接执行存储过程中语句权限的用户，也可授予他执行该存储过程的权限 常用的系统存储过程 SQL Server提供系统存储过程，它们是一组预编译的T-SQL语句 所有系统存储过程的名称都以“_sp”开头。系统存储过程位于master数据库中 常用的系统存储过程的使用 常用的扩展存储过程：xp_cmdshell 它可以完成DOS命令下的一些操作，诸如创建文件夹、列出文件等 语法: 示例: xp_cmdshell扩展存储过程的使用 常用的系统存储过程的使用 EXEC sp_databases --不带参数 EXEC sp_helpdb --可带参数,也可不带,所有数据库中可用 USE empDB --指定数据库empDB GO EXEC sp_help emp --带参数,参数为表emp 创建存储过程 创建存储过程 SSMS:可视化的方式 T-SQL:代码(重点介绍) 使用CREATE PROCEDURE语句创建存储过程。所有的存储过程都创建在当前数据库中 语法: 创建不带参数的存储过程 问题：查看部门的平均工资以及各部门工资不超过2000的员工，并根据工资数额显示加薪信息 调用存储过程 语法: 调用示例存储过程 执行结果如下: 创建带参数的存储过程 CREATE PROC[EDURE] 存储过程名 [ {@参数1 数据类型} [=默认值] [OUTPUT], ......., {@参数n 数据类型} [=默认值] [OUTPUT] ] AS SQL语句 创建带输入参数的存储过程示例-1 emp表中，当部门不同时，需要加薪的员工和部门的平均工资会有所不同 问题：给定部门号，求该部门的平均工资 创建带输入参数的存储过程示例-2 问题：对于不同部门，求出从事销售工作的人数 创建带输出参数的存储过程 如果希望调用存储过程后，返回一个或多个值，就需要使用输出(OUTPUT)参数了。 为了使用输出参数，必须在CREATE PRODECURE 语句和EXECUTE语句中指定OUTPUT关键字。在执行存储过程时，如果忽略OUTPUT关键字，存储过程仍然会执行但不返回值 创建带输出参数的存储过程示例-1 USE empDB GO CREATE PROCEDURE pro_job @dno int=20, @jb varchar(20)=SALESMAN, --默认参数放后 @rs int output --OUTPUT关键字，否则视为输入参数 AS print 部门: + convert(varchar(5),@dno) + 工作: + @jb print --------------------------- SELECT @rs=COUNT(*) FROM emp WHERE deptno=@dno and job=@jb print 人数： + convert(varchar(10),@rs) GO /*--调用存储过程--*/ declare @no int --定义变量，用于存放调用存储过程时返回的结果 exec pro_job 30, CLERK,@no output --调用时要带有OUTPUT关键字 print 求得的人数是： + convert(varchar(10),@no) 创建带输出参数的存储过程示例-2 USE empDB GO CREATE PROCEDURE MathProd @m1 smallint， @m2 smallint， @result smallint OUTPUT AS SET @result=@m1 * @m2