数据库复习（九）——存储过程

 

9.1  游标的使用

       使用游标（CURSOR）在需要一行一行处理时，游标十分有用。游标可以打开一个结果集合（按照指定的标准选择的行），并提供在结果集中一行一行处理的功能。基于游标的类型，可以对其进行回滚或者前进。

一．游标的声明   

用DECLARE语句对游标进行声明，有两种方法可以指定一个游标。

SQL-92 语法

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FOR select_statement

[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

Transact-SQL 扩展语法

DECLARE cursor_name CURSOR

[ LOCAL | GLOBAL ]

[ FORWARD_ONLY | SCROLL ]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ TYPE_WARNING ]

FOR select_statement

[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

 

例1：定义一个游标，用于生成学生信息表中的所有记录。

DECLARE student_cursor CURSOR

    FOR SELECT * FROM student

二．打开游标  

   打开游标就是创建结果集，执行游标定义时指定的 Transact-SQL 语句填充游标。语法如下：

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }

 

例2：定义一个游标，用于显示学号为“110101”的学生选修课的详细情况，并打开游标。

DECLARE choice_Cursor CURSOR FOR

SELECT *

FROM choice

WHERE sno=’110101’

OPEN choice_Cursor

三．使用游标读取数据  

    在从游标中读取数据的过程中，可以在结果集中的每一行上来回移动和处理。如果游标定义成了可滚动的（在声明时使用SCROLL关键字），则任何时候都可取出结果集中的任意行。对于非滚动的游标，只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下：

FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]

FROM [GLOBAL] cursor_name} | cursor_variable_name}

[INTO @variable_name ][,……n]]

 

例3：定义一个游标，返回学生信息表中所有的数据，打开游标，然后遍历学生信息表，直到找到学生名称为“张三丰”的记录为止，并且打印学生学号和学生名称。

DECLARE student_cursor CURSOR

FOR SELECT sno,sname FROM student

        DECLARE @ID char(10), @Name char(30)

        OPEN student_cursor

        FETCH NEXT FROM student_cursor INTO @ID, @Name

        WHILE @@fetch_status = 0

BEGIN

IF @Name = '张三丰'

BEGIN

            PRINT '找到张三丰'

            PRINT @ID+@Name

            BREAK

END

FETCH NEXT FROM student_cursor INTO @ID, @Name

END

四、关闭和释放游标

    游标使用之后，要及时对它进行关闭和释放操作。CLOSE语句用来关闭游标并释放结果集。游标关闭之后，不能再执行FETCH操作。如果还需要使用FETCH语句，则要重新打开游标。语法如下：

CLOSE [GLOBAL] cursor_name | cursor_variable_name

游标确实不再需要之后，要释放游标。DEALLOCATE语句释放数据结构和游标所加的锁。语法如下：

DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name

 

例4：

    以例3为基础，要体现一个完整的使用游标的过程，在最后还应包括以下两句，对游标进行关闭和释放：

    CLOSE student_cursor

    DEALLOCATE student_cursor

9.2 存储过程的创建与管理

存储过程是一段在服务器上执行的程序，它在服务器端对数据库记录进行处理，再把结果返回到客户端。通过使用存储过程，一方面可以利用服务器强大的计算能力和速度，另一方面避免把大量的数据从服务器下载到客户端，减少网络上传输量，服务器只需将计算结果传给客户端，因此可以提高客户端的工作效率。

包括系统存储过程和用户存储过程

系统存储过程中又分为一般系统存储过程和扩展存储过程。

一、使用存储过程的原因

存储过程的主要优点是可以提供对数据的轻松访问，客户端不需要知道复杂的数据结构或业务逻辑过程，只需要把查询指令通过存储过程发给服务器，服务器就能把需要的数据返回给用户。

二、使用系统存储过程管理SQL SERVER

存储过程分为两类，系统存储过程和用户自定义存储过程，SQL SERVER提供了大量的系统存储过程，用于管理SQL SERVER并显示有关数据库和用户的信息。

 

例5：用系统存储过程列出当前SQL SERVER实例中的所有数据库以及数据库的大小，用sp_databases。

    EXEC sp_databases

例6：用系统存储过程列出当前数据中所有可以访问的表，用sp_tables

    EXEC sp_tables

三、使用扩展存储过程

扩展存储过程是系统存储过程的一种，提供从 SQL Server 到外部程序的接口，以便进行各种维护活动。扩展存储过程的前缀是xp_

 

例7：用扩展存储过程显示当前目录下的全部文件，可以用xp_cmdshell实现。

   EXEC master..xp_cmdshell ‘dir *.*’

四、定义存储过程

可以用CREATE PROCEDURE语句来定义存储过程，语法如下：

CREATE PROC [ EDURE ] procedure_name [ ; number ]

    [ { @parameter data_type }

        [ VARYING ] [ = default ] [ OUTPUT ]

    ] [ ,...n ]

[ WITH

    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

 

procedure_name：新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。要创建局部临时过程，可以在 procedure_name 前面加一个编号符 (#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两个编号符 (##procedure_name)。完整的名称（包括 # 或 ##）不能超过 128 个字符。指定过程所有者的名称是可选的。

 

@parameter

    过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2100 个参数。

     使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。

 

data_type：参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。

 

OUTPUT：表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。

 

AS：指定过程要执行的操作。

 

sql_statement：过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。

 

五、执行存储过程

    存储过程的执行有两种方法：SQL Server Management Studio中可视化界面操作执行和TRANSACT-SQL语句执行。

（1）SQL Server Management Studio中可视化界面操作执行存储过程

     在对象资源管理器中，展开students数据库，在“可编程性”中展开“存储过程”树型目录，右击proc_stu1,在快捷菜单上，选择“执行存储过程”命令，运行该存储过程，如图所示。

（2）通过TRANSACT-SQL语句的EXEC命令执行一个已定义的存储过程

语法格式：

    [ EXEC [ UTE ] ][@return_status = ]

     { procedure_name [;number ] | @procedure_name_var }

     [ [ @parameter = ] {value |@variable [ OUTPUT]|[DEFAULT ]}]

     [ ,...n ]

例8：创建一个存储过程proc_jsjstu，在students数据库中，实现查找“计算机系”学生的信息，运行结果如图7-7所示。

注：在学生学号中的第5、6位是系部编号，系部编号与系部名称对应关系见表。   

EXEC proc_stu1

    GO

 

如果要执行的存储过程不是批处理的第一句，则需要加上 exec 或 execute，如果是批处理的第一句，则可以省略 exec 或 execute

USE  students

GO

CREATE PROCEDURE proc_jsjstu @xbbh varchar(4)

AS

SELECT *

FROM stu

WHERE  SUBSTRING(id,5,2)=@xbbh

GO

EXEC proc_jsjstu '02'

 

例9：创建一个存储过程，该存储过程能够实现根据系部的编号查询出系部中男生、女生的人数。

USE students

GO

CREATE PROCEDURE proc_countsex @xbbh varchar(4)

AS

BEGIN

SELECT sex as '性别',count(id) as '人数合计'

 FROM student

 WHERE  substring(id,5,2)=@xbbh

 GROUP BY  sex

END

EXEC proc_countsex '02'

六、带输出参数的存储过程

1.带输出参数的存储过程

用OUTPUT 可以声明一个带返回值参数，可以将信息返回给调用过程，如果某个参数在传输到存储过程中时被定义成OUTPUT，则对该参数的任何修改在退出存储之后仍然有效。

2.创建带输出参数的存储过程

    通过在创建存储过程的语句中定义输出参数，可以创建带输出参数的存储过程。执行该存储过程，可以返回一个或多个值。具体语法如下：

 

CREATE PROC [EDURE] procedure_name [;number] /*定义存储过程名*/

[{@parameter data_type }            /*定义参数的类型*/

[VARYING] [=default][OUTPUT]]

/* OUTPUT关键字指定参数的属性为输出参数*/

[ ,...n ]

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

                                              /*定义存储过程的处理方式*/

[ FOR REPLICATION ]

 AS   sql_statements                    /*执行的操作*/

 

例10 在students学生数据库中，创建一个存储过程proc_ testOutput，要求实现返回成绩在@p1与@p3之间的学生人数，其中，@p1与@p3是输入参数，用于指定分数段。

CTEATE PROCEDURE dbo.proc_testOutput  

    (

    @p1 int ,

    @p2 int OUTPUT,

    @p3 int 

    )   

AS

BEGIN

    select @p2 = count(*) from stu where score between @p1 and @p3

    RETURN @@rowcount

    PRINT @P2

END

--这个存储过程返回两个值，一个是output型参数@p2，调用存储过程后，返回结果存放在@p2中。

--另外一个是由return值@@rowcount（语句所影响的行数）

七、自动执行存储过程

   SQL Server 2008启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员创建，并在 sysadmin 固定服务器角色下作为后台过程执行。这些过程不能有任何输入参数，也不能从过程中返回任何结果集。

    若要创建启动存储过程，必须作为 sysadmin 固定服务器角色的成员登录，并在 master 数据库中创建存储过程。

使用Transact-SQL命令方式设置自动执行存储过程：将存储过程创建在master数据库中，使用系统存储过程sp_procoption可以将现有存储过程设置为自动执行过程或停止自动执行。

（1）先在master数据库中创建存储过程

CREATE PROCEDURE pro_viewstudent

AS

SELECT * FROM spt_values

GO

（2）使用系统存储过程sp_procoption可以将现有存储过程设置为自动执行过程

USE master

GO

EXEC sp_procoption 'pro_viewstudent','startup','on'

--第一个参数是存储过程名

--第二个参数startup，该值表示设置存储过程的自动执行状态

--第三个参数可以设置为开（true或on）还是关（false或off）

八、管理存储过程

1.重命名存储过程

（1）使用SQL Server Management Studio重命名存储过程

     在对象资源管理器中，展开"数据库"文件夹，再展开“可编程性”的“存储过程”，找到要修改名字的存储过程，然后右击打开快捷方式，选择“重命名”命令，即可完成存储过程的重命名，如图所示。

2.修改存储过程

（1）使用SQL Server Management Studio修改存储过程

（2）使用Transact-SQL语句修改存储过程

使用ALTER PROCEDURE语句修改存储过程，语法格式：

ALTER PROC[EDURE］存储过程名［；下标］

［｛@形参 数据类型｝

［VARYING］［=默认值］［OUTPUT］ ］［，...n1］

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

AS  Transact-SQL 语句 ［...n2］ /*执行的操作*/

 

    各参数含义与CREATE PROCEDURE命令相同。从形式看，该语句与CREATE PROCDURE语句的主要差别仅在开头的关键字不同（一为CREATE，另一为ALTER），但ALTER PROCEDURE语句不会更改权限和启动属性，即如果我们先删除某存储过程，再用CREATE PROCDURE语句创建与它同名的存储过程，虽然两者的语句一致，但原先该存储过程的权限以及启动属性将不复存在。

    而用ALTER PROCEDURE更改存储过程后，该过程的权限和启动属性保持不变。另外，如果原来的过程定义是用 WITH ENCRYPTION 或 WITH RECOMPILE 创建的，那么只有在 ALTER PROCEDURE 中也包含这些选项时，这些选项才有效。

 

例11修改存储过程proc_scorepass，在students数据库中，要求根据该学生所选课程编号为“0201305”课程的成绩显示提示信息，即如果成绩在90分以上，显示“优秀”，在80分以上，显示“良好”，在70分以上，显示“中等”，在60分以上，显示“及格”，否则显示“不及格！”。

USE students

GO

ALTER PROCEDURE proc_scorepass

AS

SELECT *,等级=case

WHEN score>=90 THEN  '优秀'

WHEN score>=80 THEN '良'

WHEN score>=70 THEN '中'

WHEN score>=60 THEN '及格'

ELSE  '不及格'

END

FROM grade

WHERE sno='0203105'

EXEC proc_scorepass

3.删除存储过程

（1）使用SQL Server Management Studio删除存储过程

在SQL Server Management Studio中，找到要删除的存储过程所在的数据库，展开数据库，展开“可编程性”目录树，展开“存储过程”，选中所要删除的存储过程，然后单击右键打开快捷菜单，点击“删除”命令，如图所示。

（2）使用TRANSACT-SQL语句删除

语法

DROP PROCEDURE { procedure } [ ,...n ]

参数

procedure 是要删除的存储过程或存储过程组的名称

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

例12.将“students”数据库中存储过程“proc_scorepass”删除

USE students

GO

DROP PROCEDURE proc_scorepass

九、存储过程的加密

如果用户不想让其他人查看存储过程的定义文本，可以在定义存储过程时，对其进行加密。加密存储过程的关键字是：WITH ENCRYPTION，使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。

 

十、存储过程中的错误处理

在存储过程中进行错误处理是非常重要的。在访问存储过程的时候发生错误，可以通过@@ERROR系统函数返回错误代码。系统变量@@error在执行每一个Transact SQL语句之后都会得到一个值。对于成功的执行，@@error的值为0，如果出现错误，则@@error中将包含错误信息。

可以用系统存储过程sp_addmessage自定义错误信息，语法如下：

sp_addmessage [ @msgnum =] msg_id ,

    [ @severity = ] severity ,

    [ @msgtext = ] 'msg'

    [ , [ @lang = ] 'language' ]

    [ , [ @with_log = ] 'with_log' ]

    [ , [ @replace = ] 'replace' ]

错误的有效级别为 1 到 25。只有系统管理员可以使用从 19 到 25 之间的严重级别添加消息。用户自定义的错误信息从50001 开始，例如可以自定义一个错误信息：

EXEC sp_addmessage 50001, 16, '发生错误，请重新执行该语句'

用RAISERROR可以触发错误信息，客户端可以从 sysmessages 表中检索条目，或者使用用户指定的严重度和状态信息动态地生成一条消息。这条消息在定义后就作为服务器错误信息返回给客户端。

    RAISERROR语法如下：

    RAISERROR ( { msg_id | msg_str } { , severity , state }

    [ , argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

例如触发上例中自定义的消息，可以用如下语句：

    RAISERROR 50001, 1