Custom function programming in mysql

Custom function programming in mysql

Syntax:

New:

Create function function_name (parameter list) returns Return Value Type

Function body

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

A function should belong to a database. You can use db_name.funciton_name to execute the database to which the current function belongs. Otherwise, it is the current database.

The parameter section consists of the parameter name and parameter type.

Return Value Type

The function body consists of multiple available mysql statements, process control, and variable declaration statements.

Multiple statements should be included in the begin end statement block.

Note that there must be 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!

IFStatement

IFSearch_conditionTHEN

Statement_list

[ELSEIFSearch_conditionTHENStatement_list]

...

[ELSEStatement_list] ENDIF;

CASEStatement

CASECase_value

WHENWhen_valueTHENStatement_list

[WHENWhen_valueTHENStatement_list]

...

[ELSEStatement_list]

End case;

Loop:

While

[Begin_label:] WHILESearch_conditionDO

Statement_list

End while [End_label];

If you need to terminate the while loop in advance, you need to use tags. tags must appear in pairs.

Exit Loop

Exiting the entire loop leave is equivalent to break

Exiting the current loop iterate is equivalent to continue

The exit tag determines which loop to exit.

Variable declaration:
Syntax:
DECLARE var_name [,...] type [DEFAULT value]
This statement is used to declare local variables. To provide a DEFAULT value for a variable, include a DEFAULT clause. The value can be specified as an expression and does not need to be a constant. If no DEFAULT clause exists, the initial value is NULL.
Use
Use the set and select into statements to assign values to variables.

Note that global variables (User-Defined variables) @ XXX global variables can be used directly @ XXX without declaration in the function.

Example: obtain the maximum student ID in the current class.

Refer to 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)

);

Add student ID for Calculation

Drop function if existssno;

Delimiter $ # You can replace the statement Terminator ";" with "$" when a statement block is included"

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

Begin

Declare last_no char (10); # declare a local variable to save the current largest student ID. If not, it 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 into last_no;

If last_no is null then # if it is null, no student in the current class starts from 1 and the class name is obtained.

Return concat (select c_name from join_class where id = c_id into class_name), '001'); # the function of concat () is to connect strings.

Else

Return concat (left (last_no, 7), lpad (right (last_no, 3) + 1, 3, '0 '));

End if;

# Return @ last_no;

End

$

Delimiter;

Obtain the Student name at random.

Drop function if exists sname;

Delimiter $

Create function sname () returns char (2)

Begin

Declare first_name char (16) default 'zhao Qian Sun Li Zhou Wu Zheng Wang Feng Chen Xiaowei Jiang Shen Han yang ';

Declare last_name char (10) default 'jia Yi bingding Wuji Geng Xin qingdece ';

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 built-in functions

Numeric Functions

Abs (X), absolute value abs (-10.9) = 10

Format (X, D), format the kilobytes (1234567.456, 2) = 1,234,567.46

Ceil (X), rounded up to ceil (10.1) = 11

Floor (X), rounded down to the entire floor (10.1) = 10

Round (X), rounded up

Mod (M, N) M % n m mod n evaluate the remainder 10% 3 = 1

Pi () to obtain the circumference Rate

Pow (M, N) M ^ N

Sqrt (X), arithmetic square root

Rand (), random number

TRUNCATE (X, D) truncates D decimal places

Time and date functions

Now (), current_timestamp (); current date and time

Current_date (); current date

Current_time (); current time

Date ('yyyy-mm-dd HH; ii: ss'); obtain the Date part.

Time ('yyyy-mm-dd HH; ii: ss'); obtain the Time part

Date_format ('yyyy-mm-dd HH; ii: ss', '% D % y % a % d % m % B % J ');

Unix_timestamp (); obtain the unix Timestamp

From_unixtime (); // obtain the time from the timestamp

String Functions

LENGTH (string) // string LENGTH, in bytes

CHAR_LENGTH (string) // number of characters in string

SUBSTRING (str, position [, length]) // starts from the position of str and takes length characters

REPLACE (str, search_str, replace_str) // REPLACE search_str with replace_str in str

INSTR (string, substring) // returns the position where the substring first appears in the string.

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

CHARSET (str) // returns the string Character Set

LCASE (string) // converts it to lowercase.

LEFT (string, length) // take the length from the LEFT of string2

LOAD_FILE (file_name) // read content from the file

LOCATE (substring, string [, start_position]) // same as INSTR, but you can specify the start position

LPAD (string, length, pad) // repeat pad to start with string until the string length is length

LTRIM (string) // removes leading Spaces

REPEAT (string, count) // REPEAT count times

RPAD (string, length, pad) // use pad after str until the length is length.

RTRIM (string) // remove backend Spaces

STRCMP (string1, string2) // compare the size of two strings by character

Process functions:

Case when [condition] THEN result [WHEN [condition] THEN result...] [ELSE result] END multiple branches

IF (expr1, expr2, expr3) Dual branch.

Aggregate functions

Count ()

Sum ();

Max ();

Min ();

Avg ();

Group_concat ()

Other common functions

Md5 ();

Default ();