mysql 存储过程

最新推荐文章于 2024-07-15 16:30:05 发布

az44yao

最新推荐文章于 2024-07-15 16:30:05 发布

阅读量451

点赞数

文章标签： css3 前端 css

本文链接：https://blog.csdn.net/az44yao/article/details/121478168

版权

CREATE DEFINER=`root`@`%` PROCEDURE `test_while_0066`(IN in_count INT)
BEGIN
 
DECLARE COUNT INT DEFAULT 1;
 
 DECLARE COUNT2 INT DEFAULT 0;
 DECLARE countnum INT;
 
 DROP TABLE if  exists youhuitable1;
 create temporary table if not exists youhuitable1(id INT);
 
 DROP TABLE if  exists youhuitable2;
 create temporary table if not exists youhuitable2(beginid INT,endid INT);

 
while COUNT<=in_count DO
set COUNT2 =COUNT*1000;

set countnum= (SELECT count(*) from tj_appointment_chose_group_item where id=COUNT2);
   
IF countnum=0 THEN 
   INSERT into youhuitable1 SELECT COUNT;
END IF;
  set COUNT=COUNT+1;
end while;
 
SELECT * from youhuitable1;

begin
    -- 创建 用于接收游标值的变量
    declare idx,total int;
                declare beforeid,curid int;
    -- 注意 接收游标值为中文时 需要 给变量 指定 字符集为utf8
  
    -- 游标结束的标志
    declare done int default 0;
    -- 声明游标
    declare cur cursor for select id from youhuitable1;
    -- 指定游标循环结束时的返回值 
    declare continue handler for not found set done = 1;
    -- 打开游标
    open cur;
    
    -- 初始化 变量
    set total = 0;
                  set beforeid = -1;
    
    -- while 循环
    while done != 1 do
        fetch cur into idx;
          if done != 1 then
             set total = total + 1;                                                
                                                  
                                                        set curid=idx;
                                                        if(curid=beforeid+1) then
                                                        UPDATE youhuitable2 set endid= curid where endid = beforeid;
                                                        end if;
                                                        
                                                                if(curid>beforeid+1) then
                                                         INSERT into youhuitable2(beginid,endid) VALUES (idx,idx);
                                                        end if;
                                                        
                                                
                                                  set beforeid=idx;
                                                 
                                                 
                                                 
                                                 
                                                 
                                                 
          end if;    
    end while;
    -- 关闭游标
    close cur;
    -- 输出 累计的结果
    select total;
end;


SELECT * from youhuitable2;
 
END

二

CREATE DEFINER=`root`@`%` PROCEDURE `kill002`()
BEGIN

 DECLARE  pid int default 0;
         
                  declare done int default 0;
  -- 游标
  DECLARE cur CURSOR FOR select id FROM information_schema.`PROCESSLIST` where  command='Sleep';
    -- 指定游标循环结束时的返回值 
    declare continue handler for not found set done = 1;
  -- 打开游标
  OPEN cur;
         -- while 循环
    while done != 1 do
                  fetch cur into pid;
                   kill pid;
                
        
   END WHILE;

        
        
         -- 关闭游标
    close cur;
end

三

CREATE DEFINER=`root`@`%` PROCEDURE `平移数据`()
BEGIN
-- 需要定义接收游标数据的变量 
  DECLARE  st VARCHAR(100);
	  DECLARE  dt VARCHAR(100);
		 DECLARE  spk VARCHAR(100);
		 DECLARE  dpk VARCHAR(100);
	 
		DECLARE  tableName VARCHAR(100);
		  declare done int default 0;
  -- 游标
  DECLARE cur CURSOR FOR select srcTable,destTable from `ry-vue`.trans_relation_table;
    -- 指定游标循环结束时的返回值 
    declare continue handler for not found set done = 1;
  -- 打开游标
  OPEN cur;
  
   -- while 循环
    while done != 1 do
          fetch cur into st,dt;
					start transaction;
					set @str0 ="CREATE TABLE if not EXISTS  `";

set @str1="`
                              (                  `id` int NOT NULL AUTO_INCREMENT,
                                                `dest_table_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '目标表表名',
                                                `dest_primary_key_column` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '目标表主键列',
                                                `dest_primary_key_value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '目标表主键值',
                                                `src_table_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '源表表名',
                                                `src_primary_key_column` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '源表主键列',
                                                `src_primary_key_value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '源表主键值',
                                                `create_user_id` int NULL DEFAULT NULL COMMENT '创建人id',
                                                `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
                                                `create_user_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人名称',
                                                `update_user_id` int NULL DEFAULT NULL COMMENT '修改人id',
                                                `update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改的时间',
                                                `update_user_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改人的名称',
                                                `is_delete` bit(1) NULL DEFAULT b'0' COMMENT '逻辑删除 0代表未删除1已删除',
                                                `table_relation_id` int NULL DEFAULT NULL COMMENT '表关系id',
                                                `module_id` int NULL DEFAULT NULL COMMENT '模块id',
                                                `plan_id` int NULL DEFAULT NULL COMMENT '方案id',
                                                `src_table_column_value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '源表源列源值',
                                                PRIMARY KEY (`id`) USING BTREE,
                                                INDEX `IDX_SRC_TABLE_COLUMN_VALUE`(`src_table_column_value`) USING BTREE
                                        ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '目标表与源表主键值对应关系'                 ROW_FORMAT = DYNAMIC;";


	set tableName =CONCAT(st,'-',dt,'-keyvalue');
	
	set @str3= CONCAT(@str0,tableName,@str1);
						
	
 -- SELECT @str3;
	
	 prepare s1 from  @str3;
 execute s1;
 deallocate prepare s1;
commit;
 
 		start transaction;
 select scr_table_column,dest_table_column into spk, dpk   from `ry-vue`.trans_relation_external where scr_table_name = st and dest_table_name = dt and is_pk_relation=1;
 

set @likestr=  CONCAT('1-',st,'-',spk,'-',dt,'-',dpk,'%');

set @strinsert =
     CONCAT (  'INSERT into  `',tableName,"`(  `id` ,
  `dest_table_name` ,
  `dest_primary_key_column` ,
  `dest_primary_key_value` ,
  `src_table_name`,
  `src_primary_key_column`,
  `src_primary_key_value` ,
  `create_user_id` ,
  `create_time` ,
  `update_user_id` ,
  `update_time` ,
  `update_user_name`,
  `is_delete`  ,
  `table_relation_id`,
  `module_id`,
  `plan_id` ,
  `src_table_column_value` )  select `id` ,
  `dest_table_name` ,
  `dest_primary_key_column` ,
  `dest_primary_key_value` ,
  `src_table_name`,
  `src_primary_key_column`,
  `src_primary_key_value` ,
  `create_user_id` ,
  `create_time` ,
  `update_user_id` ,
  `update_time` ,
  `update_user_name`,
  `is_delete`  ,
  `table_relation_id`,
  `module_id`,
  `plan_id` ,
  `src_table_column_value` from  `ry-vue`.`trans_key_value_relation`  where src_table_column_value like '", @likestr,"'" );
	
	
-- SELECT @strinsert;

	 prepare sinsert from @strinsert;
 execute sinsert;
 deallocate prepare sinsert;

COMMIT;
	
    end while;
    -- 关闭游标
    close cur;
	
END

az44yao

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql 存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `test_while_0066`(IN in_count INT)BEGIN DECLARE COUNT INT DEFAULT 1; DECLARE COUNT2 INT DEFAULT 0; DECLARE countnum INT; DROP TABLE if exists youhuitable1; create temporary table if not exists youhuitable1(i.
复制链接

扫一扫