Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the variable types of mysql stored procedures". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn what variable types are there in mysql stored procedures.

Mysql stored procedure variable types: 1, local variables, define syntax as "DECLARE variable name data type"; 2, user variables, define syntax as "set @ variable name: =..."; 3, system variables, can be divided into global variables "@ @ global" and session variables "@ @ session".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What are the variable types of mysql stored procedures

Common variables of MySQL stored procedures: local variables, user variables, system variables

1. Local variables

In the process body, you can declare local variables and use the temporary to save some values.

1. Define local variable syntax:

DECLARE var_name [, var_name]... Type [DEFAULT value]

Where type is the data type of MySQL, such as int, float, date, varchar (length)

Note:

① DECLARE is used to declare local variables, and DECLARE is only used in BEGIN. In END compound statements, and must be at the beginning of the compound statement, before any other statement; can be used in nested blocks, except for those blocks that declare variables with the same name.

② uses the default clause (the value can be constant or specified as an expression) if you want to provide a default value for the variable; if there is no DEFAULT clause, the initial value is NULL.

2. Basic format:

CREATE PROCEDURE sp_name ([proc_parameter [,...]]) BEGINDECLARE var_name1 type [DEFAULT value]; DECLARE var_name2 type [DEFAULT value]; DECLARE. ; [characteristic...]

Example 1: create a procedure, specify a default value for a local variable, and call the procedure

Mysql > delimiter $$mysql > create procedure test1 (out num1 int)-> begin- > declare num2 int default 100 leaders-> set num1=num2;- > end- > $mysql > delimiter; mysql > call test1 (@ num); mysql > select@num;+-+ | @ num | +-+ | 100 | +-+

Parsing:

Create a test1 stored procedure: output the value of the num1 variable, declare that the local variable num2 is of type int, with a default value of 100. assign the value of num2 (unprocessed, default) to num1 with set

Call test1: use the user variable num to receive the output value num1 of the stored procedure.

In addition to literals, default values can also be complex expressions, including scalar quantum queries

Example 2: the creation process uses the number of all players to initialize a variable

Mysql > delimiter $$mysql > create procedure test2 (out num1 int)-> begin- > declare num2 int default (select count (*) fromPLAYERS);-> set num1=num2;- > end$$mysql > delimiter; mysql > call test2 (@ num); mysql > select@num;+-+ | @ num | +-+ | 14 | +-+

Note: MySQL does not support arrays as local variables.

3. Scope of local variables:

That is, the scope of the block in which variables can be used normally without error.

In the case of nested blocks

Variables declared in an external block can be used directly in an internal block

Variables declared in internal blocks can only be used in internal blocks.

Parsing: variable v2 can only be used in internal block b2, where the set statement and the last set statement in block b3 are incorrect.

II. User variables

> user variables are related to database connections: variables declared in the current connection will disappear when the connection is disconnected; variables declared in this connection cannot be used in another connection.

> an @ symbol represents a user variable.

1. Definition of user variables (set, select):

1) set statement assigns values to user variables:

You can use "=" or ":" as the allocator

The expr assigned to each variable can be an integer, real number, string, or null value

Mysql > set @ zjc:=999;mysql > select@zjc;+-+ | @ zjc | +-+ | 999 | +-+

2) select statement assigns values to user variables:

The allocator must be ": =" and "=" cannot be used because = is treated as a comparison operator in non-SET statements

Mysql > select@abc: = 123 + abc:=123 | +-+ | 123 | +-+ mysql > select@abc;+-+ | @ abc | +-+ | 123 | +-+

Note:

The ① user variable can be defined anywhere and can be used anywhere; without definition, it can be used directly (the value defaults to null).

The variable name of the ② user variable is in the form @ var_name, with the @ symbol.

The abuse of user variables by ③ can make the program difficult to understand and manage.

Extended variable assignment: set syntax

Variables in MySQL do not strictly restrict the data type, and the data type of the variable changes from time to time according to the value assigned to the variable.

SET variable_assignment [, variable_assignment]... variable_assignment:user_var_name=expr | [GLOBAL | SESSION] system_var_name = expr | [@ @ global. | @ @ session. | | @] system_var_name = expr |

# # it is used to assign values to user variables and system variables, as well as to local variables in the process.

Note:

When set system variable, there is no scope modification. The default is session scope.

(in particular, it is important to note that some system variables cannot be set without scope modification, so it is best to bring scope setting system variables.)

2. Scope of user variables

As long as the user variables defined in the current connection are OK in the current connection, nothing else is allowed.

3. Different from local variables:

Local variables have only variable names and no @ sign; user variable names are preceded by @ symbols.

All are defined and then used; for undefined variables, the select value is empty.

Local variables are used only inside the stored procedure, but are meaningless outside the procedure. When the begin-end block is processed, the local variable disappears, while the user variable can be used inside and outside the stored procedure.

Tips: within the stored procedure, use local variables, not user variables.

3. User variable and system variable of MySQL variable

1. The user variable is @ var_name (an @ symbol) as described above.

The ① user variable is related to the database connection. Declare the variable after the connection, and disappear automatically when the connection is disconnected.

② select an unassigned user variable that returns NULL, that is, no value

The variable of Mysql is similar to a dynamic language in that the value of the variable varies according to the type of value to be assigned.

2. System variables: according to the scope of system variables, they are divided into global variables and session variables (two @ symbols).

① global variable (@ @ global.)

Global variables are initialized to default values automatically by the server when MySQL starts

The default values of global variables can be changed by changing the MySQL configuration files (my.ini, my.cnf).

② session variable (@ @ session.)

MySQL initializes each time a new connection is established

MYSQL makes a copy of the values of all current global variables as session variables (that is, if the values of session variables and global variables have not been changed manually after the session is established, then the values of all these variables are the same).

# the difference between global variables and session variables: changes to global variables affect the entire server, but changes to session variables only affect the current session.

Thank you for your reading, the above is the content of "what are the variable types of mysql stored procedures". After the study of this article, I believe you have a deeper understanding of the variable types of mysql stored procedures, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.