Is there any other efficient way to use table variable instead of using temporary table
Posted
by varta shrimali
on Stack Overflow
See other posts from Stack Overflow
or by varta shrimali
Published on 2009-10-28T12:49:00Z
Indexed on
2010/05/11
16:04 UTC
Read the original article
Hit count: 342
mysql
|temporary-tables
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
© Stack Overflow or respective owner