Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Regular Coder Apothem's Avatar
    Join Date
    Mar 2008
    Posts
    380
    Thanks
    36
    Thanked 25 Times in 25 Posts

    Procedures and functions

    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?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    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:
    Code:
         ...  
         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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    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.
    Code:
        ...
        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:
    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.
    Last edited by Old Pedant; 06-15-2010 at 10:57 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Regular Coder Apothem's Avatar
    Join Date
    Mar 2008
    Posts
    380
    Thanks
    36
    Thanked 25 Times in 25 Posts
    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.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    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:
    Code:
        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:
    Code:
        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:
    Code:
        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:
    Code:
        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).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    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) .
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    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/...trictions.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.
    Last edited by BubikolRamios; 06-17-2010 at 12:36 AM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    <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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Regular Coder Apothem's Avatar
    Join Date
    Mar 2008
    Posts
    380
    Thanks
    36
    Thanked 25 Times in 25 Posts
    Well, I'm not exactly sure what you two are debating about but I have a problem:
    Code:
    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

  • #11
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    i dont see realy why would use stored proc for that.

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

    Code:
    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:

    Code:
    ....
    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.
    Last edited by BubikolRamios; 06-17-2010 at 02:05 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Quote Originally Posted by BubikolRamios View Post
    will give you an example of reasonable use:

    Code:
    ....
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #14
    Regular Coder Apothem's Avatar
    Join Date
    Mar 2008
    Posts
    380
    Thanks
    36
    Thanked 25 Times in 25 Posts
    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.

  • #15
    New Coder
    Join Date
    Jun 2010
    Posts
    20
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Apothem View Post
    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:
    Code:
    show create procdure [procedure name from above select]
    Here is a PHP script calling sp with out variables.
    PHP Code:
    $rs mysql_query"CALL getCountry(1, @userName)" );
    $rs mysql_query"SELECT @userName" );
    while(
    $row mysql_fetch_assoc($rs))
    {
         echo 
    $row['@userName'];

    Nothing's imposible imagination is everything!
    Database Benchmark Software (GNU GPL) | world's fastest database


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •