存储过程及触发器
一、 存储过程
1. 用户注册的存储过程(add_user)
CREATE PROCEDURE add_user
@u_nickname varchar(14),
@u_password char(32),
@u_sex char(1),
@u_email varchar(20),
@u_intro varchar(200)
AS
DECLARE @u_id char(10)
SET @u_id = (SELECT cast(floor(rand()*10000000000)as bigint))
INSERT INTO tb_user
VALUES(@u_id,@u_nickname,@u_password,@u_sex,@u_email,@u_intro,GETDATE())
GO
-- 测试
EXECUTE add_user 'abc','123','0','1622135447@qq.com','暂无'
2. 用户选择词典的存储过程(choose_dictionary)
该存储过程用于用户来选择词典,选择四级/六级词典(01/02),同时更新计划表
(tb_plan),默认设定计划每天背词数为 50 个。
CREATE PROCEDURE choose_dictionary
@u_id char(10),
@p_select char(2),
@p_daywords int = 50
AS
DECLARE @p_id char(8), @p_leave_total int, @p_total int, @p_leave_days int
SET @p_id = (SELECT cast(floor(rand()*100000000)as bigint))
SELECT @p_total = COUNT(*) FROM all_words WHERE w_id like @p_select + '%'
SET @p_leave_total = @p_total - (SELECT COUNT(*) FROM tb_word_record WHERE u_id=@u_id
AND w_id=@p_select+'%')
SET @p_leave_days = ceiling(cast(@p_leave_total as float)/@p_daywords)
IF EXISTS (SELECT * FROM tb_plan WHERE u_id=@u_id)
UPDATE tb_plan SET p_select=@p_select, p_daywords=@p_daywords,
p_leave_total=@p_leave_total,
p_total=@p_total, p_leave_days=@p_leave_days, update_time=GETDATE()
WHERE u_id=@u_id
ELSE
INSERT INTO tb_plan