且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

从Oracle函数返回一个表

更新时间:2022-12-03 11:21:58

T_TABLE_COLL is a collection. You cannot use insert on collections.

CREATE OR REPLACE FUNCTION FN_MyFunction
RETURN T_TABLE_COLL
IS
  l_res_coll T_TABLE_COLL;
  l_index number;
BEGIN
  l_res_coll := T_TABLE_COLL();
  FOR I IN (SELECT col1, col2 FROM Table1)
  LOOP
    IF I.col1 = 1 THEN
      l_res_coll.extend;
      l_index := l_res_coll.count;  
      l_res_coll(l_index):= T_TABLE(i.col1, i.col2);
    END IF;
  END LOOP;
  return l_res_coll;
END;

Function in action

select *
  from table(FN_MyFunction())

To get more information about what collections are and how to use them read this