首页 > 数据库 >MYSQL-->存储过程与存储函数

MYSQL-->存储过程与存储函数

时间：2022-10-08 21:44:42浏览次数：52  
标签：语句 存储 变量 -- MYSQL 游标 SQL select

介绍

存储过程是事先经过编译并且存储在数据库中的一段SQL语句的集合。

调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，有助于提高数据处理的效率。

存储过程思想上就是SQL语言层面的代码封装和重用。

特点

封装，复用

可以接收参数，可以返回数据

减少网络交互，提高了效率

基本语法

创建存储过程

create procedure 存储过程名([参数列表])
begin
	--SQL语句
end;

调用存储过程

call 存储过程名([参数]);

查看存储过程

查询是否存在对应存储过程

查询 information_shema系统数据库下的routines表

里面存储了对应的存储过程

当我们要查询数据库内某个表可以直接 数据库名.表名 这种方式查询，或者先use进入这个数据库在查询

select * from information_shema.routines where routine_schema = '要查询的存储过程名';

查询指定存储过程的定义方式

show create procedure 存储过程名;

删除存储过程

drop procedure [if exists] 存储过程名;

注意！

如果我们是在命令行中创建存储过程的创建SQL，会报错！

因为默认我们是 分号 结束SQL语句

但是在datagrip等软件中就不会报错！

解决办法！

修改mysql的结束符！

delimiter 结束符

变量

系统变量

系统变量是MYSQL服务器提供，不是用户自定义的，属于服务器层面，

分为全局变量(GLOBAL)。会话变量(SESSION)。

一个查询控制台就是一个会话

查看系统变量

如果不指定是session还是global那么默认session

查看所有系统变量

show [session | global ] variables;

通过模糊匹配方式查询指定变量

show [session | global ] variables like '....';

查看指定系统变量

select @@[session | global]系统变量名;

如果要加global级别那么要

select @@global.系统变量名;

用 点 作为分隔符！

设置系统变量

set [session | global ] 系统变量名 = 值;
set @@[session | global ]系统变量名=值;

设置的系统变量值如果服务器重启后会失效！

要永久修改就修改/etc/my.cnf 文件内容

用户自定义变量

用户根据自己定义的变量叫用户自定义变量

用户变量不需要提前声明，用的时候直接 @变量名 使用即可

如果没有对变量进行复制，获取到的值为NULL。

作用域为当前的连接！

赋值

用set方式赋值

set @变量名1=值1,@变量2=值2,...;
set @变量名1:=值1,@变量2:=值2,...;		->推荐，用于区分 =

用select方式赋值

select @变量名1 := 值1.@变量名2 := 值2,...;
select 字段名 into @变量名 from 表名; ->将select查询到的值作为变量的值

使用

select @变量名1,@变量名2,...;

局部变量

局部变量 是根据需要定义在局部生效的变量。

访问之前需要DECLARE声明。

可用作存储过程内的局部变量和输入参数，局部变量的范围是在其声明内的BEGIN...END块

局部变量一般用在存储过程内部.

声明

Declare 变量名 数据类型 [default 默认值];

赋值

set 局部变量名1 = 值1,局部变量名2 = 值2,...;
set 局部变量名1 := 值1,局部变量名2 = 值2...;
select 字段 into 变量名 from 表名;

使用

select 变量名;

存储过程的参数

参数类型

类型 含义 备注
IN 该类作为输入，也就是需要调用时传入值 默认
OUT 该类作为输出，也就是该参数可以作为返回值
INOUT 既可以作为传入值也可以作为返回值使用

in 就相当于你函数里面写的值

out 就相当于你函数的 return值

inout 就是俩个的结合体

举例

create procedure 存储过程名(in id int,out name char(5))
begin
	--SQL
end;
call 存储过程名(传入值,用于接收name这个传出参数的值的变量);

用法

create procedure 存储过程名([in | out | inout 参数名 参数的数据类型])
begin
	--SQL语句
end;

条件判断

if条件判断的语法

if 条件1 then
	执行语句1;
else 条件2 then
	执行语句2;
else
	执行语句n;
end if;

case流程控制的语法

语法1 (表达式=值1 or 值2 or 值3 or.....or 值n)

如果表达式等于对应的值就执行对应语句。

Case 表达式
	when 值1 then 执行语句1;
	when 值2 then 执行语句2;
	....
	else 执行语句n;
end case;

语法2(条件表达式1 = 值1 or 条件表达式2 = 值2 or 条件表达式3 = 值3 or ... or 条件表达式n = 值 n)

如果条件表达式成立就执行对应语句。

case
	when 条件表达式1 then 执行语句1;
	when 条件表达式2 then 执行语句2;
	...
	else 执行语句n;
end case;

循环

while循环

while循环是有条件的循环控制语句。

先判断条件，如果为true则执行逻辑，否则就不执行。

while 条件 do
	SQL逻辑
end while;

repeat循环

repeat循环是有条件的循环控制语句。

当满足条件的时候！退出循环。

先执行一次逻辑，然后判断逻辑是否满足，相当于 do while

repeat
	SQL逻辑;
	until 循环停止条件		->这里没有分号！
end repeat;

loop循环

Loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用来实现简单的死循环。

LOOP可以配合：

  • leave 配合循环使用，退出循环，相当于break
  • iterate 必须在循环中使用，作用是跳过当前循环剩下的语句，直接进入下一次循环，相当于 continue
[开始标记:] Loop
	SQL逻辑;
end Loop[结尾标记:];

leave 标记名;  ->退出指定标记的循环体
iterate 标记名;  -->直接进入下一次循环

开始标记和结尾标记要一模一样，开始标记多个 :

用法举例

从1累加到n

create procedure p(in n int)
begin
	declare total int default 0;

	sum:loop
		if n<=0 then
			leave sum;
		end if;


		set total := total + n;
		set n := n-1;
	end loop sum;

    select total;
end;

从1到n之间偶数累加

create procedure p0(in n int)
begin
	declare total int default 0;

	sum:loop
		if n<=0 then
			leave sum;
		elseif n%2 = 1 then
		    set n:= n-1;
			iterate sum;
		end if;


		set total := total + n;
		set n := n-1;
	end loop sum;

    select total;
end;

游标

我们知道变量可以通过select查询表数据并将数据设置为变量数据

但是！变量只能存储单行/单列的数据！

游标却可以存储一张表！

介绍

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

游标的使用包括了

  • 游标的声明
  • open
  • fetch
  • close

变量的声明必须在游标之前！！

语法

声明游标

Declare 游标名 cursor for 查询语句;

打开游标

在使用游标之前必须打开游标

open 游标名;

获取游标记录

将游标的记录遍历赋值给变量

Fetch 游标名 into 变量1,变量2,....;

关闭游标

close 游标名;

使用方法举例

根据传入的参数 uage

查询 tb_user表的姓名和专业，条件是年龄小于 uage的记录

并将查询返回的结果插入到新创建的表中。

这个新创建的表包含了(id ，name，profession)

逻辑实现：

  1. 声明游标，存储查询的结果集
  2. 准备工作：创建表结构
  3. 开启游标
  4. 获取游标中的记录
  5. 插入数据到新表
  6. 关闭游标
create procedure p2(in uage int)
begin
    declare u_name varchar(10);
    declare u_pro varchar(10);
    declare u_cursor cursor for select name,profession from tb_user where age <= uage;

    drop table if exists tb_user_profession;
    create table if not exists tb_user_profession(
        id int primary key auto_increment,
        name char(10),
        profession varchar(100)
    );

    open u_cursor;
	
	#这里写的是死循环！只靠游标和循环无法得知如何停止循环！
    while true do

        fetch u_cursor into u_name,u_pro;
        insert into tb_user_profession value (null,u_name,u_pro);
    end while;

    close u_cursor;
end;

只靠游标和循环无法得知如何停止循环！

数据库会报错 [02000] [1329]No data - zero rows fetched,selected,or processed

即循环结束后我们如果只靠游标和循环无法得到什么时候游标已经空了！

解决办法！条件处理程序！

条件处理程序

用来定义在流程控制结果执行过程遇到问题时相应的处理步骤

比如说上面的游标什么时候为空问题！

使用条件处理程序只需要声明他即可！

语法

声明条件处理程序

Declare 条件处理程序的类型 handler for 执行条件1,... [SQL逻辑];

条件处理程序的类型

  • Continue 继续执行当前程序
  • Exit 终止执行当前程序

执行条件类型

SQLSTATE 'SQL状态码' 状态码，如02000
SQLWARNING 所有以01开头的SQLSTATE代码简写
NOT FOUND 所有以02开头的SQLSTATE代码简写
SQLEXCEPTION 除了SQLWARNING和NOT FOUND以外的SQLSTATE状态码简写

这个状态码就是程序报错显示的第一个中括号的数字

02开头叫 代表找不到数据

具体状态码可以去MYSQL官方文档查看！

用法举例

条件处理程序可用来完善游标终止条件。

因为游标死循环后最终会爆错并返回02000状态码表示游标无数据

然后出发条件处理程序的退出类型！

create procedure p2(in uage int)
begin
    declare u_name varchar(10);
    declare u_pro varchar(10);
    declare u_cursor cursor for select name,profession from tb_user where age <= uage;
    # 当报错后触发条件处理程序执行关闭游标后再关闭程序！
    declare exit handler for SQLSTATE '02000' close u_cursor;
	
	
    drop table if exists tb_user_profession;
    create table if not exists tb_user_profession(
        id int primary key auto_increment,
        name char(10),
        profession varchar(100)
    );

    open u_cursor;

    while true do
        fetch u_cursor into u_name,u_pro;
        insert into tb_user_profession value (null,u_name,u_pro);
    end while;

end;

存储函数

介绍

存储函数是有返回值的存储过程，且存储函数必须有返回值。

存储函数的参数只能是in类型的。

语法

创建存储函数

create function 存储函数名([参数列表])
returns 返回值类型 [存储函数的特性列表]
begin
	SQL语句;
	return...;
end;

存储函数的调用

select 存储函数名(参数);

存储参数的特性

DETERMINITIC 相同的输入参数总是产生相同的结果
NO SQL 在当前存储函数不包含SQL语句
READS SQL DATA 存储函数只包含读取数据的SQL语句，不包含写入数据的SQL语句

注意事项

  1. 对应MYSQL 8.x版本来说

  2. MYSQL的二进制日志(binary logging)是默认开启的，他要求我们必须指定存储函数的具体特性！是DETERMINITIC 还是NO SQL 或READS SQL DATA

  3. 对于存储函数的参数列表不需要指定参数的类型是in out 还是 inout因为他强制为in

  4. 对于存储函数的调用，把他当成一个值来看待！！，这个值就是他的返回值

标签：语句,存储,变量,--,MYSQL,游标,SQL,select
From： https://www.cnblogs.com/wdadwa/p/MYSQL_Learning_10.html

相关文章

  • 软件设计实验4
    实验4：抽象工厂模式[实验任务一]：人与肤色源码：#include<iostream>usingnamespacestd;classMan;classWoman;classAbstractFactory{public:virtualMan......
  • Docker方式快速启动一个Redis实例
    安装Redis有多种方式，除了可以通过各个平台的软件包工具安装外，还可以直接从源码安装。但是，安装Redis可能会遇到一些这样的问题，比如：1.网络环境比较差，下载耗时比较长2.从源......
  • qt常用数据类型
    常用数据类型1、常用基本数据类型常用接口//求绝对值，T是泛型，以下都是TqAbs(constT&t)//最大值和最小值，会把最大或最小的返回出来T&qMax(constT&value1,cons......
  • grep命令详解
    grep搜索文本的匹配内容，逐行搜索所指定的文件或标准输入，并显示匹配模式的每一行。 常用的grep选项有： -c只输出匹配行的计数。 -i不区分大小写（只适用于单字符）。 ......
  • vi命令
    vi有三种模式 直接输入vi进入vi编辑器的命令模式，该模式保存、删除和替换文件，以及退出vi编辑 不管在什么模式下，按ESC键可进入命令模式 在命令模式下，插入命令i，附加......
  • ORA-39126: expdp
        Starting"SERVICEDESK"."SYS_EXPORT_SCHEMA_07": servicedesk/********@//11.32.18.110:1521/MOS7100directory=MOS7100_BACKUPDB_DIRdumpfile=servicedesk.......
  • 网络字节与主机字节序的转换函数实践
     1.为什么要进行转换？1.1在进行网络编程时，由于网络字节的顺序和主机的字节顺序可能存在不同，需要进行转换以统一“格式”2.什么是网络字节顺序和主机字节顺序呢？2.1网络......
  • ABC 271 F - XOR on Grid Path（搜索 meet in the mid）
    ABC271F-XORonGridPath题意：​ 给出20*20的地图，每个点上都有一个点权，保证为正整数。请问从(1,1)走到(n,n)且路径上所有点权异或和为0的路径有多少条。思路：​......
  • 信息系统项目管理师备考
          国家信息化的核心任务是什么？        ......
  • Mysql基本语法
    Mysql基本语法基础概念实体实际需要关注的物体，想要描述相关的静态特征数据计算机中用于描述关注物体的静态特征的符号，将这些特征组合在一起形成了记录(reocrd)数......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99