Shulou(Shulou.com)06/01 Report--

Mysql has supported stored procedures since 5.0. generally speaking, stored procedures are encapsulated SQL, but not only SQL, but also variables, condition judgments, loop bodies, cursors and so on.

The role of stored procedures

In many scenarios, we need to process the data of multiple tables to produce the new data we need. The data of these multiple tables can not be given through queries such as joins, but can only be generated through judgment and loop. At this point, stored procedures can be used to implement.

In addition, stored procedures have some benefits, such as high performance and the ability to reduce network requests. If you do not use stored procedures to implement, using php to implement requires multiple calls to mysql, resulting in multiple requests.

Of course, the stored procedure is not without shortcomings, which mode it compares, and does not support clustering.

Create a stored procedure

The syntax for creating a stored procedure is as follows:

CREATE PROCEDURE procedure name (parameter) BEGIN process body END

For parameters, set the parameter syntax to

[IN | OUT | INOUT] Parameter name type

IN indicates that this variable can only be used in the procedure body.

OUT indicates that this variable can only be used outside the procedure.

INOUT says it can be used both in vivo and in vitro.

Next, let's create the simplest stored procedure.

CREATE PROCEDURE p1 (IN x INT) BEGIN SELECT xterend

Variable

Variables are divided into global variables and local variables in MySQL.

Global variables start with @ and can be used without declaration, as shown in

SET @ name='gwx'

Local variables need to be declared first, and the initialization method of local variables is as follows:

DECLARE x int DEFAULT 0

Let's complete a stored procedure: according to the distance, the fare is calculated as 6 distances within 3 kilometers, and the excess distance is calculated as 1.2 yuan per kilometer.

-- distance distance CREATE PROCEDURE p1 (in distance FLOAT) BEGIN DECLARE d_money FLOAT DEFAULT 0; IF distance > 3 THEN SET dudes moneymakers 6 + (distance-3) * 1.2; ELSE SET dudes moneymakers 6; END IF; SELECT dudes moneyholders end

Vernier

Compared with php, cursors are a bit like foreach, fetching one record at a time.

Define a cursor:

Declare cursor name CURSOR FOR SELECT statement

Open and close cursors:

Open cursor name

Close cursor name

Fetch cursor data:

FETCH cursor name INTO variable name

Can be such a simple introduction, we will have doubts, do not know how to use. Next, let's look at an example from which you can learn how to use cursors.

Use cursors to complete a very simple function that adds up all the numbers in the test_ cursortable.

CREATE TABLE IF NOT EXISTS test_cursor (num1 INT (10) UNSIGNED NOT NULL DEFAULT 0, num2 INT (10) UNSIGNED NOT NULL DEFAULT 0); INSERT INTO test_cursor (num1,num2) VALUES (1), (2), (3); CREATE PROCEDURE `test_ cursor` () BEGIN DECLARE sum INT (10) DEFAULT 0; DECLARE N1 INT (10); DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT num1,num2 FROM test_cursor; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1 OPEN cur;-- Open the cursor WHILE done=0 DO FETCH cur INTO N1 IF done=0 THEN; IF done=0 THEN-- notice why the IF condition is added here. If not, the last value will be added again SET sum=sum+n1+n2; END IF; END WHILE; CLOSE cur;-- close the cursor SELECT sum;END.

There are a few points to note here. First, the definition of a local variable must be declared before the cursor is declared.

In addition, here DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; means that the done is set to 1 after the end of the cursor to end the loop.

These are the details of the application of Mysql stored procedures, please pay attention to other related articles!

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.