如何修复ORA-01427

问题描述

我正在将存储过程从SQL转换为Oracle，但出现此错误:

I'm working on converting a stored procedure from SQL to Oracle and I get this error:

ORA-01427:单行子查询返回多个行ORA-06512:位于"CMIUSER.PROCEDURE3"的第21行ORA-06512:在第7行

ORA-01427: single-row subquery returns more than one rowORA-06512: at "CMIUSER.PROCEDURE3", line 21ORA-06512: at line 7

t_name_match和t_descript_match是全局临时表.

t_name_match and t_descript_match are global temp tables.

有人可以向我解释我做错了什么吗?

Can someone explain to me what I'm doing wrong?

create or replace PROCEDURE "PROCEDURE3" 
(
  IN_SEARCH_TEXT IN NVARCHAR2 
, OUT_O_RC OUT SYS_REFCURSOR 
) AS  
BEGIN
/*******************************************************************************    ***
** Name matches
********************************************************************************    ***/
/*
** Load matches to Name Table
*/
INSERT INTO t_name_match (KBID, SYMBOLID, FEATURENAME)
SELECT  fm.KBID, fm.SYMBOLID, fm.FEATURENAME
  from  FEATURE_MASTER fm
 where  Upper(featurename) like IN_SEARCH_TEXT;
/*
** Add description for each of these matching names.
*/

UPDATE  t_name_match nm
SET nm.DESCRIPT = (SELECT x.DESCRIPT
  from  CHAR_FEATURE_XRF    x
  where x.KBID = nm.KBID
    and x.SYMBOLID = nm.SYMBOLID);

/*******************************************************************************    ***
** Description matches
********************************************************************************    ***/
/*
** Load matches to Descript table
*/
INSERT INTO t_descript_match (KBID, SYMBOLID, DESCRIPT)
SELECT  x.KBID, x.SYMBOLID, x.DESCRIPT
  from  CHAR_FEATURE_XRF x
 where  Upper(descript) like IN_SEARCH_TEXT;


/*
** Add name for each of these matching descriptions.
*/

UPDATE  t_descript_match dm
SET dm.FEATURENAME = (SELECT f.FEATURENAME
  from  FEATURE_MASTER      f
  where f.KBID = dm.KBID
    and f.SYMBOLID = dm.SYMBOLID);

/*******************************************************************************    ***
** Return result set
********************************************************************************    ***/
 OPEN OUT_O_RC FOR
SELECT nm.featurename, nm.descript
  from  t_name_match nm
  UNION
 SELECT dm.featurename, dm.descript
  from  t_descript_match dm
ORDER BY featurename, descript;



END PROCEDURE3;

推荐答案

(我注意到)唯一具有单行子查询"的地方是:

The only place (that I notice) where you have a "single row subquery" is:

UPDATE  t_name_match nm
    SET nm.DESCRIPT = (SELECT x.DESCRIPT
                       from  CHAR_FEATURE_XRF x
                       where x.KBID = nm.KBID and x.SYMBOLID = nm.SYMBOLID
                      );

如何修复它取决于您要执行的操作.两种简单的方法是MAX()和rownum = 1:

How you fix it depends on what you want to do. Two simple ways are MAX() and rownum = 1:

UPDATE  t_name_match nm
    SET nm.DESCRIPT = (SELECT MAX(x.DESCRIPT)
                       from  CHAR_FEATURE_XRF x
                       where x.KBID = nm.KBID and x.SYMBOLID = nm.SYMBOLID
                      );

UPDATE  t_name_match nm
    SET nm.DESCRIPT = (SELECT x.DESCRIPT
                       from  CHAR_FEATURE_XRF x
                       where x.KBID = nm.KBID and x.SYMBOLID = nm.SYMBOLID and
                             rownum = 1
                      );

哦，我看到有两个地方.另一个是:

Oh, I see there are two places. The other is:

UPDATE t_descript_match dm
    SET dm.FEATURENAME = (SELECT f.FEATURENAME
                          from  FEATURE_MASTER f
                          where f.KBID = dm.KBID and f.SYMBOLID = dm.SYMBOLID
                         );

您将以相同的方式修复它.

You would fix it the same way.

这篇关于如何修复ORA-01427的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！