"Go" MySQL Getting Started learning 12: basic operations for Stored procedures

Reprint Address: http://www.2cto.com/database/201212/177380.html

A stored procedure is simply a collection of one or more MySQL statements that are saved for later use. It can be considered a batch file, although their role is not limited to batch processing.  www.2cto.com          Using stored procedures requires MySQL5 and later version support. Why use stored procedures         simplify complex operations by enclosing processing in easy-to-use units;        Putting a series of processing steps into the same stored procedure, Ensures data integrity and operational security;        simplifies management of changes;        improved performance. Using stored procedures faster than using separate SQL statements;        There are some MySQL elements and features that can only be used in a single request, and stored procedures may use them to write more powerful and flexible code; Basic operations 1, creating stored procedures         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 stored procedure         call sp_name;        Example:[sql] mysql> call Sp_test ();  +--------+----------+  | UserID | Username |  +--------+----------+  |    215 | NULL     |  +--------+----------+   3, delete stored procedures         DROP PROCEDURE [IF EXISTS] sp_name;  &N Bsp     Example:[sql] mysql> drop procedure if exists sp_test;   4, view stored procedure creation information         SHOW create PROCEDURE sp_name;        Example: [SQL]&NB Sp;mysql> Show CREATE PROCEDURE sp_test;  +-----------+----------+--------------------------------------------------------+----------------------+- ---------------------+--------------------+  | Procedure | Sql_mode | Create Procedure                                       | character_set_client | Collation_connection| Database Collation |  +-----------+----------+--------------------------------------------------------+----------------------+- ---------------------+--------------------+  | 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  |  +-----------+----------+--------------------------------------------------------+----------------------+- ---------------------+--------------------+   5, view stored procedure status         SHOW PROCEDURE status [ Like '];        Example:[sql] mysql> Show procedure status like ' Sp_test ';  +------+---------+-----------+----------------+---------------------+---------------------+-------------- -+---------+----------------------+- | Db   | Name    | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client |  +------+---------+-----------+----------------+---------------------+---------------------+-------------- -+---------+----------------------+- | Test | Sp_test | PROCEDURE | [Email protected] | 2012-12-17 23:57:38 | 2012-12-17 23:57:38 | Definer       |         | Latin1               |  +------+---------+-----------+----------------+---------------------+---------------------+-------------- -+---------+----------------------+-   www.2cto.com  6, using stored procedure parameters         Example: [SQL]  mysql> delimiter// mysql> CREATE PROCEDURE sp_type_cnt (         in in_t ype 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 |  +------+