"I just wish that MySQL was a year or two further along than it is so that 5.x would be fairly mature and we could actually start coding stored procedures, views, etc...."
Oh indeed, neither can I! Not just procedures and views either, but also triggers and sequences! And a job scheduling system would be fantastic as well!! Mark <dreaming away the day> -----Original Message----- From: Rhino [mailto:[EMAIL PROTECTED] Sent: 30 November 2004 16:04 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Stored Procedure? ----- Original Message ----- From: "Mark Leith" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, November 30, 2004 10:47 AM Subject: RE: Stored Procedure? > I think a much better example of store procedures is one showing > actual procedural processing. Whilst they also allow a lot less > communication over the network, and a lot less SQL knowledge for the > "user"(!) - a stored procedure is also a way of doing all the work > that you currently get the "client" to do (thinking things like PHP > with IF, WHILE or LOOP statements). > > So, if you have a long transaction that does multiple round trips from > the "client" - which could be your web server), whilst looping through > the results in the "client" code and doing "something else" with mysql > - then a stored procedure is where it's at. > > Here's a fairly easy example of one I use in Oracle, that allows an > admin user to reset the passwords for only a specific set of users: > > CREATE PROCEDURE reset_password > (rp_username in varchar2, > rp_password in varchar2) > as > begin > if ( USER = 'ADMIN' and rp_username in > ('GERRY', 'LYNNE', 'KRIS', 'STEPH') ) > then > execute immediate > 'alter user ' || rp_username || > ' identified by ' || rp_password; > else > send_admin_mail > ('Admin trying to change password for ' || rp_username ); > raise_application_error ( -20001, 'Change not Authorised' ); > end if; > end; > > The above will ensure that only the ADMIN user can execute the change > (enforcing extra security). It will then check that the user being > changed is within the given list, and if it is - it will run the > password change script (alter user LYNNE identified by 'password';). > > However, if any of the aforementioned check fail, it will first of all > execute another procedure (send_admin_email), passing the message > along with the substitued username to be used as the message body. It > will then generate an application error to the user with another > procedure. > > > Don't get me wrong Rhino, your example was great, and is still very > applicable.. I just thought this might be a slightly more "insightful" > example of what SP's can really handle. > No offense taken, Mark. You've simply given a more advanced example that illustrates even more capabilities of a stored procedure. It's a great supplemental example. I was simply citing the 'classic' example that I've taught in DB2 courses. Those courses were for people who were new to stored procedures (and many other aspects of DB2) and needed to know the basic concepts before trying to write one. That seemed to be the level of the person who asked the original question. However, your example illustrates how much more a stored procedure can do so it should certainly be strongly considered by all developers, particularly advanced ones. I just wish that MySQL was a year or two further along than it is so that 5.x would be fairly mature and we could actually start coding stored procedures, views, etc.... Rhino -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
