table functions

Solutions on MaxInterview for table functions by the best coders in the world

showing results for - "table functions"
Emet
27 Aug 2016
1-- Create the types to support the table function.
2DROP TYPE t_tf_tab;
3DROP TYPE t_tf_row;
4
5CREATE TYPE t_tf_row AS OBJECT (
6  id           NUMBER,
7  description  VARCHAR2(50)
8);
9/
10
11CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
12/
13
14-- Build the table function itself.
15CREATE OR REPLACE FUNCTION get_tab_tf (p_rows IN NUMBER) RETURN t_tf_tab AS
16  l_tab  t_tf_tab := t_tf_tab();
17BEGIN
18  FOR i IN 1 .. p_rows LOOP
19    l_tab.extend;
20    l_tab(l_tab.last) := t_tf_row(i, 'Description for ' || i);
21  END LOOP;
22
23  RETURN l_tab;
24END;
25/
26
27-- Test it.
28SELECT *
29FROM   TABLE(get_tab_tf(10))
30ORDER BY id DESC;
31
32        ID DESCRIPTION
33---------- --------------------------------------------------
34        10 Description for 10
35         9 Description for 9
36
3710 rows selected.
38
39SQL>