按时、天、月、年来调用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);