MYSQL 8 基本操作之10 (实现for循环逐个遍历)

SQL：结构化查询语言，包括数据定义语言(DDL)如：CREATE、DROP、ALTER等；数据操作语言(DML)如：INSERT、UPDATE、DELETE之类；数据查询语言(DQL)如：SELECT语句；数据控制语言(DCL)如：GRANT、REVOKE、COMMIT、ROLLBACK等。
T-SQL：Transact-SQL，为SQL的语言的增强版，加入了程序语言中的if,while 等流程控制语法，同时可以使用函数功能

1. 创建测试用表

create table temp_table
 (
   id int auto_increment not null,
   province varchar(20),
   primary key(id)
 ) engine=InnoDB auto_increment=101;

insert temp_table(province) values('江苏省'),('广东'),('浙江省'),('山东'),('山西省');

root@127.0.0.1 : testdb【06:10:22】7 SQL->select * from temp_table;
+-----+-----------+
| id  | province  |
+-----+-----------+
| 101 | 江苏省    |
| 102 | 广东      |
| 103 | 浙江省    |
| 104 | 山东      |
| 105 | 山西省    |
+-----+-----------+

2. 编写T-SQL代码将表中 “省” 字去掉

delimiter //  # 定义//为一句sql的结束标志，取消;的所代表的意义
drop procedure if exists test;  # 如果存在名字为test的procedure则删除
create procedure test()  # 创建（创建函数使用的关键字为function 函数名()）
begin
    declare old_pro varchar(30);  # 声明变量
    declare temp_id int;
    declare flag int default 0;
    # 这是重点，定义一个游标来记录sql查询的结果(此处的知识点还有SQL的模糊查询，见补充)
    declare s_list cursor for select id, province from temp_table where  province  like '%省'; 
    # 为下面while循环建立一个退出标志，当游标遍历完后将flag的值设置为1
    declare continue handler for not found set flag=1;
    open s_list;  # 打开游标
    # 将游标中的值赋给定义好的变量，实现for循环的要点
        fetch s_list into temp_id, old_pro;
        while flag <> 1 do
            # sql提供了字符串的切分，有left、right、substring、substring_index
            # 在T-SQL中，局部变量必须以@作为前缀，声明方式set，select还有点差别
            set @temp_s = substring_index(old_pro, "省", 1);
            # 根据id的唯一性，利用当前查询到的记录中的字段值来实现更新
            update temp_table set province=@temp_s where id=temp_id;
            # 游标往后移（此处的游标是不是让你想起了C里面的指针）
            fetch s_list into temp_id, old_pro;
        end while;
        #select * from temp_table;
    close s_list;  # 关闭游标
end
//
delimiter ;  # 重新定义;为一句sql的结束标志，取消//的所代表的意义

3. 显示存储过程

root@127.0.0.1 : testdb【06:17:15】93 SQL->show create procedure test\G;
*************************** 1. row ***************************
           Procedure: test
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
begin
    declare old_pro varchar(30);  
    declare temp_id int;
    declare flag int default 0;    
    declare s_list cursor for select id, province from temp_table where  province  like '%省';     
    declare continue handler for not found set flag=1;
    open s_list;      
        fetch s_list into temp_id, old_pro;
        while flag <> 1 do           
            set @temp_s = substring_index(old_pro, "省", 1);            
            update temp_table set province=@temp_s where id=temp_id;            
            fetch s_list into temp_id, old_pro;
        end while;        
    close s_list;  
end

character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci

4. 调用存储过程

call test(); # 调用

5. 查询调用后的效果

root@127.0.0.1 : testdb【06:19:28】95 SQL->select * from temp_table;
+-----+----------+
| id  | province |
+-----+----------+
| 101 | 江苏     |
| 102 | 广东     |
| 103 | 浙江     |
| 104 | 山东     |
| 105 | 山西     |
+-----+----------+