《数据库》课件第7章-存储过程-输入参数.ppt

1、声明变量系名，为系名赋值声明变量系名，为系名赋值“computer”，查找该系的平，查找该系的平均年龄，赋值给变量均年龄，赋值给变量avgStuAge，并显示变量的值。，并显示变量的值。要查询要查询1号学生对应的姓名。号学生对应的姓名。解决方案：解决方案：SELECT StuName FROM Student WHERE StuID=A00001 要查询要查询2号学生对应的姓名。号学生对应的姓名。解决方案：解决方案：SELECT StuName FROM Student WHERE StuID=A00002 要查询要查询3号直至号直至n号学生对应的姓名。号学生对应的姓名。解决方案：解决方案：S

2、ELECT StuName FROM Student WHERE StuID=？SELECT StuName FROM Student WHERE StuID=StuID 用户输入：用户输入：A00005Student(StuID,StuName,StuAge,StuSex,StuScore,DepID)第七章第七章存储过程存储过程-输入参数输入参数存储过程的概念存储过程的概念创建带有参数的存储过程创建带有参数的存储过程(输入参数输入参数)存储过程的调用存储过程的调用 存储过程（存储过程（Stored Procedure）是在大型数据库系）是在大型数据库系统中，一组为了完成特定功能的统中，一组

3、为了完成特定功能的SQL 语句集，经编译后存语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。（如果该存储过程带有参数）来执行它。系统存储过程系统存储过程:SQL Server内置的存储过程，存储在内置的存储过程，存储在master库中，主要用途是执行库中，主要用途是执行SQL Server的某些管理的某些管理功能、显示有关数据库和用户的信息。系统存储过程名以功能、显示有关数据库和用户的信息。系统存储过程名以SP_开头，可以在任何数据库中执行系统存储过程。开头，可以在任何数据库中执行系统存储

4、过程。用户存储过程用户存储过程：用户自行创建并存储在用户数据库中的存：用户自行创建并存储在用户数据库中的存储过程。储过程。语法：语法：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

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

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

7、tuID char(10),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

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

9、0),CourseName 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 ch

10、ar(10)，CourseName varchar(20)AS BEGIN TRY DECLARE Grade int SET Grade=0 DECLARE CourseID int SET CourseID=0 SELECT Grade=Score,CourseID=SC.CourseID FROM SC JOIN Course ON SC.CourseID=Course.CourseID WHERE StuID=StuID and CourseName=CourseName IF Grade=60BEGIN SELECT Credit=Credit FROM Course WHERE C

11、ourseID=CourseID RETURN Credit END ELSE RETURN 0 ENDELSE RETURN-1 用户输入：用户输入：declare ReturnValue intexec ReturnValue=prcGetCreditInfo A00001,1print ReturnValue注意：注意：RETURN只能返回整数只能返回整数示例：写一个存储过程，查找某个学生是否选修了某门课程，如果选修示例：写一个存储过程，查找某个学生是否选修了某门课程，如果选修了则返回了则返回1，否则返回，否则返回0，错误返回，错误返回-1.解决方案：解决方案：CREATE PROC p

12、rcIsCourseSelectedStuID char(10),CourseID intAS BEGIN TRY if exists(select*from SC where StuID=StuID and CourseID=CourseID)return 1 else return 0 END TRY BEGIN CATCH return-1 END CATCH思考：如果根据课程号返回相应课思考：如果根据课程号返回相应课程名，该如何操作？程名，该如何操作？写一存储过程，根据不同的系名，查找该系的所有学生最高写一存储过程，根据不同的系名，查找该系的所有学生最高入学成绩，假设最高分大于等于入学

13、成绩，假设最高分大于等于90分，则返回状态分，则返回状态1，否则，否则返回状态返回状态0，出错返回状态，出错返回状态-1,系名不存在返回状态系名不存在返回状态-2。用户调用该存储过程，返回值为用户调用该存储过程，返回值为1，显示存在学生成绩大于，显示存在学生成绩大于等于等于90，返回状态，返回状态0，显示不存在学生最高分大于等于，显示不存在学生最高分大于等于90，否则提示用户出错了。否则提示用户出错了。带有输入参数的存储过程带有输入参数的存储过程带有返回值的存储过程带有返回值的存储过程创建带有输入参数及返回值的存储过程创建带有输入参数及返回值的存储过程RETURN语句能返回整数，如要返回其他类型或返回多个语句能返回整数，如要返回其他类型或返回多个值，该如何解决？值，该如何解决？