Custom function programming in mysql and mysql

Custom function programming in mysql and 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 ();

How do I write mysql user-defined functions?

Mysql> create function HelloWorld4 ()
-> Returns varchar (20)
-> BEGIN
-> RETURN 'Hello World! ';
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

Mysql> select HelloWorld4 ()//
+ --------------- +
| HelloWorld4 () |
+ --------------- +
| Hello World! |
+ --------------- +
1 row in set (0.00 sec)

If you have other databases, it will be helpful to look at the reference manual for writing the SQL stored procedure.

Pan.baidu.com/...715080

How does MySQL test user-defined functions?

I don't know the specific situation of your table. I will give an example:
-- Create a test table
Create table tb12
(Id int identity (1, 1) not null,
Zl int null,
Yf decimal (8, 2) null
)
Go
-- Add Test Data
Insert into tb12
Select 80, null
Union all
Select 105, null
Union all
Select 5000, null
Union all
Select 6000, null
Go

Select * from tb12
----------------------------------
Id zl yf
--------------------------------
1 80 NULL
2 105 NULL
3 5000 NULL
4 6000 NULL

(The number of affected rows is 4)

-- Drop FUNCTION funship.pdf
-- Create the built-in Table value function funship.pdf for calculating postage
Create function funship.pdf ()
RETURNS TABLE
AS
RETURN (SELECT id, zl, yf =
Case when zl <= 100 then zl * 0.3
When zl between 101 and 5000 then zl * 0.15
Else zl x 0.12
End
FROM tb12)
-- Call funshipenders to return the following result set:
SELECT *
FROM funship.pdf ()
-------------------------------------------
Id zl yf
-------------------------------------
1 80 24.00
2 105 15.75
3 5000 750.00
4 6000 720.00

(The number of affected rows is 4)