mysql游标

最新推荐文章于 2024-05-20 20:26:39 发布

wjopenice

最新推荐文章于 2024-05-20 20:26:39 发布

阅读量21

点赞数 1

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/u013286192/article/details/132473285

版权

数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

CREATE TABLE `goods_category` (
`id` bigint(20) NOT NULL COMMENT '主键',
`category_name` varchar(50) NOT NULL DEFAULT '' COMMENT '分类名称',
`short_name` varchar(50) NOT NULL DEFAULT '' COMMENT '简称',
`pid` bigint(20) NOT NULL DEFAULT '0' COMMENT '分类上级',
`level` tinyint(3) NOT NULL DEFAULT '0' COMMENT '层级',
`is_show` tinyint(3) NOT NULL DEFAULT '0' COMMENT '是否显示',
`sort` tinyint(4) NOT NULL DEFAULT '0' COMMENT '排序',
`image` varchar(255) NOT NULL DEFAULT '' COMMENT '分类图片',
`keywords` varchar(50) NOT NULL DEFAULT '' COMMENT '分类页面关键字',
`description` varchar(100) NOT NULL DEFAULT '' COMMENT '分类介绍',
`attr_class_id` tinyint(3) NOT NULL DEFAULT '0' COMMENT '关联商品类型id',
`attr_class_name` varchar(100) DEFAULT '' COMMENT '关联商品类型名称',
`category_id_1` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '一级分类id',
`category_id_2` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '二级分类id',
`category_id_3` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '三级分类id',
`category_id_4` bigint(20) NOT NULL DEFAULT '0' COMMENT '四级分类id',
`category_full_name` varchar(100) DEFAULT '' COMMENT '组装名称',
`image_adv` varchar(255) DEFAULT '' COMMENT '分类广告图',
`created_user_id` bigint(20) DEFAULT NULL COMMENT '创建人ID',
`created_user_name` varchar(50) DEFAULT NULL COMMENT '创建人名称',
`created_time_db` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间-数据库操作时间',
`updated_user_id` bigint(20) DEFAULT NULL COMMENT '修改人ID',
`updated_user_name` varchar(50) DEFAULT NULL COMMENT '修改人名称',
`updated_time_db` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间-数据库默认写入时间',
`deleted_state` tinyint(3) DEFAULT '0' COMMENT '是否删除(0未删除;1已删除)',
`deleted_time` datetime DEFAULT NULL COMMENT '删除时间',
`deleted_user_id` bigint(20) DEFAULT NULL COMMENT '删除用户ID',
`deleted_user_name` varchar(50) DEFAULT NULL COMMENT '删除用户名称',
`tenant_id` bigint(20) DEFAULT '199' COMMENT '租户ID',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT=' 商品分类';

INSERT INTO `goods`.`goods_category`(`id`, `category_name`, `short_name`, `pid`, `level`, `is_show`, `sort`, `image`, `keywords`, `description`, `attr_class_id`, `attr_class_name`, `category_id_1`, `category_id_2`, `category_id_3`, `category_id_4`, `category_full_name`, `image_adv`, `created_user_id`, `created_user_name`, `created_time_db`, `updated_user_id`, `updated_user_name`, `updated_time_db`, `deleted_state`, `deleted_time`, `deleted_user_id`, `deleted_user_name`, `tenant_id`) VALUES (1538730771090817025, '食品酒水', '食品酒水', 0, 1, 0, 1, 'http://xxxx.oss-cn-hangzhou.aliyuncs.com/upload22035917164015995761566.png', '食品酒水', '食品酒水', 1, '', 1538730771090817025, 0, 0, 0, '食品酒水', '', NULL, NULL, '2022-06-20 11:49:38', NULL, NULL, '2022-06-24 16:45:46', 0, NULL, NULL, NULL, 199);
INSERT INTO `goods`.`goods_category`(`id`, `category_name`, `short_name`, `pid`, `level`, `is_show`, `sort`, `image`, `keywords`, `description`, `attr_class_id`, `attr_class_name`, `category_id_1`, `category_id_2`, `category_id_3`, `category_id_4`, `category_full_name`, `image_adv`, `created_user_id`, `created_user_name`, `created_time_db`, `updated_user_id`, `updated_user_name`, `updated_time_db`, `deleted_state`, `deleted_time`, `deleted_user_id`, `deleted_user_name`, `tenant_id`) VALUES (1538730881019330562, '护肤美妆', '护肤美妆', 0, 1, 0, 1, 'http://xxx.oss-cn-hangzhou.aliyuncs.com/222040437164016027715575.png', '护肤美妆', '护肤美妆', 1, '', 1538730881019330562, 0, 0, 0, '护肤美妆', '', NULL, NULL, '2022-06-20 11:50:04', NULL, NULL, '2022-06-24 16:45:46', 1, NULL, NULL, NULL, 199);
INSERT INTO `goods`.`goods_category`(`id`, `category_name`, `short_name`, `pid`, `level`, `is_show`, `sort`, `image`, `keywords`, `description`, `attr_class_id`, `attr_class_name`, `category_id_1`, `category_id_2`, `category_id_3`, `category_id_4`, `category_full_name`, `image_adv`, `created_user_id`, `created_user_name`, `created_time_db`, `updated_user_id`, `updated_user_name`, `updated_time_db`, `deleted_state`, `deleted_time`, `deleted_user_id`, `deleted_user_name`, `tenant_id`) VALUES (1538730953618538498, '数码家电', '数码家电', 0, 1, 0, 1, 'http://xxxx.oss-cn-hangzhou.aliyuncs.com/16031565326.png', '数码家电', '数码家电', 1, '', 1538730953618538498, 0, 0, 0, '数码家电', '', NULL, NULL, '2022-06-20 11:50:22', NULL, NULL, '2022-06-24 16:45:46', 0, NULL, NULL, NULL, 199);
INSERT INTO `goods`.`goods_category`(`id`, `category_name`, `short_name`, `pid`, `level`, `is_show`, `sort`, `image`, `keywords`, `description`, `attr_class_id`, `attr_class_name`, `category_id_1`, `category_id_2`, `category_id_3`, `category_id_4`, `category_full_name`, `image_adv`, `created_user_id`, `created_user_name`, `created_time_db`, `updated_user_id`, `updated_user_name`, `updated_time_db`, `deleted_state`, `deleted_time`, `deleted_user_id`, `deleted_user_name`, `tenant_id`) VALUES (1538731006064115713, '个人护理', '个人护理', 0, 1, 0, 1, 'http://xxxx.oss-cn-hangzhou.aliyuncs.com016035746314.png', '个人护理', '个人护理', 1, '', 1538731006064115713, 0, 0, 0, '个人护理', '', NULL, NULL, '2022-06-20 11:50:34', NULL, NULL, '2022-06-24 16:45:46', 0, NULL, NULL, NULL, 199);
INSERT INTO `goods`.`goods_category`(`id`, `category_name`, `short_name`, `pid`, `level`, `is_show`, `sort`, `image`, `keywords`, `description`, `attr_class_id`, `attr_class_name`, `category_id_1`, `category_id_2`, `category_id_3`, `category_id_4`, `category_full_name`, `image_adv`, `created_user_id`, `created_user_name`, `created_time_db`, `updated_user_id`, `updated_user_name`, `updated_time_db`, `deleted_state`, `deleted_time`, `deleted_user_id`, `deleted_user_name`, `tenant_id`) VALUES (1538731286054879234, '家居日用', '家居日用', 0, 1, 0, 1, 'http://xxxx.oss-cn-hangzhou.aliyuncs.com/9164016038941983.png', '家居日用', '家居日用', 1, '', 1538731286054879234, 0, 0, 0, '家居日用', '', NULL, NULL, '2022-06-20 11:51:41', NULL, NULL, '2022-06-24 16:45:46', 0, NULL, NULL, NULL, 199);
INSERT INTO `goods`.`goods_category`(`id`, `category_name`, `short_name`, `pid`, `level`, `is_show`, `sort`, `image`, `keywords`, `description`, `attr_class_id`, `attr_class_name`, `category_id_1`, `category_id_2`, `category_id_3`, `category_id_4`, `category_full_name`, `image_adv`, `created_user_id`, `created_user_name`, `created_time_db`, `updated_user_id`, `updated_user_name`, `updated_time_db`, `deleted_state`, `deleted_time`, `deleted_user_id`, `deleted_user_name`, `tenant_id`) VALUES (1538731438421360641, '母婴玩具', '母婴玩具', 0, 1, 0, 1, 'http://xxxx.oss-cn-hangzhou.aliyuncs.com/6042382170.png', '母婴玩具', '母婴玩具', 1, '', 1538731438421360641, 0, 0, 0, '母婴玩具', '', NULL, NULL, '2022-06-20 11:52:17', NULL, NULL, '2022-06-24 16:45:46', 0, NULL, NULL, NULL, 199);
INSERT INTO `goods`.`goods_category`(`id`, `category_name`, `short_name`, `pid`, `level`, `is_show`, `sort`, `image`, `keywords`, `description`, `attr_class_id`, `attr_class_name`, `category_id_1`, `category_id_2`, `category_id_3`, `category_id_4`, `category_full_name`, `image_adv`, `created_user_id`, `created_user_name`, `created_time_db`, `updated_user_id`, `updated_user_name`, `updated_time_db`, `deleted_state`, `deleted_time`, `deleted_user_id`, `deleted_user_name`, `tenant_id`) VALUES (1538731517337190401, '珠宝配饰', '珠宝配饰', 0, 1, 0, 1, 'http://xxxx.oss-cn-hangzhou.aliyuncs.com/8164016068819321.png', '珠宝配饰', '珠宝配饰', 1, '', 1538731517337190401, 0, 0, 0, '珠宝配饰', '', NULL, NULL, '2022-06-20 11:52:36', NULL, NULL, '2022-06-24 16:45:46', 0, NULL, NULL, NULL, 199);
INSERT INTO `goods`.`goods_category`(`id`, `category_name`, `short_name`, `pid`, `level`, `is_show`, `sort`, `image`, `keywords`, `description`, `attr_class_id`, `attr_class_name`, `category_id_1`, `category_id_2`, `category_id_3`, `category_id_4`, `category_full_name`, `image_adv`, `created_user_id`, `created_user_name`, `created_time_db`, `updated_user_id`, `updated_user_name`, `updated_time_db`, `deleted_state`, `deleted_time`, `deleted_user_id`, `deleted_user_name`, `tenant_id`) VALUES (1538731572056080385, '户外运动', '户外运动', 0, 1, 0, 1, 'http://xxxx.oss-cn-hangzhou.aliyuncs.com/20211208164016072840188.png', '户外运动', '户外运动', 1, '', 1538731572056080385, 0, 0, 0, '户外运动', '', NULL, NULL, '2022-06-20 11:52:49', NULL, NULL, '2022-06-24 16:45:47', 0, NULL, NULL, NULL, 199);
INSERT INTO `goods`.`goods_category`(`id`, `category_name`, `short_name`, `pid`, `level`, `is_show`, `sort`, `image`, `keywords`, `description`, `attr_class_id`, `attr_class_name`, `category_id_1`, `category_id_2`, `category_id_3`, `category_id_4`, `category_full_name`, `image_adv`, `created_user_id`, `created_user_name`, `created_time_db`, `updated_user_id`, `updated_user_name`, `updated_time_db`, `deleted_state`, `deleted_time`, `deleted_user_id`, `deleted_user_name`, `tenant_id`) VALUES (1538731638762291202, '服饰箱包', '服饰箱包', 0, 1, 0, 1, 'http://xxxxx.oss-cn-hangzhou.aliyuncs.com/upload/1245164016076594552.png', '服饰箱包', '服饰箱包', 1, '', 1538731638762291202, 0, 0, 0, '服饰箱包', '', NULL, NULL, '2022-06-20 11:53:05', NULL, NULL, '2022-06-24 16:45:47', 0, NULL, NULL, NULL, 199);
INSERT INTO `goods`.`goods_category`(`id`, `category_name`, `short_name`, `pid`, `level`, `is_show`, `sort`, `image`, `keywords`, `description`, `attr_class_id`, `attr_class_name`, `category_id_1`, `category_id_2`, `category_id_3`, `category_id_4`, `category_full_name`, `image_adv`, `created_user_id`, `created_user_name`, `created_time_db`, `updated_user_id`, `updated_user_name`, `updated_time_db`, `deleted_state`, `deleted_time`, `deleted_user_id`, `deleted_user_name`, `tenant_id`) VALUES (1538731720433778689, '汽车用品', '汽车用品', 0, 1, 0, 1, 'http://xxxx.oss-cn-hangzhou.aliyuncs.com/uplo067438359454.png', '汽车用品', '汽车用品', 1, '', 1538731720433778689, 0, 0, 0, '汽车用品', '', NULL, NULL, '2022-06-20 11:53:25', NULL, NULL, '2022-06-24 16:45:47', 0, NULL, NULL, NULL, 199);

案例：

DROP PROCEDURE IF EXISTS init_reportUrl3;

CREATE PROCEDURE init_reportUrl3()
BEGIN
DECLARE s int DEFAULT 0;
DECLARE Sid BIGINT DEFAULT 0;
DECLARE ACI tinyint DEFAULT 0;

DECLARE report CURSOR FOR select id as Sid,attr_class_id as ACI from goods_category;

open report;
fetch report into Sid,ACI;
while s<>1 do
update goods_category set sort = sort + ACI where id = Sid;
fetch report into Sid,ACI;
end while;
close report;
END;

call init_reportUrl3();