在PL/SQL中,将表作为参数,对其进行过滤并返回 [英] In PL/SQL, take a table as parameter, filter it and return it
本文介绍了在PL/SQL中,将表作为参数,对其进行过滤并返回的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在PL/SQL函数方面苦苦挣扎.我正在尝试编写一个函数,该函数将接受对象表,并根据某些条件(我打算针对其他表测试值)和返回过滤后的表来过滤该表.
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.
我的表类型定义如下:
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;
但是我要放什么呢?
推荐答案
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;
这篇关于在PL/SQL中,将表作为参数,对其进行过滤并返回的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文