maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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-subclause
https://mariadb.com/kb/en/create-procedurefunctionmethod-statement/#sql-data-access-indication-subclause

Regards,
Sergei