《ERP沙盘模拟管理综合实训手册 教学课件 ppt 作者 高冬梅第9章 存储过程》由会员分享，可在线阅读，更多相关《ERP沙盘模拟管理综合实训手册 教学课件 ppt 作者 高冬梅第9章 存储过程（67页珍藏版）》请在金锄头文库上搜索。
1、第9章 存储过程,本章学习目标: 掌握存储过程的基本概念，学会编写常用的存储过程； 对存储过程的实际应用有较好的理解。,开 始,第9章 存储过程,9.1 存储过程概述 9.2 存储过程的创建与执行 9.3 管理存储过程,9.1 存储过程概述,9.1.1 存储过程的基本概念 存储过程是已保存的 Transact-SQL 语句集合，或对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用。 可以创建过程供永久使用，也可以在一个会话（以“#”开头局部临时过程）或在所有会话（以“#”开头全局临时过程）中临时使用。 客户端应用程序可以通过指定存储过程的名字并给出参数
2、（如果该存储过程带有参数）来执行存储过程。,在 SQL Server 中，许多管理活动和信息活动都可以使用系统存储过程来执行。 在SQL Server 2008中存储过程分为三大类： 系统提供的存储过程、 用户自定义存储过程 扩展存储过程。 系统提供的存储过程 一般冠以前缀sp_，例如：sp_rename。用户可以直接使用。 用户自定义存储过程 用户在数据库中创建的存储过程（Transact-SQL存储过程和CLR存储过程）。 扩展存储过程 实际上是封装在DLL文件中的C函数，前缀为xp_，例如：xp_cmdshell。,9.1.2 存储过程的优点 允许标准组件式编程，即可在应用程序中反复调用
3、，增强了重用性和共享性。 由于存储过程在服务器端运行，并且第一次执行后在内存中保留，以后调用时不需地、再从磁盘装载，能够实现较快的执行速度。 执行存储过程则只需从客户端调用存储过程的名称即可，能够大大减少网络流量。,可被作为一种安全机制来充分利用。例如，不希望某一位用户直接访问某个表，但又必须要求他针对该表执行特定的操作，则可将该用户能针对表执行的操作编写成一个存储过程，并赋予他执行该过程的权限，这样，既使该用户能完成特定操作，又保证了数据安全。,9.2 存储过程的创建与执行,9.2.1 在图形界面下创建存储过程 单击“开始”，选择“程序”“Microsoft SQL Server 2008”
4、 “SQL Server Management Studio” “对象资源管理器”。 依次展开“数据库”、CJMS 数据库和“可编程性”。 右键单击“存储过程”，再单击“新建存储过程”。如图9-1所示。,图9-1 在快捷菜单中选择“新建存储过程”,在右侧查询编辑器中出现存储过程的模板，如图9-2所示。用户可以在此基础上编辑存储过程。,图9-2 存储过程模板窗口,在“查询”菜单上，单击“指定模板参数的值”。如图9-3所示。,图9-3 “指定模板参数的值“菜单,出现“指定模板参数的值”对话框，如图9-4所示。,图9-4 “指定模板参数的值”对话框,如果需要指定存储过程的输入输出参数，则可在对话框中
5、输入参数的值。如果创建无参数的存储过程，则删除其中“值”列数据。 单击“确定”，更新存储过程的参数值。如果是输出参数，则要在查询编辑器中的参数后加上关键字“OUTPUT”。 在查询编辑器中，编辑SELECT 语句。 若要测试语法，请在“查询”菜单上，单击“分析”。 若要保存脚本，请在“文件”菜单上，单击“保存”。输入新的文件名，再单击“保存”。 若要运行存储过程，请在工具栏上单击“新建查询”。 在查询窗口中，输入语句可以执行存储过程。 在“查询”菜单上，单击“执行”。,9.2.2 用Transacr-SQL语句创建存储过程 1. 用Transacr-SQL语句创建不带参数的存储过程 具体格式为
6、： CREATE PROCEDURE AS SQL语句 【例9-1】针对CJMS库中学生信息表tblStudents，创建一个名为Students_proc1的存储过程,该过程的作用是从数据表中查询所有软件技术系学生的信息。语句如下：,USE CJMS GO CREATE PROCEDURE Students_proc1 AS SELECT * FROM tblStudents WHERE Department=软件技术系 GO 【例9-2】创建一个多表查询的存储过程。语句如下： USE CJMS GO,CREATE PROCEDURE CJMS_joinproc AS SELECT s.St
7、udentID,s.Sname,s.Department,k.Cname,k.Credit,b.Grade FROM tblstudents s INNER JOIN tblScore b ON s.StudentID=b.StudentID INNER JOIN tblCourses k ON b.CourseID=kCourseID WHERE Sname=王芸,2.自动运行不带参数的存储过程 SQL Server启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员创建，并在sysadmin固定服务器角色下作为后台过程执行，且不能有任何参数，并在master数据库中创建。 使
8、用系统存储过程 sp_procoption可设置或停止自动执行的存储过程，也可以查看SQL Server 启动时执行的所有过程的列表。其语法格式为： sp_procoption ProcName = procedure , OptionName = option , OptionValue = value,其中： 参数ProcName被标记成自动执行的过程的名称。 参数OptionName是可选用项，唯一有效的选项是STARTUP。 参数OptionValue切换自动执行的开（ON）与关（OFF）。 说明：系统存储过程sp_configure的高级选项scan for startup proc
9、s需要被设置为1，这将由系统存储过程 sp_procoption设置。详见SQL Server联机手册。,【例9-3】假设已经在Master数据库中创建了无参数的存储过程SelfProcedure1,现将其设为随SQL Server启动自动执行。语句如下： USE MASTER GO EXEC SP_PROCOPTION SelfProcedure1,STARTUP,ON GO 3.用Transacr-SQL语句创建带参数的存储过程 语法格式为：,CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = de
10、fault OUTPUT ,.n WITH RECOMPILE ENCRYPTION RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n ,的形式是： BEGIN 语句 END 的形式是： EXTERNAL NAME assembly_name.class_name.method_name 参数说明： procedure_name ，新存储过程的名称。 ; number ，是可选整数，用于对同名的过程分组。使用一个 DROP PROCEDURE 语句可将这些分组过程一起删除。 parameter，过程中的参数。可以声明一个或多个
11、输入或输出参数。除非定义了参数的默认值或者将参数设置为等于另一个参数，否则用户必须在调用过程时为每个声明的参数提供值。,data_type ，参数的数据类型。 VARYING，指定作为输出参数支持的结果集。仅适用于 cursor 参数。 default ，参数的默认值。如果定义了 default 值，则无需指定此参数的值即可执行过程。默认值必须是常量或 NULL。如果过程使用带 LIKE 关键字的参数，则可包含下通配符%、_、 和 。 OUTPUT，指示参数是输出参数。 READONLY，指示不能在过程的主体中更新或修改参数。如果参数类型为用户定义的表类型，则必须指定 READONLY。,RE
12、COMPILE，指示该过程在运行时编译。如果指定了 FOR REPLICATION，则不能使用此选项。对于 CLR 存储过程，不能指定本参数。 ENCRYPTION，指示将 CREATE PROCEDURE 语句的原始文本转换为模糊格式(相当于加密)。 EXTERNAL NAME assembly_name.class_name.method_name，指定 .NET Framework 程序集的方法，以便 CLR 存储过程引用。具体参见SQL Server 2008联机手册。 成功执行 CREATE PROCEDURE 语句后，过程名称将存储在 sysobjects 系统表中，而 CREAT
13、E PROCEDURE 语句的文本将存储在 syscomments 中。如果要创建的存储过程名与一个已经存在的存储过程同名，则先要删除同名的过程。删除的方法请参见例9-14。,【例9-4】指定输入参数。针对学生信息表tblStudents，创建一个名为CJMS_proc1的存储过程，执行该过程将向表中插入一条记录，新记录的值由参数提供。语句如下： USE CJMS GO CREATE PROCEDURE CJMS_proc1 (StudentID char(10),Sname char(8),Sex char(2),Birthday datetime,Department varchar(40
14、),Note varchar(200) ) -形式参数,As INSERT INTO tblStudents VALUES(StudentID,Sname,Sex,Birthday,Department,Note ) GO 【例9-5】指定输入参数。针对学生信息表tblStudents，创建一个名为CJMS_proc2的存储过程，执行该过程将显示指定姓名的记录，姓名由参数提供。语句如下： USE CJMS GO,CREATE PROCEDURE CJMS_proc2 Sname varchar(8) -形式参数 As SELECT * FROM tblStudents WHERE Sname=
15、Sname GO 【例9-6】利用输出参数计算阶乘，并保证其在每次执行时都被重编译处理，且对存储过程的原始文本转换为模糊格式（加密处理）。语句如下： USE CJMS GO,CREATE PROCEDURE CJMS_proc3 in float,out float OUTPUT WITH RECOMPILE，ENCRYPTION AS DECLARE i int DECLARE s float SET i=1 SET s=1 WHILE i=in BEGIN SET s=s*i,SET i=i+1 END SET out=s 【例9-7】在数据库CJMS上新建一个统计人数的存储过程名CJMS
16、_proc4，其功能是输入两个日期型数据，并使用输出参数返回这两个出生日期之间的所有学生人数。语句如下： USE CJMS GO CREATE PROCEDURE CJMS_proc4 startdate datetime,enddate datetime,recordcount int OUTPUT,AS IF startdate is NULL or enddate IS NULL BEGIN RAISERROR( NULL value are invalid,5,5) RETURN END SELECT * FROM tblStudents WHERE Birthday BETWEEN startdate AND enddate SELECT recordcount=R
