MySql程序错误:1242

Question

我是sql初学者..我想让程序像oracle中的序列一样
制作程序时，遇到错误:1242...请帮帮我~

<小时/>

1. 目标表

   CREATE TABLE id_generator (

      seq_currval INT(8) UNSIGNED NOT NULL, 
      org_code varchar(4) not null
   

   );

2. 程序

   delimiter $$

   DROP PROCEDURE IF EXISTS sp_seq$$

   CREATE PROCEDURE sp_seq( IN org_code varchar(4), OUT parma1 VARCHAR(11) )

   BEGIN

      DECLARE year   VARCHAR(1);
      DECLARE month   VARCHAR(2);
      DECLARE seq_current int;
      SET year = '5';
      SET month = '01';
      SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   

      START TRANSACTION;
   
      SET seq_current = (select seq_currval 
                         from id_generator 
                         where org_code = org_code);
      update id_generator 
      set seq_currval = seq_current + 1 where org_code = org_code;
   
      COMMIT;
   
      SET seq_current = seq_current + 1;
   
      select CONCAT(year, month ,seq_current);
   

   END $$

   分隔符；

3. 调用过程后，我发现错误:1242:

   CALL sp_seq('1234');

   --> ERROR : Error Code: 1242. Subquery returns more than 1 row

Answer 1

此查询

SET seq_current = (select seq_currval 
                      from id_generator 
                      where org_code = org_code);

返回多条记录且无法存储在单个变量中。

where org_code = org_code

始终为真。将输入参数重命名为列名称以外的名称。否则数据库无法区分它们。