ORA-01422:精确提取返回的行数超过了函数中请求的行数

Question

Select语句在Resault中返回多行。是否可以用此函数获得resault中的所有行？

CREATE OR REPLACE PACKAGE PKG_EMP AS

    TYPE measure_record IS RECORD(
       ename VARCHAR2(50), 
       sal VARCHAR2(50), 
       empno number);

    TYPE measure_table IS TABLE OF measure_record;

    FUNCTION get_emp(id NUMBER)
        RETURN measure_table
        PIPELINED;
END;

CREATE OR REPLACE PACKAGE BODY PKG_EMP AS

    FUNCTION get_emp(id number)
        RETURN measure_table
        PIPELINED IS

        rec  measure_record;

    BEGIN
        SELECT ename, sal, empno
          INTO rec
          FROM emp where deptno = id;   
        PIPE ROW (rec);
       

    RETURN;
    END get_emp;
END;

SELECT * FROM table(PKG_EMP.get_ups(30));

ORA-01422:精确提取返回的行数多于请求的行数

Answer 1

select...INTO...FROM...用于单行。

如果需要多行，则：

• 使用select...BULK COLLECT INTO...FROM...并遍历集合；或
• 使用光标并遍历行集。

CREATE OR REPLACE PACKAGE BODY PKG_EMP AS
  FUNCTION get_emp(
    id number
  ) RETURN measure_table PIPELINED
  IS
    rec  measure_table;
  BEGIN
    SELECT ename, sal, empno
    BULK COLLECT INTO rec
    FROM emp where deptno = id;   

    FOR i IN 1 .. rec.COUNT LOOP
      PIPE ROW (rec(i));
    END LOOP;
  END get_emp;
END;
/

CREATE TABLE emp ( empno, ename, sal, deptno ) AS
SELECT 1, 'Alice', 200, 30 FROM DUAL UNION ALL
SELECT 2, 'Beryl', 300, 30 FROM DUAL UNION ALL
SELECT 3, 'Carol', 150, 30 FROM DUAL;

sql prettyprint-override">SELECT * FROM table(PKG_EMP.get_emp(30));

ENAME | SAL | EMPNO
:---- | :-- | ----:
Alice | 200 |     1
Beryl | 300 |     2
Carol | 150 |     3

db<>在这里摆弄