we are writing script to display banners on a web page where we are using temporary table in mysql procedure. Is there any other efficient way to use table variable instead of using temporary table
we are using following code:
-- banner location CURSOR --
DECLARE banner_location_cursor CURSOR FOR
select bm.id as masterId, bm.section as masterName, bs.id as locationId,
bs.sectionName as locationName
from banner_master as bm inner join banner_section as bs
on bm.id=bs.masterId
where bm.section=sCode ;
-- DECLARE banner CURSORS
DECLARE banner_cursor CURSOR FOR
SELECT bd.id as bannerId, bd.sectionId, bd.bannerName, bd.websiteURL, bd.paymentType, bd.status,
bd.startDate, bd.endDate,
bd.bannerDisplayed, bs.id, bs.sectionName
from banner_detail as bd inner join banner_section as bs
on bs.id=bd.sectionId
where bs.id= location_id
and bd.status='A'
and (dates between cast(bd.startDate as DATE) and cast(bd.endDate as DATE))
order by rand(), bd.bannerDisplayed asc
limit 1
;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = 1;
SET dates = (select curdate());
-- RESULTS TABLE WHICH WILL BE RETURNED --
CREATE temporary TABLE test
(
b_id INT,
s_id INT,
b_name varchar(128),
w_url varchar(128),
p_type varchar(128),
st char(1),
s_date datetime,
e_date datetime,
b_display int,
sec_id int,
s_name varchar(128)
);
-- OPEN banner location CURSOR
OPEN banner_location_cursor;
the_loop: LOOP
FETCH banner_location_cursor
INTO master_id, master_name, location_id, location_name;
IF no_more_rows THEN
CLOSE banner_location_cursor;
leave the_loop;
END IF;
OPEN banner_cursor;
-- select FOUND_ROWS();
the_loop2: LOOP
FETCH banner_cursor
INTO banner_id,
section_id,
banner_name,
website_url,
payment,
status,
start_date,
end_date,
banner_displayed,
sec_id,
section_name;
IF no_more_rows THEN
set no_more_rows = 0;
CLOSE banner_cursor;
leave the_loop2;
END IF;
INSERT INTO test
(
b_id,
s_id,
b_name ,
w_url,
p_type,
st,
s_date,
e_date,
b_display,
sec_id,
s_name
)
VALUES
(
banner_id,
section_id,
banner_name,
website_url,
payment,
status,
start_date,
end_date,
banner_displayed,
sec_id,
section_name
);
UPDATE banner_detail
set bannerDisplayed = (banner_displayed+1)
where id = banner_id;
END LOOP the_loop2;
END LOOP the_loop;
-- RETURN result
SELECT * FROM test;
-- DROP RESULTS TABLE
DROP TABLE test;
END
