【DB】存储过程[基于SQL Server]

1。什么是存储过程？

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集。他存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数来执行它。

2。存储过程特点

优点：

1-存储过程存储在数据库中，一次编译永久有效。

2-存储过程有更好的安全性。参数化的存储过程可以防止SQL注入式攻击，可设定只有某些用户才具有对指定存储过程的使用权。

3-存储过程可以减少网络通信流量。

1-减少了SQL字符串的传递，传递的只有存储过程名字和参数。

2-减少了SQL来回处理传递的次数。能一次获取符合条件的数据结果；而不是在代码和数据库中多次调用，来回传递SQL字符串和结果。减少了IO操作。

4-存储过程提高运行速度。原因同3。

存储过程适用于业务比较复杂、数据量庞大的系统。

缺点

每个数据库的存储过程语法几乎都不一样，十分难以维护（不通用）
业务逻辑放在数据库上，难以迭代

2。存储过程分类

1-系统存储过程，数据库自带的，以sp_开头。

SQLSever中：数据库=>可编程性=>存储过程=>系统存储过程

2-本地存储过程，用户创建完成某一特定功能的存储过程。一般说的存储过程指这种存储过程。

SQLSever中：数据库=>可编程性=>存储过程

3-临时存储过程，

第一种：本地临时存储过程：以#开头，存放在tempdb数据库中的本地临时存储过程，只有创建它的用户可执行。

第二种：全局临时存储过程：以##开头，存放在tempdb数据库中的本地临时存储过程，任意用户可执行，且不需要特定权限。

4-远程存储过程

在SQLServer2005中，Romte Stored Procedures位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。

5-扩展存储过程

Extended Stored Procedures是客户可以使用外部程序语言编写的存储过程，扩展存储过程通常以xp_开头

2。基本语法

1-创建存储过程

create procedure [存储过程名]
@[参数名] [参数类型],
@[参数名] [参数类型]
as
begin
 -- SQL语句
end

2-调用存储过程

1- command命令下，exec [存储过程名] [参数];

2- SQL环境下，call [存储过程名] [参数];
3- PL/SQL环境下，begin [存储过程名] [参数] end;

3-删除存储过程

drop procedure sp_name

注意，不能在存储过程中删除存储过程。

触发器

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，

之前执行使用 BEFORE 关键字，BEFORE 用于数据验证和净化。强制检验或转换数据。

之后执行使用 AFTER 关键字，AFTER 用于审计跟踪，将修改记录到另外一张表中。

INSERT 触发器包含一个名为 NEW 的虚拟表。

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

触发器的优点：

触发器可通过数据库中的相关表实现级联更改，不过，通过级联引用完整性约束可以更有效地执行这些更改。

触发器可以强制用比CHECK约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。

触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。

慎用触发器：

滥用会造成数据库及应用程序的维护困难。

如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度。

一：触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。

**所以触发器可以用来实现对表实施复杂的完整性约束。

二：SQL Server为每个触发器都创建了两个专用表：Inserted表和Deleted表。

这两个表由系统来维护，它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行完成后，与该触发器相关的这两个表也被删除。

Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。

Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。

三：Instead of 和 After触发器

SQL Server2000提供了两种触发器：Instead of 和After 触发器。这两种触发器的差别在于他们被激活的不同：

Instead of触发器用于替代引起触发器执行的T-SQL语句。除表之外，Instead of 触发器也可以用于视图，用来扩展视图可以支持的更新操作。

After触发器在一个Insert,Update或Deleted语句之后执行，进行约束检查等动作都在After触发器被激活之前发生。After触发器只能用于表。

一个表或视图的每一个修改动作(insert,update和delete)都可以有一个instead of 触发器，一个表的每个修改动作都可以有多个After触发器。

四：触发器的执行过程

如果一个Insert﹑update或者delete语句违反了约束，那幺After触发器不会执行，因为对约束的检查是在After触发器被激活之前发生的。所以After触发器不能超越约束。

Instead of 触发器可以取代激活它的操作来执行。它在Inserted表和Deleted表刚刚建立，其它任何操作还没有发生时被执行。因为Instead of 触发器在约束之前执行，所以它可以对约束进行一些预处理。

五：使用T-SQL语句来创建触发器

基本语句如下：

create trigger trigger_name

　　on {table_name | view_name}

　　{for | After | Instead of }

　　[ insert, update,delete ]

　　as

　　sql_statement

六：删除触发器：

基本语句如下：

drop trigger trigger_name

七：查看数据库中已有触发器：

-- 查看数据库已有触发器

use jxcSoftware

　　go

　　select * from sysobjects where xtype='TR'

-- 查看单个触发器

exec sp_helptext '触发器名'

八：修改触发器：

基本语句如下：

alter trigger trigger_name

　　on {table_name | view_name}

　　{for | After | Instead of }

　　[ insert, update,delete ]

　　as

　　sql_statement

九：触发器实例

创建表student表

use triggerTest;
-- 创建student表
create table Student
(ID        int
 primary key identity(1, 1), 
 studentID int --学号
);

创建borrowRecord表

--创建borrowRecord表
create table Borrowrecord
(   --学生借书记录表
BorrowRecord int identity(1, 1), --流水号
studentID    int, --学号
BorrowDate   datetime, --借出时间
ReturnDate   datetime, --归还时间

);

添加测试数据

INSERT into Student(studentID) values(1)
INSERT into Student(studentID) values(2)
INSERT into BorrowRecord(studentID,BorrowDate,ReturnDAte) VALUES(1,getdate(),getdate()+1)
INSERT into BorrowRecord(studentID,BorrowDate,ReturnDAte) VALUES(2,getdate(),getdate()+1)

触发器的编写

--编写一个触发器 功能：如果我更改了学生的学号，我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);
create trigger truStudent on Student             --在Student表中创建触发器
for update             --为什么事件触发
as
     --事件触发后要做的事情
     if update(studentID)
         begin
             declare @stuidnew int;  --从临时表Inserted记录新的学号ID
             declare @stuidold int;   --从临时表Delete记录更新以前旧的学号ID
             select @stuidold = studentID
             from deleted;
             select @stuidnew = studentID
             from inserted;
             update Borrowrecord
               set 
                   studentID = @stuidnew
             where studentID = @stuidold;
             print @stuidnew;
             print @stuidold;
     end;
go

测试触发器

--测试触发器
update Student
  set 
      studentID = 111
where studentID = 1;

总结:

重点是知道触发器主要的2张表：
--触发器的操作 deleted表和inserted表的数据变化 --插入操作（Insert） Inserted表有数据，Deleted表无数据 --删除操作（Delete） --Inserted表无数据，Deleted表有数据 --更新操作（Update） --Inserted表有数据（新数据），Deleted表有数据（旧数据）

posted on 2022-08-28 22:42 tony_cyou 阅读(207) 评论(0) 编辑收藏举报

刷新页面返回顶部