table 1
id name time
1 david 2010-11-11 23:23
2 david 2010-11-11 01:12
3 david 2010-11-11 01:12
4 david 2010-11-11 01:12
5 david 2010-11-11 01:12采集数据表
id type num
1 all_2010 5
我现在要写个存储过程
执行的过程是把上面table1的数据count(*) 采集之后放到采集表里
怎么写存储过程
id name time
1 david 2010-11-11 23:23
2 david 2010-11-11 01:12
3 david 2010-11-11 01:12
4 david 2010-11-11 01:12
5 david 2010-11-11 01:12采集数据表
id type num
1 all_2010 5
我现在要写个存储过程
执行的过程是把上面table1的数据count(*) 采集之后放到采集表里
怎么写存储过程
如果是的话,不用存储过程,就用个sql就可以
insert into 采集数据表
(id, type, num)
select rownum, 'all_' || substr(time, 1, 4), count(1) from table1 group by substr(time, 1, 4)
id name time
1 david 2010-11-11 23:23
2 david 2010-11-11 01:12
3 david 2010-11-11 01:12
4 david 2010-11-11 01:12
5 david 2010-11-11 01:12采集数据表
id type num
1 all_2010 5
--直接sql
insert into 采集数据表
select row_num() over(order by 1) id,'all_'||to_char(time,'yyyy') type,count(*) num from tb1 group by to_char(time,'yyyy')
create or replace procedure p_upp
as
begin
insert into 采集数据表
select row_number() over(order by 1) id,'all_'||to_char(time,'yyyy') type,count(*) num from tb1 group by to_char(time,'yyyy');
commit;
end;
row_number() over(order by 1) 都是什么意思吗?