文档章节

存储过程和触发器——MySQL

passionfly
 passionfly
发布于 2015/04/17 21:38
字数 2489
阅读 403
收藏 22
点赞 0
评论 0

MySQL5.0版本开始就对存储过程和触发器进行了支持,在MySQL进行学习前,先查看您所使用的版本吧,方法有:

1.$mysql -V  //linux终端下

2.select version();  //mysql

3. mysql --help | grep Distrib  //linux终端下

在了解您所使用的版本支持情况下再下一步

 

存储过程  sql语句执行的时候要先编译,然后执行。存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
  
一、存储过程介绍
  
存储过程是由流控制和SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,应用程序使用时只要调用即可。在ORACLE中,若干个有联系的过程可以组合在一起构成程序包。
  
存储过程是利用SQL Server所提供的Tranact-SQL语言所编写的程序。Tranact-SQL语言是SQL Server提供专为设计数据库应用程序的语言,它是应用程序和SQL Server数据库间的主要程序式设计界面。它好比Oracle数据库系统中的Pro-SQLInformix的数据库系统能够中的Informix- 4GL语言一样。这类语言主要提供以下功能,让用户可以设计出符合引用需求的程序:
  1)
、变量说明
  2)
ANSI兼容的SQL命令(Select,Update….)
  3)
、一般流程控制命令(if…else…while….)
  4)
、内部函数
  
二、使用存储过程有以下的优点:
  * 
存储过程的能力大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
  * 
可保证数据的安全性和完整性。
  # 
通过存储过程可以使没有权限的用户在控制之下间接地存取数据库,从而保证数据的安全。
  # 
通过存储过程可以使相关的动作在一起发生,从而可以维护数据库的完整性。
  * 
再运行存储过程前,数据库已对其进行了语法和句法分析,并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成,所以存储过程能以极快的速度执行。
  * 
可以降低网络的通信量。
  * 
使体现企业规则的运算程序放入数据库服务器中,以便:
  # 
集中控制。
  # 
当企业规则发生变化时在服务器中改变存储过程即可,无须修改任何应用程序。企业规则的特点是要经常变化,如果把体现企业规则的运算程序放入应用程序中,则当企业规则发生变化时,就需要修改应用程序工作量非常之大(修改、发行和安装应用程序)。如果把体现企业规则的运算放入存储过程中,则当企业规则发生变化时,只要修改存储过程就可以了,应用程序无须任何变化。
   
三、存储过程的书写格式:
  CREATE PROCEDURE [
拥有者.]存储过程名[;程序编号]
  [(
参数#1,…参数#1024)]
  [WITH
  {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
  ]
  [FOR REPLICATION]
  AS 
程序行

其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数
  (SQL Server 7.0
以上版本),参数的使用方法如下:
  @
参数名数据类型 [VARYING] [=内定值] [OUTPUT]
  
每个参数名前要有一个“@”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外,其他SQL Server所支持的数据类型都可使用。

  output:表示此参数是可传回的
  with {recompile|encryption}
  recompile:
表示每次执行此存储过程时都重新编译一次
  encryption:
所创建的存储过程的内容会被加密

 

实例:

建立2张表格employeedepartment,他们以员工ID建立关系

1. 创建2表:

create table employee (
     employee_id int(5) primary key not null,
     employee_name varchar(20),
     employee_salary int(5));

create table department (
     dept_id int(3),
     dept_name varchar(20),
     employee_id int(5),
     constraint fk_employee_id foreign key(employee_id) references employee(employee_id));

2. employee创建添加记录的存储过程

 在这之前,需要设置一下分隔符,以免后面使用;时就终止了创建

delimiter //
create procedure add_employee(in id int,in name varchar(20),in salary int)  /*
参数输入的形式,in:输入,若为输出,则为out*/
  begin
  insert into employee values(id,name,salary);  #
输入的数据插入到表格中
  select * from employee;
  end//  #
这里就以//结束程序的录入

3.department创建添加记录的存储过程

create procedure insert_dept(in id int,in name varchar(20),in emp_id int)
  begin
  insert into department values(id,name,emp_id);
  select * from department;
  end;//

 

4. 调用存储过程完成数据录入

call add_employee(2,'Jason',6500);//
call insert_dept(1,'STE',2);//

 

5.创建一个给指定部门员工加薪的存储过程

create procedure raise (in department_id int,in add_salary_amount int)
  begin
  update employee set employee_salary=employee_salary+add_salary_amount
     where employee_id in ( select employee_id from department where department_id=dept_id);
  commit;
  end;//

 

call raise(1,200);//


触发器  触发器(trigger)是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由个事件来触发,比如当对一个表进行操作( insertdelete update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器可以从DBA_TRIGGERSUSER_TRIGGERS数据字典中查到。

 

触发器介绍
触发器是一种特殊的存储过程,它在插入,删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力。数据库触发器有以下的作用:
安全性。可以基于数据库的值使用户具有操作数据库的某种权利。
可以基于时间限制用户的操作,例如不允许下班后和节假日修改数据库数据。
可以基于数据库中的数据限制用户的操作,例如不允许股票的价格的升幅一次超过10%
审计。可以跟踪用户对数据库的操作。
审计用户操作数据库的语句。
把用户对数据库的更新写入审计表。
实现复杂的数据完整性规则。 #实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同,触发器可以引用列或数据库对象。例如,触发器可回退任何企图吃进超过自己保证金的期货。
提供可变的缺省值。
实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。#在修改或删除时级联修改或删除其它表中的与之匹配的行。
在修改或删除时把其它表中的与之匹配的行设成NULL值。
在修改或删除时把其它表中的与之匹配的行级联设成缺省值。

触发器能够拒绝或回退那些破坏相关完整性的变化,取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时,这种触发器会起作用。

同步实时地复制表中的数据。
自动计算数据值,如果数据的值达到了一定的要求,则进行特定的处理。例如,如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。

 

触发器语法
create  trigger 
触发器名触发时间触发事件
on 
表名
[for each row]
pl/sql 
语句
其中:
触发器名:触发器对象的名称。由于触发器是数据库自动执行的,因此该名称只是一个名称,没有实质的用途。
触发时间:指明触发器何时执行,该值可取:
before---
表示在数据库动作之前触发器执行;
after---
表示在数据库动作之后出发器执行。
触发事件:指明哪些数据库动作会触发此触发器:
insert
:数据库插入会触发此触发器;
update
:数据库修改会触发此触发器;
delete
:数据库删除会触发此触发器;

名:数据库触发器所在的表。

 

.使用触发机制时的限制
当你在使用触发机制时你必须要知道它有如下的使用限制:
不能在临时表中创建触发机制
触发机制必须在当前的表所在的数据库中创建
不能在视图中创建触发机制
当表被删除以后所有与之相关的触发机制会被自动地删除

 

实例:

6. 记录员工薪资变更日志

这时就可以对salary创建一个触发器,记录变更信息,先创建一个记录日志的表格:

create table salary_adjust_log(
  employee_id int(5),
  old_salary int(4),
  new_salary int(4),
  changedata datetime);//   #
使用datetime格式记录变更时间

创建触发器:

create trigger update_salary
  after update on employee
  for each row       #
对表格每一行执行
  begin
  insert into salary_adjust_log
    values(new.employee_id,  /*new:
表示更新后的记录,old:更新前的记录,在oracle中语法是 :new.employee_id,:old employee_salary,多了个冒号*/
      old.employee_salary,  
      new.employee_salary,
      now());   #
使用now()函数记录时间
  end;//


本文转载自:http://blog.csdn.net/zzzmmmkkk/article/details/4400557

共有 人打赏支持
passionfly
粉丝 13
博文 106
码字总数 76465
作品 0
西安
PostgreSQL与MySQL比较

http://bbs.chinaunix.net/thread-1688208-1-1.html 特性 MySQL PostgreSQL 实例 通过执行 MySQL 命令(mysqld)启动实例。一个实例可以管理一个或多个数据库。一台服务器可以运行多个 mysq...

kajhsdjkah
2012/07/29
7.6K
12
PostgreSQL与MySQL的区别收集(转)

特性 MySQL PostgreSQL 实例 通过执行 MySQL 命令(mysqld)启动实例。一个实例可以管理一个或多个数据库。一台服务器可以运行多个 mysqld 实例。一个实例管理器可以监视 mysqld 的各个实例。...

easonjim
05/12
0
0
PostgreSQL与MySQL比较

MySQL相对于PostgreSQL的劣势: PostgreSQL主要优势:   1. PostgreSQL完全免费,而且是BSD协议,如果你把PostgreSQL改一改,然后再拿去卖钱,也没有人管你,这一点很重要,这表明了Postg...

杨_静
2012/07/30
0
0
MySQL的触发器

触发器—trigeer是数据库下用于监视数据改变的某种情况,并触发执行某种操作的一个功能,简单来说就是对某一张表符合某种条件的更改前或者后执行一段存储过程。在这里就简单的说一说在MySQL...

往事_Jim_遗
01/08
0
0
RDBMS-mysql初步

rdbms: 1、数据库的建立删除 2、表单的创建、删除、修改 3、索引的创建和删除 4、用户和权限 5、数据的增删改 6、查询 DML:数据操作语言 INSERT REPLACE DELETE UPDATE DDL:数据定义语言 ...

jyxcto
2017/05/29
0
0
MySQL 5.7.2 发布,增强性能和可扩展性

甲骨文近日发布了MySQL 5.7分支的最新的开发里程碑版本(DMR版)——MySQL 5.7.2。该版本提供了更快的连接速度,更高的事务吞吐量,提升了复制速度,带来了内存仪表和其他增强功能,从而实现...

oschina
2013/09/22
5.3K
25
MySQL事件调度器(Event Scheduler)介绍

一、介绍 事件调度器是在 MySQL 5.1 中新增的另一个特色功能,可以作为定时任务调度器,取代部分原先只能用操作系统任务调度器才能完成的定时功能。而且 MySQL 的事件调度器可以实现每秒钟执...

Amamatthew
2014/09/26
0
0
新手MySQL工程师必备命令速查手册

作者介绍 MySQL的基本操作可以包括两个方面:MySQL常用语句如高频率使用的增删改查(CRUD)语句和MySQL高级功能,如存储过程、触发器、事务处理等。而这两个方面又可以细分如下: 1、MySQL常...

DBAplus社群
05/17
0
0
【强烈强烈推荐】《ORACLE PL/SQL编程详解》全原创(共八篇)--系列文章导航

《ORACLE PL/SQL编程详解》 系列文章目录导航 ——通过知识共享树立个人品牌。 本是成书的,但后来做其他事了,就无偿的贡献出来,被读者夸其目前为止最“实在、经典”的写ORACLE PL/SQL编程...

yonghu86
2012/07/30
0
0
mysql存储过程入门与提高(第一篇)

以前在网上看到歪国人专门写了本mysql存储过程,感觉很强大啊。 MySQL stored programs包括存储过程,函数,触发器。 主要有三种类型的数据库存储程序 存储过程 存储过程是最常见的存储程序,...

落叶刀
2015/09/23
132
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

java 复制对象有哪些方式

java 复制对象有哪些方式 Apache的 Common beanutils库 org.apache.commons.beanutils.BeanUtils.copyProperties(dest,origin); Springframework 的BeanUtil 依赖: <dependency> ......

黄威
16分钟前
0
0
1.13 单用户模式

命令 init 6 #重启,reboot或shutdown -r now init 0 #重启,poweroff 单用户模式:密码忘记情况下 主机重启,按方向键↑,选择CentOS Linux (3.10.0-514.e17.X86 64) 7 (Core),按e键编辑...

小丑鱼00
18分钟前
1
0
jstack的简单使用

公司测试反应, 一个java应用的机器,cpu始终是30%, 即使不做交易, 于是想到了之前看到的jstack文章, 实践步骤记录一下: 1, 找出java应用的进程号 ps -ef|grep 应用名|grep -v grep 2, 找出pid...

零二一七
23分钟前
0
0
崛起于Springboot2.X之项目war打包部署(18)

将springboot项目打包步骤: 1、启动类 extends SpringBootServletInitializer 2、启动类添加覆盖方法 @Overrideprotected SpringApplicationBuilder configure(SpringApplicationBuilder......

木九天
32分钟前
1
0
导入CSV文件就行数据整理分析

#-*-coding:utf-8-*-import csv,os,re,mathlocalPath=input("请输入所有群文件的根目录:") #所有QQ群文件的物理根目录路径def info(): info_dic=[] dirList=os.listdi...

Kefy
38分钟前
0
0
CoreText进阶(六)-内容大小计算和自动布局

CoreText进阶(六)-内容大小计算和自动布局 其它文章: CoreText 入门(一)-文本绘制 CoreText入门(二)-绘制图片 CoreText进阶(三)-事件处理 CoreText进阶(四)-文字行数限制和显示更...

aron1992
39分钟前
0
0
一个Unity高人的博客,涉猎范围很广,深度也很深。

https://blog.csdn.net/ecidevilin/article/list/

爽歪歪ES
41分钟前
0
0
Spring Cloud Config-Git后端

EnvironmentRepository的默认实现使用Git后端,这对于管理升级和物理环境以及审核更改非常方便。要更改存储库的位置,可以在Config Server中设置“spring.cloud.config.server.git.uri”配置...

itcloud
43分钟前
1
0
centos7 卸载mysql

[root@zyf ~]# rpm -qa|grep -i mysqlmysql-community-libs-5.6.34-2.el7.x86_64mysql-community-server-5.6.34-2.el7.x86_64mysql-community-release-el7-5.noarchmysql-community-......

Yao--靠自己
51分钟前
0
0
【Spring 系列 条件注解】

Spring 提供了按条件注册Bean的功能涉及到两个组件分别是:核心接口Condition,核心注解Conditional。 1、示例说明 为了演示条件注解的效果,需要定义一个属性文件,然后根据属性文件中配置的...

HansonReal
59分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部