views:

86

answers:

0

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