Hi, Federico! On Sep 02, Federico Razzoli wrote:
Ciao,
I wanted to add some info in the CREATE PROCEDURE page in the KB, but I think that some info about CREATE PROCEDURE clauses miss both in the kb and in the MySQL manual.
* [NOT] DETERMINISTIC. From MySQL docs: "A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise." What does this exactly mean for procedures? OUT and INOUT parameters? Resultsets returned by the procedure? Both?
It doesn't really matter. For stored functions, optimizer uses the value of [NOT] DETERMINISTIC to optimize function calls. But for procedures it's not used for anything, as far as I know.
* NO SQL: As far as I understand, even SET is a SQL statement. I guess that a "no sql" function is something like CREATE FUNCTION x(n INT) RETURNS INT BEGIN RETURN n*2; END;
Not quite. As far as I understand, "NO SQL" means that there's no SQL in the routine *at all*. Like,it might be written on C or ADA or whatever sql standard specifies: <language clause> ::= LANGUAGE <language name> <language name> ::= ADA | C | COBOL | FORTRAN | M | MUMPS | PASCAL | PLI | SQL It's particularly important if a routine has the EXTERNAL clause, instead of the BEGIN ... END body. But MariaDB does not support EXTERNAL or any other LANGUAGE besides SQL.
I'm sorry if this info is already written somewhere, but I really wasn't able to find it - and I feel that the KB should specify things like these.
It's only on the SQL-99 Complete, Really pages: https://mariadb.com/kb/en/create-procedurefunctionmethod-statement/#language... https://mariadb.com/kb/en/create-procedurefunctionmethod-statement/#sql-data... Regards, Sergei