Mysql：Stored Object：视图、触发器、调度事件、存储过程、存储函数：【复合语句】语法

begin [...] end 复合语句，可以为“空”的begin end复合语句

复合语句内可以包含0+其他语句，包括复合语句

复合语句内包含的语句需要以分号 " ; " 分割

整个复合语句必须作为一个完整的整体字符串发送给mysqld服务器去处理，因此，客户端必须要能正确处理批命令结束符，尤其是这个mysql

复合语句可以带有标签

在存储对象内，begin [...] end复合语句的“begin”与控制事务的“begin”关键字冲突，此时控制事务的语句必须使用“start transaction”

严格的declare声明语句顺序：

　　variable
　　conditon　　
　　cusor
　　handler

@@系统变量、@用户自定义变量

DECLARE var_name [, var_name] ... type [DEFAULT value] 这个value是{ NULL|constant|express }

var_name是大小写【不】敏感的；其作用域在声明它的begin ... end块内；可以被内层块看到，外层看不到（即内层同名变量会覆盖外层变量）；

var_name不应该与表列同名，防止隐藏了表列的引用

mysql没有FOR循环

循环内控制语句： ... [iterate labn | leave labn] ...

return：退出存储函数并返回值，不可用于其他存储对象奥

loop ... end loop

repeat ... until ? end repeat

while ？ do ... end while

Server-Side Cursors DECLARE cursor_name CURSOR FOR select_statement

mysqld cursor游标：只读、单向、不敏感的

DECLARE condition_name CONDITION FOR { mysql_error_code | SQLSTATE [VALUE] sqlstate_value }
DECLARE { CONTINUE | EXIT | UNDO } HANDLER FOR { mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION } [,...]
statement

signal、resignal、get diagnostics ：错误处理

如果存储过程产生了【未处理】的异常错误，则存储过程的out、inout参数不会反馈给调用方！

#--------------------------------------------------------------------------------------------

13.2.9.1 SELECT ... INTO Statement

The SELECT ... INTO form of SELECT enables a query result to be stored in variables or written to a file:

SELECT ... INTO var_list selects column values and stores them into variables.
SELECT ... INTO OUTFILE writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format.
SELECT ... INTO DUMPFILE writes a single row to a file without any formatting.

A given SELECT statement can contain at most one INTO clause, although as shown by the SELECT syntax description (see Section 13.2.9, “SELECT Statement”), the INTO can appear in different positions:

Before FROM. Example:
```
SELECT * INTO @myvar FROM t1;
```
Before a trailing locking clause. Example:
```
SELECT * FROM t1 INTO @myvar FOR UPDATE;
```

An INTO clause should not be used in a nested SELECT because such a SELECT must return its result to the outer context. There are also constraints on the use of INTO within UNION statements; see Section 13.2.9.3, “UNION Clause”.

For the INTO var_list variant:

var_list names a list of one or more variables, each of which can be a user-defined variable, stored procedure or function parameter, or stored program local variable. (Within a prepared SELECT ... INTO var_list statement, only user-defined variables are permitted; see Section 13.6.4.2, “Local Variable Scope and Resolution”.)
The selected values are assigned to the variables. The number of variables must match the number of columns. The query should return a single row. If the query returns no rows, a warning with error code 1329 occurs (No data), and the variable values remain unchanged. If the query returns multiple rows, error 1172 occurs (Result consisted of more than one row). If it is possible that the statement may retrieve multiple rows, you can use LIMIT 1 to limit the result set to a single row.
```
SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;
```

User variable names are not case-sensitive. See Section 9.4, “User-Defined Variables”.

13.7.4 SET Statements

13.7.4.1 SET Syntax for Variable Assignment
13.7.4.2 SET CHARACTER SET Statement
13.7.4.3 SET NAMES Statement

The SET statement has several forms. Descriptions for those forms that are not associated with a specific server capability appear in subsections of this section:

SET var_name = value enables you to assign values to variables that affect the operation of the server or clients. See Section 13.7.4.1, “SET Syntax for Variable Assignment”.
SET CHARACTER SET and SET NAMES assign values to character set and collation variables associated with the current connection to the server. See Section 13.7.4.2, “SET CHARACTER SET Statement”, and Section 13.7.4.3, “SET NAMES Statement”.

Descriptions for the other forms appear elsewhere, grouped with other statements related to the capability they help implement:

SET PASSWORD assigns account passwords. See Section 13.7.1.7, “SET PASSWORD Statement”.
SET TRANSACTION ISOLATION LEVEL sets the isolation level for transaction processing. See Section 13.3.6, “SET TRANSACTION Statement”.

13.7.4.1 SET Syntax for Variable Assignment

SET variable = expr [, variable = expr] ...

variable: {
    user_var_name
  | param_name
  | local_var_name
  | {GLOBAL | @@GLOBAL.} system_var_name
  | [SESSION | @@SESSION. | @@] system_var_name
}

SET syntax for variable assignment enables you to assign values to different types of variables that affect the operation of the server or clients:

User-defined variables. See Section 9.4, “User-Defined Variables”.
Stored procedure and function parameters, and stored program local variables. See Section 13.6.4, “Variables in Stored Programs”.
System variables. See Section 5.1.7, “Server System Variables”. System variables also can be set at server startup, as described in Section 5.1.8, “Using System Variables”.

A SET statement that assigns variable values is not written to the binary log, so in replication scenarios it affects only the host on which you execute it. To affect all replication hosts, execute the statement on each host.

The following sections describe SET syntax for setting variables. They use the = assignment operator, but the := assignment operator is also permitted for this purpose.

User-Defined Variable Assignment

User-defined variables are created locally within a session and exist only within the context of that session; see Section 9.4, “User-Defined Variables”.

A user-defined variable is written as @var_name and is assigned an expression value as follows:

SET @var_name = expr;

Examples:

SET @name = 43;
SET @total_tax = (SELECT SUM(tax) FROM taxable_transactions);

As demonstrated by those statements, expr can range from simple (a literal value) to more complex (the value returned by a scalar subquery).

The Performance Schema user_variables_by_thread table contains information about user-defined variables. See Section 25.12.10, “Performance Schema User-Defined Variable Tables”.

Parameter and Local Variable Assignment

SET applies to parameters and local variables in the context of the stored object within which they are defined. The following procedure uses the increment procedure parameter and counter local variable:

CREATE PROCEDURE p(increment INT)
BEGIN
  DECLARE counter INT DEFAULT 0;
  WHILE counter < 10 DO
    -- ... do work ...
    SET counter = counter + increment;
  END WHILE;
END;

System Variable Assignment

The MySQL server maintains system variables that configure its operation. A system variable can have a global value that affects server operation as a whole, a session value that affects the current session, or both. Many system variables are dynamic and can be changed at runtime using the SET statement to affect operation of the current server instance. (To make a global system variable setting permanent so that it applies across server restarts, you should also set it in an option file.)

If you change a session system variable, the value remains in effect within your session until you change the variable to a different value or the session ends. The change has no effect on other sessions.

If you change a global system variable, the value is remembered and used to initialize the session value for new sessions until you change the variable to a different value or the server exits. The change is visible to any client that accesses the global value. However, the change affects the corresponding session value only for clients that connect after the change. The global variable change does not affect the session value for any current client sessions (not even the session within which the global value change occurs).

Note

Setting a global system variable value always requires special privileges. Setting a session system variable value normally requires no special privileges and can be done by any user, although there are exceptions. For more information, see Section 5.1.8.1, “System Variable Privileges”.

The following discussion describes the syntax options for setting system variables:

To assign a value to a global system variable, precede the variable name by the GLOBAL keyword or the @@GLOBAL. qualifier:
```
SET GLOBAL max_connections = 1000;
SET @@GLOBAL.max_connections = 1000;
```
To assign a value to a session system variable, precede the variable name by the SESSION or LOCAL keyword, by the @@SESSION., @@LOCAL., or @@ qualifier, or by no keyword or no modifier at all:
```
SET SESSION sql_mode = 'TRADITIONAL';
SET LOCAL sql_mode = 'TRADITIONAL';
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET @@LOCAL.sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';
SET sql_mode = 'TRADITIONAL';
```
A client can change its own session variables, but not those of any other client.

To set a global system variable value to the compiled-in MySQL default value or a session system variable to the current corresponding global value, set the variable to the value DEFAULT. For example, the following two statements are identical in setting the session value of max_join_size to the current global value:

SET @@SESSION.max_join_size = DEFAULT;
SET @@SESSION.max_join_size = @@GLOBAL.max_join_size;

To display system variable names and values:

Use the SHOW VARIABLES statement; see Section 13.7.5.39, “SHOW VARIABLES Statement”.
Several Performance Schema tables provide system variable information. See Section 25.12.13, “Performance Schema System Variable Tables”.

SET Error Handling

If any variable assignment in a SET statement fails, the entire statement fails and no variables are changed.

SET produces an error under the circumstances described here. Most of the examples show SET statements that use keyword syntax (for example, GLOBAL or SESSION), but the principles are also true for statements that use the corresponding modifiers (for example, @@GLOBAL. or @@SESSION.).

Use of SET (any variant) to set a read-only variable:

mysql> SET GLOBAL version = 'abc';
ERROR 1238 (HY000): Variable 'version' is a read only variable

Use of GLOBAL to set a variable that has only a session value:

mysql> SET GLOBAL sql_log_bin = ON;
ERROR 1231 (42000): Variable 'sql_log_bin' can't be
set to the value of 'ON'

Use of SESSION to set a variable that has only a global value:

mysql> SET SESSION max_connections = 1000;
ERROR 1229 (HY000): Variable 'max_connections' is a
GLOBAL variable and should be set with SET GLOBAL

Omission of GLOBAL to set a variable that has only a global value:

mysql> SET max_connections = 1000;
ERROR 1229 (HY000): Variable 'max_connections' is a
GLOBAL variable and should be set with SET GLOBAL

The @@GLOBAL., @@SESSION., and @@ modifiers apply only to system variables. An error occurs for attempts to apply them to user-defined variables, stored procedure or function parameters, or stored program local variables.
Not all system variables can be set to DEFAULT. In such cases, assigning DEFAULT results in an error.
An error occurs for attempts to assign DEFAULT to user-defined variables, stored procedure or function parameters, or stored program local variables.

Multiple Variable Assignment

A SET statement can contain multiple variable assignments, separated by commas. This statement assigns a value to a user-defined variable and a system variable:

SET @x = 1, SESSION sql_mode = '';

If you set multiple system variables in a single statement, the most recent GLOBAL or SESSION keyword in the statement is used for following assignments that have no keyword specified.

Examples of multiple-variable assignment:

SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
SET @@GLOBAL.sort_buffer_size = 1000000, @@LOCAL.sort_buffer_size = 1000000;
SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;

The @@GLOBAL., @@SESSION., and @@ modifiers apply only to the immediately following system variable, not any remaining system variables. This statement sets the sort_buffer_size global value to 50000 and the session value to 1000000:

SET @@GLOBAL.sort_buffer_size = 50000, sort_buffer_size = 1000000;

System Variable References in Expressions

To refer to the value of a system variable in expressions, use one of the @@-modifiers. For example, you can retrieve system variable values in a SELECT statement like this:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode, @@sql_mode;

Note

A reference to a system variable in an expression as @@var_name (with @@ rather than @@GLOBAL. or @@SESSION.) returns the session value if it exists and the global value otherwise. This differs from SET @@var_name = expr, which always refers to the session value.

13.7.4.2 SET CHARACTER SET Statement

SET {CHARACTER SET | CHARSET}
    {'charset_name' | DEFAULT}

This statement maps all strings sent between the server and the current client with the given mapping. SET CHARACTER SET sets three session system variables: character_set_client and character_set_results are set to the given character set, and character_set_connection to the value of character_set_database. See Section 10.4, “Connection Character Sets and Collations”.

charset_name may be quoted or unquoted.

The default character set mapping can be restored by using the value DEFAULT. The default depends on the server configuration.

Some character sets cannot be used as the client character set. Attempting to use them with SET CHARACTER SET produces an error. See Impermissible Client Character Sets.

13.7.4.3 SET NAMES Statement

SET NAMES {'charset_name'
    [COLLATE 'collation_name'] | DEFAULT}

This statement sets the three session system variables character_set_client, character_set_connection, and character_set_results to the given character set. Setting character_set_connection to charset_name also sets collation_connection to the default collation for charset_name. See Section 10.4, “Connection Character Sets and Collations”.

The optional COLLATE clause may be used to specify a collation explicitly. If given, the collation must one of the permitted collations for charset_name.

charset_name and collation_name may be quoted or unquoted.

The default mapping can be restored by using a value of DEFAULT. The default depends on the server configuration.

Some character sets cannot be used as the client character set. Attempting to use them with SET NAMES produces an error. See Impermissible Client Character

13.3.6 SET TRANSACTION Statement

SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {
    ISOLATION LEVEL level
  | access_mode
}

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

access_mode: {
     READ WRITE
   | READ ONLY
}

This statement specifies transaction characteristics. It takes a list of one or more characteristic values separated by commas. Each characteristic value sets the transaction isolation level or access mode. The isolation level is used for operations on InnoDB tables. The access mode specifies whether transactions operate in read/write or read-only mode.

In addition, SET TRANSACTION can include an optional GLOBAL or SESSION keyword to indicate the scope of the statement.

Transaction Isolation Levels

To set the transaction isolation level, use an ISOLATION LEVEL level clause. It is not permitted to specify multiple ISOLATION LEVEL clauses in the same SET TRANSACTION statement.

The default isolation level is REPEATABLE READ. Other permitted values are READ COMMITTED, READ UNCOMMITTED, and SERIALIZABLE. For information about these isolation levels, see Section 14.7.2.1, “Transaction Isolation Levels”.

Transaction Access Mode

To set the transaction access mode, use a READ WRITE or READ ONLY clause. It is not permitted to specify multiple access-mode clauses in the same SET TRANSACTION statement.

By default, a transaction takes place in read/write mode, with both reads and writes permitted to tables used in the transaction. This mode may be specified explicitly using SET TRANSACTION with an access mode of READ WRITE.

If the transaction access mode is set to READ ONLY, changes to tables are prohibited. This may enable storage engines to make performance improvements that are possible when writes are not permitted.

In read-only mode, it remains possible to change tables created with the TEMPORARY keyword using DML statements. Changes made with DDL statements are not permitted, just as with permanent tables.

The READ WRITE and READ ONLY access modes also may be specified for an individual transaction using the START TRANSACTION statement.

Transaction Characteristic Scope

You can set transaction characteristics globally, for the current session, or for the next transaction only:

With the GLOBAL keyword:
- The statement applies globally for all subsequent sessions.
- Existing sessions are unaffected.
With the SESSION keyword:
- The statement applies to all subsequent transactions performed within the current session.
- The statement is permitted within transactions, but does not affect the current ongoing transaction.
- If executed between transactions, the statement overrides any preceding statement that sets the next-transaction value of the named characteristics.
Without any SESSION or GLOBAL keyword:
- The statement applies only to the next single transaction performed within the session.
- Subsequent transactions revert to using the session value of the named characteristics.
- The statement is not permitted within transactions:
```
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.02 sec)

mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR 1568 (25001): Transaction characteristics can't be changed
while a transaction is in progress
```

A change to global transaction characteristics requires the SUPER privilege. Any session is free to change its session characteristics (even in the middle of a transaction), or the characteristics for its next transaction (prior to the start of that transaction).

To set the global isolation level at server startup, use the --transaction-isolation=level option on the command line or in an option file. Values of level for this option use dashes rather than spaces, so the permissible values are READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE.

Similarly, to set the global transaction access mode at server startup, use the --transaction-read-only option. The default is OFF (read/write mode) but the value can be set to ON for a mode of read only.

For example, to set the isolation level to REPEATABLE READ and the access mode to READ WRITE, use these lines in the [mysqld] section of an option file:

[mysqld]
transaction-isolation = REPEATABLE-READ
transaction-read-only = OFF

At runtime, characteristics at the global, session, and next-transaction scope levels can be set indirectly using the SET TRANSACTION statement, as described previously. They can also be set directly using the SET statement to assign values to the transaction_isolation and transaction_read_only system variables:

SET TRANSACTION permits optional GLOBAL and SESSION keywords for setting transaction characteristics at different scope levels.
The SET statement for assigning values to the transaction_isolation and transaction_read_only system variables has syntaxes for setting these variables at different scope levels.

The following tables show the characteristic scope level set by each SET TRANSACTION and variable-assignment syntax.

Table 13.6 SET TRANSACTION Syntax for Transaction Characteristics

Syntax	Affected Characteristic Scope
`SET GLOBAL TRANSACTION transaction_characteristic`	Global
`SET SESSION TRANSACTION transaction_characteristic`	Session
`SET TRANSACTION transaction_characteristic`	Next transaction only

Table 13.7 SET Syntax for Transaction Characteristics

Syntax	Affected Characteristic Scope
`SET GLOBAL var_name = value`	Global
`SET @@GLOBAL.var_name = value`	Global
`SET SESSION var_name = value`	Session
`SET @@SESSION.var_name = value`	Session
`SET var_name = value`	Session
`SET @@var_name = value`	Next transaction only

It is possible to check the global and session values of transaction characteristics at runtime:

SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;
SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;

Prior to MySQL 5.7.20, use tx_isolation and tx_read_only rather than transaction_isolation and transaction_read_only.

posted on 2020-03-25 15:57 jinzhenshui 阅读(240) 评论(0) 收藏举报