oracle 在PL/SQL中，以表为参数，过滤并返回
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4249010/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In PL/SQL, take a table as parameter, filter it and return it
提问by Infeligo
I'm struggling with PL/SQL functions. I'm trying to write a function which would take in a table of objects, filter that table, based on some criteria (I intend to test values against other tables) and the return filtered table.
我正在努力使用 PL/SQL 函数。我正在尝试编写一个函数，该函数将接收一个对象表，根据某些标准（我打算针对其他表测试值）和返回过滤表来过滤该表。
My table type is defined as follows:
我的表类型定义如下：
CREATE TYPE test_obj AS OBJECT (test_id NUMBER(16,0), test_name VARCHAR2(50));
CREATE TYPE test_tbl AS TABLE OF test_obj;
The function might look like this.
该函数可能如下所示。
CREATE OR REPLACE
FUNCTION filterme(i_test IN test_tbl) RETURN test_tbl AS
o_test test_tbl;
BEGIN
--NOT WORKING: SELECT INTO o_test FROM i_test t WHERE t.test_id > 10;
RETURN o_test;
END filterme;
But what do I put inside?
但是我在里面放了什么？
回答by milan
CREATE OR REPLACE FUNCTION filterme(i_test IN test_tbl)
RETURN test_tbl
AS
ret_tab test_tbl = test_tbl();
begin
for i in 1 .. i_test.count loop
if i_test(i).test_id > 10 then /* do the test */
ret_tab.extend(1);
ret_tab(ret_tab.count) := i_test(i);
end if;
end loop;
return ret_tab;
end;
回答by Erich Kitzmueller
Not sure if it works with this kind of collection, but you might want to try
不确定它是否适用于这种集合，但您可能想尝试
SELECT * bulk collect INTO o_test FROM TABLE(i_test) t WHERE t.test_id > 10;
