存储过程插入数据

最新推荐文章于 2024-03-08 16:54:41 发布

weixin_42346558

最新推荐文章于 2024-03-08 16:54:41 发布

阅读量182

点赞数

文章标签： java 数据库 sql

本文链接：https://blog.csdn.net/weixin_42346558/article/details/125183140

版权

对员工插入打卡记录

CREATE PROCEDURE `createsignrecord`()
begin
	declare i int default 1;
	-- 声明日期变量
	declare dateVar varchar(16) default '2022-05';
	-- 定义局部变量
 	declare employeevar int(11);
 	-- 声明游标
 	declare my_cursor cursor for
        select employee_id from employee
    DECLARE EXIT HANDLER FOR NOT found CLOSE my_cursor;
	open my_cursor;
  	-- 通过游标获取每一行数据
  	label:loop
        fetch my_cursor into employeevar;
       	set i=1;
         WHILE i <= 31 DO
         	if i < 10 
         	then INSERT INTO employee_sign_record
( employee_id, sign_time, sign_site, sign_type, sign_wifi, sign_blue_tooth, sign_device, amend_id, is_delete, create_by, create_date, last_update_by, last_update_date, location_id, data_source)
VALUES( employeevar,concat(dateVar,"-0",i," 8:00:00"), NULL, '7', NULL, NULL, 'CJDG215260296', NULL, 'N', 1, '2022-02-23 18:15:00.0', 1, '2022-02-23 18:15:00.0', NULL, 'FACE');
INSERT INTO corehr_sit.t_att_sign_record
( employee_id, sign_time, sign_site, sign_type, sign_wifi, sign_blue_tooth, sign_device, amend_id, is_delete, create_by, create_date, last_update_by, last_update_date, location_id, data_source)
VALUES( employeevar,concat(dateVar,"-0",i," 19:00:00"), NULL, '7', NULL, NULL, 'CJDG215260296', NULL, 'N', 1, '2022-02-23 18:15:00.0', 1, '2022-02-23 18:15:00.0', NULL, 'FACE');
         	else 
         	INSERT INTO employee_sign_record
( employee_id, sign_time, sign_site, sign_type, sign_wifi, sign_blue_tooth, sign_device, amend_id, is_delete, create_by, create_date, last_update_by, last_update_date, location_id, data_source)
VALUES( employeevar,concat(dateVar,"-",i," 8:00:00"), NULL, '7', NULL, NULL, 'CJDG215260296', NULL, 'N', 1, '2022-02-23 18:15:00.0', 1, '2022-02-23 18:15:00.0', NULL, 'FACE');
INSERT INTO corehr_sit.t_att_sign_record
( employee_id, sign_time, sign_site, sign_type, sign_wifi, sign_blue_tooth, sign_device, amend_id, is_delete, create_by, create_date, last_update_by, last_update_date, location_id, data_source)
VALUES( employeevar,concat(dateVar,"-",i," 19:00:00"), NULL, '7', NULL, NULL, 'CJDG215260296', NULL, 'N', 1, '2022-02-23 18:15:00.0', 1, '2022-02-23 18:15:00.0', NULL, 'FACE');
         	end if;
         	set i=i+1;
         END WHilE;
        
    end loop label;
    -- 关闭游标
	close my_cursor;
end

创建用户

CREATE PROCEDURE `createuser`(in start int , in end int, in random int )
begin
     WHILE start <= end DO
		insert into user values (start, concat("test-",start), RAND() * random);
     	set start=start+1;
     END WHilE;
end
call createsignrecord(1, 10000, 30)
truncate table t_sys_user

创建角色

CREATE PROCEDURE `createRole`(in rstart int , in rend int, in uend int  )
begin
	declare i int default 1;
  	-- 通过游标获取每一行数据
     WHILE rstart <= rend DO
		insert into role values (rstart, concat("test-",rstart), RAND() * uend);
	    set i=i+1;
     	set rstart=rstart+1;
     END WHilE;
end
call createRole(1, 2000, 10000)
truncate table t_sys_role