mysql存储过程

最新推荐文章于 2024-04-15 10:15:00 发布

狼的诱惑_LE

最新推荐文章于 2024-04-15 10:15:00 发布

阅读量120

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_40720919/article/details/108388006

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

分100张表执行

#drop PROCEDURE idata;

delimiter ;;
create procedure iData()
begin
    declare i int;
    declare s varchar(2);
    set i = 0;
    set s = '';
    while (i <= 100)
        do
            set s = LPAD(i, 2, 0);
            ##每个表新增指定的num记录数
            set @sqlStr = concat('update user_card_0', s,
                                 ' set activity_limit_type=''10'',activity_id=''T13234612'' WHERE sale_order in (''00020200427001000075'',''XSD20200116001000352''); ');
            PREPARE stmt from @sqlStr;
            EXECUTE stmt;
            ##每个表新增指定的num记录数
            set @sqlStr2 = concat('update user_card_0', s,
                                 ' set activity_limit_type=''10'',activity_id=''T13234613'' WHERE sale_order in (''00020200708001000092''); ');
            PREPARE stmt from @sqlStr2;
            EXECUTE stmt;
            set i = i + 1;
        end while;
end;;
delimiter ;
call iData();

查询

drop PROCEDURE idata1;
delimiter ;;
create procedure iData1(OUT data_t VARCHAR(100))
begin
    declare i int;
    declare s varchar(2);
    set i = 0;
    set s = '';
    while (i <= 100)
        do
            set s = LPAD(i, 2, 0);
            ##每个表新增指定的num记录数
            set @sqlStr = concat(
                    'select activity_id,user_id,sale_order into @card_no_result, @user_id_result,@sale_order_result FROM user_card_0',
                    s,
                    ' WHERE sale_order in (''00020200427001000075'',''XSD20200116001000352'',''00020200708001000092'') limit 1; ');
            PREPARE stmt from @sqlStr;
            EXECUTE stmt;
            IF @card_no_result is not null THEN
                select @card_no_result, @user_id_result, @sale_order_result;
            END IF;

            SET data_t := @card_no_result;
            set i = i + 1;
        end while;
end;;
delimiter ;
call iData1(@data_t);
SELECT @data_t;