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');
协议
-
本作品代码部分采用 Apache 2.0协议进行许可。遵循许可的前提下,你可以自由地对代码进行修改,再发布,可以将代码用作商业用途。但要求你:
- 署名:在原有代码和衍生代码中,保留原作者署名及代码来源信息。
- 保留许可证:在原有代码和衍生代码中,保留Apache 2.0协议文件。
-
本作品文档部分采用知识共享署名 4.0 国际许可协议进行许可。 遵循许可的前提下,你可以自由地共享,包括在任何媒介上以任何形式复制、发行本作品,亦可以自由地演绎、修改、转换或以本作品为基础进行二次创作。但要求你:
- 署名:应在使用本文档的全部或部分内容时候,注明原作者及来源信息。
- 非商业性使用:不得用于商业出版或其他任何带有商业性质的行为。如需商业使用,请联系作者。
- 相同方式共享的条件:在本文档基础上演绎、修改的作品,应当继续以知识共享署名 4.0国际许可协议进行许可。