《sql server数据库应用与实践教程》课件第7章-存储过程-输入参数.ppt

1、 要查询要查询1号学生对应的姓名。号学生对应的姓名。解决方案：解决方案：SELECT StuName FROM Student WHERE StuID=A00001 要查询要查询2号学生对应的姓名。号学生对应的姓名。解决方案：解决方案：SELECT StuName FROM Student WHERE StuID=A00002 要查询要查询3号直至号直至n号学生对应的姓名。号学生对应的姓名。解决方案：解决方案：SELECT StuName FROM Student WHERE StuID=？SELECT StuName FROM Student WHERE StuID=StuID 用户输入：用

2、户输入：A00005Student(StuID,StuName,StuAge,StuSex,StuScore,DepID)第七章第七章存储过程存储过程-输入参数输入参数存储过程的概念存储过程的概念创建带有参数的存储过程创建带有参数的存储过程(输入参数输入参数)存储过程的调用存储过程的调用 存储过程（存储过程（Stored Procedure）是在大型数据库系）是在大型数据库系统中，一组为了完成特定功能的统中，一组为了完成特定功能的SQL 语句集，经编译后存语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参

3、数）来执行它。（如果该存储过程带有参数）来执行它。系统存储过程系统存储过程:SQL Server内置的存储过程，存储在内置的存储过程，存储在master库中，主要用途是执行库中，主要用途是执行SQL Server的某些管理的某些管理功能、显示有关数据库和用户的信息。系统存储过程名以功能、显示有关数据库和用户的信息。系统存储过程名以SP_开头，可以在任何数据库中执行系统存储过程。开头，可以在任何数据库中执行系统存储过程。用户存储过程用户存储过程：用户自行创建并存储在用户数据库中的存：用户自行创建并存储在用户数据库中的存储过程。储过程。学校将在每个月的学校将在每个月的1号统计该月中过生日的同学，为

4、这些号统计该月中过生日的同学，为这些同学集中举行生日晚会，如何将本月过生日的同学学号，同学集中举行生日晚会，如何将本月过生日的同学学号，姓名，生日和系名显示出来？姓名，生日和系名显示出来？CREATE PROC prcGetStuInfoByBirthDateASBEGINDECLARE Today int SET Today=DAY(GETDATE()IF(Today=1)BEGIN SELECT StuID,StuName,StuBirthDate,DepName FROM Student JOIN Department ON Student.DepID=Department.DepID

5、WHERE MONTH(StuBirthDate)=MONTH(GETDATE()ENDEND语法：语法：CREATE PROCEDURE 存储过程名存储过程名参数参数 数据类型数据类型=默认值默认值,.n AS SQL语句语句 .n 用户输入：用户输入：A00005CREATE PROC prcGetNameAS SELECT StuName FROM Student WHERE StuID=StuID CREATE PROC prcGetName StuIDSELECT StuName FROM Student WHERE StuID=StuID declare char(5)ASStuI

6、D char(10)语法：语法：CREATE 输入参数输入参数PROCEDURE 存储过程名存储过程名参数参数 数据类型数据类型=默认值默认值,.nAS SQL语句语句 .n 用户输入：用户输入：A00005结论结论1：存储过程的输入参数是由用存储过程的输入参数是由用户需要提供的实际参数而决定的户需要提供的实际参数而决定的解决方案：解决方案：EXEC prcGetName A00005语法：语法：EXEC 存储过程名存储过程名 实参实参,n解决方案：解决方案：CREATE PROCAS使用存储过程实现由用户输入学生的学号及课程名称，根据输使用存储过程实现由用户输入学生的学号及课程名称，根据输入

7、的信息，显示相应的成绩，如果成绩大于等于入的信息，显示相应的成绩，如果成绩大于等于60分，则显示分，则显示pass，否则显示，否则显示failed。prcGetScoreStuID char(10),CourseName varchar(20)解决方案：解决方案：CREATE PROCAS使用存储过程实现由用户输入学生的学号及课程名称，根据输使用存储过程实现由用户输入学生的学号及课程名称，根据输入的信息，显示相应的成绩，如果成绩大于等于入的信息，显示相应的成绩，如果成绩大于等于60分，则显示分，则显示pass，否则显示，否则显示failed。prcGetScoreStuID char(10),

8、CourseName varchar(20)DECLARE Grade int SET Grade=0IF Grade 60 PRINT failedELSE PRINT passSELECT FROM ON WHERE Score SC JOIN Course SC.CourseID=Course.CourseID StuID=and CourseName=StuIDCourseNameGrade=用户输入：用户输入：EXEC prcGetScore A00001,DataBase用户输入用户输入1：EXEC prcGetScore A00001用户输入用户输入2：EXEC prcGetSc

9、ore DataBase,A00001结论结论2：调用存储过程时，用户输入的实参个数必须与输入参数个数一调用存储过程时，用户输入的实参个数必须与输入参数个数一致，且一一对应。致，且一一对应。使用存储过程实现由用户输入学生的学号及课程名称，根据使用存储过程实现由用户输入学生的学号及课程名称，根据输入的信息，显示相应的成绩，如果成绩小于输入的信息，显示相应的成绩，如果成绩小于60分，则向分，则向ReExam(StuID,CourseID,Score)表中插入一条记录。表中插入一条记录。解决方案：解决方案：CREATE PROC prcReExamStuID char(10),CourseName

10、varchar(20)ASDECLARE CourseID int SET CourseID=0SELECT CourseID=CourseID FROM Course WHERE CourseName=CourseNameIF Grade 60INSERT INTO ReExam VALUES(StuID,CourseID,Grade)假设用户输入的值违反约束性规则。假设用户输入的值违反约束性规则。BEGIN TRYSQL语句语句END TRYBEGIN CATCH错误处理语句错误处理语句END CATCHCREATE PROC prcReExamStuID char(10)，CourseN

11、ame varchar(20)AS BEGIN TRY DECLARE Grade int SET Grade=0 DECLARE CourseID int SET CourseID=0 SELECT Grade=Grade,CourseID=SC.CourseID FROM SC JOIN Course ON SC.CourseID=Course.CourseID WHERE StuID=StuID and CourseName=CourseName IF Grade 60 insert into ReExam values(StuID,CourseID,Grade)END TRYBEGIN

12、 CATCH PRINT 输入数据有误，学号以输入数据有误，学号以A,B或或Z打头，后跟打头，后跟5位数字位数字END CATCH假设用户输入一个假设用户输入一个不存在的课程名？不存在的课程名？IF EXISTS语句用来判断某条信息是否存在语句用来判断某条信息是否存在例如，查询例如，查询1号学生的成绩之前可以先判断号学生的成绩之前可以先判断1号学生是否存在，号学生是否存在，存在则查询，不存在提示用户该学生不存在。可写成：存在则查询，不存在提示用户该学生不存在。可写成：IF EXISTS(SELECT*FROM Student WHERE StuID=A00001)SELECT*FROM SC

13、WHERE StuID=A00001 and CourseID=1ELSEPRINT 该学生不存在！该学生不存在！CREATE PROC 存储过程名存储过程名参数名参数名 参数类型参数类型,nASBEGIN TRYIF EXISTS()BEGIN/执行功能执行功能ENDELSE/信息不存在信息不存在END TRYBEGIN CATCH/错误提示信息错误提示信息END CATCH请继续改进前页的程序！请继续改进前页的程序！exec prcReExam A00001,databaseexec prcReExam A00002,math可以在存储过程中使用可以在存储过程中使用RETURN语句返回一个

14、值语句返回一个值示例：写一存储过程由用户输入学号和课程号，返回学生考试成绩示例：写一存储过程由用户输入学号和课程号，返回学生考试成绩CREATE proc prcGetInfoStuID char(10),CourseID intAs IF EXISTS(SELECT*FROM SC WHERE StuID=StuID and CourseID=CourseID)BEGINDECLARE Grade intSELECT Grade=Grade FROM SC WHERE StuID=StuID and CourseID=CourseIDRETURN Grade ENDELSE RETURN 0

15、 用户输入：用户输入：declare ReturnValue intexec ReturnValue=prcIsCourseSelected A00001,1print ReturnValue注意：注意：RETURN只能返回整只能返回整数数示例：写一个存储过程，查找某个学生是否选修了某门课程，如果选修示例：写一个存储过程，查找某个学生是否选修了某门课程，如果选修了则返回了则返回1，否则返回，否则返回0，错误返回，错误返回-1.解决方案：解决方案：CREATE PROC prcIsCourseSelectedStuID int,CourseID intAS BEGIN TRY if exists

16、(select*from SC where StuID=StuID and CourseID=CourseID)return 1 else return 0 END TRY BEGIN CATCH return-1 END CATCH思考：如果根据课程号返回相应课思考：如果根据课程号返回相应课程名，该如何操作？程名，该如何操作？写一存储过程，根据不同的系名，查找该系的所有学生最高写一存储过程，根据不同的系名，查找该系的所有学生最高入学成绩，假设最高分大于等于入学成绩，假设最高分大于等于90分，则返回状态分，则返回状态1，否则，否则返回状态返回状态0，出错返回状态，出错返回状态-1,系名不存在返回状态系名不存在返回状态-2。用户调用该存储过程，返回值为用户调用该存储过程，返回值为1，显示存在学生成绩大于，显示存在学生成绩大于等于等于90，返回状态，返回状态0，显示不存在学生最高分大于等于，显示不存在学生最高分大于等于90，否则提示用户出错了。否则提示用户出错了。带有输入参数的存储过程带有输入参数的存储过程带有返回值的存储过程带有返回值的存储过程创建带有输入参数及返回值的存储过程创建带有输入参数及返回值的存储过程RETURN语句能返回整数，如要返回其他类型或返回多个语句能返回整数，如要返回其他类型或返回多个值，该如何解决？值，该如何解决？