Databases
StackOverflow2013
Postsdbo
POTemporary Table in Stored Functions?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTemporary Table in Stored Functions?
    primarykey
    data
    text
    <p>I'm writing a function that I need to use either a <code>TABLE</code> variable for (<a href="https://stackoverflow.com/questions/1524858/create-table-variable-in-mysql">I hear they don't exist in MySQL</a>) or a temporary table.</p> <p>However, it seems that temporary tables only seem to work in stored procedures, not functions. I keep getting this error:</p> <blockquote> <p>Explicit or implicit commit is not allowed in stored function or trigger.</p> </blockquote> <hr> <p>What I'm trying to build is a solution to <a href="https://stackoverflow.com/questions/8015018/mysql-in-clause-for-dates-between-a-start-and-end-date">an earlier question of mine</a>. It's a function that receives a start date, an end date, and a comma-deliminated string. It first finds all the months between the start and end date and saves them as individual records in the first temporary table. It then parses out the comma-deliminated string and saves those into a second temporary table. Then it does a select join on the two, and if records are present, it returns true, otherwise false.</p> <p>My intention is to use this as part of another queries <code>WHERE</code> clause, so it needs to be a function and not a stored procedure.</p> <p>How can I use temporary tables in stored functions? And if I can't, what can I do instead?</p> <p>Here's my (currently broken) function (or as a <a href="https://gist.github.com/1342136" rel="nofollow noreferrer">gist</a>):</p> <pre><code>-- need to parse out a string like '4,2,1' and insert values into temporary table -- MySQL doesn't have a native string split function, so we make our own -- taken from: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ DROP FUNCTION IF EXISTS SPLIT_STR; CREATE FUNCTION SPLIT_STR(x VARCHAR(255), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255) RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, ''); -- need to find all months between the start and end date and insert each into a temporary table DROP FUNCTION IF EXISTS months_within_range; DELIMITER // CREATE FUNCTION months_within_range(starts_at DATE, ends_at DATE, filter_range VARCHAR(255)) RETURNS TINYINT BEGIN DROP TABLE IF EXISTS months_between_dates; DROP TABLE IF EXISTS filter_months; CREATE TEMPORARY TABLE months_between_dates (month_stuff VARCHAR(7)); CREATE TEMPORARY TABLE filter_months (filter_month VARCHAR(7)); SET @month_count = (SELECT PERIOD_DIFF(DATE_FORMAT(ends_at, "%Y%m"), DATE_FORMAT(starts_at, "%Y%m"))); -- PERIOD_DIFF only gives us the one month, but we want to compare to, so add one -- as in, the range between 2011-01-31 and 2011-12-01 should be 12, not 11 INSERT INTO months_between_dates (month_stuff) VALUES (DATE_FORMAT(starts_at, "%Y-%m")); SET @month_count = @month_count + 1; -- start he counter at 1, since we've already included the first month above SET @counter = 1; WHILE @counter &lt; @month_count DO INSERT INTO months_between_dates (month_stuff) VALUES (DATE_FORMAT(starts_at + INTERVAL @counter MONTH, "%Y-%m")); SET @counter = @counter + 1; END WHILE; -- break up the filtered string SET @counter = 1; -- an infinite loop, since we don't know how many parameters are in the filtered string filters: LOOP SET @filter_month = SPLIT_STR(filter_range, ',', @counter); IF @filter_month = '' THEN LEAVE filters; ELSE INSERT INTO filter_months (filter_month) VALUES (@filter_month); SET @counter = @counter + 1; END IF; END LOOP; SELECT COUNT(*) INTO @matches FROM months_between_dates INNER JOIN filter_months ON months_between_dates.month_stuff = filter_months.filter_month; IF @matches &gt;= 1 THEN RETURN 1; ELSE RETURN 0; END// DELIMITER ; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USneezer
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTemporary Table in Stored Functions?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POTemporary Table in Stored Functions?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload