为每一行添加一个随机uuid

#删除已有的存储过程
DROP PROCEDURE IF EXISTS update_user_info;

– 定义更新数据表存储过程
CREATE PROCEDURE update_user_info()

BEGIN
   -- 定义存储过程变量
   DECLARE ids BIGINT(20);
   DECLARE stopCur INT DEFAULT 0;
   -- 定义游标(更新指定部分数据)
   DECLARE cur CURSOR FOR (SELECT id FROM cert_app ORDER BY id);
   -- 定义游标结束,当遍历完成时，将stopCur设置为null ,也可以写成 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stopCur = null;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopCur = null;
   
   -- 开游标 
   OPEN cur;
   -- 游标向下走一步，将查询出来的两个值赋给定义的两个变量
   FETCH cur INTO ids;
      -- 循环体
      WHILE( stopCur IS NOT NULL) DO
      -- 更新对应关系表数据
      UPDATE cert_app SET uuid=replace(uuid(), '-', '') WHERE id = ids;
      -- 游标向下走一步
      FETCH cur INTO ids;
      
      END WHILE;
   -- 关闭游标
   CLOSE cur;

END 

执行存储过程

CALL update_user_info;

删除 已有的 存储过程

DROP PROCEDURE update_user_info;

批量插入测试数据

创建存储过程

create or replace procedure PRO_FOR_DATA
IS
   BEGIN

    for sts in 1 .. 10000
    loop
         INSERT INTO "BK_DEV"."TB_APP"("ID", "AID", "NAME", "TYPE_ID", "DEVELOPER", "SUPPELIER", "CREATE_AT", "USER_ID", "UPDATE_AT", "APPLY_STATE", "ERR_MSG", "CERT_SN", "PACKET", "DOMAIN", "LANGUAGES", "DEPLOY_ID", "SERVICE_ID", "PERSON_ID", "CERT_IDENTITY") VALUES (SEQUENCE_TB_APP.nextval, 'APP202112279747643353', '飞猪9999', '1', '博雅中科有限公司', NULL, '1640589784252', '4', '1640589784252', '1', NULL, '7f53d0faba378afe', lower(sys_guid()), 'www.feizhu1.com', '汉语', '67', '001', '66', '博雅中科有限公司@0099');
    end loop;
 END;

执行存储过程

CALL PRO_FOR_DATA();