Detailed explanation of MySQL calling stored procedures and functions (case)

There are many ways to call stored procedures and functions. Stored procedures must be called using the call statement, and stored procedures are related to the database. If you want to execute stored procedures in other databases, you need to specify the database name. For example call dbname.procname. Stored functions are called in the same way as predefined functions in MySQL.

(Free learning recommendation: mysql video tutorial)

1. Calling a stored procedure

The stored procedure is called through the call statement. The syntax is as follows:

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

The call statement calls a stored procedure previously created with create procedure, where sp_name is the stored procedure. Name, parameter is the parameter of the stored procedure.

[Example 1] Define a stored procedure named CountProcl, and then call this stored procedure.

Define the stored procedure:

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,@num);Query OK, 1 row affected (0.08 sec)

View the return result:

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

The stored procedure returned the fruit with the specified s_id=101 The type of fruit provided by the supplier, the return value is stored in the num variable, use select to view, the return result is 3.

2. Call stored functions

In MySQL, the use of stored functions is the same as the use of MySQL internal functions. That is, user-defined stored functions have the same nature as MySQL internal functions.

[Example 2] Define the storage function CountProc2, and then call this function. The code is 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(101);+-----------------+| CountProc2(101) |+-----------------+|               3 |+-----------------+1 row in set (0.05 sec)

As you can see, this example is the same as the previous one The results returned in the example are the same. Although the definitions of stored functions and stored procedures are slightly different, the same functions can be achieved.

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of Detailed explanation of MySQL calling stored procedures and functions (case). For more information, please follow other related articles on the PHP Chinese website!