sql 函数与触发器（old new）

1.创建一个简单的储存过程getallstudentinfor，用于获取所有的学生信息
Create Function [dbo].[GetAllstudentinfor]
()
Returns Table
As
Return(Select * From[student)

2.创建一个使用output参数的储存过程getspecialteacherrank用于获取指定教师的职称
CREATE FUNCTION getspecialteacherRank(@sco int )
RETURNS int
AS
BEGIN
return
(select tea_rank
from teacher
where tea_id=@sco)
END

3.为学生创建一个insert触发器，挡在学生表上插入一行数据表明新增一名学生时，自动更新班级表的人数字段的数值
create trigger trig_insert1
on student after insert
as
begin
declare @stuNumber int;
declare @newcla_id varchar(10);
--select @newcla_id=cla_id from inserted;
update class
set number = (select count()
from student
where CLA_ID=(select cla_id from inserted ) )
where CLA_ID=(select cla_id from inserted ); --把更新后总的学生数插入到class表中
end
4.为学生表创建一个update触发器，在学生表修改班级的班号时，自动修改原有班级及新班级表中对应办号的人数
create trigger trig_insert2
on student after update
as
begin
declare @stuNumber int;
declare @newcla_id varchar(10);
--select @newcla_id=cla_id from inserted;
update class
set number = (select count()
from student
where CLA_ID=(select cla_id from deleted ) )
where CLA_ID=(select cla_id from deleted ); --把更新前总的学生数插入到class表中
update class
set number = (select count()
from student
where CLA_ID=(select cla_id from inserted ) )
where CLA_ID=(select cla_id from inserted )
end
5.为学生创建一个delete触发器。当在学生表上删除一行数据（表示只扫减少一名学生时），自动删除该学生的成绩信息
create trigger trig_insert3
on student after delete
as
begin
declare @stuNumber int;
declare @newcla_id varchar(10);
--select @newcla_id=cla_id from inserted;
update class
set number = (select count()
from student
where CLA_ID=(select cla_id from deleted ) )
where CLA_ID=(select cla_id from deleted ); --把更新前总的学生数插入到class表中

delete grade
from grade
where id=(select id from deleted)

end
6.有一串‘human railway professional college’,对其进行如下操作，去掉左边右边空格，将字符串全部转为大写，了解整个字符串长度，提取左边6个字符，提取hunan子串
select LEFT(UPPER(LTRIM(' Hunan railway professional college ')),6)

7.有两个数值123.45和-123.45，使用下列数学函数求值ABS（），SIGN（），CEILING（），FLOOR（），ROUND（）
select ABS(123.45),ABS(-123.45)
select SIGN(123.45),SIGN(-123.45)
select ceiling(123.45),ceiling(-123.45)
select floor(123.45),floor(-123.45)
select round(123.45,2),round(-123.45,2)

8.利用日期和时间函数获取某个学生的出生年份，计算其年龄
select id,name,DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2') as age
FROM student

sql 函数与触发器 （old new）

推荐阅读更多精彩内容

sql 函数与触发器（old new）