【MySQL】存储过程/参数验证/函数

文章目录

  • 存储过程是什么？
  • 创建存储过程：creat procedure…begin…end
    • 修改默认分隔符：dilimiter
    • 调用存储过程：call
    • 删除存储过程：drop procesure if exists
    • 练习
  • 存储过程中添加参数：(形参 数据类型)
    • 示例
    • 练习
  • 设置参数的默认值
    • 参数为空时，给参数设置一个值
    • 参数为空时，如何查询数据；不为空时，又如何查询数据
      • if…else…写法
      • ifnull()写法
    • 练习
  • 参数验证
    • 没有参数验证会出现的问题
    • 带参数验证的写法
      • if 后接验证条件
      • signal sqlstate 设置错误代码
      • set meesage_text 设置错误信息
  • 输出参数（尽量避免使用）
  • 变量
    • 用户或会话变量
    • 本地变量
      • declare 声明变量 / default 默认值
  • 函数
    • 创建函数
    • 设置返回值的类型
    • 设置函数属性：每个函数至少有一个属性，可以有多个属性
    • 删除或更新函数：drop function if exists

存储过程是什么？

  • 不会在应用代码里写SQL语句，原因如下：
    • 会让应用代码混乱且难以维护，比如不会把SQL和java代码混在一起写
    • java需要编译，如果把SQL和java写在一起的话，要修改SQL代码时必须重新编译才行。
  • 把SQL代码储存在应属的数据库中，即存储过程
    • 存储过程：是一个包含一堆SQL代码的数据库对象。在应用代码里，通过调用这些过程来获取或保存数据。使用存储过程来存储和管理SQL代码。
  • 存储过程的优势：
    • 可以对存储过程里的代码做一些优化，所以存储过程里的SQL代码有时候可以执行得更快
    • 能加强数据安全性。比如：可以取消对所有表的直接访问权限，并让插入、更新或删除数据的操作由存储过程来完成，指定能执行特定存储过程的某人，这样会限制用户对数据的操作范围，比如可以防止一些用户删除数据。

创建存储过程：creat procedure…begin…end

  • begin和end关键字之间的内容是存储过程的主体

修改默认分隔符：dilimiter

  • 注意：主体中的代码需要用分号分隔，即使只有一句查询，也需要有分号。那此时就要更改默认分隔符了。
  • 更改默认分隔符：delimiter，国际惯例通常用 $ $ 。在end后再用上$ $ ，表示两次$$之间的内容是一个整体。最后再用dilimiter把分隔符换成原来的分号。
delimiter $$
create procedure get_clients()
begin
    select * from clients;
end$$
delimiter ;

运行结果：此时生成了一个对应名字的存储过程。
在这里插入图片描述

调用存储过程：call

call get_clients()

运行结果：调用了存储过程中的sql语句
在这里插入图片描述

删除存储过程：drop procesure if exists

  • 将已有的存储过程删除后，继续执行删除操作的话会报错。
    在这里插入图片描述

  • 因此可把删除存储过程的语句改成：drop procedure if exists，这样删除一个不存在的存储过程时不会报错，更安全。

练习

  • 创建存储过程，获取发票结余大于0的数据
  • 可以使用视图
delimiter $$
create procedure get_invoices_with_balance()
begin
    select *
    from invoices_with_balance
    where balance > 0;
end$$
delimiter ;

运行结果：
在这里插入图片描述

  • 调用刚创建的存储过程
call get_invoices_with_balance()

运行结果：
在这里插入图片描述

存储过程中添加参数：(形参 数据类型)

  • 使用参数为存储过程传递值

示例

  • 使用参数为存储过程传递值
  • 通过存储过程获取位于某个州的客户
    • 存储过程中传入参数，（列名 数据类型）
    • 字符数据类型通常用varchar，还有int，char等
    • 调用存储过程时，需要传入参数调用。如果不传参数会报错。
drop procedure if exists get_clients_by_state;

delimiter $$
create procedure get_clients_by_state
    (
        state char(2)
        -- char(2)代表有2个字符的字符串，例如CA, NY
        -- varchar代表可变长度的字符串，在存储姓名、电话、信息时很有用
        -- 多数时候用varchar，除非能确定字符串有固定长度
    )
begin
    select * from clients c
    where c.state = state;
end $$
delimiter ;

-- 调用时要传参数
call get_clients_by_state('CA')

在这里插入图片描述

  • 如果不传参数，会报错
    在这里插入图片描述

练习

  • 编写存储过程，返回给定客户的发票
drop procedure if exists get_invoices_by_client;

delimiter $$
create procedure get_invoices_by_client
(
    client_id int
)
begin
    select * from invoices i
    where i.client_id = client_id;
end $$
delimiter ;

call get_invoices_by_client(3)

运行结果
在这里插入图片描述

设置参数的默认值

参数为空时，给参数设置一个值

  • 在存储过程主体中设置参数的默认值
begin
    if 参数 is null then
    	-- 如果参数为空，就给参数设置一个返回值
        set 参数 = 默认值;
        -- 或者，参数为空时，直接查询何种结果。
        select * from clients;
    end if;
    ……
end
  • 示例：如果州为空，就返回州为CA的数据
drop procedure if exists get_clients_by_state;

delimiter $$
create procedure get_clients_by_state
(
    state char(2)
)
begin
    if state is null then
        set state = 'CA';
    end if;
    select * from clients c
    where c.state = state;
end $$
delimiter ;

call get_clients_by_state(null)

返回结果：
在这里插入图片描述

参数为空时，如何查询数据；不为空时，又如何查询数据

if…else…写法

  • 如果不说是哪个州，即州为null，就返回所有数据；如果指定了州，就返回指定的数据
drop procedure if exists get_clients_by_state;
delimiter $$
create procedure get_clients_by_state
(
    state char(2)
)
begin
    if state is null then
        select * from clients;
    else
        select * from clients c
        where c.state = state;
    end if;
end $$
delimiter ;

call get_clients_by_state(null)

运行结果
在这里插入图片描述

ifnull()写法

  • 简化上一段代码：
    • 用ifnull简化代码
drop procedure if exists get_clients_by_state;
delimiter $$
create procedure get_clients_by_state
(
    state char(2)
)
begin
        select * from clients c
        -- ifnull：如果为state为空，就返回c.state.
        where c.state = ifnull(state, c.state);
end $$
delimiter ;

call get_clients_by_state(NULL)

返回结果：因为传入的参数是null，所以ifnull函数就将c.state传过去，这样就相当于c.state = c.state，会返回所有数据
在这里插入图片描述

练习

  • 写一个存储过程：获取付款。带两个参数：客户id和付款方式id，这两个参数都不是必填的，如果这两个参数都传递空值，应该返回数据库里所有的付款记录。如果只提供客户id，就只返回这个客户的付款；如果两个参数都赋值，就返回指定客户使用指定付款方式支付的所有付款；
drop procedure if exists get_payments;
delimiter $$
create procedure get_payments
(
    client_id int,
    payment_method_id tinyint
)
begin
    select *
    from payments p
    where
        p.client_id = ifnull(client_id, p.client_id) and
        p.payment_method = ifnull(payment_method_id, p.payment_method);
end $$
delimiter ;
  • 测试存储过程
call get_payments(null,null);
call get_payments(1,null);
call get_payments(null,2);
call get_payments(5,1);

运行结果1：
在这里插入图片描述
运行结果2：
在这里插入图片描述
运行结果3：
在这里插入图片描述
运行结果4：
在这里插入图片描述

参数验证

  • 过程可以用来插入、更新和删除数据
  • 通过参数验证确保过程不会意外地往数据库存储错误数据

没有参数验证会出现的问题

drop procedure if exists make_payments;
delimiter $$
create procedure make_payments
(
    invoice_id int,
    payment_amount decimal(9,2), -- 9代表位数，2代表小数点后的位数
    payment_date date
)
begin
    update invoices i
    set -- 只更新两列
        i.payment_total = payment_amount,
        i.payment_date = payment_date
    where i.invoice_id = invoice_id;
end $$
delimiter ;

call make_payments(2,100,'2019-01-01')

结果：invoices表被更新了，id为2的数据被更新了。
在这里插入图片描述

  • 但如果把金额写为-100，也会进行更新。但这个数据是不合理的数据，因此要对传递的参数进行验证。
call make_payments(2,-100,'2019-01-01')

结果：
在这里插入图片描述

带参数验证的写法

  • 尽量少用参数验证逻辑，只保留最关键的。
drop procedure if exists make_payments;
delimiter $$
create procedure make_payments
(
    invoice_id int,
    payment_amount decimal(9,2), -- 9代表位数，2代表小数点后的位数
    payment_date date
)
begin
	-- 增加参数验证
    if payment_amount <= 0 then
        signal sqlstate '22003' 
        set message_text = 'Invalid payment_amount';
    end if;
    -- 更新数据
    update invoices i
    set -- 只更新两列
        i.payment_total = payment_amount,
        i.payment_date = payment_date
    where i.invoice_id = invoice_id;
end $$
delimiter ;

call make_payments(2,-100,'2019-01-01')

结果：此时会出现错误提示
在这里插入图片描述

if 后接验证条件

  • if后接验证条件，如果满足，就执行then之后的语句

signal sqlstate 设置错误代码

  • 用signal语句来标志或引发错误（类似抛出异常）
  • 不同的异常有不同的错误代码，错误代码查询链接

set meesage_text 设置错误信息

  • 可以选择发送错误信息，以帮助过程调用者识别原因。

输出参数（尽量避免使用）

  • 通过select语句返回参数。
drop procedure if exists get_unpaid_invoices_for_client;
delimiter $$
create procedure get_unpaid_invoices_for_client
(
    client_id int
)
begin
    select count(*), sum(invoice_total)
    from invoices i
    where i.client_id = client_id and payment_total = 0;
end $$
delimiter ;

call get_unpaid_invoices_for_client(3)

在这里插入图片描述

  • 输出参数，尽量避免使用
    • 用out标记输出参数；
    • 选择语句中，选择相应的值into到输出参数中。
  • 使用输出参数时，读取数据比较麻烦
  • set：定义变量，@符号前缀。在调用有输出参数的存储过程时使用这些变量；通过传递变量，获取输出参数值。
drop procedure if exists get_unpaid_invoices_for_client;
delimiter $$
create procedure get_unpaid_invoices_for_client
(
    client_id int,
    -- out会把参数标记为输出参数
    out invoices_count int,
    out invoices_total decimal(9,2)
)
begin
    select count(*), sum(invoice_total)
    into invoices_count, invoices_total
    from invoices i
    where i.client_id = client_id and payment_total = 0;
end $$
delimiter ;

-- 用set语句定义用户变量
set @invoices_count = 0;
set @invoices_total = 0;
call get_unpaid_invoices_for_client(3, @invoices_count, @invoices_total);
select @invoices_count, @invoices_total;

在这里插入图片描述

变量

用户或会话变量

  • set语句 + @前缀
  • 在调用有输出参数的存储过程时使用这些变量；通过传递变量，获取输出参数值。
  • 用户或会话变量在整个过程中被保存，当客户从mysql断线时，这些变量被清空。
-- 用set语句定义用户变量
set @invoices_count = 0;
set @invoices_total = 0;
call get_unpaid_invoices_for_client(3, @invoices_count, @invoices_total);
select @invoices_count, @invoices_total;

本地变量

  • 在存储过程或函数内定义的变量
  • 通常使用本地变量在存储过程中执行计算任务，当存储过程执行完任务，这些本地变量就被清除了

declare 声明变量 / default 默认值

  • 其中的risk_factor, invoices_total, invoices_count 都是本地变量，存储过程执行完以后就被清除了。
drop procedure if exists get_risk_factor;
delimiter $$
create procedure get_risk_factor()
begin
    declare risk_factor decimal(9,2) default 0;
    declare invoices_total decimal(9,2);
    declare invoices_count int;

    select count(*), sum(invoice_total)
    into invoices_count, invoices_total
    from invoices i;

    set risk_factor = invoices_total / invoices_count * 5;

    select risk_factor;
end $$
delimiter ;

运行结果
在这里插入图片描述

函数

  • 函数和存储过程的主要区别
    • 函数只能返回单一值
    • 存储过程可以返回拥有多行和多列的结果集
    • 因此，如果想返回单一值，就可以创建函数

创建函数

drop function if exists get_risk_factor_for_client;
create function get_risk_factor_for_client
(
    client_id int
)
-- 明确返回值的类型
returns integer
-- 设置函数属性
reads sql data
begin
	-- 声明变量
    declare risk_factor decimal(9,2) default 0;
    declare invoices_total decimal(9,2);
    declare invoices_count int;
	
	-- 给变量赋值
    select count(*), sum(invoice_total)
    into invoices_count, invoices_total
    from invoices i
    where i.client_id = client_id;
	
	-- 定义变量
    set risk_factor = invoices_total / invoices_count * 5;
	-- 函数的返回值，如果为空就返回0
    return ifnull(risk_factor, 0);
end

-- 调用函数
select client_id,
       name,
       get_risk_factor_for_client(client_id) as risk_factor
from clients

运行结果：
在这里插入图片描述

设置返回值的类型

  • returns 类型

设置函数属性：每个函数至少有一个属性，可以有多个属性

  • deterministic：确定性。如果给函数同样的一组值，函数永远会返回一样的值
  • reads sql data：读取sql数据。函数中会配置选择语句，用于读取一些数据
  • modifies sql data：修改sql数据。函数中有插入、更新、删除函数

删除或更新函数：drop function if exists

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/253905.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

RocketMQ系统性学习-SpringCloud Alibaba集成RocketMQ以及批量发送消息、消息过滤实战

RocketMQ系统性学习-SpringCloud Alibaba集成RocketMQ以及批量发送消息、消息过滤实战

文章目录 批量发送消息消息过滤 批量发送消息 批量发送消息可以减少网络的 IO 开销&#xff0c;让多个消息通过 1 次网络开销就可以发送&#xff0c;提升数据发送的吞吐量 虽然批量发送消息可以减少网络 IO 开销&#xff0c;但是一次也不能发送太多消息 批量消息直接将多个消…
阅读更多...
docker-harbor仓库

docker-harbor仓库

Docker 镜像 容器 仓库 仓库&#xff1a;保存镜像 私有&#xff1a;自定义用户的形式登录仓库&#xff0c;拉取或者上传镜像&#xff08;内部管理的用户&#xff09; Harbor&#xff1a;是VMware公司开发的&#xff0c;开源的企业级的docker register项目 帮助用户快速的搭建…
阅读更多...
对DataFrame中每列的数值进行限值 指定最小值和最大值：超过最大值的数据，则改为最大值小于最小值的数据，则改为最小值 DataFrame.clip()

对DataFrame中每列的数值进行限值 指定最小值和最大值：超过最大值的数据，则改为最大值小于最小值的数据，则改为最小值 DataFrame.clip()

【小白从小学Python、C、Java】 【计算机等级考试500强双证书】 【Python-数据分析】 对DataFrame中每列的数值进行限值 指定最小值和最大值&#xff1a; 超过最大值的数据&#xff0c;则改为最大值 小于最小值的数据&#xff0c;则改为最小值 DataFrame.clip() [太阳]选择题 请…
阅读更多...
GoLang 学习 （入门）

GoLang 学习 （入门）

go run 1.go 执行命令 go build 1.go 打包为exe 快速 并且无依赖 在开始项目 需要 生成 go.mod go mod init mod 终端执行 go: creating new go.mod: module mod go: to add module requirements and sums:go mod tidy go的基本目录结构 src ------gocode ------------项…
阅读更多...
整合SpringSecurity

整合SpringSecurity

目录 前言 数据库设计 用户表 角色表 用户角色表 权限表 角色权限表 插入数据 表的实体类 用户表实体类 角色表实体类 权限表实体类 mapper层接口 UserMapper RoleMapper AuthorityMapper 封装登录信息 统一响应结果 上下文相关类 jwt令牌工具类 依赖导入…
阅读更多...
Win11极速安装Tensorflow-gpu+CUDA+cudnn

Win11极速安装Tensorflow-gpu+CUDA+cudnn

文章目录 0.pip/conda换默认源1.Anacondapython虚拟环境2.安装CUDA以及cudnn测试tensorflow的GPU版本安装成功的办法参考文献 不要使用官网版本&#xff0c;直接使用conda版本&#xff0c;有对应的包&#xff0c;安装很方便 0.pip/conda换默认源 为了高效下载&#xff0c;建议…
阅读更多...
nodejs微信小程序＋python＋PHP邮件过滤系统的设计与实现-计算机毕业设计推荐

nodejs微信小程序＋python＋PHP邮件过滤系统的设计与实现-计算机毕业设计推荐

邮件过滤系统综合网络空间开发设计要求。该系统主要设计并完成了管理过程中的用户登录、个人信息修改、邮件信息、垃圾箱、意见反馈、论坛等功能。该系统操作简便&#xff0c;界面设计简洁&#xff0c;不但可以基本满足本行业的日常管理工作&#xff0c; 目的是将邮件过滤通过网…
阅读更多...
Flink系列之：自定义函数

Flink系列之：自定义函数

Flink系列之&#xff1a;自定义函数 一、自定义函数二、概述三、开发指南四、函数类五、求值方法六、类型推导七、自动类型推导八、定制类型推导九、确定性十、内置函数的确定性十一、运行时集成十二、标量函数十三、表值函数十四、聚合函数十五、表值聚合函数 一、自定义函数 …
阅读更多...
HttpRunner接口自动化测试框架

HttpRunner接口自动化测试框架

简介 HttpRunner是一款面向 HTTP(S) 协议的通用测试框架&#xff0c;只需编写维护一份 YAML/JSON 脚本&#xff0c;即可实现自动化测试、性能测试、线上监控、持续集成等多种测试需求。 项目地址&#xff1a;GitHub - httprunner/httprunner: HttpRunner 是一个开源的 API/UI…
阅读更多...
【MySQL表的增删改查】

【MySQL表的增删改查】

目录&#xff1a; 前言表的增删改查Create(创建)1.插入插入测试插入否则更新 2.替换 Retrieve(查找)1.SELECT 列全列查找指定列查找查询字段为表达式字段重命名结果去重 2.WHERE条件英语不及格的同学及英语成绩&#xff08;<60&#xff09;语文成绩在 [80, 90] 分的同学及语…
阅读更多...
RTOS队列的写入与读出

RTOS队列的写入与读出

我们在stm32f103c8t6单片机上验证RTOS队列的写入与读出&#xff0c;利用stm32cube进行RTOS的配置。在选择TIM2当做RTOS的时钟&#xff0c;裸机的时钟源默认是 SysTick&#xff0c;但是开启 FreeRTOS 后&#xff0c;FreeRTOS会占用 SysTick &#xff08;用来生成1ms 定时&#x…
阅读更多...
ChatGLM基于LangChain应用开发实践（一）

ChatGLM基于LangChain应用开发实践（一）

一、概述 在使用大模型&#xff08;LLM&#xff09;做应用开发时&#xff0c;LangChain是一个主流的开发框架&#xff0c;通过它来构建Agent&#xff0c;根据用户查询访问企业私有数据&#xff0c;调用自定义或者第三方工具库&#xff0c;然后再调用LLM&#xff0c;利用其推理…
阅读更多...
qt-C++笔记之std::tostring()、.toStdString()、.toLocal8Bit().constData()的使用场景

qt-C++笔记之std::tostring()、.toStdString()、.toLocal8Bit().constData()的使用场景

qt-C笔记之std::tostring()、.toStdString()、.toLocal8Bit().constData()的使用场景 参考博文&#xff1a;C笔记之system()用于在Qt中执行系统命令的习惯 code review! 注&#xff1a;之所以记录该笔记&#xff0c;是因为在Qt中自己经常使用C语言的int system( const char …
阅读更多...
c++11--左值，右值，移动语义，引用折叠，模板类型推断，完美转发

c++11--左值，右值，移动语义，引用折叠，模板类型推断，完美转发

1.移动语义 移动构造和移动赋值均属于移动语义范畴。 移动语义的实现依赖于右值概念&#xff0c;右值引用。 1.1.一个移动构造的实例 #include <iostream> using namespace std; class HasPtrMem{ public:HasPtrMem():d(new int(3)){cout << "Construct: &qu…
阅读更多...
信号与线性系统翻转课堂笔记4——连续LTI系统的微分方程模型与求解

信号与线性系统翻转课堂笔记4——连续LTI系统的微分方程模型与求解

信号与线性系统翻转课堂笔记4——连续LTI系统的微分方程模型与求解 The Flipped Classroom4 of Signals and Linear Systems 对应教材&#xff1a;《信号与线性系统分析&#xff08;第五版&#xff09;》高等教育出版社&#xff0c;吴大正著 一、要点 &#xff08;1&#x…
阅读更多...
gitee提交代码步骤介绍（含git环境搭建）

gitee提交代码步骤介绍（含git环境搭建）

1、gitee官网地址 https://gitee.com; 2、Windows中安装git环境 参考博客&#xff1a;《Windows中安装Git软件和TortoiseGit软件》&#xff1b; 3、设置用户名和密码 这里的用户名和密码就是登录gitee网站的用户名和密码如果设置错误&#xff0c;可以在Windows系统的“凭据管理…
阅读更多...
Kubernetes (k8s) 快速认知

Kubernetes (k8s) 快速认知

应用部署方式 传统部署时代 早期的时候&#xff0c;各个组织是在物理服务器上运行应用程序。缺点 资源分配问题&#xff1a; 无法限制在物理服务器中运行的应用程序资源使用 维护成本问题&#xff1a; 部署多个物理机&#xff0c;维护许多物理服务器的成本很高 虚拟化部署时…
阅读更多...
论文修改润色算学术不端吗 快码论文

论文修改润色算学术不端吗 快码论文

大家好&#xff0c;今天来聊聊论文修改润色算学术不端吗&#xff0c;希望能给大家提供一点参考。 以下是针对论文重复率高的情况&#xff0c;提供一些修改建议和技巧&#xff0c;可以借助此类工具&#xff1a; 标题&#xff1a;论文修改润色是否算学术不端&#xff1f;专业软件…
阅读更多...
U-boot启动流程与加载内核过程

U-boot启动流程与加载内核过程

目录 一、U-boot启动过程流程图二、U-boot启动过程函数简单注释 本篇文章梳理了一下对正点原子的驱动开发教程中u-boot启动流程的梳理&#xff0c;制作了一份流程图&#xff0c;并简单的记录了一下各函数的作用&#xff0c;方便回头翻阅。 一、U-boot启动过程流程图 二、U-boot…
阅读更多...
git-lfs基本知识讲解

git-lfs基本知识讲解

目录 1. 基本知识2. 安装 1. 基本知识 git-lfs 是 Git Large File Storage 的缩写&#xff0c;是 Git 的一个扩展&#xff0c;用于处理大文件的版本控制。 它允许你有效地管理和存储大型二进制文件&#xff0c;而不会使 Git 仓库变得过大和不稳定。以下是一些与 git-lfs 相关…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站