mysql、oracle 构建数据

mysql 构建数据

--创建表
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
CREATE TABLE `vote_records_memory` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(20) NOT NULL DEFAULT '',
`vote_num` int(10) unsigned NOT NULL DEFAULT '0',
`group_id` int(10) unsigned NOT NULL DEFAULT '0',
`status` tinyint(2) unsigned NOT NULL DEFAULT '1',
`create_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

--生成函数
set global log_bin_trust_function_creators=1;
DELIMITER //
DROP FUNCTION IF EXISTS `rand_strings` //
SET NAMES utf8 //
CREATE FUNCTION `rand_strings` (n INT) RETURNS VARCHAR(255) CHARSET 'utf8'
BEGIN 
DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str varchar(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
SET i = i+1;
END WHILE;
RETURN return_str;
END //

--存储过程导入数据
DELIMITER $$
CREATE PROCEDURE `insert_vote_records_memory`(IN n INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE vote_num INT DEFAULT 0;
DECLARE group_id INT DEFAULT 0;
DECLARE status TINYINT DEFAULT 1;
WHILE i < n DO
SET vote_num = FLOOR(1 + RAND() * 10000);
SET group_id = FLOOR(0 + RAND()*3);
SET status = FLOOR(1 + RAND()*2);
INSERT INTO `vote_records_memory` VALUES (NULL, rand_strings(20), vote_num, group_id, status, NOW());
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;

--插入数据
CALL insert_vote_records_memory(100);

==========================================================

--oracle构建数据

create table cux_demo (a number,b number,c number); 

begin 
  for i in 1 .. 15 loop 
    insert into cux_demo 
    values 
      (round(dbms_random.value, 2) * 100, 
       round(dbms_random.value, 2) * 100, 
       round(dbms_random.value, 2) * 100); 
  end loop; 
  commit; 
end
/