文享日志

(SQLServer)存储过程,触发器,自定义函数

数据库 SQLServer

发表于2018年01月13日21:57:07

0条评论 155次阅读

存储过程

概念:

       存储过程是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。


特点:

  1. 允许模块化程序设计
  2. 执行速度更快
  3. 减少网络流量
  4. 可作为安全机制使用
  5. 减轻操作人员和程序设计者的劳动强度


问题示例:

通过学生学号来查询学生的姓名、年龄、系名。

创建存储过程
Create Procedure Pro_Qsinf
@sno_in char(8) = 'S2' , @sname_out char(8) output , 
@sage_out int output , @dept_out char(10) output 
as select @sname_out = sn , @sage_out = age , @dept_out = dept 
     from S where sno = @sno_in

执行存储过程
//select @sno_in = 'S4';
declare
@sno_in char(8) , @sname_out char(8),
@sage_out int , @sdept_out char(10)
exec Pro_Qsinf default , @sname_out output , @sage_out output , @sdept_out output
print @sname_out
print @sage_out
print @sdept_out


触发器

概念及特点:

      触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。


作用:

  1. 通过数据库中的相关表实现级联更改
  2. 可以强制比用check约束定义的约束更为复杂的约束
  3. 评估数据修改前后的表状态,并且根据其差异采取对策
  4. 一个数据库表中的多个同类触发器(INSERT、UPDATE或者DELETE)允许采取多个不同的对策以响应同一个修改语句。


触发器类型

update、insert、delete


问题示例:

若修改学生表(S)一学生的学号,则SC表中与该学生相关的学号自动修改。

Create Trigger Tri_S
  on S for update as if update(sno)
  begin
    declare @sno_new char(2) , @sno_old char(2)
    select @sno_new = sno from inserted
    select @sno_old = sno from deleted
    update sc set sno = @sno_new where sno = @sno_old
  end


自定义函数

概念:

       用户定义函数是由一个或多个 Transact-SQL 语句组成的子程序,可用于封装代码以便重新使用。


分类:

        表值函数、标量值函数。

表值函数。表值函数返回table,表值函数又有内嵌表值函数和多语句表值函数。

标量值函数。返回在returns子句中定义的类型的单个数据值。


问题示例:

1、创建一个标量值函数Score_FUN,根据学生姓名和课程名查询成绩。

创建函数
Create Function Score_FUN(@sname_in char(8),@cname_in char(10))
Returns tinyint
as 
  begin
      declare @score_out tinyint
      select @score_out = score from s,sc,c
      where s.sno = sc.sno and c.cno = sc.cno and sn = @sname_in and cn = @cname_in
      return(@score_out)
  end

执行函数
  declare @s_score tinyint
  exec @s_score = Score_FUN '钱尔','编译原理'
  Print '钱尔的编译原理成绩是'+STR(@s_score)


2、根据学生姓名查询该生所有选课的成绩(内嵌表值函数)

Create Function S_Score_FUN(@Sname_in char(8))
return table
as 
  return (select cn , score from s,sc,c
          where s.sno = sc.sno and c.cno = sc.cno and sn = @Sname_in)


3、根据课程名查询所有选择该课程学生的成绩信息,包括:学号,姓名,性别,成绩。结果按成绩降序排列(多语句函数)

//创建函数
Create Function All_Score_FUN(@cname_in char(10))
returns 
       @all_score_tab table (
                sno char(2) primary key ,
                sn char(8) not null ,
                sex char(2) ,
                score tinyint
       )
as
  begin
       insert @all_score_tab
       select s.sno , sn , sex , score from s , c , sc 
         where s.sno = sc.sno and c.cno = sc.cno and cn = @cname_in
       return 
  end

//执行函数
select *  from  All_Score_FUN('微机原理');




👍 0  👎 0
共有0条评论

发表新评论

提交

广告展示

腾讯云推广 阿里云推广