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

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

例9-2-1：创建一个名称为“StuInfo”的存储过程，要求完成以下功能：在T_STUDENT表中查询05541班学生的学号、姓名、性别、出生日期和政治面貌五个字段的内容。
在创建存储过程的窗口中，单击“查询”菜单，选择“指定模板参数的值”，会弹出“指定模板参数的值”对话框。
在“指定模板参数的值”对话框中将“Procedure_Name”参数对应的名称修改为“StuInfo”，单击“确定”按钮，关闭此对话框。在创建存储过程的窗口中将对应的SELECT语句修改为以下程序代码：
Select S_NUMBER AS 学号,
S_NAME AS 姓名,
SEX AS 性别,
BIRTHDAY AS 出生日期,
POLITY AS 政治面貌
FROM T_STUDENT
WHERE LEFT(S_NUMBER,5)='05541'
输入完毕后，单击窗口工具栏上的“执行”按钮执行以上程序段，就会创建一个新的存储过程“StuInfo”。

例9-2-2：创建一个存储过程StuScoreInfo，完成的功能是在表T_STUDENT、表T_COURSE和表t_SCORE中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。
程序清单如下：
--打开STUDENT数据库
USE STUDENT
--查询是否已存在此存储过程，如果存在，就删除它
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'StuScoreInfo' AND type = 'P')
DROP PROCEDURE StuScoreInfo
GO
--创建存储过程
CREATE PROCEDURE StuScoreInfo
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 AS 性别,
T_COURSE.C_NAME AS 课程名称,
t_SCORE.SCORE AS 考试分数
FROM T_STUDENT,T_COURSE,t_SCORE
WHERE T_STUDENT.S_NUMBER=t_SCORE.S_NUMBER
AND T_COURSE.C_NUMBER=t_SCORE.C_NUMBER
GO

例9-2-3：创建一个带有参数的存储过程Stu_Info，该存储过程根据传入的学生编号，在T_STUDENT中查询此学生的信息。
程序清单如下：
--删除已存在的存储过程
USE STUDENT
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Stu_Info' AND type = 'P')
DROP PROCEDURE Stu_Info
GO
--创建存储过程
USE STUDENT
GO
CREATE 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 AS 性别,
BIRTHDAY AS 出生日期,
POLITY AS 政治面貌
FROM T_STUDENT
WHERE S_NUMBER=@S_NUMBER
GO

例9-2-4：创建一个带有参数的存储过程Stu_Age，该存储过程根据传入的学生编号，在T_STUDENT中计算此学生的年龄，并根据程序的执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。
USE STUDENT
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Stu_Age' AND type = 'P')
DROP PROCEDURE Stu_Age
GO
--创建存储过程
USE STUDENT
GO
CREATE PROCEDURE Stu_Age
@S_NUMBER varchar(10),
@Age int OUTPUT
AS
--定义并初始化局部变量，用于保存返回值
DECLARE @ErrorValue int
SET @ErrorValue=0
--求此学生的年龄
SELECT @Age=YEAR(GETDATE())-YEAR(BIRTHDAY)
FROM T_STUDENT
WHERE S_NUMBER=@S_NUMBER
--根据程序的执行结果返回不同的值
IF (@@ERROR<>0)
SET @ErrorValue=@@ERROR
RETURN @ErrorValue
GO

例9-3-1：执行前面创建的StuInfo存储过程，它是一个无参的存储过程。
程序清单如下：
USE STUDENT
EXEC StuInfo
或直接写存储过程的名称：
USE STUDENT
GO
StuInfo

例9-3-2：执行存储过程StuScoreInfo。
程序清单如下：
USE STUDENT
EXEC StuScoreInfo

例9-3-3：执行存储过程Stu_Info，该存储过程有一个输入参数“学号”，在执行时需要传入一个学号值。
程序清单如下：
USE STUDENT
GO
EXECUTE Stu_Info '0554101'
或：
USE STUDENT
GO
EXECUTE Stu_Info @S_NUMBER='0554101'

例9-3-4：执行存储过程Stu_Age，该存储过程有一个输入参数“学号”，另外，还有一个输出参数@Age。存储过程执行完后，有一个返回的状态值，这个值可以从变量@ErrorValue得到。
程序清单如下：
USE STUDENT
GO
DECLARE @stuAge int,
@ReturnValue int,
@S_NUMBER char(10)
SET @S_NUMBER='0554101'
EXECUTE @ReturnValue=Stu_Age @S_NUMBER,@stuAge OUTPUT
PRINT '本程序的执行结果：'
PRINT '程序的返回值='+CAST(@ReturnValue AS char(2))
PRINT '学号为"'+RTRIM(@S_NUMBER)+'"的学生的年龄是'
+CAST(@stuAge AS char(2))+'岁。'
或：
USE STUDENT
GO
DECLARE @stuAge int,
@ReturnValue int,
@S_NUMBER1 char(10)
SET @S_NUMBER1='0554101'
EXECUTE @ReturnValue=Stu_Age @S_NUMBER=@S_NUMBER1,
@Age=@stuAge OUTPUT
PRINT '本程序的执行结果：'
PRINT '程序的返回值='+CAST(@ReturnValue AS char(2))
PRINT '学号为"'+RTRIM(@S_NUMBER1)+'"的学生的年龄是'
+CAST(@stuAge AS char(2))+'岁。'

例9-4-1：使用系统存储过程查看Stu_Age存储过程的参数及其数据类型。
程序清单如下：
--查看参数及其数据类型
USE STUDENT
GO
sp_help Stu_Age
GO

例9-4-2：使用系统存储过程查看StuScoreInfo存储过程的相关的数据库对象。
程序清单如下：
--查看相关的数据库对象
USE STUDENT
GO
sp_depends StuScoreInfo
GO

例9-4-3：修改前面创建的Stu_Info存储过程，使之完成以下功能：根据传入的学号，在表T_STUDENT、表T_COURSE和表t_SCORE中查询此学生的班级、学号、姓名、性别、考试课程名称和考试分数。
程序清单如下：
--修改存储过程
USE STUDENT
GO
ALTER 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 AS 性别,
T_COURSE.C_NAME AS 课程名称,
SCORE AS 考试成绩
FROM T_STUDENT,T_COURSE,t_SCORE
WHERE T_STUDENT.S_NUMBER=@S_NUMBER
AND T_STUDENT.S_NUMBER=t_SCORE.S_NUMBER
AND T_COURSE.C_NUMBER=t_SCORE.C_NUMBER
GO
执行修改后的Stu_Info存储过程：
USE STUDENT
GO
EXEC Stu_Info '0554101'
GO

例9-5-1：使用系统存储过程将Stu_Info存储过程的名称修改为Stu_Info_SCORE。
程序清单如下：
USE STUDENT
GO
Sp_rename Stu_Info, Stu_Info_SCORE
GO

例9-5-2：使用DROP命令删除StuInfo和StuScoreInfo两个存储过程。
程序清单如下
USE STUDENT
GO
DROP procedure StuInfo, StuScoreInfo
GO