- 存储过程简单示例
delimiter $$ #设置终结符号为$$
create procedure testa()
begin
select * from db_name;
end;
$$
delimiter ;
- 存储过程参数示例
delimiter $$ #设置终结符号为$$
create procedure testa(IN my_id int) #IN、OUT、INOUT，参数可读、可写、都可
begin
select * from db_name where uid=my_id;
end;
$$
delimiter ;
IN:参数传入时有具体值，可用在sql语句中使用，不能更改值
OUT:预设一个参数，可在sql语句中对其赋值
- 存储过程条件语句示例
delimiter $$ #设置终结符号为$$
create procedure testa(IN my_id int) #IN、OUT、INOUT，参数可读、可写、都可
begin
declare my_status int default 0;
select status into my_status from db_name where uid=my_id; #将获取的status赋值给my_status
IF(my_status=1)
THEN
UPDATE users set score=concat(score,'1') where uid=my_uid;
ELSEIF(my_status=2)
THEN
UPDATE users set score=score+2 where uid=my_uid;
ELSE
UPDATE users set score=score+3 where uid=my_uid;
ENDIF;
end;
$$
delimiter ;
- 存储过程循环语句示例
delimiter $$ #设置终结符号为$$
create procedure testa(IN my_id int) #IN、OUT、INOUT，参数可读、可写、都可
begin
while(i<10) do
begin
sql语句
end;
end;
$$
delimiter ;
delimiter $$ #设置终结符号为$$
create procedure testa(IN my_id int) #IN、OUT、INOUT，参数可读、可写、都可
begin
repeat
begin
sql语句
end;
UNTIL i>10
END repeat;
end;
$$
delimiter ;
