MySQL Custom function

Syntax:

New:

Create function function_name (argument list) returns return value type

function body

The function name, which should be a valid identifier, and should not conflict with an existing keyword.

A function should belong to a database, you can use the form of Db_name.funciton_name to execute the database that the current function belongs to, otherwise the current database.

The parameter part, consisting of the parameter name and the argument type.

return value class type

The function body consists of a number of available MySQL statements, flow control, variable declaration and other statements.

Multiple statements should be contained using the BEGIN END statement block.

Note that you must have a return value statement.

Delete:

Dropfunction if existsfunction_name;

View:

Show function status like ' Partten '

Show Create functionfunction_name;

Modify:

Alter functionfunction_name function option.

Example:

Hello world!

IF Statement

IF search_conditionThen

Statement_list

[ELSEIF search_conditionthenstatement_list]

...

[ELSE statement_list] ENDIF;

Case Statement

Case Case_value

When when_valuethenstatement_list

[When when_value thenstatement_list]

...

[ELSE statement_list]

END case;

Cycle:

While

[Begin_label:] Whilesearch_conditiondo

Statement_list

END while [End_label];

If you need to terminate the while loop prematurely within the loop, you need to use a label; the label needs to appear in pairs.

Exit loop

Exit the entire loop leave equivalent to break

Exiting the current loop iterate equivalent to continue

The Exit tab determines which loop to exit.

Variable declaration:
Grammar:
DECLARE var_name[,...] Type [DEFAULT value]
This statement is used to declare a local variable. To provide a default value for a variable, include one of the defaults clauses. The value can be specified as an expression and does not need to be a constant. If there is no default clause, the initial value is null.
Use
Word order assigns a value to a variable using the set and select INTO statements.

Note that a global variable (a user-defined variable) can be used within a function @XXX global variables are not declared and can be used directly @xxx.

Example: Get the maximum number of students in the current class.

Reference Student Table

CREATE TABLE Join_student (

stu_id int NOT NULL auto_increment,

Stu_no Char (10),

class_id int NOT NULL,

Stu_name varchar (10),

Stu_info text,

Primary KEY (STU_ID)

);

Calculate new number

drop function if Existssno;

Delimiter $$ #在包含有语句块时 can replace the statement terminator ";" To "$$"

Create function sno (c_id int) returns char (10)

Begin

Declare Last_no char (10); #声明一个局部变量 is used to save the current maximum number, if none is null

Declare class_name char (10);

Select Stu_no from Join_student where class_id=c_id order by stu_no desc limit 1 to last_no;

If last_no is null then #如果为空代表当前班级没有学生 starting from 1, get class name

Return concat ((select C_name from Join_class where id=c_id into class_name), ' 001 '); The role of the #concat () function is to concatenate strings.

Else

Return Concat (Left (last_no,7), Lpad (right (last_no,3) + 1, 3, ' 0 '));

End If;

#return @last_no;

End

$$

delimiter;

Randomly get student names.

Drop function if exists sname;

Delimiter $$

Create function Sname () returns char (2)

Begin

Declare first_name char (+) Default ' Zhou Chansunli Lingwei romanization ';

Declare last_name char (TEN) The default ' methyl-N-propyl-Xing ';

Declare Full_name char (2);

Set Full_name=concat (SUBSTRING (First_name,floor (rand () *16+1), 1), SUBSTRING (Last_name,floor (rand () *10+1), 1));

return full_name;

End

$$

delimiter;

========================================================================================

MySQL common built-in functions

numeric functions

ABS (X), absolute ABS (-10.9) = 10

Format (x,d), formatting the value of the thousand-digit format (1234567.456, 2) =1,234,567.46

Ceil (X), rounding up ceil (10.1) = 11

Floor (X), pull down floor (10.1) = 10

Round (X), rounding off the entire

MoD (m,n) m%n M mod N seek remainder 10%3=1

Pi (), Get pi

Pow (m,n) m^n

Sqrt (X), arithmetic square root

Rand (), random number

TRUNCATE (x,d) intercept D-Decimal

Time-Date function

Now (), Current_timestamp (); Current date Time

Current_date (); current date

Current_time (); Current time

Date (' Yyyy-mm-dd hh;ii:ss '); Get date part

Time (' Yyyy-mm-dd hh;ii:ss ');

Date_format (' Yyyy-mm-dd hh;ii:ss ', '%d%y%a%d%m%b%j ');

Unix_timestamp (); Get Unix timestamp

From_unixtime ();//Get time from timestamp

String functions

Length (string)//string, bytes

Char_length (String)//string number of characters

SUBSTRING (str, position [, length])//starting with the position of STR, take the length of characters

Replace (str, SEARCH_STR, REPLACE_STR)//replaces SEARCH_STR with REPLACE_STR in str

INSTR (string, substring)//Returns the position of the first occurrence of the substring in string

CONCAT (string [,...])//connection string

CHARSET (str)//return string character set

LCASE (String)//convert to lowercase

Left (string, length)//The length of the character from string2

Load_file (file_name)//read content from File

LOCATE (substring, string [, start_position])//Same as InStr, but can specify start position

Lpad (string, length, pad)//repeat pad to start with string until string length

LTRIM (String)//Remove front-end spaces

REPEAT (String, count)//Repeat Count times

Rpad (string, length, pad)//after STR with pad, until length

RTRIM (String)//Remove back-end spaces

STRCMP (string1, string2)//character comparison two string size

Process functions:

case when [Condition]then result[when [condition]then result ...] [ELSE result] END Multi-Branch

IF (EXPR1,EXPR2,EXPR3) dual branch.

Aggregation functions

Count ()

Sum ();

Max ();

Min ();

AVG ();

Group_concat ()

Other common functions

Md5 ();

Default ();

MySQL Custom function