MySQL 触发器、存储过程、事务简单示例

最新推荐文章于 2024-09-20 21:26:51 发布

sage425

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量1.1k

点赞数

分类专栏： MySql 数据库文章标签： mysql 存储 insert 数据库 query null

MySql 数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一个存储过程包括名字，参数列表，以及可以包括很多SQL语句的SQL语句集。
创建存储过程：

语法：
CREATE PROCEDURE p()
BEGIN
/*此存储过程的正文*/
END

CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(pro_price) AS priceaverage
FROM products;
END;
# begin…end之间是存储过程的主体定义

# mysql的分界符是分号（；）

调用存储过程的方法是：

# CALL加上过程名以及一个括号
# 例如调用上面定义的存储过程
CALL productpricing();
# 哪怕是不用传递参数，存储过程名字后面的括号“（）”也是必须的

删除存储过程的方法是：
DROP PROCUDURE productpricing;

创建带参数的存储过程：
CREATE PROCUDURE productpricing(

OUT p1 DECIMAL(8,2),

OUT ph DECIMAL(8,2),

OUT pa DECIMAL(8,2)
)
BEGIN
SELECT Min(prod_price) INTO pl FROM products;
SELECT Max(prod_price) INTO ph FROM products;
SELECT Avg(prod_price) INTO pa FROM products;
END;
# DECIMAL用于指定参数的数据类型
# OUT用于表明此值是用于从存储过程里输出的
# MySQL支持 OUT, IN, INOUT

调用带参数的存储过程：
CALL productpricing(@pricelow,
@pricehigh,
@priceaverage);
# 所有的参数必须以@开头
# 要想获取@priceaverage的值，用以下语句
SELECT @priceaverage;
# 获取三个的值，用以下语句
SELECT @pricehigh, @pricelow, @priceaverage;
另一个带IN和OUT参数的存储过程：
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END;
CALL ordertotal(20005, @total);
SELECT @total;

添加一个完整的例子:(这是一个自定义分页的存储过程)
DELIMITER $$
DROP PROCEDURE IF EXISTS `dbcall`.`get_page`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_page`(
/**//*Table name*/
tableName varchar(100),
/**//*Fileds to display*/
fieldsNames varchar(100),
/**//*Page index*/
pageIndex int,
/**//*Page Size*/
pageSize int,
/**//*Field to sort*/
sortName varchar(500),
/**//*Condition*/
strWhere varchar(500)
)
BEGIN
DECLARE fieldlist varchar(200);
if fieldsNames=''||fieldsNames=null THEN
set fieldlist='*';
else
set fieldlist=fieldsNames;
end if;

if strWhere=''||strWhere=null then
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
else
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
end if;
PREPARE stmt1 FROM @strSQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;

今天玩了一下触发器，感觉不错
先GOOGLE了一下，NND网上的文章全都都是一个模子出来的，一模一样
看了半天还没看懂，于是我翻开手册，看了看，感觉比他们写的还容易看懂
小弟在本地做了一下小小地测试

建了2个表
a表。aid,name,age,sex,
b表。bid,name,address,

然后再a表上创建触发器，语句如下

CREATE TRIGGER insert_a BEFORE INSERT ON a
FOR EACH ROW
INSERT INTO b SET name=NEW.name;

//如果想在触发的时候执行多条语句，把语句放在BEGIN ……END之间

然后执行语句

INSERT INTO `a`(`name`,`age`,`sex`) VALUES('wuzhijian','22','男');

执行成功之后去查看b表的name字段。'wuzhijian'插入进去了
hoho~~~
第一次测试成功
我也来解释一下创建触发器的语句，为不能理解的XDJM们能看懂，（我也是新手）

CREATE TRIGGER     // 创佳一个触发器
insert_a                     // 触发器的名字
BEFORE                    // 触发的时间
INSERT                     // 激活触发器的语句可以是UPDATE或者DELETE
ON                            // 创建触发器的表   我这里是a表
FOR EACH ROW     //这句什么意思我也没看懂

如果想要在触发器的时候执行多条语句可以这个写：

CREATE TRIGGER insert_a BEFORE INSERT ON a
FOR EACH ROW
BEGIN
INSERT INTO b SET name=NEW.name;
语句…………
语句…………
END

一直以来我都以为MYSQL不支持事务处理，所以在处理多个数据表的数据时，一直都很麻烦（我是不得不将其写入文本文件，在系统重新加载得时候才写入数据库以防出错）～今天发现MYSQL数据库从4.1就开始支持事务功能，据说5.0将引入存储过程^_^
     先简单介绍一下事务吧！事务是DBMS得执行单位。它由有限得数据库操作序列组成得。但不是任意得数据库操作序列都能成为事务。一般来说，事务是必须满足4个条件（ACID）
     原子性（Autmic）：事务在执行性，要做到“要么不做，要么全做！”，就是说不允许事务部分得执行。即使因为故障而使事务不能完成，在rollback时也要消除对数据库得影响！
    一致性（Consistency）：事务得操作应该使使数据库从一个一致状态转变倒另一个一致得状态！就拿网上购物来说吧，你只有即让商品出库，又让商品进入顾客得购物篮才能构成事务！
    隔离性（Isolation）：如果多个事务并发执行，应象各个事务独立执行一样！
    持久性（Durability）：一个成功执行得事务对数据库得作用是持久得，即使数据库应故障出错，也应该能够恢复！

   MYSQL的事务处理主要有两种方法。
1、用begin,rollback,commit来实现
       begin 开始一个事务
       rollback 事务回滚
       commit 事务确认
   2、直接用set来改变mysql的自动提交模式
       MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！我们可以通过
     set autocommit=0 禁止自动提交
     set autocommit=1 开启自动提交
来实现事务的处理。
但注意当你用 set autocommit=0 的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或rollback结束，注意当你结束这个事务的同时也开启了个新的事务！按第一种方法只将当前的作为一个事务！
个人推荐使用第一种方法！
   MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型是不支持的！（切记！）