我们可以创建一个带有 IN和OUT参数的存储过程，从MySQL表中获取多个值。为了让它理解，我们以一个名为“student_info”的表为例，该表包含以下数据−
mysql> Select * from student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Jaipur | Literature | | 110 | Rahul | Chandigarh | History | | 125 | Raman | Bangalore | Computers | +------+---------+------------+------------+ 4 rows in set (0.01 sec)
现在，通过创建以下名为‘select_studentinfo’的过程，我们可以通过提供‘id’的值从‘student_info’表中选择值−
mysql> DELIMITER // ;
mysql> Create Procedure Select_studentinfo ( IN p_id INT, OUT p_name varchar(20),OUT p_address varchar(20), OUT p_subject varchar(20))
-> BEGIN
-> SELECT name, address, subject INTO p_name, p_address, p_subject
-> FROM student_info
-> WHERE id = p_id;
-> END //
Query OK, 0 rows affected (0.03 sec)在上面的查询中，除了1个IN参数外，还有4个OUT参数。现在，按照我们要提供的条件值调用该过程，如下所示 −
mysql> DELIMITER ; // mysql> CALL Select_studentinfo(110, @p_name, @p_address, @p_subject); Query OK, 1 row affected (0.06 sec) mysql> Select @p_name AS Name,@p_Address AS Address, @p_subject AS Subject; +--------+------------+-----------+ | Name | Address | Subject | +--------+------------+-----------+ | Rahul | Chandigarh | History | +--------+------------+-----------+ 1 row in set (0.00 sec)
