【SQL Server】存储过程的创建和使用

主要内容

        1．编写带输入参数的存储过程proc_St

        2．执行带输入参数的存储过程proc_St

        3．编写并执行带输出参数的存储过程proc_STAVG

        4．编写3个带输入参数的存储过程并分别执行

        5.  用系统存储过程查看刚创建的存储过程的信息、查看存储过程定义的脚本

        6.  练习修改存储过程和删除存储过程的语句

---

（1）编写带输入参数的存储过程proc_St：

打开SMSS，继续使用在实验6中创建的S_C_SC数据库；

打开“新建查询”窗口，输入以下T-SQL语句进行存储过程proc_St的创建；

CREATE PROC proc_St @SSname VARCHAR(20),@SSno VARCHAR(9)

AS
   BEGIN
         IF NOT EXISTS (SELECT * FROM Student WHERE Sname = @SSname AND Sno=@SSno)
             BEGIN
                  PRINT '没有该学生！'
                  RETURN
             END
         ELSE
             BEGIN
                  SELECT Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student,SC
                  WHERE Student.Sno=SC.Sno and Student.Sno=@SSno
                  RETURN
             END
   END

运行后发现系统报对象名（表名）无效，核对后发现所选择的数据库不准确，按照以下操作将数据库从master更改为S_C_SC；

此时T-SQL语句便可正常运行，但是发现有一处Sno报不明确；

检查后发现引用的两个表（Student、SC）中均有Sno，故将此处的Sno明确为Student.Sno即可

执行后顺利创建出名为proc_St的存储过程；

（2）执行带输入参数的存储过程proc_St：

打开“新建查询”窗口，分别输入下列T-SQL语句进行存储过程proc_St的调用；

要求：如果学生选课数据库中存在所指定的学号和姓名的学生，则返回这个学生的信息及选课信息；否则给出“没有该学生”的提示信息；

EXEC proc_St "浦飞白" ,"202008"
EXEC proc_St "唐黄" ,"201908"

我提供的数据库中存在浦飞白，但没有唐黄，功能顺利实现。

（3）编写并执行带输出参数的存储过程proc_STAVG：

打开“新建查询”窗口，输入以下T-SQL语句进行存储过程proc_STAVG的创建；

CREATE PROC proc_STAVG @SSage SMALLINT OUTPUT
AS
    SET @SSage = (SELECT AVG(Sage) FROM Student)
    PRINT @SSage

可以看见已成功创建；

下面使用T-SQL语句进行该存储过程的调用；

DECLARE @SSage SMALLINT
EXEC proc_STAVG @SSage OUTPUT

经验证，输出的平均年龄无误；

（4）编写3个带输入参数的存储过程并分别执行：

(a).insert_Student

T-SQL语句:

CREATE PROCEDURE insert_Student @SSno CHAR(9),@SSname CHAR(20),@SSex CHAR(2),@SSage SMALLINT,@SSdept CHAR(20)
AS
BEGIN
    IF EXISTS (SELECT * FROM Student WHERE Sno=@SSno and Sname = @SSname)
             BEGIN
                  PRINT '该学生已存在！'
                  RETURN
             END
             ELSE
             BEGIN
                  INSERT
                  INTO Student (Sno,Sname,Ssex,Sdept,Sage)
                  VALUES(@SSno,@SSname,@SSex,@SSdept,@SSage)   
                  RETURN
             END
END

成功创建，下面执行——插入一条学生唐黄的信息；

T-SQL语句：

DECLARE @SSno CHAR(9),@SSname CHAR(20),@SSex CHAR(2),@SSage SMALLINT,@SSdept CHAR(20)
EXEC insert_Student @SSno = '201908',@SSname = '唐黄',@SSex = '男',@SSdept = 'CS',@SSage = 21

Student表前后对照：

 

成功插入；

(b).update_Student

T-SQL语句:

CREATE PROCEDURE  update_Student @SSno CHAR(9),@SSname CHAR(20),@SSex CHAR(2),@SSage SMALLINT,@SSdept CHAR(20)
AS
BEGIN
    update Student
    SET Sname = @SSname,Ssex = @SSex,Sage = @SSage,Sdept = @SSdept
    WHERE Sno = @SSno
END

成功创建，下面执行——更新学生唐黄的信息；

T-SQL语句：

DECLARE  @SSno CHAR(9),@SSname CHAR(20),@SSex CHAR(2),@SSage SMALLINT,@SSdept CHAR(20)
EXEC update_Student @SSno='201908',@SSname='唐黄',@SSex='男',@SSdept='IS',@SSage=20

“唐黄”信息前后对照（修改年龄为20，专业为IS）：

成功更新；

(c).delete_Student

T-SQL语句:

CREATE PROCEDURE  delete_Student @SSno CHAR(9)
AS
BEGIN
    DELETE
    FROM Student
    WHERE Sno = @SSno                                  
END

成功创建，下面执行——删除学生唐黄的信息；

T-SQL语句：

DECLARE  @SSno CHAR(9)
EXEC delete_Student @SSno = '201908'

Student表前后对照：

 

（4）用系统存储过程查看刚创建的存储过程的信息、查看存储过程定义的脚本：

T-SQL语句:

SP_HELP proc_St

SP_HELPTEXT insert_Student

（5）练习修改存储过程和删除存储过程的语句：

修改存储过程T-SQL语句:

ALTER PROCEDURE insert_Student(@SSname VARCHAR(50))
AS
SELECT * FROM Student WHERE Sname = @SSname

存储过程insert_Student前后对照（基于SP_HELP）：

 

删除存储过程T-SQL语句:

DROP PROC insert_Student

 

已成功删除。

---

总结

本文主要进行了存储过程的设计、创建、使用、修改、删除等操作，详细展示了这一流程。