Detailed explanation of mysql stored procedure with examples

What is a mysql stored procedure?

Stored procedures are combined SQL statements stored in the server. They are compiled, created and saved in the database. Users can call and execute them through the name of the stored procedure. The core idea of ​​stored procedures is encapsulation and reusability at the SQL language level of the database. Using stored procedures can reduce the business complexity of the application system, but it will increase the load on the database server system, so comprehensive business considerations need to be considered when using it.

Basic syntax format

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

(Free learning video tutorial sharing: mysql video tutorial)

Example 1: Calculate consumption Discount

-- 创建存储过程
DROP PROCEDURE IF EXISTS p01_discount ;
CREATE PROCEDURE p01_discount(IN consume NUMERIC(5,2),OUT payfee NUMERIC(5,2))BEGIN
    -- 判断收费方式
    IF(consume>100.00 AND consume<=300.00) THEN
        SET payfee=consume*0.8;
    ELSEIF (consume>300.00) THEN 
        SET payfee=consume*0.6;
    ELSE 
        SET payfee = consume;    END IF;    SELECT payfee AS result;END ;
        -- 调用存储过程
        CALL p01_discount(100.0,@discount);

Example 2: While..Do write data

Provide a data table

CREATE TABLE `t03_proced` (  
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',  
`temp_name` varchar(20) DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='触发器写数据';

Store the program

According to the incoming Parameter to determine the number of data written to the t03_proced table.

DROP PROCEDURE IF EXISTS p02_batch_add ;
CREATE PROCEDURE p02_batch_add(IN count INT(11))BEGIN
    DECLARE temp int default 0;
    WHILE temp < count DO
        INSERT INTO t03_proced(temp_name) VALUES ('pro_name');        
        SET temp = temp+1 ;   
         END WHILE;
         END ;
         -- 测试：写入10条数据call p02_batch_add(10);

Notes

1. Business scenarios

The application of stored procedures in actual development is not very widespread. Usually complex business scenarios are Application-level development allows for better management, maintenance and optimization.

2. Execution speed

If in a simple scenario of single table data writing, based on application writing or database connection client writing, compared with stored procedure writing The speed will be much slower, and stored procedures have no network communication overhead, parsing overhead, optimizer overhead, etc. to a large extent.

Recommended related articles and tutorials: mysql tutorial

The above is the detailed content of Detailed explanation of mysql stored procedure with examples. For more information, please follow other related articles on the PHP Chinese website!