MySQL Advanced learning Note five: the creation of MySQL functions! (Video serial Number: Advanced _13)

Knowledge Point six: Creation of MySQL functions (13)

Built-in functions:

Custom functions:

　　First, check to see if the function that created the function has been turned on:

SHOW VARIABLES like '%fun% ';

　　If the value of the variable is off, you need to turn on

Set global Log_bin_trust_function_creators=1;

　　syntax for creating a function:

CREATE function name (variable 1, variable 2 ...)

RETURNS Data types

BEGIN

...... Program code to execute

RETURN data;

END;

1 --Default Data2 CREATE TABLE IF  not EXISTSUserInfo (3Useridint( One) not NULLAuto_incrementKEY,4Usernamevarchar( -)DEFAULT NULL,5Userpwdvarchar( -)DEFAULT NULL,6 );7 8 INSERT  intoUserInfoVALUES(1,'Admin','123'),(2,'Test','Test'),(4,'yy','Ko'),(5,'yy','Ko');

Default Data

1 --13 Creation of functions2 --ask for two numbers and3DELIMITER//4 CREATE  FUNCTIONFun_add (AintBint)5 RETURNS int6 BEGIN7     RETURNA+b;8 END 9 //Ten  One DELIMITER; A SELECTFun_add (1,2); -  -SHOWCREATE FUNCTIONFun_add; the  -  - --User Login Detection -DELIMITER// + CREATE  FUNCTIONFun_login (unameVARCHAR( -), PWDVARCHAR( -)) - RETURNS VARCHAR( -) + BEGIN A     DECLARERowresultint DEFAULT 0; at     SELECT COUNT(*) intoRowresult fromUserInfowhereUsername=uname; -     IFRowresult=0  Then -         RETURN 'user does not exist! '; -     END IF; -     SELECT COUNT(*) intoRowresult fromUserInfowhereUsername=Uname andUserpwd=pwd; -     IFRowresult=0  Then in         RETURN 'Wrong password! '; -     ELSE to     RETURN 'Landing Success! '; +     END IF; - END  the // *  $ DELIMITER;Panax Notoginseng SELECTFun_login ('Admin','123');

creating tests for functions

Management of functions:

To view the functions under the database:

SELECT * from Mysql.func; or SHOW FUNCTION status;

To view function contents:

SHOW CREATE FUNCTION Fun_name;

To delete a function:

DROP FUNCTION IF EXISTS fun_name;

MySQL Advanced learning Note five: the creation of MySQL functions! (Video serial Number: Advanced _13)