create table tb(eirCate varchar2(10),employeeid varchar2(100), sendTime varchar2(10) , count int) ;
insert into tb values( '氧气' , '松昌', '2009-06-01' , 74);
insert into tb values( '氧气' , '松昌', '2009-06-09' , 94);
insert into tb values( '氮气' , '常态', '2009-06-03' , 93);
insert into tb values( '氧气' , '常态', '2009-06-05' , 94);
insert into tb values( '氮气' , '常态', '2009-06-04' , 74);
insert into tb values( '氧气' , '福尔卓', '2009-06-02' , 83);
insert into tb values( '氧气' , '福尔卓', '2009-06-06' , 94);
insert into tb values( '氮气' , '福尔卓', '2009-06-05' , 84);
create procedure protest
as
declare
impl varchar(4000) := 'select employeeid';
v_time tb.sendTime%type;
cursor v_cur is select distinct sendTime from tb;
begin
open v_cur;
fetch v_cur into v_time;
while v_cur %found loop
impl:=impl || ', max(case sendTime when '''||v_time||''' then count else 0 end) '||v_Time ;
fetch v_cur into v_time;
end loop;
close v_cur;
impl := impl || 'from tb group by employeeid';
end; call protest
这个存储过程怎么执行不了?过程创建、执行都没问题。可执行调用后失败?
如何把这个表查询后的结果如下结构
企业 2009-06-01 2009-06-02 2009-06-03 2009-06-04 2009-06-05 2009-06-06 2009-06-08 2009-06-09 平均数 总数
常态 0 0 93 74 94 0 0 0 87.00 261
富尔卓 0 83 0 0 84 94 94 0 88.75 355
松昌 74 0 0 0 0 0 0 94 84.00 168
insert into tb values( '氧气' , '松昌', '2009-06-01' , 74);
insert into tb values( '氧气' , '松昌', '2009-06-09' , 94);
insert into tb values( '氮气' , '常态', '2009-06-03' , 93);
insert into tb values( '氧气' , '常态', '2009-06-05' , 94);
insert into tb values( '氮气' , '常态', '2009-06-04' , 74);
insert into tb values( '氧气' , '福尔卓', '2009-06-02' , 83);
insert into tb values( '氧气' , '福尔卓', '2009-06-06' , 94);
insert into tb values( '氮气' , '福尔卓', '2009-06-05' , 84);
create procedure protest
as
declare
impl varchar(4000) := 'select employeeid';
v_time tb.sendTime%type;
cursor v_cur is select distinct sendTime from tb;
begin
open v_cur;
fetch v_cur into v_time;
while v_cur %found loop
impl:=impl || ', max(case sendTime when '''||v_time||''' then count else 0 end) '||v_Time ;
fetch v_cur into v_time;
end loop;
close v_cur;
impl := impl || 'from tb group by employeeid';
end; call protest
这个存储过程怎么执行不了?过程创建、执行都没问题。可执行调用后失败?
如何把这个表查询后的结果如下结构
企业 2009-06-01 2009-06-02 2009-06-03 2009-06-04 2009-06-05 2009-06-06 2009-06-08 2009-06-09 平均数 总数
常态 0 0 93 74 94 0 0 0 87.00 261
富尔卓 0 83 0 0 84 94 94 0 88.75 355
松昌 74 0 0 0 0 0 0 94 84.00 168
create procedure protest
as
impl varchar2(4000) := 'select employeeid';
v_time tb.sendTime%type;
cursor v_cur is select distinct sendTime from tb order by sendtime;
begin
open v_cur;
fetch v_cur into v_time;
while v_cur %found loop
impl:=impl || ', max(case sendTime when '''||v_time||''' then count else 0 end) '||v_Time ;
fetch v_cur into v_time;
end loop;
close v_cur;
impl := impl || ' from tb group by employeeid';
end;
/
declare
begin
protest;
end;为什么看不到结果集?
create procedure protest
as
impl varchar2(4000) := 'create or replace view aaa as select employeeid';
v_time tb.sendTime%type;
cursor v_cur is select distinct sendTime from tb order by sendtime;
begin
open v_cur;
fetch v_cur into v_time;
while v_cur %found loop
impl:=impl || ', max(case sendTime when '''||v_time||''' then count else 0 end) '||v_Time ;
fetch v_cur into v_time;
end loop;
close v_cur;
impl := impl || ' from tb group by employeeid';
end;
/select * from aaa;
as
impl varchar2(4000) := 'create or replace view aaa as select employeeid';
v_time tb.sendTime%type;
cursor v_cur is select distinct sendTime from tb order by sendtime;
begin
open v_cur;
fetch v_cur into v_time;
while v_cur %found loop
impl:=impl || ', max(case sendTime when '''||v_time||''' then count else 0 end) '||v_Time ;
fetch v_cur into v_time;
end loop;
close v_cur;
impl := impl || ' from tb group by employeeid';
execute immediate impl;
end;
select * from aaa;
加了执行不了。视图没有创建。?
dbms_output.putline(impl);
看看语句对不对
对的话,拷贝出来执行一下,看看能不能创建成功
执行不了。算了。我换了个方法。颇有见效。
create or replace view
v_day(eircate,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
select
eircate,
sum(decode(substrb(sendTime,6,2),'01',count,0)),
sum(decode(substrb(sendTime,6,2),'02',count,0)),
sum(decode(substrb(sendTime,6,2),'03',count,0)),
sum(decode(substrb(sendTime,6,2),'04',count,0)),
sum(decode(substrb(sendTime,6,2),'05',count,0)),
sum(decode(substrb(sendTime,6,2),'06',count,0)),
sum(decode(substrb(sendTime,6,2),'07',count,0)),
sum(decode(substrb(sendTime,6,2),'08',count,0)),
sum(decode(substrb(sendTime,6,2),'09',count,0)),
sum(decode(substrb(sendTime,6,2),'10',count,0)),
sum(decode(substrb(sendTime,6,2),'11',count,0)),
sum(decode(substrb(sendTime,6,2),'12',count,0))
from tb
group by eirCate
这个是月统计。日统计道理一样。
还是感谢superhsj