Mysql stored procedures and functions

Your current location: Home Page tutorial programming mysql database mysql stored procedures and functions thanks 3lian8 delivery time: Source: Triple tutorial stored procedures is a piece of code, composed of declarative and procedural SQL statements stored in a database directory

Your current location: Home> tutorial> programming and development> mysql database> mysql stored procedures and functions thanks to 3lian8 delivery time: Source: the stored procedure stored in a three-way tutorial is a piece of code. It consists of a database directory, declarative and procedural SQL statements.

Your current location: Home> tutorial> programming and development> mysql database> mysql stored procedures and functions

Mysql stored procedures and functions

Thanks for the delivery time of 3lian8: Source: Sanlian tutorial

Stored Procedure

A stored procedure is a piece of code that consists of declarative and procedural SQL statements stored in a database directory, you can call a program, trigger, or stored procedure to activate it.

Each Stored Procedure contains at least three parts: a parameter list, a stored procedure, and a name.

The name of a stored procedure in a database must be unique, just like the name of a table.

A parameter list can contain 0, 1, or multiple parameters. With these parameters, the process can be associated with the outside world.

Stored Procedures support parameter types in 3:

1. input parameter IN: data can be transmitted to the stored procedure;

2. output parameter OUT: data can be transmitted from the stored procedure to the outside world;

3. Input and Output Parameters INOUT: can act as either an input parameter or an output parameter.

Like C functions, even if there are no parameters, the process name must be followed by a pair of parentheses.

The stored procedure ends with begin and can also be nested with begin-end blocks.

Local variables:

Declare Variable list variable type [default value]

Stored Procedures can use not only local variables, but also global variables.

The default value is not limited to the direct quantity, it can be an expression or a scalar quantum query.

?

1

2

3

4

5

6

7

8

Mysql> delimiter //

Mysql> create procedure test (in a integer)

-> Begin

-> Declare B integer default

-> (Select count (*) from student );

-> End

-> //

Query OK, 0 rows affected (0.42 sec)

Set statement

Set is used to assign values to a variable. For example:

?

1

2

3

Set a = 1;

Set a: = 1;

Set a = 1, B: =;

Leave statement

Leave a block (loop block or statement block), similar to break;

As follows.

?

1

2

3

4

Mysql> create procedure test (in a integer)

-> Block: begin

-> Leave block;

-> End //

Iterate statement

Enter a loop.

Call Statement

Call the stored procedure.

If-esle statement

Format:

If condition then statement;

Elseif condition then statement;

Esle statement;

End if

?

1

2

3

4

5

6

7

8

9

Mysql> create procedure test (in a integer)

-> Begin

-> Declare B integer;

-> If a <60 then set B =-1;

-> Elseif a> 60 then set B = 1;

-> Else set B = 0;

-> End if;

-> End

-> //

Case statement

Format:

Case

When condition then statement;

When condition then statement;

Else statement;

End case;

While statement

Format:

While condition do

Statement;

End while;

?

1

2

3

4

5

6

7

Mysql> create procedure test (in a integer)

-> Begin

-> Declare B integer default 1;

-> While B <a do

-> Set B = B + 1;

-> End while;

-> End //

Repeat statement

Format:

Repeat

Statement;

Until condition end repeat;

?

1

2

3

4

5

6

7

Mysql> create procedure test (in a integer)

-> Begin

-> Declare B integer default 1;

-> Repeat

-> Set B = B + 1;

-> Until B> a end repeat;

-> End //

Loop statement

Format:

Loop

If or case condition leave loop;

Statement;

End loop;

?

1

2

3

4

5

6

7

8

9

Mysql> create procedure test (in a integer)

-> Begin

-> Declare B integer default 1;

-> Loop_block: loop

-> If B> a then leave loop_block;

-> End if;

-> Set B = B + 1;

-> End loop;

-> End //

Select into statement

Assign the query result of select to the variable in the process.

?

1

2

3

4

Mysql> create procedure test (out B integer)

-> Begin

-> Select count (*) into B from student;

-> End //

Currently, student has four data records. The following test is called:

?

1

2

3

4

5

6

7

8

Mysql> set @ B = 0 //

Mysql> call test (@ B )//

Mysql> select @ B //

+ ------ +

| @ B |

+ ------ +

| 4 |

+ ------ +

If the query result of a select statement contains multiple rows, it is not feasible to directly assign values using. For example:

?

1

2

3

4

Mysql> create procedure test (out B integer)

-> Begin

-> Select stu_id into B from student;

-> End //

Although the syntax is correct, an error is reported during the call:

?

1

2

Mysql> call test (@ B )//

ERROR 1172 (42000): Result consisted of more than one row

What should I do?

Use a cursor to access multiple rows of data

Using a cursor involves four statements:

Declare cursor, open cursor, fetch cursor (get a row of data), and close cursor.

Format:

Declare: cursor for Table query statement

Calculate the number of rows in the student table.

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Mysql> create procedure test (out a integer)

-> Begin

-> Declare found boolean default true; -- found is used to determine whether the last row is reached.

-> Declare B integer;

-> Declare num cursor for select stu_id from student; -- defines the cursor

-> Declare continue handler for not found set found = false;

-> Set a = 0;

-> Open num; -- open the cursor

-> Fetch num into B; -- read a row

-> While found do

-> Set a = a + 1;

-> Fetch num into B;

-> End while;

-> Close num; -- close the cursor

-> End //

Declare handler statement

The stored procedure may encounter errors during execution. The declare handler statement is used to solve the problem.

Format:

Declare handler <条件> Solution

Including: continue, exit, undo

　　 <条件> Including: sqlwarning, not found, and sqlexception

Therefore, the preceding "declare continue handler for not found set found = false;" means that when the cursor reaches the end of the row, the execution continues and set found = false.

Drop statement

Delete a stored procedure;

Drop procedure [if exists] process name

Storage Functions

Stored functions are similar to stored procedures: they are code snippets composed of SQL statements and procedural statements that can be called from applications and SQL statements.

Differences:

1. A storage function can have input parameters, but cannot have output parameters. The storage function itself is the output parameter.

2. The call of the stored function is the same as that of the call of the two functions in the familiar table. A call statement cannot be used to call the stored function.

3. The stored function must contain a return statement.

Format:

Create function Name ( <参数列表> ) Return type

Begin

Function body;

End

?

1

2

3

4

5

6

7

8

9

10

11

12

Mysql> create function dd (ss char (20 ))

-> Returns date

-> Begin

-> Return (date (ss ));

-> End //

Mysql> select dd ('2017-12-12 12:12:12 ')//

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

| Dd ('2017-12-12 12:12:12 ') |

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

| 2012-12-12 |

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

1 row in set (0.00 sec)

?

1

2

3

4

5

6

7

Mysql> select * from student //

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

| Name | address | sid | score |

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

| Zh | beijing | 1 | 70 |

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

1 row in set (0.00 sec)

?

1

2

3

4

5

Mysql> create function dd (id int)

-> Returns int

-> Begin return (select score from student where id = id );

-> End //

Query OK, 0 rows affected (0.00 sec)

?

1

2

3

4

5

6

7

Mysql> select dd (1 )//

+ ------- +

| Dd (1) |

+ ------- +

| 70 |

+ ------- +

1 row in set (0.00 sec)

Related Articles

[Back to triple homepage] [back to mysql database]/[join triple collection]