CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO
建表脚本:
1--
删除用户表
2 DROP TABLE T_PORTAL_USER;
3
4 -- 创建用户表
5 CREATE TABLE T_PORTAL_USER
6(
7 id NUMBER PRIMARY KEY, -- 用户表示
8 username VARCHAR2( 24) NOT NULL, -- 用户名
9 password VARCHAR2( 24) NOT NULL, -- 密码
10 realname VARCHAR2( 24) NOT NULL, -- 真实姓名
11 sex VARCHAR2( 2) DEFAULT ' 0 ', -- 性别 "0":Male "1":Female
12 registerDate TIMESTAMP NOT NULL, -- 注册日期
13 state VARCHAR2( 2) NOT NULL -- 账号状态 "0":启用 "1":注销
14 )
15
16 -- 删除用户表序列
17 DROP SEQUENCE SEQ_T_PORTAL_USER;
18
19 -- 创建用户表序列
20 CREATE SEQUENCE SEQ_T_PORTAL_USER
21START WITH 1
22INCREMENT BY 1
23NOMAXVALUE
24CACHE 20
2 DROP TABLE T_PORTAL_USER;
3
4 -- 创建用户表
5 CREATE TABLE T_PORTAL_USER
6(
7 id NUMBER PRIMARY KEY, -- 用户表示
8 username VARCHAR2( 24) NOT NULL, -- 用户名
9 password VARCHAR2( 24) NOT NULL, -- 密码
10 realname VARCHAR2( 24) NOT NULL, -- 真实姓名
11 sex VARCHAR2( 2) DEFAULT ' 0 ', -- 性别 "0":Male "1":Female
12 registerDate TIMESTAMP NOT NULL, -- 注册日期
13 state VARCHAR2( 2) NOT NULL -- 账号状态 "0":启用 "1":注销
14 )
15
16 -- 删除用户表序列
17 DROP SEQUENCE SEQ_T_PORTAL_USER;
18
19 -- 创建用户表序列
20 CREATE SEQUENCE SEQ_T_PORTAL_USER
21START WITH 1
22INCREMENT BY 1
23NOMAXVALUE
24CACHE 20
-- 批量新增一万条用户
1
--
批量新增一万条用户
2 CREATE OR REPLACE PROCEDURE PROC_USER_CREATE_BAT
3 AS
4 startTime VARCHAR2( 32); -- 开始时间
5 endTime VARCHAR2( 32); -- 结束时间
6 BEGIN
7 SELECT TO_CHAR(SYSDATE, ' yyyy-mm-dd hh24:mi:ss:ff ') INTO startTime FROM DUAL;
8 DBMS_OUTPUT.PUT_LINE( ' Start Time: ' ||startTime);
9 FOR i in 1.. 10000 LOOP
10 INSERT INTO T_PORTAL_USER VALUES(SEQ_T_PORTAL_USER.NEXTVAL, ' owen ' ||i, ' 123456 ', ' gekunjin ' ||i, ' 0 ',sysdate, ' 0 ');
11 END LOOP;
12 SELECT TO_CHAR(SYSDATE, ' yyyy-mm-dd hh24:mi:ss:ff ') INTO endTime FROM DUAL;
13 DBMS_OUTPUT.PUT_LINE( ' End Time: ' ||endTime);
14 END PROC_USER_CREATE_BAT;
2 CREATE OR REPLACE PROCEDURE PROC_USER_CREATE_BAT
3 AS
4 startTime VARCHAR2( 32); -- 开始时间
5 endTime VARCHAR2( 32); -- 结束时间
6 BEGIN
7 SELECT TO_CHAR(SYSDATE, ' yyyy-mm-dd hh24:mi:ss:ff ') INTO startTime FROM DUAL;
8 DBMS_OUTPUT.PUT_LINE( ' Start Time: ' ||startTime);
9 FOR i in 1.. 10000 LOOP
10 INSERT INTO T_PORTAL_USER VALUES(SEQ_T_PORTAL_USER.NEXTVAL, ' owen ' ||i, ' 123456 ', ' gekunjin ' ||i, ' 0 ',sysdate, ' 0 ');
11 END LOOP;
12 SELECT TO_CHAR(SYSDATE, ' yyyy-mm-dd hh24:mi:ss:ff ') INTO endTime FROM DUAL;
13 DBMS_OUTPUT.PUT_LINE( ' End Time: ' ||endTime);
14 END PROC_USER_CREATE_BAT;