CREATE OR REPLACE PROCEDURE ss(eff_day VARCHAR2)
-- eff_day 统计日期,格式为 'yyyymmdd'
AS
v_year INTEGER;
v_month INTEGER;
v_day INTEGER;
v_date DATE;
v_tmpDate DATE;
v_count INTEGER;
v_begin INTEGER;
v_eff_date VARCHAR2(12);
BEGIN
v_eff_date := TO_CHAR(TO_DATE(eff_day,'yyyymmdd')-1,'yyyymmdd');
v_year := TO_NUMBER(SUBSTR(v_eff_date,1,4));
v_month := TO_NUMBER(SUBSTR(v_eff_date,5,2));
v_day := TO_NUMBER(SUBSTR(v_eff_date,7,2));
v_date := to_date(v_eff_date,'yyyymmdd'); -- 时段的起始时间
v_begin := 0;
FOR v_count IN 1..24 -- 一天24时段,循环24次
LOOP
INSERT INTO a_contentbyper_7_1(icp_id,channel_id,content_id,area_code,year, month,day,time_id,itemclass,itemvalue)
(SELECT /*+ INDEX(A I_CREATED_T_EVENT_T) index(a I_CREATED_T_EVENT_T) index(a I_EVENT_ID) use_nl(a c)*/ a.icp_account_id,-1,b.id,1,1,1,1,
1 - 1,2,sum(c.amount) FROM mid.event_t a,content_t b,mid.event_resimpact_t c
WHERE a.itemname = b.name AND c.event_id = a.id AND c.dest_res_name = 'RMB Yuan'
AND a.created_t >= TO_DATE('20030716','YYYYMMDD') AND a.created_t < TO_DATE('20030716','YYYYMMDD') + 1/24
GROUP BY a.icp_account_id,b.id);
insert into log_date values (sysdate,v_count);
COMMIT;
INSERT INTO a_contentbyper_7_1(icp_id,channel_id,content_id,area_code,year, month,day,time_id,itemclass,itemvalue)
(SELECT /*+ INDEX(A I_CREATED_T_EVENT_T) index(a I_CREATED_T_EVENT_T) index(a I_EVENT_ID) use_nl(a c)*/ a.icp_account_id,-1,b.id,1,1,1,1,
1 - 1,2,sum(c.amount) FROM mid.event_t a,content_t b,mid.event_resimpact_t c
WHERE a.itemname = b.name AND c.event_id = a.id AND c.dest_res_name = 'RMB Yuan'
AND a.created_t >= v_date AND a.created_t < v_date + 1/24
GROUP BY a.icp_account_id,b.id);
insert into log_date values(sysdate,v_count);
COMMIT;
v_date := v_date + 1/24; -- 下一个时段的起始时间
END LOOP;
END;
/
上下两段代码,唯一的区别就是
a.created_t >= TO_DATE('20030716','YYYYMMDD') AND a.created_t < TO_DATE('20030716','YYYYMMDD') + 1/24
和
a.created_t >= v_date AND a.created_t < v_date + 1/24
但上面能使用索引,而下面的无法使用
v_date := to_date(v_eff_date,'yyyymmdd');
v_eff_date 是存储过程的输入参数
为什么 在存储过程中,变量替换无法使用索引?
谢谢!!