Application of Mysql variables, cursors and stored procedures

Mysql has supported stored procedures since 5.0. In layman's terms, a stored procedure is an encapsulated SQL, but it is not just SQL, it usually also has variables, conditional judgments, loop bodies, cursors, etc.

The role of stored procedures

In many scenarios, data from multiple tables needs to be processed to generate new data we need. . The data of these multiple tables cannot be given through query methods such as connections, and can only be generated through judgment and looping. At this time, you can use stored procedures to achieve this.

In addition, stored procedures also have some benefits, such as higher performance and reduced network requests. If you don't use stored procedures to implement it, you need to call MySQL multiple times and generate multiple requests if you use PHP to implement it.

Of course, the stored procedure is not without its shortcomings. It is relatively debugged and does not support clustering.

Create a stored procedure

The syntax for creating a stored procedure is as follows:

CREATE PROCEDURE 过程名(参数) 
BEGIN
 过程体
END

Regarding parameters, the syntax for setting parameters is

[IN|OUT|INOUT] Parameter name type

• IN indicates that the variable can only be used within the procedure body

• OUT indicates that the variable Variables can only be used outside the process

• INOUT means they can be used inside and outside the process

Next, let’s create the simplest stored procedures.

CREATE PROCEDURE p1(IN x INT) 
BEGIN
  SELECT x;
END;

Variables

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

Global variables start with @ and do not need to be declared. They can be used directly. For example,

SET @name='gwx';

local variables need to be declared first. The initialization method of local variables is as follows:

DECLARE x int DEFAULT 0;

Let’s follow To complete a stored process: calculate the fare according to the distance, the distance within 3 kilometers is calculated as 6 yuan, and the distance beyond is calculated as 1.2 yuan per kilometer.

-- distance 路程
CREATE PROCEDURE p1(in distance FLOAT)
BEGIN
  DECLARE d_money FLOAT DEFAULT 0; 
    IF distance>3 THEN 
       SET d_money=6+(distance-3)*1.2;
    ELSE
      SET d_money=6;
    END IF;
    SELECT d_money;
END;

Cursor

Compared with PHP, the cursor is a bit like foreach, which obtains one record each time it loops.

Define a cursor:

declare 游标名 CURSOR FOR SELECT 语句

Open and close the cursor:

• open cursor name

• close cursor Name

Get cursor data:

FETCH 游标名 INTO 变量名

It can be introduced in such a simple way, but everyone will have doubts and don’t know how to use it. Next, let's look at an example and learn how to use cursors from the example.

Use a cursor to complete a very simple function, which is to accumulate all the numbers in the test_cursor table.

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,1),(2,2),(3,3);
 
CREATE PROCEDURE `test_cursor`()
BEGIN
    DECLARE sum INT(10) DEFAULT 0;
    DECLARE n1,n2 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; -- 打开游标
    WHILE done=0 DO      
        FETCH cur INTO n1,n2;
        
        IF done=0 THEN -- 注意这里为什么加IF条件，不加的话，最后一个值会被多加一遍         
      SET sum=sum+n1+n2;
        END IF;
    END WHILE;
    CLOSE cur;  -- 关闭游标
    
    SELECT sum;
END

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

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

The above is the detailed content of Application of Mysql variables, cursors and stored procedures. For more information, please follow other related articles on the PHP Chinese website!