MySQL Learning notes-custom functions

MySQL Learning note-Custom function comment syntax:

MySQL server supports 3 kinds of annotation styles:

• From the ' # ' character from the end of the line.

• From the ' – ' sequence to the end of the line. Note that the ' – ' (double dash) annotation style requires a 2nd dash followed by at least one space character (such as spaces, tab, line breaks, and so on). This syntax is slightly different from the standard SQL comment syntax.

• The From/ sequence to the back /sequence. The end sequence is not necessarily in the same row, so the syntax allows comments to span multiple lines.

The following example shows comments in 3 styles:

mysql> SELECT 1+1; # This comment continues to the end of linemysql> SELECT 1+1; -- This comment continues to the end of linemysql> SELECT 1 /* this is an in-line comment */ + 1;mysql> SELECT 1+/*this is amultiple-line comment*/1;

Custom User variables:

• You can first save the value in a user variable and then reference it later, so that you can pass the value from one statement to another. User variables are related to connections. In other words, a client-defined variable cannot be seen or used by other clients. When the client exits, all the variables for that client connection are automatically freed.

• The user variable is in the form @var_name, where the variable name Var_name can be composed of literal numeric characters, '. ', ' _ ' and ' $ ' of the current character set. The default character set is cp1252 (Latin1). You can change the character set with the mysqld –default-character-set option. The user variable name is not case sensitive.
  One way to set up a user variable is to execute the SET statement:

...

For set, you can use = or: = as an assignment. The expr assigned to each variable can be an integer, a real number, a string, or a null value.

You can also assign a value to a user variable by using the statement select instead of set. In this case, the quantifier must be: = instead of =, because in a non-set statement = is treated as a comparison operator:

SET@t1=0@t2=0@t3=0SELECT@t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

Basic ways to customize functions

• 1. Custom Functions
  A user-defined function (user-defined function, UDF) is a way to extend MySQL, using the same functionality as built-in functions.
  Two prerequisites for a custom function:

  • A. Parameters
  • B. return value

  Functions can return any type of value, and they can also accept parameters of these types;
  There is no necessary connection between the parameters of the function and the return value.

• 2. Creating a Custom function
  CREATE FUNCTION function_name ([func_parameter[,...])
  RETURNS {string| integer| real| Decimal| ...}
  Routine_body-function body

  By default, the child program is associated with the current database. To explicitly associate a program with a given database, you can specify its name as Db_name.function_name when creating a subroutine.
  If the subroutine name is the same as the built-in SQL function name, you need to insert a space between the name and the next parenthesis, or a syntax error occurs. You also need to insert the subroutine when you call it later.
  Returns words can only be specified for function, which is mandatory for functions. It is used to specify the return type of the function, and the body of the function must contain a return value statement

• 3. About the function body

  • The function body is composed of legal SQL syntax;
  • The function body can be a simple select or INSERT statement;
  • function body If it is a composite structure, use begin ... End statement;
  • Composite structures can include declarations, loops, and control structures.

Example

• Create a custom function with no parameters:
  This function returns the time of the system in the format set

FUNCTION myTime()RETURNS VARCHAR(30)RETURN DATE_FORMAT(NOW(),‘%Y年%m月%d日 %H点%i分%s秒‘);

• Create a custom function with parameters:
  The function calculates the average of two incoming parameters

CREATE FUNCTION avg(num1 SMALLINT UNSIGNED ,num2 SMALLINT UNSIGNED)RETURNS FLOAT(3,2) RETURN (num1+num2)/2;

BEGIN ... End Compound statement

BEGIN ... End Compound Statement format:

[begin_label:]BEGIN    [statement_list]END[end_label]

The storage subroutine can use the begin ... End Compound statement to contain multiple statements. Statement_list represents a list of one or more statements. Each statement within Statement_list must be terminated with a semicolon (;).

Compound statements can be marked. Unless Begin_label exists, End_label cannot be given, and if both exist, they must be the same.

Using multiple statements requires the client to send a query string containing the statement delimiter; This symbol is handled by the command line client with the delimiter command. Change the end of the query delimiter ";" (e.g. change to "//") make; Can be used in the subroutine body.
For example, in MySQL, execute the following command:

//

Then the symbol at the end of the command becomes "//" instead of ";"

The following example creates a function that inserts data into the TB1 table (data includes username,age) and returns the oldest data of the Age:

• Execute First:

//

• Re-enter the function:

create  < Span class= "Hljs-keyword" >function  adduser   ( Username varchar , age  tinyint   (3)  unsigned ) returns  tinyint   (3)  unsigned  begin  insert  tb1   ( Username,age)  values   (username,age) ; Return (select  Max (age) from  tb1); end  // 

• Change back to end delimiter:

delimiter ;

• Execute function:

select addUser(‘chenjs‘,20);

Delete a statement of a function

Delete a statement of a function

function function_name;

Just write the function name here, the parameters of the functions can not be written out.
For example, delete the function created above addUser (username varchar, age tinyint (3) unsigned), which can be deleted directly with the following statement:

function addUser;

Flow control constructs an if statement

IF search_condition THEN statement_list    ...    [ELSE statement_list]END IF

If a basic conditional construct is implemented. If the search_condition evaluates to True, the corresponding list of SQL statements is executed. If there is no search_condition match, the list of statements in the ELSE clause is executed. Statement_list can include one or more statements.
Note that there is also an if () function, which differs from the IF statement described here:
IF (EXPR1,EXPR2,EXPR3)
If Expr1 is true (Expr1 is not equal to 0 and expr1 is not equal to NULL), then the return value of if () is expr2; Otherwise the return value is EXPR3. The return value of IF () is a numeric value or a string value, depending on the context in which it is located.
For example:

Returns 2 and assigns 2 to the variable @m.

Case statement

Case statement

CASE case_value    WHEN when_value THEN statement_list    ...    [ELSE statement_list]END CASE

Or:

CASE    WHEN search_condition THEN statement_list    ...    [ELSE statement_list]END CASE

The case statement of the stored program implements a complex conditional construct. If the search_condition evaluates to TRUE, the corresponding SQL is executed. If no search criteria match, the statement in the ELSE clause is executed.

• Example
  This example performs a different operation on the number of incoming and outgoing numbers, based on a string that represents the name of the function and a number to be processed (before which the end delimiter has been changed to "//")

Create  function casetest(str varchar (5),num int)returns int  begin Case  str if 'power'  then set @ result=power(num,2);When' Ceil '  Then Set@result=ceil (num); when' floor '  Then Set@result=floor (num); when' Round '  Then Set@result=round (num);Else Set@result=0;End  Case; return (Select@result);End //

The test is as follows:

Loop statement

Loop statement Format

LOOP    statement_listENDLOOP [end_label]

Loop allows repeating execution of a particular statement or group of statements to implement a simple looping construct. Statements within a loop are repeated until the loop is exited, and the exit is usually accompanied by a leave statement.

Loop statements can be labeled. Unless Begin_label exists, End_label cannot be given, and if both are present, they must be the same.

• Leave statements
  Format

LEAVE label

This statement is used to exit any annotated process control constructs. It and begin ... End or loop is used together.

• Iterate statements
  Format

label

Iterate can only appear within loops, REPEAT, and while statements. Iterate means: "Cycle again." ”

For example, it is created using CREATE PROCEDURE instead of CREATE function, because procedure does not need returns and return fields, and function must have returns and return fields:

CREATEPROCEDURE doiterate(p1 INT)BEGIN  label1:LOOP    SET1;    IF10THEN ITERATE label1;     ENDIF;    LEAVE label1;  ENDLOOP label1;  SET @x = p1;END

Repeat statements

Repeat statement format

REPEAT    statement_listUNTIL search_conditionENDREPEAT [end_label]

The statement or statement group within the repeat statement is repeated until search_condition is true.

REPEAT statements can be labeled. Unless Begin_label also exist, End_label can be used, and if both exist, they must be the same.

Using repeat to implement the above program, the program is as follows:

function doRepeat(p1 int)repeat1until p1>10  endrepeat;return p1;end

Test:

While statement

While statement format

WHILEDO    statement_listENDWHILE [end_label]

The statement or statement group within the while statement is repeated until search_condition is true.

The while statement can be labeled. Unless Begin_label also exist, End_label can be used, and if both exist, they must be the same.

Example:

CREATEPROCEDURE dowhile()BEGIN  DECLARE v1 INT DEFAULT 5;  WHILE0DO    SET1;  ENDWHILE;END

• Declare is only used in the begin ... End Compound statement, and must precede any other statement at the beginning of the compound statement. Used to declare a local variable that is not accessible outside the function, and if you want to access a value that must be returned, you should use the CREATE function instead of CREATE procedure, because only the CREATE function can have a return value, and the function changes as follows:

function dowhile()RETURNS intBEGIN  DECLAREDEFAULT5;  WHILE0DO    SET1;  ENDWHILE;RETURN v1;END

MySQL Learning notes-custom functions