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.
Best regards
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
-----Original Message-----
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Wolfram Kraus
Sent: 30 November 2004 12:58
To: [EMAIL PROTECTED]
Subject: Re: Stored Procedure?
Michael J. Pawlowsky wrote:
> Because you will be downloading 500,000 rows... And I don't really
> think that was the point. Who cares what the example is. Personally I
> was quite impressed with great explanation.
I second that it was a great explanation! Please don't get me wrong! And
furthermore I will only download one row two times: select count -> one
row select ... limit count/2, 1 -> one row
> I would have simply said a chunk of code that runs on the server that
> the client can call. And this guy took the time to put together a
> really good reply with an example.
I don't say that the example is bad, I only said that in MySQL you can
do this without a SP.
>
> Mike
>
Wolfram
>
> Wolfram Kraus wrote:
>
>> [...]
>>
>>> Suppose you have a table with a million records, test scores from a
>>> widely taken exam for example. You need to find the median mark
>>> - NOT the average! - so your algorithm needs to read all million
>>> records, sort them into ascending or descending sequence by the test
>>> score, then read exactly half way through the sequenced list to find
>>> the middle record; that's the one that contains the median score.
>>>
>>> If that work were to happen on the client, the client would have to
>>> fetch a million records, sort them all, then read through half of
>>> those records until it found the middle record; then it would report
>>> on the median mark. There would clearly be a lot of network traffic
>>> involved in getting all those records to the client, sorting them
>>> and then reading through the sorted records.
>>>
>>>
>> [...] Pardon my ignorance, but why can't you do this (in MySQL) with
>> a "select count ..." and afterwards a "select ... order by... LIMIT"?
>> All the work is done on the server, too. No need for a SP here.
>>
>> Wolfram
>>
>>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
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]
