存储过程和触发器——MySQL

jopen 10年前

从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-SQL和Informix的数据库系统能够中的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张表格employee和department，他们以员工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）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由个事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器可以从DBA_TRIGGERS，USER_TRIGGERS数据字典中查到。

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

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

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

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

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

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

实例：

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

存储过程和触发器——MySQL

相关经验

目录