Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to realize Mariadb compound sentence and process control". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "Mariadb compound sentences and process control how to achieve" it!

Compound statement

In MariaDB 10.1.1 + version, we can use compound statements externally in stored procedures, as the name implies, compound statements are executed as a whole, in which you can use some logical judgment, looping and other functions, which greatly improve the programmability of SQL language.

Using compound statements outside of stored procedures requires the following conventions:

Only BEGIN, IF, CASE, LOOP, WHILE, REPEAT statements can be used BEGIN must use BEGIN NOT ATOMIC, so that autocommit cannot be circumvented and cannot start with a label

When you want to use compound statements, you can use the following format:

BEGIN [NOT ATOMIC] [statement_list] END

Because the SQL statement uses; as the closing identifier, but for multiple SQL statements, which one is the Terminator? So we can change the Terminator of the compound statement to other characters, using the following command: | can be replaced with any number of characters.

Delimiter | / / this command is valid in the current session and will affect the Terminator compound statement examples of all subsequent SQL statements MariaDB [world] > DELIMITER | | MariaDB [world] > BEGIN NOT ATOMIC-> SELECT * FROM user;-> SELECT * FROM department;-> END-> |

The contents of the two tables are displayed sequentially, but what?

Hey, like I said, programmability has been increased, but it hasn't been judged or circulated yet.

Define local variables

Local variables only take effect within the current BEGIN..END * *. The syntax for defining a local variable is as follows:

DECLARE var_name [, var_name] type [DEFAULT value] type are those data types supported in MariaDB.

For example, the following example: query the test1 user's group ID and put it in the tmpdid variable

MariaDB [world] > BEGIN NOT ATOMIC-> DECLARE tmpdid INT DEFAULT 0;-> SELECT deptid INTO tmpdid FROM user WHERE name='test1';-> SELECT tmpdid;-> END | +-+ | tmpdid | +-+ | 1 | +-+ 1 row in set (0.00 sec)

So, BEGIN... END can be used in nesting, such as using BEGIN in IF statements. END to create a new definition domain, of course BEGIN... END is also a sign to start a transaction.

IF statement

The syntax is as follows:

IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list]... [ELSE statement_list] END IF

This is the same as in the programming language, no longer nagging, let's try it.

BEGIN NOT ATOMIC DECLARE tmpdid INT DEFAULT 0; SELECT deptid INTO tmpdid FROM user WHERE name='test1'; IF (tmpdid = 1) THEN SELECT 'The User test1 is the member of salse'; ELSE SELECT * FROM department WHERE id = tmpdid; END IF; END |

So search_condition can do as long as it is an expression that expresses TRUE or FALSE, such as:

BEGIN NOT ATOMIC IF EXISTS (SELECT * FROM user WHERE name = 'lucy') THEN SELECT' Find the user lucy'; ELSE SELECT 'Not Find'; END IF; END | +-+ | Find the user lucy | +-+ | Find the user lucy | +-+ 1 row in set (0.00 sec) CASE statement

CASE can be used in two ways, one is to select data like SWITCH in a programming language, and the other is to implement multi-branch IF-ELSE statements with the following syntax:

/ / filter CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] on a value. [ELSE statement_list] END CASE// multi-branch IF-ELSECASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list]. [ELSE statement_list] END CASE

... Can't think of an example, give up, and come back and make it up when you think of it.

An official example is attached:

DELIMITER | CREATE PROCEDURE p () BEGIN DECLARE v INT DEFAULT 1; CASE v WHEN 2 THEN SELECT v; WHEN 3 THEN SELECT 0; ELSE BEGIN END; END CASE;END; | LOOP statement

LOOP has not been used before, it seems that many programming languages do not have the keyword LOOP, to implement a simple loop, you need to work with LEAVE statements to jump out of the loop.

Set a variable, add it to 100 and exit: why add it to 100? Who knows, ha.

BEGIN NOT ATOMIC DECLARE tempNum INT DEFAULT 0; test:LOOP SET tempNum=tempNum+1; IF tempNum=100 THEN LEAVE test; END IF; END LOOP test; SELECT tempNum;END; |

So the syntax of LOOP is as follows:

[begin_label:] LOOP statement_listEND LOOP [end_label]

In general, you need to set up a begin_label to identify the loop to make it easy to use LEAVE to jump out of the loop, while end_label can be omitted, but if you want to give end_label, it must have the same name as begin_label, and the syntax of LEAVE is simple:

LEAVE labelWHILE statement

WHILE is the same as in programming languages, and the syntax is as follows:

[begin_label:] WHILE search_condition DO statement_listEND WHILE [end_label]

The loop is no longer executed when the search_condition expression is not TRUE.

Find out what the ID value of a user is and why write a loop? I don't know. Wouldn't it be better to use WHERE?

REPEAT..LOOP cycle

The REPEAT loop looks a lot like do... The while loop, well, it's the same thing.

The body of the first loop does not judge that any condition is executed once, and then determines the condition, and continues to execute if the condition is still satisfied, until the condition is not satisfied. The syntax is as follows:

[begin_label:] REPEAT statement_listUNTIL search_conditionEND REPEAT [end_label]

Concatenate all user names into a string:

MariaDB [world] > BEGIN NOT ATOMIC-> DECLARE i INT DEFAULT 1;-> DECLARE userNames VARCHAR DEFAULT'';-> DECLARE tmpName VARCHAR (10) DEFAULT'';-> DECLARE userNums INT DEFAULT 0;-> SELECT COUNT (id) INTO userNums FROM user;-> REPEAT-> SELECT name INTO tmpName FROM user WHERE id = I;-> SET userNames = CONCAT (userNames,',',tmpName) -> SET I = I + 1;-> UNTIL NOT i END REPEAT;-> SELECT userNames -> END | +-+ | userNames | | +-+ | Test,test1,lucy,mars,mark,test6,test7,test7,test8,test8,test9,test10,test11,test12,test13 | +-+ to this point I believe that you have a deeper understanding of "Mariadb compound statements and process control", might as well come to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.