第09章 存储过程的创建和使用——例题解答

1、第9章 存储过程的创建和使用例题解答例9-2-1：创建一个名称为“StuInfo”的存储过程，要求完成以下功能：在T_STUDENT表中查询05541班学生的学号、姓名、性别、出生日期和政治面貌五个字段的内容。在创建存储过程的窗口中，单击“查询”菜单，选择“指定模板参数的值”，会弹出“指定模板参数的值”对话框。在“指定模板参数的值”对话框中将“Procedure_Name”参数对应的名称修改为“StuInfo”，单击“确定”按钮，关闭此对话框。在创建存储过程的窗口中将对应的SELECT语句修改为以下程序代码：Select S_NUMBER AS 学号, S_NAME AS 姓名, SEX AS

2、 性别, BIRTHDAY AS 出生日期, POLITY AS 政治面貌FROM T_STUDENTWHERE LEFT(S_NUMBER,5)='05541'输入完毕后，单击窗口工具栏上的“执行”按钮执行以上程序段，就会创建一个新的存储过程“StuInfo”。例9-2-2：创建一个存储过程StuScoreInfo，完成的功能是在表T_STUDENT、表T_COURSE和表t_SCORE中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。程序清单如下：-打开STUDENT数据库USE STUDENT-查询是否已存在此存储过程，如果存在，就删除它IF EXISTS (S

3、ELECT name FROM sysobjects WHERE name = 'StuScoreInfo' AND type = 'P') DROP PROCEDURE StuScoreInfoGO-创建存储过程CREATE PROCEDURE StuScoreInfo ASSelect 班级=SUBSTRING(T_STUDENT.S_NUMBER,1,LEN(T_STUDENT.S_NUMBER)-2), 学号=SUBSTRING(T_STUDENT.S_NUMBER,LEN(T_STUDENT.S_NUMBER)-1,2), S_NAME AS 姓名,

4、SEX AS 性别, T_COURSE.C_NAME AS 课程名称, t_SCORE.SCORE AS 考试分数FROM T_STUDENT,T_COURSE,t_SCOREWHERE T_STUDENT.S_NUMBER=t_SCORE.S_NUMBERAND T_COURSE.C_NUMBER=t_SCORE.C_NUMBERGO例9-2-3：创建一个带有参数的存储过程Stu_Info，该存储过程根据传入的学生编号，在T_STUDENT中查询此学生的信息。程序清单如下：-删除已存在的存储过程USE STUDENTIF EXISTS (SELECT name FROM sysobjects

5、 WHERE name = 'Stu_Info' AND type = 'P') DROP PROCEDURE Stu_InfoGO-创建存储过程USE STUDENTGOCREATE PROCEDURE Stu_Info S_NUMBER varchar(10)AS Select 班级=SUBSTRING(T_STUDENT.S_NUMBER,1,LEN(T_STUDENT.S_NUMBER)-2), 学号=SUBSTRING(T_STUDENT.S_NUMBER,LEN(T_STUDENT.S_NUMBER)-1,2), S_NAME AS 姓名, SEX

6、AS 性别, BIRTHDAY AS 出生日期, POLITY AS 政治面貌FROM T_STUDENTWHERE S_NUMBER=S_NUMBERGO例9-2-4：创建一个带有参数的存储过程Stu_Age，该存储过程根据传入的学生编号，在T_STUDENT中计算此学生的年龄，并根据程序的执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。USE STUDENTIF EXISTS (SELECT name FROM sysobjects WHERE name = 'Stu_Age' AND type = 'P') DROP PROCE

7、DURE Stu_AgeGO-创建存储过程USE STUDENTGOCREATE PROCEDURE Stu_Age S_NUMBER varchar(10), Age int OUTPUTAS -定义并初始化局部变量，用于保存返回值DECLARE ErrorValue intSET ErrorValue=0-求此学生的年龄SELECT Age=YEAR(GETDATE()-YEAR(BIRTHDAY) FROM T_STUDENTWHERE S_NUMBER=S_NUMBER-根据程序的执行结果返回不同的值IF (ERROR<>0) SET ErrorValue=ERRORRET

8、URN ErrorValueGO例9-3-1：执行前面创建的StuInfo存储过程，它是一个无参的存储过程。程序清单如下：USE STUDENTEXEC StuInfo或直接写存储过程的名称：USE STUDENTGOStuInfo例9-3-2：执行存储过程StuScoreInfo。程序清单如下：USE STUDENTEXEC StuScoreInfo例9-3-3：执行存储过程Stu_Info，该存储过程有一个输入参数“学号”，在执行时需要传入一个学号值。程序清单如下：USE STUDENTGOEXECUTE Stu_Info '0554101'或：USE STUDENTGOE

9、XECUTE Stu_Info S_NUMBER='0554101'例9-3-4：执行存储过程Stu_Age，该存储过程有一个输入参数“学号”，另外，还有一个输出参数Age。存储过程执行完后，有一个返回的状态值，这个值可以从变量ErrorValue得到。程序清单如下：USE STUDENTGODECLARE stuAge int, ReturnValue int, S_NUMBER char(10)SET S_NUMBER='0554101'EXECUTE ReturnValue=Stu_Age S_NUMBER,stuAge OUTPUTPRINT '

10、本程序的执行结果：'PRINT '程序的返回值='+CAST(ReturnValue AS char(2)PRINT '学号为"'+RTRIM(S_NUMBER)+'"的学生的年龄是'+CAST(stuAge AS char(2)+'岁。'或：USE STUDENTGODECLARE stuAge int, ReturnValue int, S_NUMBER1 char(10)SET S_NUMBER1='0554101'EXECUTE ReturnValue=Stu_Age S_NUM

11、BER=S_NUMBER1,Age=stuAge OUTPUTPRINT '本程序的执行结果：'PRINT '程序的返回值='+CAST(ReturnValue AS char(2)PRINT '学号为"'+RTRIM(S_NUMBER1)+'"的学生的年龄是' +CAST(stuAge AS char(2)+'岁。'例9-4-1：使用系统存储过程查看Stu_Age存储过程的参数及其数据类型。程序清单如下：-查看参数及其数据类型USE STUDENTGOsp_help Stu_AgeGO例9-4

12、-2：使用系统存储过程查看StuScoreInfo存储过程的相关的数据库对象。程序清单如下：-查看相关的数据库对象USE STUDENTGOsp_depends StuScoreInfoGO例9-4-3：修改前面创建的Stu_Info存储过程，使之完成以下功能：根据传入的学号，在表T_STUDENT、表T_COURSE和表t_SCORE中查询此学生的班级、学号、姓名、性别、考试课程名称和考试分数。程序清单如下：-修改存储过程USE STUDENTGOALTER PROCEDURE Stu_Info S_NUMBER varchar(10)AS Select 班级=SUBSTRING(T_STU

13、DENT.S_NUMBER,1,LEN(T_STUDENT.S_NUMBER)-2), 学号=SUBSTRING(T_STUDENT.S_NUMBER,LEN(T_STUDENT.S_NUMBER)-1,2), S_NAME AS 姓名, SEX AS 性别, T_COURSE.C_NAME AS 课程名称, SCORE AS 考试成绩FROM T_STUDENT,T_COURSE,t_SCOREWHERE T_STUDENT.S_NUMBER=S_NUMBERAND T_STUDENT.S_NUMBER=t_SCORE.S_NUMBERAND T_COURSE.C_NUMBER=t_SCORE.C_NUMBERGO执行修改后的Stu_Info存储过程：USE STUDENTGOEXEC Stu_Info '0554101'