Mysql Stored Procedure Example

Stored procedures and functions are a collection of SQL statements that have been compiled and stored in a database, except that the function must have a return value, and that the stored procedure has no parameters for the stored procedure to use in, out, inout types, and the arguments of the function can only be in type. The stored procedure is simply a collection of one or more MySQL statements saved for later use. It can be considered as a petition, although their role is not limited to batching. In my opinion, a stored procedure is a collection of business logic and processes that can create tables, update data, delete, and so on in stored procedures. This blog is about the stored procedure

Operation of the stored procedure

The syntax is as follows:

Create:CREATE PROCEDURESp_name ([proc_parameter[,...]])    [characteristic ...]routine_body Proc_parameter:[in| Out| INOUT]Param_name type #type: anyvalid MySQL data typecharacteristic:language SQL|[ not]Deterministic|{CONTAINSSql|NO SQL|READS SQL DATA|Modifies SQL DATA}|SQL SECURITY {Definaer|INVOKER}|COMMENT'string'routine_body:valid SQLprocedureStatementorStatements Modification:ALTER PROCEDURESp_name[characteristic ...]characteristic:{CONTAINSSql|NO SQL|READS SQL DATA|Modifies SQL DATA}|SQL SECURITY {Definaer|INVOKER}|COMMENT'string'Invoke: Call Sp_name ([parameter[,...]]) Delete:DROP PROCEDUREsp_name view: ShowPROCEDURESTATUS[Like ' pattern ']SHOWCREATE PROCEDURESp_name

MySQL's stored procedures and functions allow the inclusion of DDL statements, as well as the execution of commits or rollbacks in stored procedures, but stored procedures and functions do not allow the load DATA infile statement to be executed, and stored procedures and functions can invoke other procedures or functions.

Insert a small knowledge point @:

1. User variables: Starting with "@", the "@ Variable name" user variable is bound to the MySQL client, and the set variable is only valid for the client used by the current user.  2. Global variables: defined by the SET global variable name  or  set @ @global. Variable names are valid for all clients, and you can set global variables only if you have super permissions. 

A stored procedure is simply a collection of one or more MySQL statements that are saved for later use. It can be considered as a petition, although their role is not limited to batching.

In my opinion, a stored procedure is a collection of business logic and processes that can create tables, update data, delete, and so on in stored procedures.

Why you use stored procedures

1. Simplify complex operations by encapsulating the processing in an easy-to-use unit (as described in the previous example).
2. This guarantees the integrity of the data because it does not require a series of processing steps to be repeatedly established. If all developers and applications use the same (test and test) stored procedures, the code used is the same. The extension of this point is to prevent errors. The more steps you need to perform, the greater the likelihood of errors. Prevent errors to ensure data consistency.
3. Simplify the management of changes. If a table name, column name, or business logic (or something else) changes, you only need to change the code of the stored procedure. The people who use it don't even need to know about these changes.

The users table is as follows:

Create a stored procedure, pass in the gender (male or female), display the corresponding gender user ID, and return the number of gender (I am operating in MySQL front):

#DELIMITER $$ CREATE PROCEDURE user_procedure (inVARCHAR (2) Character set UTF8INT)BEGIN     SELECTfrom  WHERE gender=sex;     SELECT  into num;    END #$$ #DELIMITER;

If you use the Navicat version, you should change it to:

DELIMITER $$ CREATE PROCEDURE user_procedure (inVARCHAR (2) Character set UTF8INT)BEGIN     SELECTfrom  WHERE gender=sex;     SELECT  into num;    END $ $DELIMITER;

Remember the Chinese characters field, be sure to set the code: Character Set UTF8, here himself was a long time to wake up ...

Call

Call User_procedure (' female ',@num); Select @num;

Defining conditions and handling

The definition and processing of a condition can be used to define the corresponding processing steps when a problem is encountered during processing.

The syntax is as follows:

condition Definition:DECLARECondition_name condition forcondition_valuecondition_value:sqlstate[VALUE]Sqlstate_value|mysql_error_code Condition Processing:DECLAREHandler_type Handler forCondition_value[,...]Sp_statementhandler_type:CONTINUE|EXIT|undocondition_value:sqlstate[VALUE]Sqlstate_value|Condition_name|SQLWarning| notFOUND|SQLEXCEPTION|Mysql_error_code

Keep using users for an example!
Now there are the following tables:

(1) When the condition is not processed:

#delimiter $$Create procedureUser_insert ()beginSet @x=1;Insert  intoUsers (Id,gender,name)Values(1,'male','Changgui');Set @x=2;Insert  intoUsers (Gender,name)Values('female','Bigfoot');Set @x=3;END#$$

The above example shows that when inserting id=1, the primary key repeats, exits directly, and does not execute the remaining statements, so the value of @x is 1.

Well, let's just write it here. Going out to walk the baby ... Not to be continued ...

Mysql Stored Procedure Example