第8章(2)DataBase存储过程

创建存储过程语法形式如下： CREATE PROC[EDURE] 存储过程名 [;版本号] [ {@参数1 数据类型} [VARYING] [= 默认值] [OUTPUT], …… , {@参数n 数据类型} [VARYING] [= 默认值] [OUTPUT] ] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} ] [FOR REPLICATION] AS SQL语句 ……
GO
EXEC student_info
(2) 使用带参数的存储过程
【例2】从数据库的三个表中查询某人指定课程的成绩和学分。该 存储过程接受与传递参数精确匹配的值。 USE 魏菊丽20086666 GO /*检查是否已存在同名的存储过程，若有，删除。*/ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'student_info1' AND type = 'P') DROP PROCEDURE student_info1 GO CREATE PROCEDURE student_info1 @name char (8),@cname char(15)
(3) 临时存储过程 属于本地存储过程。如果本地存储过程的名称前面有一个“#”，该 存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会 话中使用；如果本地存储过程的名称前面有两个“##”，该存储过程就 称为全局临时存储过程，这种存储过程可以在所有用户会话中使用。
(4) 远程存储过程 远程存储过程指从远程服务器上调用的存储过程。
……
8.3.3 重命名和删除存储过程
1. 重命名存储过程
修改存储过程的名称可以使用系统存储过程sp_rename， 其语法形式如下：
sp_rename 原存储过程名称，新存储过程名称
•
另外，通过对象资源管理器也可以修改存储过程的名称。
2. 删除存储过程
• 删除存储过程可以使用DROP命令，DROP命令可以将一 个或者多个存储过程或者存储过程组从当前数据库中删除， 其语法形式如下： • drop procedure {procedure} [,…n] • 当然，利用对象资源管理器也可以很方便地删除存储过程。
(5) 扩展存储过程 在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前 缀是sp_。使用时需要先加载到SQL Server系统中，并且按照使用存储 过程的方法执行。
8.3.1 创建存储过程
利用对象资源管理器创建 利用T-SQL语句创建(Create Procedure)
创建存储过程时，需要确定存储过程的三个组成部分：
执行存储过程：
•直接执行存储过程可以使用EXECUTE命令来执行，其语 法形式如下： [[EXEC[UTE]] { [@return_status=] {procedure_name[;number] |@procedure_name_var} [[@parameter=]{value|@variable[OUTPUT |[DEFAULT]}] [,...n] [ WITH RECOMPILE ]
AS
SELECT student.sno, sname, cname, grade, ccredit FROM student ,sc,course where student.sno = sc.sno and o=o and student.sname=@name and ame=@cname GO
例：如果要通过SQL命令定义一个存储过程查询魏菊丽20086666 数据库中每个同学各门功课的成绩，然后调用该存储过程步骤如下：
定义如下存储过程
USE 魏菊丽20086666 Go CREATE PROCEDURE student_grade AS SELECT student.sno,student.sname,ame, sc.grade FROM student,sc,course WHERE student.sno=sc.sno AND o=o Go 调用存储过程 EXEC student_grade GO
参数的意义如下： • 如果执行存储过程的语句是批处理中的第一条语句，可以 省略EXECUTE关键字。 • @return_status：为整型局部变量，用于保存存储过程 的返回值。 • @procedure_name_var：为存储过程名。 • @parameter：为创建存储过程时定义的参数名。 • Value：为存储过程实参。 • @variable:为变量，用于保存OUTPUT参数返回的值。 • DEFAULT：表示不提供实参，使用对应的默认值。 • WITH RECOMPILE：强制编译。
2. 修改存储过程 • 修改存储过程的语法如下： ALTER PROC[EDURE] 存储过程名 [;版本号] [ {@ 参 数 1 数 据 类 型 } [VARYING] [= DEFAULT] [OUTPUT], …… , {@ 参 数 n 数 据 类 型 } [VARYING] [= DEFAULT] [OUTPUT] ] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} ] [FOR REPLICATION] AS SQL语句
三、存储过程的类型
(1) 系统存储过程 是由系统提供的存储过程，作为命令执行各种操作。它定义在系统数据 库master中，其前缀是sp_，例如sp_addtype。系统存储过程可在任何 数据库中使用。 (2) 本地存储过程 是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操 作任务，其名称不能以sp_为前缀。
如果一个输出参数的类型为游标， 必须同时指定 VARYING和OUTPUT。 RECOMPILE：表明存储过程每次执行时重新编译。 ENCRYPTION ：表示加密了syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。这样可防止过 程作为SQL Server复制的一部分发布，防止用户使用系 统存储过程读取存储过程的定义文本。 FOR REPLICATION：只能在复制过程中执行。本选项 不能和WITH RECOMPILE选项一起使用。 AS：用于指定该存储过程要执行的操作。 SQL 语句：是存储过程中要包含的任意数目和类型的 Transact-SQL 语句。
•可供使用的系统存储过程及其语法形式如下： •sp_help：用于显示存储过程的参数及其数据类型 • sp_help [[@objname=] name] • 参数name为要查看的存储过程的名称。 •sp_helptext：用于显示存储过程的源代码 • sp_helptext [[@objname=] name] • 参数name为要查看的存储过程的名称。 •sp_depends：用于显示和存储过程相关的数据库对象 • sp_depends [@objname=]’object’ • 参数object为要查看依赖关系的存储过程的名称。 •sp_stored_procedures：用于返回当前数据库中的存储过 程列表
参数的意义如下：
存储过程名：要符合标识符规则，少于128个字符。 版本号：该参数是可选的整数，它用来对同名的存储过程 分组，以便用一条 DROP PROCEDURE 语句即可将同 组的过程一起除去。 @参数：过程中的参数。在 CREATE PROCEDURE 语 句中可以声明一个或多个参数。执行存储过程时应提供相 应的实参，除非定义了该参数的默认值，默认值只能是常 量。 VARYING：用于指定作为输出OUTPUT参数支持的结果 集（仅适用于游标参数）。 OUTPUT：用于指定参数从存储过程返回信息，可将信 息返回给调用过程。
EXEC student_info1 '李勇','数据库'
(3) 使用带有通配符参数的存储过程
【例3】从三个表的联接中返回指定学生的学号、姓名、所选课程名 称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提 供参数，则使用预设的默认值。 USE 魏菊丽20086666 GO IF EXISTS (SELECT name FROM sysobjects WHERE name = 'student_info2' AND type = 'P') DROP PROCEDURE student_info2 GO CREATE PROCEDURE student_info2 @name varchar(30) = '刘%' AS SELECT student.sno, sname, cname, grade, ccredit FROM student ,sc,course where student.sno = sc.sno and o=o and sname LIKE @name GO
GO /*创建存储过程*/
CREATE PROCEDURE student_info AS SELECT student.sno, sname, cname, grade, ccredit FROM student ,sc,course WHERE student.sno = sc.sno and o=o
①所有的输入参数以及传给调用者的输出参数。 ②被执行的操作语句(针对数据库), 包括调用其它存储过 程的语句。 ③返回给调用者的状态值，以指明调用是成功还是失败。
1. 使用CREATE PROCEDURE命令创建存储过程 • 创建存储过程前，应该考虑下列几个事项： • ①不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。 • ②创建存储过程的权限默认属于数据库所有者，该所 有者可将此权限授予其他用户。 • ③存储过程是数据库对象，其名称必须遵守标识符规 则。 • ④只能在当前数据库中创建存储过程。 • ⑤一个存储过程的最大尺寸为128M。