怎么用sql语句向数据库表中添加循环数据

怎么用sql语句向数据库表中添加循环数据

可以使用循环语句

declare

@i

int

set

@i=1

while

@i<30

begin

insert

into

test

(userid)

values(@i)

set

@i=@i+1

end

sql语句遍历一个用户表 涉及到循环 *** 作 ，添加纪录就是简单的insert *** 作

不同的数据库的稍有不同。

还要处理数据库表主键的不同设置情况。

循环 *** 作+添加 *** 作的案例：（oracle）

1：主键采用自加方式实现

delete from whilestu1

commit

DECLARE

num1 number

maxstuid number

age number

begin

num1 := 1

WHILE num1 <= 100 LOOP

--获取最大的stuid

select max(stuid) + 1 into maxstuid from whilestu1

--dbms_output.put_line(maxstuid)

if maxstuid is null then

maxstuid := 1

--dbms_output.put_line('r')

end if

age := ROUND(DBMS_RANDOM.VALUE(18, 40), 0)

--插入数据

insert into whilestu1

(stuid, stuName, age)

values

(maxstuid, '学员' || cast(maxstuid as varchar2(50)), age)

commit

num1 := num1 + 1

END LOOP

end

/

2：使用触发器声称主键的方式

CREATE OR REPLACE TRIGGER trg_whilestu2

BEFORE INSERT OR UPDATE OF stuid

ON whilestu2

FOR EACH ROW

BEGIN

IF INSERTING THEN

SELECT SEQ_whilestu2.NEXTVAL INTO :NEW.stuid FROM DUAL

ELSE

RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！')

END IF

END

/

delete from whilestu2

select * from whilestu2

commit

--select SEQ_whilestu2.Nextval from dual

DECLARE

num1 number

maxstuid number

age number

begin

num1 := 1

WHILE num1 <= 100 LOOP

age := ROUND(DBMS_RANDOM.VALUE(18, 40), 0)

select SEQ_whilestu2.Currval + 1 into maxstuid from dual

--插入数据

insert into whilestu2

(stuName, age)

values

('学员' || cast(maxstuid as varchar2(50)), age)

commit

num1 := num1 + 1

END LOOP

end

/

3：使用GUID生成主键的方式

select sys_guid() from dual

DECLARE

num1 number

--maxstuid number

age number

stuid raw(16)

begin

num1 := 1

WHILE num1 <= 100 LOOP

age := ROUND(DBMS_RANDOM.VALUE(18, 40), 0)

select sys_guid() into stuid from dual

--插入数据

insert into whilestu3

(stuid, stuName, age)

values

(stuid, '学员' || cast(num1 as varchar2(50)), age)

commit

num1 := num1 + 1

END LOOP

end

/

---