MySQL stored procedures

Using stored procedures in MySQL, you can package related data manipulation statements, reduce the application-to-database connection, and improve performance accordingly. Of course, if you want to write a lot of stored procedures, maintenance debugging is also a problem. So you need to weigh the pros.

0. Grammar

DELIMITER $$ CREATE    /* [definer = {User | Current_User}]*      /PROCEDURE ' test '. ' My_procedure ' (    )/*  LANGUAGE SQL    | [NOT] Deterministic    | {CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}    | SQL SECURITY {definer | INVOKER}    | COMMENT ' string '* *    BEGIN     SQL    statementEND  $ $DELIMITER;

1. Parameter Type

There are 3 main types of MySQL stored procedure parameters: In, out, INOUT. In represents an input parameter, out represents an output parameter, and inout can be used as an input parameter or as an output parameter.

2. Variable definition and scope of action

Variable definition (DECLARE):

DECLARE INT (4DEFAULT0;

The scope of the variable is being ... END, pay attention to begin ... END can also nest begin ... END. There are two ways to assign a variable: Use the keyword set, set sum_var = 10; The other is select XX into Sum_var from tb_xx;

3. Case

Create a simple commodity table

CREATE TABLEProduct (product_idINT(Ten) not NULLauto_increment, Product_codeVARCHAR(Ten), Product_NameVARCHAR( -), Product_descVARCHAR( -),    PRIMARY KEY(product_id)) CHARSET=UTF8;

#插入数据
INSERT into Product VALUES (0, ' phone ', ' mobile ', ' IPhone '), (0, ' apple ', ' fruit ', ' apple ');

Stored procedures with only in parameters

DELIMITER $$DROP PROCEDURE IF EXISTSgetproductsbycode$$CREATE    PROCEDURE' Test '. ' Getproductsbycode ' (inchProductCodeVARCHAR(Ten)) COMMENT'get Products by code'    BEGIN     　　 SELECT *  fromProductWHEREProduct_code=ProductCode; END$ $DELIMITER;

The stored procedure is invoked as: Call Procedure_name (...);

Mysql>Call Getproductsbycode ('Phone');+------------+--------------+--------------+--------------+|product_id|Product_code|Product_Name|Product_desc|+------------+--------------+--------------+--------------+|          1 |Phone|Cell phone|Iphone|+------------+--------------+--------------+--------------+1Rowinch Set(0.00sec) Query OK,0Rows Affected (0.01Sec

In, Out parameters

DELIMITER $$DROP PROCEDURE IF EXISTSprocedure_add$$CREATE    PROCEDURE' Test '. ' Procedure_add ' (inchAINT(4) ,inchBINT(4), out CINT(4))    BEGIN     　　 DECLARESum_INT(4)DEFAULT 0; SETSum_=A+b; SETC=sum_; END$ $DELIMITER;

Mysql>Call Procedure_add (2,3,@sum); Query OK,0Rows Affected (0.00sec) MySQL> Select @sum;+------+| @sum |+------+|    5 |+------+1Rowinch Set(0.00Sec

When the client calls the store, the out parameter needs to be received using @xx, and the delimiter $$ (customizable) definition of the string is telling MySQL to end up writing the SQL only if it encounters the $$. Because MySQL defaults to a semicolon (;), this causes the semantics to be incorrect.

MySQL stored procedures