DROP PROCEDURE IF EXISTS variable_demo;

delimiter //

CREATE PROCEDURE variable_demo()

BEGIN

select 'hello world!';

END;//

delimiter ;

DROP PROCEDURE IF EXISTS demo1;

delimiter //

CREATE PROCEDURE demo1(IN type int)

BEGIN

    DECLARE c varchar(500);

    IF type = 0 THEN

        set c = 'param is 0';

    ELSEIF type = 1 THEN

        set c = 'param is 1';

    ELSE

        set c = 'param is others, not 0 or 1';

    END IF;

    select c;

END;//

delimiter ;

DROP PROCEDURE IF EXISTS demo2;

delimiter //

CREATE PROCEDURE demo2(IN type int)

BEGIN

    DECLARE c varchar(500);

    CASE type

    WHEN 0 THEN

        set c = 'param is 0';

    WHEN 1 THEN

        set c = 'param is 1';

    ELSE

        set c = 'param is others, not 0 or 1';

    END CASE;

    select c;

END;//

delimiter ;

DROP PROCEDURE IF EXISTS demo3;

delimiter //

CREATE PROCEDURE demo3()

BEGIN

    declare type int;

    DECLARE c varchar(500);

    declare i int;

    set i=0;

    while i<10 do

    IF i = 0 THEN

        set c = 'param is 0';

    ELSEIF i = 1 THEN

        set c = 'param is 1';

    ELSE

        set c = 'param is others, not 0 or 1';

    END IF;

    select c;

    set i=i+1;

    end while;

END;//

delimiter ;

//sql语句的操作

DROP PROCEDURE IF EXISTS demo4;

delimiter //

create PROCEDURE  demo4()

begin

declare cnt int;

select count(*) into cnt from user_order_detail_1;

select cnt;

end; //

delimiter ;

DROP PROCEDURE IF EXISTS demo5;

delimiter //

create PROCEDURE  demo5()

begin

declare cnt int;

while select count(*) into cnt from user_order_detail_1 do

end loop;

select cnt;

end; //

delimiter ;