日期:2014-05-17  浏览次数:20683 次

PL/SQL问题,大侠帮帮忙!急急急!
帮我写个PL/SQL块,也就是说把下面这句话循环执行1500次。数据是 
1F001 
1F002 
这样加的 
INSERT INTO room(num) VALUES ('1F001'); 
效果如下 
1F001 
1F002 
1F003 
1F004 
1F005 
…… 
…… 
1F1500

------解决方案--------------------
SQL code

DECLARE
  I INTEGER;
BEGIN
  FOR I IN 997 .. 1002 LOOP
    IF LENGTH(I) < 3 THEN
      INSERT INTO ROOM (NUM) VALUES ('1F' || LPAD(I, 3, '0'));
    ELSE
      INSERT INTO ROOM (NUM) VALUES ('1F' || TO_CHAR(I));
    END IF;
  END LOOP;
END;
/

------解决方案--------------------
SQL code
INSERT INTO room
  (num)
  select '1F'||reverse(substr(reverse('000' || rownum), 1, 4)) as num
    from system.help t1, system.help t2
   where rownum <= 1500

------解决方案--------------------
DECLARE
I INTEGER:=1;
BEGIN
LOOP
INSERT INTO ROOM (NUM) VALUES ('1F' || LPAD(I, decode(length(to_char(i)),4,4,3), '0'));
exit when i=1500;
i:=i+1
END LOOP;
END;