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

#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;

#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 ;

#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 ;