mysql存储过程快速上手

建表SQL

创建user表

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for user-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user`  (`busiid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT \'\',`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT \'\',`partmnt` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`uuid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;SET FOREIGN_KEY_CHECKS = 1;

创建partment表

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for partment-- ----------------------------DROP TABLE IF EXISTS `partment`;CREATE TABLE `partment`  (`busiid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT \'\',`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT \'\',`partmnt` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`uuid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`numb` int(50) NULL DEFAULT NULL,PRIMARY KEY (`busiid`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;SET FOREIGN_KEY_CHECKS = 1;

存储过程一

循环插入num条数据

drop procedure create4user;create procedure create4user(in busiid varchar(100),in num int(20))BEGINwhile num>0 doinsert into user select busiid,concat(num,\'a\'),null,\'hello\' as partment,replace(uuid(),\"-\",\"\") as uuid;set num = num - 1;END WHILE;end;

存储过程二

插入一条关联存储过程一的busiid的数据到partment表

drop procedure create4partment;create procedure create4partment(in busi varchar(100))BEGINDECLARE NUMB int(50);declare pwd varchar(255);DECLARE cnt_cursor cursor for select count(*) as numb,concat(\'000000000001\',REPLACE(unix_timestamp(current_timestamp(3)),\'.\',\'\')) AS pwd from user where `busiid` = busi ;OPEN cnt_cursor;FETCH cnt_cursor INTO NUMB,pwd;insert into partment select busi as busiid,\'jackson\',pwd as `password`,\'hello\' as partment,replace(uuid(),\"-\",\"\") as uuid,numb;close cnt_cursor;end;-- 这么使用的原因;在Orcle数据库中group by语句只能为group by的-- 字段或者聚集函数当有很多非聚集的字段且不需出现在group by 中，则可以使用存储过程实现-- 尽量不要使入参变量与变量名相同，可能会出现结果与想象的不同

存储过程三

调用存储过程一和存储过程二

drop procedure test4union;create procedure test4union(in num int(20))BEGINDECLARE busiid varchar(36);set busiid = (select replace(uuid(),\"-\",\"\"));call create4user(busiid,num);call create4partment(busiid);END

调用存储过程三

call test4union(1234567890)

mysql游标使用

DROP PROCEDURE CURSOR_OPERATE;CREATE PROCEDURE CURSOR_OPERATE(in num int(20))BEGINDECLARE CURSOR_OP1 VARCHAR(50); -- 定义变量DECLARE CURSOR_OP CURSOR FOR SELECT BUSIID FROM partment WHERE PARTMNT = \'hello\'; -- 定义游标OPEN CURSOR_OP;LOOPFETCH CURSOR_OP into CURSOR_OP1;UPDATE partment set numb = num where busiid = CURSOR_OP1; -- 使用LOOP循环 根据游标的结果集对指定行进行更新END LOOP;CLOSE CURSOR_OP;END;

CALL CURSOR_OPERATE(\'123\');