（中职）计算机组装与维护A0701教学课件.pptx

正版可修改PPT课件（中职）计算机组装与维护A0701教学课件SQL Server数据库项目教程教学课件项目7：创建和管理存储过程我们毕业啦其实是答辩的标题地方任务1 创建并执行存储过程 在“学生成绩系统”数据库中使用T-SQL创建一个存储过程。要求该存储过程实现功能为：输入学生名字返回学生所在的班级名字，要求如下：（1）该存储过程命名为Proc_StuClass。（2）要求有判断功能，如果输入的学生不存在，则输出该学生不是本校学生。（3）执行该存储过程，分别输入学生姓名“王伟”，“常山”测试结果。任务概述 本任务可以使用2种方法完成：（1）使用SSMS图形界面方式创建并执行存储过程。（2）使用T-SQL语句创建并执行存储过程。在使用CREATE PROCEDURE语法时需要对输入参数、输出参数进行声明。任务分析1.知识准备CONTANTS2.使用SSMS创建并执行存储过程3.使用T-SQL创建并执行存储过程思维导图知识准备1.知识准备1.1 存储过程的分类存储过程是一组具有特定功能的T-SQL语句的集合，经编译后存储在数据库中，是封装重复性工作的一种方法。下面主要介绍系统存储过程、扩展存储过程和用户自定义存储过程。系统存储过程：由系统自动创建，存储在master和msdb数据库中，并以sp 为前缀。系统存储过程主要是从系统表中获取信息，为系统管理员管理SQL Server 2019提供支持。扩展存储过程：以在SQL Server 2019环境外执行的动态链接库（Dynamic-Link Libraries,DLL）来实现。一般以xp_为前缀标识。用户自定义存储过程：用户自定义存储过程是由用户编写，是指封装了可重用代码的模板或者例程。1.知识准备1.2 存储过程的优点1324存储过程只在创造时进行编译，以后每次执行都不需再重新编译。可以被多次调用执行,可减少数据库开发人员的工作量。安全性高,可实现对某些数据访问的限制，保证数据的安全。减少网络通信量。1.知识准备1.3 存储过程的缺点1324可移植性差。由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。代码可读性较差，影响维护。不支持集群。不支持面向对象的设计。无法采用面向对象的方式将逻辑业务进行封装，甚至形成通用的可支持服务的业务逻辑框架。1.知识准备1.4 创建存储过程的语法格式CREATE PROCEDURE schema_name.procedure_name ;numberparameter data_typeVARYING =default OUTPUT ,n WITH ENCRYPTION|RECOMPILE|RECOMPILE,ENCRYPTION FOR REPLICATIONAS sql_statementn1.知识准备1.41 创建语法参数说明（1）procedure_name:用于指定存储过程的名称。（2）number:用于指定对同名的过程分组。（3）parameter:用于指定存储过程中的参数。（4）data_type:用于指定参数的数据类型。（5）VARYING：指定作为输出参数支持的结果集，仅适用于游标参数。（6）default:用于指定参数的默认值。1.知识准备1.42 创建语法参数说明（7）OUTPUT：指定参数是输出参数。（8）RECOMPILE:指定数据库引擎不缓存该过程的计划，该过程在运行时编译。（9）ENCRYPTION:指定SQL Server 加密 syscomments表中包含CREATE PROCEDURE语句文本的条目。（10）FOR REPLICATION:指定不能在订阅服务器上执行为复制创建的存储过程。（11）:要包含在过程中的一个或多个T-SQL语句。1.知识准备1.5 执行存储过程的语法格式 EXECU|EXECUTE return_status=module_name ;number|module_name_var parameter =value|variable OUTPUT|DEFAULT ,n WITH RECOMPILE 1.知识准备1.51 执行语法参数说明（1）return_status:可选的整型变量，存储模块的返回状态。（2）module_name:是要调用的存储过程的完全限定或者不完全限定的名称。（3）number:可选整数，用于对同名的过程分组。（4）module_name_var:是局部定义的变量名，代表模块名称。（5）parameter:存储过程中使用的参数，与在模块中定义的相同。参数名称前必须加上符号。1.知识准备1.52 执行语法参数说明（6）value:传递给模块或传递命令的参数值。（7）variable:是用来存储参数或返回参数的变量。（8）OUTPUT:指定模块或命令字符串返回一个参数。（9）DEFAULT:根据模块的定义，提供参数的默认值。（10）WITH RECOMPILE:执行模块后，强制编译、使用和放弃新计划。建议尽量少使用该选项，因为它消耗较多的资源系统。2.使用SSMS创建并执行存储过程2.使用SSMS创建并执行存储过程2.1 实施步骤（1）打开SSMS窗口，选择“数据库”“dbStudentScore”“可编程性”“存储过程”结点。右击“存储过程”，在弹出的快捷菜单中选择“新建存储过程”菜单命令，如图7-1所示。图7-1“新建”“存储过程”菜单命令任务配备微课视频2.使用SSMS创建并执行存储过程2.1 实施步骤（2）在创建存储过程的窗口中选择“查询”“指定模板参数的值”菜单命令，如图7-2所示。图7-2“指定模板参数的值”菜单命令2.使用SSMS创建并执行存储过程2.1 实施步骤（3）弹出“指定模板参数的值”对话框，将Procedure_Name参数对应的名称修改为“Proc_StuClass1”单击“确定”按钮，即可关闭此对话框，如图7-3所示。图7-3“指定模板参数的值”对话框2.使用SSMS创建并执行存储过程2.1 实施步骤（4）在编辑窗口，输入对应代码。把一些暂时无关的代码删除，整理代码，效果如下图7-4所示，单击执行完成创建。图7-4输入代码创建存储过程2.使用SSMS创建并执行存储过程2.1 实施步骤（5）执行存储过程。在左边节点刷新后，右击刚才新建立的存储过程“Proc_StuClass1”。在弹出快捷菜单中选择“执行存储过程”菜单命令，如图7-5所示。图7-5选择“执行存储过程”菜单命令2.使用SSMS创建并执行存储过程2.1 实施步骤（6）打开“执行存储过程”窗口，输入参数值“王伟”，单击“确定”，执行结果如图7-6所示。（7）结果如图7-7所示。图7-6输入参数值“王伟”图7-7“王伟”查询结果”2.使用SSMS创建并执行存储过程2.1 实施步骤（8）再次执行存储过程“Proc_StuClass1”，输入参数值“常山”，单击“确定”，执行结果如图7-8所示。图7-8“常山”查询结果3.使用T-SQL创建并执行存储过程3.使用T-SQL创建并执行存储过程3.1 实施步骤（1）点击“新建查询”按钮，就可以在下面的命令界面中输入代码，如图7-9所示。图7-9查询界面任务配备微课视频3.使用T-SQL创建并执行存储过程3.1 实施步骤（2）输入以下代码。USE dbStudentScore;GOCREATE PROCEDURE Proc_StuClassStuName Nvarchar(20),-学生名字ClaName Nvarchar(20)OUTPUT-班级名字，输出变量AS-根据输入的学生名字查询学生IDDECLARE StuID char(10)SELECT StuID=StudentIDFROM tbStudent WHERE StudentName=StuName3.使用T-SQL创建并执行存储过程3.1 实施步骤（2）输入以下代码。-判断学生是否属于该学校，如查StuID为空，则该生不属于学校学生，否则属于学校学生IF StuID IS NullBEGINPrint 对不起，该学生不是本校学生RETURN-后面代码不执行，退出存储过程END-查询该学生的班级名字SELECT StuName=StudentName,ClaName=ClassNameFROM tbStudent inner join tbClass on tbStudent.ClassID=tbClass.ClassIDWHERE StudentID=StuIDGO3.使用T-SQL创建并执行存储过程3.1 实施步骤（3）点击执行代码。刷新左边目录树后，可以看到存储过程Proc_StuClass已经创建成功。（4）执行存储过程Proc_StuClass，以学生“王伟”为例，将“王伟”赋值给StuName，并将返回结果保存在ClaName中。USE dbStudentScore;GODECLARE StuName Nvarchar(20)=王伟,-声明变量，将学生姓名赋值给变量ClaName Nvarchar(20)EXEC Proc_StuClass StuName,ClaName OUTPUTPRINT StuName+同学属于+ClaName;-输出学生姓名和所在班级GO3.使用T-SQL创建并执行存储过程3.1 实施步骤（5）单击“执行”按钮，结果如图7-10所示：图7-10“王伟”运行结果3.使用T-SQL创建并执行存储过程3.1 实施步骤（6）同理，查询学生“常山”，结果如图7-11所示：图7-11“常山”运行结果THANKS