MySQL中的匿名函数？

I wrote a query for a client that used a stored procedures. Turns out they can't grant my user write access to the database, so I can't store my procedure. Can I turn my stored procedure into an anonymous function inside my query, as to keep the functionality without using a stored procedure?

$establishFunction = mysql_query("DROP FUNCTION IF EXISTS fn_distance_cosine;
CREATE FUNCTION fn_distance_cosine (
    latitude_1 DOUBLE,
    longitude_1 DOUBLE,
    latitude_2 DOUBLE,
    longitude_2 DOUBLE
)
RETURNS DOUBLE
DETERMINISTIC
SQL SECURITY INVOKER
RETURN ACOS(
      SIN(RADIANS(latitude_1)) * SIN(RADIANS(latitude_2))
      + COS(RADIANS(latitude_1)) * COS(RADIANS(latitude_2))
      * COS(RADIANS(longitude_2 - longitude_1))
    ) * 3956.547;
");

mysql_query("SET @input_lat :=" . $lat . ";");
mysql_query("SET @input_lon :=" . $lon . ";");

mysql_query("SET @max_latitude := @input_lat + DEGREES(50.0/3956.547);");
mysql_query("SET @min_latitude := @input_lat - DEGREES(50.0/3956.547);");
mysql_query("SET @max_longitude := @input_lon + DEGREES(50.0/3956.547/COS(RADIANS(@input_lat)));");
mysql_query("SET @min_longitude := @input_lon - DEGREES(50.0/3956.547/COS(RADIANS(@input_lat)));");

if($result = mysql_query("SELECT
      *,
      fn_distance_cosine(lat, lon, @input_lat, @input_lon) AS distance_in_miles,
      COUNT(*) AS store_number_count
    FROM stores
    JOIN items_stores
    ON stores.store_number=items_stores.store_number
    WHERE `lat` BETWEEN @min_latitude AND @max_latitude
      AND `lon` BETWEEN @min_longitude AND @max_longitude
    GROUP BY stores.store_code
    HAVING store_code_count > 1
    ORDER BY distance_in_miles
    LIMIT 10;
    ")){  
        while ($r = mysql_fetch_assoc($result)){
        $rows[] = $r;
    }
}else{
    //failed.
    echo (mysql_error ());
}

print json_encode($rows);

If you can't do stored procedures, you probably can't do functions either because they won't have the security rights for that either.

Option #1 - Have their own DBA vet the SQL statements and then make the modification to the database to house the stored procedure.

Option #2 - Your other option is to move that code out of the database and into the PHP. So yes, you can move it into the PHP but it won't be nearly as fast or efficient as having the database engine do it.

Can you get a separate database on the same mysql server? Then you might be able to declare the procedure in there and then use it in your query by explicitly prefixing with the database name. I'm not entirely sure if it'll actually work, but I would guess it does.

No you can't have anonymous functions. If your client can't grant you proper permissions to add this function, then why did they ask you to write one? If your DB user account can't install the function, then surely that have someone with proper user account to be able to create this function for you based on your SQL.

Also why a function instead of a stored procedure? You could put all of that logic you are doing in several DB queries in a single call like:

call give_me_my_data(lat, long)

It seems odd that you would use some db-based logic and some php-based logic (with a number of unnecessary queries) to get at the data you need, rather than just go all the way and define a stored procedure that would clean up your PHP code to make only a single query.