十二.存储过程

于 2022-05-18 13:37:53 发布
阅读量126 收藏
点赞数
分类专栏： MySQL 文章标签： sql 数据库 sqlserver
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_31511385/article/details/124840198
版权

1.概念

(1).MySQL5.0版本开始支持存储过程。

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

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

2.特性

(1).有输入输出参数，可以声明变量，有if/else, case, while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能

(2).函数的普遍特征性: 模块化，封装，代码复用

(3).速度快，只有首次执行需要经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤

3.入门示例

(1).格式

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

(2).示例

-- 创建存储过程
delimiter $$
create procedure proc01()
begin
  select empno, ename from emp; 
end $$
delimiter;
-- 调用存储过程
call proc01();

3.变量定义

(1).局部变量

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

[1].语法

-- 声明变量
declare var_name tyoe [default var_value];

[2].示例

delimiter $$
create procedure proc02()
begin
   declare var_name01 varchar(20) default 'aaa';
   set var_name01 = 'zhangsan'；
   select var_name01;
end $$
-- 调用存储过程
call proc02();

[3].select…into

MySQL中还可以使用SELECT…INTO语句为变量赋值，其基本语法如下:

select col_name [...] into var_name[,...]
from table_name where 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;
end $$
-- 调用存储过程
call proc03();

(2).用户变量

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

[1].语法

-- 不需要提前声明，使用即声明
@var_name

[2].操作

-- 赋值
delimiter $$
create procedure proc04()
begin
   set @var_name01 = 'ZS';
end $$
call proc04() $$
select @var_name01 $$

(3).系统变量

[1].简介

[a].系统变量又分为全局变量与会话变量

[b].全局变量在MySQL启动的时候有服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。

[c].会话变量在每次建立一个新的连接的时候，由MySQL来初始化。MySQL会将当前所有全局变量的值复制一份。来作为会话变量。

[d].也就是说，如果在建立会话之后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。

[e].全局变量与会话变量的区别就在于，对于全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话(也就是当前的数据库连接)

[f].有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。

[2].语法

-- 全局变量
@@global.var_name
-- 会话变量
@@session.var_name

[3].示例

-- 查看全局变量
show global variables;
-- 查看某个全局变量
select @@global.auto_increment_increment;
-- 修改全局变量
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000;

-- 查看会话变量
show session variables;
-- 查看某个会话变量
select @@session.auto_increment_increment;
-- 修改会话变量
set session sort_buffer_size = 40000;
set @@session.sort_buffer_size = 40000;

4.参数传递

(1).in

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

[1].示例

-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
   select * from emp where empno = param_empno;
end $$
delimiter ;
call dec_param01('1001');

(2).out

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

[1].示例

delimiter $$
create procedure proc08(in empno int, out out_name varchar(50))
begin
   select ename into out_name from emp where emp.empno = empno;
end $$
delimiter;
call proc08(1001, @o_ename);
select @o_ename;

(3).inout

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

[1].示例

-- 传入员工名; 拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc10(inout inout_ename varchar(50), inout inout_sal int)
begin
   select concat(deptno, "_", inout_ename) into inout_name from emp where ename = inout_name;
   set inout_sal = inout_sal * 12;
end $$
delimiter ;
set @inout_ename = '关羽';
set @inout_sal = 3000;
call proc10(@inout_name, @inout_sal);
select @inout_ename;
select @inout_sal;

5.流程控制

(1).if

[1].简介

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

[2].语法

-- 语法
if search_conition_1 then statement_list_1
    [elseif search_condition_2 then statement_list_2]...
    [else statement_list_n]
end if

[3].示例

-- 输入员工的名字，判断工资的情况
delimiter $$
create procedure proc12_if(in in_ename varchar(50))
begin
   declare result varchar(20);
   declare val_sal decimal(7,2);
   select sal into var_sal from emp where ename = in_ename;
   if val_sal < 10000
        then set result = '试用薪资';
   elseif va;_sal < 30000
         then set result = '转正信息';
   else
         set result = '元老薪资';
   end if;
   select result;
end $$
delimiter ;

(2).case

[1].简介

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

[2].语法

-- 语法一(类比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

[3].示例

语法一:

delimiter $$
create procedure proc14_case(int 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);

语法二:

delimiter $$
create procedure proc15_case(int pay_type int)
begin
   case 
       when pay_type = 1 then select '微信支付';
       when pay_type = 2 then select '支付宝支付';
       when pay_type = 3 then select '银行卡支付';
       else select '其他方式支付';
   end case;    
end $$
delimiter ;
call proc14_case(2);

(3).循环

[1].概述

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

[b].循环中的代码会运行特定的次数，或者是运行到特定条件成立时结束。
在这里插入图片描述

[2].循环分类

[a].while

[b].repeat

[c].loop

[3].循环控制

[a].leave类似于break, 跳出，结束当前所在的循环

[b].iterate类似于continue, 继续， 结束本次循环，继续下一次

(4).while

[1].格式

[标签:]while 循环条件 do
    循环体;
end while [标签];

[2].示例

-- 创建测试表
create table user(
  uid int primary key,
  username varchar(50),
 password varchar(50)
);
-- 新建存储过程，向表中指定条数条数据
delimiter $$
create procedure proc16_while(in instertCount 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);

-- 新建存储过程，向表中指定条数条数据,条数小于等于5，否则只能插入5条
delimiter $$
create procedure proc17_while(in instertCount 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 $$
-- 调用存储过程
call proc17_while(10);

-- 相当于循环continue
delimiter $$
create procedure proc18_while(in instertCount 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
   iterate label;
 end if;
 set i = i + 1;
end while label;
end $$
-- 调用存储过程
call proc18_while(10);

(5).repeat

[1].格式

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

[2].示例

-- 创建测试表
create table user(
     uid int primary key,
     username varchar(50),
    password varchar(50)
);
-- 新建存储过程，向表中指定条数条数据
delimiter $$
create procedure proc16_repeat(in instertCount 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;
end $$
delimiter;
-- 调用存储过程
call proc16_repeat(10);

(6).loop

[1].格式

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

[2].示例

-- 创建测试表
create table user(
     uid int primary key,
     username varchar(50),
    password varchar(50)
);
-- 新建存储过程，向表中指定条数条数据
delimiter $$
create procedure proc16_loop(in instertCount 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;
    end if;
  end loop label;
end $$
delimiter;
-- 调用存储过程
call proc16_loop(10);

6.游标

(1).简介

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

(2).格式

-- 声明语法
declare cursor_name cursor for select_statement
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, varname] ...
-- 关闭语法
close cursor_name

(3).示例

delimiter $$
create procedure proc19_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 $$;
delimiter ;

-- 调用存储过程
call proc19_cursor('销售部');

7.异常处理

(1).简介

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

参考地址:https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html

(2).格式

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement
/*
  异常处理完之后程序该怎么执行
     continue：继续执行剩余代码
     exit:直接终止程序
     undo:不支持
*/    
handler_action:{
  CONTINUE | EXIT | UNDO
}

/*
 触发条件
  条件码: 1329
  条件码: SQLWARNING, NOT FOUND, SQLEXCEPTION
 */
condition_value:{
  mysql_error_code | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION
}

(3).示例

delimiter $$
create procedure proc19_cursor(in in_dname varchar(50))
begin
  -- 定义局部变量
  declare var_empno varchar(50);
  declare var_ename varchar(50);
  declare var_sal decimal(7, 2);
  -- 定义标记值
  declare flag 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;
        select var_empno, var_ename, var_sal;
     end loop label;
     
     -- 关闭游标
     close my_cursor;
end $$;
delimiter ;

-- 调用存储过程
call proc19_cursor('销售部');

8.在Mybatis中调用存储过程

其他部分都相同，只有注意xml的写法不一样

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xiaotian.mapper.UserMapper">
    <update id="calls" statementType="CALLABLE">
        {call proc_user(#{id,mode=IN}, #{username,mode=IN}, #{age,mode=IN})}
    </update>
</mapper>
qq_31511385
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值