发布时间 : 星期一 文章sql存储过程详细解释，非常有用更新完毕开始阅读d10a4d36fe00bed5b9f3f90f76c66137ee064fac
use jxgl
--首先判断有没有已经建立up_getallstudents存储过程，有则先删除 if exists
(select name from sysobjects where name = 'up_getallstudents' and type = 'p' )
drop procedure up_getallstudents
--编写存储过程up_getallstudents,用于获取学生表students的所有记录 create procedure up_getallstudents as
select * from students
--使用execute执行存储过程up_getallstudents exec up_getallstudents --也可写成
execute up_getallstudents
--编写一个存储过程up_insertstudent,完成学生表students数据的插入 --1、不带默认值的参数
create procedure up_insertstudent
@sid varchar(15), @sname varchar(30), @ssex char(10), @sbirth datetime, @sbirthplace varchar(300) as begin
insert into students
(stu_id, stu_name, stu_sex, stu_birth, stu_birthplace) values
(@sid, @sname, @ssex, @sbirth, @sbirthplace) end
exec up_insertstudent '200712110111', '肖玉峰', '男', '1975-02-05', '山 东省滕州市木石镇'
--等同于
exec up_insertstudent
@sname = '肖玉峰', @sid = '200712110111', @ssex = '男', @sbirth = '1975-02-05', @sbirthplace = '山东省滕州市木石镇'
drop procedure up_insertstudent
delete students where stu_name = '肖玉峰'
insert into students(stu_id, stu_name, stu_sex, stu_birth, stu_birthplace)
values('200712110110', '马缪', '男', '1986-010-17 00:00:00.000', '广东广 州')
--编写一个存储过程up_delstudentbyname,根据输入的学生姓名，删除该学生记录 if object_id('up_delstudentbyname', 'p') is not null drop procedure up_delstudentbyname go
create procedure up_delstudentbyname @sname varchar(30) as begin
delete from students where stu_name = @sname end
--调用存储过程的代码如下：
exec up_delstudentbyname '马缪' select * from students
--编写一个存储过程up_getstuinformationbyname,根据输入的学生姓名，显示该学生的 学号、姓名、课程名和成绩
if object_id('up_getstuinformationbyname', 'p') is not null drop procedure up_getstuinformationbyname go
create procedure up_getstuinformationbyname @sname varchar(30) as begin
select \.stu_id, stu_name, cour_name, score from students \, courses \, course_score \
where \.stu_id = \.stu_id and \.cour_id = \.cour_id and stu_name = @sname
end
--调用存储过程的代码如下：
exec up_getstuinformationbyname '仇立权' --ok
--2、带默认值的参数
--编写一个存储过程up_insertstuwithdefault，给参数定义默认值，完成学生表 students数据的插入
drop procedure up_insertstuwithdefault go
create procedure up_insertstuwithdefault
@sid varchar(15), @sname varchar(30), @ssex char(10) = '男',
@sbirth datetime, @sbirthplace varchar(300) = '', @semail varchar(50) = '' as begin
insert into students
(stu_id, stu_name, stu_sex, stu_birth, stu_birthplace, stu_email) values
(@sid, @sname, @ssex, @sbirth, @sbirthplace, @semail) end
--调用存储过程的代码如下：
exec up_insertstuwithdefault @sid = '11', @sname = '赵小乐', @sbirth = '1976-07-05'
select * from students
--3、带输出参数
--在创建存储过程时，可以用关键字OUTPUT来创建一个输出参数，另外，调用时也必须给出 OUTPUT关键字
--根据给定的学生姓名，获取该生的平均成绩 use jxgl go
create procedure up_getAvgScorebyname @aname varchar(30), @avgscore int output as begin
select @avgscore = avg(score) from students \, course_score \where \.stu_id = \.stu_id and \.stu_name = @aname end
--调用过程代码
declare @avgscore int
---@avgscore可以改成任意变量名
--ok
exec up_getAvgScorebyname '仇立权', @avgscore output
print @avgscore
--不能写成print '@avgscore' 或print （'@avgscore'），这是输出字符串
--等同于
select avg(score) from students s, course_score c where s.stu_id = c.stu_id and s.stu_name = '仇立权'
--4、带返回值的存储过程
--（）print语句可以将用户定义的消息返回给客户端
--编写一个存储过程up_insertstudent2,在插入学生数据前，先判断一下学号是否存在， --如果存在，输出“要插入的学生的学号已经存在”；否则，插入学生数据，并返回”恭喜，数据 插入成功“
create procedure up_insertstudent2
@sid varchar(15), @sname varchar (30), @ssex char(10) = '男', @sbirth datetime as begin
if exists(select * from students where stu_id = @sid) print ('要插入的学生的学号已经存在')
--也可写成raiserror('要插入的学生的学号已经存在', 16, 1) else begin
insert into students
(stu_id, stu_name, stu_sex, stu_birth) values
(@sid, @sname, @ssex, @sbirth) print('恭喜，数据插入成功')
--raiserror('恭喜，数据插入成功', 16, 10) end end
drop procedure up_insertstudent2 select * from students --调用过程代码如下：
exec up_insertstudent2 @sid = '13', @sname = '张小飞', @sbirth = '1983-02-01' --ok
exec up_insertstudent2 @sid = '10', @sname = '张小龙', @sbirth =