SQLServer 学习笔记之超详细基础SQL语句 Part 7

Sqlserver 学习笔记

by:授客 QQ：1033553122

-----------------------接Part 6-------------------

29 存储过程和触发器

存储过程由一组预先编辑好的SQL语句组成。将其放在服务器上。由用户通过指定存储过程的名称来执行。

 

触发器是一种特殊类型的存储过程，它不是由用户直接调用的，而是当用户对数据进行操作（包括数据的INSERT ,UPDATE,DELETE操作）时自动执行。

 

29.1创建、执行、删除简单的存储过程。

CREATE PROCEDURE 存储过程名

[@parameter data_type][= default][output][,…..]

AS

sql语句

 

29.2执行存储过程

对存储在服务器上的存储过程，可以使用exectue命令或者其名称执行

 

29.3查看存储过程

对用户建立存储过程，可以使用对象资源管理器或系统存储过程查看该存储过程的定义。

1， 使用对象资源管理器查看存储过程，在“对象资源管理器”窗口中，展开“数据库”结点，选择相应的数据库，展开“可编程性”，“存储过程”结点。

2， 使用系统存储过程查看存储过程，可以使用SP_HELPTEXT  SP_DEPENDS SP_HELP

 

29.4删除存储过程

可以利用资源管理器或者drop proc 语句将其删除

示例

--存储过程

CREATE PROCEDURE pro

AS

SELECT 学生.学号, 学生.姓名, 班级.班级名称, 专业.专业名称, 系部.系部名称

FROM 学生

JOIN 班级 ON 学生.班级代码=班级.班级代码

JOIN 专业 ON 班级.专业代码=专业.专业代码

JOIN 系部 ON 班级.系部代码=系部.系部代码

--上述语句的执行后，我们可以在[可编程性\存储过程]看到一个名为dbo.pro的存储过程

EXEC pro

 

--创建带参数的一个存储过程

CREATE PROC pro1

@sex VARCHAR(10),--注意这里有逗号

@id VARCHAR(10)

AS

SELECT 学生.学号, 学生.姓名, 班级.班级名称, 专业.专业名称, 系部.系部名称

FROM 学生

JOIN 班级 ON 学生.班级代码 = 班级.班级代码 AND 学生.性别 = @sex

JOIN 专业 ON 班级.专业代码 = 专业.专业代码

JOIN 系部 ON 班级.系部代码 = 系部.系部代码 AND 系部.系部代码 = @id

 

--执行存储过程

EXEC pro1 '男', '1'

 

--带输出参数的存储过程

 

CREATE PROC pro2

@setName VARCHAR(10),

@getNum VARCHAR(10) OUTPUT

AS

SELECT @getNum = 学号 FROM 学生 WHERE 姓名= @setName

 

--执行

----该语句的执行，看不到结果，但是如果用.net等编程语言调用时则会看到效果

EXEC pro2 '林姐','1'

 

--查看存储过程

EXEC SP_HELPTEXT pro1

 

EXEC SP_DEPENDS pro1

 

 

 

EXEC SP_HELP pro1

 

 

 

--删除存储过程

DROP PROC pro1

 

29.5触发器

触发器的优点：

1触发器自动执行，在对表中的数据做了任何修改之后立即被激活

2触发器能够对数据库中的相关表实现级联更改。

 

29.6临时表

INSERTED表和DELETED表用于存放对表中数据行的修改信息。他们是触发器执行时自动创建的。当触发器工作完成，他们也被删除。他们只是只读表，不能向他们写入内容。

 

INSERTED表：用来存储INSERT和UPDATE语句所影响的行的副本。

意思就是在INSERTED表中临时保存了被插入或被更新后的记录行。

在执行INSERT或UPDATE语句时，新加行被同时添加到INSERTED表和触发器表中。

 

DELETED表：用来存储DELETE和UPDATE语句所影响的行的副本。

意思是在DELETED表中临时保存了被删除或被更新前的记录行。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传到DELETED表中。所以可以从deleted表中检查删除的数据行是否能删除。

所以当表中某条记录的某项值发生变化时，变化前的值已经通过系统自动创建的临时表DELETED表和INSERTED表保存了被删除行或插入的记录行的副本。我么可以从这两个表中查询出变化前的值并赋给变量。

 

29.7使用sql创建触发器

格式：

CREATE TRIGGER trigger_name

ON {TABLE | view}

FOR {[INSERT],[DELETE],[UPDATE]}

AS

sql 语句

 

查看触发器信息

EXEC sp_helptrigger 表名

 

删除触发器

DROP TRIGGER 触发器名称

 

示例

USE student --在数据库student下的操作。。

GO---这个词不能少

--创建触发器

CREATE TRIGGER tri_insert

ON std_TABLE ---指定目标表

FOR INSERT ---触发器类型

AS--别忘了这个

DECLARE @a CHAR(20), @name CHAR(20), @major CHAR(20)

 --从临时表INSERTED中获取数据，数据保存在定义的变量中

--INSERTED保存着被插入或更新后的数据

SELECT @a = 学号, @name = 姓名, @major = 专业方向 FROM INSERTED

--删除时的对应操作，把获取的数据插入另一表中

INSERT INTO std_table2(学号, 名称, 专业方向) VALUES(@a, @name, @major)

--执行上述语句后，我们可以在触发器　结点下看到一个tri_insert

--测试

SELECT * FROM std_table

--SELECT * FROM std_table2--此时出错，std_table2没有建立

--删除触发器

DROP TRIGGER tri_insert

 

--重新创建

USE student --在数据库student下的操作。。

GO---这个词不能少

--上述语句的执行--选定数据库，和试图界面下手动选定一个样

--创建触发器

CREATE TRIGGER tri_insert

ON std_TABLE ---指定目标表

FOR INSERT---触发器类型

AS--别忘了这个

DECLARE @a CHAR(20), @name CHAR(20), @major VARCHAR(20)--注意这边貌似只能这样写，多个变量一起定义

--从临时表INSERTED中获取数据，数据保存在定义的变量中

--INSERTED保存着被插入或更新后的数据

SELECT @a = 学号, @name = 姓名, @major = 专业方向 FROM INSERTED

--删除时的对应操作，把获取的数据插入另一表中

INSERT INTO std_table1(学号, 姓名, 专业方向) VALUES(@a, @name, @major)

 

--测试

SELECT * FROM std_table

SELECT * FROM std_table1

--查看触发器的信息

exec sp_helptrigger std_table