• 【MySQL】的存储过程

    ​
    ​

    活动地址：CSDN21天学习挑战赛
    作者简介：大家好我是狂暴于涛侠本侠
    🦸个人主页：狂暴于涛侠

    🌺MySQL的存储过程:

    什么是存储过程?

    1. MySQL 5.0 版本开始支持存储过程。
    2. 简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；
    3. 存储过就是数据库 SQL 语言层面的代码封装与重用。

    有哪些特性?

    1. 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
    2. 函数的普遍特性：模块化，封装，代码复用；
    3. 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤

      MySQL的存储过程

    💐1、入门案例

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

    操作：
    在这里插入图片描述
    我们为了演示先操作上表：

    delimiter $$-- 把$$当做函数自定义结束符号 
create procedure proc01()
begin
	select empno, ename from emp;
end $$
delimiter;

-- 调用存储过程
call proc01();

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    💐2、MySQL操作-变量定义

    🌼局部变量

    格式

    • 局部变量
    • 用户自定义，在begin/end块中有效

    语法： 声明变量 declare var_name type [default var_value];
    举例：declare nickname varchar(32);

    操作：

    -- 变量定义
/*
语法1： 声明变量 declare var_name type [default var_value]; 
语法2：
select col_name [...] into var_name[,...] 
from table_name wehre condition 

*/
delimiter $$
create procedure proc02()
begin
  declare var_name01 varchar(20) default 'aaa';  -- 声明/定义变量
	set var_name01 = 'zhangsan';  -- 给变量赋值
	select var_name01; -- 输出变量的值
end $$
delimiter ;

call proc02();
select var_name01;-- 只能在函数里调用除了函数就不行了（局部变量）

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    在这里插入图片描述

    MySQL 中还可以使用 SELECT…INTO 语句为变量赋值。其基本语法如下：
    select col_name […] into var_name[,…]
    from table_name wehre condition
    其中：
    col_name 参数表示查询的字段名称；
    var_name 参数是变量的名称；
    table_name 参数指表的名称；
    condition 参数指查询条件。
    注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。

    delimiter $$
create procedure proc03()
begin
  declare my_ename varchar(20) ;  -- 声明/定义变量
	select ename into my_ename from emp where empno = 1001;  -- into 给变量my_ename赋值 
	select my_ename; -- 输出变量的值
end $$
delimiter ;

call proc03();

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    在这里插入图片描述

    🌼用户变量

    • 用户变量
      用户自定义，当前会话（连接）有效。类比java的成员变量

    格式
    语法：
    @var_name
    不需要提前声明，使用即声明

    操作：

    delimiter $$
create procedure proc04()
begin
 set @var_nam01 = 'beijing';
 select  @var_nam01;
end $$
delimiter ;
call proc04();

select @var_nam01; -- 也可以使用用户变量
-- 在函数外同样可用，除非链接断开

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    在这里插入图片描述

    🌼系统变量

    • 系统变量
    • 介绍
    • 系统变量又分为全局变量与会话变量
    • 全局变量在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 = 33000;

select @@global.sort_buffer_size;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    🌼会话变量

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

    格式
    语法：
    @@session.var_name

    操作：

    -- 会话变量
-- 查看会话变量
show session variables;
-- 查看某会话变量 
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000; 
set @@session.sort_buffer_size = 60000 ;

select @@session.sort_buffer_size;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    💐3、MySQL的存储过程

    • 存储过程传参in，out, inout

    • in 输入参数，意思说你的参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被返回

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

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

    🌹存储过程传参-in

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

    -- ---------传入参数：in---------------------------------
-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure proc06(in empno int )-- 这里empno就是一个变量
begin
  select * from emp where emp.empno = empno; -- emp.empno是emp表里的empon
end $$

delimiter ;

call proc06(1001);
call proc06(1002);




-- ------------------------------------
-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure proc07(in param_dname varchar(50), in param_sal decimal(7,2))
begin
 select * from dept a, emp b where a.deptno = b.deptno and a.dname = param_dname and b.sal > param_sal;
end $$
delimiter ;

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    在这里插入图片描述

    🌹存储过程传参-out

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

    
-- ---------传出参数：out---------------------------------
-- 封装有参数的存储过程，传入员工编号，返回员工名字
delimiter $$
create procedure proc08(in in_empno int, out out_ename varchar(50))
begin
  select ename into out_ename  from emp where empno = in_empno;
end $$
delimiter ;

call proc08(1002, @o_ename);
select @o_ename;



-- ------------------------------------------
-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure proc09(in 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 empno = in_empno;
end $$
delimiter ;

call proc09(1002, @o_ename,@o_sal);
select @o_ename;-- 黛绮丝
select @o_sal;-- 16000.00

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    在这里插入图片描述

    🌹存储过程传参-inout

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

    
-- 传入一个数字，传出这个数字的10倍值
delimiter $$
create procedure proc10(inout num int)
begin
  set num = num * 10;
end $$
delimiter ;

set @inout_num = 3;
call proc10(@inout_num);
select @inout_num;



-- 传入员工名，拼接部门号，传入薪资，求出年薪
-- concat_ws('_',deptno,ename)的意思是 关羽 ----> 30_关羽 

delimiter $$

create procedure proc11(inout inout_ename varchar(50), inout inout_sal int)
begin
  select concat_ws('_',deptno,ename)  into  inout_ename from emp where emp.ename = inout_ename;
	set inout_sal = inout_sal * 12;
end $$

delimiter ;

set @inout_ename = '关羽';
set @inout_sal = 3000;

call proc11(@inout_ename,@inout_sal);

select  @inout_ename ;-- 30_关羽 
select  @inout_sal;-- 36000

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    💐3、流程控制

    🌺流程控制-判断

    格式
    语法：
    IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句，与编程语言中的if、else if、else语法类似，其语法格式如下：
    – 语法
    if search_condition_1 then statement_list_1
    [ elseif search_condition_2 thenstatement_list_2] …
    [ else statement_list_n]
    end if

    -- 存储过程-if
-- 案例1
-- 输入学生的成绩，来判断成绩的级别：
/*
  score < 60 :不及格
  score >= 60  , score <80 :及格
	score >= 80 , score < 90 :良好
	score >= 90 , score <= 100 :优秀
	score > 100 :成绩错误
*/

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)-- 成绩错误


-- 输入员工的名字，判断工资的情况。
/*
  sal < 10000 :试用薪资
	sal >= 10000 and sal < 20000 :转正薪资
	sal >= 20000 :元老薪资
*/
delimiter  $$
create procedure proc_13_if(in in_ename varchar(20))
begin
 declare var_sal decimal(7,2);
 declare result varchar(20);
 select sal into var_sal from emp where ename = in_ename;
 
 if var_sal < 10000
	then
		set result = '试用薪资';
 elseif var_sal < 20000
	then 
	  set result = '转正薪资'	;
 else
	  set result = '元老薪资' ;
 end if;
 select result;
end $$
delimiter  ;

-- 调用
call proc_13_if('关羽');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63

    🌺流程控制-case

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

    格式
    语法：
    IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句，与编程语言中的if、else if、else语法类似，其语法格式如下：
    – 语法一（类比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

    -- 流程控制语句：case
/*
 支付方式:
   1  微信支付
	 2  支付宝支付
	 3  银行卡支付
	 4  其他方式支付
*/

-- 格式1

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);


-- 格式2
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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54

    🌺流程控制-循环

    概述：
    循环是一段在程序中只出现一次,但可能会连续运行多次的代码。
    循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环

    • 循环分类：
    • 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)
);

    • 1
    • 2
    • 3
    • 4
    • 5

    然后进行一些测试：

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

*/
-- 需求：向表中添加指定条数的数据
-- -------存储过程-循环-while
delimiter $$
create procedure proc16_while(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);


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    在这里插入图片描述

    存储过程-循环控制-while + leave：

    -- -------存储过程-循环控制-while + leave
-- leave：直接跳出本层while循环
truncate table user;

delimiter $$
create procedure proc17_while_leave(in insertCount int) -- 10
begin
	 declare i int default 1;
	 label:while i <= insertCount do -- 10
		 insert into user(uid, username, password) values(i,concat('user-',i),'123456');
		 if i = 5 then
		   leave label;-- leave label可以理解为跳出label循环
		 end if;
		 set i = i + 1;
	 end while label;
end $$
delimiter ;

call proc17_while_leave(10);


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    在这里插入图片描述
    存储过程-while+iterate：

    -- -------存储过程-while+iterate
-- iterate:跳过本次循环的剩余代码，进入下一次循环
-- 先创建一个新表
create table user2 (
	uid int ,-- 不设置主键
	username varchar(50),
  password varchar(50)
);

use mysql7_procedure;
truncate table user2;

delimiter $$
create procedure proc17_while_iterate(in insertCount int) -- 10
begin
	 declare i int default 0; -- 创建i变量默认为0
	 label:while i < insertCount do -- 10
	  set i = i + 1;  -- 修改i的值
	  if i = 5 then
		   iterate label;-- 退出本次循环，而不是直接退出整个循环
		 end if;
		 insert into user2(uid, username, password) values(i,concat('user-',i),'123456');
	 end while label;
	 select '循环结束';
end $$
delimiter ;

call proc17_while_iterate(10);  -- 1 2 3 4 6 7 8 9 10

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    在这里插入图片描述

    在这里插入图片描述

    🍁流程控制-循环-repeat

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

    
-- -------存储过程-循环控制-repeat 
use mysql7_procedure;
truncate table user;


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(10);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    在这里插入图片描述
    在这里插入图片描述

    🍁流程控制-loop+leave

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

    -- -------存储过程-循环控制-loop
/*
[标签:] loop
  循环体;
  if 条件表达式 then 
     leave [标签]; 
  end if;
end 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 > insertCount
	 then 
	   leave label; --如果i > insertCount就离开循环
  end if ;
	
 end loop label;
end $$
delimiter ;

call proc19_loop(10);


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    在这里插入图片描述

    💐4、游标

    游标：其实类似于c语言的指针
    游标(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

    select empno,ename,sal
from dept a, emp b
where a.deptno = b.deptno and a.dname = in_dname;

    • 1
    • 2
    • 3

    在这里插入图片描述

    -- 操作游标(cursor)

-- 声明游标
-- 打开游标
-- 通过游标获取值
-- 关闭游标
use mysql7_procedure;
drop procedure if exists proc19_cursor;
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc19_cursor(in in_dname varchar(50))
begin
  -- 定义局部变量
	declare var_empno int;
	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;
	-- xxxx如果说后面还有代码不会执行，怎么解决看下面异常处理
	-- 关闭游标
	close my_cursor;
end $$;

delimiter ;

call proc19_cursor('销售部');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38

    在这里插入图片描述

    💐5、异常处理-HANDLER句柄

    MySql存储过程也提供了对异常处理的功能：通过定义HANDLER来完成异常声明的实现.
    官方文档： https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html.

    格式
    [标签:] loop
    循环体;
    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声明是必须按照先后顺序书写的，否则创建存储过程出错。

    -- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc21_cursor_handler(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;
		
	-- 定义句柄：定义异常的处理方式
	/*
	  1:异常处理完之后程序该怎么执行
		   continue :继续执行剩余代码
			 exit : 直接终止程序
			 undo: 不支持
			 
	   2: 触发条件
		  条件码:
			  1329
			条件名：
		    SQLWARNING
        NOT FOUND
        SQLEXCEPTION
		  3:异常触发之后执行什么代码
				设置flag的值 ---》 0
	*/
		
	declare continue handler for 1329  set flag = 0;-- 异常处理
		
	-- 打开游标
	open my_cursor;
	-- 通过游标获取值
	label:loop
		fetch my_cursor into var_empno, var_ename,var_sal;
		-- 判断flag，如果flag的值为1，则执行，否则不执行
		if flag = 1 then 
		 select var_empno, var_ename,var_sal;
	  else 
		 leave label;
	  end if;
	end loop label;
	
	-- xxxxx
	-- 关闭游标
	close my_cursor;
end $$;

delimiter ;

call proc21_cursor_handler('销售部');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58

    在这里插入图片描述

    💐6、练习

    需求：
    创建下个月的每天对应的表user_2021_11_01、user_2021_11_02、…

    需求描述：
    我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为(注意，此处是假设用数据库保存)，当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表！
    在这里插入图片描述

    预备知识
    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) 返回日

    -- -----------------------------------------
-- 练习
/*
创建下个月的每天对应的表user_2021_12_01、user_2022_12_02、...

需求描述：
我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为(注意，此处是假设用数据库保存)，当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表！
*/
-- 思路：循环构建表名 user_2021_11_01 到 user_2020_11_30；并执行create语句。
create database mydb18_proc_demo;
use mydb18_proc_demo;
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 varchar(2);  -- 下一个月的月份字符串
	declare next_month_day_str varchar(2);-- 下一个月的日字符串
	
	-- 处理每天的表名
	declare table_name_str varchar(10);
	
	declare t_index int default 1;
	-- declare create_table_sql varchar(200);
	
	-- 获取下个月的年份
	set next_year = year(date_add(now(),INTERVAL 1 month)); 
	-- now()当前时间，这句话的的意思让data_add算当前日期加上一个月是多少 year()是拿到年份
	-- 获取下个月是几月 
	set next_month = month(date_add(now(),INTERVAL 1 month)); 
	-- 下个月最后一天是几号
	--  LAST_DAY(）获取那个月最后一天是几年几月几号   dayofmonth()获取几号
	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); -- 其实就是让个位数月份前加上0 1  ---> 01
	else
		set next_month_str = concat('',next_month); -- 12 --->12
	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 ,`uname` 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();

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73

    在这里插入图片描述

    💐7、MySQL的存储函数

    概述

    介绍
    MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。
    存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。

    存储函数与存储过程的区别
    1.存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。
    2.存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
    3.存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；
    4.存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
    5.存储过程可以调用存储函数。但函数不能调用存储过程。
    6.存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.

    格式
    在MySQL中，创建存储函数使用create function关键字，其基本形式如下：
    create function func_name ([param_name type[,…]])
    returns type
    [characteristic …]
    begin
    routine_body
    end;
    参数说明：
    （1）func_name ：存储函数的名称。
    （2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。
    （3）RETURNS type：指定返回值的类型。
    （4）characteristic：可选项，指定存储函数的特性。
    （5）routine_body：SQL代码内容。

    我面用下面这个表来进行测试
    在这里插入图片描述

    - 允许创建函数权限信任
set global log_bin_trust_function_creators = TRUE;

-- 创建存储函数-没有参数
delimiter $$
create function myfunc1_emp( ) returns int 
begin
-- 定义局部变量
	declare cnt int default 0;
	select count(*) into cnt from emp;
	return cnt;
end $$
delimiter ;

-- 调用存储函数
select myfunc1_emp();

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    在这里插入图片描述

    -- 创建存储过程-有输入参数
-- 需求:传入一个员工的编号，返回员工的名字
drop function if exists myfunc2_emp;
delimiter $$
create function myfunc2_emp(in_empno int) returns varchar(50)
begin
	declare out_ename varchar( 50);
	select ename into out_ename from emp where empno = in_empno;
	return out_ename;
end $$
delimiter ;
-- 调用存储函数
select myfunc2_emp(1008);


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    在这里插入图片描述

  • 相关阅读:
    Makefile泛谈
    day 0912
    数据结构刷题——bfs
    计算机网络 网络层设备的 冲突域和广播域
    CAD长方形纤维插件2D
    MySQL JDBC编程
    Rust中打印语句为什么使用宏实现?
    【vector题解】连续子数组的最大和 | 数组中出现次数超过一次的数字
    2-3 Moves Codeforces 1716A
    知识分享系统
  • 原文地址：https://blog.csdn.net/m0_58465176/article/details/126316042