MySQL 存储过程

【黑马程序员】两小时带你玩转MySQL数据库存储过程【配套源码+笔记】_哔哩哔哩_bilibili

一、语法

delimter $$ 

声明结束符。因为MySQL 默认使用“；”作为结束符，而在存储过程中，会使用“；”作为一段语句的结束，导致“；”使用冲突。

1. 语法结构

CREATE

        [DEFINER = user]

        PROCEDURE sp_name ( [ proc_parameter[...] ] )

        [chrarcteristic ... ] routine_body

-- proc_parameter 参数部分，可以如下书写：

        [ IN | OUT | INOUT ] param_name type

        -- type 类型可以是 MySQL 支持的所有类型

-- routine_body（程序体）部分，可以书写合法的 SQL 语句

        BEGIN ... END

简单演示：

delimiter $$

CREATE PROCEDURE hello_procedure()
BEGIN
    select * from users;
END $$

使用：

call hello_procedure();

2. 变量及赋值

类比一下 java 中的局部变量和成员变量的声明和使用

（1）局部变量

用户自定义，在 begin/end 块中有效

语法：

声明变量： declare var_name type [ default var_value ];

举例：declare nickname varchar(32);

-- set 赋值

delimiter $$

create procedure sp_var01()
begin
    declare nickname varchar(32) default 'unkown';
    set nickname = 'ZS';
    select nickname;
end $$

 -- into 赋值

delimiter $$

create procedure sp_var_into()
begin
    declare emp_name varchar(32) default 'unkown';
    declare emp_no int default 0;
    select e.empno,e.ename into emp_no,emp_name from emp e where e.empno = 7839;
    select emp_no,emp_name ;
end $$

（2）用户变量

用户自定义，当前会话（连接）有效

  --  赋值

delimiter $$

create procedure sp_var02()
begin
    set @nickname = 'ZS';
end $$

call sp_var02() $$
select @nickname $$

（3）会话变量

 由系统提供，当前会话（连接）有效

语法：

@@session.var_name

举例：

-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.unique_checks;
-- 修改会话变量
set @@session.unique_checks = 0;

（4）全局变量

  由系统提供，整个 mysql 服务器有效

语法：

@@global.var_name

举例：

-- 查看全局变量中变量名有char的记录
show global variables like '%char%';

-- 查看全局变量character_set_client的值
select @@global.character_set_client;

3. 入参出参

-- 语法

in | out | inout  param_name type

（1）-- IN 类型演示

delimiter $$

create procedure sp_param01(in age int)
begin
    set @user_age = age;
end $$

call sp_param01(10) $$
select @user_age $$

 （2）-- OUT 类型演示，只负者输出

delimiter $$

create procedure sp_param02(in loc varchar(64),out dedpt_no int(11))
begin
    select d.deptno into dept_no from dept d where d.loc = loc;
end $$

-- 测试
set @dept_no = 100;
call sp_param02('DALLAS',@dept_no); $$
select @dept_no; $$

  （3）-- INOUT 类型 

delimiter $$

create procedure sp_param03(inout name varchar)
begin
    select name = concat('hello',name);
end $$

-- 测试
set @username = '小妹';
call sp_param03(@username);
select @user_name;

4. 流程控制-判断

（1）if

语法：

IF search_condition THEN statement_list

        [ ELSEIF search_condition THEN  statement_list ] ...

        [ ELSE statement_list ]

END IF

前置知识点：timestampdiff( unit,exp1,exp2-) 取差值exp2-exp1 差值，单位是 unit

select timestampdiff( year,e.hiredate,now() ) from emp e where e.empno = '7499';

delimiter $$

create procedure sp_hire_if()
begin
    declare result varchar(32);
    if timestampdiff(year,'2001-01-01',now()) > 40
        then set result = '元老';
    elseif timestampdiff(year,'2001-01-01',now()) > 35
        then set result = '老员工';
    else
        set result = '新手';
    end if;
    select result;
end $$

（2）case

语法一：（类比 java 的 switch）

CASE case_value

        WHEN when_value THEN statement_list

        [ WHEN when_value THEN statement_list ] ...

       [ ELSE statement_list ]

END CASE

语法二：

CASE  

        WHEN search_condition THEN statement_list

        [ WHEN search_condition THEN statement_list ] ...

       [ ELSE search_condition ]

END CASE

5. 流程控制-循环

（1）loop

语法：

[ begin_label : ] LOOP

        statement_list 

END IOOP [ end_label ]

注意：

loop 是死循环，需要手动退出循环，可以把 leave 看成 java 中的 break；iterate（继续循环）看成 java 中的 continue。

leave 控制循环的退出

delimiter $$

create procedure sp_flow_loop()
begin
    declare c_index int default 1;

    num_loop:loop

        select c_index;
        if c_index >= 10
        then leave num_loop;
        end if;

        set c_index = c_index + 1;

    end loop num_loop;
    
end $$

 iterate + leave 控制循环

delimiter $$

create procedure sp_flow_loop02()
begin
    declare c_index int default 1;
    declare result_str varchar(256) default '1';

    cnt:loop

        set c_index = c_index + 1;
        set result_str = concat(result_str ,c_index);

        
        if c_index < 10 then iterate cnt;
        end if;

        leave cnt;

    end loop cnt;
    select result_str;
end $$

（1）repeat

[ begin_label : ] REPEAT

        statement_list 

UNTIL search_condition          -- 直到。。。为止

END REPEAT [ end_label ]

delimiter $$

create procedure sp_flow_repeat()
begin
    declare c_index int default 1;
    declare result_str varchar(256) default '1';

    count_lab:repeat

        set c_index = c_index + 1;
        set result_str = concat(result_str ,c_index);
        until c_idnex >= 10
        
    end repeat count_lab;
    select result_str;
end $$

（1）while

[ begin_label : ] WHILE  search_condition DO         

        statement_list 

END WHILE  [ end_label ]

delimiter $$

create procedure sp_flow_while()
begin
    declare c_index int default 1;
    declare result_str varchar(256) default '1';

    while  c_idnex < 10 do
        set c_index = c_index + 1;
        set result_str = concat(result_str ,c_index);

    end while;
    select result_str;
end $$

6. 流程控制-退出、继续循环

leave 看成 java 中的 break；iterate（继续循环）看成 java 中的 continue。

7. 游标

类比 jdbc 的 ResultSet

-- 声明语法

DECLARE cursor_name CURSOR FOR select_statement

-- 打开语法

OPEN cursor_name

-- 取值语法

FETCH cursor_name INTO var_name [ ,var_name ] ...

-- 关闭语法

CLOSE cursor_name

需求：按照部门名称查询员工，通过 select 查看员工的编号、姓名、薪资

delimiter $$

create procedure sp_create_table02(in dept_name varchar(32))
begin
    declare emp_no int;
    declare emp_name varchar(32);
    declare emp_sal decimal(7,2);
    declare exit_flag int default 0;

    declare emp_cursor cursor for
        select e.empno,e.ename,e.sal
        from emp e inner join dept d on e.deptno = d.deptno
        where d.dname = dept_name;

    declare continue handler for not found set exit_flag = 1;

    open emp_cursor;

    c_loop:loop

        fetch emp_cursor into emp_no ,emp_name ,emp_sal ;

        if exit_flag != 1    
            select emp_no ,emp_name ,emp_sal ;
            -- ITERATE c_loop;
        else
            then leave c_loop;
        end if;

    end loop c_loop;

    select @sex_res;
    close emp_cursor;

end $$

注意 ：在语法中，变量声明、handler 声明是必须按照先后顺序书写的，否则创建存储过程出错。

8. 存储过程中的 handler

 DECLARE handler_action HANDLER

        FOR condition_value [ , condition_value ] ...

        statement

handler_action：｛

        CONTINUE

        | EXIT

        | UNDO

｝

condition_value：｛

        mysql_error_code

        | SQLSTATE [ VALUE ] sqlstate_value

        | condition_name

        | SQLWARING

        | NOT FOUND

        | SQLEXCEPTION

｝

SQLWARING：Shorthand for the class of SQLSTATE values that begin whit '01'（以“01”开头的SQLSTATE值类的缩写）

NOT FOUND：Shorthand for the class of SQLSTATE values that begin whit '02'

SQLEXCEPTION：Shorthand for the class of SQLSTATE values that do not begin whit '00'，'01'，or'02'（不以“00”、“01”、“02”开头的SQLSTATE值类的缩写）

对于游标遍历到尽头时的错误码处理，我们可以使用：

Error number: 1329 ; Symbol:ER_SP_PETCH_NO_DATA; SQLSTATE: 02000
Message: No data - zero rows fetched,selected, or processed