Oracle 选择查询中的错误ORA-01422

嗨，
我在我的plsql过程中面临 “ORA-01422: 精确获取返回超过请求的行数”。
以下是我正在尝试的测试的详细信息:

创建表cust_bug_metadata (产品id号，组件varchar2(50)，子组件varchar2(50)，电子邮件varchar2(50))
插入cust_bug_metadata (产品id，组件，子组件，电子邮件) 值 (5757，'提取'，'检查点'，'subhas@oracle.com')
插入cust_bug_metadata (产品id，电子邮件) 值 (5757，'subhas@oracle.com')

现在创建一个过程:

创建或替换过程 “发送 _ 错误 _ 电子邮件 _ 每日”
是
组件 _ 值VARCHAR2(200);
sub_组件 _ 值VARCHAR2(200);
cursor mailCursor 是 select product_id from cust_bug_metadata;
开始
对于邮件光标循环中的m
从cust_bug_metadata中选择component，sub_component到component_value，sub_component到component_value，其中product_id = m.product_id;
结束循环;
结束;

过程被创建。

now when running th是 procedure:

开始
发送 _ bug _ 电子邮件 _ 每日;
结束
/

我得到 “ORA-01422: 精确获取返回比请求的行数更多”

Th是 是 because I am using product_id as 5757 in both the rows but th是 has to be in my table as per requirement.
Could you please let me know on how to avoid th是 error with having same product_id in both the row.

谢谢，
子哈希

这取决于您要实现的目标。

SQL> create table cust_bug_metadata(product_id number, component varchar2(50),sub_component varchar2(50), email varchar2(50));

Table created.

SQL> insert into cust_bug_metadata(product_id, component, sub_component, email) values(5757, 'extract', 'checkpoint','subhas@oracle.com');

1 row created.

SQL> insert into cust_bug_metadata(product_id, email) values(5757,'subhas@oracle.com');

1 row created.

SQL>
SQL>
SQL> create or replace procedure "SEND_BUG_EMAIL_DAILY"
  2  is
  3  component_value VARCHAR2(200);
  4  sub_component_value VARCHAR2(200);
  5  cursor mailCursor is select product_id from cust_bug_metadata;
  6  begin
  7  FOR m IN mailCursor LOOP
  8    select component,sub_component into component_value,sub_component_value from cust_bug_metadata where product_id = m.product_id;
  9  END LOOP;
 10  end;
 11  /

Procedure created.

SQL>
SQL>
SQL> exec SEND_BUG_EMAIL_DAILY;
BEGIN SEND_BUG_EMAIL_DAILY; END;

*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "MCDONAC.SEND_BUG_EMAIL_DAILY", line 8
ORA-06512: at "MCDONAC.SEND_BUG_EMAIL_DAILY", line 8
ORA-06512: at line 1

--
-- if you want to cycle through all rows, then just add another cursor
--

SQL>
SQL> create or replace procedure "SEND_BUG_EMAIL_DAILY"
  2  is
  3  component_value VARCHAR2(200);
  4  sub_component_value VARCHAR2(200);
  5  cursor mailCursor is select product_id from cust_bug_metadata;
  6  begin
  7  FOR m IN mailCursor LOOP
  8    for inner in ( select component,sub_component into component_value,sub_component_value from cust_bug_metadata where product_id = m.product_id )
  9    loop
 10      do_something;
 11    end loop;
 12  END LOOP;
 13  end;
 14  /

Procedure created.

--
-- if you want to pick just one (arbitrary) row, you can add a predicate with rownum
--

SQL>
SQL> exec SEND_BUG_EMAIL_DAILY;

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace procedure "SEND_BUG_EMAIL_DAILY"
  2  is
  3  component_value VARCHAR2(200);
  4  sub_component_value VARCHAR2(200);
  5  cursor mailCursor is select product_id from cust_bug_metadata;
  6  begin
  7  FOR m IN mailCursor LOOP
  8    select component,sub_component into component_value,sub_component_value from cust_bug_metadata where product_id = m.product_id and rownum = 1;
  9  END LOOP;
 10  end;
 11  /

Procedure created.

SQL>
SQL> exec SEND_BUG_EMAIL_DAILY;

PL/SQL procedure successfully completed.

--
-- if you know a particular criteria that will always give just one matching row (eg component = null in my test case), then use that
--

SQL>
SQL> create or replace procedure "SEND_BUG_EMAIL_DAILY"
  2  is
  3  component_value VARCHAR2(200);
  4  sub_component_value VARCHAR2(200);
  5  cursor mailCursor is select product_id from cust_bug_metadata;
  6  begin
  7  FOR m IN mailCursor LOOP
  8    select component,sub_component into component_value,sub_component_value from cust_bug_metadata where product_id = m.product_id and component is not null;
  9  END LOOP;
 10  end;
 11  /

Procedure created.

SQL>
SQL> exec SEND_BUG_EMAIL_DAILY;

PL/SQL procedure successfully completed.

SQL>
SQL>