MYSQL entry 12: basic operations of Stored Procedures

MYSQL entry 12: links to basic operations of stored procedures: MYSQL Entry 1: basic operations http://www.bkjia.com/database/201212/173868.htmlMYSQL Entry 2: Search Using Regular Expressions http://www.bkjia.com/database/201212/173869.htmlMYSQL Entry 3: Full Text Search http://www.bkjia.com/database/201212/173873.htmlMYSQL Entry 4: MYSQL Data Types http://www.bkjia.com/database/201212/175536.htmlMYSQL Entry 5: MYSQL Character Set http://www.bkjia.com/database/201212/175541.htmlMYSQL Getting started 6: MYSQL Operators http://www.bkjia.com/database/201212/175862.htmlMYSQL Entry 7: MYSQL common functions http://www.bkjia.com/database/201212/175864.htmlMYSQL Step 8: basic database and table operations http://www.bkjia.com/database/201212/175867.htmlMYSQL 9: simple indexing operations http://www.bkjia.com/database/201212/176772.htmlMYSQL 10: basic view operations http://www.bkjia.com/database/201212/176775.htmlMYSQL Quick Start 11: Basic trigger operations http://www.bkjia.com/database/201212/176781.html A stored procedure is a collection of one or more MySQL statements for future use. It can be considered as a batch file, although they are not limited to batch processing. The use of stored procedures on www.2cto.com requires the support of MySQL 5 and later versions. I. Why should we use stored procedures to simplify complicated operations by sealing processing in easy-to-use units? Put a series of processing steps into the same stored procedure, this ensures data integrity and operational security, simplifies change management, and improves performance. Using Stored Procedures is faster than using separate SQL statements. There are some MySQL elements and features that can only be used in a single request. stored procedures can be used to write more flexible code; ii. Basic operation 1. create procedure sp_name ([proc_parameter [,...]) [characteristic...] routine_body proc_parameter: [IN | OUT | INOUT] param_name type example: [SQL] mysql> create procedure sp_test ()-> begin-> select userid, username from newname where userid = 215; -> end-> // 2. Execute the Stored Procedure CALL sp_name. Example: [SQL] mysql> call sp_test (); + -------- + ---------- + | userid | username | + -------- + ---------- + | 215 | NULL | + -------- + ---------- + 3. Delete the Stored procedure drop procedure [if exists] sp_name; example: [SQL] mysql> drop procedure if exists sp_test; 4. view the stored PROCEDURE creation Information show create procedure sp_name; example: [SQL] mysql> show create procedure sp_test; + ----------- + ---------- + examples + | Procedure | SQL _mode | Create Procedure | character_set_client | collation_connection | Database Collation | + ----------- + ---------- + tables -------------------- + | sp_test | create definer = 'root' @ 'localhost' PROCEDURE 'SP _ test '() begin select userid; username from newname where userid = 215; end | latin1 | latin1_swedish_ci | latin1_swedish_ci | + ----------- + ---------- + upper + lower + upper + ------------------ + 5. view the Stored procedure status show procedure status [LIKE '']. Example: [SQL] mysql> show procedure status like 'SP _ test '; + ------ + --------- + ----------- + ---------------- + response + ------------- + --------- + response +-| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | + ------ + --------- + ----------- + ---------------- + response + ------------- + --------- + response +-| test | sp_test | PROCEDURE | root @ localhost | 23:57:38 | 23:57:38 | DEFINER | latin1 | + ------ + --------- + ----------- + ---------------- + response + ------------------- + --------------- + --------- + -------------------- +-www.2cto.com 6. Example of using stored procedure parameters: [SQL] mysql> delimiter // mysql> create procedure sp_type_cnt (-> IN in_type int,-> OUT out_cnt int->)-> begin-> select count (*) -> from newname-> where type = in_type-> into out_cnt;-> end;-> // mysql> delimiter; mysql> call sp_type_cnt (0, @ cnt ); mysql> select @ cnt; + ------ + | @ cnt | + ------ + | 159 | + ------ +