Mysql (13) usage of variables and conditions _ MySQL

Mysql (13) variables and conditions using bitsCN.com

Mysql (13) usage of variables and conditions

Related links:

Mysql (1) installation of mysql

Http: // database/201210/162314 .html;

Mysql-related operations (2)

Http: // database/201210/162315 .html;

Mysql operations on data tables (3)

Http: // database/201210/162316 .html;

Mysql (4) Data Table query operations

Http: // database/201210/162317 .html;

Mysql operations (5)

Http: // database/201210/162318 .html;

Mysql (6) string pattern matching

Http: // database/201210/163969 .html;

Mysql (7) in-depth select query

Http: // database/201210/163970 .html;

Mysql Index (8)

Http: // database/201210/163971 .html;

Mysql common functions

Http: // database/201210/164229 .html;

Mysql (10) trigger 1

Http: // database/201210/164516 .html;

Mysql things (11) trigger 2

Http: // database/201210/164766 .html;

Mysql stored procedures

Http: // database/201210/164795 .html

Variables and conditions can be used in stored procedures

1. Define variables

You can use DECLARE to define a variable, but it is local. It can only be effective in BEGIN... END and can be used in nested blocks. of course, you can use default to set the default value.

SQL code

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

Example: define a variable

SQL code

DECLARE last_day_start DATE -- defines a DATE variable named last_day_start.

2. variable assignment

Values can be directly assigned, and values can be queried.

SQL code

-- Use set to assign values directly. constants or expressions can be assigned.

-- Syntax format

SET var_name = expr [, var_name = expr...]

Example: assign values to the variables defined above

SQL code

SET last_day_start = DATE_SUB (CURRENT_DATE (), INTERVAL 1 MONTH) -- assign a value to the variable

Assign values to variables through queries

SQL code

-- Assign values to variables through queries

-- Syntax format

SELECT col_name [,...] INTO var_name [,...] table_expr

SQL code

-- Assign a value to the variable in the example

SQL code

DECLARE v_payment DECIMAL (5, 2); -- defines a variable

-- Assign values to variables using queries

Select ifnull (SUM (payment. amount), 0) INTO v_payment FROM payment

3. condition definition

SQL code

-- Define the syntax format of the condition

DECLARE condition_name condition for condition_value

Condition_value:

SQLSTATE [VALUE] sqlstate_value

| Mysql_error_code

4. processing of conditions

SQL code

--- Condition processing

DECLARE handler_type handler for condition_value [,...] sp_statement

Handler_type:

CONTINUE | EXIT | UNDO

Condition_value:

SQLSTATE [VALUE]

| Condition_name

| SQLWARNING

| NOT FOUND

| SQLEXCEPTION

| Mysql_error_code

Example:

SQL code

Declare continue handler for sqlstate '000000' SET @ x2 = 1

CONTINUE indicates that the execution continues;

EXIT indicates that the execution is aborted.

BitsCN.com