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;