Self-defined function programming in MySQL

1. Syntax1.1 New function

Create function function_name (parameter list) returns return value type function Body

(1) The function name should be a valid identifier and should not conflict with an existing keyword .

(2) 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.

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

(4) Return value class type.

(5) The function body consists of a number of available MySQL statements , Process Control , variable declaration and other statements.

(6) multiple statements should be contained using the BEGIN END Statement block .

Note that you must have a return value statement.

1.2 Delete

if exists function_name;

1.3 views

function  like  Create function function_name;

1.4 Modifications

Alter  function function_name option;

2. Program Control2.1 If statement

IF search_condition thenstatement_list [ELSEIF search_condition then statement_list] ... [ELSE statement_list] END IF;

2.2 Case Statement

 Case Case_value  when When_value then Statement_list [whenWhen_value then statement_list] ... [ELSE statement_list] END  Case

2.3 While loop

[Begin_label:] While search_condition do 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 .

2.4 Exit Loop

(1) exit the entire loop :leave equivalent to break

(2) exit current loop :iterate equivalent to continue

(3) Decide which loop to exit by exiting the label .

3. 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 ();

Self-defined function programming in MySQL