...

View Full Version : Procedures and functions



Apothem
06-15-2010, 08:27 PM
First I've never used or made a MySQL procedure or function. I looked at a few, though, and thought it could be very useful - including for simplifying PHP code. I looked at a few tutorials but I didn't see one thing that I really wanted to know:
Whats the difference between a Function and a Procedure? They look very similar and I only noted the differ that one can do: CALL GetAverage() / SELECT GetAverage()
How do you store values (or a value) from a SELECT statement into a variable?

Old Pedant
06-15-2010, 10:47 PM
Functions can return values. Stored procedures can't. Simple as that.

Which means that if one stored proc calls another stored proc, then only way for the caller to "see" the work that the callee did is to pass that work through something outside the two procedures, and that probably means using a temp table.

On the other hand, the fatal flaw in MySQL functions (at least up through version 5.1; I haven't looked at newer versions) is that they can't return tables or table equivalents. (SQL Server can, and it's a wonderful addition.)


How do you store values (or a value) from a SELECT statement into a variable?
I assume you mean you want to do that in a stored proc or function.

It's really pretty easy. Code fragment follows:


...
DECLARE v_name VARCHAR(255);
DECLARE v_age INT;
...
SELECT name, age INTO v_name, v_age
FROM users
WHERE userid = i_userid;

The prefixes (i_ and v_) are *NOT* required. I am only using them there to indicate that they are "v_ariables" and "i_nput" values. That is v_ are local to the procedure/function and i_ are the input arguments to the procedure/function. Whether you adopt some similar convention or not is completely up to you. (For the same reason, I tend to name my temporary tables with at "t_" prefix. But again that's arbitrary.)

ANYWAY... If you need to create a subroutine that returns a table, then it really doesn't matter much whether you use a function or stored procedure. Either one will need to create a temporary table that becomes essentially like a global variable known to both it and its caller. I suppose in that case you might want to use a function, just so that you could return a success/failure indicator as well as the temp table.

Don't be afraid of cursors and temp tables. I had a *VERY* complex stored procedure where I had tried assidiously to avoid both cursors and temp tables, because of my experience with SQL Server where they can be performance hogs. I finally broke down and converted to using a 5 cursors and 5 temporary tables...and my performance *increased* by almost ten-fold!! MySQL is very efficient in its use of both, it would seem.

Old Pedant
06-15-2010, 10:53 PM
Should have clarified: If you use either a function or s.p. from your PHP code, and you only need a single value, then indeed the only difference is what you noted: The syntax.

But *INSIDE* of a stored proc (or function), you can call a function and simply assign it to a variable *or* just use it like you would any of the builtin functions of MySQL.

...
DECLARE v_age INT;
...
SET v_age = getUserAge( 'adams', 'joe', 'engineering' );
...
SELECT * FROM users WHERE age > getUserAge( 'barnes', 'ann', 'administration' );

So if that getUserAge( ) function is expecting lastname, firstname, department arguments, returning an INT, the first statement there simply sets the variable to the age of the given person where the second finds all users older than the given person. And so on.

Finally, you can even use a user-defined function when making ad hoc SQL calls from your php code:

$sql = "SELECT lastname, firstname FROM users "
. " WHERE age > getUserAge( '$last', '$first', '$dept' ) "
. " LIMIT 1;" ;

So you pass in the last,first, and department of one person and get the person who is the next oldest, using that query. In short, a user-defined function is no different than a built-in one such as DATE_FORMAT() or any other.

Apothem
06-16-2010, 12:24 AM
For the SELECT INTO syntax, what happens if the variable your setting the things are null or does not exist?

How would you even access a single/all members of the variable? i.e. if there are 10 rows from a query and its set into the variable "v_age" how would you go through each of them?

Thanks for your detailed post.

Old Pedant
06-16-2010, 12:34 AM
All variables in MySQL behave like column values in a DB table: It's perfectly legal for a variable to have a NULL value.

So if you did:

DECLARE foo INT;
SELECT zam INTO foo FROM xyz WHERE 1 > 2;

then clearly foo would have a NULL value, and you can indeed then test for that:

IF foo IS NULL THEN ...


*******

if there are 10 rows from a query and its set into the variable "v_age"...
Can't do that. Your query has to return a *single record* or you get an error.

If you need to traipse through multiple records, then you have to use a cursor:


DECLARE foo INT;
DECLARE zamcursor FOR
SELECT zam FROM table WHERE xyz > 73;
OPEN zamcursor;
REPEAT
FETCH zamcursor INTO foo;
IF foo IS NOT NULL THEN
... do something ...
END IF;
UNTIL foo IS NULL END REPEAT;
CLOSE zamcursor;

If the SELECT of the cursor selects multiple fields, then you assign them, in order, into mutiple variables:


FETCH cursorWithThreeFieldsInSelect INTO v_one, v_two, v_three;


You have to work with one record at a time, only, when working with variables. Again, luckily cursors in MySQL are pretty efficient (even if the syntax for using them is ugly as pig snot).

BubikolRamios
06-16-2010, 09:12 AM
Note: do not use functions ever, instead do procedures all the time(you have out parameter there if you need it)

Reason: you can't call function from stored proc, or something like that search for my posts(and it does not error alert you about that at all, instead it let you loose a lot of time to figure that out) .

Old Pedant
06-16-2010, 08:08 PM
Note: do not use functions ever,

I 100% *DISAGREE*. I use functions ALL THE TIME and quite successfully.

Yes, they are limited to returning one single value. But that doesn't mean they are bad. Just don't use them for the wrong things.

Very very seldom have I used a stored procedure and then used OUT parameters to return data from one. Yes, it works, but it means you can only return the info from one record. (When I have used them, it's generally to retrieve aggregate information--AVG(), MIN(), MAX(), etc.) As I noted in my post, it's usually more useful to have an "inner" stored proc return a temporary table.


you can't call function from stored proc
Not at all true. At least certainly not true with MySQL 5.1. Again, I do this all the time.

BubikolRamios
06-17-2010, 12:31 AM
hmm cant find my old post, why I changed all functions into procedures. There was one
realy good reason. Anyway: http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html

mybe this:


SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them).

don't remember.

Old Pedant
06-17-2010, 06:22 AM
<shrug>Generally, I try real real real hard to *AVOID* using dynamic SQL.

I don't know that I think of any case where writing a stored function use dynamic SQL would be useful, even if you could do it.

Quite frankly, I was unaware that I couldn't use dynamic SQL in functions, meaning that I've never even tried to do so despite writing dozens of functions.

Anyway, that says you can't use EXECUTE and its cousins inside of functions. It says nothing whatsoever about not using function inside stored procedures.

Apothem
06-17-2010, 08:38 AM
Well, I'm not exactly sure what you two are debating about but I have a problem:

DELIMITER |
CREATE PROCEDURE getChapterQuery( IN chapterID INT(11) unsigned )
BEGIN
SELECT CONCAT(s.abbr, '/',m.folder,'/', c.folder) FROM chapters c INNER JOIN manga m ON(c.mid=m.id)
INNER JOIN sites s ON (m.sid = s.id) WHERE c.id = chapterID;
END |
DELIMITER ;

That's the procedure I want to make. When I call it via:
CALL getChapterQuery(1)
The error is returned: PROCEDURE mv.getChapterQuery can't return a result set in the given context

BubikolRamios
06-17-2010, 01:50 PM
i dont see realy why would use stored proc for that.

I see all kinds of strange syntax, but mysql query browser auto suggest:



DELIMITER $$

DROP PROCEDURE IF EXISTS `proc` $$
CREATE DEFINER=`root`@`192.168.1.5` PROCEDURE `proc`(params)
BEGIN

your contence

END $$

DELIMITER ;


mybe green part is the problem.

EDIT:
Old Pedant : That is why I have a dosen and you have a dosens of stored functions/procedures (-:

will give you an example of reasonable use:



....
SET @s = CONCAT('INSERT INTO tree SELECT CHILD_ID, PARENT_ID FROM ', sourceTable);
PREPARE stmt FROM @s;
EXECUTE stmt;
....


Have a bunch of tree structure data tables, and in order to convert any of them from adjacent(upon adjacent table update) to nested model table I need only one procedure !
Dont see why you are avoiding dynamic sql in stored procs, except that I read somewhere that it will not be supported in future.

Old Pedant
06-17-2010, 07:53 PM
Apothem: I don't think there is anything at all wrong with that procedure.

*WHERE* are you trying to execute that CALL from? I think that is the problem area.

It looks like you are perhaps trying to CALL it to get a single record in a place that can't handle getting a recordset. Please understand that even if the query does return a single record, it will be returned *as* a recordset, and that may be the problem.

Old Pedant
06-17-2010, 07:56 PM
will give you an example of reasonable use:



....
SET @s = CONCAT('INSERT INTO tree SELECT CHILD_ID, PARENT_ID FROM ', sourceTable);
PREPARE stmt FROM @s;
EXECUTE stmt;
....


<shrug>Not clear to me why you would have multiple "sourceTable"s in the first place, but okay, I'll grant you that code is workable.

But why would you want to make that a FUNCTION???? It doesn't return any value, so it *should* be a SP and should not be a function.

Apothem
06-18-2010, 07:52 AM
What do you mean where I am trying to call it? I'm basically connected as root and did CALL getChapterQuery(...)

When I tried that on phpMyAdmin, it sent me to the home phpMyAdmin page for some reason. When I tried in a normal php file it gave me that error.

saviola
06-18-2010, 04:16 PM
What do you mean where I am trying to call it? I'm basically connected as root and did CALL getChapterQuery(...)

When I tried that on phpMyAdmin, it sent me to the home phpMyAdmin page for some reason. When I tried in a normal php file it gave me that error.

Check your MySQL version.
Mysql version 5.0.X supports stored procedures. PHPmyadmin does not support executing the procedures : 'Call <procedure>'.

NOTE: I'm not sure about latest version...

This will output a list of Stored Procedure names. To see a procedure, run the query:

show create procdure [procedure name from above select]

Here is a PHP script calling sp with out variables.


$rs = mysql_query( "CALL getCountry(1, @userName)" );
$rs = mysql_query( "SELECT @userName" );
while($row = mysql_fetch_assoc($rs))
{
echo $row['@userName'];
}

Old Pedant
06-18-2010, 08:04 PM
But Saviola, he doesn't *HAVE* any OUT parameters in that SP.

I just re-created that SP (different field and table names, but otherwise identical) on my installation of MySQL 5.1 and it worked perfectly. Yes, even including the CALL, directly from the mysql command prompt. (I just use the command line "mysql" and don't use anything like phpMyAdmin, so dunno what else to try.)

Are you using some version other than 5.1? (Actually, 5.1.40, community edition.)

Apothem
06-20-2010, 05:07 AM
I'm using the MySQL via the software called "XAMPP":
http://www.apachefriends.org/en/xampp-windows.html
MySQL 5.1.41 + PBXT engine



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum