SqlServer高级

• 用户自定义存储过程
– 由用户在自己的数据库中创建的存储过程
常用的系统存储过程
系统存储过程
sp_databases sp_helpdb sp_renamedb
说
明
列出服务器上的所有数据库 报告有关指定数据库或所有数据库的信息 更改数据库的名称
sp_tables
sp_columns sp_help sp_helpconstraint sp_helpindex sp_password
SELECT @subjectNo=subjectNo FROM Subject WHERE SubjectName = 'Java Logic'
试平均分以及未通过考试的学员名单 获得课程编号
第一步：获得“Java Logic”的课程编号
SELECT @date=max(ExamDate) FROM Result INNER JOIN Subject ON Result.SubjectNo=Subject.SubjectNo WHERE Result.SubjectNo=@subjectNo 获得考试时间 SELECT @avg=AVG(StudentResult) FROM Result 考试平均分 WHERE ExamDate=@date and SubjectNo=@subjectNo 第二步：获得“Java Logic”最近一次的考试时 …… 间 IF (@avg>70) PRINT '考试成绩：优秀' ELSE PRINT '考试成绩：较差' 显示考试成绩的等级 … … 第三步：查询得到平均成绩 SELECT StudentName,Student.StudentNo,StudentResult FROM 第四步：查询这次考试成绩低于60分的学生 Student INNER JOIN Result ON Student.StudentNo=Result.StudentNo 查询未通过的学员 WHERE StudentResult<60 AND … … GO 演示案例2：创建无参的存储过程 EXEC usp_GetAvgResult 执行存储过程
SQLServer存储过程的最大嵌套层数为32。 如果超过最大嵌套层数，会造成执行出错
• 定义存储过程的语法
CREATE PROC[EDURE] 存储过程名 @参数1 数据类型 = 默认值 OUTPUT, ……, @参数n 数据类型 = 默认值 OUTPUT AS SQL语句 GO
• 存储过程的参数
– 和C#语言的方法一样，参数可选 – 参数分为输入参数、输出参数
创建、执行无参的存储 CREATE PROCEDURE usp_GetAvgResult 过程 AS … … • 创建存储过程，查询Java Logic最近一次考
存储过程的分类
• 系统存储过程
– 系统存储过程的名称一般以“sp_”开头 – 由SQLServer创建、管理和使用 – 存放在Resource数据库中 – 类似C#语言类库中的方法
• 扩展存储过程
– 扩展存储过程的名称通常以“xp_”开头 – 使用编辑语言（如C#)创建的外部存储过程 – 以DLL形式单独存在
什么是存储过程2-2
• 可带参数，也可返回结果
• 可包含数据操纵语句、变量、逻辑控制语 句等
单个 SELECT 语句 存储过程
----------------------
SELECT 语句块
SELECT语句与逻辑 控制语句 可以包含
存储过程的优点
• • • • 执行速度更快 允许模块化程序设计 提高系统安全性 减少网络流通量
此处不能用空格替 使用参数名指定参 使用default代表默 代默认值 数值 认值
常见错误2-2
CREATE PROCEDURE usp_unpass @score int = 60, @subName varchar(50) AS 存储过程的调用语句被放置到 …… 了创建语句中，形成了递归调 用 GO EXEC usp_unpass 50, 'Java Logic' GO
返回当前环境下可查询的对象的列表
返回某个表列的信息 查看某个表的所有信息 查看某个表的约束 查看某个表的索引 添加或修改登录帐户的密码
sp_stored_procedures 列出当前环境中的所有存储过程
sp_helptext
显示默认值、未加密的存储过程、用户定义 的存储过程、触发器或视图的实际文本
• 了解存储过程的优点 • 掌握常用的系统/扩展存储过程 • 使用存储过程封装业务逻辑
• 掌握如何实现错误处理
什么是存储过程2-1
• 预先存储好的SQL程序 • 保存在SQL Server中 • 通过名称和参数执行
类似于C#语言中的方法 int sum(int a, int b) { int s; s = a + b; return s; }
GO /*---创建存储过程----*/ CREATE PROCEDURE usp_grade_subject AS SELECT GradeName,SubjectName,ClassHour FROM Grade INNER JOIN Subject ON Grade.GradeId=Subject.GradeId ORDER BY Subject.GradeId,SubjectNo GO /*---调用执行存储过程---*/ EXEC usp_grade_subject
– 假定C# OOP课程最近一次考试的试题偏难， 考试及格线定为50分
输入参数的默认值2-1
• 如果试卷难易程度合适，则调用者仍须调 用： usp_unpass 'C# OOP', 60 EXEC • 有简便的方法吗？
使用参数的默认值
输入参数默认值2-2
• 创建带参数默认值的存储过程
CREATE PROCEDURE usp_unpass @subName varchar(50), @score int = 60 AS 有默认值的参数放在存储过程参数列表的 …… 最后 GO
指导——查询获得各学期课程信息2-1
• 训练要点：
– 使用无参存储过程完成 数据查询
• 需求说明：
– 利用存储过程查询各学 期开设的课程名称和每 门课程的课时
讲解需求说明
指导——查询获得各学期课程信息2-2
• 实现思路：
– 检测是否存在存储过程 IF EXISTS (SELECT * FROM sysobjects – 创建存储过程，通过联接查询获得结果 WHERE name = 'usp_grade_subject' ) DROP PROCEDURE usp_grade_subjBiblioteka Baiduct – 编译、执行，获得结果
练习——使用存储过程查看表信息
• 需求说明：
– 查看Student表中的列、约束信息
– 比较下面三个系统存储过程输出的数据库信 息的特点
• sp_columns • sp_helpconstraint • sp_help
• 提示：
– 观察这3个系统存储过程的执行结果
完成时间：15分钟
如何创建存储过程
调用带参数默认值的存储过程
EXEC usp_unpass 'C# OOP'
或
考试及格线默认为标准的60分
EXEC usp_unpass @subName = 'C# OOP'
如果有默认值的参数出现在没有默认值参 数的前面，那么需要指定参数名为其赋值
常见错误2-1
CREATE PROCEDURE usp_unpass @score int = 60, @subName varchar(50) AS …… GO EXEC usp_unpass @subName= 'Java Logic' default, 'Java Logic' , 'Java Logic'
完成时间：20分钟
共性问题讲解
共性问题集中讲解
常见调试问题及解决办法 代码规范问题
存储过程参数
int rt, sum;
• 存储过程的参数分两种 ：
– 输入参数 – 输出参数
rt =add(5, 8, out sum); 传入参数值 -- C#方法
传出参数值
int add (int a, int b, out int c) { int s=1; c=0; if (a<0 || b<0) s=0; else c=a+b; return s; 返回结果
列出当前系统中的数据库
修改数据库的名称(单用户访问)
EXEC sp_helptext 'view_Student_ Result_Info'
EXEC sp_stored_procedures 查看当前数据库中的存储过程
演示案例1：常用的存储过程
常用的扩展存储过程
• xp_cmdshell
USE master – 可以执行DOS命令下的一些的操作 创建文件夹D:\bank GO – 以文本行方式返回任何输出 EXEC xp_cmdshell 'mkdir D:\bank', NO_OUTPUT IF EXISTS(SELECT * FROM sysdatabases [NO_OUTPUT] EXEC xp_cmdshell DOS命令 WHERE name='bankDB') DROP DATABASE bankDB GO CREATE DATABASE bankDB ( … ) 查看文件夹D:\bank 创建数据库bankDB，要求保存在D:\bank GO EXEC xp_cmdshell 'dir D:\bank\' -- 查看文件
调用存储过程
• 调用存储过程的语法
EXECUTE 过程名 [参数]
或
EXEC
过程名 [参数]
如果执行存储过程的语句是批处理中的第一个语句，则 可以不指定 EXECUTE 关键字
常用的系统存储过程
sp_databases EXEC sp_renamedb 'MyBank','Bank' USE MySchool GO sp_tables EXEC sp_columns Student EXEC sp_help Student EXEC sp_helpconstraint Student 当前数据库中查询的对象的列表 返回某个表列的信息 查看表Student的信息 查看表Student的约束 查看视图的语句文本
第七章
存储过程
回顾
• 管理控制事务的常用T-SQL语句有哪些？
• 什么是视图？它有什么好处？
• T-SQL中哪个关键字可以实现用索引查询 数据？
本章任务
• 使用存储过程完成以下功能：
– 查看各学期的课程信息 – 查询指定学期开设的课程信息 – 获得指定学期开设的课程数目 – 插入新增课程记录
本章目标
• 输入参数：
– 向存储过程传入值
• 输出参数：
– 调用存储过程后，传出执 行结果
}
带输入参数的存储过程
• 变更上例的需求
EXEC usp_unpass 'C# OOP' , 50 CREATE PROCEDURE usp_unpass 输入参数：考试及格线 @subName varchar(50), 或 @score int 输入参数：课程名称 AS 上述存储过程添加2个输入参数 EXEC usp_unpass @score=50, @subName='C# OOP' DECLARE @date datetime --最近考试时间 @score：考试及格线 DECLARE @subjectNo int --课程编号 SELECT @subjectNo=SubjectNo FROM Subject @subName：课程名称 Where SubjectName = @subName …… SELECT StudentName,Student.StudentNo,StudentResult FROM Student INNER JOIN Result ON Student.StudentNo = Result.StudentNo “C# OOP ”课程最近一次考试及格线 WHERE StudentResult < @score AND ExamDate = @date 降分后，田园（39分）仍然没有通过 AND SubjectNo=@subjectNo 查询某课程最近一次考试没有 GO 演示案例3：创建、执行输入参数的存储过程 通过的学员