存储过程及存储函数Demo代码

select * from city;

# 定义分隔符
delimiter $;

# 创建基础存储过程
create procedure test()
begin
    select 'stephen';
end $;

# 调用存储过程
call test();

# 表关联存储过程
create procedure test01()
begin
    select * from city where Name like '%wuhan%';
end $;

call test01();

drop procedure test01;

# 带入参存储过程
create procedure test01(out population int)
begin
    set population = 100;
end $;

call test01(@population) $;

select @population;

drop procedure test01;

# 带if判断
create procedure test03(in edge int, out total int)
begin
    declare origin int default 0;
    set total = 0;
    while origin <= edge do
        set total = total + origin;
        set origin = origin + 1;
        end while ;
end $;

create procedure test02(in population int)
begin
    declare description varchar(50);
    case
        when population > 1000 then
            set description = '特大城市';
            select concat(description, ': sasas');
        when population > 500 and population < 1000 then
            set description = '大型城市';
            select concat(description, ': sasas');
        else
            set description = '小型城市';
            select concat(description, ': sasas');
    end case;

end $;

call test02(99);

drop procedure test02;

drop procedure test03;

call test03(100, @total);

select @total;

drop procedure test04;

# repeat
create procedure test04(in edge int, out total int)
begin
    declare origin int default 0;
    set total = 0;
    repeat
        set total = total + origin;
        set origin = origin + 1;
        until origin = edge + 1
    end repeat;
end $;

call test04(100, @total);

select @total;

# loop
create procedure test05(in edge int, out total int)
begin
    declare origin int default 0;
    set total = 0;
    l:loop
        set total = total + origin;
        set origin = origin + 1;
        if origin >= edge + 1 then
            leave l;
        end if;
    end loop l;
end $;

call test05(10, @total);

select @total;

drop procedure test06;

# cursor
drop procedure test06 $;
create procedure test06()
begin
    declare id int;
    declare name varchar(50);
    declare countryCode varchar(50);
    declare district varchar(50);
    declare population int;
    declare cursor01 Cursor for select * from city;

    open cursor01;
    fetch cursor01 into id,name,countryCode,district,population;
    select id,name,countryCode,district,population;

    fetch cursor01 into id,name,countryCode,district,population;
    select id,name,countryCode,district,population;

    fetch cursor01 into id,name,countryCode,district,population;
    select id,name,countryCode,district,population;
    close cursor01;
end $;

call test06();

create procedure test07()
begin
    declare id int;
    declare name varchar(50);
    declare countryCode varchar(50);
    declare district varchar(50);
    declare population int;

    declare total int default 1;



    declare cursor01 Cursor for select * from city;
    select COUNT(1) into total from city;



    open cursor01;

    repeat
        fetch cursor01 into id,name,countryCode,district,population;
        select id,name,countryCode,district,population;
        until id = total
    end repeat;
    close cursor01;
end $;

call test07() $;

create procedure test08()
begin
    declare id int;
    declare name varchar(50);
    declare countryCode varchar(50);
    declare district varchar(50);
    declare population int;

    declare total int default 1;

    declare cur int default 1;



    declare cursor01 Cursor for select * from city;
    select COUNT(1) into total from city;



    open cursor01;

    repeat
        fetch cursor01 into id,name,countryCode,district,population;
        select id,name,countryCode,district,population;
        set cur = cur + 1;
        until cur = total
    end repeat;
    close cursor01;
end $;

call test08() $;

create procedure test09()
begin
    declare id int;
    declare name varchar(50);
    declare countryCode varchar(50);
    declare district varchar(50);
    declare population int;

    declare total int default 1;

    declare cur int default 1;



    declare cursor01 Cursor for select * from city;
    select COUNT(1) into total from city;



    open cursor01;

    l:loop
        fetch cursor01 into id,name,countryCode,district,population;
        select id,name,countryCode,district,population;
        set cur = cur + 1;
        if cur = total then
            leave l;
        end if;
    end loop;
    close cursor01;
end $;

call test09() $;

create procedure test10()
begin
    declare id int;
    declare name varchar(50);
    declare countryCode varchar(50);
    declare district varchar(50);
    declare population int;

    declare total int default 1;

    declare cur int default 1;



    declare cursor01 Cursor for select * from city;
    select COUNT(1) into total from city;



    open cursor01;

    while cur <= total do
        fetch cursor01 into id,name,countryCode,district,population;
        select id,name,countryCode,district,population;
        set cur = cur + 1;
    end while ;
    close cursor01;
end $;

call test10() $;

create procedure test11()
begin
    declare id int;
    declare name varchar(50);
    declare countryCode varchar(50);
    declare district varchar(50);
    declare population int;

    declare total int default 1;

    declare cur int default 1;



    declare cursor01 Cursor for select * from city;
#     select COUNT(1) into total from city;
    declare exit handler for not found set cur = 0;



    open cursor01;

    while cur = 1 do
        fetch cursor01 into id,name,countryCode,district,population;
        select id,name,countryCode,district,population;
    end while ;
    close cursor01;
end $;

drop procedure test11 $;

call test11() $;

create function fun1(countryCodes varchar(20))
returns int
begin
    declare cityCount int;
    select COUNT(1) into cityCount from city where CountryCode = countryCodes;
    return cityCount;
end $;