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

What is a mysql stored procedure?

A stored program is a combined SQL statement stored in the server, which is compiled, created and saved in the database, and the user can call and execute it through the name of the stored procedure. The core idea of stored procedure is the encapsulation and reusability of database SQL language. The use of stored procedures can reduce the business complexity of the application system, but it will increase the load of the database server system, so comprehensive business considerations are needed when using it.

Basic grammatical format

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

Example 1: calculating consumption discount

-- create stored procedure DROP PROCEDURE IF EXISTS p01_discount; CREATE PROCEDURE p01_discount (IN consume NUMERIC (5jing2), OUT payfee NUMERIC (5Magne2)) BEGIN-- determine charging method IF (consume > 100.00 AND consume300.00) THEN SET payfee=consume*0.6; ELSE SET payfee=consume; END IF; SELECT payfee AS result;END;-- call stored procedure CALL p01_discount (100.0)

Example 2: While..Do writes data

Provide a data sheet

CREATE TABLE `t03Proced` (`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', `temp_ name` varchar (20) DEFAULT NULL COMMENT' name', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' trigger to write data'

Storage program

Determine the number of pieces of data written to the t03_proced table based on the parameters passed in.

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;-- Test: write 10 pieces of data call p02_batch_add (10)

Matters needing attention

1. Business scenario

Stored procedures are not widely used in actual development, and complex business scenarios are usually developed at the application level, which can be better managed, maintained and optimized.

2. Execution speed

If, in the simple scenario of single table data writing, client writes based on application programs or database connections will be much slower than stored procedures, and stored procedures have to a large extent no network communication overhead, parsing overhead, optimizer overhead, etc.

The above are examples to explain the details of mysql stored procedures, more 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.