任务4 修改存储过程

一 、在SSMS中修改存储过程

存储过程是一段T-SQL代码，在使用过程中，如果它无法满足业务需要，就要对存储过程进行修改，修改存储过程通常指编辑它的参数和SQL语句。修改存储过程可以通过SSMS和T-SQL语句两种方式。

在SSMS中修改存储过程和创建存储过程有些类似。只要在“对象资源管理器”中找到所要修改的存储过程右击，在弹出的快捷菜单中选择“修改(Y)”命令，出现“修改存储过程”的模板界面，在代码区中修改好存储过程的内容，最后单击工具栏中的“执行”按钮，即可完成对存储过程的修改。

二

二、使用T-SQL语句修改存储过程

除了使用SSMS外，还可以使用ALTER PROCEDURE语句修改存储过程，但该语句不会更改权限，也不影响相关的存储过程。其语法格式如下：

ALTER PROC  [  EDURE  ]  procedure_name  [;number]

[  { @parameter data_type}

[   VARYING]  [=default]  [OUTPUT]]  [,…n]

[   WITH{RECOMPILE | ENCRYPTION | RECOMPILE,  ENCRYPTION}]

[ FOR REPLICATION

AS

sql_statement  [, . . .n]

ALTER PROCEDURE语句中各参数的含义与CREATE PROCEDURE的参数类似。

存储过程在建立后，可对存储过程的定义进行修改或重新命名。

  任务   删除存储过

     当不再使用一个存储过程时，就要把它从数据库中删除。SQL Server 2008提供两种删除存储过程的方法：一种是使用SSMS，另一种是使用T-SQL语句。

一 、 使用SSMS删除存储过程

     SSMS删除存储过程非常简单。在“对象资源管理器”中找到所要修改的存储过程右击，在弹出的快捷菜单中选择“删除(D)”命令，弹出“删除对象”对话框，单击“确定”按钮，即可完成存储过程的删除。

二 、使用T-SQL语句删除存储过程

      T-SQL语言删除存储过程的语句是DROP PROCEDURE，其语法格式如下：

DROP  {PROC | PROCEDURE}  {  [schema_name.  ]  procedure}  [,…n]

语法参数的含义如下。

Schema_name:存储过程所属架构的名称，不能指定服务器名称或数据库名称。

Procedure:要删除的存储过程或存储过程组的名称。

n：表示可以指定多个过程的占位符。

用DROP PROC命令删除存储过程

提高：SQL Server  存储过程经验

•

存储过程是存储在数据库中的T-SQL代码，可以被应用程序多次调用。

存储过程可以接受输入参数。存储过程中的处理结果可以通过输出参数获得。

参数化的存储过程可以提高系统安全性，有助于保护应用程序不受SQL Injection攻击。

在开发基于数据库的应用程序时，建议考虑将各种T-SQL代码放在存储过程中，这样可有效减少网络数据流量。

要点小结：

存储过程的概念及其优点

创建和调用存储过程

修改存储过程

删除存储过程

重新编译存储过程

•

一、简答题

1、简述存储过程的分类。

2、简述存储过程的优点。

二、上机实践题

1.创建存储过程P_LEND1,能根据给定的读者ID，查询该读者的借阅情况，包括读者ID，借阅图书的书名，借阅日期，归还日期等信息。

2.创建存储过程P_LEND2,能根据给定的读者ID，返回该读者借书的数量。

3.创建存储过程P_LEND3,统计BookInfo(图书信息表)每一类图书的本数。

4.调试、创建示例存储过程。

--5. 创建存储过程，根据给定的课程号，打印该课程的成绩清单（DB： [Teaching] ）

USE  [Teaching] 

GO

CREATE PROC PrintScore

@COUNO VARCHAR(6)

AS

DECLARE @CNO VARCHAR(5),@COUNAME VARCHAR(20),@SNO VARCHAR(6),@SNAME VARCHAR(8),@SCORE SMALLINT

DECLARE Cr1 CURSOR  FOR

SELECT C.CNO,CNAME,S.SNO,SNAME,SCORE

FROM S,C,SC

WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SC.CNO=@COUNO

OPEN Cr1

PRINT '--------------------成绩清单-----------------'

PRINT  '课程号    课程名     学号     姓名      成绩'

FETCH NEXT FROM Cr1 INTO @CNO,@COUNAME,@SNO,@SNAME,@SCORE

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @CNO++SPACE(5)+CONVERT(CHAR(6),@COUNAME)+SPACE(5)+@SNO+SPACE(3)+CONVERT(CHAR(8),@SNAME)+SPACE(3)+CONVERT(CHAR(3),@SCORE)

FETCH NEXT  FROM Cr1 INTO @CNO,@COUNAME,@SNO,@SNAME,@SCORE

END

CLOSE Cr1

DEALLOCATE Cr1

--应用示例

DECLARE @RC int

DECLARE @COUNO varchar(6)

-- TODO: 在此处设置参数值。

EXECUTE @RC = [Teaching].[dbo].[PrintScore]     'C1'

--应用示例　输出多门课程成绩表

DECLARE @RC int,@C VARchar(6),@K int

DECLARE @COUNO varchar(6)

-- TODO: 在此处设置参数值。

SET  @k=1

WHILE  @K<=8

BEGIN

SET @c='C'+STR(@k,1)

EXECUTE @RC = [Teaching].[dbo].[PrintScore]     @C

SET @K=@K+1

END