前言,面试的时候有人让我写一个向sql server数据库中循环插入1000条记录。因为我平时接触oracle比较多。就用oracle语句写了。结果面试官看了下说。oracle我也不懂。大体应该差不多。现在我把oracle和sql的循环插入语句都写出来。以免以后又忘记。
(1)sql下代码:
- //声明变量;
- DECLARE @i int
- DECLARE @name varchar(10)
- DECLARE @password varchar(10)
- Set @i = 1000
- //开始循环插入数据;
- WHILE @i < 1000
- BEGIN
- Set @i =@i +1
- SET @name = RIGHT('00000' + CAST(@i AS varchar(10)),5)
- set @password = @name
- insert into admin values(@name,@password)
- END
(2)oracle下代码:
- (1)先理论
- declare
- i number;
- begin
- i := 1;
- while i<=100 loop
- dbms_output.put_line(i);
- i := i+1;
- end loop;
- end;
- (2)实践
- declare
- year varchar2(4):='2008';
- month number(2):=1;
- schoolid number(10):=14640;
- begin
- loop
- loop
- insert into xx_jb1
- (ID,
- XXID,
- GLBMJY,
- XXXZGB,
- XXLS,
- HJXX,
- HJXS,
- LX_TZH_XX,
- LX_TZH_XS,
- BGNF,
- BGYF,
- STATE,
- SSRQ,
- TJRQ,
- TBR)
- values
- (seq_xx_jb1.nextval,
- schoolid,
- 1,
- 1,
- 2,
- 1,
- 248,
- 1,
- 248,
- year,
- month,
- '已通过',
- sysdate,
- sysdate,
- '管理员');
- month := month+1;
- exit when month>12;
- end loop;
- month := month-12;
- schoolid := schoolid+1;
- exit when schoolid>15040;
- end loop;
- end;