数据库(SQLServer)基本语法
1)存储过程
2)函数
模拟场景:
某所高中学校的一套内部管理系统,设有学生管理,班级管理,教师管理,教室管理以及课程管理
以下例子为教师管理。
表管理说明:
①Teacher -- 主表(记录所有教师最新信息)
②TeacherChange -- 登记表(记录所有教师业务办理历史:1-入职,2-调职,3-基本信息变动,4-离职,5-调动)
③TeacherEvent -- 履历表(记录所有教师所有历史信息)
-- 教师主表(记录所有教师最新信息)
create table Teacher
( TeaId INT, -- 教师ID
Name nvarchar(200), -- 教师名称
TeaNo nvarchar(200), -- 教师工号
Post int, -- 职务(教师,班主任,教导主任,副校长,校长)
CourID int, --所授科目(语文,数学,英语,地理,历史,政治,生物,化学,体育)
Level int, -- 负责年级(高一,高二,高三)
Class nvarchar(200), -- 授课班级ID(集合,例如:1,2,3) 表示该教师同时给高一1班,高一2班,高一3班授课
Class1 nvarchar(500), -- 授课班级名称(集合,例如:1班,2班,3班)
IDNo varchar(18), -- 身份证号
BirthDay datetime, -- 出生日期
Age int, -- 年龄
Sex nvarchar(5), -- 性别
MaritalStatus int, -- 婚姻状态
CreateDate datetime, -- 就职日期
Disabled int, -- 是否离职
DisabledDate datetime, -- 离职日期
Remark nvarchar(500) -- 备注说明
)
-- 教师登记表(记录所有教师业务处理历史)
create table TeacherChange
( ID int, -- 主键ID
xType int, -- 业务类型:1-入职,2-调职,3-基本信息变动,4-离职,5-调动
TeaId INT, -- 教师ID
Name nvarchar(200), -- 教师名称
TeaNo nvarchar(200), -- 教师工号
Post int, -- 职务(教师,班主任,教导主任,副校长,校长)
CourID int, --所授科目(语文,数学,英语,地理,历史,政治,生物,化学,体育)
Level int, -- 负责年级(高一,高二,高三)
Class nvarchar(200), -- 授课班级ID(集合,例如:1,2,3) 表示该教师同时给高一1班,高一2班,高一3班授课
Class1 nvarchar(500), -- 授课班级名称(集合,例如:1班,2班,3班)
IDNo varchar(18), -- 身份证号
BirthDay datetime, -- 出生日期
Age int, -- 年龄
Sex nvarchar(5), -- 性别
MaritalStatus int, -- 婚姻状态
CreateDate datetime, -- 就职日期
Disabled int, -- 是否离职 0/NULL:在职 1:离职
DisabledDate datetime, -- 离职日期
Remark nvarchar(500), -- 备注说明
ChangeDate datetime, -- 变动日期
RegBY int, -- 登记人
RegDate datetime, -- 登记时间
Closed int, -- 是否处理
ClosedBy int, -- 处理人
ClosedTime datetime -- 处理时间
)
-- -- 教师履历表(记录所有教师所有历史信息)
create table TeacherEvent
( ID int, -- 主键ID
xType int, -- 业务类型:1-入职,2-调职,3-基本信息变动,4-离职,5-调动
BeginDate datetime, -- 该条履历开始日期
EndDate datetime, -- 该条履历结束日期
TeaId INT, -- 教师ID
Name nvarchar(200), -- 教师名称
TeaNo nvarchar(200), -- 教师工号
Post int, -- 职务(教师,班主任,教导主任,副校长,校长)
CourID int, --所授科目(语文,数学,英语,地理,历史,政治,生物,化学,体育)
Level int, -- 负责年级(高一,高二,高三)
Class nvarchar(200), -- 授课班级ID(集合,例如:1,2,3) 表示该教师同时给高一1班,高一2班,高一3班授课
Class1 nvarchar(500), -- 授课班级名称(集合,例如:1班,2班,3班)
IDNo varchar(18), -- 身份证号
BirthDay datetime, -- 出生日期
Age int, -- 年龄
Sex nvarchar(5), -- 性别
MaritalStatus int, -- 婚姻状态
CreateDate datetime, -- 就职日期
Disabled int, -- 是否离职
DisabledDate datetime, -- 离职日期
Remark nvarchar(500), -- 备注说明
ChangeDate datetime, -- 变动日期
RegBY int, -- 登记人
RegDate datetime, -- 登记时间
Closed int, -- 是否处理
ClosedBy int, -- 处理人
ClosedTime datetime -- 处理时间
)
创建一个存储过程,用来执行教师从入职,信息变动,调职到离职的所有业务处理
执行存储过程: EXEC ToTeacherDO 1,2,1,0
CREATE PROCEDURE [dbo].[ToTeacherDO]
(@id int, -- 对应数据
@McType int,-- 执行业务:1-执行限制判断 2-执行业务处理
@UserID int,-- 执行人,操作人
@RetVal VARCHAR(500) = 0 output -- 返回值:整个存储过程执行之后会返回一个值,用来判断整个存储过程是否成功执行
)
as
BEGIN
/** 设置动态参数并赋值
--DECLARE @11 nvarchar(200),-- 动态参数1
-- @22 INT,-- 动态参数2
-- ...
-- 赋值①
select @11 = Name,
@22 = Sex
from Teacher where TeaId = @id
-- 赋值②
select @11 = (select Name from Teacher where TeaId = @id)
select @22 = (select Sex from Teacher where TeaId = @id)
**/
declare @Type int, --业务处理ID
@BaseID int -- 履历最新历史
select @Type = (select xType from TeacherChange where id = @id)
-- 执行前限制判断
if @McType = 1
begin
-- 例如:入职时,身份证号码不能为空!
IF EXISTS(
SELECT 1
FROM TeacherChange
WHERE IDNo is null
and id = @id
and isnull(xType,0)=1
)
Begin
-- SET @RetVal=20002 -- 特殊表内:配置该编码对应的弹出限制
set @RetVal= N'学生身份证号码不能为空!'
Return @RetVal -