create or replace procedure tj_count_coupon
as
a number;
v_limitid number;
v_limitname varchar2;
v_merchantname varchar2;
Begin
select count(*) into a
from couponinfo t
left join coupon_merchant t1 on t.coupon_id = t1.product_id
left join merchant t2 on t1.merchant_id = to_char(t2.merchant_id)
and t1.product_type = 1
where to_date(t.end_date, 'yyyy-mm-dd HH24:mi:ss') >= sysdate
and to_char(t.str_date, 'yyyy-mm-dd HH24:mi:ss' ) <= to_char(sysdate,'yyyy-mm-dd HH24:mi:ss')
for i in 1...a loop
select t2.name into v_merchantname,
t.coupon_id into v_limitid ,
t.coupon_name into v_limitname
from couponinfo t
left join coupon_merchant t1 on t.coupon_id = t1.product_id
left join merchant t2 on t1.merchant_id = to_char(t2.merchant_id)
and t1.product_type = 1
where to_date(t.end_date, 'yyyy-mm-dd HH24:mi:ss') >= sysdate
and to_char(t.str_date, 'yyyy-mm-dd HH24:mi:ss' ) <= to_char(sysdate,'yyyy-mm-dd HH24:mi:ss')
where rownum=i
insert into count_coupon((id, coupon_id, coupon_name, rec_date, down_count, merchant_name) values
(SEQ_count_coupon_ID.Nextval,v_limitid,v_limitname,to_char(sysdate,'yyyy-mm-dd'),
(select count(*) as t from user_coupon where coupon_id=v_limitid and to_char(reg_date,'yyyy-MM-dd')=to_char(sysdate,'yyyy-mm-dd')),