oracle存储过程调用小技巧

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