The difference between a MySQL stored procedure/stored procedure and a custom function

Grammar:

To create a stored procedure:

CREATE [definer = {User|current_user}] PROCEDURE sp_name ([Proc_parameter [, Proc_parameter ...]) [Characteristics ...] Routime_body

which

Proc_parameter: [in | Out | INOUT] Parameter_name type

Where in represents an input parameter , out represents an output parameter,INOUT means that it can be either input or output; Param_name represents the name of the parameter; type

in the stored procedure body, you can use the composite structure/Process Control/SQL statements/custom variables and so on in the custom Function (UDF) For more information, see MySQL Custom Function usage Detailed-composite structure custom variable/process Control

To call a stored procedure:

Call Sp_name ([Proc_parameter [, Proc_parameter ...])

Call Sp_name

Note: When there is no parameter, "()" can be omitted and "()" should not be omitted when there are parameters.

Stored Procedure Modifications:

The ALTER statement modifies the stored procedure only to modify the stored procedure's comments and other insignificant things, cannot modify the stored procedure body, so to modify the stored procedure, the method is to delete the rebuild!

To delete a stored procedure:

DROP PROCEDURE [IF EXISTS] Sp_name

Example:

To create a non-parametric stored procedure:

// CREATE PROCEDURE showTime () BEGIN SELECT Now (); END // delimiter; Call ShowTime;

Function: Shows the current time, no practical significance

To create a stored procedure with parameters:

Only one in parameter

// CREATE PROCEDURE Selebyid (inSMALLINT  UNSIGNED)BEGINSELECT*  fromWHERE=  uid; END // delimiter; call Selebyid (2);

Include in parameters and out parameters

Delimiter//CREATE PROCEDUREDeletebyid (inchUidSMALLINTUNSIGNED, out NumSMALLINTUNSIGNED)BEGINDELETE  fromSonWHEREId=uid; Selete Row_count () intonum;END//delimiter; call Selebyid (2,@changeLine); Selete@changeLine;

Description: Creates a stored procedure Deletebyid that contains an in parameter and an out parameter. When called, the ID of the passed-in deletion and the user variable that holds the modified row value are @changeline,select @changeLine; the output is affected by the number of rows.

the difference between a stored procedure and a custom function: the process of the stored procedure is more complicated, and the function is more pertinence; stored procedures can have multiple return values, while custom functions have only one return value, and stored procedures are generally independent to execute, and functions are often used as part of other SQL statements ;

Necessity of the existence of stored procedures (benefits):

A stored procedure is to encapsulate a frequently used SQL statement or business logic, save it in a database, and invoke it directly from the database when needed, eliminating the need for a compilation process.
Improve the speed of operation;
While reducing the amount of network data transfer (Do you think it is faster to pass a bunch of SQL code, or a stored procedure name and a few parameters fast???)

The difference between a MySQL stored procedure/stored procedure and a custom function