欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

个人笔记：数据库——存储过程&触发器

程序员文章站 2024-03-20 23:14:52
...

本文仅供参考学习使用，谢谢

文章目录

存储过程

利用T-SQL 以存储过程和触发器实现编程

一、存储过程的定义

  • 存储过程就是利 Transact-SQL语言编写的存储在数据库内的可以执行一些特殊的或常用的数据处理的程序，它是数据库对象之一，可以通过编译在数据库后台执行，是数据库端编程的重要技术。
    即： 存储过程是存放在数据库服务器上的预先定义与编译好的T-SQL语句集合，是一个独立的数据库对象。
  • 我们可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。
  • 存储过程在创建时就被编译和优化，在第一次执行时进行语法检查和编译。编译好的版本存储在过程高速缓存*应用程序多次调用

二、存储过程的特点

  • 实现了模块化编程。
  • 存储过程具有对数据库立即访问的功能。
  • 使用存储过程可以加快程序的运行速度。
  • 使用存储过程可以减少网络流量。
  • 使用存储过程可以提高数据库的安全性。
  • 存储过程由应用程序**，而不是由系统自动执行
  • 存储过程可以接受输入参数和返回值。

三、存储过程的使用

1. 创建存储过程

  1. 存储过程的创建
    存储过程的定义主要包括两部分：
    • 一是过程名及参数的说明
    • 二是过程体的说明
CREATE PROCEDURE <存储过程名>
[<参数列表>]
AS	
<过程体>
  • 参数列表：由一个或多个参数说明组成，每个参数说明包括参数名和参数的数据类型，存储过程可以没有任何参数。
//参数格式为：
@ 参数名 数据类型 [=缺省值] [OUTPUT]

//存储过程可以分为无参数和有参数两种
//有参数的又可以分为	带OUTPUT	和	不带OUTPUT	两种

  • 过程体：是实现存储过程功能的一组T-SQL语句，可以包含任意多的SQL语句。

    1. 但sql语句中不能使用CREATE（VIEW、TRIGER、DEFAULT、RULE、PROCEDURE等）语句，同时要慎重使用其他的CREATE、DROP等语句。
    2. 为了使存储过程的设计更方便，功能更强大。可使用流程控制语句，主要有以下几种：
      • 赋值语句：可将SQL表达式的值赋值给局部变量。
      • 分支语句：用于设计分支程序。如： IF语句、CASE
        语句等。
      • 循环语句：如：WHILE等语句。
      • 调用存储过程语句：CALL，或EXECUTE和从存储过程返回语句RETURN。
    3. 在存储体中除了可以使用流程控制语句外，还可以使用游标。

eg：

//创建一个不带参数的存储过程，完成查询每位学生的选课情况及其成绩。
CREATE PROCEDURE Proc1 
AS
SELECT Student.Sno,Sname,Course.Cno,Cname,Grade  
     FROM Student,Course,SC   
       WHERE Student.Sno=SC.Sno 
         AND Course.Cno=SC.Cno

eg：

//创建一个带参数的存储过程，完成查询指定课程的选修情况。
CREATE PROCEDURE Proc2 @x CHAR(2)
AS
SELECT Student.Sno,Sname,Course.Cno,Cname,Grade  
   FROM Student,Course,SC 
    WHERE Student.Sno=SC.Sno 
      AND Course.Cno=SC.Cno and SC.Cno=@x 
        ORDER by SC.Sno

eg：

//创建一个带参数并有返回值的存储过程，完成输出指定学号的学生的‘2’号课程的成绩，并将结果，赋给一输出参数。
CREATE PROCEDURE Proc3 
    @x CHAR(5),@vgrade INT OUTPUT
AS 
    SELECT @vgrade=Grade FROM SC  
         WHERE Sno=@x AND Cno=‘2’
    RETURN

2. 执行存储过程

在SQL Server中，使用EXECUTE语句执行存储过程。
EXECUTE语句一般格式如下：

EXEC  [UTE] <存储过程名> [ [过程参数变量=] { 值| 变量 [OUTPUT] }

eg：

//执行例2定义的存储过程。
EXEC Proc2 @x=’3’   
//或   
EXEC Proc2  ’3’

eg：

//执行例8定义的存储过程。
DECLARE @v1 CHAR(5),@v2 INT 
SELECT @v1=’00101’
EXEC Proc3 @v1,@v2 OUTPUT
SELECT @v2

3. 修改和删除存储过程

修改存储过程：

ALTER PROC[EDURE] <存储过程名> {同定义}

删除存储过程：DROP PROCEDURE

DROP PROC	[EDURE] <存储过程名>

四、存储过程的种类

  • 存储过程分系统存储过程、扩展存储过程和用户自定义的存储过程。
  • 系统存储过程是SQL Server 自身配备的具有特殊功能和用途的存储过程，SQL Server 也配备了部分扩展存储过程。
  • 用户自定义的存储过程是用户使用Transact-SQL编写的用户常用的一些程序，这些程序代存储在SQL Server数据库中，并可以通过编译行。
  • 调用存储过程类似调用系统函数，可以输入参数，也可以通过参数输出执行的结果，大大提了代码的重用性、安全性和执行速度。

1.系统存储过程(sp_)：

  • SQL Server系统存储过程是为管理员而提供的，SQL Server安装时在master数据库中创建并由系统管理员拥有。使用户可以很容易地从系统表中取出信息，管理数据库，并执行涉及更新系统表的其他任务。系统存储过程命令均以sq_打头，其作用进行数据库管理。

  • SQL Server提供了许多系统存储过程以方便检索和操纵存放在系统表中的信息，系统存储过程可以在任意一个数据库中执行。

  • 例如，常用的系统存储过程有：

    • sp_ helpdb(database_name)：返回指定数据库信息
    • sp_help(object)：返回指定数据库对象的信息
    • sp_addlogin：建立SQL Server用户帐号
    • sp_datatype_info：返回由当前环境支持的数据类型的信息
    • sp_monitor：按一定格式显示的系统全局变量的当前值

2.扩展存储过程(xp_)

扩展存储过程提供一种类似于存储过程的方式，它们是动态装入和执行的动态连接库（DLL）内的函数，无缝地扩展SQL Server功能。SQL Serve之外的动作可以很容易地触发，外部信息返回到SQL Server。另外，扩展存储过程支持返回状态码和输出。
注意：必须从master数据库执行扩展存储过程。
用户可以创建自己的扩展存储过程。
例如，下面是一些的扩展存储过程：

  • xp_cmdshell：作为一个操作系统外壳执行指定命令串，并以文本形式返回任何输出。
  • xp_logevent：在SQL Server日志文件或WindowsNT事件查看器中记录用户定义的信息。
  • xp_msver：返回SQL Server版本信息及各种环境信息。

3.远程存储过程

是从连接到不同服务器的远程服务器或客户机调用的存储过程。

4.局部存储过程

局部存储过程在各个用户数据库中创建。只能由创建它的用户调用。

5.临时存储过程

  • 临时存储过程可是局部的，名字前的前缀是"#"；也可是全局的，名字前的前缀是"##"。临时存储过程存放在tempdb数据库中。
  • 局部临时存储过程在单个用户会话中使用，该用户退出时，自动被删除。
  • 全局临时存储过程所有用户都可以使用，当最后一个用户退出时，自动被删除。

五、触发器

  • 触发器是用户定义在关系表上的一类由事件驱动的特殊过程，当一个触发器建立后，它作为一个数据库对象被存储。当某个触发事件发生时，触发器被触发，执行一系列操作
  • 触发器一旦由某个用户建立，任何用户对该触发器指定的数据进行增、删或改操作时，DBMS系统将自动**相应的触发器，定义在触发器中的功能将被DBMS执行，在核心层进行集中的完整性控制
  • 触发器可以实施更为复杂的检查和操作，具有更精细 和更强大的数据控制能力，不仅能实现完整性规则，而且能保证一些较复杂业务规则的实施

补充：
使用触发器时，SQL Server提供了两张特殊的临时表：
inserted表和deleted表。

  • 这两张表存在于高速缓存中，它们与创建触发器的表有相同的结构。
  • 用户可以使用该表检查某些修改操作的效果。
  • 但用户不能直接修改该表中的数据。
  • 用户可以使用该表的内容作为查询操作的判断条件，但要在FROM中写出使用的表名（inserted 或 deleted）

eg：
假设学生表S新增一属性Cnum(类型为INT,初值均为0), 记录该学生的选课数，分别创建如下触发器：
（1）INSERT 触发器

//创建INSERT 触发器Tri1，其功能是：在学习表SC插入一条记录后，立即更新学生表S的Cnum属性，即将原值加一。
CREATE  TRIGGER Tri1 ON SC 
FOR INSERT
	AS
    UPDATE Student SET Cnum=Cnum+1
	WHERE Student.Sno=
          (SELECT Sno FROM inserted 
          WHERE	Student.Sno=inserted.Sno)

⑵ DELETE触发器

//创建DELETE 触发器Tri2，其功能是：在删除学习表SC一条记录后，立即更新学生表S的Cnum属性，即将原值减一。
CREATE  TRIGGER Tri2  ON SC 
FOR DELETE
	AS
	UPDATE Student SET Cnum=Cnum-1
    WHERE Student.Sno=
		(SELECT Sno FROM deleted   
		WHERE Student.Sno=deleted.Sno)

⑶ UPDATE触发器

//创建UPDATE 触发器Tri3，功能是：在SC表更新一条记录的学号后，立即更新学生表S的Cnum属性，改前学号学生的Cnum值减1，改后学号学生的Cnum值加1。
CREATE  TRIGGER Tri3 ON SC 
FOR UPDATE  
	AS
	IF UPDATE(Sno)
	BEGIN
    UPDATE Student SET Cnum=Cnum-1 
		WHERE Student.Sno=(
			SELECT Sno 
			FROM deleted 
			WHERE Student.Sno=deleted.Sno)  
	UPDATE Student SET Cnum=Cnum+1 
		WHERE Student.Sno=(
			SELECT Sno 
			FROM inserted 
			WHERE Student.Sno=inserted.Sno)
   END

1.定义触发器

//表的拥有者才可以在 表上创建触发器
CREATE TRIGGER <触发器名>	//触发器名可以包含模式名，也 可以不包含模式名
							//同一模式下，触发器名必须是 唯一的
							
							//• 触发器只能定义在基本表上，不能定义 在视图上
							//• 当基本表的数据发生变化时，将**定 义在该表上相应触发事件的触发器
{BEFORE | AFTER} <触发事件> ON <表名>			//触发事件：
//AFTER/BEFORE是触发的时机					//• INSERT、DELETE或UPDATE 
//• AFTER表示在触发事件的操作执行之后**触发器	//• 几个事件的组合
//• BEFORE表示在触发事件的操作执行之前**触发器	//• UPDATE OF<触发列，...>
 													
REFERENCING NEW|OLD ROW AS <变量> FOR EACH {ROW | STATEMENT}
									//触发器类型
									//• 行级触发器(FOR EACH ROW)
									//• 语句级触发器(FOR EACH STATEMENT)
[WHEN <触发条件>]<触发动作体>
//• 触发器被**时，只有当触发条件为真时触发动作体才执行;否则触发动作体不执行。
//• 如果省略WHEN触发条件，则触发动作体在触发 器**后立即执行
				//• 触发动作体可以是一个匿名PL/SQL 过程块，也可以是对已创建存储过程的调用
				//• 如果是行级触发器，用户都可以在过程体中使用NEW和OLD引用事件，之后的新值和事件之前的旧值
				//• 如果是语句级触发器，则不能在触 发动作体中使用NEW或OLD进行引 用
				//• 如果触发动作体执行失败，**触 发器的事件就会终止执行，触发器 的目标表或触发器可能影响的其他 对象不发生任何变化

CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS <变量> 
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>

eg：

在TEACHER表上创建一个AFTER UPDATE触发器，
触发事件是UPDATE语句:UPDATE TEACHER SET Deptno=5;
假设表TEACHER有1000行
如果是语句级触发器，那么执行完该语句后，触发动作只发生一次
如果是行级触发器，触发动作将执行1000次

eg：

//当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中: 
//SC_U(Sno,Cno,Oldgrade,Newgrade)

CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC REFERENCING
	OLD row AS OldTuple,
	NEW row AS NewTuple
FOR EACH ROW
WHEN (NewTuple.Grade >= 1.1*OldTuple.Grade)
INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade) VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)
 

//将每次对表Student的插入操作所增加的学生个数 记录到表StudentInsertLog中。
CREATE TRIGGER Student_Count 
AFTER INSERT ON Student 
REFERENCING
	NEW TABLE AS DELTA 
FOR EACH STATEMENT
	INSERT INTO StudentInsertLog (Numbers) 
	SELECT COUNT(*) FROM DELTA

2.**触发器

  • 触发器的执行，是由触发事件**的，并由数据库服务器
    自动执行
  • 一个数据表上可能定义了多个触发器，遵循如下的执行顺
    序:
    • 执行该表上的BEFORE触发器;
    • **触发器的SQL语句;
    • 执行该表上的AFTER触发器。

3.删除触发器

触发器必须是一个已经创建的触发器，并且只能由具有相 应权限的用户删除

DROP TRIGGER <触发器名> ON <表名>;

六、事务

  • 定义
    • 一个数据库操作序列
    • 一个不可分割的工作单位
    • 恢复和并发控制的基本单位
  • 事务和程序比较
    • 在关系数据库中，一个事务可以是一条或多条SQL语句,也可以包含一个或多个程序。
    • 一个程序通常包含多个事务
相关标签： DataBase 数据库

上一篇： 用eclipselink来取代hibernate

下一篇：

推荐阅读