Example Analysis of MySQL calling stored procedures and functions

This article mainly introduces the example analysis of MySQL calling stored procedures and functions, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

Stored procedures and functions can be called in many ways. The stored procedure must be called using the call statement, and the stored procedure is related to the database, and you need to specify the database name if you want to execute the stored procedure in another database. For example, call dbname.procname. Stored functions are called in the same way as predefined functions in MySQL.

1. Call the stored procedure

The stored procedure is called through a call statement, and the syntax is as follows:

Call sp_name ([parameter [,...])

The call statement calls a stored procedure that was previously created with create procedure, where sp_name is the name of the stored procedure and parameter is the parameter of the stored procedure.

[example 1] define a stored procedure named CountProcl, and then call the stored procedure.

Define stored procedures:

Mysql > delimiter / / mysql > create procedure CountProcl (in sid int, out num int)-> begin-> select count (*) into num from fruits where s_id = sid;-> end / / Query OK, 0 rows affected (0.06 sec) mysql > delimiter

Call the stored procedure:

Mysql > call CountProcl (101 sec); Query OK, 1 row affected (0.08 sec)

View the returned results:

Mysql > select @ num;+-+ | @ num | +-+ | 3 | +-+ 1 row in set (0.00 sec)

The stored procedure returns the fruit type provided by the fruit vendor of the specified s_id=101. The return value is stored in the num variable, viewed using select, and the result is 3.

2. Call the storage function

In MySQL, stored functions are used in the same way as internal functions in MySQL. That is, the user-defined storage function is the same as the MySQL internal function.

[example 2] define the storage function CountProc2, and then call the function as follows:

Mysql > delimiter / / mysql > create function CountProc2 (sid int)-> returns int-> begin-> return (select count (*) from fruits where s_id = sid);-> end//Query OK, 0 rows affected (0.06 sec) mysql > delimiter

Call the storage function:

Mysql > delimiter; mysql > select CountProc2; +-+ | CountProc2 (101) | +-+ | 3 | +-+ 1 row in set (0.05sec)

As you can see, this example is the same as the result returned in the previous example, and although the definitions of stored functions and stored procedures are slightly different, the same functionality can be achieved.

Thank you for reading this article carefully. I hope the article "sample Analysis of MySQL calling stored procedures and functions" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!