Stored procedures are an important feature of database storage. Stored procedures were not supported by MySQL prior to version 5.0. Stored procedures can greatly improve the processing speed of the database and the flexibility of database programming.
1. What is a stored procedure?
Stored procedures are a collection of SQL statements that are designed to perform specific functions.The purpose of using stored procedures is to pre-write common or complex work in SQL statements and store it with a specified name, which is compiled and optimized and stored on the database server and therefore becomes a stored procedure.When the database is later required to provide the same functionality as a defined stored procedure, this can be done automatically by simply calling the CALL stored procedure name.
A stored procedure is a programmable function that is created and saved in a database and generally consists of SQL statements and some special control structures.
Stored procedures are especially appropriate when you want to perform the same specific functionality on different applications or platforms.
2. What are the advantages of stored procedures?
- Encapsulation: When a stored procedure is created, it can be called multiple times in the program without rewriting its SQL statement, and the DBA can modify the stored procedure at any time without affecting the application source code that calls it.
- Enhanced: The functionality and flexibility of the SQL statement stored procedures can be written with process control statements, with great flexibility to complete complex judgments and more complex operations.
- Reduces network traffic: Because a stored procedure runs on the server side and executes quickly, when it is called on a client computer, only the call statement is transmitted across the network, thereby reducing network load.
- High performance: After a stored procedure is executed once, the resulting binary code resides in the buffer, and in subsequent calls, only the binary code needs to be executed from the buffer, thereby improving the efficiency and performance of the system.
- Improving database security and data integrity: All database operations can be accomplished using stored procedures, and access to database information can be programmatically controlled.
3. Examples of custom stored procedures
#Query a sql statement
mysql> select * from t1;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 2.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)
mysql> delimiter // #Change its default delimiter to'//', or any other symbol, as long as it is not the default';'
mysql> create procedure test() # Define stored procedure test
-> begin
-> select * from t1; # Write sql statements to stored procedures, you can write more than one
-> end //
mysql> delimiter ; # Change back to default delimiter
mysql> call test(); # Call Stored Procedure
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 2.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)From the example above, it can be seen that a stored procedure is similar to a shell script in that it stores a collection of sql statements. Of course, it also has statements such as judgment, looping, and so on, as follows:
4. Stored procedures for while loops
The following example is how a stored procedure uses a while loop to calculate the result of 1+2+3...+100
mysql> delimiter // #Change the default truncator to'//'
mysql> create procedure test1() # Create Stored Procedures
-> begin # Stored Procedure Start
-> declare n int; # Define a variable name
-> declare summary int; # Define a variable name
-> set n=0; # Set the initial value of the variable to 0
-> set summary=0; # Set the initial value of the variable to 0
-> while n<=100 # When n is less than or equal to 100
-> do
-> set summary=summary+n; # summary is + n
-> set n=n+1; # Then n+1
-> end while; # End of cycle
-> select summary; # Query summary value
-> end // #End of Stored Procedure
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ; # Change truncator back to default
mysql> call test1(); # Call Stored Procedure
+---------+
| summary |
+---------+
| 5050 |
+---------+
1 row in set (0.01 sec)5. Stored Procedures with if Judgement
The following implements that if the value of a parameter is greater than or equal to 10, the SQL statement under else is executed, and if the value of a parameter is less than 10, the SQL statement under the n is executed
mysql> delimiter //
mysql> create procedure test3(in num int)
-> begin
-> if num < 10 then
-> select * from t1 where f_price<10;
-> else
-> select * from t1 where f_price>=10;
-> end if;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call test3(9); # The incoming value is 9
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m2 | 105 | xbabay | 2.60 |
| o2 | 103 | coconut | 9.20 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 2.60 |
+------+------+---------+---------+
11 rows in set (0.01 sec)
mysql> call test3(10); # The incoming value w is 10
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| bs1 | 102 | orange | 11.20 |
| m1 | 106 | mango | 15.70 |
| m3 | 105 | xxtt | 11.60 |
| t1 | 102 | banana | 10.30 |
+------+------+------------+---------+
5 rows in set (0.00 sec)
6. Stored Procedures with case
The result of this stored procedure is that when the value passed in is even, the s_id column in the output t1 table is even, if the value passed in is odd, the output s_id column is odd, otherwise the output is empty
mysql> delimiter //
mysql> create procedure test4(in num int)
-> begin
-> case num%2
-> when 0 then
-> select * from t1 where s_id%2=0;
-> when 1 then
-> select * from t1 where s_id%2=1;
-> else
-> select null;
-> end case;
-> end//
mysql> delimiter ;
mysql> call test4(4); # Incoming Even Number
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b2 | 104 | berry | 7.60 |
| bs1 | 102 | orange | 11.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
+------+------+--------+---------+
6 rows in set (0.00 sec)
mysql> call test4(1); # Incoming Odd Number
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b5 | 107 | xxxx | 3.60 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t4 | 107 | xbababa | 2.60 |
+------+------+------------+---------+
10 rows in set (0.00 sec)7. Outgoing stored procedures to global environment variables
mysql> delimiter //
mysql> create procedure test6(out num float)
-> begin
-> select max(f_price) into num from t1;
-> end //
mysql> delimiter ;
mysql> call test6(@num);
Query OK, 1 row affected (0.00 sec)
mysql> select @num;
+--------------------+
| @num |
+--------------------+
| 15.699999809265137 | # Since the maximum value is a floating point number, this is the case
+--------------------+
1 row in set (0.00 sec)8. Other operation statements on stored procedures
mysql> help procedure; # view help topics: ALTER PROCEDURE # Modify the procedure, which is usually not needed. If you need to modify it, simply delete it and create it. CREATE PROCEDURE # Create procedure DROP PROCEDURE # Delete procedure PROCEDURE ANALYSE SELECT SHOW SHOW CREATE PROCEDURE Stored procedure name # View the details of a stored procedure
9. Attach: How to copy tables
Method 1:like can duplicate the results of a table exactly to a new table, including the notes, indexes, primary key foreign keys, storage engine, and so on.But it does not include table data, as follows:
mysql> create table new_t1 like t1; Query OK, 0 rows affected (0.01 sec) mysql> desc new_t1; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | f_id | char(10) | NO | PRI | NULL | | | s_id | int(11) | NO | | NULL | | | f_name | char(255) | NO | | NULL | | | f_price | decimal(8,2) | NO | | NULL | | +---------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
Method 2: select's method value copies field properties without any other primary keys, indexes, table notes, or storage engines.The following:
mysql> create table new_t1_2 select * from t1; Query OK, 16 rows affected (0.01 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> select * from new_t1_2; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | | c0 | 101 | cherry | 3.20 | | l2 | 104 | lemon | 6.40 | | m1 | 106 | mango | 15.70 | | m2 | 105 | xbabay | 2.60 | | m3 | 105 | xxtt | 11.60 | | o2 | 103 | coconut | 9.20 | | t1 | 102 | banana | 10.30 | | t2 | 102 | grape | 5.30 | | t4 | 107 | xbababa | 2.60 | +------+------+------------+---------+ 16 rows in set (0.00 sec)
10. Delete stored procedures
mysql> drop procedure test4;