当前位置:Gxlcms > 数据库问题 > SQLServer------存储过程的使用

SQLServer------存储过程的使用

时间:2021-07-01 10:21:17 帮助过:27人阅读

www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html

 例子:

1.学生表

CREATE TABLE [dbo].[Student](
    [StudentID] [int] IDENTITY(1,1) NOT NULL,  --主键
    [Number] [varchar](15) NULL,   --学号
    [Name] [nchar](8) NULL,  --学生姓名
    [ClassID] [int] NOT NULL  --学生所在班级ID
)

插入学生数据:
declare @count int =1;
while @count < 100
begin
insert into Student select @count,学生+ convert(varchar,@count,15),cast(ceiling(rand() * 5) as int)
set @count = @count + 1; 
end

2.教师表

create table Teacher(
    [TeacherID] [int] IDENTITY(1,1) NOT NULL, --老师ID
    [TeacherName] [nchar](8) NULL,  --老师名称
    [ClassID] [int] NOT NULL  -- 老师所教的班级ID
)

插入数据:
insert into Teacher select 陈老师, 1
insert into Teacher select 李老师, 3
insert into Teacher select 王老师, 2
insert into Teacher select 赵老师, 5

3.班级表

create table Class(
    [ClassID] [int] IDENTITY(1,1) NOT NULL,   --班级ID
    [Code] [varchar](3) NULL,   --班级编号
    [ClassName] [nchar](8) NULL  --班级名
)

插入班级数据:
insert into Class select 003,计算机3班
insert into Class select 001,计算机1班
insert into Class select 002,计算机2班
insert into Class select 005,计算机5班
insert into Class select 004,计算机4班

4.创建存储过程

create proc proc_getStudentRecord(
    @pageIndex int,         --页码
    @pageSize int,          --每页信息数
    @name nchar(8) output   --任课老师
)
as
    declare @startRow int, @endRow int
    set @startRow = (@pageIndex - 1) * @pageSize + 1
    set @endRow = @startRow + @pageSize -1
    
    select s.Number,s.Name,b.Code,b.ClassName from(
        select *, row_number() over (order by StudentID asc) as num from Student a where exists(select 1 from Teacher t where a.ClassID = t.ClassID and t.TeacherName = @name)
    ) s 
    join
    Class as b on
    b.ClassID = s.ClassID
    where s.num between @startRow and @endRow;
    
go

4.执行存储过程

exec proc_getStudentRecord 1,5,陈老师

 

SQLServer------存储过程的使用

标签:order   insert   asc   begin   arch   table   tween   rom   创建   

人气教程排行