oracle 表类型 表变量 例子

-- 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 (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
/

CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/

-- Build the table function itself.
create or replace function get_tab_tf 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, i, i, i, sysdate, i, i, i);
END LOOP;*/
for c_emp in (select * from scott.emp) loop
l_tab.extend;
l_tab(l_tab.last) := t_tf_row(c_emp.empno,
c_emp.ename,
c_emp.job,
c_emp.mgr,
c_emp.hiredate,
c_emp.sal,
c_emp.comm,
c_emp.deptno);
end loop;

return l_tab;
end;
/


-- Test it.
SELECT *
FROM TABLE(get_tab_tf())
ORDER BY EMPNO DESC;