MySQL's function

The difference between a function and a stored procedure is that the function must have a return value, while the stored procedure does not. A function has much more parameter types than stored procedure parameters.

function operations include creating functions, viewing functions, updating functions, and deleting functions.

1. Create a function

The CREATE function is implemented by the SQL statement create function, which has the following syntax:

CREATE FUNCTION function _name ([function_paramter[,... ]])    [characteristic ... ] Routine_body

The procedure_name parameter represents the name of the function to be created, Procedure_paramter represents the function's arguments,characteristic represents the function's attributes, routine_body the SQL statement code that represents the function,
You can use the BEGIN ... End to flag the start and end of the SQL statement.

The syntax for each parameter in Procedure_name is as follows:

Procedure_name type

Each parameter consists of two parts, the Param_name parameter is the parameter name of the function, and the type parameter specifies the function's parameter type, which can be any data type of the MySQL database.

The characteristic parameter has multiple values. The values are described as follows:

LANGUAGE sql: Description The routine_body part is made up of SQL language statements, which are the default languages of the database system.

[NOT] Deterministic: Indicates whether the execution result of the function is deterministic. Deterministic indicates that the result is deterministic. The same input will get the same output each time the function is executed. Not deterministic indicates that the result is indeterminate, and the same input may get different output. By default, the result is non-deterministic.

{CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}: Indicates the limitations of the use of SQL statements by subroutines. CONTAINS SQL indicates that a subroutine contains SQL statements, but does not contain statements that read or write data; No SQL indicates that the subroutine does not contain SQL statements; READS SQL data represents the statements in the subroutine that contain read data; modifies SQL Data represents a statement in a subroutine that contains write data. By default, the system is specified as contains SQL.

SQL SECURITY {definer | INVOKER}: Indicates who has permission to execute. Definer means that only the definition can execute itself; Invoker indicates that the caller can execute it. By default, the system-specified permissions are definer.

COMMENT ' string ': Comment information.

Examples are as follows:

DELIMITER $$CREATE FUNCTIONFunc_employee_sal (empnoINT( One))    RETURNS DOUBLE(Ten,2) COMMENT'Query the salary of an employee'BEGIN    RETURN(SELECTSal fromT_employeeWHERET_employee.empno=empno);END$ $DELIMITER;

1.2 Expressions about functions

expressions, like expressions in other high-level languages, are composed of variables, operators, and process controls.

1.2.1 Action variables

A. Defining variables

Use the DECLARE keyword to define a variable. The basic syntax for defining variables is as follows:

DECLARE  var_name[,... ]  type  [DEFAULT value      

The DECLARE keyword is used to declare a variable, the Var_name argument is the name of the variable, and you can define multiple variables at the same time; the type parameter is used to specify the types of the variables, the default value clause sets the variable defaults to value, The default value is null.

Examples are as follows:

DECLARE  my_sql    INT  

B. Assignment variables

Use the Set keyword to assign a value to a variable. The basic syntax for the SET statement is as follows:

SET  [, var_name = expr   

The SET keyword is used to assign a value to a variable; the Var_name parameter is the name of the variable, and the expr parameter is an assignment expression. A SET statement can assign values to multiple variables at the same time, separating the assignment statements of each variable with commas.

Examples are as follows:

SET  

You can also use Select ... The INTO statement assigns a value to the variable. The basic syntax is as follows:

SELECT  col_name[,... ] into  var_name[,... 

Where the Col_name parameter represents the field name of the query, the Var_name parameter is the name of the variable, the table_name parameter refers to the name of the table, and the condition parameter refers to the query condition.

It is important to note that when you assign a query result to a variable, the result of the query statement is only a single row.

Examples are as follows:

SELECT  d_id to    my_sql from    employee  wehre  id=  

1.2.2 Operating conditions

Defining conditions and handlers is a pre-defined problem that you might encounter during program execution. You can also define ways to resolve these problems in your handlers. This way you can anticipate potential problems in advance and propose solutions. This will enhance the ability of the program to handle problems, and prevent the program from stopping abnormally. In MySQL, the conditions and handlers are defined by the DECLARE keyword.

A. Defining conditions

Use the DECLARE keyword to define the condition. The basic syntax is as follows:

DECLARE  condition_name  condition for    condition_value  condition_value:  [  VALUE    

Where the Condition_name parameter represents the name of the condition, the Condition_value parameter represents the type of condition, and the Sqlstate_value parameter and Mysql_error_code parameter can represent the MySQL error. For example, in error 1146 (42S02), the Sqlstate_value value is the 42s02,mysql_error_code value is 1146.

Examples are as follows:

Define "error 1146 (42S02)", which is named Can_not_find. It can be defined in two different ways, with the following code:

method One: Use Sqlstate_value     DECLARE  can_not_find  CONDITION for    SQLSTATE  ' 42s02'//        

B. Defining handlers

Use the Declare keyword to define handlers. The basic syntax is as follows:

DECLARE Handler_type HandlerFor Condition_value[,... ] Sp_statement Handler_type: continue | exit |[value] sqlstate_value | condition_name |  sqlwarning | not FOUND | SQLEXCEPTION | mysql_error_code        

Where the Handler_type parameter indicates how the error is handled, and the parameter has 3 values. These 3 values are continue, exit, and Undo, respectively. Continue indicates that an error has not been processed, continues to execute downward, exits immediately after an error has been encountered, and Undo indicates that the operation was not supported until the error was encountered and is temporarily unsupported in MySQL.

Note: Typically, errors encountered during execution should immediately stop executing the following statement and recall the previous action. However, the undo operation is not currently supported in MySQL. Therefore, it is best to perform an exit operation if an error is encountered. If the error type can be predicted beforehand and processed accordingly, the continue operation can be performed.

The Condition_value parameter indicates the type of error, which has 6 values. Sqlstate_value and Mysql_error_code are the same meaning as in conditional definitions. Condition_name is the condition name defined by declare. SQLWarning represents all sqlstate_value values that begin with 01. Not found represents all sqlstate_value values that begin with 02. SqlException represents all sqlstate_value values that are not captured by sqlwarning or not found. Sp_statement represents the execution statements of some functions or functions.

Examples are as follows:

Here are a few ways to define the handler. The code is as follows:

    //Method One: Capture Sqlstate_valueDECLARECONTINUE HANDLERFor SQLSTATE‘42s02‘SET@info=‘CAN not FIND‘;//Method Two: Capture Mysql_error_codeDECLARECONTINUE HANDLERFor1146SET@info=‘CAN not FIND‘;//Method Three: Define the condition first, and then call theDECLARE Can_not_find CONDITIONFor1146;DECLARECONTINUE HANDLERFor Can_not_findSET@info=‘CAN not FIND‘;//Method Four: Use SQLWarningDECLAREEXIT HANDLERFor sqlwarningSET@info=‘ERROR‘;//Method Five: Use not FOUNDdeclare exit HANDLER  For not FOUND set  @info Span style= "color: #808080;" >= ' can not Find "; //exit HANDLER for SQLEXCEPTION set  @info = "error           

The above code is 6 ways to define handlers. The first method is to capture the Sqlstate_value value. If you encounter a Sqlstate_value value of 42S02, perform a continue operation and output "CAN not FIND" information. The second method is to capture the Mysql_error_code value. If a mysql_error_code value of 1146 is encountered, the continue operation is performed and the "CAN not FIND" message is output. The third method is to define the condition before calling the condition. The can_not_find condition is defined first, and the continue operation is performed with a 1146 error. The fourth method is to use SQLWarning. SQLWarning captures all Sqlstate_value values starting with 01, then executes the exit operation and outputs the "ERROR" information. The fifth method is to use not FOUND. Not found captures all sqlstate_value values starting with 02, then performs the exit operation and outputs "CAN not FIND" information. The sixth method is to use SqlException. SqlException captures all sqlstate_value values that are not captured by the sqlwarning or not found, and then performs the exit operation and outputs the "ERROR" information.

2. View functions

2.1 Viewing function state information through the show function status statement

The syntax is as follows:

[like' pattern '];

For example, the Query function object func_employee_sal:

FUNCTION  like ' Func_employee_sal ';

2.2 Viewing function definition information through the show CREATE function statement

The syntax is as follows:

CREATE FUNCTION Func_name;

For example, the Query function object func_employee_sal:

CREATEfunc_employee_sal;

3. Updating functions

The modified function is implemented by the SQL "ALTER function ", which has the following grammatical form:

ALTER FUNCTION func_name     [characteristic ... ]

The value can only be the following value compared to the parameter that defines the function:

| modifies SQL DATA} | INVOKER} 'string'     

4. Delete a function

To delete a function from the drop statement:

FUNCTION Func_name;

MySQL's function