开发者社区> 余二五> 正文

数据库优化之创建存储过程、触发器

简介:
+关注继续查看

    存储过程可加快查询的执行速度,提高访问数据的速度,帮助实现模块化编程,保存一致性,提高安全性。触发器是在对表进行插入、更新、删除操作时自动执行的存储过程,通常用于强制业务规则。


一、存储过程

1. 为什么需要存储过程

    从客户端通过网络向服务器发送SQL代码并执行是不安全的,给黑客提供盗取数据的机会,如下图所示,一个简单的SQL注入过程

杨书凡38.png

   从上图可知,应用程序的执行过程是不安全的,主要有以下几个方面:

(1)数据不安全,网络传送SQL代码,容易被未授权者截获

(2)每次提交SQL代码都要经过语法编译后在执行,影响应用程序的运行性能

(3)网络流量大,对于反复执行的SQL代码,在网络上多次传送,影响网络传输量


2. 什么是存储过程

    存储过程是SQL语句和控制语句的预编译集合,保存在数据库中,可有应用程序调用执行,而且允许用户声明变量、逻辑控制语句及其他强大的编程功能。包含逻辑控制语句和数据操作语句,可以接收参数、输出参数、返回单个或多个结果值及返回值

    使用存储过程的优点:

(1)模块化程序设计,只需创建一次,以后即可调用该存储过程任意次

(2)执行速度快,效率高

(3)减少网络流量

(4)具有良好的安全性

    存储过程分为系统存储过程和用户自定义的存储过程


3. 系统存储过程

    是一组预编译的T-SQL语句,提供了管理数据库和更新表的机制,并充当从系统表中检索信息的快捷方式

(1)常见的系统存储过程

    系统存储过程的名称以“sp_”开头,存放在Resource数据库中

杨书凡39.png

   

    使用存储过程的语法如下:

exec  存储过程名  [参数值]


例如:执行以下T-SQL语句

杨书凡40.png


(2)常用的扩展存储过程

    扩展存储过程是SQL Server提供的各类系统存储过程的一类,允许使用其他编程语言(如C#)创建外部存储过程,通常以“xp_”开头,以DDL形式单独存在

    一个常用的扩展存储过程为xp_cmdshell ,它可以完成DOS命令下的一些操作,如创建文件夹、列出文件夹。语法如下:

exec  xp_cmdshell  DOS命令  [no_output]

其中,no_output为可选参数,设置执行DOS命令后是否输出返回信息

例如:在C盘下创建一个文件夹bank,并查看文件

杨书凡41.png


4. 用户自定义的存储过程

    除了使用系统的存储过程外,也可以创建自己的存储过程。可以使用SSMS或T-SQL语句创建存储过程

(1)使用SSMS创建存储过程

杨书凡42.png


(2)使用T-SQL语句创建存储过程

    创建存储过程的语法如下:

杨书凡43.png

    删除存储过程的语法如下:

drop  proc  存储过程名


案例:有以下两个表,编写存储过程,实现网络管理专业的平均分

杨书凡44.png

杨书凡45.png  

  

触发器

    触发器是一种特殊的存储过程,当表中数据发生更新时自动调用,以响应INSERT、UPDATE、DELETE语句

1. 什么是触发器

    触发器是对表进行插入、更新、删除操作时自动执行的存储过程,通常用于强制业务规则,可以定义比用CHECK约束更为复杂的约束。触发器主要是通过事件触发而被执行的,而存储过程可以通过存储过程名称而被直接使用。


2. 触发器的分类

INSERT触发器:当向表中插入数据时触发

UPDATE触发器:当更新表中某列或多列时触发

DELETE触发器:当删除表中记录是触发


3. deleted表和inserted表

    每个触发器都有两个特殊的逻辑表:删除表和插入表。由系统管理,存储在内存而不是数据库中,因此,不允许用户直接对其修改。它们只是临时存放对表中数据行的修改信息,当触发器工作完成,它们也被删除。

杨书凡46.png

4. 触发器的作用

    主要作用是:实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性,除此之外,还有以下几种功能

(1)强化约束:实现比CHECK约束更为复杂的约束

(2)跟踪变化:侦测数据库内的操作,从而不允许未经许可的更新和变化

(3)级联运行:侦测数据库内的操作,并自动级联影响整个数据库的各项内容


5. 创建触发器

    创建触发器可使用SSMS或T-SQL语句

(1)使用SSMS创建触发器

杨书凡47.png


(2)使用T-SQL语句创建触发器

    使用T-SQL语句创建触发器的语法如下:

1
2
3
4
5
create  trigger  触发器名          //创建的触发器名称
on  表名                            //在其上执行触发器的表或视图名称
[with  encryption]                 //可选,防止将触发器作为SQL Server复制的一部分发布
for  {[delete,insert,update]}         //关键字,至少指定一项,如果多项,由逗号分隔
as sql语句


案例:创建一个触发器,当有人更改信息时,提示一条消息,并阻止操作

杨书凡48.png


    如果需要修改触发器,操作方法如下,在弹出的窗口修改T-SQL语句即可

杨书凡49.png


创建触发器时注意事项

(1)create trigger必须是批处理中的第一条语句,并只能应用到一个表中

(2)触发器只能在当前数据库中创建,但可以引用当前数据库的外部对象

(3)在同一条create trigger语句中,可以为多种用户操作(如DELETE)定义相同的触发器操作










本文转自 杨书凡 51CTO博客,原文链接:http://blog.51cto.com/yangshufan/2046648,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Linux系统中使用GCC CPU参数优化代码编译
Linux系统中使用GCC CPU参数优化代码编译 使用特定的GCC参数可以使编译出的程序执行效率有较大提升。具体如下: 1、优化原理: 在编译程序时,借助参数传递的方法,使用与系统CPU相匹配的gcc参数,编译出的程序就是为系统CPU而进行特定优化过的,因而执行速度和效率都会是最好。
820 0
mysql 创建存储过程
引用:http://database.51cto.com/art/201010/229242.htm 下文将教您如何创建MySQL存储过程,并附上了详细的步骤,如果您在MySQL存储过程方面遇到过问题,不妨一看,对您会有所帮助。
681 0
《OpenACC并行程序设计:性能优化实践指南》一 3.12 参考文献
本节书摘来自华章出版社《OpenACC并行程序设计:性能优化实践指南》一 书中的第3章,第3.12节,作者:[美] 罗布·法伯(Rob Farber),更多章节内容可以访问云栖社区“华章计算机”公众号查看。
613 0
Flink批处理优化器之数据属性
在一段时间之前我们已介绍过IP(Interesting Property)对于优化器的意义以及它将对优化器的优化决策产生的影响。本篇我们将介绍Flink的批处理优化器中涉及到的所有的IP,我们将其统称为数据属性。
1102 0
+关注
20381
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载