存储过程-进阶

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;