SqlServer高级
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
• 提示：
– 观察这3个系统存储过程的执行结果
完成时间：15分钟
如何创建存储过程
• 定义存储过程的语法
CREATE PROC[EDURE] 存储过程名 @参数1 数据类型 = 默认值 OUTPUT, ……, @参数n 数据类型 = 默认值 OUTPUT
AS SQL语句
GO
• 存储过程的参数
– 和C#语言的方法一样，参数可选 – 参数分为输入参数、输出参数
获得课程编号
SELECT @date=max(ExamDate) FROM Result INNER JOIN Subject
ON Result.SubjectNo=Subject.SubjectNo
WHERE Result.SubjectNo=@subjectNo
获得考试时间
第一步：获得“Java Logic”的课程编号
修改数据库的名称(单用户访问)
GO
当前数据库中查询的对象的列表
sp_tables EXEC sp_columns Student EXEC sp_help Student
返回某个表列的信息 查看表Student的信息
EXEC sp_helpconstraint Student
查看表Student的约束
EXEC xp_cmdshell 'dir D:\bank\' -- 查看文件
练习——使用存储过程查看表信息
• 需求说明：
– 查看Student表中的列、约束信息 – 比较下面三个系统存储过程输出的数据库信
息的特点
• sp_columns • sp_helpconstraint • sp_help
• 用户自定义存储过程
– 由用户在自己的数据库中创建的存储过程
常用的系统存储过程
系统存储过程
说明
sp_databases
列出服务器上的所有数据库
sp_helpdb
报告有关指定数据库或所有数据库的信息
sp_renamedb
更改数据库的名称
sp_tables
返回当前环境下可查询的对象的列表
sp_columns
什么是存储过程2-2
• 可带参数，也可返回结果
• 可包含数据操纵语句、变量、逻辑控制语 句等
单个 SELECT 语句
存储过程
----------------------
SELECT 语句块
可以包含
SELECT语句与逻辑 控制语句
存储过程的优点
• 执行速度更快 • 允许模块化程序设计 • 提高系统安全性 • 减少网络流通量
调用存储过程
• 调用存储过程的语法
EXECUTE 过程名 [参数]
或 EXEC 过程名 [参数]
如果执行存储过程的语句是批处理中的第一个语句，则 可以不指定 EXECUTE 关键字
常用的系统存储过程
sp_databases
列出当前系统中的数据库
EXEC sp_renamedb 'MyBank','Bank' USE MySchool
第七章
存储过程
回顾
• 管理控制事务的常用T-SQL语句有哪些？ • 什么是视图？它有什么好处？ • T-SQL中哪个关键字可以实现用索引查询
数据？
本章任务
• 使用存储过程完成以下功能：
– 查看各学期的课程信息 – 查询指定学期开设的课程信息 – 获得指定学期开设的课程数目 – 插入新增课程记录
本章目标
存储过程的分类
• 系统存储过程
– 系统存储过程的名称一般以“sp_”开头 – 由SQLServer创建、管理和使用 – 存放在Resource数据库中 – 类似C#语言类库中的方法
• 扩展存储过程
– 扩展存储过程的名称通常以“xp_”开头 – 使用编辑语言（如C#)创建的外部存储过程 – 以DLL形式单独存在
返回某个表列的信息
sp_help
查看某个表的所有信息
sp_helpconstraint
查看某个表的约束
sp_源自文库elpindex
查看某个表的索引
sp_stored_procedures 列出当前环境中的所有存储过程
sp_password
添加或修改登录帐户的密码
sp_helptext
显示默认值、未加密的存储过程、用户定义 的存储过程、触发器或视图的实际文本
IF EXISTS(SEELEXCECT *xpF_RcOmMdsshysedllaDtaObSa命se令s [WNHOE_OREUTnPaUmTe]='bankDB') DROP DATABASE bankDB
GO CREATE DATABASE bankDB (
…
)GO创建数据库bankD查B看，文件要夹D求:\b保ank存在D:\bank
EXEC sp_helptext 'view_Student_ Result_Info' EXEC sp_stored_procedures
查看视图的语句文本
查看当前数据库中的存储过程
演示案例1：常用的存储过程
常用的扩展存储过程
• xp_cmdshell
UGEXSOEECmxaps––_tec可 以rmd以 文she执 本ll 'm行行kd方DirOD式S:\b命创返an建令k回文', N下件任O夹_的何OD:U\一bT输aPn些U出kT的操作
• 了解存储过程的优点 • 掌握常用的系统/扩展存储过程 • 使用存储过程封装业务逻辑 • 掌握如何实现错误处理
什么是存储过程2-1
• 预先存储好的SQL程序 • 保存在SQL Server中 • 通过名称和参数执行
类似于C#语言中的方法
int sum(int a, int b) {
int s; s = a + b; return s; }
SELECT @avg=AVG(StudentResult) FROM Result
考试平均分
WHERE第Ex二am步Dat：e=@获da得te “andJSauvbjaectLNoo=g@iscu”bj最ect近No一次的考试时
间 … …
IF (@avg>70) PRINT '考试成绩：优秀'
ELSE ……
创建、执行无参的存储
过程 CASREAT…•E…P创RO建CED存UR储E u过sp_程GetA，vgR查es询ult Java Logic最近一次考
试平均分以及未通过考试的学员名单 SELECT @subjectNo=subjectNo FROM Subject
WHERE SubjectName = 'Java Logic'
– 观察这3个系统存储过程的执行结果
完成时间：15分钟
如何创建存储过程
• 定义存储过程的语法
CREATE PROC[EDURE] 存储过程名 @参数1 数据类型 = 默认值 OUTPUT, ……, @参数n 数据类型 = 默认值 OUTPUT
AS SQL语句
GO
• 存储过程的参数
– 和C#语言的方法一样，参数可选 – 参数分为输入参数、输出参数
获得课程编号
SELECT @date=max(ExamDate) FROM Result INNER JOIN Subject
ON Result.SubjectNo=Subject.SubjectNo
WHERE Result.SubjectNo=@subjectNo
获得考试时间
第一步：获得“Java Logic”的课程编号
修改数据库的名称(单用户访问)
GO
当前数据库中查询的对象的列表
sp_tables EXEC sp_columns Student EXEC sp_help Student
返回某个表列的信息 查看表Student的信息
EXEC sp_helpconstraint Student
查看表Student的约束
EXEC xp_cmdshell 'dir D:\bank\' -- 查看文件
练习——使用存储过程查看表信息
• 需求说明：
– 查看Student表中的列、约束信息 – 比较下面三个系统存储过程输出的数据库信
息的特点
• sp_columns • sp_helpconstraint • sp_help
• 用户自定义存储过程
– 由用户在自己的数据库中创建的存储过程
常用的系统存储过程
系统存储过程
说明
sp_databases
列出服务器上的所有数据库
sp_helpdb
报告有关指定数据库或所有数据库的信息
sp_renamedb
更改数据库的名称
sp_tables
返回当前环境下可查询的对象的列表
sp_columns
什么是存储过程2-2
• 可带参数，也可返回结果
• 可包含数据操纵语句、变量、逻辑控制语 句等
单个 SELECT 语句
存储过程
----------------------
SELECT 语句块
可以包含
SELECT语句与逻辑 控制语句
存储过程的优点
• 执行速度更快 • 允许模块化程序设计 • 提高系统安全性 • 减少网络流通量
调用存储过程
• 调用存储过程的语法
EXECUTE 过程名 [参数]
或 EXEC 过程名 [参数]
如果执行存储过程的语句是批处理中的第一个语句，则 可以不指定 EXECUTE 关键字
常用的系统存储过程
sp_databases
列出当前系统中的数据库
EXEC sp_renamedb 'MyBank','Bank' USE MySchool
第七章
存储过程
回顾
• 管理控制事务的常用T-SQL语句有哪些？ • 什么是视图？它有什么好处？ • T-SQL中哪个关键字可以实现用索引查询
数据？
本章任务
• 使用存储过程完成以下功能：
– 查看各学期的课程信息 – 查询指定学期开设的课程信息 – 获得指定学期开设的课程数目 – 插入新增课程记录
本章目标
存储过程的分类
• 系统存储过程
– 系统存储过程的名称一般以“sp_”开头 – 由SQLServer创建、管理和使用 – 存放在Resource数据库中 – 类似C#语言类库中的方法
• 扩展存储过程
– 扩展存储过程的名称通常以“xp_”开头 – 使用编辑语言（如C#)创建的外部存储过程 – 以DLL形式单独存在
返回某个表列的信息
sp_help
查看某个表的所有信息
sp_helpconstraint
查看某个表的约束
sp_源自文库elpindex
查看某个表的索引
sp_stored_procedures 列出当前环境中的所有存储过程
sp_password
添加或修改登录帐户的密码
sp_helptext
显示默认值、未加密的存储过程、用户定义 的存储过程、触发器或视图的实际文本
IF EXISTS(SEELEXCECT *xpF_RcOmMdsshysedllaDtaObSa命se令s [WNHOE_OREUTnPaUmTe]='bankDB') DROP DATABASE bankDB
GO CREATE DATABASE bankDB (
…
)GO创建数据库bankD查B看，文件要夹D求:\b保ank存在D:\bank
EXEC sp_helptext 'view_Student_ Result_Info' EXEC sp_stored_procedures
查看视图的语句文本
查看当前数据库中的存储过程
演示案例1：常用的存储过程
常用的扩展存储过程
• xp_cmdshell
UGEXSOEECmxaps––_tec可 以rmd以 文she执 本ll 'm行行kd方DirOD式S:\b命创返an建令k回文', N下件任O夹_的何OD:U\一bT输aPn些U出kT的操作
• 了解存储过程的优点 • 掌握常用的系统/扩展存储过程 • 使用存储过程封装业务逻辑 • 掌握如何实现错误处理
什么是存储过程2-1
• 预先存储好的SQL程序 • 保存在SQL Server中 • 通过名称和参数执行
类似于C#语言中的方法
int sum(int a, int b) {
int s; s = a + b; return s; }
SELECT @avg=AVG(StudentResult) FROM Result
考试平均分
WHERE第Ex二am步Dat：e=@获da得te “andJSauvbjaectLNoo=g@iscu”bj最ect近No一次的考试时
间 … …
IF (@avg>70) PRINT '考试成绩：优秀'
ELSE ……
创建、执行无参的存储
过程 CASREAT…•E…P创RO建CED存UR储E u过sp_程GetA，vgR查es询ult Java Logic最近一次考
试平均分以及未通过考试的学员名单 SELECT @subjectNo=subjectNo FROM Subject
WHERE SubjectName = 'Java Logic'