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

存储过程的创建和使用

例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中查询以下字段：班级、学号、姓名、性别、课程名称、考试分

SQL Server 2005实用教程

数。

程序清单如下：

--打开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章存储过程的创建和使用

例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，如果执行出错，则返回错误号。

SQL Server 2005实用教程

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

或直接写存储过程的名称：

第9章存储过程的创建和使用

USE STUDENT

GO

StuInfo

例9-3-2：执行存储过程StuScoreInfo。

程序清单如下：

USE STUDENT

EXEC StuScoreInfo

例9-3-3：执行存储过程Stu_Info，该存储过程有一个输入参数“学号”，在执行时需要传入一个学号值。

程序清单如下：

USE STUDENT

SQL Server 2005实用教程

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

第9章存储过程的创建和使用

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

SQL Server 2005实用教程

例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'

第9章存储过程的创建和使用

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