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

Blog outline:

1. What is a stored procedure? 2. What are the advantages of stored procedures? 3. Examples of custom stored procedures 4, while loop stored procedures 5, stored procedures with if judgment 6, stored procedures with case 7, stored procedures outputted to global environment variables 8, other operation statements about stored procedures 9, additional: how to copy tables. Preface

Stored procedure is an important function of database storage. MySQL did not support stored procedure before 5.0. stored procedure can not only greatly improve the speed of database processing, but also improve the flexibility of database programming.

1. What is a stored procedure?

A stored procedure is a collection of SQL statements that perform specific functions. The purpose of using stored procedures is to write common or complex work in advance in SQL statements and store them with a specified name, which is compiled and optimized and stored in the database server, so it becomes a stored procedure. When you need the database to provide the same service as the defined stored procedure in the future, you only need to call "CALL stored procedure name" to do it automatically.

A stored procedure is a programmable function that is created and saved in the database and is generally composed of SQL statements and some special control structures.

Stored procedures are especially appropriate when you want to perform the same specific functions on different applications or platforms.

2. What are the advantages of stored procedures? Encapsulation: after the stored procedure is created, it can be called many times in the program without having to rewrite the SQL statement of the stored procedure, and DBA can modify the stored procedure at any time without affecting the application source code that calls it. Can be enhanced: the function and flexibility of SQL statements stored procedures can be written with process control statements, with strong flexibility, can complete complex judgments and more complex operations. Reduce network traffic: because the stored procedure runs on the server side and executes quickly, when the stored procedure is called on the client computer, only the calling statement is transmitted in the network, thus reducing the network load. High performance: after the stored procedure is executed once, the resulting binary code resides in the buffer, and in future calls, only the binary code needs to be executed from the buffer, thus improving the efficiency and performance of the system. Improve database security and data integrity: all database operations can be completed by using stored procedures, and the access to database information can be controlled programmatically. 3. Example of custom stored procedures 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 | bs1 | 102 | orange | 11.20 | bs2 | melon | 8.20 | c0 | 101 | cherry | 3.20 | | 12 | 104 | lemon | 6.40 | M1 | 106 | mango | 15.70 | m2 | 105 | 105 | | xbabay | 2.60 | m3 | 105 | xxtt | 11.60 | | O2 | 103 | coconut | 9.20 | | T1 | 102 | banana | 10.30 | | T2 | 102 | grape | 5.30 | | T2 | xbababa | 3.60 | +-+ mysql > delimiter / / | Mysql > create procedure test ()-> begin-> select * from T1 -> end//Query OK, 0 rows affected (0.00 sec) mysql > delimiter; mysql > call test () +-+ | 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 | bs1 | 102 | orange | 11.20 | bs2 | melon | 8.20 | c0 | 101 | cherry | 3.20 | | 12 | 104 | lemon | 6.40 | M1 | 106 | mango | 15.70 | m2 | 105 | 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 | 3.60 | +-+ 16 rows in set (0.00 sec) |

From the above example, we can see that the stored procedure is similar to a shell script, storing a collection of sql statements, of course, it also has some judgments, loops and other statements, as follows.

4. Stored procedures for while loops

The following example is that the stored procedure uses the while loop to calculate the result of 1, 2, 3. 100.

Mysql > delimiter / / mysql > create procedure test1 ()-> begin-> declare n int;-> declare summary int;-> set nylon;-> set summary=0;-> while n-do-> set summary=summary+n;-> set n=n+1 -> end while;-> select summary;-> end / / mysql > delimiter; mysql > call test1 () +-+ | summary | +-+ | 5050 | +-+ 1 row in set (0.00 sec) 5. Stored procedure with if judgment

The following implementation is that if the value of the passed parameter is greater than or equal to 10, the SQL statement under else is executed, and if the value of the passed parameter is less than 10, the SQL statement under then is executed.

Mysql > delimiter / / mysql > create procedure test3 (in num int)-> begin-> if num

Select * from T1 where f_price 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) +-+ | 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 | melon | 8.20 | c0 | 101 | cherry | 3.20 | | 12 | 104 | lemon | 6.40 | m2 | xbabay | 2.60 | O2 | 103 | coconut | 9.20 | T2 | 102 | grape | 5.30 | | T4 | 107 | xbababa | 3.60 | +-+ | -+-+ 11 rows in set (0.00 sec) Query OK 0 rows affected (0.00 sec) mysql > call test3 (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 the stored procedure implementation is: when the value passed in is even, output the even-numbered rows of s_id in the T1 table, if the value passed in is odd, output s_id as odd rows, 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 swatches% 2.0;-> when 1 then-> select * from T1 where slots% 2room1;-> else-> select null;-> end case;-> end-> / / mysql > delimiter; mysql > call test4 (4) +-+ | 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 (3) +-+ | 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 | xbabay | 2.60 | xbabay | 105 | xxtt | 11.60 | O2 | 103 | coconut | 9.20 | T4 | 107 | xbababa | 3.60 | +-+ -+ 7. Export the stored procedure to the global environment variable mysql > delimiter / / mysql > create procedure test6 (out num float)-> begin-> select max (f_price) into num from T1 -> end-> / / Query OK, 0 rows affected (0.00 sec) mysql > delimiter; mysql > call test6 (@ num); Query OK, 1 row affected (0.00 sec) mysql > select @ num +-+ | @ num | +-+ | 15.699999809265137 | +-+ 1 row in set (0.00 sec) 8. Other operation statements about stored procedures mysql > help procedure Topics: ALTER PROCEDURE CREATE PROCEDURE DROP PROCEDURE PROCEDURE ANALYSE SELECT SHOW SHOW CREATE PROCEDURE stored procedure name 9, attach: how to copy a table.

The 1:like method can exactly copy the results of a table into a new table, including the comments of the replicated table, index, primary key foreign key, storage engine and so on. However, table data is not included, as follows:

Mysql > create table new_t1 like T1 * * query OK, 0 rows affected (0.00 sec) mysql > desc new_t1 +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | f_id | char (10) | NO | | NULL | s_id | int (11) | NO | | NULL | | f_name | char | NO | | NULL | | f_price | decimal (8L2) | NO | | NULL | | +-+-- -+ 4 rows in set (0.00 sec)

Method 2: the method value of select copies the field properties, while other primary keys, indexes, table comments, and storage engines do not copy. As follows:

Mysql > create table new_t1_2 select * from T1 politics query OK, 16 rows affected (0.01sec) Records: 16 Duplicates: 0 Warnings: 0mysql > 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 | melon | 8.20 | c0 | 101 | cherry | 3.20 | 12 | 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 | xbababa | 3.60 | +-+ 16 rows in set (0.00 sec)

-this is the end of this article. Thank you for reading-

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.