自定义函数(sqlserver)

• 返回学生的年龄

select sno,name,datediff(yyyy,birthday,getdate()),datediff(yyyy,join
time,getdate()) from student

标量函数（只返回一个值）

create function fun_getYearDiff(@date datetime) returns int 
as
begin
    return datediff(yyyy,@date,getdate())
end
-- 利用标量函数返回学生的年龄
select sno,name,dbo.fun_getYearDiff(birthday),dbo.fun_getYearDiff(jo
intime) from student

• 将身高段封装成函数，简化表达式查询

内嵌 表值函数

• 输入学号，获得该学生选课信息

create function function_getCnameBySno(@sno char(8))
returns table
as
 return
 select cname from course c join sc on c.cno=sc.cno join student
  s on sc.sno = s.sno
  where s.sno = @sno

select * from db.function_getCnameBySno('09010101')

多语句表值函数

• 输入专业关键字，查询该专业各班学生人数

select c.cname,count(*) from student s join class c on s.cno 
 = c.cno and descript like '%电商%'
 group by s.cno,c.cname
union
select '电商专业',count(*) from student s join class c on s.cno 
 = c.cno and descript like '%电商%'
--- 
create function fun_gerProfressionInfo(@name varchar(10))
returns @result table(名称 varchar(8 ),人数 int) 
as 
begin 
 insert into @result select c.cname,count(*) from student s 
    join class c on s.cno 
    = c.cno and descript like '%'+@name+'%'
    group by s.cno,c.cname
 insert into @result select @name+'专业',count(*) from @result
 return
end

select * from dbo.fun_gerProfressionInfo('电商')

---

• 视图 -->配合权限管理简化查询
• 触发器 -->自动执行的任务
• 存储过程 -->既返回表，又返回变量（输出参数）
• 自定义函数：针对字段表达式的优化->标量值函数，在同一张表查询自身的基
  础上另外有一些简单的计算->多语句表值函数，如果查询的结果还需要和其他查
  询进行子连接查询等不是固定组合的操作，自定义函数能够实现，存储过程不能
  实现

事务

• A 原子性
  同一事务中，要么SQL语句全部执行成功，要么全部失败。
• C 一致性
  又叫正确性，总额正确匹配。
• I 独立性
  又叫隔离性，一个用户操作一个资源的时候，其他用户不能操作的程度叫做隔离
  级别。
• D 持久性
  事务一旦取消或者提交就永久保存结果

①
begin transaction
    update student
    set = '男'
    where sno='09010101'
②
rollback
commit

• 隔离级别；
  set transaction isolation level
  未提交读(隔离事务的最低级别，只能保证不读取物理上损坏的数据)
  read uncommitted
  已提交读（数据库引擎的默认级别）
  read committed
  可重复读
  repeatable read
  快照
  snapshot
  可序列化（隔离事务的最高级别，事务之间完全隔离）
  serializable

• 脏读（未提交的依赖关系），不可重复读(不一致的分析)，幻想读

1. 脏读：用户2读取到用户1未提交数据，结果用户1取消了，用户2得到的数据
   是错误数据。
2. 不一致的分析（修改造成）：用户2连续两次查询结果，期间用户1修改了数
   据，则用户2的两次查询结果不一致。
3. 幻读（插入或删除）：用户2插入（删除）了数据未确认，期间用户查看到了
   新插入或删掉后的结果。
4. 丢失更新（修改造成）。

锁

按粒度分表锁，行锁等。
按优先级分共享锁（行锁），排它锁（表锁）等。

• 表锁（已提交读 默认级别以上）

在最低隔离级别下，用户1开启事务查询带表锁，用户2可以查询同一张表；在默
认级别以上用户2不能查询同一张表，可以查询其他表。

begin tran 
select * from student(tablockx)

• 行级锁（可重复读级别以上）

在默认级别及以下，用户1事务查询带行级锁，用户2可以更新同一张表中的同样
的行，可重复读级别及以上，用户1事务查询带行级锁，用户2只能更新其他行。
begin tran 
select * from student(rowlock)
where sno = '09010101'