实例1:
为了演示MySQL中的存储过程,我们先创建一些表和数据:
drop table if exists my_test_table;
create table my_test_table (
id integer primary key not null AUTO_INCREMENT,
name varchar(20),
age integer,
point double
);
insert into my_test_table (name,age,point) values
('刘德华',40,10.0),('周杰伦',30,20.0),('周星驰',20,40.0),('范晓萱',20,20.0),('陈绮贞',10,10.0),
('朴树',30,12.0),('谢天笑',40,10.0),('谢春花',20,10.0),('房东的猫',50,100.0),('许巍',30,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 ;
-----------------------------------
公众号【Csharp编程大全】,需要进技术群交流的,请添加小编mm1552923!