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

---------------------------Stored Procedure--------------------

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),

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:

Delimiter//create PROCEDURE showTime () Beginselect now (); End//delimiter; Call ShowTime;

Function: Shows the current time, no practical significance

To create a stored procedure with parameters:

Only one in parameter

Delimiter//create PROCEDURE Selebyid (in uid SMALLINT UNSIGNED) beginselect * from son WHERE id = uid; End//delimiter; call Selebyid (2);

Include in parameters and out parameters

Delimiter//create PROCEDURE Deletebyid (in uid SMALLINT UNSIGNED, out num SMALLINT UNSIGNED) Begindelete from son WHERE ID = UID; Selete row_count () into Num; 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.

Difference:

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???)

---------------------------function Usage--------------------

Custom Functions (user-defined function UDF) is to extend MySQL with a new function that acts like an intrinsic (built-in) function such as ABS () or CONCAT ().

So UDF is an extension to MySQL functionality

Create and delete custom function syntax:

To create a UDF:

CREATE　　 [AGGREGATE] FUNCTION function_name (parameter_name type,[parameter_name type,...])

　　RETURNS {String| integer| REAL}

Runtime_body

In simple terms:

CREATE FUNCTION　 function name (parameter list)

RETURNS return value type

function body

To delete a UDF:

DROP　 FUNCTION function_name

Call Custom Function Syntax:

　　SELECT function_name (parameter_value,...)

Syntax Examples:

Create a simple parameterless UDF

CREATE FUNCTION simplefun () RETURNS Varvhar () RETURN "Hello world!";

Description

UDF can do more than this, the UDF has two key points, one is a parameter, one is the return value, theUDF can have no parameters, but the UDF must have and only one return value

In the function weight we can use more complex syntax, such as composite structure/Process Control/Any SQL statement/definition variable , etc.

Composite structure definition Syntax:

In the body of a function, if you have more than one statement, we need to put multiple statements into the BEGIN ... The END statement block

DELIMITER//create FUNCTION IF EXIST Deletebyid (uid SMALLINT UNSIGNED) RETURNS VARCHAR (begindelete) from son WHERE id = Uid RETURN (SELECT COUNT (id) from son); end//

to modify the default terminator syntax:

DELIMITER// means to modify the default Terminator ";" To "//" and future SQL statements are terminated with "//"

Special Note:

UDF, theReurn statement is also included in the Begin ... End in

Define local variable syntax in a custom function:

DECLARE var_name[,varname]...date_type [DEFAULT VALUE];

In simple terms:

DECLARE variable 1[, variable 2,...] Variable type [ default Defaults]

The scope of these variables is at begin ... End program, and the definition of a local variable statement must be at begin ... The first line of the end defines

Example:

DELIMITER//create FUNCTION addtwonumber (x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED) RETURNS smallintbegindeclare A, b SMALL INT UNSIGNED DEFAULT 10; SET  a = x, b = y; RETURN a+b; end//

The code above just adds two numbers, and of course, there is no need to write this, just to illustrate the use of local variables, or to illustrate the following: The scope of these local variables is at begin ... End Program

To assign a value to a variable syntax:

SET parameter_name = Value[,parameter_name = value ...]

SELECT into parameter_name

eg

... In a UDF ... DECLARE x int; SELECT COUNT (ID) from tdb_name to X; RETURN x; end//

User variable definition syntax: (can be understood as a global variable)

SET @param_name = value

SET @allParam = 100; SELECT @allParam;

The above definition and displays the @allparam user variable whose scope is valid only for the current user's client

The Process Control statement syntax in a custom function:

Process controls can be used in stored procedures and functions to control the execution of statements.

In MySQL, you can use if statements, case statements, loop statements, leave statements, iterate statements, repeat statements, and while statements for process control.

Each process may contain a single statement, or use the BEGIN ... End constructs a compound statement that can be nested

1. If statement

The IF statement is used to make conditional judgments. Depending on whether the condition is met, different statements are executed. The basic form of its syntax is as follows:

Where the search_condition parameter represents the conditional judgment statement; The Statement_list parameter represents the execution statement for different conditions.

Note: MySQL also has an if () function that differs from the IF statement described here

The following is an example of an if statement. The code is as follows:

IF age>20 then SET @[email protected]+1;  ELSEIF age=20 then SET @[email protected]+1;  ELSE SET @[email protected]+1;  

The example executes different set statements based on the size relationship between age and 20.

If the age value is greater than 20, then the value of Count1 is added 1, and if the age value equals 20, the value of Count2 is added to 1;

In other cases, the value of Count3 is added to 1. The IF statement needs to end with End If.

2. Case statement

Case statements are also used to make conditional judgments, which can be more complex than if statements. The basic form of the case statement is as follows:

wherein, the Case_value parameter denotes the variable of the condition judgment;

The When_value parameter represents the value of the variable;

The Statement_list parameter represents the execution statement for different when_value values.

There is another form of case statement. The syntax for this form is as follows:

wherein, the search_condition parameter indicates the conditional judgment statement;

The Statement_list parameter represents the execution statement for different conditions.

The following is an example of a case statement. The code is as follows:

The code can also be in the following form:

In this example, if the age value is 20,count1 plus 1, the value of Count2 is added 1. The case statement ends with the end case.

Note: The case statement here differs slightly from the case statement in the SQL case expression described in control flow functions. The case statement here cannot have an else NULL clause

and use end case instead of end to terminate!!

3. Loop statement

The Loop statement enables certain statements to be executed repeatedly, implementing a simple loop.

However, the Loop statement itself does not have a statement to stop looping, it must encounter a leave statement, and so on to stop the loop.

The basic form of the loop statement syntax is as follows:

Among them, the Begin_label parameter and the End_label parameter represent the sign of the loop start and end respectively, the two flags must be the same, and can be omitted;

The Statement_list parameter represents a statement that needs to be executed in a loop.

The following is an example of a loop statement. The code is as follows:

Add_num:loop  SET @[email protected]+1;  

The example loops through the Count plus 1 operation. Because there is no statement that jumps out of the loop, the loop becomes a dead loop.

Loop loops end With End loop.

4. Leave statements

The leave statement is primarily used to jump out of loop control. Its grammatical form is as follows:

Where the label parameter represents the loop's flag.

The following is an example of a leave statement. The code is as follows:

The example loops through the Count plus 1 operation. When the value of Count equals 100, the leave statement jumps out of the loop.

5. Iterate statements

The iterate statement is also a statement used to jump out of a loop. However, the iterate statement jumps out of the loop and then goes directly to the next loop.

The iterate statement can only appear within the loop, REPEAT, and while statements.

The basic syntax form of the iterate statement is as follows:

Where the label parameter represents the loop's flag.

The following is an example of a iterate statement. The code is as follows:

The example loops through the Count plus 1 operation, and the count value is 100 when the loop ends. If the value of count is divisible by 3, it jumps out of the loop and no longer executes the following SELECT statement.

Note: Both the leave statement and the iterate statement are used to jump out of a looping statement, but the function is different.

The leave statement is to jump out of the entire loop and then execute the program behind the loop. The iterate statement jumps out of the loop and then into the next loop.

Be sure to distinguish between the two statements when using them.

6. Repeat statements

The repeat statement is a conditional-controlled loop statement. When certain conditions are met, the loop statement is jumped out. The basic syntax form of the repeat statement is as follows:

Where the statement_list parameter represents the execution statement of the loop, and the search_condition parameter represents the condition that ends the loop, and the loop ends when the condition is met.

The following is an example of a iterate statement. The code is as follows:

The example loops through the Count plus 1 operation, and the count value is 100 when the loop ends.

The repeat loop ends with end repeat.

7. While statement

The while statement is also a conditional-controlled loop statement. But the while statement and the repeat statement are not the same.

The While statement executes the statement within the loop when the condition is met.

The basic syntax form of the while statement is as follows:

Where the search_condition parameter indicates the condition of the loop execution, and the loop executes when the condition is satisfied;

The Statement_list parameter represents the execution statement for the loop.

The following is an example of a iterate statement. The code is as follows:

The example loops through the Count plus 1 operation, and the count value is less than 100 when the loop is executed.

If the count value equals 100, it jumps out of the loop. The while loop needs to end with the end while.

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