oracle时间段分组,如何用connect by level按时间段分组

SQL> create table plsql(code number ,stime date,etime date);

表已创建。

SQL> insert into plsql values (1000 , to_date('08:00','hh24:mi') , to_date('12:0

0','hh24:mi'));

已创建 1 行。

SQL> insert into plsql values (2000 , to_date('14:30','hh24:mi') , to_date('18:3

0','hh24:mi'));

已创建 1 行。

SQL> commit;

SQL> select * from (

2  SELECT code,to_char(stime,'hh24:mi') stime ,to_char((trunc(stime,'mi')+30/

440),'hh24:mi') etime

3  FROM plsql

4  CONNECT BY LEVEL <= 1

5  union all

6  SELECT code,to_char((trunc(stime,'mi')+30/1440),'hh24:mi') stime ,to_char(

trunc(stime,'mi')+60/1440),'hh24:mi') etime

7  FROM plsql

8  CONNECT BY LEVEL <= 1

9  union all

10  SELECT code,to_char((trunc(stime,'mi')+60/1440),'hh24:mi') stime ,to_char(

trunc(stime,'mi')+90/1440),'hh24:mi') etime

11  FROM plsql

12  CONNECT BY LEVEL <= 1

13  union all

14  SELECT code,to_char((trunc(stime,'mi')+90/1440),'hh24:mi') stime ,to_char(

trunc(stime,'mi')+120/1440),'hh24:mi') etime

15  FROM plsql

16  CONNECT BY LEVEL <= 1

17  union all

18  SELECT code,to_char((trunc(stime,'mi')+120/1440),'hh24:mi') stime ,to_char

(trunc(stime,'mi')+150/1440),'hh24:mi') etime

19  FROM plsql

20  CONNECT BY LEVEL <= 1

21  union all

22  SELECT code,to_char((trunc(stime,'mi')+150/1440),'hh24:mi') stime ,to_char

(trunc(stime,'mi')+180/1440),'hh24:mi') etime

23  FROM plsql

24  CONNECT BY LEVEL <= 1

25  union all

26  SELECT code,to_char((trunc(stime,'mi')+180/1440),'hh24:mi') stime ,to_char

(trunc(stime,'mi')+210/1440),'hh24:mi') etime

27  FROM plsql

28  CONNECT BY LEVEL <= 1

29  union all

30  SELECT code,to_char((trunc(stime,'mi')+210/1440),'hh24:mi') stime ,to_char

etime,'hh24:mi') etime

31  FROM plsql

32  CONNECT BY LEVEL <= 1) t order by stime;

CODE STIME ETIME

---------- ----- -----

1000 08:00 08:30

1000 08:30 09:00

1000 09:00 09:30

1000 09:30 10:00

1000 10:00 10:30

1000 10:30 11:00

1000 11:00 11:30

1000 11:30 12:00

2000 14:30 15:00

2000 15:00 15:30

2000 15:30 16:00

CODE STIME ETIME

---------- ----- -----

2000 16:00 16:30

2000 16:30 17:00

2000 17:00 17:30

2000 17:30 18:00

2000 18:00 18:30

已选择16行。

这个时间30分钟间隔可以利用一个变量！