Oracle-表值函数中的循环赋值

Question

这里有一个有趣的案例。我想知道是否有可能做以下事情：

在表值函数中，我需要对每个"；雇主"；然后将结果连接到要返回的表类型中。我已经简化了这些结果，比我的实际代码要简单得多，但我知道我需要为每个"；雇主"；由于所实现的逻辑而单独地。

我写了这个，但这给出了一个错误，即l_VALID_EMPLOYEE_OBJ_TABLE不存在：

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;

Answer 1

在表值函数中，我需要对每个"雇主"；然后将结果连接到要返回的表类型中。

这通常不是一个好的做法，速度会很慢，但。。。

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;
/

使用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;
/

Answer 2

不，您不需要循环，而且您不需要insert into，而是select into那个集合。

样本数据，因为我没有你的表：

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

类型：

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

功能：

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.

测试：

SQL> SELECT * FROM TABLE (get_valid_employees);
COLUMN_VALUE
--------------------------------------------------
CLARK
KING
MILLER
SQL>