第十一章,存储过程,课程内容回顾,事务的概念 事务的特性:原子性、一致性、隔离性和持久性 (ACID) 事务可以分为如下类型： 显式事务 隐式事务 自动提交事务 索引的好处和分类 聚集索引和非聚集索引。 视图的好处,本章目标,了解存储过程的优点 掌握常用的系统存储过程 掌握如何创建存储过程 掌握如何调用存储过程,存储过程介绍,存储和执行T-SQL程序的方法有两种: 一种是在本地存储程序，然后创建应用程序来将命令发送到SQL Server并对结果进行处理。 另一种方法是将程序存储为SQL Server中的存储过程，然后创建应用程序来执行存储过程并对结果进行处理。 存储过程 用户定义的存储过程:数据库开发人员或管理员编写的用来运行经常执行的管理任务，或者应用复杂的业务规则。包含数据操纵或数据检索语句 系统存储过程:SQL Server提供了一些预编译的存储过程，用以管理SQL Server和显示有关数据库和用户的信息,存储过程中的语句,存储过程 - -,单个 SELECT 语句,SELECT 语句块,可以包含,SELECT语句与逻辑控制语句,存储过程中的语句,SQL Server中的存储过程与其他语言中的过程或函数类似，它们的共同特征是 : 它们都接收输入参数，并向调用过程或语句返回值。 它们都包含在数据库中执行操作或调用其他存储过程的编程语句。 它们都向调用过程返回状态值，指示执行过程是否成功,存储过程的优点,存储过程的优点: 允许模块化程序设计 只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次 允许更快执行 存储过程将比Transact-SQL批代码的执行要快 减少网络流量 存储过程存储在后端数据库中不需要通过网络传输 可作为安全机制使用 即使对于没有直接执行存储过程中语句权限的用户，也可授予他执行该存储过程的权限,常用的系统存储过程,SQL Server提供系统存储过程，它们是一组预编译的T-SQL语句 所有系统存储过程的名称都以“_sp”开头。系统存储过程位于master数据库中,常用的系统存储过程的使用,常用的扩展存储过程：xp_cmdshell 它可以完成DOS命令下的一些操作，诸如创建文件夹、列出文件等 语法: 示例: xp_cmdshell扩展存储过程的使用,EXEC xp_cmdshell DOS 命令 NO_OUTPUT,USE master GO -创建文件夹H:prod exec xp_cmdshell mkdir H:prod,NO_OUTPUT exec xp_cmdshell dir H:prod -查看文件,常用的系统存储过程的使用,EXEC sp_databases -不带参数 EXEC sp_helpdb -可带参数,也可不带,所有数据库中可用 USE empDB -指定数据库empDB GO EXEC sp_help emp -带参数,参数为表emp,示例:其他系统存储过程的使用,一些系统存储过程必须在特定的数据库中使用,大多数在所有数据库中可用,创建存储过程,创建存储过程 SSMS:可视化的方式 T-SQL:代码(重点介绍) 使用CREATE PROCEDURE语句创建存储过程。所有的存储过程都创建在当前数据库中 语法:,CREATE PROCEDURE 存储过程名 参数1 数据类型 =默认值 OUTPUT, ., 参数n 数据类型 =默认值 OUTPUT AS SQL语句,其中,参数部分为可选,创建不带参数的存储过程,问题：查看部门的平均工资以及各部门工资不超过2000的员工，并根据工资数额显示加薪信息,USE empDB GO /*-创建存储过程-*/ CREATE PROCEDURE proc_emp AS DECLARE salAvg float, salSum float SELECT salAvg=AVG(sal),salSum=sum(sal) FROM emp print 员工平均工资： + convert(varchar(10),salAvg) print 员工总工资： + convert(varchar(10),salSum) IF (salAvg2000) print 员工不需要加薪 ELSE print 员工需要加薪 print - print 部门需要加薪的员工: SELECT dname,empno,ename,sal FROM dept INNER JOIN emp ON dept.deptno=emp.deptno WHERE sal2000 GO,调用存储过程,语法: 调用示例存储过程 执行结果如下:,EXEC 过程名 参数,EXEC proc_emp,创建带参数的存储过程,CREATE PROCEDURE 存储过程名 参数1 数据类型 =默认值 OUTPUT, ., 参数n 数据类型 =默认值 OUTPUT AS SQL语句,回顾创建存储过程的语法,存储过程中的参数可分为2种: 输入参数:可以在调用时向存储过程传递参数,此参数可用来在存储过程中传入值 输出参数:如果希望返回值，则可以使用输出参数，输出参数后有“OUTPUT”标记，执行存储过程后，将把返回值存放在输出参数中，可供其他T-SQL语句读取访问,CREATE PROCEDURE 语句中声明一个或多个变量作为参数 参数 数据类型=默认值,创建带输入参数的存储过程示例-1,emp表中，当部门不同时，需要加薪的员工和部门的平均工资会有所不同 问题：给定部门号，求该部门的平均工资,USE empDB GO /*-创建存储过程-*/ CREATE PROCEDURE pro_emp dno int AS declare salavg float SELECT salavg=AVG(sal) FROM emp WHERE deptno=dno print 部门平均工资为: + convert(varchar(10),salavg) GO /*- 调用存储过程 -*/ -给定部门号10，求出该部门的平均工资 EXEC pro_emp 10,演示创建带输入参数的存储过程,创建带输入参数的存储过程示例-2,问题：对于不同部门，求出从事销售工作的人数,USE empDB GO /*-创建参数带默认值的存储过程-*/ CREATE PROCEDURE proc_job dno int=20, jb varchar(20)=SALESMAN AS declare rs int print 部门: + convert(varchar(5),dno) + 工作: + jb print - SELECT rs=COUNT(*) FROM emp WHERE deptno=dno and job=jb print 人数： + convert(varchar(10),rs) GO /*- 调用存储过程-*/ EXEC proc_job -都采用默认值， 得到10部门，从事销售工作的人数 EXEC proc_job 30 -部门号为30，工种采用默认值 EXEC proc_job 20, CLERK -都不采用默认值，部门号位0，工种为CLERK -错误的调用方式：EXEC proc_job ,CLERK 部门号为默认值 -正确的调用方式：EXEC proc_job jb=CLERK 部门号为默认值,创建带输出参数的存储过程,如果希望调用存储过程后，返回一个或多个值，就需要使用输出(OUTPUT)参数了。 为了使用输出参数，必须在CREATE PRODECURE 语句和EXECUTE语句中指定OUTPUT关键字。在执行存储过程时，如果忽略OUTPUT关键字，存储过程仍然会执行但不返回值,CREATE PROCEDURE 存储过程名 参数1 数据类型 =默认值 OUTPUT, ., 参数n 数据类型 =默认值 OUTPUT AS SQL语句,表示输出参数,创建带输出参数的存储过程示例-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 GO /*调用存储过程，传递两个实参5和6，将求得结果输出到变量answer*/ DECLARE answer smallint EXEC MathProd 5,6,answer OUTPUT SELECT The result is: ,answer,问题:创建一个名为MathProd的存储过程，用以计算出两个数字的乘积，然后声明一个变量以打印字符串“The result is：,演示创建带输出参数的存储过程,修改和删除存储过程,修改存储过程 可以通过SSMS或T-SQL语句修改存储过程。 使用ALTER PROCEDURE 语句来修改现有的存储过程，在使用ALTER PROCEDURE进行修改时，SQLServer会覆盖存储过程以前的定义 删除存储过程 DROP PROCEDURE 语句用来从当前的数据库删除用户定义的存储过程 语法: 示例:删除存储过程MathProd,DROP PROCEDURE 存储过程名,USE empDB GO DROP PROCEDURE MathProd GO,错误信息处理,为了提高存储过程的效率，存储过程应该包含与用户进行交互的状态（成功或失败）的错误信息，在错误发生时，尽可能给客户提供足够多的信息 在错误处理中可以检查以下内容： SQL Server错误 自定义的错误信息。 error 这个系统函数包含最近一次执行的Transact-SQL语句的错误编号。当语句执行时，对错误编号进行清除并重新设置。 RAISERROR 使用RAISERROR语句能返回用户定义的错误信息并设置一个系统标志来记录已经发生的错误。 在使用RAISERROR语句时必须指定错误严重级别和信息状态。,错误信息处理示例,问题:在empDB数据库中创建名为AddRec的存储过程， 该存储过程使用error系统函数来确定在每个INSERT语句执行 时是否发生错误。如 果发生错误，事务将回滚,USE empDB GO CREATE PROCEDURE AddRec deptno int=NULL, dname varchar(20)=NULL, lo