mysql 存储过程
2022/4/22 19:16:25
本文主要是介绍mysql 存储过程,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
--alter table shiyanshuju.gdpoint add S_X_S1 double(10,3),add S_Y_S1 double(10,3),add E_X_S1 double(10,3),add E_Y_S1 double(10,3),add S_H_S1 float(9,3),add E_H_S1 float(9,3),add SE_S1 VARCHAR(255),
add D_S_S1 VARCHAR(20),add E_Deep_S1 double(10,3),add S_Deep_S1 double(10,3),add D_Type_S1 VARCHAR(20);--
存储过程调用 方法
call delColoums (1,2) --参数 字段排序开始,字段排序结束 call addColoums(1,1)
**** 存储过程 1:循环增加字段****
DROP PROCEDURE IF EXISTS `hd_ybz`.`addColoums`; CREATE DEFINER=`root`@`localhost` PROCEDURE `addColoums`(IN `startnum` int,IN `endnum` int) BEGIN DECLARE num int default 1; WHILE startnum <= endnum DO set @addSql = CONCAT('alter table shiyanshuju.gdpoint add S_X_S',startnum,' double(10,3), add S_Y_S',startnum,' double(10,3),add E_X_S',startnum,' double(10,3),add E_Y_S',startnum,' double(10,3),add S_H_S',startnum,' float(9,3),add E_H_S',startnum,' float(9,3),add SE_S',startnum,' VARCHAR(255), add D_S_S',startnum,' VARCHAR(20),add E_Deep_S',startnum,' double(10,3),add S_Deep_S',startnum,' double(10,3),add D_Type_S',startnum,' VARCHAR(20)'); prepare addstmt from @addSql; execute addstmt; DEALLOCATE PREPARE addstmt; set startnum = (startnum+num); END WHILE; IF startnum > endnum THEN set startnum = endnum; END IF ; SELECT CONCAT('增加字段PROCEDURE执行完毕:',startnum) result; END;
**** 存储过程 2: 循环删除字段***
DROP PROCEDURE IF EXISTS `hd_ybz`.`delColoums`; CREATE DEFINER=`root`@`localhost` PROCEDURE `delColoums`(IN `startnum` int,IN `endnum` int) BEGIN DECLARE num int default 1; WHILE startnum <= endnum DO set @delSql = CONCAT('alter table shiyanshuju.gdpoint drop column S_X_S',startnum,' ,drop column S_Y_S',startnum,' ,drop column E_X_S',startnum,' ,drop column E_Y_S',startnum,' ,drop column S_H_S',startnum,' ,drop column E_H_S',startnum,' ,drop column SE_S',startnum,', drop column D_S_S',startnum,',drop column E_Deep_S',startnum,',drop column S_Deep_S',startnum,',drop column D_Type_S',startnum,''); prepare delstmt from @delSql; execute delstmt; DEALLOCATE PREPARE delstmt; set startnum = (startnum+num); END WHILE; IF startnum > endnum THEN set startnum = endnum; END IF ; SELECT CONCAT('删除字段PROCEDURE执行完毕:',startnum) result; END;
这篇关于mysql 存储过程的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享
- 2024-02-22docker mysql 5.7
- 2024-02-18从 20 多套 MySQL 到 1 套 TiDB丨骏伯网络综合运营管理平台应用实践
- 2024-02-07mysql 外键索引入门介绍,为什么工作中很少有人使用?