第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

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