任务10.2 创建和执行存储过程

在SQL Server 中，创建一个存储过程有两种方法：

一是使用T-SQL语句CREATEPROCEDURE，

二是使用SSMS图形工具界面。

一、 使用T-SQL创建存储过程

创建存储过程需要用CREATE PROCEDURE语句，在学习具体的创建方法之前，先来了解一下创建T-SQL存储过程的规则。

(1)在设计和创建存储过程时，应该满足一定的约束和规则。只有满足了这些约束和规则才能创建有效的存储过程。

(2) CREATE PROCEDURE定义自身可以包括任意数量和类型的T-SQL语句，但下列语句除外：

CREATE AGGREGATE、CREATE SCHEMA、

CREATE DEFAULE、CREATE RULE、

CREATE或ALTER PROCEDURE、

CREATE或ALTER TRIGGER、

CREATE或ALTERVIEW、

CREATE或ALTER FUNCTION、

SET PARSEONLY、SET SHOWPLAN_ALL、

SET SHOWPLAN_TEXT、

SET SHOWPLAN_XML和USE database_nake。

(3)可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可。

(4)可以在存储过程内引用临时表。

(5)如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在；退出该存储过程后，临时表将消失。

(6)如果执行的存储过程将调用另一个存储过程，则被调用的存储过程可以访问由第一个存储过程创建的所有对象，包括临时表在内。

(7)如果执行对远程SQL Server 2008实例进行更改的远程存储过程，则不能回滚这些更改，而且远程存储过程不参与事务处理。

(8)存储过程中的参数的最大数目为2100。

(9)存储过程中的局部变量的最大数目仅受可用内存的限制。

(10)根据可用内存的不同，存储过程最大可达128MB。

语法格式：

CREATE PROC 存储过程名

[@输入参数 数据类型[=默认值],……，@输出参数 数据类型 OUTPUT]

[WITH ENCRYPTION]

[WITH RECOMPILE]

AS

过程体

例：对[教务数据库] ，创建存储过程[查询成绩]，输出：学号,姓名，课程号,课程名,成绩, 任课教师姓名,任课教师号

USE  [教务数据库]

GO 

CREATE  PROCEDURE  查询成绩

 AS 

   SELECT G.学号,S.姓名,G.课程号,K.课程名,G.成绩,T.姓名,G.任课教师号

  FROM  [成绩表]  G,[学生表]  S,[课程表]  K,教师表 T

    WHERE  G.学号=S.学号 AND  G.课程号=K.课程号  AND  G.任课教师号=T.教师号

例： 执行存储过程

EXEC   查询成绩

结果（略）

思考：若要求输出成绩在85分以上的，如何修改？   

利用系统存储过程，查阅存储过程内容： 

 sp_helptext  存储过程名

例：sp_helptext 查询成绩

任务 执存储行 过程

存储过程创建完后，要产生效果，必须执行。

在SQL Server 中创建执行存储过程当需要执行存储过程时，可以使用T-SQL语句EXECUTE。如果存储过程是批处理中的第一条语句，那么不使用EXECUTE关键字也可以执行该存储过程，其语法格式如下：

Execute a stored procedure or function    [ { EXE | EXECUTE)}]    {    [ @return_status=  ]    {  module_name  [;number] | @module_name_var  }    [[@parameter=]  {  value | @variable  [OUTPUT  ] |  [  DEFAULT ]    ][，…n]    [  WITH RECOMPILE]    }    [；]

常用格式：

EXEC PROC 存储过程名

[[@输入参数=]值,……，@对应输出参数的变量 OUTPUT]

例：创建存储过程 GetBookCategory

USE Library

GO

CREATE PROCEDURE GetBookCategory

 AS 

SELECT 类编号, 类名称

FROM BookCategory

执行已创建的存储过程

要使用EXECUTE (或简写EXEC) 命令 ，如下图所示。

单击工具栏中【执行】按钮，创建该存储过程。存储过程创建成功后，执行存储过程：　　EXECUTE GetBookCategory

例：不带参数的存储过程

【例】在“学生成绩管理系统”的[教务数据库]中创建一个存储过程，该存储过程返回‘19软件’专业的学生信息。该存储过程不使用任何参数，可在【查询编辑器】窗口中输入如下代码：

USE [教务数据库]

GO

CREATE PROCEDURE GetStuInfo

AS

SELECT * FROM  学生表

WHERE 专业班级LIKE '%19软件%'

单击工具栏中【执行】按钮，创建该存储过程。存储过程创建成功后，执行存储过程：

　　EXECUTE  GetStuInfo

　　　执行结果如图所示：

二、 使用SSMS执行存储过程

     除了使用EXEC语句执行存储过程，使用SSMS图形工具界面也可以来执行存储过程。

加密存储过程

可使用WITH ENCRYPTION选项对存储过程进行加密，对用户隐藏存储过程的脚本。

　　　【例】在“学生成绩管理系统”的[教务数据库]中创建一个加密存储过程，该存储过程可获取所有学生信息。可在【查询编辑器】窗口中输入如下代码并执行：

　　USE [教务数据库]

　　GO

　　CREATE PROCEDURE GetInfo 

　　WITH ENCRYPTION

　　AS

          SELECT * FROM  学生表

存储过程加密后，将无法直接查看其脚本，如使用系统存储过程sp_helpext查看：

　　EXEC sp_helptext GetInfo

　　查看的结果是显示提示信息“对象 ‘GetInfo’ 的文本已加密。”。

存储过程加密后可用不可看存储过程脚本（程序）

    EXEC GetInfo