1.什么是存储过程?
q 存储过程(procedure)类似于C语言中的函数
q 用来执行管理任务或应用复杂的业务规则
q 存储过程可以带参数,也可以返回结果
q 存储过程可以包含数据操纵语句、变量、逻辑控制语句等
2.存储过程的优点
q 执行速度更快
q 允许模块化程序设计
q 提高系统安全性
q 减少网络流通量
3.常用的存储过程
q 系统存储过程
q 由系统定义,存放在master数据库中
q 类似C语言中的系统函数
q 系统存储过程的名称都以“sp_”开头或”xp_”开头
q 用户自定义存储过程
q 由用户在自己的数据库中创建的存储过程
q 类似C语言中的用户自定义函数
4.常用的扩展存储过程:xp_cmdshell
q 可以执行DOS命令下的一些的操作
q 以文本行方式返回任何输出
q 调用语法:
q EXEC xp_cmdshell DOS命令 [NO_OUTPUT]
5.存储过程的语法
CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 = 默认值 OUTPUT,
…… ,
@参数n 数据类型 = 默认值 OUTPUT
AS
SQL语句
GO
6.基本常识要有的,当然最重要的还是举例说明喽
(1)没有参数的存储过程
q 查看本次考试平均分以及未通过考试的学员名单
CREATE PROCEDURE proc_stu
AS
DECLARE @writtenAvg float,@labAvg float
SELECT @writtenAvg=AVG(writtenExam),
@labAvg=AVG(labExam) FROM stuMarks
print '笔试平均分:'+convert(varchar(5),@writtenAvg)
print '机试平均分:'+convert(varchar(5),@labAvg)
IF (@writtenAvg>70 AND @labAvg>70)
print '本班考试成绩:优秀'
ELSE
print '本班考试成绩:较差'
print '--------------------------------------------------'
print ' 参加本次考试没有通过的学员:'
SELECT stuName,stuInfo.stuNo,writtenExam,labExam
FROM stuInfo INNER JOIN stuMarks ON
stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<60 OR labExam<60
GO
(2)带输入参数的存储过程
由于每次考试的难易程度不一样,每次笔试和机试的及格线可能随时变化(不再是60分),这导致考试的评判结果也相应变化
CREATE PROCEDURE proc_stu
@writtenPass int,
@labPass int
AS
print '--------------------------------------------------'
print ' 参加本次考试没有通过的学员:'
SELECT stuName,stuInfo.stuNo,writtenExam,
labExam FROM stuInfo
INNER JOIN stuMarks ON
stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass
OR labExam<@labPass
GO
(3)带输出参数的存储过程
修改上例,返回未通过考试的学员人数
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT,
@writtenPass int=60,
@labPass int=60
AS
……
SELECT stuName,stuInfo.stuNo,writtenExam,
labExam FROM stuInfo INNER JOIN stuMarks
ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass
OR labExam<@labPass
SELECT @notpassSum=COUNT(stuNo)
FROM stuMarks WHERE writtenExam<@writtenPass
OR labExam<@labPass
GO
(4)调用带输出参数的存储过程
DECLARE @sum int
EXEC proc_stu @sum OUTPUT ,64
print '--------------------------------------------------'
IF @sum>=3
print '未通过人数:'+convert(varchar(5),@sum)+ '人,
超过60%,及格分数线还应下调'
ELSE
print '未通过人数:'+convert(varchar(5),@sum)+ '人,
已控制在60%以下,及格分数线适中'
GO