第八讲存储过程优秀课件.ppt
第八讲存储过程第1页，本讲稿共21页2本章目标了解存储过程的优点掌握常用的系统存储过程掌握如何创建存储过程掌握如何调用存储过程第2页，本讲稿共21页3q存储过程（procedure）类似于C#语言中的函数q用来执行管理任务或应用复杂的业务规则q存储过程可以带参数，也可以返回结果int sum(int a,int b)int s;s=a+b;return s;存储过程相当于C#语言中的函数什么是存储过程第3页，本讲稿共21页4存储过程-单个 SELECT 语句SELECT 语句块SELECT语句与逻辑控制语句可以包含可以包含什么是存储过程q存储过程可以包含数据操纵语句、变量、逻辑 控制语句等第4页，本讲稿共21页5q执行速度更快q允许模块化程序设计 q提高系统安全性q减少网络流通量存储过程的优点第5页，本讲稿共21页6q系统存储过程q由系统定义，存放在master数据库中q类似C#语言中的系统函数q系统存储过程的名称都以“sp_”开头或”xp_”开头q用户自定义存储过程q由用户在自己的数据库中创建的存储过程q类似C#语言中的用户自定义函数存储过程的分类 第6页，本讲稿共21页7常用的系统存储过程系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_rename更改表名/视图名/存储过程名sp_help查看某个表的所有信息第7页，本讲稿共21页8EXEC sp_databasesEXEC sp_renamedb Northwind,Northwind1USE stuDBGOEXEC sp_tablesEXEC sp_columns stuInfo EXEC sp_help stuInfoEXEC sp_helpconstraint stuInfoEXEC sp_helpindex stuMarksEXEC sp_helptext view_stuInfo_stuMarks EXEC sp_stored_procedures 常用的系统存储过程修改数据库的名称(单用户访问)列出当前系统中的数据库当前数据库中查询的对象的列表返回某个表列的信息查看表stuInfo的信息查看表stuInfo的约束查看表stuMarks的索引查看视图的语句文本查看当前数据库中的存储过程演示：常用的存储过程第8页，本讲稿共21页9q定义存储过程的语法 CREATE PROCEDURE 存储过程名存储过程名 参数参数1 数据类型数据类型=默认值默认值 OUTPUT,参数参数n 数据类型数据类型=默认值默认值 OUTPUT AS SQL语句语句 GOq和C#语言的函数一样，参数可选q参数分为输入参数、输出参数 q输入参数允许有默认值创建存储过程第9页，本讲稿共21页创建不带参数的存储过程q问题问题：请创建存储过程，查看本次考试平均分以及未通过考试的学员名单第10页，本讲稿共21页11qEXECUTE（执行）语句用来调用存储过程q调用的语法EXECUTE 存储过程名 参数 调用执行存储过程EXEC proc_stu第11页，本讲稿共21页12创建带参数的存储过程q存储过程的参数分两种：q输入参数q输出参数int sum(int a,int b)int s;s=a+b;return s;c=sum（5,8）传入参数值q输入参数：用于向存储过程传入值，类似C#语言的按值传递;q 输出参数：用于在调用存储过程后，返回结果，类似C#语言的 按引用传递;返回结果第12页，本讲稿共21页13带输入参数的存储过程问题：问题：第13页，本讲稿共21页14带输入参数的存储过程EXEC proc_stu 60,55 q调用带参数的存储过程 -或这样调用：EXEC proc_stu labPass=55,writtenPass=60第14页，本讲稿共21页15输入参数的默认值带参数的存储过程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线问题：问题：如果试卷的难易程度合适，则调用者还是必须如此调用：EXEC proc_stu 60,60，比较麻烦这样调用就比较合理：EXEC proc_stu 55EXEC proc_stu 笔试及格线55分，机试及格线默认为60分 笔试和机试及格线都默认为标准的60分第15页，本讲稿共21页16CREATE PROCEDURE proc_stu writtenPass int=60,labPass int=60 AS print-print 参加本次考试没有通过的学员：SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO笔试及格线：默认为60分机试及格线：默认为60分查询没有通过考试的学员输入参数的默认值第16页，本讲稿共21页17输入参数的默认值EXEC proc_stu -都采用默认值 EXEC proc_stu 64 -机试采用默认值 EXEC proc_stu 60,55 -都不采用默认值 q调用带参数默认值的存储过程-错误的调用方式：希望笔试采用默认值，机试及格线55分EXEC proc_stu ,55-正确的调用方式：EXEC proc_stu labPass=55 第17页，本讲稿共21页18带输出参数的存储过程q如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了 问题：问题：第18页，本讲稿共21页19CREATE PROCEDURE proc_stu notpassSum int OUTPUT,writtenPass int=60,labPass int=60 AS SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass SELECT notpassSum=COUNT(stuNo)FROM stuMarks WHERE writtenExamwrittenPass OR labExam=3 print 未通过人数：+convert(varchar(5),sum)+人,超过60%,及格分数线还应下调ELSE print 未通过人数：+convert(varchar(5),sum)+人,已控制在60%以下，及格分数线适中GO q调用带输出参数的存储过程带输出参数的存储过程 3-3调用时必须带OUTPUT关键字，返回结果将存放在变量sum中 后续语句引用返回结果第20页，本讲稿共21页21总结 存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等 存储过程允许带参数，参数分为：输入参数输出参数 其中，输入参数可以有默认值。输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值 输出参数从存储过程中返回（输出）值，后面跟随OUTPUT关键字第21页，本讲稿共21页
