declare
v_type varchar(30);
v_num number(10);
cursor c is
select 'all_'||trunc(to_char(clicktime,'yyyy')) into v_type,count(*) into v_num from t_por_service_clicklog group by trunc(to_char(clicktime,'yyyy'));
begin
open c;
loop
exit when (c%notfound);
dbms_output.put_line(v_type);
dbms_output.put_line(v_num);
end loop;
close c;
exit;
v_type varchar(30);
v_num number(10);
cursor c is
select 'all_'||trunc(to_char(clicktime,'yyyy')) into v_type,count(*) into v_num from t_por_service_clicklog group by trunc(to_char(clicktime,'yyyy'));
begin
open c;
loop
exit when (c%notfound);
dbms_output.put_line(v_type);
dbms_output.put_line(v_num);
end loop;
close c;
exit;
ORA-06550: 第 6 行, 第 35 列:
PL/SQL: ORA-00936: 缺失表达式
ORA-06550: 第 6 行, 第 5 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 19 行, 第 6 列:
PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind vari
v_type varchar(30);
v_num number(10);
cursor c is
select 'all_'||trunc(to_char(clicktime,'yyyy')) ,count(*) into v_type ,v_num from t_por_service_clicklog group by trunc(to_char(clicktime,'yyyy'));
begin
open c;
loop
exit when (c%notfound);
dbms_output.put_line(v_type);
dbms_output.put_line(v_num);
end loop;
close c;
end;
Error at line 1
ORA-06550: 第 18 行, 第 6 列:
PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipeScript Terminated on line 1.
--这样:
declare
v_type varchar(30);
v_num number(10);
cursor c is
select 'all_'||trunc(to_char(clicktime,'yyyy')) ,count(*) from t_por_service_clicklog group by trunc(to_char(clicktime,'yyyy'));
begin
open c;
loop
fetch c into v_type,v_num; --fetch数据
exit when c%notfound;
dbms_output.put_line(v_type);
dbms_output.put_line(v_num);
end loop;
close c;
end;
谢了我的QQ21204101
v_parentid varchar(30);
v_serviceid varchar(30);
v_type varchar(30);
v_ordernum integer;
v_num number(10);
cursor c is
select s.parentid ,c.serviceid ,'all_'||to_char(clicktime,'yyyy') ,s.ordernum ,count(*)
from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid and to_char(clicktime,'yyyy')=trunc(to_char(sysdate,'yyyy'))
group by to_char(clicktime,'yyyy'),s.parentid, c.serviceid,s.ordernum;
begin
open c ;
loop
fetch c into v_parentid,v_serviceid,v_type,v_ordernum,v_num;
exit when (c%notfound);
dbms_output.put_line(v_type);
dbms_output.put_line(v_num);
end loop;
close c;
exit;这哪错了?
提示
Error at line 1
ORA-06550: 第 20 行, 第 6 列:
PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
改成end;
declare
v_parentid varchar(30);
v_serviceid varchar(30);
v_type varchar(30);
v_ordernum integer;
v_num number(10);
cursor c is
select s.parentid ,c.serviceid ,'year_'||to_char(clicktime,'yyyy') ,s.ordernum,count(*)
from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid and to_char(clicktime,'yyyy')=trunc(to_char(sysdate,'yyyy'))
group by to_char(clicktime,'yyyy'),s.parentid, c.serviceid,s.ordernum;
begin
open c ;
loop
fetch c into v_parentid,v_serviceid,v_type,v_ordernum,v_num;
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_num);
end loop;
close c;
end;
Error at line 1
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 14改完了报这样的错
ORA-06512: 在 line 14
v_parentid varchar(30);
v_serviceid varchar(30);
v_type varchar(30);
v_ordernum integer;
v_num number(10);
这几个变量的长度是否小于你游标获取的5个字段的长度
declare
--这样定义长度,就不要去考虑其他了
v_parentid t_con_service.parentid%type;
v_serviceid t_por_service_clicklog.serviceid%type;
v_type varchar(30);
v_ordernum t_con_service.ordernum%type;
v_num number;
cursor c is
select s.parentid ,c.serviceid ,'all_'||to_char(clicktime,'yyyy') ,s.ordernum ,count(*)
from t_con_service s, t_por_service_clicklog c where s.id= c.serviceid and to_char(clicktime,'yyyy')=trunc(to_char(sysdate,'yyyy'))
group by to_char(clicktime,'yyyy'),s.parentid, c.serviceid,s.ordernum;
begin
open c ;
loop
fetch c into v_parentid,v_serviceid,v_type,v_ordernum,v_num;
exit when (c%notfound);
dbms_output.put_line(v_type);
dbms_output.put_line(v_num);
end loop;
close c;
end;
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 ,'month_'||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;select s.parentid ,c.serviceid ,'month_'||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;这个地方我换成统计月,怎么提示无效数字啊?在group by的地方?
select s.parentid ,c.serviceid ,'month_'||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;无效数字
我现在不明白trunc到底起到什么作用啊?