按时、天、月、年来调用oracle存储过程，获取对应的数据或对数据进行整理。
1、创建一张新表
create table tt_est(id number,name varchar(20),createtime date);2、插入测试数据 INSERT INTO tt_est VALUES ('1', 'aa', TO_DATE('2017-06-01 01:00:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO tt_est VALUES ('2', 'bb', TO_DATE('2017-06-01 02:00:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO tt_est VALUES ('3', 'cc', TO_DATE('2017-06-01 03:00:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO tt_est VALUES ('4', 'dd', TO_DATE('2017-06-01 04:00:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO tt_est VALUES ('5', 'ee', TO_DATE('2017-06-01', 'YYYY-MM-DD')); INSERT INTO tt_est VALUES ('6', 'ff', TO_DATE('2017-06-02', 'YYYY-MM-DD')); INSERT INTO tt_est VALUES ('7', 'gg', TO_DATE('2017-06-03', 'YYYY-MM-DD')); INSERT INTO tt_est VALUES ('8', 'hh', TO_DATE('2017-06-04', 'YYYY-MM-DD')); INSERT INTO tt_est VALUES ('9', 'ii', TO_DATE('2017-01-01', 'YYYY-MM-DD')); INSERT INTO tt_est VALUES ('10', 'jj', TO_DATE('2017-02-02', 'YYYY-MM-DD')); INSERT INTO tt_est VALUES ('11', 'kk', TO_DATE('2017-03-03', 'YYYY-MM-DD')); INSERT INTO tt_est VALUES ('12', 'll', TO_DATE('2017-04-04', 'YYYY-MM-DD')); INSERT INTO tt_est VALUES ('13', 'mm', TO_DATE('2011-01-01', 'YYYY-MM-DD')); INSERT INTO tt_est VALUES ('14', 'nn', TO_DATE('2012-02-02', 'YYYY-MM-DD')); INSERT INTO tt_est VALUES ('15', 'oo', TO_DATE('2013-03-03', 'YYYY-MM-DD')); INSERT INTO tt_est VALUES ('16', 'pp', TO_DATE('2014-04-04', 'YYYY-MM-DD'));3、分时、天、月、年建立四个存储过程
call_by_time、call_by_day、call_by_month、call_by_year
CREATE OR REPLACE PROCEDURE call_by_time( v_start_time DATE, v_end_time DATE) is Cursor v_cur is select * from tt_est t where t.createtime>=v_start_time and t.createtime<=v_end_time; BEGIN for c_cur in v_cur loop dbms_output.put_line(c_cur.name); end loop; END call_by_time; CREATE OR REPLACE PROCEDURE call_by_day( v_day DATE) AS v_start_time DATE := TRUNC(v_day, 'dd'); v_end_time DATE := TRUNC(v_day, 'dd') + 0.99999; BEGIN call_by_time(v_start_time, v_end_time); END call_by_day; CREATE OR REPLACE PROCEDURE call_by_month( v_year NUMBER, v_month NUMBER) AS v_start_day DATE; v_end_day DATE; BEGIN v_start_day := to_date(v_year ||'-' || v_month ||'-01 00:00:00','yyyy-mm-dd hh24:mi:ss'); v_end_day := TRUNC(LAST_DAY(v_start_day),'DD') + 0.99999; call_by_time(v_start_day, v_end_day); END call_by_month; CREATE OR REPLACE PROCEDURE call_by_year( v_year NUMBER) AS BEGIN -- exec by month FOR v_temp IN 1 .. 12 LOOP call_by_month(v_year, v_temp); END LOOP; END call_by_year; 调用存储过程脚本 call call_by_time(to_date('2017-06-01 01:00:00', 'yyyy-mm-dd hh24:mi:ss'),to_date('2017-06-01 02:00:00', 'yyyy-mm-dd hh24:mi:ss')); call call_by_day(to_date('2017-06-01', 'yyyy-mm-dd hh24:mi:ss')); call call_by_month(2017,6); call call_by_year(2017);