【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 2

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 2

🍁6.4、conn.cursor()

概念:

这个函数是pymysql用来调用存储过程的,语法如下:

conn.cursor('存储过程的名称')

示例【还是用上面的class表】:

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='@123456', db='testdb', charset='utf8')
# 创建游标
cursor = conn.cursor()
# 调用存储过程p1
cursor.callproc('p1')
conn.commit()
print(cursor.fetchall())
cursor.close()
conn.close()
输出结果:
((1, '三年一班'), (2, '三年四班'), (3, '五年一班'), 
(4, '六年二班'), (5, '七年二班'), (6, 'teacher触发产生'),
(7, '触发产生'), (8, '触发产生'), (9, '调用了存储过程'),
(10, '调用了存储过程'))

🍁6.5、删除存储过程

语法:

DROP PROCEDURE [IF EXISTS] 存储过程名


🍁6.6、查看存储过程

  1. 使用SHOW CREATE语句查看存储过程和函数的创建信息

语法:

SHOW CREATE PROCEDURE  存储过程名


761783940f4a41b0a7fb9f0843436c0a.png

  1. 使用SHOW STATUS语句查看存储过程和函数的状态信息:

这个语句返回子程序的特征,如数据库、名字、类型、创建者及创建和修改日期。

[LIKE 'pattern']匹配存储过程的名称,可以省略。当省略不写时,会列出MySQL数据库中存在的所有存储过程的信息。

语法:

SHOW PROCEDURE  STATUS [LIKE 'pattern']


c1b03bf247444cd9b6bdd8c228c24594.png

3、使用SHOW STATUS语句查看存储过程和函数的状态信息:

MySQL中存储过程的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。

语法结构:

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程的名' [AND ROUTINE_TYPE = 'PROCEDURE'];

说明: 如果在MySQL数据库中存在存储过程和函数名称相同的情况,最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。

🍁6.7、修改存储过程

修改存储过程,不影响存储过程功能,只是修改相关特性。使用ALTER语句实现。

ALTER PROCEDURE  存储过程名;

🍁6.8、pymysql实现拿结果集

我使用6.3中2、调用out模式的参数:的存储过程的栗子

存储过程:


delimiter //
create procedure p2(
  in n1 int,
  out n2 char(15)
)
begin 
  set n2 = "在存储过程中改变";
  SELECT * FROM class WHERE cid > n1;
end //
delimiter ;

python代码

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='@123456', db='testdb', charset='utf8')
# 创建游标
cursor = conn.cursor()
# 调用存储过程,并且传参数
cursor.callproc('p2', (8,"开始值"))
print(cursor.fetchall())
# 拿到结果集
cursor.execute('select @_p2_0,@_p2_1')
print(cursor.fetchall())
cursor.close()
conn.close()
输出结果:
((9, '调用了存储过程'), (10, '调用了存储过程'))
((8, '在存储过程中改变'),)

cursor.execute('select @_p2_0,@_p2_1')@_p2_0表示拿p2中第一个参数,因为第一个参数是in得到的就是传入的值,@_p2_0_1表示拿p2中第二个参数,因为第二个参数是out所以得到的就是在存储过程中发生改变后的变量的值,,

如果是inout的话就会根据变量在存储过程中是否发生改变而返回对应的结果【不改变的话就是传入的参数,改变的就是在存储过程中发生改变的值】

我们也可以如下的方法:

set @_v1_0 = 8;
set @_v1_1 = "开始值";
call p2(@_v1_0, @_v1_1);
select @_v1_0, @_v1_1;
输出结果:
结果一               结果二
+---------------------+      +--------------------+
cid   caption       @_v1_0    @_v1_1
9   调用了存储过程     8 在存储过程中改变
10    调用了存储过程     +---------------------+
+---------------------+   

6.9、其他拓展内容

⚡6.9.1、LOOP实现

实现的需求,假如我有一张A表如下

id    num 
1    9
2      8
3      4

要实现循环拿到A表中每一行的id与num 并且将他们的值分别对应相加放到B表中的num列

实现的B表示例如下:

id    num
1   10
2   10
3   7

实现答案:

delimiter // 
create procedure p6()
begin
  -- 自定义变量用来存储每一行id值
  declare row_id int;
  declare row_num varchar(50);
  -- 如果done为false表示还没有终止,下面的游标还能继续执行
  declare done int default false;
  declare temp int
  -- 去A表中逐行取数据,相当于遍历A表中的每一行
  declare my_cursor cursor for select id, num from A;
  -- 游标终止的条件 ,检测当A表中没有数据done = ture就退出
  declare continue handler for not found set done = ture;
  -- 创建游标
  open my_cursor;
    -- 表示开启循环 loop表示循环
    loop_label:  LOOP
      fetch my_cursor into row_id, row_num;
      IF done THEN
        LEAVE loop_label; 
      END IF; 
        set temp = row_id + row_num;
        INSERT INTO B(num) VALUES(temp);
    -- 循环终止的条件
    END LOOP loop_label;
end //
delimiter ;

⚡6.9.2、条件语句

delimiter \\
-- 创建一个条件存储过程
CREATE PROCEDURE proc_if ()
BEGIN
 declare i int default 0;
 if i = 1 THEN
     SELECT 1;
 ELSEIF i = 2 THEN
     SELECT 2;
 ELSE
     SELECT 7;
 END IF;
END\\
delimiter ;

⚡6.9.3、while循环

delimiter \\
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
  SELECT
      num ;
  SET num = num + 1 ;
-- 结束的标识就是while不满足
END WHILE ;
END\\
delimiter ;

⚡6.9.4、repeat循环

delimiter \\
CREATE PROCEDURE proc_repeat ()
BEGIN
DECLARE i INT ;
SET i = 0 ;
repeat
  select i;
  set i = i + 1;
  until i >= 5
end repeat;
END\\
delimiter ;

🍁6.10、动态执行SQL

目的:防SQL注入

实现:

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
declare p1 int;
set p1 = 11;
    -- 必须要写,在下面using后才不会报错
set @p1 = p1;
    -- 预检测某个东西 SQL语句的合法性,prod为自定义的变量
PREPARE prod FROM 'select * from tb2 where nid > ?';
    --  @p1表示用p1的值替换上一行语句中的?号
EXECUTE prod USING @p1;
    -- 执行上面的已经格式化完成的SQL语句
DEALLOCATE prepare prod; 
END\\
delimiter ;

🍁6.11、支持事务的存储过程

事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。

delimiter \\
create PROCEDURE p1(
-- p_return_code根据值的不同用来表示状态
OUT p_return_code tinyint
)
BEGIN 
-- 设置发生SQL异常退出
DECLARE exit handler for sqlexception 
BEGIN 
-- ERROR(错误时返回1)
set p_return_code = 1; 
-- 回滚到未发生错误时的初始状态
rollback; 
END; 
-- 设置发生SQL警告退出
DECLARE exit handler for sqlwarning 
BEGIN 
-- WARNING(警告时返回2)
set p_return_code = 2; 
-- 回滚到初始状态
rollback; 
END; 
-- START TRANSACTION立即启动一个事务,而不管当前的提交模式设置如何。
-- 无论当前的提交模式设置如何,以START transaction开始的事务必须通过
-- 发出显式COMMIT或ROLLBACK来结束。
START TRANSACTION; 
DELETE from tb1;
insert into tb2(name)values('seven');
-- 因为没有发生错误,就将更改的数据提交
COMMIT; 
-- SUCCESS(成功时返回0)
set p_return_code = 0; 
END\\
delimiter ;

🍀7、其他

MySQL中如果对性能的要求比较高的话,一般是不推荐使用函数的,因为函数的使用可能会破坏索引加速查找的效果。可以在程序或者架构级别使用函数操作,有创建函数的想法时先看看有没有内置函数可以满足,避免重复造轮子😀

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
4天前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
1天前
|
SQL 关系型数据库 MySQL
MySQL数据库练习——视图
MySQL数据库练习——视图
6 1
|
2天前
|
SQL 存储 关系型数据库
【MySQL技术内幕】4.7-视图
【MySQL技术内幕】4.7-视图
10 0
|
2天前
|
安全 关系型数据库 MySQL
MYSQL--视图操作
MYSQL--视图操作
|
4天前
|
存储 关系型数据库 MySQL
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(4)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
4天前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(3)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
1天前
|
存储 关系型数据库 MySQL
MySQL周内训参照1、ER实体关系图与数据库模型图绘制
MySQL周内训参照1、ER实体关系图与数据库模型图绘制
6 1
|
1天前
|
弹性计算 关系型数据库 数据库
手把手带你从自建 MySQL 迁移到云数据库,一步就能脱胎换骨
阿里云瑶池数据库来开课啦!自建数据库迁移至云数据库 RDS原来只要一步操作就能搞定!
|
2天前
|
SQL 存储 关系型数据库
MySQL 示例数据库大全
我们练习 SQL 时,总会自己创造一些测试数据或者网上找些案例来学习,其实 MySQL 官方提供了好几个示例数据库,在 MySQL 的学习、开发和实践中具有非常重要的作用,能够帮助初学者更好地理解和应用 MySQL 的各种功能和特性,特别是练习 SQL 的好帮手。
22 0