我有一个接受PL/SQL关联数组值输入的存储过程,如下所示:
TYPE IntegerArray is table of Number index by binary_integer;
PROCEDURE GetItems(itemIds in IntegerArray, results out sys_refcursor)
IS BEGIN
Open Results for
select id, name, price from Item where id in (select * from table(itemIds));
END;
它是从c#中调用的,输入项的id按照特定的顺序,应该保留在输出中。我可以编写c#代码,它将根据输入List<int> ids
对结果对象数组List<(int id,string name,int price)> data
进行排序,因为有1:1的映射输入-输出,但我不能告诉的是,如果我需要这样做,或者Oracle将根据输入自动返回行。这有保证吗?Oracle有输入排序的概念吗?
如果您在SQL中定义了嵌套表集合类型(您没有,请参见下文):
CREATE TYPE IntegerList is table of Number;
可以在SQL语句中使用。
当你这样做的时候:
SELECT * FROM TABLE(instance_of_integer_list)
那么经验证据表明它将按顺序返回集合中的值。
(注意:这不能保证,因为当结果集没有ORDER BY
子句时,结果集是非确定性排序的,并且可能存在一些情况,例如并行数据库上的大数组,其中单个数据库实例上的小数组的经验证据不成立,并且可能无法维持顺序。)
然而:
SELECT id, name, price
FROM Item
WHERE id IN (SELECT * FROM TABLE(instance_of_integer_list));
不保证外部查询的特定顺序。
如果您想根据集合的顺序对外部查询进行排序,那么您需要使用JOIN
和ORDER BY
子句:
SELECT i.id, i.name, i.price
FROM Item i
INNER JOIN (
SELECT COLUMN_VALUE AS item,
ROWNUM AS position
FROM TABLE(instance_of_integer_list)
) t
ON i.id = t.item
ORDER BY t.position;
你做NOT有一个嵌套表集合类型(c#不支持传递嵌套表集合类型,它只支持PL/SQL关联数组类型)
您应该需要将PL/SQL关联数组集合转换为SQL嵌套表集合,以便能够在SQL中使用它:
CREATE PACKAGE your_package IS
TYPE IntegerArray IS TABLE OF NUMBER INDEX BY binary_integer;
FUNCTION map_ids(
Ids IN IntegerArray
) RETURN IntegerList PIPELINED;
PROCEDURE GetItems(
ItemIds IN IntegerArray,
results OUT SYS_REFCURSOR
);
END;
/
CREATE PACKAGE BODY your_package IS
FUNCTION map_ids(
Ids IN IntegerArray
) RETURN IntegerList PIPELINED
IS
v_idx BINARY_INTEGER;
BEGIN
IF ids IS NULL THEN
RETURN;
END IF;
v_idx := ids.FIRST;
WHILE v_idx IS NOT NULL LOOP
PIPE ROW(ids(v_idx));
v_idx := ids.NEXT(v_idx);
END LOOP;
END;
PROCEDURE GetItems(
ItemIds IN IntegerArray,
results OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN Results FOR
SELECT i.id, i.name, i.price
FROM Item i
INNER JOIN (
SELECT COLUMN_VALUE AS item,
ROWNUM AS position
FROM TABLE(map_ids(ItemIds))
) t
ON i.id = t.item
ORDER BY t.position;
END;
END;
/
然后,如果关联数组对其键排序的顺序对应于您想要显示数据的顺序,那么它应该工作(注意:这是未经测试的,因为我没有您的数据,表或c#应用程序来调用包)。