CODEWITHSUNDEEP
mysqlstored-functions
Jan
Jan

Reputation: 3154

MySQL stored function: CONTAINS SQL or NO SQL?

For the stored functions below, which is the correct flag: CONTAINS SQL or NO SQL?

CREATE FUNCTION BigIntHash(str VARCHAR(255)) RETURNS BIGINT UNSIGNED DETERMINISTIC
RETURN CONV(SUBSTRING(CAST(SHA(str) AS CHAR), 1, 15), 16, 10)

and

CREATE FUNCTION upi(a VARCHAR(14), b INT(8) UNSIGNED, c VARCHAR(13)) RETURNS BIGINT UNSIGNED DETERMINISTIC
RETURN IF(a IS NULL, 
  IF(b IS NULL, 
    IF(c IS NULL, 
      NULL, 
      BigIntHash(CONCAT("a-", a))
    ), 
    BigIntHash(CONCAT("b-", b))
  ), 
  BigIntHash(CONCAT("c-", c))
)

The definitions are on http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html but I am still not sure:

Upvotes: 5

Views: 2503

Answers (2)

tecdoc ukr net
tecdoc ukr net

Reputation: 134

I found the answer in the MariaDB documentation CREATE FUNCTION Syntax.

CONTAINS SQL means that the function contains at least one SQL statement, but it does not read or write any data stored in a database. Examples include SET or DO.

NO SQL means nothing, because MariaDB does not currently support any language other than SQL.

Upvotes: 0

Mchl
Mchl

Reputation: 62395

Both are NO SQL as they do not access data in tables, cursors or variables.

For reference on what constitutes an SQL statement see: http://dev.mysql.com/doc/refman/5.1/en/sql-syntax.html

Notice that functions like CONV() SHA() or CONCAT() are not mentioned in this chapter.

Upvotes: 1

Related Questions