2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程 ...

去皮卡多 · 2024-9-19 20:05:15

目录


   Mysql And Redis基础与进阶操作系列（9）之存储过程
  1.概念
  2. 作用之长处

  格式
  示例
  创建存储过程
  调用存储过程
  3.变量
  局部变量:
  格式
  举例：
  还可以使用 SELECT..INTO 语句为变量赋值。语法如下：
  注意：
  举例：
  调用存储过程
  举例
  调用存储过程
  系统变量：
   格式
  举例
  检察全局变量
  检察某全局变量
  修改全局变量的值
  格式
  举例
  检察会话变量
  检察某会话变量
  修改会话变量的值
  4. 参数通报
  In
  封装有参数的存储过程，传入员工编号，查找员工信息
  封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
  out
  封装有参数的存储过程，传入员工编号，返回员工名字
  封装有参数的存储过程，传入员工编号，返回员工名字和薪资
  inout
  传入员工名，拼接部门号，传入薪资，求出年薪
  小结
  5. 流程控制
  5.1流程控制-判断
  格式
  举例
  输入学生的效果，来判断效果的级别：
  输入员工的名字，判断工资的情况
  5.2 流程控制-case
  格式
  语法一（类比java的switch）：
  语法二：
  示例
  语法一
  语法二

  5.3 流程控制-循环
  概述
  循环分类： while ;repeat ;loop
  循环控制：
   流程控制-循环-while
  格式
  举例
  创建测试表
  存储过程-while
  存储过程-while + leave
  存储过程-while+iterate
  流程控制-循环-repeat
  格式
  举例
  存储过程-循环控制-repeat
  流程控制-循环-loop
  格式
  举例
  存储过程-循环控制-loop
  5.4 游标
  简介
  格式
  声明语法
  打开语法
  取值语法
  关闭语法
  举例
  定义局部变量
  声明游标
  打开游标
  通过游标获取每一行数据
  关闭游标
  最后调用存储过程
  本例sql汇总
  5.5 句柄
  简介
  语法格式
  增补：
   举例
  要求：输入一个部门名，查询该部门员工的编号、名字、薪资，将查询的效果集添加游标[具体详解步骤]
  S1:定义局部变量
  S2:声明游标
  S3:定义句柄，当数据未发现时将标记位设置为0
  S4:打开游标
  通过游标获取值
  S5:判断标记位
  S6:关闭游标
  上述sql语句整合
  题目训练【前面打基础扎着实来做】
  需求
  需求描述：
  思绪：
  知识增补和预备
  处理天天的表名
  获取下个月的年份
  获取下个月是几月
  下个月最后一天是几号
  2021_11_01
  拼接create sql语句
  FROM背面不能使用局部变量！
  本题上述sql语句以及解释整合

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

复制代码

.......

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

		自动登录	找回密码
密码			立即注册

2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程 ...

0 个回复

快速回复

楼主热帖

标签云