Kingbase创建随机数据的方法

环境准备

创建随机字符串函数

create or replace function random_string(integer)
returns text as
$body$
         select array_to_string(array(select substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' FROM (ceil(random()*62))::int FOR 1) FROM generate_series(1, $1)), '');
$body$
language sql volatile; 

创建GUID环境

create extension "uuid-ossp";

建表语句

db_lic

CREATE TABLE db_lic (
id varchar(36) NOT NULL ,
ct_id varchar(36) NOT NULL ,
data_id varchar(36) NOT NULL ,
ct_name varchar(50) NOT NULL ,
lc_code varchar(100) NOT NULL ,
lc_number varchar(100) DEFAULT '',
lc_begin_time date DEFAULT NULL ,
lc_end_time date DEFAULT NULL ,
lc_status char(1) DEFAULT '' ,
iu_org_name varchar(256) DEFAULT '',
iu_org_code varchar(256) DEFAULT '',
iu_time date DEFAULT NULL ,
make_time varchar(100) DEFAULT NULL,
make_by varchar(100) DEFAULT '' ,
app_id varchar(50) DEFAULT NULL,
date_source varchar(64) DEFAULT NULL,
del_flag char(1) DEFAULT '' ,
source_flag char(1) DEFAULT '' ,
sync_flag char(1) DEFAULT '' ,
stats_flag char(1) DEFAULT '0' ,
oper_flag char(1) DEFAULT '' ,
hder_type varchar(100) NOT NULL,
hder_name varchar(100) DEFAULT '',
hder_code varchar(100) NOT NULL ,
hder_level char(1) NOT NULL ,
hder_order int NOT NULL ,
hder_status char(1) NOT NULL ,
file_name varchar(100) NOT NULL ,
file_path varchar(100) NOT NULL ,
file_type char(1) NOT NULL ,
file_size int DEFAULT NULL ,
file_hash varchar(128) DEFAULT '',
file_status char(1) NOT NULL ,
file_disk_id varchar(36) NOT NULL,
create_by varchar(64) DEFAULT '',
create_time date DEFAULT NULL ,
update_by varchar(64) DEFAULT '',
update_time date DEFAULT NULL ,
remark varchar(500) DEFAULT ''
);

data_jybgjswp

CREATE TABLE data_jybgjswp (
ID varchar(36) NOT NULL ,
SUNITNAME varchar(100) DEFAULT random_string(10) ,
SNAME varchar(100) DEFAULT random_string(10) ,
SPID varchar(100) DEFAULT random_string(10) ,
SLAO varchar(100) DEFAULT random_string(10) ,
SSAR varchar(5) DEFAULT random_string(5) ,
SSNTH varchar(3) DEFAULT random_string(3) ,
SSDAY varchar(3) DEFAULT random_string(3) ,
SSOUNTRY varchar(100) DEFAULT random_string(10) ,
SSCHOOL varchar(100) DEFAULT random_string(10) ,
SMNEY varchar(10) DEFAULT random_string(10) ,
SYAR varchar(5) DEFAULT random_string(5) ,
SMOTH varchar(3) DEFAULT random_string(3) ,
SDAY varchar(3) DEFAULT random_string(3) ,
SDEDDR varchar(100) DEFAULT random_string(10),
STEAME varchar(50) DEFAULT random_string(10) ,
STRADDR varchar(100) DEFAULT random_string(10) ,
STERPHONE varchar(20) DEFAULT random_string(10) ,
STHERFAX varchar(20) DEFAULT random_string(10) ,
SMYADDRESS varchar(100) DEFAULT random_string(10) ,
SMYPHONE varchar(20) DEFAULT random_string(10) ,
SMYFAX varchar(20) DEFAULT random_string(10) ,
SMYEMAIL varchar(50) DEFAULT random_string(10) ,
QRCODE varchar(100) DEFAULT random_string(10) ,
CATCDE varchar(100) DEFAULT random_string(10) ,
CERTCDE varchar(100) DEFAULT random_string(10) ,
MAKRT varchar(100) DEFAULT random_string(10) ,
MAKDAY varchar(100) DEFAULT random_string(10) ,
HDERTYPE varchar(100) DEFAULT random_string(10) ,
HDERCODE varchar(100) DEFAULT random_string(10) ,
HDERNAME varchar(100) DEFAULT random_string(10) ,
VTARTDAY date DEFAULT timestamp '2010-06-10 20:00:00' +
random() * (timestamp '2020-07-17 20:00:00' -
timestamp '2010-05-05 10:00:00') ,
VENDDAY date DEFAULT timestamp '2010-06-10 20:00:00' +
random() * (timestamp '2020-07-17 20:00:00' -
timestamp '2010-05-05 10:00:00') ,
CTTITLE varchar(100) DEFAULT random_string(10) ,
XTID varchar(100) DEFAULT random_string(10) ,
STCKET varchar(50) DEFAULT random_string(10) ,
LIC_CODE varchar(100) DEFAULT random_string(10) ,
SIGNERT varchar(100) DEFAULT random_string(10) ,
USETT varchar(50) DEFAULT random_string(10) ,
EXP_TIME date DEFAULT timestamp '2010-06-10 20:00:00' +
random() * (timestamp '2020-07-17 20:00:00' -
timestamp '2010-05-05 10:00:00') ,
PRO_STAS int DEFAULT NULL ,
SYN_STAS int DEFAULT NULL ,
FIL_PATH varchar(100) DEFAULT random_string(10) ,
DAT_SOURCE int DEFAULT NULL ,
CREA_USER_ID varchar(36) DEFAULT random_string(10) ,
CREA_TIME timestamp NULL DEFAULT timestamp '2010-06-10 20:00:00' +
random() * (timestamp '2020-07-17 20:00:00' -
timestamp '2010-05-05 10:00:00') ,
MOD_USER_ID varchar(36) DEFAULT random_string(10) ,
MOD_TIME timestamp NULL DEFAULT timestamp '2010-06-10 20:00:00' +
random() * (timestamp '2020-07-17 20:00:00' -
timestamp '2010-05-05 10:00:00')
);

数据插入:

db_lic

insert into db_lic values(
generate_series(1,100000),
random_string(10),
uuid_generate_v4(),
random_string(10),
random_string(10),
random_string(10),
(select timestamp '2010-06-10 20:00:00' +
random() * (timestamp '2020-07-17 20:00:00' -
timestamp '2010-05-05 10:00:00')),
(select timestamp '2010-06-10 20:00:00' +
random() * (timestamp '2020-07-17 20:00:00' -
timestamp '2010-05-05 10:00:00')),
random_string(1),
random_string(10),
random_string(10),
(select timestamp '2010-06-10 20:00:00' +
random() * (timestamp '2020-07-17 20:00:00' -
timestamp '2010-05-05 10:00:00')),
random_string(10),
random_string(10),
random_string(10),
random_string(10),
random_string(1),
random_string(1),
random_string(1),
random_string(1),
random_string(1),
random_string(10),
random_string(10),
random_string(10),
random_string(1),
ceil(random()**(10000 -1)+1),
random_string(1),
random_string(10),
random_string(10),
random_string(1),
ceil(random()**(10000 -1)+1),
random_string(10),
random_string(1),
random_string(10),
random_string(10),
(select timestamp '2010-06-10 20:00:00' +
random() * (timestamp '2020-07-17 20:00:00' -
timestamp '2010-05-05 10:00:00')),
random_string(10),
(select timestamp '2010-06-10 20:00:00' +
random() * (timestamp '2020-07-17 20:00:00' -
timestamp '2010-05-05 10:00:00')),
random_string(10));

data_jybgjswp

insert into data_jybgjswp(id) select data_id from db_lic  limit 10;