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 ;
