A brief introduction to the mysql stored procedure

In MySQL, the basic form of creating a stored procedure is as follows:

Create procedure sp_name ([proc_parameter [,...])
[Characteristic...] routine_body
Among them, the sp_name parameter is the name of the stored procedure; proc_parameter indicates the parameter list of the stored procedure; characteristic parameter specifies the features of the stored procedure; routine_body parameter is the content of the SQL code, you can use BEGIN... END indicates the start and END of the SQL code.

Each parameter in proc_parameter consists of three parts. These three parts are the input and output types, parameter names, and parameter types respectively. The format is as follows:

[IN | OUT | INOUT] param_name type

IN indicates the input parameter, OUT indicates the output parameter, INOUT indicates the input or output, and param_name indicates the parameter name of the stored procedure; the type parameter specifies the parameter type of the stored procedure, which can be any data type of the MySQL database.

The characteristic parameter has multiple values. The values are described as follows:

Language SQL: indicates that the routine_body part is composed of SQL statements, which is also the default LANGUAGE of the database system.

[NOT] DETERMINISTIC: specifies whether the execution result of a stored procedure is correct. DETERMINISTIC indicates that the result is correct. Each time a stored procedure is executed, the same input will get the same output. Not deterministic indicates that the result is uncertain. Different outputs may be obtained for the same input. The result is uncertain by default.

{Contains SQL | no SQL | reads SQL data | modifies SQL data}: limits on the use of SQL statements by subprograms. Contains SQL indicates that the subprogram contains SQL statements but does not contain statements for reading or writing DATA. no SQL indicates that the subprogram does not contain SQL statements. reads SQL DATA indicates that the subprogram CONTAINS read DATA; modifies SQL DATA indicates the statement that contains the written DATA in the subprogram. By default, the system specifies contains SQL.

SQL SECURITY {DEFINER | INVOKER}: Specifies who has the permission to execute the task. DEFINER indicates that only the DEFINER can execute the job. INVOKER indicates that the caller can execute the job. By default, the permission specified by the system is DEFINER.

COMMENT 'string': COMMENT information.

Tip: When a stored procedure is created, contains SQL is specified by default, indicating that SQL statements are used in the stored procedure. However, if no SQL statement is used in the stored procedure, it is best to set it to no SQL. In addition, the best part of the stored procedure is the COMMENT part, which makes it easier to read the stored procedure code in the future.

Advantages of stored procedures:

The business logic is "hidden" in the database to prevent sensitive data from being disclosed.

Simplified application code

Cross-platform

If you have to execute several statements at a time, a stored procedure is much faster than the client program executes statements one by one, because no round-trip data is required.

CREATE

[DEFINER = {user | CURRENT_USER}]

PROCEDURE <name> (PARAMETER (S )...)

[Characteristic (s)…] Routine_body

The syntax for creating a function is:

CREATE

[DEFINER = {user | CURRENT_USER}]

FUNCTION sp_name ([parameter])

RETURNS type

[Characteristic (s)…] Routine_body

CREATE

DEFINER defines the owner of a stored procedure or function. If it is omitted, use the default owner (current user)

RPOCEDURE <name> indicates creating a stored procedure named name.

FUNCTION <name> indicates creating a FUNCTION named name.

[IN | OUT | INOUT] <parameter name> type

An IN parameter is an input variable that provides only one value for the process.

The OUT parameter is used only to store the returned value.

INOUT indicates that a parameter can be used to input variables and return values.

Parameter name (parameter name) is the Type name of Type

RETURNS indicates the data type returned.

DETERMINISTIC certainty

Not deterministic non-DETERMINISTIC

Instance 1,

Create procedure user_avg (OUT average NUMERIC (5, 2 ))

BEGIN

Select avg (age) INTO average FROM users

END;

Execute the stored procedure:

CALL user_avg (@ );

SELECT @;

Example 2,

If admin1 exists in the database, 1 is returned.

0 is returned when admin11 does not exist in the database.

Example

Copy and paste the following statement to complete one-time execution. I have tested it. No problem!

Simple MySql stored procedure example:
/********************* Create a table **************** *************/
Delimiter //

Drop table if exists test //

Create table test (
Id int (11) NULL
)//

/********************* The simplest stored procedure *********** ***********/
Drop procedure if exists sp //
Create procedure sp () select 1 //
 
Call sp ()//
 
************ *******/

Drop procedure if exists sp1 //

Create procedure sp1 (in p int)
Comment 'Insert into a int value'
Begin
/* Define an integer variable */
Declare v1 int;
 
/* Assign the value of the input parameter to the variable */
Set v1 = p;
 
/* Execute the insert operation */
Insert into test (id) values (v1 );
End
//

/* Call this Stored Procedure */
Call sp1 (1 )//

/* Go to the database to view the results after the call */
Select * from test //

*************** *********/

Drop procedure if exists sp2 //
Create procedure sp2 (out p int)
/* The DETERMINISTIC clause indicates that the input and output values are fixed and will not be changed. one of my colleagues said that mysql has NOT implemented this function Currently. Therefore, NOT terministic */
DETERMINISTIC
Begin
Select max (id) into p from test;
End
//

/* Call the stored procedure. Note: The output parameter must be a variable with the @ symbol */
Call sp2 (@ pv )//

/* Query the variables used in the stored procedure */
Select @ pv //

/******************* Stored procedures with input and output parameters *********** ************/

Drop procedure if exists sp3 //
Create procedure sp3 (in p1 int, out p2 int)
Begin

If p1 = 1 then
/* Use the @ symbol and variable name to define a variable, which is similar to declare */
Set @ v = 10;
Else
Set @ v = 20;
End if;
 
/* Multiple SQL statements can be executed in the statement body, but must be separated by semicolons */
Insert into test (id) values (@ v );
Select max (id) into p2 from test;
 
End
//

/* Call the stored procedure. Note: the input parameter is a value, and the output parameter must be a variable with the @ symbol */
Call sp3 (1, @ ret )//

Select @ ret //

************ ***************************/

Drop procedure if exists sp4 //
Create procedure sp4 (inout p4 int)
Begin
If p4 = 4 then
Set @ pg = 400;
Else
Set @ pg = 500;
End if;
  
Select @ pg;
  
End //

Call sp4 (@ pp )//

/* Set a variable that has been assigned a value before passing in as a parameter */
Set @ pp = 4 //
Call sp4 (@ pp )//

/*************************************** *****************/