存储过程和触发器——MySQL

jopen 9年前

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;//