1, Overview
Stored procedures can be understood as a collection of SQL statements (equivalent to a function method in PHP to implement business logic). They are compiled in advance and stored in the database.
Calling stored procedures has the same effect as directly executing SQL statements, but one advantage of stored procedures is that the processing logic is encapsulated in the database.
When we call a stored procedure, we do not need to understand the processing logic. Once the processing logic changes, we only need to modify the stored procedure, which has no impact on the program calling it.
Calling stored procedures and functions can simplify a lot of work of application developers, reduce the transmission of data between database and application server, reduce the interaction with script language and bandwidth, and improve the efficiency of data processing.
2, Stored procedure structure
create procedure [Stored procedure name(parameter list)]
begin
[Stored procedure body]
endcall Stored procedure name(parameter list)
3, Use example
Example 1. Create a new data table and add 1 million records to this data table.
(1) New data table
CREATE TABLE `test_table` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `loop` int(10) unsigned NOT NULL DEFAULT '0', `name` varchar(256) NOT NULL DEFAULT '', `pen_name` varchar(256) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
(2) New stored procedure
DROP PROCEDURE IF EXISTS insert_many_rows;
CREATE PROCEDURE insert_many_rows (IN loopTime INT)
BEGIN
DECLARE executedTime INT ;
SET executedTime = loopTime;
while executedTime > 0 DO
INSERT INTO test_table(NULL, 0, 'sss', 'kkk');
SET executedTime = executedTime - 1;
END WHILE;
END;(3) Call stored procedure
CALL insert_many_rows(1000000);
The result should be that there are 1 million records in the new data table.
Example 2: create 10 data tables through stored procedures, namely test_ table_ 0 ~ test_ table_ nine
(1) Create stored procedure
DROP PROCEDURE IF EXISTS create_test_tables;
CREATE PROCEDURE `create_test_tables`()
BEGIN
DECLARE i INT;
DECLARE tableName VARCHAR(30);
DECLARE sqlText text;
SET i = 0;
WHILE i < 10 DO
SET tableName = CONCAT('test_table_' , i);
SET sqlText = CONCAT('CREATE TABLE ', tableName , '(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`loop` int(10) unsigned NOT NULL DEFAULT ''0'',
`name` varchar(256) NOT NULL DEFAULT '''',
`pen_name` varchar(256) NOT NULL DEFAULT '''',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2001001 DEFAULT CHARSET=utf8;');
SET @sqlText = sqlText;
PREPARE stmt
FROM
@sqlText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i = i + 1;
END WHILE;
END(2) Call stored procedure
CALL create_test_tables();
4, Analysis
1. Parameter type of stored procedure:
(1) IN means only for input.
(2) OUT means only for output.
(3) INOUT can be used as input or output.
2. Variable declaration in stored procedure
DECLARE a local variable through DECLARE. The scope of the variable is only begin End block.
Default values can be added to the declaration of variables, such as:
DECLARE executedTime INT DEFAULT 0;
3. Process control statement syntax
if The syntax format is:
if Conditional expression then sentence
[elseif Conditional expression then sentence] ....
[else sentence]
end if
case Syntax format of
The first is the first way to write:
case expression
when value then sentence
when value then sentence
...
[else sentence]
end case
Then there is the second way:
case
when expression then sentence
when expression then sentence
....
[else sentence]
end case
loop The syntax format of the loop is:
[grade:] loop
Circular statement
end loop [grade]
while grammar
while a>100 do
Circular statement
End while
Repeat //cursor
SQL Statement 1
UNTIL Conditional expression
END Repeat;
Loop
SQL sentence
All conditional judgments and jumps need to be realized by themselves
End loop
leave Statement is used to exit from the annotated process construct. It is usually associated with begin...end Or cycle together
leave grade;
Declaration statement terminator, which can be customized:
DELIMITER [accord with]
delimiter $$
$$4. Data types in stored procedures
Value type: Int, float, double, decimal
Date type: timestamp, date, year
String: char, varchar, text
5, Advantages and disadvantages of stored procedures
1. Advantages:
(1) Fast execution speed. Because every SQL statement needs to be compiled and then run. However, after the stored procedures are compiled directly, they can be run directly.
(2) To reduce network traffic, the overhead of transmitting a stored procedure is much less than that of transmitting a large number of SQL statements.
(3) Improve system security, because stored procedures can use permission control, and parameterized stored procedures can effectively prevent SQL injection attacks. Ensure its safety.
(4) Coupling is reduced. When our table structure is adjusted or changed, we can modify the corresponding stored procedure, and our application needs to change less to a certain extent.
(5) Reusability is strong, because after we write a stored procedure, we only need a name to call it again, that is, "write once, call anywhere", and the use of stored procedures can also strengthen the modularity of the program.
2. Disadvantages:
(1) Poor portability. Because the stored procedure is bound to the database, if we want to replace the database and other operations, many places may need to be changed.
(2) Inconvenient to modify. Because for stored procedures, we can't debug them particularly effectively. Some of its bugs may be found later, which increases the risk of application.
(3) No obvious advantages and redundant functions. For small web applications, if we use statement caching, we find that the cost of compiling SQL is not large, but using stored procedures requires the cost of checking permissions. These redundant functions will also drag down the performance to a certain extent.
6, Using stored procedures in PHP
Stored procedures can also be used in PHP, and the use of stored procedures is also very simple. Just execute the create statement and call statement of the stored procedure separately.
The simplest pdo call method is used here. If you are in the framework, in order to maintain the beauty of the code, please use the query execution statement provided by the framework.
<?php
declare(strict_types = 1);
// Note: the create stored procedure and call stored procedure should be executed separately. After creating the stored procedure, comment out the part of the create stored procedure, and then open the call stored procedure for code execution
// pdo connection
$dsn = "mysql:dbname=test;host=127.0.0.1";
$pdo = new PDO($dsn,'root','123456');
# -------------------------------Create stored procedure--------------------------
// Create stored procedure statement assignment to variable
$sql = 'DROP PROCEDURE IF EXISTS insert_many_rows_2;
CREATE PROCEDURE insert_many_rows_2 (IN loopTime INT)
BEGIN
DECLARE executedTime INT ;
SET executedTime = loopTime;
while executedTime > 0 DO
INSERT INTO test_table(NULL, 0, \'sss\', \'kkk\');
SET executedTime = executedTime - 1;
END WHILE;
END;';
// implement
$stmt = $pdo->query($sql);
var_dump($stmt->fetchAll(2));
#----------------------call stored procedure--------------------------
/*$callSql = 'CALL insert_many_rows(1000000);';
$stmt = $pdo->query($callSql);
var_dump($stmt->fetchAll(2));*/7, Summary
It's enough to know about stored procedures. In fact, many companies prohibit the use of stored procedures, mainly because once stored procedures are used, it will be very difficult for newcomers to take over, debug and expand, and there is no portability.
Moreover, the problems that can be solved by stored procedures can also be solved by general program code. Therefore, when it is not necessary, it is better to use code instead of considering using stored procedures.