MySQL存储过程、变量、游标和存储函数

最新推荐文章于 2024-07-25 14:52:43 发布
最新推荐文章于 2024-07-25 14:52:43 发布
阅读量159 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 python
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/pljnb/article/details/128679839
版权

MySQL存储过程

文章目录

  • 本文用python执行SQL语句。
    导入python库
import json
import pymysql
from pymysql.converters import escape_string
import pandas as pd

连接数据库

#连接数据库
conn=pymysql.connect(
    host="localhost",
    port=3306,#端口号
    user="root",#数据库用户
    password="123456",#数据库密码
    #database="world"#要连接的数据库名称
)
cur=conn.cursor()

跳转到指定数据库

sql = """USE PLJNB"""
cur.execute(sql)
cur.fetchall()

介绍

将SQL语句封装并重用，提高数据处理效率

特点：
  • 封装、复用
  • 可以接收参数，也可以返回数据
  • 减少网络交互，提升效率

创建存储过程

  • 在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL结束符。
  • 例：
    delimiter $$ ：表示以该命令行$$为SQL结束符
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	--SQL语句
END;
  • 例：
sql = """
    CREATE PROCEDURE p1()
    BEGIN
        SELECT COUNT(*) FROM student;
    END;
"""
cur.execute(sql)
cur.fetchall()

调用存储过程

CALL 名称([参数]);
  • 例：
sql = """
    CALL p1();
"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()),columns=[i[0] for i in cur.description])

查看存储过程

查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名'
查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

删除某个存储过程

DROP PROCEDURE [IF EXISTS] 存储过程名称;

变量

系统变量
  • MySQL服务器提供，不是用户定义的，分为全局变量(GLOBAL)、会话变量(SESSION)。
  • 服务重启后，所设置的全局变量会失效，若想不失效，需在/etc/my.cnf中设置。
查看系统变量
  1. 查看所有系统变量：
SHOW [SESSION|GLOBAL] VARIABLES;
  1. 模糊查找变量：
SHOW [SESSION|GLOBAL] VARIABLES LIKE '...'
  1. 查看指定变量：
SELECT @@[SESSION|GLOBAL].系统变量名;
设置系统变量
SET [SESSION|GLOBAL] 系统变量名 = 值;

或

SET @@[SESSION|GLOBAL].系统变量名 = 值;

用户定义变量

  • 用户变量赋值时用:=以区分判断=
  • 用户根据自己需要定义的变量，用"@变量名"定义即可，作用域为当前会话
  • 不需要初始化，也不需要提前声明，不初始化时取值为NULL
用户变量赋值
SET @变量名 = 值 [,@变量名 = 值]...;

或

SET @变量名 := 值 [,@变量名 := 值]...;

或

SELECT @变量名 := 值 [,@变量名 := 值]...;

或

SELECT 字段名 INTO @变量名 FROM 表名;
用户变量使用
SELECT @变量名;

局部变量

  • 在存储过程内使用的局部变量，需要DECLARE声明
局部变量声明
DECLARE 变量名 变量类型 [DEFAULT...];(变量类型：INT、BIGINT等)
局部变量赋值
SET 变量名 = 值;

或

SET 变量名 := 值;

或

SELECT 字段名 INTO 变量名 FROM 表名...;
  • 例：
sql = """
    CREATE PROCEDURE p2()
    BEGIN
        DECLARE stu_count INT DEFAULT 1;
        SELECT COUNT(*) INTO stu_count FROM student;
        SELECT stu_count;
    END;
"""
cur.execute(sql)
cur.fetchall()

存储过程的参数

参数类别
参数关键字作用
IN该类参数作为输入，也就是需要调用时传入值
OUT该类参数作为输出，也就是该参数可以作为返回值
INOUT既可以作为输入参数，也可以作为输出参数
含参存储过程的创建
CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
  --SQL语句
END;
  • 例：
sql = """
    CREATE PROCEDURE p4(IN age INT,OUT age_rank VARCHAR(10))
    BEGIN
        IF age >= 23 THEN
            SET age_rank := '99年';
        ELSEIF age >= 22 THEN
            SET age_rank := '00年';
        ELSE
            SET age_rank := '00后';
        END IF;
        SELECT age_rank;
    END;
"""
cur.execute(sql)
cur.fetchall()
含参存储过程的执行
SET @用户变量 = '默认值';
CALL 存储过程名(输入值, @用户变量);
SELECT @用户变量;

存储过程的逻辑函数-IF

IF 条件1 THEN
  -- 满足条件1，执行此SQL
ELSEIF 条件2 THEN
  -- 满足条件2，执行此SQL
ELSE
  -- 否则执行此SQL
END IF;
  • 例：
sql = """
    CREATE PROCEDURE p4(IN age INT,OUT age_rank VARCHAR(10))
    BEGIN
        IF age >= 23 THEN
            SET age_rank := '99年';
        ELSEIF age >= 22 THEN
            SET age_rank := '00年';
        ELSE
            SET age_rank := '00后';
        END IF;
        SELECT age_rank;
    END;
"""
cur.execute(sql)
cur.fetchall()

存储过程的逻辑函数-CASE

写法一
CASE case变量
  WHEN 值1 THEN SQL语句1  （若case变量=值1，则执行SQL语句1）
  WHEN 值2 THEN SQL语句2  （若case变量=值2，则执行SQL语句2）
  ELSE SQL语句3           （否则执行SQL语句3）
END CASE;
写法二
CASE
  WHEN 判断语句1 THEN SQL语句1 （判断语句1为真，则执行SQL语句1）
  WHEN 判断语句2 THEN SQL语句2 （判断语句2为真，则执行SQL语句2）
  ELSE SQL语句3                （否则执行SQL语句2）
END CASE
  • 例：
sql = """
    CREATE PROCEDURE p5(IN age INT,OUT age_rank VARCHAR(10))
    BEGIN
        CASE
        WHEN age >= 23 THEN SET age_rank := '99年';
        WHEN age >= 22 THEN SET age_rank := '00年';
        ELSE SET age_rank := '00后';
        END CASE;
    END;
"""
cur.execute(sql)
cur.fetchall()

存储过程的逻辑函数-WHILE

WHILE 条件 DO
  SQL逻辑...
END WHILE;
  • 例：
sql = """
    CREATE PROCEDURE pwhile(IN n INT,INOUT result INT)
    BEGIN
        DECLARE i INT DEFAULT 0;
        WHILE i <= n DO
            SET result := result + 2;
            SET i := i + 1;
        END WHILE;
        SELECT result;
    END;
"""
cur.execute(sql)
cur.fetchall()

存储过程的逻辑函数-REPEAT

REPEAT
  SQL逻辑...
  UNTIL 条件 （满足则退出）
END REPEAT;
  • 例：
sql = """
    CREATE PROCEDURE prepeat(IN n INT,INOUT result INT)
    BEGIN
        DECLARE i INT DEFAULT 0;
        REPEAT
            SET result := result + 2;
            SET i := i + 1;
            UNTIL i > n
        END REPEAT;
        SELECT result;
    END;
"""
cur.execute(sql)
cur.fetchall()

存储过程的逻辑函数-LOOP

循环体
[标记:]LOOP  (标记用于标识loop循环)
  SQL逻辑...
END LOOP [标记];
循环逻辑
LEAVE 标记;    退出指定标记的循环体
ITERATE 标记;  直接进入下一次循环
  • 例：
sql = """
    CREATE PROCEDURE ploop(IN n INT,INOUT result INT)
    BEGIN
        DECLARE i INT DEFAULT 0;
        sum:LOOP
            IF i > n THEN
                LEAVE sum;
            END IF;
            SET result := result + 2;
            SET i := i + 1;
        END LOOP sum;
        SELECT result;
    END;
"""
cur.execute(sql)
cur.fetchall()

游标

介绍

  • 游标是用来存储查询结果集的数据类型
  • 在存储过程和函数中可以使用游标对结果集进行循环的处理
  • 用法在“条件存储程序”的例子中

声明游标

DECLARE 游标名称 CURSOR FOR 查询语句; (查询语句的结果集存储在游标中)

打开游标

OPEN 游标名称;

获取游标记录

FETCH 游标名称 INTO 变量[,变量];

关闭游标

CLOSE 游标名称;

条件存储程序

  • 用来定义再流程控制结构执行过程中遇到问题时相应的处理步骤（异常处理）
定义条件处理程序

程序主体是SQL语句，SQL语句前的关键字指定异常处理类型和条件。

DECLARE handler_action HANDLER FOR condition_value SQL语句;
handler_action (条件处理程序的类型)
条件处理程序类型的关键字作用
CONTINUE：继续执行当前程序
EXIT：终止执行当前程序
condition_value (条件)：
条件的关键字作用
SQLSTATE sqlstate_value(状态码，如02000)指定状态码
SQLWARNING所有以01开头的SQLSTATE状态码
NOT FOUND所有以02开头的SQLSTATE状态码
SQLEXCEPTION所有其他开头的SQLSTATE状态码
  • 例：查找user表中年龄小于uage的记录，复制到user_pro表中。
sql = """
    CREATE PROCEDURE p11(IN uage INT)
    BEGIN
    
        DECLARE uname VARCHAR(100);
        DECLARE u_cursor CURSOR FOR SELECT name,age FROM user WHERE age <= uage;
        DECLARE EXIT HANDLER FOR SQLSTATE '02000' CLOSE u_cursor;
        
        DROP TABLE IF EXISTS user_pro;
        CREATE TABLE IF NOT EXISTS user_pro(
            id INT PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(100),
            age INT
        );
        
        OPEN u_cursor;
        WHILE TRUE DO
            FETCH u_cursor INTO uname,uage;
            INSERT INTO user_pro VALUES (NULL,uname,uage);
        END WHILE;
        CLOSE u_cursor;
        
    END;
"""
cur.execute(sql)
cur.fetchall()

存储函数

介绍

  • 是有返回值的存储过程
  • 存储函数的参数只能是IN类型

创建存储函数

CREATE FUNCTION 存储函数名称([参数列表])
  RETURNS 返回值类型 [characteristic...]
    BEGIN
      --SQL语句
      RETURN...;
    END;
characteristic(特性)
条件的关键字作用
DETERMINISTIC相同的输入参数总是产生相同的结果
NO SQL不包含SQL语句
READS SQL DATA包含读取数据的语句，但不包含写入数据的语句
  • 例：求n个2的和
sql = """
    CREATE FUNCTION floop(n INT)
    RETURNS INT DETERMINISTIC
    BEGIN
        DECLARE i INT DEFAULT 0;
        DECLARE result INT DEFAULT 0;
        sum:LOOP
            IF i > n THEN
                LEAVE sum;
            END IF;
            SET result := result + 2;
            SET i := i + 1;
        END LOOP sum;
        RETURN result;
    END;
"""
cur.execute(sql)
cur.fetchall()

执行存储函数

SELECT 存储函数名();
pljnb
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL数据库实操教程(19)——详解存储过程及游标使用
谷哥的小弟
08-04 2273
在开发过程中，我们经常会遇到重复使用某一功能的情况。为此，MySQL引人了存储过程(Stored Procedure)这一技术。所谓存储过程指的就是一条或多条SQL语句的集合。存储过程可以将一些列复杂操作封装成一个代码块，以便重复使用，从而极大地减少数据库开发人的工作量提升开发效率。SQL语句需要先编译然后执行，而存储过程可将为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字通过传参的方式对其进行调用。存储过程可看做是编程的函数，它允许以传参调用的访问方式。
mysql之存储过程，函数，游标
Linzhongyilisha的专栏
06-28 475
转载：http://www.jb51.net/article/70677.htm 存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。 一、存储过程 1.1、基本语法 CRE
MySql 自定义函数,存储过程,游标的使用
小平果的欢乐谷
08-09 5171
首先整理一下mysql内置的一些函数MySQL内置函数一、字符函数 （1）CONCAT()//字符连接 （2）CONCAT_WS()//使用指定的分隔符进行字符连接 （3）FORMAT()//数字格式化 （4）LOWER()//转化小写 （5）UPPER()//转换大写 （6）LEFT()//获取左侧字符 （7）RIGHT()//获取右侧字符 （8）LENGTH()//取得字符串长度 （9）LTR
Mysql学习笔记(三)：fetchone(), fetchmany(), fetchall()详细总结
源的博客
12-28 2338
fetchone 返回单个的元组，也就是一条记录(row)，如果没有结果 则返回 None fetchmany 返回多个元组,返回多条记录(row),需要指定具体返回多少个记录。如fetchmany(2) 返回两条记录，默认则为1 fetchall 返回多个元组，即返回多个记录(rows),如果没有结果 则返回 () 需要注明：在MySQL中是NULL，而在Python中则是None 实际操作 进入数据库 选择数据库 创建数据表 create table test( id int unsigned pr
从mysql数据库取数据：fetchone()、fetchall() fetchmany()
python学习者Joy.xiong的博客
03-09 8694
最近碰到需要从mysql数据库取数据的操作，记一下使用到的函数： 从mysql数据库取数据用到的三个函数： fetchone() ： 返回单个的元组，也就是一条记录(row)，如果没有结果 则返回 None fetchall() ： 返回多个元组，即返回多个记录(rows),如果没有结果 则返回 () fetchmany(num): 同fetchall一样，只不过返回的是指定个数的元组。 self...
mysql fetch 存储过程,与fetch_all一起使用时，存储过程导致mysqli问题
weixin_35942339的博客
02-07 110
I have broken down this issue to it's essence but am still having problems.When I try and use fetch_all to get the results of a stored procedure, I get results returned to array as expected, but subse...
MySQL的存储过程和函数
qq_54725031的博客
11-08 491
一、存储过程和函数的概述 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。 存储过程和函数的区别在于函数必须有返回值，而存储过程没有。 函数：是一个有返回值的过程； 过程：是一个没有返回值的函数 PS:存储过程是封装SQL语句的集合 二、创建存储过程 CREATE PROCEDURE procedure_name ([proc_parameter[,...]]) b
MySQL存储过程和函数
qq_40634175的博客
08-07 120
目录1 概述2 存储过程2.1 创建2.2 调用2.3 删除2.4 查看存储过程信息3 函数3.1 创建3.2 调用3.3 查看函数信息3.4 删除4 存储过程与函数的区别 1 概述 一组预先编译号的SQL语句的集合，理解成批处理语句。 好处： 提高代码的重用性。 简化操作。 减少了编译次数，并且减少了和数据库服务器的连接次数，提高了效率。 2 存储过程 2.1 创建 语法： CREATE PROCEDURE 存储过程名(参数列表) BEGIN 存储过程体; END 注意： 参数列表包含三个部分
mysql存储过程之游标（DECLARE）原理与用法详解
09-08
MySQL存储过程中的游标（DECLARE）是处理查询结果集的重要工具，它允许程序逐行处理数据，而不是一次性加载所有结果。DECLARE语句用于在存储过程中声明一个游标，定义其与哪个SELECT语句关联，以及如何操作数据。 ...
mysql的存储过程、游标 、事务实例详解
12-16
MySQL的存储过程、游标和事务是数据库管理中非常重要的概念，它们在处理大量数据和实现复杂的业务逻辑时起着关键作用。以下是对这些概念的详细解释和实例分析。 **存储过程**： 存储过程是一组预编译的SQL语句，以...
mysql游标存储过程例子
08-15
根据提供的文件信息，本文将详细解释一个MySQL存储过程的例子，其中包含了游标的使用。这个存储过程主要用于处理一批数据，涉及到日期范围内的数据处理、异常处理等。下面将逐一解析存储过程中涉及的重要知识点。 #...
存储过程：利用游标+临时表实现查询
12-14
存储过程内部使用了全局变量和字符串连接（CONCAT函数）来处理输入参数，使其包含通配符，以适应模糊匹配的需求。然后，可能使用游标来遍历查询结果，将每一行的数据存入变量，进一步进行处理或返回给调用者。 总之...
MySql存储过程和游标的使用。
csdn2990的博客
03-24 7617
这里存储过程和游标的定义和作用就不介绍了，网上挺多的，只通过简单的介绍，然后用个案例让大家快速了解。实例中会具体说明变量的定义，赋值，游标的使用，控制语句，循环语句的介绍。 1.创建存储过程。 CREATE PROCEDURE myproc(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM students; END 存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义
MySQL的面试题，从简单到困难三道题目
在那一刻，我们或许会发现自己站在了时间的另一岸，以更加成熟和宽广的胸怀，去拥抱那份隔代相传的深情。
07-25 527
需要注意的是，虽然哈希索引在某些场景下具有优势，但由于其不支持范围查询和排序操作，且哈希冲突会影响性能，因此在大多数情况下，B+Tree索引仍然是MySQL中最常用的索引类型。
linux mysql 定时备份
java小白白之没有最白的博客
07-25 289
加入以下内容（/xp/server/mysql/bin/mysqldump是你的mysqldump备份命令，一般如果是正常安装的，可以直接用全局命令mysqldump，不用带前面的路径）4、编写crontab。5、查看是否添加成功。
AutoMySQLBackup execution.. Backup failed Docker部署mysql 自动备份失败！！
童龙辉的博客
07-25 894
Using a password on the command line interface can be insecure. mysqldump: [Warning] Using a password on the command line interface can be insecure. mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-o
数据库(MySQL)-视图、存储过程、触发器
weixin_64975807的博客
07-25 1004
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后，再次调用不需要重复编译，用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数）来执行它。触发器是数据库中针对数据库表操作触发的 特殊的存储过程。
【MySQL篇】Percona XtraBackup标准化全库完整备份策略（第三篇，总共五篇）
最新发布
奈斯DBA的博客
07-25 1124
Percona XtraBackup标准化全库完整备份策略
mysql存储过程的游标
09-27
在MySQL存储过程中，使用以下语法来声明和使用游标： ``` DECLARE cursor_name CURSOR FOR select_statement; OPEN cursor_name; FETCH cursor_name INTO variable_list; CLOSE cursor_name; ``` 其中，cursor_name...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值