Chinaunix首页 | 论坛 | 博客
  • 博客访问: 4042834
  • 博文数量: 536
  • 博客积分: 10470
  • 博客等级: 上将
  • 技术积分: 4825
  • 用 户 组: 普通用户
  • 注册时间: 2006-05-26 14:08
文章分类

全部博文(536)

文章存档

2024年(3)

2021年(1)

2019年(1)

2017年(1)

2016年(2)

2013年(2)

2012年(10)

2011年(43)

2010年(10)

2009年(17)

2008年(121)

2007年(252)

2006年(73)

分类: Oracle

2007-01-30 15:47:05

I create a test procedure:
CREATE OR REPLACE PROCEDURE PROCE_0(
    ival IN OSM_TAB_PROCE.COL_0%TYPE:=10,
    ocnt IN OUT NUMBER)
AS
    a OSM_TAB_PROCE.COL_0%TYPE;
    r OSM_TAB_PROCE%ROWTYPE;  
    i NUMBER := 0;  
    CURSOR c1(ct0 NUMBER) IS SELECT COL_0 FROM OSM_DML_2SP.OSM_TAB_PROCE WHERE COL_0>ct0;BEGIN  
  IF c1%ISOPEN=FALSE THEN
    OPEN c1(196);
  END IF;

  LOOP
    FETCH c1 INTO a;
    EXIT WHEN c1%NOTFOUND;

    SELECT COL_0,COL_1,COL_2 INTO r FROM OSM_DML_2SP.OSM_TAB_PROCE
      WHERE COL_0=a AND ROWNUM<2; -- if ROWNUM < 2 delete then ORA-01422

    r.COL_0 := a + ival;
    r.COL_2 := TO_CHAR(a) || TO_CHAR(SYSDATE);
    INSERT INTO OSM_DML_2SP.OSM_TAB_PROCE VALUES r;
    i := i + 1;
  END LOOP;

  CLOSE c1;
  ocnt := i;
END;

Error Message:

Error:     ORA-01422: exact fetch returns more than requested number of rows
Cause:     You tried to execute a SELECT INTO statement and more than one row was returned.
Action: The options to resolve this Oracle error are:

   1. Rewrite your SELECT INTO statement so that only one row is returned.
   2. Replace your SELECT INTO statement with a cursor.

For example, if you tried to execute the following SQL statement:

    SELECT supplier_id
    INTO cnumber
    from suppliers
    where supplier_name = 'IBM';

And there was more than one record in the suppliers table with the supplier_name of IBM, you would receive the ORA-01422 error message.

In this case, it might be more prudent to create a cursor and retrieve each row if you are unsure of how many records you might retrieve.

Error Message Ref:
阅读(2932) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~