2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤；注意点及常见报错问题所对应的解决方法]

盛透侧视攻城狮

发布于 2024-10-22 09:21:54

20400

代码可运行

文章被收录于专栏：超前沿网络空间安全全栈学习宝典超前沿网络空间安全全栈学习宝典

运行总次数：0

代码可运行

Mysql And Redis基础与进阶操作系列（9）之存储过程

1.概念

存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于 JAVA语言中的方法；

存储过程就是数据库 SQL 语言层面的代码封装与重用。

2. 作用之优点

存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
存储过程允许标准组件式编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的 Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

格式

delimiter 自定义结束符号 create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...) begin
sql语句 end 自定义的结束符合 delimiter ;

示例

创建存储过程

delimiter $$ create procedure proc01() begin select empno,ename from emp; 

end $$ delimiter ;

调用存储过程

call proc01();

3.变量

局部变量:

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

格式

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

举例：

declare nickname varchar(32);


delimiter $$

create procedure proc02()

begin
    declare var_name01 varchar(20) default ‘aaa’;  -- 定义局部变量

    set var_name01 = ‘zhangsan’;  

    select var_name01;

end $$

delimiter ;

-- 调用存储过程

call proc02();

还可以使用 SELECT..INTO 语句为变量赋值。语法如下：

select col_name [...] into var_name[,...] from table_name wehre condition 


其中：
col_name 参数表示查询的字段名称；
var_name 参数是变量的名称；
table_name 参数指表的名称；
condition 参数指查询条件。

注意：

当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。

举例：

elimiter $$

create procedure proc03()

begin
 
  declare my_ename varchar(20) ;

  select ename into my_ename from emp where empno=1001;

  select my_ename;

end $$

delimiter ;

调用存储过程

call proc03();

举例

delimiter $$

create procedure proc04()

begin
    set @var_name01  = 'ZS';

end $$

delimiter;

调用存储过程

call proc04() ;

select @var_name01  ;--可以看到回显结果

系统变量：

系统变量又分为全局变量与会话变量全局变量
在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。

系统变量-全局变量：由系统提供，在整个数据库有效。

格式

语法：

@@global.var_name

举例

查看全局变量

show global variables;

查看某全局变量

select @@global.auto_increment_increment;

修改全局变量的值

set global sort_buffer_size = 40000; 

set @@global.sort_buffer_size = 40000;

格式

语法：

@@session.var_name

举例

查看会话变量

show session variables;

查看某会话变量

select @@session.auto_increment_increment;

修改会话变量的值

set session sort_buffer_size = 50000; 

set @@session.sort_buffer_size = 50000 ;

4. 参数传递

In

in 表示传入的参数，可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。

封装有参数的存储过程，传入员工编号，查找员工信息

delimiter $$

create procedure dec_param01(in param_empno varchar(20))

begin
        select * from emp where empno = param_empno;
end $$
 
delimiter ;

call dec_param01('1001');

封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息

delimiter $$

create procedure dec_param0x(in dname varchar(50),in sal decimal(7,2),)

begin
        select * from dept a, emp b where b.sal > sal and a.dname = dname;
end $$
 
delimiter ;

call dec_param0x('学工部',20000);

out

out 表示从存储过程内部传值给调用者

use mysql7_procedure;

封装有参数的存储过程，传入员工编号，返回员工名字

delimiter $$

create procedure proc08(in empno int ,out out_ename varchar(50) )

begin

  select ename into out_ename from emp where emp.empno = empno;

end $$
 
delimiter ;

call proc08(1001, @o_ename);

select @o_ename;

封装有参数的存储过程，传入员工编号，返回员工名字和薪资

delimiter $$

create procedure proc09(in empno int ,out out_ename varchar(50) ,out out_sal 

decimal(7,2))

begin

  select ename,sal into out_ename,out_sal from emp where emp.empno = empno;

end $$
 
delimiter ;

call proc09(1001, @o_dname,@o_sal);

select @o_dname;

select @o_sal;

inout

表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）

传入员工名，拼接部门号，传入薪资，求出年薪

delimiter $$

create procedure proc10(inout inout_ename varchar(50),inout inout_sal int)

begin

  select concat(deptno,"_",inout_ename) into inout_ename from emp where ename = 

inout_ename;

  set inout_sal = inout_sal * 12;  

end $$

delimiter ;



set @inout_ename = '关羽';

set @inout_sal = 3000;

call proc10(@inout_ename, @inout_sal) ;

select @inout_ename ;

select @inout_sal ;

小结

in 输入参数，也就是参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被返回

out 输出参数:该值可在存储过程内部被改变，并向外输出

inout 输入输出参数，既能输入一个值又能传出来一个值

5. 流程控制

5.1流程控制-判断

格式

IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句，与编程语言中的if、else if、else语法类似，其语法格式如下：

if search_condition_1 then statement_list_1

    [elseif search_condition_2 then statement_list_2] ...

    [else statement_list_n]

end if

举例

输入学生的成绩，来判断成绩的级别：

delimiter $$

create procedure proc_12_if(in score int)

begin
  if score < 60 
      then
          select '不及格';
    elseif score < 80
      then
          select '及格' ;
    elseif score >= 80 and score < 90
       then 
           select '良好';
  elseif score >= 90 and score <= 100
       then 
           select '优秀';
     else
       select '成绩错误';
  end if;
end $$

delimiter  ;

call proc_12_if(120)


/*score < 60 :不及格
 score >= 60 , score <80 :及格
   score >= 80 , score < 90 :良好
   score >= 90 , score <= 100 :优秀
   score > 100 :成绩错误
*/

输入员工的名字，判断工资的情况

delimiter $$

create procedure proc12_if(in in_ename varchar(50))

begin

    declare result varchar(20);
    declare var_sal decimal(7,2);
        select sal into var_sal from emp where ename = in_ename;
    if var_sal < 10000 
        then set result = '试用薪资';
    elseif var_sal < 30000
        then set result = '转正薪资';
    else 
        set result = '元老薪资';
    end if;

select result;

end$$

delimiter ;

call proc12_if('庞统');

5.2 流程控制-case

格式

CASE是另一个条件判断的语句，类似于编程语言中的switch语法

语法一（类比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 statement_list]

end case

示例

语法一

delimiter $$

create procedure proc14_case(in pay_type int)

begin
  case pay_type
        when  1 
          then 
              select '微信支付' ;
        when  2 then select '支付宝支付' ;
        when  3 then select '银行卡支付';
      else select '其他方式支付';
    end case ;
end $$

delimiter ;
 

call proc14_case(2);
call proc14_case(4);

语法二

delimiter $$

create procedure proc_15_case(in score int)

begin
  case
  when score < 60 
      then
          select '不及格';
    when score < 80
      then
           select '及格' ;
    when score >= 80 and score < 90
       then 
           select '良好';
  when score >= 90 and score <= 100
       then 
           select '优秀';
     else
       select '成绩错误';
  end case;
end $$

delimiter  ;
 
call proc_15_case(88);

5.3 流程控制-循环

概述

循环是一段在程序中只出现一次,但可能会连续运行多次的代码。

循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环

循环分类： while ;repeat ;loop

循环控制：

leave 类似于 break，跳出，结束当前所在的循环

iterate类似于 continue，继续，结束本次循环，继续下一次

流程控制-循环-while

格式

【标签:】while 循环条件 do
   循环体;
end while【 标签】

举例

创建测试表

create table user (

   uid int primary_key,

   username varchar ( 50 ),

    password varchar ( 50 )

);

存储过程-while

delimiter $$

create procedure proc16_while1(in insertcount int)

begin
    declare i int default 1;
   label:while i<=insertcount do
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
        set i=i+1;
    end while label;
end $$

delimiter ;

call proc16_while(10);

存储过程-while + leave

truncate table user;

delimiter $$

create procedure proc16_while2(in insertcount int)

begin
    declare i int default 1;
   label:while i<=insertcount do
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
        if i=5 then leave label;
        end if;
        set i=i+1;
    end while label;

end $$

delimiter ;
 
call proc16_while2(10);

存储过程-while+iterate

truncate table user;

delimiter $$

create procedure proc16_while3(in insertcount int)

begin
    declare i int default 1;
   label:while i<=insertcount do
        set i=i+1;
        if i=5 then iterate label;
        end if;
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
    end while label;
end $$

delimiter ;

call proc16_while3(10);

流程控制-循环-repeat

格式

[标签:]repeat 
 循环体;
until 条件表达式
end repeat [标签];

举例

use mysql7_procedure;
truncate table user;

存储过程-循环控制-repeat

delimiter $$

create procedure proc18_repeat(in insertCount int)

begin
     declare i int default 1;
     label:repeat
         insert into user(uid, username, password) values(i,concat('user-',i),'123456');
         set i = i + 1;
         until i  > insertCount
     end repeat label;
     select '循环结束';
end $$

delimiter ;
 
call proc18_repeat(100);

流程控制-循环-loop

格式

[标签:] loop
 循环体;
  if 条件表达式 then 
     leave [标签]; 
  end if;
end loop;

举例

truncate table user;

存储过程-循环控制-loop

delimiter $$

create procedure proc19_loop(in insertCount int) 

begin
     declare i int default 1;
     label:loop
         insert into user(uid, username, password) values(i,concat('user-',i),'123456');
         set i = i + 1;
         if i > 5 
          then 
           leave label;
         end if;
     end loop label;
     select '循环结束';
end $$

delimiter ;
 
call proc19_loop(10);

5.4 游标

简介

游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。

光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE.

格式

声明语法

declare cursor_name cursor for select_statement

打开语法

open cursor_name

取值语法

fetch cursor_name into var_name [, var_name] ...

关闭语法

close cursor_name

举例

use mysql7_procedure;

delimiter $$

create procedure proc20_cursor(in in_dname varchar(50))

begin

定义局部变量

declare var_empno varchar(50);

declare var_ename varchar(50);

declare var_sal  decimal(7,2);

声明游标

declare my_cursor cursor for

  select empno , ename, sal 

    from dept a ,emp b

    where a.deptno = b.deptno and a.dname = in_dname;

打开游标

open my_cursor;

通过游标获取每一行数据

label:loop

        fetch my_cursor into var_empno, var_ename, var_sal;

        select var_empno, var_ename, var_sal;

    end loop label;

关闭游标

close my_cursor;

end

最后调用存储过程

call proc20_cursor('销售部');

本例sql汇总

use mysql7_procedure;

delimiter $$

create procedure proc20_cursor(in in_dname varchar(50))

begin
 -- 定义局部变量
 declare var_empno varchar(50);
 declare var_ename varchar(50);
 declare var_sal  decimal(7,2);
 
 -- 声明游标
 declare my_cursor cursor for
  select empno , ename, sal 
    from dept a ,emp b
    where a.deptno = b.deptno and a.dname = in_dname;
    
    -- 打开游标
  open my_cursor;
  -- 通过游标获取每一行数据
 label:loop
        fetch my_cursor into var_empno, var_ename, var_sal;
        select var_empno, var_ename, var_sal;
    end loop label;
    
    -- 关闭游标
    close my_cursor;
end
 
 -- 调用存储过程
 call proc20_cursor('销售部');

5.5 句柄

简介

MySql存储过程也提供了对异常处理的功能：通过定义HANDLER来完成异常声明的实现.

官方文档

语法格式

DECLARE handler_action HANDLER

    FOR condition_value [, condition_value] ...

   statement
 

handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}
 

condition_value: {
   mysql_error_code
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION

补充：

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

举例

use mysql7_procedure;

drop procedure if exists proc21_cursor_handler;

要求：输入一个部门名，查询该部门员工的编号、名字、薪资，将查询的结果集添加游标[具体详解步骤]

delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin

S1:定义局部变量

declare var_empno int;

    declare var_ename varchar(50);

    declare var_sal decimal(7,2);
    
    declare flag int default 1;

S2:声明游标

declare my_cursor cursor for

        select empno,ename,sal

        from dept a, emp b

        where a.deptno = b.deptno and a.dname = in_dname;

S3:定义句柄，当数据未发现时将标记位设置为0

declare continue handler for NOT FOUND set flag = 0;

S4:打开游标

open my_cursor;

通过游标获取值

label:loop
        fetch my_cursor into var_empno, var_ename,var_sal;

S5:判断标志位

if flag = 1 then
            select var_empno, var_ename,var_sal;练习
        else
            leave label;
        end if;
    end loop label;

S6:关闭游标

close my_cursor;
end $$;


delimiter ;
call proc21_cursor_handler('销售部');

上述sql语句整合

use mysql7_procedure;
drop procedure if exists proc21_cursor_handler;

delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin
  -- 定义局部变量
    declare var_empno int;
    declare var_ename varchar(50);
    declare var_sal decimal(7,2);
    
    declare flag int default 1;

    -- 声明游标
    declare my_cursor cursor for
        select empno,ename,sal
        from dept a, emp b
        where a.deptno = b.deptno and a.dname = in_dname;
    
    -- 定义句柄，当数据未发现时将标记位设置为0
    declare continue handler for NOT FOUND set flag = 0; 
    
 -- 打开游标
    open my_cursor;
    -- 通过游标获取值
   label:loop
        fetch my_cursor into var_empno, var_ename,var_sal;
        -- 判断标志位
        if flag = 1 then
            select var_empno, var_ename,var_sal;练习
        else
            leave label;
        end if;
    end loop label;
    
    -- 关闭游标
    close my_cursor;
end $$;
 
delimiter ;
call proc21_cursor_handler('销售部');

题目练习【前面打基础扎实在来做】

需求

创建下个月的每天对应的表user_2021_11_01、user_2021_11_02、...

需求描述：

我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为(注意，此处是假设用数据库保存)，当每天记录较多时

如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表

思路：

循环构建表名 user_2021_11_01 到 user_2020_11_30；并执行create语句。

知识补充和预备

PREPARE stmt_name FROM preparable_stmt

EXECUTE stmt_name [USING @var_name [, @var_name] ...]

{DEALLOCATE | DROP} PREPARE stmt_name

-- 知识点 时间的处理

-- EXTRACT(unit FROM date)截取时间的指定位置值

-- DATE_ADD(date,INTERVAL expr unit) 日期运算

-- LAST_DAY(date) 获取日期的最后一天

-- YEAR(date) 返回日期中的年

-- MONTH(date) 返回日期的月

-- DAYOFMONTH(date) 返回日

use mysql7_procedure;

drop procedure if exists proc22_demo;

delimiter $$

create procedure proc22_demo()

begin

    declare next_year int;

    declare next_month int;

    declare next_month_day int;
        
    declare next_month_str char(2);

    declare next_month_day_str char(2);

处理每天的表名

declare table_name_str char(10);

declare t_index int default 1;

-- declare create_table_sql varchar(200);

获取下个月的年份

set next_year = year(date_add(now(),INTERVAL 1 month));

获取下个月是几月

set next_month = month(date_add(now(),INTERVAL 1 month));

下个月最后一天是几号

set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month)));

 if next_month < 10
        then set next_month_str = concat('0',next_month);
    else
        set next_month_str = concat('',next_month);
    end if;
    
    
    while t_index <= next_month_day do
        
        if (t_index < 10)
            then set next_month_day_str = concat('0',t_index);
        else
            set next_month_day_str = concat('',t_index);
        end if;

2021_11_01

set table_name_str = 

concat(next_year,'_',next_month_str,'_',next_month_day_str);

拼接create sql语句

set @create_table_sql = concat(

                    'create table user_',

                   table_name_str,

                    '(`uid` INT ,`ename` varchar(50) ,`information` varchar(50)) 

COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');

FROM后面不能使用局部变量！

 prepare create_table_stmt FROM @create_table_sql;

        execute create_table_stmt;

        DEALLOCATE prepare create_table_stmt;

        
        set t_index = t_index + 1;
        
    end while;  

end $$

delimiter ;

call proc22_demo();

本题上述sql语句以及注释整合

use mysql7_procedure;
drop procedure if exists proc22_demo;
delimiter $$
create procedure proc22_demo()
begin
    declare next_year int;
    declare next_month int;
    declare next_month_day int;
        
    declare next_month_str char(2);
    declare next_month_day_str char(2);

    
    -- 处理每天的表名
    declare table_name_str char(10);
    
    declare t_index int default 1;
    -- declare create_table_sql varchar(200);


    -- 获取下个月的年份第11章 存储函数 

    set next_year = year(date_add(now(),INTERVAL 1 month));


    -- 获取下个月是几月 
    set next_month = month(date_add(now(),INTERVAL 1 month));


    -- 下个月最后一天是几号
    set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month)));
    
    if next_month < 10
        then set next_month_str = concat('0',next_month);
    else
        set next_month_str = concat('',next_month);
    end if;
    
    
    while t_index <= next_month_day do
        
        if (t_index < 10)
            then set next_month_day_str = concat('0',t_index);
        else
            set next_month_day_str = concat('',t_index);
        end if;
-- 2021_11_01
        set table_name_str = 
concat(next_year,'_',next_month_str,'_',next_month_day_str);
        -- 拼接create sql语句
        set @create_table_sql = concat(
                    'create table user_',
                   table_name_str,
                    '(`uid` INT ,`ename` varchar(50) ,`information` varchar(50)) 
COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');


        -- FROM后面不能使用局部变量！
        prepare create_table_stmt FROM @create_table_sql;
        execute create_table_stmt;
        DEALLOCATE prepare create_table_stmt;
        
        set t_index = t_index + 1;
        
    end while;  
end $$
delimiter ;
 
call proc22_demo();

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2024-10-21，如有侵权请联系 cloudcommunity@tencent.com 删除

mysql