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))
BEGIN
while num>0 do
insert 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))
BEGIN
DECLARE 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))
BEGIN
DECLARE 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))
BEGIN 
DECLARE CURSOR_OP1 VARCHAR(50); -- 定义变量
DECLARE CURSOR_OP CURSOR FOR SELECT BUSIID FROM partment WHERE PARTMNT = 'hello'; -- 定义游标

OPEN CURSOR_OP;
	LOOP
	FETCH 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');

转载请注明 原文地址