第12章存储过程和用户存储过程设计

AND E.学号=S.学号 AND E.开课号=O.开课号 SELECT @平均成绩 =AVG(成绩) FROM 学生表 S, 开课表 O, 选课表 E WHERE S.学号=@学号 AND E.学号=S.学号 AND E.开课号=O.开课号 RETURN GO
»22
然后在查询分析器中调用 Scorequery 存储过程
»21
【例12-7】使用OUTPUT参数的存储过程及其执行
首先创建存储过程 USE 教学管理 GO CREATE PROCEDURE P_成绩检索和平均 (@学号 CHAR(7), @平均成绩 FLOAT OUTPUT) AS SELECT S.学号,姓名,课号,成绩 FROM 学生表 S, 开课表 O, 选课表 E WHERE S.学号=@学号
sp_cursor_list ； sp_describe_cursor_columns ； sp_describe_cursor ； sp_describe_cursor_tables
sp_addlinkedserver ； sp_indexes ； sp_addlinkedsrvlogin ；
sp_linkedservers；sp_catalogs
»19
（2）按参数名传递值 EXEC P_学生选课信息 @专业 ='计算机',
@课名='数据结构' 按参数名传递值可以改变参数的顺序： EXEC P_学生选课信息 @课名='数据结构',
@专业='计算机'
»20
（3）也可以两种方法混合使用，一旦使用了 '@name = value' 形式之后，所有后续的参 数就必须以 '@name = value' 的形式传递。 比如： EXEC P_学生选课信息 '计算机', @课名='数 据结构'
»14
语法格式： CREATE PROC [ EDURE ] 存储过程名 [；版本号] [ ({ @参数名 数据类型 } [VARYING] [=default]
[OUTPUT] [，…])] [WITH
RECOMPILE|ENCRYPTION|RECOMPILE,ENC RYPTION] [FOR REPLICATION] AS SQL语句
sp_primarykeys；
sp_droplinkedsrvlogin；sp_foreignkeys
sp_addalias ； sp_droprolemember ； sp_addapprole ； sp_dropserver ；
sp_addgroup；sp_dropsrvrolemember；
sp_addlinkedsrvlogin；sp_dropuser；sp_addlogin；sp_grantdbaccess；
sp_addremotelogin；sp_grantlogin；
sp_addrole ； sp_helpdbfixedrole ； sp_addrolemember ； sp_helpgroup ；
sp_addserver；sp_helplinkedsrvlogin；
sp_addsrvrolemember ； sp_helplogins ； sp_adduser ； sp_helpntgroup ；
»8
分类 目录过程 游标过程 分布式查 询过程
安全过程
12.2.2 一些常用的系统存储过程
常用的系统存储过程
sp_column_privileges ； sp_special_columns ； sp_columns ； sp_sproc_columns；sp_databases；sp_statistics； sp_fkeys；sp_stored_procedures；(部分)
码或分别被多次执行，那么存储过程要比批处理的 执行速度快很多。 （3）存储过程能够减少网络流量
对于同一个针对数据数据库对象的操作（如查 询、修改），如果这一操作所涉及到的 Transaction-SQL语句被组织成一存储过程，那么 当在客户计算机上调用该存储过程时，网络中传送 的只是该调用语句，否则将是多条SQL语句，从而 大大增加了网络流量，降低网络负载。
第12章存储过程和用户存储过程设计
➢ 12.1 存储过程概述 ➢ 12.2 系统存储过程 ➢ 12.3 创建和执行用户存储过程 ➢ 12.4 带状态参数的存储过程及实例分析 ➢ 12.5 修改和删除存储过程 ➢ 12.6 存储过程设计实例分析
»2
12.1 存储过程概述
12.1.1 存储过程的概念和分类 SQL Server提供了一种方法，它可以将一
»7
12.2 系统存储过程
12.2.1 系统存储过程分类 系统存储过程就是系统创建的存储过程，目的在于 能够方便地从系统表中查询信息或完成与更新数 据库表相关的管理任务或其它的系统管理任务。 系统过程以“sp_"开头,在Master数据库中创建并 保存在该数据库中，为数据库管理者所有。一些 系统过程只能由系统管理员使用，而有些系统过 程通过授权可以被其它用户所使用。
sp_approlepassword；
sp_helpremotelogin ； sp_changedbowner ； sp_helprole ； sp_changegroup
；sp_helprolemember；(部分)
»9
sp_add_data_file_recover_suspect_db
；
sp_helpconstraint ； sp_addextendedproc ； sp_helpdb ；
»17
12.3.2 执行用户存储过程
执行已创建的存储过程使用EXECUTE 命令。 语法格式： [ [ EXEC [ UTE ] ] { [ @return_status = ]{ <存储过程名> [ ;版本号] |
@procedure_name_var} [ [ @参数= ] { value | @variable [ OUTPUT ] |
过
sp_helpserver；sp_attach_db；sp_helpsort；
程
sp_attach_single_file_db ； sp_helpstats ； sp_bindefault ； sp_helptext ； sp_bindrule ；
sp_helptrigger；
sp_bindsession
USE 教学管理 IF EXISTS(SELECT*
FROM sysobjects WHERE name='SC_infor' AND type='p') BEGIN DROP PROCEDURE SC_infor END GO CREATE PROCEDURE SC_infor(@smajor CHAR(20), @cname CHAR(20)) AS SELECTS.sno,sname,smajor,sdepa,o,cname,grade FROM student S, enrollment E, offering O, course C where smajor=@smajor AND cname=@cname AND S.sno=E.sno AND E.ono=O.ono AND o=o GO
sp_addextendedproperty ； sp_helpdevice ；
sp_add_log_file_recover_suspect_db
；
sp_helpextendedproc；
系
sp_addmessage ； sp_helpfile ； sp_addtype ；
统
sp_helpfilegroup；sp_addumpdevice；sp_helpindex； sp_altermessage ； sp_helplanguage ； sp_autostats ；
»15
【例12-3】创建一个简单的存储过程，返回所有学 生的基本信息，包括学生的学号、姓名、所学专 业、所在二级学院、来自的城市。 USE 教学管理 GO CREATE PROCEDURE P_学生部分信息 AS SELECT 学号,姓名,专业,所在院系,籍贯 FROM 学生表
»16
【例12-4】创建带参数的存储过程，实现对指定的某一专业 的学生某门课程的选课信息和成绩。
创建存储过程时，需要确定存储过程的三个组成部分： ① 所有的输入参数以及传给调用者的输出参数。 ② 被执行的针对数据库的操作语句，包括调用其它存储过程 的语句。 ③ 返回给调用者的状态值，以指明调用是成功还是失败。
»12
1．使用SQL Server管理器创
建存储过程
① 在SQL Server管理器中，选择指定的 服务器和数据库，用右键单击要创建存储 过程的数据库，在弹出的快捷菜单中选择 “新建”选项，再选择下一级菜单中的 “存储过程…”选项，从弹出的快捷菜单 中选择“新建存储过程…”选项，出现创 建存储过程对话框。
[ DEFAULT ] ][ ,...n ] [ WITH RECOMPILE ]
»18
【例12-5】对存储过程P_学生部分信息的执行。 EXEC P_学生部分信息
【例12-6】带输入参数的存储过程P_学生选课信息的执行。 （1）按参数位置传递值
EXEC P_学生选课信息 '计算机','数据结构'
»11
12.3 创建和执行用户存储过程
12.3.1 创建用户存功能 （如查询用户所需数据信息）的存储过程。在SQL Server中， 可以使用以下方法创建存储过程： ① 利用SQL Server管理器创建存储过程。 ② 使用Transact-SQL语句中的CREATE PROCEDURE命令 创建存储过程。
或者： 或者：