表变量如何传参oracle,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;