我现在有两张表,一张是体温表,一张是员工表,分别如下:体温表:
t_id e_obid t_checkdate t_temperature
1 1 2012-3-2 11:11:11 36
2 1 2012-3-3 12:11:11 37.5
3 2 2012-3-2 11:11:11 36.5
4 2 2012-3-3 12:11:11 38员工表:
e_id e_name
1 小李
2 小陈我想在oracle写一个存储过程实现传入年份,得到这一年每个月体温大于37的人次,如果没有的话就显示0,预想的效果是这样:日期 异常人次
2012-1 0
2012-2 0
2012-3 1
.
.
2012-12 0先谢谢了!
t_id e_obid t_checkdate t_temperature
1 1 2012-3-2 11:11:11 36
2 1 2012-3-3 12:11:11 37.5
3 2 2012-3-2 11:11:11 36.5
4 2 2012-3-3 12:11:11 38员工表:
e_id e_name
1 小李
2 小陈我想在oracle写一个存储过程实现传入年份,得到这一年每个月体温大于37的人次,如果没有的话就显示0,预想的效果是这样:日期 异常人次
2012-1 0
2012-2 0
2012-3 1
.
.
2012-12 0先谢谢了!
to_char或者substr都可以啊
for c in (
with m as ( --这个临时表如果能事先建好,一句sql就可以了
select to_date(v_year||'-1-1','yyyy-mm-dd') begin_time,
to_date(v_year||'-2-1','yyyy-mm-dd') end_time from dual
union all
select to_date(v_year||'-2-1','yyyy-mm-dd') begin_time,
to_date(v_year||'-3-1','yyyy-mm-dd') end_time from dual
--中间省略
union all
select to_date(v_year||'-12-1','yyyy-mm-dd') begin_time,
to_date((v_year+1)||'-1-1','yyyy-mm-dd') end_time from dual
)
select m.begin_time "日期", sum(case when t.t_temperature>37 then 1 else 0 end) "异常人次"
from table t, m where t.t_checkdate>=m.begin_time and t_checkdate<m.end_time
group by m.begin_time order by m.begin_time
)loop
dbms_output.put_line(c."日期");
dbms_output.put_line(c."异常人次");
end loop;
大哥,如果我想用一个变量results_out获取返回的结果集,是这样写存储过程的吗?我刚开始学oracle
create or replace package pkg_test is
type result_type is ref cursor;
procedure year_test(results_out out result_type,v_year in integer);
end pkg_test; create or replace package body pkg_test is
procedure year_test(results_out out result_type, v_year in integer)
is
begin
for c in (
with m as ( --这个临时表如果能事先建好,一句sql就可以了
select to_date(v_year||'-1-1','yyyy-mm-dd') begin_time,
to_date(v_year||'-2-1','yyyy-mm-dd') end_time from dual
union all
select to_date(v_year||'-2-1','yyyy-mm-dd') begin_time,
to_date(v_year||'-3-1','yyyy-mm-dd') end_time from dual
--中间省略
union all
select to_date(v_year||'-12-1','yyyy-mm-dd') begin_time,
to_date((v_year+1)||'-1-1','yyyy-mm-dd') end_time from dual
)
open results_out for select m.begin_time "日期", sum(case when t.t_temperature>37 then 1 else 0 end) "异常人次"
from table t, m where t.t_checkdate>=m.begin_time and t_checkdate<m.end_time
group by m.begin_time order by m.begin_time
)loop
end loop;
end pro_test;
end pkg_test;
create or replace package pkg_test is
procedure year_test(results_out out sys_refcursor,v_year in integer);
end pkg_test; create or replace package body pkg_test is
procedure year_test(results_out out sys_refcursor, v_year in integer)
is
begin
open results_out for
with m as ( --这个临时表如果能事先建好,一句sql就可以了
select to_date(v_year||'-1-1','yyyy-mm-dd') begin_time,
to_date(v_year||'-2-1','yyyy-mm-dd') end_time from dual
union all
select to_date(v_year||'-2-1','yyyy-mm-dd') begin_time,
to_date(v_year||'-3-1','yyyy-mm-dd') end_time from dual
--中间各个月份省略
union all
select to_date(v_year||'-12-1','yyyy-mm-dd') begin_time,
to_date((v_year+1)||'-1-1','yyyy-mm-dd') end_time from dual
)
select m.begin_time "日期", sum(case when t.t_temperature>37 then 1 else 0 end) "异常人次"
from m left outer join table t on t.t_checkdate>=m.begin_time and t.t_checkdate<m.end_time
group by m.begin_time order by m.begin_time;
end pro_test;
end pkg_test;
循环里面取到月初月末,通过子查询查出结果
这些东西最好自己尝试去写,这应该是个很简单的东西存储过程能用的工具太多太多,表、视图、数组