实例1:
为了演示MySQL中的存储过程,我们先创建一些表和数据:
drop table if exists my_test_table;
create table my_test_table (
id integer primary key not null AUTO_INCREMENT,
name varchar(),
age integer,
point double
);
insert into my_test_table (name,age,point) values
('刘德华',,10.0),('周杰伦',,20.0),('周星驰',,40.0),('范晓萱',,20.0),('陈绮贞',,10.0),
('朴树',,12.0),('谢天笑',,10.0),('谢春花',,10.0),('房东的猫',,100.0),('许巍',,10.0);
然后创建一个空参数的存储过程:
delimiter //
create procedure test_procedure1()
begin
select avg(point) as pointaverage
from my_test_table;
end //
delimiter ;
然后运行这个存储过程:
call test_procedure1()
可以看到结果如下:
pointaverage|
------------|
24.2|
删除存储过程:
drop procedure test_procedure1
实例2:
举例:求两个数的最大值
if实现
#DELIMITER $$
CREATE PROCEDURE compare(IN n1 INT,IN n2 INT)
BEGIN
SET @res=0;
IF n1 > n2 THEN
SET @res=n1;
ELSEIF n1 = n2 THEN
SET @res=n1;
ELSE
SET @res=n2;
END IF;
END #$$
#DELIMITER ;
call compare(100,2);
select @res;
CASE语句
#DELIMITER $$
CREATE PROCEDURE compare2(IN n1 INT,IN n2 INT)
BEGIN
SET @res=0;
CASE
WHEN n1>n2 THEN
SET @res=n1;
WHEN n1=n2 THEN
SET @res=n1;
ELSE
SET @res=n2;
END CASE;
END #$$
#DELIMITER ;
WHILE语句
#delimiter $$
CREATE PROCEDURE inserinfo3()
BEGIN
DECLARE x INT DEFAULT 9;
ins: WHILE x<=18 DO
SET x=x+1;
INSERT INTO users(name,gender) VALUES(x,'女');
END WHILE;
END #$$
#delimiter ;