declare
v_type varchar(30);
v_num number(10);
begin
select 'all_'||to_char(clicktime,'yyyy') into v_type,count(*) into v_num from t_por_service_clicklog where to_char(clicktime,'yyyy')=(select TO_CHAR(SYSDATE,'yyyy') from dual) group by to_char(clicktime,'yyyy');
dbms_output.put_line(v_type);
dbms_output.put_line(v_num);
end;Error at line 1
ORA-06550: 第 5 行, 第 54 列:
PL/SQL: ORA-00934: 此处不允许使用分组函数
ORA-06550: 第 5 行, 第 1 列:
PL/SQL: SQL Statement ignored我用游标,结果也是一样的。提示不支持分组函数,哪有问题?
declare
v_type varchar(30);
v_num number(10);
cursor c is
select 'all_'||to_char(clicktime,'yyyy') into v_type,count(*) into v_num from t_por_service_clicklog where to_char(clicktime,'yyyy')=(select TO_CHAR(SYSDATE,'yyyy') from dual) group by to_char(clicktime,'yyyy');
begin
loop
exit when (c%notfound);
dbms_output.put_line(v_emp.id);
end loop;
close c;
dbms_output.put_line(v_type);
dbms_output.put_line(v_num);
end;
v_type varchar(30);
v_num number(10);
begin
select 'all_'||to_char(clicktime,'yyyy') into v_type,count(*) into v_num from t_por_service_clicklog where to_char(clicktime,'yyyy')=(select TO_CHAR(SYSDATE,'yyyy') from dual) group by to_char(clicktime,'yyyy');
dbms_output.put_line(v_type);
dbms_output.put_line(v_num);
end;Error at line 1
ORA-06550: 第 5 行, 第 54 列:
PL/SQL: ORA-00934: 此处不允许使用分组函数
ORA-06550: 第 5 行, 第 1 列:
PL/SQL: SQL Statement ignored我用游标,结果也是一样的。提示不支持分组函数,哪有问题?
declare
v_type varchar(30);
v_num number(10);
cursor c is
select 'all_'||to_char(clicktime,'yyyy') into v_type,count(*) into v_num from t_por_service_clicklog where to_char(clicktime,'yyyy')=(select TO_CHAR(SYSDATE,'yyyy') from dual) group by to_char(clicktime,'yyyy');
begin
loop
exit when (c%notfound);
dbms_output.put_line(v_emp.id);
end loop;
close c;
dbms_output.put_line(v_type);
dbms_output.put_line(v_num);
end;
from t_por_service_clicklog
where clicktime>=trunc(sysdate,'yyyy') and clicktime<add_months(trunc(sysdate,'yyyy'),12)
group by to_char(clicktime,'yyyy');
另外请教下,我的为么报错了?
而你的没报错?
trunc(sysdate,'yyyy') and clicktime<add_months(trunc(sysdate,'yyyy'),12)
这是什么意思
谢谢
v_type varchar(30);
v_num number(10);
begin
select 'all_'||to_char(clicktime,'yyyy'),count(*) into v_type,v_num
from t_por_service_clicklog where to_char(clicktime,'yyyy')=trunc(to_char(sysdate,'yyyy'))
group by to_char(clicktime,'yyyy');
dbms_output.put_line(v_type);
dbms_output.put_line(v_num);
end;
我这样写的也没有问题,但是为什么没有数据啊?
不对啊
为什么加上trunc就没问题,不加就报分组函数不可用?怎么回事
另外查出来为什么没结果啊?
加上set serveroutput on 就出来了
但是 为什么这里非得要加上一个trunc就好了?declare
v_type varchar(30);
v_num number(10);
begin
select 'all_'||to_char(clicktime,'yyyy'),count(*) into v_type,v_num
from t_por_service_clicklog where to_char(clicktime,'yyyy')=trunc(to_char(sysdate,'yyyy'))
group by to_char(clicktime,'yyyy');
dbms_output.put_line(v_type);
dbms_output.put_line(v_num);
end;