Press "Enter" to skip to content

存储过程与函数

存储过程和函数实在数据库中年定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数实在MySQL服务器中存储和执行的,可以减少客户端和服务器端的数据传输。

创建存储过程和函数

创建存储过程和函数是指将经常使用的一组SQL语句组合在一起,并将这些SQL语句当做一个整体存储在MySQL服务器中。

创建示例表

idnamecompanypriceproduce_timevalidity_timeaddress
1AA饼干AA饼干厂2.520083北京
2CC牛奶CC牛奶厂3.520091河北
3EE果冻EE果冻厂1.520072北京
4FF咖啡FF咖啡厂2020025天津
5GG奶糖GG奶糖厂1420033广东
#创建表
mysql> create table food
(id int(10) not null unique primary key auto_increment,
name varchar(20) not null, 
company varchar(30) not null, 
price float, 
produce_time YEAR, 
validity_time int(4), 
address varchar(50)) 
default charset=utf8;
#插入数据
mysql> insert into food values
(1,'AA饼干','AA饼干厂',2.5,'2008',3,'北京'),
(2,'CC牛奶','CC牛奶厂',3.5,'2009',1,'河北'),  
(3,'EE果冻','EE果冻厂',1.5,'2007',2,'北京'),  
(4,'FF咖啡','FF咖啡厂',20,'2002',5,'天津'),  
(5,'GG奶糖','GG奶糖厂',14,'2003',3,'广东');
#创建存储过程
mysql> delimiter &&
mysql> create procedure food_price_count(in price_info1 float,in price_info2 float,out  count int)
    -> reads sql data
    -> begin
     -> declare temp float;
    -> declare match_price cursor for select price from food;
    -> declare exit handler for not found close match_price;
    -> set @sum=0;
    -> select count(*) into count from food
    -> where price>price_info1 and price_info2;
    -> open match_price;
    -> repeat
    -> fetch match_price into temp;
    -> if temp>price_info1 and price_info2
    -> then set @sum=@sum+temp;
    -> end if;
    -> until 0 end repeat;
    -> close match_price;
    -> end&&
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call food_price_count(2,18,@count);
Query OK, 0 rows affected (0.00 sec)
mysql> select @count,@sum;
+--------+------+
| @count | @sum |
+--------+------+
|      4 |   40 |
+--------+------+
1 row in set (0.00 sec)
#查看存储过程状态
mysql> show create procedure food_price_count\G
#删除存储过程
mysql> drop procedure food_price_count;