当前位置: 代码迷 >> Oracle开发 >> PL/SQL有关问题,大侠帮帮忙!
  详细解决方案

PL/SQL有关问题,大侠帮帮忙!

热度:69   发布时间:2016-04-24 07:36:50.0
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;
  相关解决方案