Learn MYSQL stored procedure _ MySQL

Learn about MYSQL Stored Procedure 1.

Stored Procedure is a set of SQL statements for specific functions. it is compiled and Stored in the database. You can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters. Stored procedures are an important object in databases. any well-designed database application should use stored procedures.

Although commonly used terms are stored procedures, MySQL actually implements two types. apart from stored procedures, there are also stored functions (stored routine), which are collectively referred to as storage routines.

II. basic format
1. Stored Procedure
Create procedure process name ([process parameter [,...])
[Features...] process body

For example, create ceate procedure p1 (a INT)

SELECT;

CALL p1 (8 );

Will display:

+ ------ +

| A |

+ ------ +

| 8 |

+ ------ +

1 row in set (0.00 sec)
2. storage functions
Create function name ([FUNCTION parameter [,...])
RETURNS return type
[Features...] function body

For example, create function f1 (x INT)

RETURNS INT

RETURN x;/* The Process function can RETURN only one value at a time.

Call: SELECT f1 (3 );

Will display:

+ ------- +

| F1 (3) |

+ ------- +

| 3 |

+ ------- +

1 row in set (0.00 sec)

3. process parameters
[IN | OUT | INOUT] parameter name parameter type
4. function parameters
Parameter name parameter type
5. return type
A valid MySQL data type is enough.
6. the format of the process body/function body is as follows:
BEGIN
Valid SQL statements
END

7. features (not required generally)
LANGUAGE SQL
| [NOT] DETERMINISTIC
| {Contains SQL | no SQL | reads SQL data | modifies SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'string'

8. IN, OUT, and INOUT types of parameters IN the stored procedure

The stored procedure can accept input parameters and return the parameters to the caller. However, for each parameter, you need to declare its parameter name and data type, and specify whether this parameter is used to transmit information to the process, return information from the process, or both.

To declare a parameter, use one of the following three keywords:

IN: The IN parameter is only used to pass information to the process. it is the default value.

Timed OUT: The OUT parameter is only used to return information from the process.

˜ INOUT: The INOUT parameter can pass information to the process. if the value changes, it can be called from outside the process.

If you only want to pass data to the MySQL stored procedure, use the "in" type parameter. if you only return values from the MySQL stored procedure, use the "out" type parameter; if you need to pass the data to the MySQL stored procedure, you need to pass it back to us after some computation. in this case, you need to use the "inout" type parameter.

For any parameter declared as OUT or INOUT, you need to add the @ symbol before the parameter name when calling the stored procedure, so that the parameter can be called outside the process.

The following are three examples: MySQL stored procedure "in" parameter: similar to the value transfer of C-language function parameters, MySQL stored procedure may modify this parameter, however, the modification to the in type parameter is invisible to the caller (caller ).

Create procedure pr_param_in (in id int -- in type MySQL stored procedure parameter) begin if (id is not null) then set id = id + 1; end if; select id as id_inner; end; set @ id = 10; call pr_param_in (@ id); select @ id as id_out; mysql> call pr_param_in (@ id ); + ---------- + | id_inner | + ---------- + | 11 | + ---------- + mysql> select @ id as id_out; + -------- + | id_out | + -------- + | 10 | + -------- + the input value of user variable @ id is 10. after the stored procedure is executed, the internal value of the process is 11. (Id_inner), but the external variable value is still 10 (id_out ).

MySQL stored procedure "out" parameter: transfers a value from the stored procedure to the caller. In a stored procedure, the initial value of this parameter is null, regardless of whether the caller sets a value for the stored procedure parameter.

Create procedure pr_param_out (out id int) begin select id as id_inner_1;/* the initial value of id is null */if (id is not null) then set id = id + 1; select id as id_inner_2; else select 1 into id; end if; select id as id_inner_3; end; set @ id = 10; call pr_param_out (@ id); select @ id as id_out; mysql> set @ id = 10; mysql> call pr_param_out (@ id ); /* External variables cannot be passed to the stored procedure */+ ------------ + | id_inner_1 | + ------------ + | NULL | +- ----------- ++ ------------ + | Id_inner_3 | + ------------ + | 1 | + ------------ + mysql> select @ id as id_out; /* the process passes the changed value to the external variable */+ -------- + | id_out | + -------- + | 1 | + -------- +, although we set the user-defined variable @ id to 10, after passing @ id to the stored procedure, the initial value of id is always null (id_inner_1) within the stored procedure ). The last id value (id_out = 1) is returned to the caller.

The inout parameter of the MySQL stored procedure is similar to the out parameter, and can be passed to the caller from the stored procedure. The difference is that the caller can also pass the value to the stored procedure through the inout parameter.

Drop procedure if exists pr_param_inout; create procedure pr_param_inout (inout id int) begin select id as id_inner_1; -- the id value is the value passed by the caller if (id is not null) then set id = id + 1; select id as id_inner_2; else select 1 into id; end if; select id as id_inner_3; end; set @ id = 10; call pr_param_inout (@ id); select @ id as id_out; mysql> set @ id = 10; mysql> call pr_param_inout (@ id); + ------------ + | id_ I Nner_1 | + ------------ + | 10 | + ------------ ++ ------------ + | bytes | + ------------ + | 11 | + ------------ + | id_inner_3 | + ------------ + | 11 | + ------------ + mysql> select @ id as id_out; + -------- + | id_out | + -------- + | 11 | + -------- + the result shows that after passing @ id (10) to the stored procedure, the stored procedure finally returns the calculation result 11 (id_inner_3) to the caller. The inout parameter of the MySQL stored procedure is similar to that of the C-language function.

Using the preceding example: if you only want to pass data to the MySQL stored procedure, use the "in" type parameter. if you only return values from the MySQL stored procedure, use the "out" type parameter; if you need to pass the data to the MySQL stored procedure, you need to pass it back to us after some computation. in this case, you need to use the "inout" type parameter.

9. declare and set variables

(1) declare variables

Before using local variables in the storage routine, you must DECLARE local variables. the variable declaration is implemented using the DECLARE statement. its prototype is as follows:

DECLARE variable_name TYPE;

For example, DECLARE x VARCHAR (254 );

When declaring a variable, the declaration must be placed in the BEGIN/END block. In addition, the statement must be performed before any other statements of the block are executed.

(2) set variables

The SET statement is used to SET the declared storage routine variable value. The prototype is as follows:

SET variable_name = value;

The following statements are used to declare and set variables:

DECLARE x INT;

SET x = 155;

You can also use SELECT ..... INTO statement.

For example:

SELECT 155 INTO x;

Of course, this variable is a local variable within the scope of the variable's BEGIN/END block. If you want to use this variable outside the storage routine, you need to pass it as the OUT variable.

10. execute the storage routine

The execution of storage routines is generally completed through the CALL and SELECT statements.

III. Multi-statement storage routine

The single-statement storage routine is very useful, but the real function of the storage routine is that it can encapsulate and execute multiple SQL statements.

The following describes the common syntax used to create a multi-statement storage routine.

1. BEGIN and END

When creating a multi-statement storage routine, you must enclose the statements in the BEGIN/END block.

The prototype of this block is as follows:

BEGIN

Statement 1;

Statement 2;

......

Statement N;

END

Note: each statement in the block must end with a semicolon.

2. conditional statements

Explain IF-ELSEIF-ELSE statement

Similar to the if statement in c.

The prototype is as follows:

IF condition1 THEN

Statement1;

ELSEIF condition2 THEN

Statement2;

.......

END IF

CASE Statement

The CASE statement is useful when you need to compare a set of possible values. It is also a condition judgment statement.

The prototype is as follows:

CASE

WHEN condition1 THEN statement1;

WHEN condition2 THEN statement2;

.........

End case;

3. iteration

Some tasks need to be able to execute a group of statements repeatedly. The following describes the methods for iterative execution and loop exit.

˜ ITERATE statement

Executing the ITERATE statement will return the LOOP, REPEAT, or while loop embedded in the statement to the top and execute it here.

The prototype is as follows:

ITERATE label

˜ LEAVE statement

After obtaining the value of the variable or the result of a specific task, you may want to use the LEAVE command to immediately exit the loop or BEGIN/END block.

The prototype is as follows:

LEAVE label

Nested LOOP statement

LOOP statements define a group of statements in the code block for continuous iteration until a LEAVE is encountered.

The prototype is as follows:

[Begin_label:] LOOP

Statement_list

End loop [end_label]

˜ REPEAT statement

The REPEAT statement is almost the same as the WHILE statement in the C language .... WHERE statement.

The prototype is as follows:

REPEAT

Statement_list

UNTIL condition

END REPEAT

When WHILE statement

The prototype is as follows:

WHILE condition DO

Statement_list

END WHILE

The following describes the stored procedure of a loop statement:

DELIMITER $

Drop procedure if exists 'yyw'. 'P2' $

Create procedure 'yyw'. 'P2 '()

BEGIN

Declare v int;

Set v = 0;

LOOP_LABLE: loop

If v = 3 then

Set v = v + 1;

ITERATE LOOP_LABLE;

End if;

Insert into vs values (v);/* insert the cyclic value into the data table */

Set v = v + 1;

If v> = 5 then

Leave LOOP_LABLE;

End if;

End loop;

END $

DELIMITER;

4. call a routine from another routine

DELIMITER //

Create procedure p1 ()

BEGIN

Statement_list

END //

Create procedure p2 ()

BEGIN

Statement_list

END //

Create procedure p3 ()

BEGIN

CALL p1 ();

CALL p2 ();

END //

Note: you can directly use function text input when editing using the MySQL Administrator manager;
However, when a stored procedure or function is automatically imported into a script, because MySQL uses the ";" separator by default, every statement of the process body is compiled by MySQL as a stored procedure, an error is reported during the compilation process. Therefore, use the DELIMITER keyword to declare the DELIMITER of the current segment.
When it is used up, the separator is restored. As follows:
DELIMITER $
Stored Procedures and Functions
DELIMITER;

5. delete and view storage routines

1. delete a storage routine

To delete a storage routine, run the DROP statement.

The prototype is as follows:

DROP (PROCEDURE | FUNCTION) P_name;

2. view the routine status

The prototype is as follows:

SHOW (PROCEDURE | FUNCTION) status like 'p _ name'

For example:

Show procedure status like 'p3'/G;

Note: Use the/G option to display output information in vertical format.

3. view the routine creation syntax

You can use the show create statement to view the syntax used to CREATE a specific routine.

The prototype is as follows;

Show create (PROCEDURE | FUNCTION) Db_name.P_name

VI. instances

It is generally easier to create stored procedures and modify content in MySQL Query Browser.

(1) simple addition operations

DELIMITER $

Drop procedure if exists 'yyw'. 'p4 '$

Create definer = 'Yang' @ '10. 10.19.161 'procedure 'p4 '(a int, B int)

BEGIN

Declare c int;/* declared variable

If a is null then/* IF statement

Set a = 0;

End if;

If B is null then

Set B = 0;

End if;

Set c = a + B;

Select c as sum;/* The result shows the value of c.

END $

DELIMITER;

CALL p4 (3, 4 );

Will display:

+ ------ +

| Sum |

+ ------ +

| 7 |

+ ------ +

1 row in set (0.00 sec)

(2) use of cyclic statements, data import and export of data tables, and SQL functions in stored procedures

DELIMITER $

Drop procedure if exists 'yyw'. 'p4 '$

Create definer = 'Yang' @ '10. 10.19.161 'PROCEDURE 'Pro _ prime2' (in num int)

BEGIN

Declare I, j, x, y int default 0;/* declare the variable and the default value is 0 */
Select yywID into j from text1;/* assign the value of the yywID field in txte1 to the variable j */
Select 'count', j;/* Display the count character and j value */
While I Set x = 2;
Pp1: while x <= sqrt (j) do/* call the internal function SQRT to calculate the square */
If j % x = 0 then/* if loop statement */
Set y = 1;
Leave pp1;
Else
Set x = x + 1;
End if;
End while;
If y = 1 then
Set y = 0;
Else
Set I = I + 1;
Insert into text2 values (j);/* insert the value of j into the data table text2 */
End if;
Set j = j + 1;/* implement auto-increment of j */
End while;
END $

DELIMITER;

Assume that the table text1 and text2 are created in the database respectively. The initial value of a field in text1 is 3, and text2 is null;

Run the following stored procedure:

Mysql> CALL pro_prime2 (5 );

+ -------- + ------ +

| Count | j |

+ -------- + ------ +

| Count | 3 |

+ -------- + ------ +

1 row in set (0.00 sec)

Mysql> select * from text2;

+ ------- +

| YywID |

+ ------- +

| 3 |

| 5 |

| 7 |

| 11 |

| 13 |

+ ------- +

5 rows in set (0.00 sec)

(3) a stored procedure is used to calculate the total score and average score of a table in the database.

Displays basic information (such as student ID, name, total score, and average score) automatically ).

First, create a catalog table in the database (named chengjibiao) as follows:

+ ----- + ------ + -------- + ------- + -------- +
| NUM | Name | Enlish | Maths | Physis |
+ ----- + ------ + -------- + ------- + -------- +
| 1 | Yang Ye | 92 | 87 | 96 |
| 2 | Jianfeng | 82 | 98 | 93 |
| 3 | Zhang Mei | 96 | 86 | 94 |
| 4 | Zhang Wen | 76 | 99 | 95 |
| 5 | Ye Qian | 97 | 86 | 88 |
| 6 | Fang Wen | 87 | 96 | 94 |
| 7 | Lili | 97 | 86 | 83 |
| 8 | Jia Yu | 67 | 89 | 77 |
| 9 | Wang Bo | 89 | 67 | 75 |
| 10 | Liu San | 85 | 78 | 95 |
+ ----- + ------ + -------- + ------- + -------- +

Explain write the stored procedure p1 using SQL statements

DELIMITER $

Drop procedure if exists 'yyw'. 'p1' $

Create definer = 'Yang' @ '10. 10.19.161 'procedure 'p1' (N int)

BEGIN

Declare a int;/* variable declaration */

Declare B int;

Declare c int;

Declare d int;

Declare e int;

Declare f char (100 );

Declare g decimal (4, 2 );

Set e = 1;

Create table zongping (NUM int, Name char (255), Enlish int, Maths int, Physis int, Total int, aver decimal )); /* create a data table to store the content to be displayed */

Repeat/* introduce a REPEAT loop to calculate the total score and average score of each student */

Select Enlish, Maths, Physis, Name into a, B, c, f from chengjibiao where NUM = e;

/* Export the three scores and names in the database chengjibiao and assign them to the variables a, B, c, and f respectively ;*/

Set d = a + B + c;/* sum */

Set g = (a + B + c)/3;/* calculate the average score */

Insert into zongping (NUM, Name, Enlish, Maths, Physis, Total, aver) values (e, f, a, B, c, d, g );

/* Insert the student ID, name, three scores, total score, and average score into the newly created data table zongping.

Set e = e + 1;/* This condition can end the cycle */

Until e = N/* N is set based on the number of students when the stored procedure is called */

End repeat;

Select * from zongping;/* Display the running result in the form of a data table */

Drop table zongping;/* delete the table after the result is displayed. do not delete the table */

END $

DELIMITER;

L call the stored procedure

CALL P1 (11);/* because the original orders table contains 10 columns of data, set N = 11. you can also set a different sequence table.

The result is as follows:

+ ------ + -------- + ------- +

| Name | Enlish | Maths | Physis | Total | Aver |
+ ------ + -------- + ------- +
| 1 | Yang Ye | 92 | 87 | 96 | 275 | 91.67 |
| 2 | Jianfeng | 82 | 98 | 93 | 273 | 91.00 |
| 3 | Zhang Mei | 96 | 86 | 94 | 276 | 92.00 |
| 4 | Jiang Wen | 76 | 99 | 95 | 270 | 90.00 |
| 5 | Ye Qian | 97 | 86 | 88 | 271 | 90.33 |
| 6 | Fang Wen | 87 | 96 | 94 | 277 | 92.33 |
| 7 | Lili | 97 | 86 | 83 | 266 | 88.67 |
| 8 | Jia Yu | 67 | 89 | 77 | 233 | 77.67 |
| 9 | Wang Bo | 89 | 67 | 75 | 231 | 77.00 |
| 10 | Liu San | 85 | 78 | 95 | 258 | 86.00 |
+ ------ + -------- + ------- +