(1)创建food表
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)
);
(2)插入数据
INSERT INTO food VALUES(1,'AA饼干','AA饼干厂',2.5,'2008',3,'北京');
INSERT INTO food (id,NAME,company,price,produce_time,validity_time,address)
VALUES(2,'CC牛奶','CC牛奶厂',3.5,'2009',1,'河北');
INSERT INTO food VALUES
(NULL,'EE果冻','EE果冻厂',1.5,'2007',2,'北京'),
(NULL,'FF咖啡','FF咖啡厂',20,'2002',5,'天津'),
(NULL,'GG奶糖','GG奶糖厂',14,'2003',3,'广东');
(3)创建存储过程food_price_count
DELIMITER && //使用“delimiter &&”将sql语句结束付符号变成&&
CREATE PROCEDURE food_price_count(IN price_info1 FLOAT,IN price_info2 FLOAT,OUT COUNT INT)
READS SQL DATA
BEGIN
DECLARE temp FLOAT; //定义变量temp
DECLARE match_price CURSOR FOR SELECT price FROM food; //定义光标match_price
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE match_price; //定义条件处理,如果没有遇到关闭光标,就退出存储过程
SET @sum=0; //为临时变量sum赋值
SELECT COUNT(*) INTO COUNT FROM food //使用select..into语句来为输出变量count赋值
WHERE price>price_info1 AND price<price_info2;
OPEN match_price; //打开光标
REPEAT //执行光标
FETCH match_price INTO temp; //使用光标match_price
IF temp>price_info1 AND temp<price_info2 //执行条件语句
THEN SET @sum=@sum+temp;
END IF;
UNTIL 0 END REPEAT; //结束循环
CLOSE match_price; //关闭光标
END &&
DELIMITER; //将sql语句的结束符号变成“;”
(4)使用call语句来调用存储过程。查询价格在2~18之间的食品种数
CALL food_price_count(2,18,@count);
(5)使用select语句来查看结果
SELECT @count,@sum;
(6)使用drop语句删除存储过程
DROP PROCEDURE food_price_count;
证明存储过程已经删除
SHOW CREATE PROCEDURE food_price_count;
(7)这个存储过程的功能也可以通过存储函数来实现
DELIMITER &&
CREATE FUNCTION food_price_count1(price_info1 FLOAT,price_info2 FLOAT)
RETURNS INT READS SQL DATA
BEGIN
RETURN(SELECT COUNT(*) FROM food
WHERE price>price_info1 AND price<price_info2);
END &&
DELIMITER ;
存储函数只能返回一个值,所以只实现了计算机满足条件的食品种数,使用return来将计算的食品种数返回回来
SELECT food_price_count1(2,18);
(8)删除这个存储过程
DROP FUNCTION food_price_count1;
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)
);
(2)插入数据
INSERT INTO food VALUES(1,'AA饼干','AA饼干厂',2.5,'2008',3,'北京');
INSERT INTO food (id,NAME,company,price,produce_time,validity_time,address)
VALUES(2,'CC牛奶','CC牛奶厂',3.5,'2009',1,'河北');
INSERT INTO food VALUES
(NULL,'EE果冻','EE果冻厂',1.5,'2007',2,'北京'),
(NULL,'FF咖啡','FF咖啡厂',20,'2002',5,'天津'),
(NULL,'GG奶糖','GG奶糖厂',14,'2003',3,'广东');
(3)创建存储过程food_price_count
DELIMITER && //使用“delimiter &&”将sql语句结束付符号变成&&
CREATE PROCEDURE food_price_count(IN price_info1 FLOAT,IN price_info2 FLOAT,OUT COUNT INT)
READS SQL DATA
BEGIN
DECLARE temp FLOAT; //定义变量temp
DECLARE match_price CURSOR FOR SELECT price FROM food; //定义光标match_price
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE match_price; //定义条件处理,如果没有遇到关闭光标,就退出存储过程
SET @sum=0; //为临时变量sum赋值
SELECT COUNT(*) INTO COUNT FROM food //使用select..into语句来为输出变量count赋值
WHERE price>price_info1 AND price<price_info2;
OPEN match_price; //打开光标
REPEAT //执行光标
FETCH match_price INTO temp; //使用光标match_price
IF temp>price_info1 AND temp<price_info2 //执行条件语句
THEN SET @sum=@sum+temp;
END IF;
UNTIL 0 END REPEAT; //结束循环
CLOSE match_price; //关闭光标
END &&
DELIMITER; //将sql语句的结束符号变成“;”
(4)使用call语句来调用存储过程。查询价格在2~18之间的食品种数
CALL food_price_count(2,18,@count);
(5)使用select语句来查看结果
SELECT @count,@sum;
(6)使用drop语句删除存储过程
DROP PROCEDURE food_price_count;
证明存储过程已经删除
SHOW CREATE PROCEDURE food_price_count;
(7)这个存储过程的功能也可以通过存储函数来实现
DELIMITER &&
CREATE FUNCTION food_price_count1(price_info1 FLOAT,price_info2 FLOAT)
RETURNS INT READS SQL DATA
BEGIN
RETURN(SELECT COUNT(*) FROM food
WHERE price>price_info1 AND price<price_info2);
END &&
DELIMITER ;
存储函数只能返回一个值,所以只实现了计算机满足条件的食品种数,使用return来将计算的食品种数返回回来
SELECT food_price_count1(2,18);
(8)删除这个存储过程
DROP FUNCTION food_price_count1;