Result sets in triggers, stored procedures, and functions

[monkle]

I am running MySQL 5.0.27

I need to know how to deal with result sets in pure SQL functions/stored procedures. I will explain:

I am working on building a set of triggers for a relational database. Essentially, I have two sets of independent tables, and I want to keep them in sync. I have a set of source tables, and a dynamic number of multiple target tables. I don't want to have to touch the structure of the tables on either side more than I have to, and I want to do it completely in SQL

Here is the structure of what I have so far:

7 source tables
3 triggers per table (update, insert, and delete)

A stored procedure that gathers all the needed information from all 7 tables, and inserts it into a target middle table that will feed all of the other (multiple sets of) target tables.

I have the first set of triggers for the first table done. For changes to this table there is a single record that needs to be inserted into the target (middle) table.

Where I am right now is starting in on the triggers for the second table. The kicker for this table is that, for any one change in this table, there will have to be multiple records updated in the target (middle) table.

I have written a function that gets passed a key, which it can use to run a select query to return all of the needed rows. This is where what I know how to do breaks down.

How do you execute a query and return the result set to be looped on, OR execute a query, build an array by looping on the result set, and then return the array?

I have done this a lot using other programming languages, but this is the first time I've wanted to do something like this in pure SQL.

I have been researching this all week, and while I have found a lot of information, I have not been able to find anything that has helped. Any input would be greatly appreciated.

 

[monkle]

For anybody looking for information, here is a series of articles that have gotten me a good bit further along:

http://www.databasejournal.com/features/mysql/article.php/10897_3525581_2

http://www.databasejournal.com/features/mysql/article.php/10897_3547931_2

http://www.databasejournal.com/features/mysql/article.php/10897_3554616_2

 

[KarveR]

Heres a function to calculate lead time on an order, so we need to select relevant info from a table, do teh calulations and pass it back.

As usual, MySQL syas some blah about not being able to use resultsets in functions, so we do a SELECT INTO ....

DELIMITER $$

CREATE FUNCTION lead_time (var1 varchar(20)) 

      RETURNS VARCHAR(20) 
      BEGIN
           DECLARE late,raw_lead,satsun,deliver,weekend_days varchar(20);
           DECLARE lead INT; 
        
           SELECT
                  IF(
                    crt_time >= '13:00:00'
                    ,'1'
                    ,'0'
                  ),
                  
                  
                  DATEDIFF(
                    req_datc
                   ,crt_date
                  ) + 1,
                  
                  
                  CASE 
                    dayofweek(crt_date) 
                       WHEN '1' THEN  '1'  
                       WHEN '7' THEN  '2'  
                       ELSE '0' 
                 END, 
              
                 CASE 
                    dayofweek(req_datc) 
                       WHEN '1' THEN  '2'  
                       WHEN '7' THEN  '2'  
                       ELSE '0' 
                 END,
                 
                 (( dayofweek(crt_date) + datediff(req_datc,crt_date) ) div 7 ) * 2
              
           [b] INTO 
                 late,raw_lead,satsun,deliver,weekend_days[/b]
            
            FROM
                 ORD_HDR 
            
            WHERE 
                 ord_num=var1;

            SET lead = (raw_lead - late) - satsun - weekend_days + deliver; 
            RETURN lead; 
     END $$

DELIMITER ;

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.