MySQL Basics 08 Functions

The main contents of this article are as follows:

1. syntax structure of functions

2. Examples of functions

2.1. the simplest function

2.2. example of returning query results

3. the difference between a function and a stored procedure

3.1. differences between parameters and return values

3.2. function does not allow commit transactions

3.3. feature limitations of functions

1. syntax structure of a function

The syntax structure of a Function procedure is as follows:

CREATE

[definer = {User | Current_User}]

FUNCTION sp_name ([func_parameter[,...])

RETURNS type

[Characteristic ...] Routine_body

The various sections are described below:

Definer: The user name the creator of the function belongs to.

Parameters of the function:

Proc_parameter:

Param_name type

the parameters of the function can only be the parameter in the type.

Parameter type:

Type

Any valid MySQL data type

return value:

RETURNS type

Characteristics:

Characteristic:

COMMENT ' String '

| LANGUAGE SQL

| {CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}

| SQL SECURITY {definer | INVOKER}

function Body:

Routine_body:

Valid SQL Routine statement

which

(1) COMMENT definition annotations;

(2) LANGUAGE SQL

(3) Deterministic

Deterministic: OK.

Not deterministic: indeterminate.

(4) CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA:

(a) CONTAINS sql: function contains SQL statement;

(b) No SQL: SQL statements are not included in the function .

(c) READS SQL data: A function contains SQL statements that read data .

(d) Mofifies SQL Data: The SQL statement that contains the modified data in the function .

(5) SQL SECURITY {definer | INVOKER}:

assuming that the function is defined by A and the caller is b, the function is executed :

Definer: First check if b has execute function permission, and then check whether a has permission to access the related data table.

INVOKER: First check if b has execute function permission, and then check whether b has permission to access the related data table.

2. Examples of functions

2.1. The simplest of functions

An example of the simplest function is as follows:

Drop function if exists sf_p1;

Delimiter $$

Create function Sf_p1 ()

Returns integer

Deterministic

return 100;

$$

delimiter;

Use The deterministic feature is limited because MySQL has binary log enabled and binary log , The function cannot be created successfully if a feature restriction such as deterministric is not used.

Call this function:

Mysql> select SF_P1 ();

+---------+

| SF_P1 () |

+---------+

| 100 |

+---------+

1 row in Set (0.00 sec)

2.2. example of returning query results

Drop function if exists sf_p1;

Delimiter $$

Create function Sf_p1 ()

Returns integer

Deterministic

Begin

declare x integer;

Select COUNT (*) into x from T1;

return x;

End

$$

delimiter;

Call Function:

Mysql> select SF_P1 ();

+---------+

| SF_P1 () |

+---------+

| 1 |

+---------+

1 row in Set (0.00 sec)

3. the difference between a function and a stored procedure

3.1. the difference between a parameter and a return value

(1) the arguments to the function are not syntactically allowed inch and the out as well INOUT modifier; semantically support only inch The type parameter. Stored procedures Support both in,out and INOUT Three modifiers, both syntactically and semantically .

(2) The function must have a return value definition, i.e. RETURNS A function must also have at least one RETURN statement to return a value ; The function must also have and have only one return value. Stored procedures do not have the concept of return values; stored procedures need to return data through parameters of out or INOUT type; stored procedures can define N parameters of an out or INOUT type,n>=0.

3.2. function does not allow commit transactions

The function does not allow an explicit commit transaction (Start transaction and commit statements ) , the function also does not allow implicit commit transactions (TRUNCATE TABLE and other statements ) , while stored procedures do not have this limitation.

The TRUNCATE TABLE statement for an implicitly committed transaction exists in the following function , so this function cannot be created successfully.

Drop function if exists sf_p1;

Delimiter $$

Create function Sf_p1 ()

Returns varchar (100)

Not deterministic

Begin

TRUNCATE TABLE T1;

INSERT into T1 values (@ @hostname);

INSERT into T1 values (UUID ());

INSERT into T1 VALUES (CAST (rand () as Char));

Return ' 1 ';

End

$$

delimiter;

ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.

Workaround:

Remove The TRUNCATE statement.

3.3. feature limitations for functions

When you enable the Binary Log , the function must use some feature restrictions, otherwise the function cannot be created successfully. There is no such restriction on stored procedures.

When you enable the Binary Log, it is possible to have a master-slave copy, it is necessary to ensure that the return value of the function is exactly the same in master and slave , Failure to do so will result in inconsistent master-slave data.

therefore, when you enable the In the caseof Binary Log,MySQL requires that the function must use some feature restrictions, otherwise it is not allowed to create the function.

These limits can be one of the following three:

Deterministic: The determined value.

No SQL: function does not execute any SQL.

READS SQL Data: The function simply reads some data.

If the current user has SUPER Privilege, there is no such limit.

if the global variable log_bin_trust_function_creators is set to on, there is no such restriction. This global variable defaults to OFF.

Mysql> Show variables like '%log_bin_trust% ';

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | OFF |

+---------------------------------+-------+

1 row in Set (0.02 sec)

Drop function if exists sf_p1;

Delimiter $$

Create function Sf_p1 ()

Returns varchar (100)

Begin

DECLARE x varchar (100);

Set x = ' 001 ';

return x;

End

$$

delimiter;

ERROR 1418 (HY000): This function has none of the deterministic, NO SQL, or READS SQL DATA in its declaration and binary Loggi NG is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Workaround:

Increase Features such as determinstic or NO SQL .

Drop function if exists sf_p1;

Delimiter $$

Create function Sf_p1 ()

Returns varchar (100)

Deterministic

Begin

DECLARE x varchar (100);

Set x = ' 001 ';

return x;

End

$$

delimiter;

Or:

Drop function if exists sf_p1;

Delimiter $$

Create function Sf_p1 ()

Returns varchar (100)

No SQL

Begin

DECLARE x varchar (100);

Set x = Cast (rand () as Char);

return x;

End

$$

delimiter;

MySQL Basics 08 Functions