sql存储过程详细解释，非常有用

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 =