mysql - 存储过程不给出多个输出参数的结果

Question

我不擅长编写存储过程。请帮我解决这个问题。我使用mysql工作台。我创建了存储过程来通过两个 select 语句获取两个输出。但它只给出存储过程中最后写入的一条 select 语句的输出。以下是程序。

  CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(IN date_x DATE,   OUT emplist3 varchar(200), OUT emplist4 varchar(200))
  BEGIN
  select employee.name as emplist3
  from employee
  where employee.id IN (
    select emp_position.employee_id
    from emp_position
    where emp_position.employee_id NOT IN (
       select emp_event.employee_id
       from emp_event
       where emp_event.date = date_x
    )
    AND emp_position.position = "Cameraman"
   );

  select employee.name as emplist4
  from employee
  where employee.id IN (
     select emp_position.employee_id
     from emp_position
     where emp_position.employee_id NOT IN (
        select emp_event.employee_id
        from emp_event
        where emp_event.date = date_x
     )
     AND emp_position.position = "Camera_Assistant"
     );
    END

请帮我从上面的过程中得到 emplist3 和 emplist4 的结果。谢谢你。

Answer 1

根据您的需要调整和修改以下示例。

mysql> DELIMITER //

mysql> DROP PROCEDURE IF EXISTS `new_procedure`//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(
    ->   IN `date_x` DATE,
    ->   OUT `emplist3` VARCHAR(200),
    ->   OUT `emplist4` VARCHAR(200)
    -> )
    -> BEGIN
    ->   SELECT 'EMPLOYEE.NAME 1' INTO `emplist3`
    ->   FROM DUAL
    ->   WHERE `date_x` = `date_x`;
    -> 
    ->   SELECT 'EMPLOYEE.NAME 2' INTO `emplist4`
    ->   FROM DUAL
    ->   WHERE `date_x` = `date_x`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> SET @`NAME1` := NULL,
    ->     @`NAME2` := NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL `new_procedure`(DATE('2000-01-01'), @`NAME1`, @`NAME2`);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @`NAME1`, @`NAME2`;
+-----------------+-----------------+
| @`NAME1`        | @`NAME2`        |
+-----------------+-----------------+
| EMPLOYEE.NAME 1 | EMPLOYEE.NAME 2 |
+-----------------+-----------------+
1 row in set (0.00 sec)