MySQL5.0 new features tutorial stored procedures: Third Lecture

The new SQL statements SQL statement

Variables variable

The instruction to declare a variable in a compound statement is declare.

(1) Example with two DECLARE statements

Two examples of DECLARE statements

While ... End While CREATE PROCEDURE P8 () BEGIN DECLARE a INT; DECLARE b INT; SET a = 5; SET B = 5; INSERT into T VALUES (a); SELECT S1 * A from T WHERE S1 >= b; End; /* I won ' t call this */

The variables defined in the process are not really defined, you just define them within the begin/end block (i.e., formal parameters).

Note that these variables and session variables are not the same, you cannot use modifiers @ You must clearly declare variables and their types in begin/end blocks.

Once a variable is declared, you can use it anywhere you can use a session variable, text, or column name.

(2) Example with no DEFAULT clause and SET statement

Examples of no default clauses and SET statements

CREATE PROCEDURE p9 () BEGIN DECLARE a INT/* There is no DEFAULT clause * *; DECLARE b INT/* There is no DEFAULT clause * *; SET a = 5; /* There is a SET statement */set B = 5; /* There is a SET statement */INSERT into T VALUES (a); SELECT S1 * A from T WHERE S1 >= b; End; /* I won ' t call this */

There are many ways to initialize variables. If there is no default clause, then the initial value of the variable is null. You can assign a value to a variable at any time using the SET statement.

(3) Example with DEFAULT clause

Example with DEFAULT clause

CREATE PROCEDURE P10 () BEGIN DECLARE A, b INT DEFAULT 5; INSERT into T VALUES (a); SELECT S1 * A from T WHERE S1 >= b; End; //

We've made some changes here, but the results are the same. The default clause is used here to set the initial value, which does not need to separate the implementation of the declare and SET statements.

(4) Example of call

Examples of Calls

Mysql> call P10 ()//+--------+ | S1 * a | +--------+ | 25 | | 25 | +--------+ 2 rows in Set (0.00 sec) Query OK, 0 rows Affected (0.00 sec)

The results show that the process works.

(5) Scope

Scope

CREATE PROCEDURE p11 () BEGIN DECLARE x1 CHAR (5) DEFAULT ' outer '; BEGIN DECLARE x1 CHAR (5) DEFAULT ' inner '; SELECT X1; End; SELECT X1; End; //

Now let's discuss the scope problem. There are nested begin/end blocks in the example, which is, of course, legal. It is also legal to include two variables, the names of which are X1. Internal variables enjoy higher precedence within their scope. When the end statement is executed, the internal variable disappears, and the variable is no longer visible outside of its scope, so the declared variable cannot be found beyond the stored procedure, but you can save its value either through an out parameter or by assigning its value to a session variable.

Invoke the procedure for the scope example:

Mysql> call P11 ()/+-------+ | X1 | +-------+ | Inner | +-------+ +-------+ | X1 | +-------+ | Outer | +-------+

When we see the results, the first SELECT statement retrieves the most inner variables, and the second retrieves the second-level variables

Conditions and If-then-else-conditional and If-then-else

1.

Now we can write some examples that contain conditional expressions:

CREATE PROCEDURE p12 (in Parameter1 int) BEGIN DECLARE variable1 int; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT into T VALUES (17); End IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; End IF; End; //

Here is a procedure that contains an if statement. There are two if statements, one is if statement end If, the other is if statement else statement end If. We can use complex processes here, but I'll try to make it easier for you to figure it out.

2.

Call P12 (0)//

We call this procedure, passing in a value of 0, so that the Parameter1 value will be 0.

3.

CREATE PROCEDURE p12 (in Parameter1 int) BEGIN DECLARE variable1 int; SET variable1 = parameter1 + 1; <--IF variable1 = 0 THEN INSERT into T VALUES (17); End IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; End IF; End; //

Here the variable variable1 is assigned a value of Parameter1 plus 1, so the variable variable1 after execution is 1.

4.

CREATE PROCEDURE p12 (in Parameter1 int) BEGIN DECLARE variable1 int; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN <--INSERT into T VALUES (17); End IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; End IF; End; //

Because the variable variable1 value is 1, the condition "if Variable1 = 0" is False,

IF

......

End IF

Be skipped, not executed.

5.

CREATE PROCEDURE p12 (in Parameter1 int) BEGIN DECLARE variable1 int; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT into T VALUES (17); End IF; IF parameter1 = 0 THEN <--UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; End IF; End; //

To the second if condition, the result is true, so the middle statement is executed

6.

CREATE PROCEDURE p12 (in Parameter1 int) BEGIN DECLARE variable1 int; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT into T VALUES (17); End IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; <--ELSE UPDATE t SET s1 = s1 + 2; End IF; End; //

Because the parameter Parameter1 value equals 0,update statement is executed. If the Parameter1 value is null, the next UPDATE statement is executed now there are two rows in table T, they all contain the value 5, so if we call P12, the values of the two rows will be 6.

7.

Mysql> call P12 (0)//Query OK, 2 rows affected (0.28 sec) mysql> SELECT * from t//+------+ | S1 | +------+ | 6 | | 6 | +------+ 2 rows in Set (0.01 sec)

The result is what we expect.

Case directives

1.

CREATE PROCEDURE p13 (in Parameter1 int) BEGIN DECLARE variable1 int; SET variable1 = parameter1 + 1; Case Variable1 when 0 THEN inserts into T VALUES (17); When 1 THEN inserts into T VALUES (18); ELSE INSERT into T VALUES (19); End case; End; //

We can use the case statement if we need more conditions to make a false judgment. Case statements are used as simple as if they are.

We can refer to the above example: 2.

Mysql> call P13 (1)//Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from t//+------+ | S1 | +------+ | 6 | | 6 | | 19 | +------+ 3 rows in Set (0.00 sec)

After executing the procedure, pass in the value 1, as in the example above, the value 19 is inserted into table T.

Question

Problem

Question: What is the function of call P13 (NULL)//?

Another: The call statement did those actions?

You can do this by observing what the select does, or by the code, in 5 seconds.

Answer

Answer

Mysql> call P13 (NULL)/Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from t//+------+ | S1 | +------+ | 6 | | 6 | | 19 | | 19 | +------+ 4 rows in Set (0.00 sec)

The answer is that when you call P13, MySQL inserts another record that contains a value of 19. The reason is that the variable1 value of the variable is null,case the else part of the statement is executed. I hope it will make sense to everyone. If you can't answer it, no problem, we can keep going down.

Loops Loop Statement while ... End-While loop ... End LOOP REPEAT ... End REPEAT GOTO

Below we will create some loops. We have three standard cycle modes:

While loops, loop loops, and repeat loops. There is also a non-standard way to cycle: go to (Translator: it is best not to use it, so that the process chaos).

CREATE PROCEDURE p14 () BEGIN DECLARE v INT; SET v = 0; While v < 5 does INSERT into T VALUES (v); SET v = v + 1; End while; End; //

This is the way of the while loop. I like this way, it is similar to if statement, so do not need to master a lot of new syntax. Here the insert and SET statements are between the while and the end while the loop exits when the variable v is greater than 5. Use

"SET v = 0;"

Statement to prevent a common error, if not initialized, the default variable value is NULL, and null and any value operation result is null.

While ... End While example mysql> call P14 ()//Query OK, 1 row Affected (0.00 sec)

This is the result of calling the procedure P14 without paying attention to whether the system return is "one row affected" or "five rows affected" because the count here counts only for the last insert action.

While ... End While Example:call mysql> select * from T; // +------+ | S1 | +------+ .... | 0 | | 1 | | 2 | | 3 | | 4 | +------+ 9 rows in Set (0.00 sec)

After the call, you can see that the program has inserted 5 rows into the database.

REPEAT ... End REPEAT CREATE PROCEDURE p15 () BEGIN DECLARE v INT; SET v = 0; REPEAT INSERT into T VALUES (v); SET v = v + 1; UNTIL v >= 5 end REPEAT; End; //

This is an example of a repeat loop, and the function is the same as the previous while loop. The difference is that it checks the results after execution, while the while is a pre-execution check. (translator: May be equivalent to doing while bar)

REPEAT ... End Repeat:look at the Until:until action CREATE PROCEDURE p15 () BEGIN DECLARE v INT; SET v = 0; REPEAT INSERT into T VALUES (v); SET v = v + 1; UNTIL v >= 5 <--end REPEAT; End; //

Note that there is no semicolon after the until statement, where you can write a semicolon without a semi-colon, and of course you add an extra semicolon better.

REPEAT ... End repeat:calling: Calling

Mysql> call P15 ()//Query OK, 1 row Affected (0.00 sec) mysql> SELECT COUNT (*) from t//+----------+ | COUNT (*) | +----------+ | 14 | +----------+ 1 row in Set (0.00 sec)

We can see the call to the P15 procedure and then insert a 5-line record.

LOOP ... End LOOP CREATE PROCEDURE p16 () BEGIN DECLARE v INT; SET v = 0; Loop_label:loop INSERT into T VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE Loop_label; End IF; End LOOP; End; //

These are examples of loop loops.

The loop loop does not require an initial condition, which is similar to a while loop, and it does not require an end conditions as well as a repeat loop.

LOOP ... End Loop:with if and LEAVE loop loops that contain if and LEAVE

CREATE PROCEDURE p16 () BEGIN DECLARE v INT; SET v = 0; Loop_label:loop INSERT into T VALUES (v); SET v = v + 1; IF v >= 5 THEN <--LEAVE Loop_label; End IF; End LOOP; End; //

Add an If statement inside the loop and include the leave statement in the IF statement. Here the meaning of the leave statement is to leave the loop.

The grammar of leave is a leave and cyclic statement, and the labeling of circular statements will be explained further later.

LOOP ... End loop:calling: Calling

Mysql> call P16 ()//Query OK, 1 row Affected (0.00 sec) mysql> SELECT COUNT (*) from t//+----------+ | COUNT (*) | +----------+ | 19 | +----------+ 1 row in Set (0.00 sec)

After invoking the procedure p16, the result is that another 5 rows are inserted into the table T.

Labels Marking

CREATE PROCEDURE p17 () label_1:begin label_2:while 0 = 1 do LEAVE label_2; End while; Label_3:repeat LEAVE Label_3; UNTIL 0 =0 End REPEAT; Label_4:loop LEAVE Label_4; End LOOP; End; //

In the last loop example, I used the statement label. Now here is an example of a process that contains 4 statements. We can use statement labels before the begin, while, repeat, or loop statements, and statement labels can only be used before legitimate statements. Therefore "LEAVE label_3" means leaving statements or compound statements defined as Label_3 for the statement label name.

End Labels label Terminator

CREATE PROCEDURE p18 () label_1:begin label_2:while 0 = 1 do LEAVE label_2; End While label_2; Label_3:repeat LEAVE Label_3; UNTIL 0 =0 End REPEAT label_3; Label_4:loop LEAVE Label_4; End LOOP Label_4; End Label_1; //

You can also use statement labels at the end of a statement, as you would at the beginning. These label terminators are not very useful.

They are optional. If you need them, they must be the same as the label names they start to define. In order to have good programming habits to facilitate others to read, it is best to use the end of the label.

LEAVE and Labels jumping and marking

CREATE PROCEDURE p19 (parameter1 CHAR) label_1:begin label_2:begin label_3:begin If Parameter1 is not NULL THEN if para Meter1 = ' A ' THEN LEAVE label_1; ELSE BEGIN IF parameter1 = ' B ' THEN LEAVE label_2; ELSE LEAVE Label_3; End IF; End; End IF; End IF; End; End; end;//LEAVE

Statement causes the program to jump out of complex compound statements.

Iterate

Iteration if the target is a iterate (iteration) statement, you must use the Leave statement

CREATE PROCEDURE P20 () BEGIN DECLARE v INT; SET v = 0; Loop_label:loop IF v = 3 THEN SET v = v + 1; Iterate Loop_label; End IF; INSERT into T VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE Loop_label; End IF; End LOOP; End; Iterate

Iteration) statement, like the leave statement, is also a circular reference within a loop, which is somewhat similar to the "Continue" in C, which can also appear in compound statements, referencing compound statement labels, and iterate (iterations) meaning to restart compound statements.

So let's start and look at the following loop, which is a loop that requires an iterative process:

Iterate:walking through the Loop

Deep loop

CREATE PROCEDURE P20 () BEGIN DECLARE v INT; SET v = 0; Loop_label:loop <--IF v = 3 THEN SET v = v + 1; Iterate Loop_label; End IF; INSERT into T VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE Loop_label; End IF; End LOOP; End; //

Let the loop that has defined the label run.

Iterate:walking through the Loop CREATE PROCEDURE P20 () BEGIN DECLARE v INT; SET v = 0; Loop_label:loop IF v = 3 THEN <--SET v = v + 1; Iterate Loop_label; End IF; INSERT into T VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE Loop_label; End IF; End LOOP; End; //

The value of V becomes 3, and then we increase it to 4.

Iterate:walking through the Loop CREATE PROCEDURE P20 () BEGIN DECLARE v INT; SET v = 0; Loop_label:loop IF v = 3 THEN SET v = v + 1; Iterate Loop_label; <--End IF; INSERT into T VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE Loop_label; End IF; End LOOP; End; //

Then start the iterate (iterative) process.

Iterate:walking through the Loop CREATE PROCEDURE P20 () BEGIN DECLARE v INT; SET v = 0; Loop_label:loop <--IF v = 3 THEN SET v = v + 1; Iterate Loop_label; End IF; INSERT into T VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE Loop_label; End IF; End LOOP; End; //

Here the iterate (iteration) lets the loop return to the loop's head.

Iterate:walking through the Loop CREATE PROCEDURE P20 () BEGIN DECLARE v INT; SET v = 0; Loop_label:loop IF v = 3 THEN SET v = v + 1; Iterate Loop_label; End IF; INSERT into T VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE Loop_label; <--End IF; End LOOP; End; //

When the value of V changes to 5 o'clock, the program executes the Leave statement

Iterate:walking through the Loop CREATE PROCEDURE P20 () BEGIN DECLARE v INT; SET v = 0; Loop_label:loop IF v = 3 THEN SET v = v + 1; Iterate Loop_label; End IF; INSERT into T VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE Loop_label; End IF; End LOOP; End; <--

The result of leave is to jump out of the loop, so that the running instruction reaches the final step of the compound statement.

GOTO CREATE PROCEDURE p ... BEGIN ... LABEL Label_name; ... GOTO Label_name; ... End;

The goto statement can be used in MySQL's stored procedure. Although this is not a standard SQL statement, the method of establishing a label here is not the same as in practice. This statement is slow to be eliminated for compatibility with other DBMS, so we don't mention it in the MySQL reference manual.

Grand Combination

Large combination

CREATE PROCEDURE p21 (in parameter_1 int, out parameter_2 int) LANGUAGE SQL deterministic SQL security Invoker BEGIN DECLA RE v INT; Label Goto_label; Start_label:loop IF v = v THEN LEAVE start_label; ELSE iterate Start_label; End IF; End LOOP Start_label; REPEAT while 1 = 0 does BEGIN end; End while; UNTIL v = v End REPEAT; GOTO Goto_label; end;//

The statement in the example above contains all of the syntax we talked about earlier, including parameter lists, attribute arguments, begin/end block compound statements, variable declarations, If,while,loop,repeat,leave,iterate,goto. This is an absurd stored procedure and I will not run it because there are endless loops inside. But the grammar inside is quite legal. These are the new Process control and variable declaration statements. Here we are going to touch more new things.