cursor c_all is select s.parentid,c.serviceid,s.ordernum,count(*) from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid group by s.parentid, c.serviceid,s.ordernum; cursor c_year is select s.parentid ,c.serviceid ,'year_'||to_char(clicktime,'yyyy-mm') ,s.ordernum,count(*) from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid and to_char(clicktime,'yyyy-mm')=trunc(to_char(sysdate,'yyyy-mm')) group by to_char(clicktime,'yyyy-mm'),s.parentid, c.serviceid,s.ordernum;
begin delete from t_por_service_list where listtype='all'; commit; open c_all; loop fetch c_all into v_parentid,v_serviceid,v_ordernum,v_sum; exit when (c_all%notfound); --dbms_output.put_line(v_parentid); --dbms_output.put_line(v_serviceid); --dbms_output.put_line(v_ordernum); --dbms_output.put_line(v_sum); insert into t_por_service_list(id,parentserviceid,listtype,serviceid,ordernum,sumcount) values(sys_guid(),v_parentid,'all',v_serviceid,v_ordernum,v_sum); commit; end loop; close c_all;
open c_year ; loop fetch c_year into v_parentid,v_serviceid,v_type,v_ordernum,v_sum; exit when (c_year%notfound); dbms_output.put_line(v_parentid); dbms_output.put_line(v_serviceid); dbms_output.put_line(v_type); dbms_output.put_line(v_ordernum); dbms_output.put_line(v_sum); insert into t_por_service_list(id,parentserviceid,listtype,serviceid,ordernum,sumcount) values(sys_guid(),v_parentid,v_type,v_serviceid,v_ordernum,v_sum); commit; end loop; close c_year;
end; 这上面两个是一样的,分开的话没问题,但是我写在一起都放一个里面 提示这样的错误
end; Error at line 1 ORA-01722: 无效数字 ORA-06512: 在 line 34
declare v_parentid varchar(32); v_serviceid varchar(32); v_type varchar(30); v_ordernum integer; v_sum number(10); cursor c is select s.parentid ,c.serviceid ,'year_'||to_char(clicktime,'yyyy-mm') ,s.ordernum,count(*) from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid and to_char(clicktime,'yyyy-mm')=trunc(to_char(sysdate,'yyyy-mm')) group by to_char(clicktime,'yyyy-mm'),s.parentid, c.serviceid,s.ordernum; begin open c ; loop fetch c into v_parentid,v_serviceid,v_type,v_ordernum,v_sum; exit when (c%notfound); dbms_output.put_line(v_parentid); dbms_output.put_line(v_serviceid); dbms_output.put_line(v_type); dbms_output.put_line(v_ordernum); dbms_output.put_line(v_sum); insert into t_por_service_list(id,parentserviceid,listtype,serviceid,ordernum,sumcount) values(sys_guid(),v_parentid,v_type,v_serviceid,v_ordernum,v_sum); commit; end loop; close c; end;单独执行这个也出问题了 报无效数字 我看看那错了
declare num number; v_parentid varchar(32); v_serviceid varchar(32); v_type varchar(30); v_ordernum integer; v_sum number(10); cursor c is select s.parentid ,c.serviceid ,'year_'||to_char(clicktime,'yyyy-mm') ,s.ordernum,count(*) from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid and to_char(clicktime,'yyyy-mm')=trunc(to_char(sysdate,'yyyy-mm')) group by to_char(clicktime,'yyyy-mm'),s.parentid, c.serviceid,s.ordernum; begin select count(*) into num from t_por_service_list; if num>0 then delete from t_por_service_list where listtype='all'; else open c ; loop fetch c into v_parentid,v_serviceid,v_type,v_ordernum,v_sum; exit when (c%notfound); insert into t_por_service_list(id,parentserviceid,listtype,serviceid,ordernum,sumcount) value(sys_guid(),v_parentid,v_type,v_serviceid,v_ordernum,v_sum); commit; end loop; close c; end if; end;
declare num number; v_parentid varchar(32); v_serviceid varchar(32); v_type varchar(30); v_ordernum integer; v_sum number(10); cursor c is select s.parentid ,c.serviceid ,'year_'||to_char(clicktime,'yyyy-mm') ,s.ordernum,count(*) from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid and to_char(clicktime,'yyyy-mm')=to_char(sysdate,'yyyy-mm') group by to_char(clicktime,'yyyy-mm'),s.parentid, c.serviceid,s.ordernum; begin select count(*) into num from t_por_service_list; if num>0 then delete from t_por_service_list where listtype='all'; else open c ; loop fetch c into v_parentid,v_serviceid,v_type,v_ordernum,v_sum; exit when (c%notfound); insert into t_por_service_list(id,parentserviceid,listtype,serviceid,ordernum,sumcount) value(sys_guid(),v_parentid,v_type,v_serviceid,v_ordernum,v_sum); commit; end loop; close c; end if; end;
你show error 看看什么错误
declare
v_parentid varchar(32);
v_serviceid varchar(32);
v_type varchar(30);
v_ordernum integer;
v_sum number(10);
cursor c_all is
select s.parentid,c.serviceid,s.ordernum,count(*) from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid
group by s.parentid, c.serviceid,s.ordernum;
cursor c_year is
select s.parentid ,c.serviceid ,'year_'||to_char(clicktime,'yyyy-mm') ,s.ordernum,count(*)
from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid and to_char(clicktime,'yyyy-mm')=trunc(to_char(sysdate,'yyyy-mm'))
group by to_char(clicktime,'yyyy-mm'),s.parentid, c.serviceid,s.ordernum;
begin
delete from t_por_service_list where listtype='all';
commit;
open c_all;
loop
fetch c_all into v_parentid,v_serviceid,v_ordernum,v_sum;
exit when (c_all%notfound);
--dbms_output.put_line(v_parentid);
--dbms_output.put_line(v_serviceid);
--dbms_output.put_line(v_ordernum);
--dbms_output.put_line(v_sum);
insert into t_por_service_list(id,parentserviceid,listtype,serviceid,ordernum,sumcount) values(sys_guid(),v_parentid,'all',v_serviceid,v_ordernum,v_sum);
commit;
end loop;
close c_all;
open c_year ;
loop
fetch c_year into v_parentid,v_serviceid,v_type,v_ordernum,v_sum;
exit when (c_year%notfound);
dbms_output.put_line(v_parentid);
dbms_output.put_line(v_serviceid);
dbms_output.put_line(v_type);
dbms_output.put_line(v_ordernum);
dbms_output.put_line(v_sum);
insert into t_por_service_list(id,parentserviceid,listtype,serviceid,ordernum,sumcount) values(sys_guid(),v_parentid,v_type,v_serviceid,v_ordernum,v_sum);
commit;
end loop;
close c_year;
end;
这上面两个是一样的,分开的话没问题,但是我写在一起都放一个里面
提示这样的错误
end;
Error at line 1
ORA-01722: 无效数字
ORA-06512: 在 line 34
declare
v_parentid varchar(32);
v_serviceid varchar(32);
v_type varchar(30);
v_ordernum integer;
v_sum number(10);
cursor c is
select s.parentid ,c.serviceid ,'year_'||to_char(clicktime,'yyyy-mm') ,s.ordernum,count(*)
from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid and to_char(clicktime,'yyyy-mm')=trunc(to_char(sysdate,'yyyy-mm'))
group by to_char(clicktime,'yyyy-mm'),s.parentid, c.serviceid,s.ordernum;
begin
open c ;
loop
fetch c into v_parentid,v_serviceid,v_type,v_ordernum,v_sum;
exit when (c%notfound);
dbms_output.put_line(v_parentid);
dbms_output.put_line(v_serviceid);
dbms_output.put_line(v_type);
dbms_output.put_line(v_ordernum);
dbms_output.put_line(v_sum);
insert into t_por_service_list(id,parentserviceid,listtype,serviceid,ordernum,sumcount) values(sys_guid(),v_parentid,v_type,v_serviceid,v_ordernum,v_sum);
commit;
end loop;
close c;
end;单独执行这个也出问题了
报无效数字
我看看那错了
为什么又是trunc trunc起到什么作用?
你用trunc()就 会报错了!
具体用法自己查查吧
declare
num number;
v_parentid varchar(32);
v_serviceid varchar(32);
v_type varchar(30);
v_ordernum integer;
v_sum number(10);
cursor c is
select s.parentid ,c.serviceid ,'year_'||to_char(clicktime,'yyyy-mm') ,s.ordernum,count(*)
from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid and to_char(clicktime,'yyyy-mm')=trunc(to_char(sysdate,'yyyy-mm'))
group by to_char(clicktime,'yyyy-mm'),s.parentid, c.serviceid,s.ordernum;
begin
select count(*) into num from t_por_service_list;
if num>0 then
delete from t_por_service_list where listtype='all';
else
open c ;
loop
fetch c into v_parentid,v_serviceid,v_type,v_ordernum,v_sum;
exit when (c%notfound);
insert into t_por_service_list(id,parentserviceid,listtype,serviceid,ordernum,sumcount) value(sys_guid(),v_parentid,v_type,v_serviceid,v_ordernum,v_sum);
commit;
end loop;
close c;
end if;
end;
--你的
to_char(clicktime,'yyyy-mm')=trunc(to_char(sysdate,'yyyy-mm'))
修改
to_char(clicktime,'yyyy-mm')=to_char(sysdate,'yyyy-mm')
declare
num number;
v_parentid varchar(32);
v_serviceid varchar(32);
v_type varchar(30);
v_ordernum integer;
v_sum number(10);
cursor c is
select s.parentid ,c.serviceid ,'year_'||to_char(clicktime,'yyyy-mm') ,s.ordernum,count(*)
from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid and to_char(clicktime,'yyyy-mm')=to_char(sysdate,'yyyy-mm')
group by to_char(clicktime,'yyyy-mm'),s.parentid, c.serviceid,s.ordernum;
begin
select count(*) into num from t_por_service_list;
if num>0 then
delete from t_por_service_list where listtype='all';
else
open c ;
loop
fetch c into v_parentid,v_serviceid,v_type,v_ordernum,v_sum;
exit when (c%notfound);
insert into t_por_service_list(id,parentserviceid,listtype,serviceid,ordernum,sumcount) value(sys_guid(),v_parentid,v_type,v_serviceid,v_ordernum,v_sum);
commit;
end loop;
close c;
end if;
end;