1.声明存储过程：关键字create procedure begin end
create procedure name（）
begin
end;
2.声明变量 关键字 declare ，变量设值set，或者select into；
declare var = param； select field into var from table;
3.存储过程可以有多个begin end 代码块，声明全局变量可在多个代码块中起作用；
4.存储过程传入参数in,传出参数out声明,既是传入参数也是传出参数用INOUT；注传参用@变量，不能是常量；
根据id查下name：
create procedure query（in userId int，out userName varchar（32））；
set @userName = ‘’；
call query(1,@userName);
select @userName as userName;
5.INOUT例子；根据id和name查询出id和name；
set @userId= 2，@userName = ‘’；
call testQuery(@userId,@userName)；
select @userId as userId ,@userName as userName;
6.存储过程条件语句：if（）then ...else...end if;多条件语句：if()then...elseif() then ...else ...end if;
7.存储过程循环语句：whie() do ... end while; repepat ... until ...end repeat;
8.游标cursor用于取查询数据结果集的一条记录，流程为： 声明游标，打开游标，获取下一行记录，释放游标
声明cursor：declare cursor_name cursor for table(这里table为取出来的结果集)；
打开cursor： open cursor_name;
获取下一行数据：fetch cursor_name into @variable;
关闭释放游标：close cursor_name;
游标例子：
create procedure test11()
begin
declare stopflag int default 0;
declare username VARCHAR(32);
-- 创建一个游标变量，declare 变量名 cursor ...
declare username_cur cursor for select name from users where id%2=0;
-- 游标是保存查询结果的临时区域
-- 游标变量username_cur保存了查询的临时结果，实际上就是结果集
-- 当游标变量中保存的结果都查询一遍(遍历),到达结尾，将变量stopflag设置为1，用于循环中判断是否结束
declare continue handler for not found set stopflag=1;
open username_cur; -- 打卡游标
fetch username_cur into username; -- 游标向前走一步，取出一条记录放到变量username中
while(stopflag=0) do -- 如果游标还没有结尾，就继续
begin
-- 在用户名前门拼接 '_cur' 字符串
update users set name=CONCAT(username,'_cur') where name=username;
fetch username_cur into username;
end;
end while; -- 结束循环
close username_cur; -- 关闭游标
end;
9.自定义函数： 和存储过程的区别是 自定义函数有返回值
create function getusername(userid int) returns varchar(32)
reads sql data -- 从数据库中读取数据，但不修改数据
begin
declare username varchar(32) default '';
select name into username from users where id=userid;
return username;
end;
概括：
1.创建函数使用create function 函数名(参数) returns 返回类型；
2.函数体放在begin和end之间；
3.returns指定函数的返回值；
4.函数调用使用select getusername()。
涉及到的存储过程案例：
create PROCEDURE test_procedure_query()
BEGIN
SELECT * from person;
SELECT * from person where id = '1';
end;
CREATE procedure test_procedure_query_with_param()
begin
declare name VARCHAR(255);
set name = 'zhang';
SELECT name;
end;
create procedure test_procedure_param()
begin
DECLARE userCount int default 0;
DECLARE maxCountId int default 0;
begin
SELECT count(*) into userCount from pserson;
SELECT max(id) into maxCountId from person;
SELECT userCount as count ,maxCountId as countId;
end;
begin
SELECT userCount,maxCountId;
end;
end;
create procedure test_param_inandout(in userId int ,out userName varchar(255))
begin
SELECT name into userName from person where id = userId;
end ;
create procedure test_param_bothinout(inout userId int,inout userName varchar(255))
begin
SELECT id as userId,name as userName into userId ,userName from person where id = userId;
end;
create procedure test_query_condition(in userId int)
begin
DECLARE userName VARCHAR(255) DEFAULT('');
if(userId % 2 = 0)
then
SELECT name into userName from person;
SELECT userName;
else
SELECT userId;
end if;
end;
create procedure test_query_morecondition(in userId int)
begin
DECLARE res int DEFAULT(0);
SELECT age into res FROM person where id = userId;
if(res % 2 = 0)
then
update person set age = age + 10;
elseif(res % 2 = 1)
then
update person set age = age + 20;
else
update person set ate = ate + 30;
end if;
end;
create procedure test_while_loop()
begin
DECLARE i int DEFAULT(0);
while(i<10) do
set i = i+1;
insert into test values(i);
end while;
end;
call test_while_loop();
call test_query_morecondition(4);
set @userId = 1;
call test_query_condition(@userId);
set @userId = 2;
set @userName = '';
call test_param_bothinout(@userId,@userName);
SELECT @userId as userId,@userName as userName;
set @userName='';
call test_param_inandout(5,@userName);
SELECT @userName as userName;
call test_procedure_param();
call test_procedure_query_with_param();
EXPLAIN SELECT * from person where id = 1 ;
EXPLAIN SELECT * FROM person WHERE id = '1';
call test_procedure_query();
create function test_getusername(userid int) returns varchar(32)
reads sql data; -- 从数据库中读取数据，但不修改数据
begin
declare username varchar(32) default '';
select name into username from person where id=userid;
return username;
end;