MySql stored procedure-3. Variables

MySql stored procedure-3. Variable-related links: MySql stored procedure-1. Basic knowledge of SQL stored procedure http://www.bkjia.com/database/201208/148790.html ; MySql stored procedure-2. Creation of the first MySql Stored Procedure http://www.bkjia.com/database/201208/148791.html 1. in Mysql, variables can be defined as variables in code to maintain intermediate results. See the following format: www.2cto.com [SQL] DECLARE variable_name datatype (size) DEFAULT default_value; DECLARE is equivalent to a keyword, a bit similar to var, indicating to define a variable; then variable_name is the name of the variable you specified; datatype indicates the data type and the size in the brackets indicates the format of the corresponding type, such as varchar (50). Note that datatype can only be the basic data types supported by MySql; the DEFAULT keyword indicates that the DEFAULT value of the variable is default_value. Multiple variables of the same type can be defined as follows: [SQL] DECLARE x, y INT DEFAULT 0 indicates two variables x, y, and integer are defined, the default value is 02, and the variable value is the same as that in the program. We also need to assign values to the variable in the program. The following is the sample code: www.2cto.com DECLARE total_count int default 0 SET total_count = 10; you can see that the keyword SET must be added before the assignment. Of course, because of the special nature of the stored procedure (used for database data operations), in addition to the SET value assignment method, you can also assign the returned value to the variable through the select into statement: DECLARE total_products int default 0 select count (*) INTO total_products FROM products. Here, the number of records of products is assigned to the total_products variable. So if we have three records in the table above, Here total_products is equal to 3. 3. The Scope of the variable is similar to that in programming, here, it is generally between the corresponding begin and end. This variable does not work after the end and cannot be used. This is the same as programming. Another variable is called session variable, or user-defined variable ). This type of variable must be preceded by the "@" symbol, which is called a session variable. It indicates that the entire session process is effective. This is similar to a global variable. This variable is widely used because it can share data between called stored procedures or codes as long as it is in a session (that is, a connection process of an application. Let's take a look at a simple example to understand the difference between a variable and a local variable. First, modify the previous stored procedure as follows: www.2cto.com defines a local variable, one is the session variable, which does not need to be defined for the session variable, mysql determines the type based on the value you assign to the variable (for more details, we need another article to explain:> ). Then, we run such a statement on the Query browser to assign the initial value to @ t2: after the initial value is assigned, we call our storage process. Every time we execute this stored process, we find that the value of t1 remains unchanged, and @ t2 increases by 1 each time, because we are in a session, so @ t2 always works, add one at a time.