List:General Discussion« Previous MessageNext Message »
From:Gordon Date:March 24 2006 7:49pm
Subject:RE: Stored procedures and views
View as plain text  

If the features available in stored procedures {i.e. looping etc.} are
required, have you tried having the procedure 1st write/edit the data in a
temporary table and at the end select the values you want from that table. 

I think I might write 2 procedures. One that does the data manipulation and
the other that selects the result. 
something like this  

DELIMITER //
DROP PROCEDURE IF EXITS manipulate//

CREATE PROCEDURE manipulate(
CREATE TEMPORARY TABLE data_result

...
...
END// 

DROP PROCEDURE IF EXITS result//

CREATE PROCEDURE result(
...
  CALL manipulate (
...
  SELECT ... FROM data_result
END//

DELIMITER ;
> -----Original Message-----
> From: Martijn Tonies [mailto:m.tonies@stripped]
> Sent: Friday, March 24, 2006 1:04 PM
> To: Chris Carrier; mysql@stripped
> Subject: Re: Stored procedures and views
> 
> 
> 
> > I just mean is it possible to hook a set of stored procedures to a view
> as
> > opposed to a temporary table that we'd have to explicitly update?
> 
> "hook"?
> 
> If you mean: using the views in select statements inside a procedure:
> sure it. Did you try?
> 
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
> 
> > Chris
> >
> > -----Original Message-----
> > From: Martijn Tonies [mailto:m.tonies@stripped]
> > Sent: Friday, March 24, 2006 10:26 AM
> > To: mysql@stripped
> > Subject: Re: Stored procedures and views
> >
> > Chriss,
> >
> > > I'm trying to create a flatfile dump from our database which requires
> some
> > > functionality that's not possible with raw sql.  Up to now we've been
> > using
> > > PHP tied to mySql to do all the work and get the data in the correct
> > format.
> > > We would like to switch this functionality over to stored procedures
> in
> > > mySql.  My question is this: Is it possible to hook a series of stored
> > > procedures to a view so that the data is live and current?
> >
> > What do you mean by that?
> >
> > A view is always current.
> >
> > Can you explain it a bit better?
> >
> > >We could set it
> > > up to run our procedures on some regular interval and dump the result
> into
> > a
> > > temporary table but having up-to-date data would be ideal.
> >
> > Martijn Tonies
> > Database Workbench - development tool for MySQL, and more!
> > Upscene Productions
> > http://www.upscene.com
> > My thoughts:
> > http://blog.upscene.com/martijn/
> > Database development questions? Check the forum!
> > http://www.databasedevelopmentforum.com
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=1
> >
> >
> > --
> > No virus found in this incoming message.
> > Checked by AVG Free Edition.
> > Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 3/24/2006
> >
> >
> > --
> > No virus found in this outgoing message.
> > Checked by AVG Free Edition.
> > Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 3/24/2006
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Foreign keysJoão Cândido de Souza Neto24 Mar
  • Re: Foreign keysOlusola Fadero24 Mar
    • Stored procedures and viewsChris Carrier24 Mar
      • Re: Stored procedures and viewsMartijn Tonies24 Mar
        • RE: Stored procedures and viewsChris Carrier24 Mar
          • Re: Stored procedures and viewsMartijn Tonies24 Mar
            • RE: Stored procedures and viewsGordon24 Mar