Oracle PL/SQL Einzel-Tipp ansehen

Rückgabe der Function in einen Typ

Tipp 47

CREATE OR REPLACE TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(2001);
/

CREATE OR REPLACE FUNCTION getEmpArray RETURN EMPARRAY
AS
  l_data EmpArray := EmpArray();
  CURSOR c_emp IS SELECT ename FROM EMP;
  BEGIN
    FOR emp_rec IN c_emp LOOP
      l_data.extend;
      l_data(l_data.count) := emp_rec.ename;
    END LOOP;
    RETURN l_data;
END;
/

SELECT getemparray FROM dual;

/*GETEMPARRAY
--------------------------------------------------------------------------------
EMPARRAY('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT',
 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER')
*/