sql存储过程讲解.ppt

1、SQL SERVER 2000中文版 入门与提高 主讲：王玮,第十八章存储过程和触发器,存储过程和触发器的功能和特点 创建存储过程 修改存储过程 删除存储过程,本章主要内容,存储过程概述,什么是存储过程 一组预先编释好的代码，可以做为一个独立的数据库对象，也可以作为一个单元被用户的应用程序调用。 存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次无须编译可以直接调用执行。,允许模块化编程，增强代码的重用性和共享性 使用存储过程可以加快程序的运行速度. 使用存储过程可以减少网络流量。 存储过程可以提高数据库的安全性。,存储过程的优点,1、系统存储过程 2、用户存储过程 3

2、、临时存储过程 4、扩展存储过程 5、远程存储过程,存储过程的分类,系统存储过程是由系统提供的过程，可以作为命令直接执行。系统存储过程存储在master数据库中，其前缀为sp_。系统存储过程可以在任意一个数据库中执行。 用户存储过程是创建在用户数据库中的存储过程。其名称前面一般不加sp_前缀。主要在应用程序中使用，以完成特定的任务。 临时存储过程属于用户存储过程。如果用户存储过程前面加上符号“#”，则该存储过程称为局部临时存储过程，只能在一个用户会话中使用。,存储过程的分类,4、扩展存储过程是在SQL Server环境之外执行的动态链接库DLL，其前缀为xp_。尽管这些动态链接库在SQL Se

3、rver环境之外，但它们可以被加载到SQL Server系统中，并且按照存储过程的方式执行。 5、远程存储过程是指从远程服务器上调用的存储过程，或者是从连接到另外一个服务器上的客户机上调用的存储过程。,存储过程的分类,创建存储过程,在SQL Server中使用三种方法创建存储过程 ： 使用创建存储过程向导创建存储过程。 利用SQL Server 企业管理器创建存储过程。 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。,使用向导创建存储过程,使用企业管理器创建存储过程,创建存储过程,CREATE PROCEDURE 存储过程名;数字 参数 数据类型 =de

4、faultOUTPUT With RECOMPILE -重新编译 | ENCRYPTION -加密 | RECOMPILE , ENCRYPTION AS SQL语句,不带参数的存储过程： CREATE PROC pr_例一 AS SELECT * FROM 学生花名册 GO EXEC pr_例一,查看存储过程,用于显示存储过程的参数及其数据类型 sp_help objname=存储过程名称 用于显示存储过程的源代码 sp_helptext objname= 存储过程名称 用于显示和存储过程相关的数据库对象 sp_depends objname= 存储过程名称 用于返回当前数据库中的存储过程列

5、表 sp_stored_procedures objname= 存储过程名称,执行存储过程,如果对存储过程的调用是批处理的第一条语句，则可以省略exec关键字，直接使用存储过程的名字调 用这个存储过程写入存储过程名直接执行。 Execute 存储过程名,删除存储过程,Drop procedure 名字,n,修改存储过程,Alter procdure 存储过程名 with encryption As SQL语句,修改pr_例一存储过程，使其按年龄排序 ALTER PROC pr_例一 AS SELECT * FROM 学生花名册 ORDER BY 年龄 EXEC pr_例一,重命名存储过程,sp

6、_rename 原存储过程名称,新存储过程名称,通过存储过程传递参数,Create PROC 过程名 (参数 数据类型 =DEFAULT OUTPUT ) AS SQL语句 Default:定义参数的默认值，如果定义了一个默认值，则在调用存储过程时，可以不指定该参数的值。默认值必须是一个常量，或者是null。,带参数的存储过程： CREATE PROCEDURE pr_例二 xh int = 1 AS BEGIN SET NOCOUNT ON SELECT * FROM 学生花名册 WHERE 学号=xh END DECLARE 接收值 INT EXEC pr_例二 6 PRINT 接收值,存

7、储过程的执行 EXECUTE 返回值 = 存储过程名;数字 参数= 值|变量 OUTPUT|DEFAULT WITH RECOMPILE,CREATE PROC pr_例一 AS BEGIN DECLARE 返回值 int SELECT 返回值=COUNT(DISTINCT 学号) FROM 学生花名册 RETURN 返回值 END EXEC pr_例一,DECLARE 接收值 INT EXEC 接收值=pr_例一 PRINT 接收值,CREATE PROC pr_例一 返回值 INT OUTPUT AS BEGIN SELECT 返回值=COUNT(DISTINCT 学号) FROM 学生花

8、名册 END,DECLARE 接收值 INT EXEC pr_例一 接收值 OUTPUT PRINT 接收值,创建一个存储过程，求两个整数的乘积 CREATE PROC MUL a INT， b INT， RESULT INT OUTPUT AS SET RESULT =a*b GO,DECLARE 接收值 INT EXEC MUL 9,3,接收值 OUTPUT PRINT 接收值,CREATE PROCEDURE factorial in float, out float OUTPUT AS DECLARE i int,s float SET i=1 SET s=1 WHILE i=in B

9、EGIN SET s=s*i SET i=i+1 END SET out=s DECLARE ou float EXEC factorial 5,ou OUTPUT PRINT 其阶乘为：+RTRIM(ou),CREATE PROCEDURE pr_例六 姓名 varchar(15) OUTPUT, SQL int AS BEGIN SELECT * FROM 成绩 WHERE 成绩.SQL SQL END DECLARE 姓名 varchar(15) EXEC pr_例六 姓名 OUTPUT,88 PRINT 姓名,CREATE PROC pr_例七 AS BEGIN declare 返回值

10、 int SELECT 返回值 = MAX(SQL) FROM 成绩 return 返回值 END DECLARE 接收值 INT EXEC 接收值=pr_例七,创建一个加密的存储过程 CREATE PROC 学生信息 姓名 nvarchar(15) WITH ENCRYPTION AS SELECT * FROM 学生花名册 WHERE 姓名= 姓名 DECLARE 姓名 varchar(15) EXEC 学生信息 姓名=王小兵 PRINT 姓名,存储过程组： CREATE PROC pr_例十一;1 as select * from 学生花名册 GO CREATE PROC pr_例十一;2 学号 int as select * from 学生花名册 where 学号= 学号 GO,CREATE PROC #存储过程 AS SELECT * FROM 学生花名册 GO EXEC #存储过程,临时存储过程,注意： RETURN 返回的是整数 OUTPUT 可以返回任何数据,使用T-SQL语句创建