Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of storage functions in MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Storage function

What is a storage function: encapsulates a piece of sql code, performs a specific function, and returns the result.

Syntax for storing functions:

Create function function ([function arguments],... .]) Returns return type Begin If (Return) Else Return (returned data) end if; end

For example: create function count_news (hits int) returns int

Unlike the stored procedure return parameters, the stored function does not directly declare which variable is the return parameter when it is defined, but only uses returns to declare the data type to which the return parameter belongs, and the return parameter is expressed in the function body in the form of the data variable returned by return. What we should pay attention to is:

The storage function only supports input parameters, and there is no IN or INOUT before input parameters.

Limitations in storage functions

Flow control (Flow-of-control) statements (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE) are also legal.

Variable declaration (DECLARE) and assignment (SET) are legal.

Conditions are allowed to be declared.

Exception handling declarations are also allowed.

But keep in mind here that the function has restrictions: you cannot access the table in the function. Therefore, it is illegal to use the following statement in a function.

ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE

DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL

LOCK OPTIMIZE REPAIR REPLACE REVOKE

ROLLBACK SAVEPOINT 'SELECT FROM table'

'SET system variable' 'SET TRANSACTION'

SHOW 'START TRANSACTION' TRUNCATE UPDATE

The difference between stored functions and stored procedures

First, the stored function has only one return value, while the stored procedure cannot have a return value.

Second, the function can only have input parameters, and can not take in, and stored procedures can have multiple in,out,inout parameters.

Third, the statement function in the stored procedure is more powerful, the stored procedure can achieve very complex business logic, and the function has many limitations, such as not using statements such as insert,update,delete,create in the function; the stored function only completes the query and can accept input parameters and return a result, that is, the function is more targeted.

Fourth, stored procedures can call stored functions. However, functions cannot call stored procedures.

Stored procedures are generally executed as a separate part (call calls). The function can be called as part of the query statement.

Example 1:

Id

Name

QQ

Phone

one

Qin Yun

10102800

13500000

two

On the way

10378

13600000

three

LEO

10000

13900000

Id

Name

Boarding time

Administrator

one

Qin Yun

2004-1-1

Li Dawei

two

Qin Yun

2005-1-1

Pony Ma

three

On the way

2005-1-1

Pony Ma

four

Qin Yun

2005-1-1

Li Dawei

five

On the way

2005-1-1

Li Dawei

To achieve the goal:

Take the list of all personnel from Table 1 and the number of visits and administrators from Table 2.

The list of people on the plane and the number of times on the plane, administrator.

Qin Yun 3 Li Dawei, Ma Huateng, Li Dawei

On the road 2 Ma Huateng, Li Dawei

LEO 0

This is the end of this article on "sample analysis of storage functions in MySQL". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.