Oracle - Looping Assignments in Table-Valued Function-CodePudding

I have an interesting case here. I want to know if it is possible to do something like the following:

In a table-valued function, I need to run a query for every single "employer" individually and then concatenate the results into the table type being returned. I have simplified these results much more than my actual code, but know that I need to be looping for each and every "employer" individually due to the logic being implemented.

I wrote this, but this gives the error that l_VALID_EMPLOYEE_OBJ_TABLE does not exist:

CREATE OR REPLACE TYPE VALID_EMPLOYEE_OBJ_TABLE IS TABLE OF NVARCHAR2(50);
/
CREATE OR REPLACE FUNCTION GET_VALID_EMPLOYEES
RETURN VALID_EMPLOYEE_OBJ_TABLE
IS l_VALID_EMPLOYEE_OBJ_TABLE VALID_EMPLOYEE_OBJ_TABLE:= VALID_EMPLOYEE_OBJ_TABLE();
BEGIN
FOR i IN (SELECT * FROM EMPLOYER_TABLE)
LOOP
INSERT INTO l_VALID_EMPLOYEE_OBJ_TABLE (SELECT EMPLOYEE FROM EMPLOYEE_TABLE WHERE EMPLOYER = i.EMPLOYER);
END LOOP;
RETURN l_VALID_EMPLOYEE_OBJ_TABLE;
END;

CodePudding user response：

No, you don't need a loop, and you don't insert into but select into that collection.

Sample data as I don't have your table:

SQL> CREATE TABLE employee_table
  2  AS
  3     SELECT ename
  4       FROM emp
  5      WHERE deptno = 10;

Table created.

Type:

SQL> CREATE OR REPLACE TYPE valid_employee_obj_table IS TABLE OF NVARCHAR2 (50);
  2  /

Type created.

Function:

SQL> CREATE OR REPLACE FUNCTION get_valid_employees
  2     RETURN valid_employee_obj_table
  3  IS
  4     l_valid_employee_obj_table  valid_employee_obj_table
  5                                    := valid_employee_obj_table ();
  6  BEGIN
  7     SELECT *
  8       BULK COLLECT INTO l_valid_employee_obj_table
  9       FROM employee_table;
 10
 11     RETURN l_valid_employee_obj_table;
 12  END;
 13  /

Function created.

Testing:

SQL> SELECT * FROM TABLE (get_valid_employees);

COLUMN_VALUE
--------------------------------------------------
CLARK
KING
MILLER

SQL>

CodePudding user response：

In a table-valued function, I need to run a query for every single "employer" individually and then concatenate the results into the table type being returned.

This is not normally good practice and will be very slow but ...

CREATE OR REPLACE FUNCTION GET_VALID_EMPLOYEES
RETURN VALID_EMPLOYEE_OBJ_TABLE
IS 
  l_emps VALID_EMPLOYEE_OBJ_TABLE:= VALID_EMPLOYEE_OBJ_TABLE();
BEGIN
  FOR i IN (SELECT * FROM EMPLOYER_TABLE)
  LOOP
    l_emps.EXTEND(1);
    l_emps(l_emps.COUNT) := i.employee;
  END LOOP;
  RETURN l_emps;
END;
/

It would be much faster and simpler to use BULK COLLECT:

CREATE OR REPLACE FUNCTION GET_VALID_EMPLOYEES
RETURN VALID_EMPLOYEE_OBJ_TABLE
IS 
  l_emps VALID_EMPLOYEE_OBJ_TABLE;
BEGIN
  SELECT employee
  BULK COLLECT INTO l_emps
  FROM   EMPLOYER_TABLE;

  RETURN l_emps;
END;
/