table functions
-- Create the types to support the table function.
DROP TYPE t_tf_tab;
DROP TYPE t_tf_row;
CREATE TYPE t_tf_row AS OBJECT (
id NUMBER,
description VARCHAR2(50)
);
/
CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/
-- Build the table function itself.
CREATE OR REPLACE FUNCTION get_tab_tf (p_rows IN NUMBER) RETURN t_tf_tab AS
l_tab t_tf_tab := t_tf_tab();
BEGIN
FOR i IN 1 .. p_rows LOOP
l_tab.extend;
l_tab(l_tab.last) := t_tf_row(i, 'Description for ' || i);
END LOOP;
RETURN l_tab;
END;
/
-- Test it.
SELECT *
FROM TABLE(get_tab_tf(10))
ORDER BY id DESC;
ID DESCRIPTION
---------- --------------------------------------------------
10 Description for 10
9 Description for 9
10 rows selected.
SQL>
Are there any code examples left?
New code examples in category SQL
-
SQL 2022-03-27 22:45:25 how to get mysql on ubuntu
-
SQL 2022-03-27 20:00:47 install latest mysql on debian 9
-
SQL 2022-03-27 19:45:11 sql '=' cannot be applied to date varchar(10) athena
-
SQL 2022-03-27 19:20:41 sql is not null
-
SQL 2022-03-27 19:20:39 sql group by error
-
SQL 2022-03-27 18:35:10 replace null with 0 in sql
-
SQL 2022-03-27 17:30:32 concat column value of same user in mysql
-
SQL 2022-03-27 17:05:02 sql developer sql worksheet not showing
-
SQL 2022-03-27 16:45:07 kill a pid redshift
-
SQL 2022-03-27 16:00:09 joins in sql server