Talking about mysql user-defined functions and mysql Functions

Talking about mysql user-defined functions and mysql Functions

To customize the number of mysql rows

DELIMITER $ drop function if exists 'onlinefunction '$ create function 'onlinefunction' (rrrr VARCHAR (50) returns varchar (255) BEGINIF (rrrr = 'online ') then return 'online'; end if; END $ DELIMITER;

The first line of DELIMITER defines an end identifier. because MySQL uses a semicolon as the end character of an SQL statement by default, and a semicolon is used inside the function body, it will conflict with the default SQL end character, therefore, you must first define another symbol as the SQL Terminator. If this definition is not added...

Error code: 1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end' at line 1

The second line is to delete the class with the same name, otherwise it will...

Error code: 1304 FUNCTION onlineFunction already exists

Row 3 function name, function variable, and return type

The fourth line, begin, is the start and corresponds to end $.

The fifth line is the if judgment Statement, in the format

if(...) then....;elseif....;else.....;end if;return ..;

Sometimes mysql cannot create a UDF because function 2 is not enabled.

Enter the show variables like '% func %' command.

The status of log_bin_trust_function_creators is displayed. If it is OFF, the UDF function is disabled.

Enter the command set global log_bin_trust_function_creators = 1;

You can enable the UDF function for log_bin_trust_function_creators.

However, this setting is a temporary solution, because the status changes to OFF after mysql is automatically restarted, so you need

Add the "-- log-bin-trust-function-creators = 1" parameter when the service is started.
Or add log-bin-trust-function-creators = 1 in the [mysqld] section of my. ini (my. cnf.

How do I write mysql user-defined functions?

Mysql> create function HelloWorld4 ()
-> Returns varchar (20)
-> BEGIN
-> RETURN 'Hello World! ';
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

Mysql> select HelloWorld4 ()//
+ --------------- +
| HelloWorld4 () |
+ --------------- +
| Hello World! |
+ --------------- +
1 row in set (0.00 sec)

If you have other databases, it will be helpful to look at the reference manual for writing the SQL stored procedure.

Pan.baidu.com/...715080

How does MySQL test user-defined functions?

I don't know the specific situation of your table. I will give an example:
-- Create a test table
Create table tb12
(Id int identity (1, 1) not null,
Zl int null,
Yf decimal (8, 2) null
)
Go
-- Add Test Data
Insert into tb12
Select 80, null
Union all
Select 105, null
Union all
Select 5000, null
Union all
Select 6000, null
Go

Select * from tb12
----------------------------------
Id zl yf
--------------------------------
1 80 NULL
2 105 NULL
3 5000 NULL
4 6000 NULL

(The number of affected rows is 4)

-- Drop FUNCTION funship.pdf
-- Create the built-in Table value function funship.pdf for calculating postage
Create function funship.pdf ()
RETURNS TABLE
AS
RETURN (SELECT id, zl, yf =
Case when zl <= 100 then zl * 0.3
When zl between 101 and 5000 then zl * 0.15
Else zl x 0.12
End
FROM tb12)
-- Call funshipenders to return the following result set:
SELECT *
FROM funship.pdf ()
-------------------------------------------
Id zl yf
-------------------------------------
1 80 24.00
2 105 15.75
3 5000 750.00
4 6000 720.00

(The number of affected rows is 4)