MySQL Learning 20: operator and function custom functions

Introduction to a custom function (1) Custom function definition

The user-defined function (user-defined function,udf) is a way to extend the MySQL database using the same usage as built-in functions.

(2) Two necessary conditions for a custom function 1) Parameters: can have 0 or more.

2) return value: Only one return value can be returned.

Considerations for using Custom functions

1) All functions have return values, but they do not necessarily have parameters.

2) There is no intrinsic connection between the parameters of the function and the return value.

3) functions can return any type of value, and they can also receive parameters of these types.

(3) Creating a custom function to create a syntax structure for a custom function:

CREATE FUNCTION function_name RETURNS {string| integer| real| DECIMAL} routine_body;

(4) function Body

1) The function body is composed of valid SQL statements.

2) The function body can be a simple select or INSERT statement.

3) function Body If the composite structure is used brgin ... End statement.

4) Composite structures can contain declarations, loops, and control structures.

Two creating a custom function with no parameters

Instance:

View current datetime and format current time

SELECT now ();

SELECT Date_format (now (), '%y year%m month%d day%h point:%i min:%s seconds ');

We are not accustomed to the first of the above, the second formatted time we are more agreeable to the usage date time. Go to the grid every time.

Date and time can be cumbersome, so we consider customizing the process as a function.

Encapsulate the above procedure as a function

CREATE FUNCTION F1 () RETURNS VARCHAR (() RETURN Date_format (now (), '%y year%m month%d day%H

Point:%i minutes:%s seconds ');

Calling functions

SELECT F1 ();

Three Create a custom function with parameters

Customize a function that asks for an average of two numbers, requiring the result to retain two decimal places.

CREATE FUNCTION F2 (num1 SMALLINT unsigned,num2 SMALLINT UNSIGNED) RETURNS FLOAT (10,2)

UNSIGNED RETURN (num1+num2)/2;

Calling functions

SELECT F2 (10,15);

Four creating a custom function with a composite structure function body

Customizes a function that returns a record of the last inserted data table and returns the last inserted ID number

Use T1;

SELECT * from Test;

CREATE FUNCTION adduser (username VARCHAR) RETURNS INT UNSIGNED BEGIN INSERT

Test (username) VALUES (username); RETURN last_insert_id (); END;

The reason for the above error is that the MySQL statement ending symbol error, our usual MySQL statement is to end, but the composite structure includes the plug

Statements, query statements, and so on, and each statement will end, so the client of the MySQL database will only recognize the first;

content no longer read, resulting in a statement error.

We make the following changes:

Modify the end symbol of a statement

DELIMITER//

Creating a Custom function

CREATE FUNCTION adduser (username VARCHAR) RETURNS INT UNSIGNED BEGIN INSERT

Test (username) VALUES (username); RETURN last_insert_id (); end//

DELIMITER;

Calling functions

SELECT adduser (' Lee ');

SELECT * from Test;

search for five custom functions

Find a Custom function

SHOW FUNCTION status\g;

six Delete a function

To delete a function's syntax structure:

DROP FUNCTION [IF EXISTS] function_name;

MySQL Learning 20: operator and function custom functions