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

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
/