在循环 PL/SQL 中执行选择并返回游标?

Question

对于谁知道PL/SQL来说，这可能是一个简单的问题。 我有一个存储过程，它在输入中接受一个 varchar 数组：

TYPE MULTI is table of VARCHAR(15) index by BINARY_INTEGER;

PROCEDURE MYPROC(
SINGLE                                IN     MULTI,
P_RESULT                              OUT    MY_PCK.MYCURSOR,
P_SOMETHING                           OUT    VARCHAR2,
);

游标之所以有效，是因为我已经在其他情况下对其进行了测试，但这是第一个带有数组参数的游标。

我的正文有问题，如何将从选择中获得的每个值分配给光标？

BEGIN 
FOR i IN SINGLE.first .. SINGLE.last
LOOP
--BEGIN OPEN P_RESULT FOR this --this on left gives me error
SELECT MT.DESCR INTO P_SOMETHING
FROM MYTABLE1 MT
WHERE MT.IDS = SINGLE(i)
AND and rownum < 2;
--dbms_output.put_line(SINGLE(i)); --if i use this instead of select i get the values i send to this procedure.
END LOOP;

我也试过：

SELECT MT.DESCR INTO P_RESULT but gives error

我做错了什么？ 谢谢建议。

Answer 1

不能在 11g 的 SQL 语句中使用 PL/SQL 集合类型。您可以创建一个 SQL 集合类型，或者找到一个您已经有权访问的类型(具有合适的字符串长度(，并使用它来至少验证机制。

例如，它使用类型为SYS.HSBLKNAMLST的局部变量，它被定义为table of varchar2(30)，足以匹配你自己的PL/SQL类型的字符串长度。该变量从传入的 PL/SQL 类型填充，然后在查询中用于打开游标：

PROCEDURE MYPROC(
SINGLE                                IN     MULTI,
P_RESULT                              OUT    MY_PCK.MYCURSOR
) IS
LOCAL_COLL SYS.HSBLKNAMLST := SYS.HSBLKNAMLST();
BEGIN 
FOR i IN SINGLE.first .. SINGLE.last
LOOP
LOCAL_COLL.extend();
LOCAL_COLL(LOCAL_COLL.last) := SINGLE(i);
END LOOP;
OPEN P_RESULT FOR
SELECT MT.DESCR
FROM MYTABLE1 MT
LEFT JOIN TABLE(LOCAL_COLL) LC
ON LC.COLUMN_VALUE = MT.IDS
WHERE LC.COLUMN_VALUE IS NULL;
END;

不过，我对您在循环中显示的查询有点困惑; 您似乎正在尝试重新打开数组每个元素的游标，尽管您可能正在尝试将每个元素的查询结果附加到同一游标 - 认为!=意味着所有行都将在某个时候被包含。我猜您正在尝试获取具有不在数组中的IDS值的所有记录。如果你真的想要所有这些，那么那就是：

OPEN P_RESULT FOR
SELECT MT.DESCR
FROM TABLE(LOCAL_COLL) LC
JOIN MYTABLE1 MT
ON MT.IDS = LC.COLUMN_VALUE;

---

您可以通过查询数据字典来查看哪些内置类型可供您使用，例如：

select owner, type_name, coll_type, elem_type_name, length
from all_coll_types
where elem_type_name = 'VARCHAR2'
and coll_type = 'TABLE'
and owner = 'SYS'
order by length;

如果可以的话，最好创建自己的 SQL 类型。