1、创建自定义对象类型
create or replace type t_emp as object(
employee_id integer,
hire_date date,
employee_name varchar2(60)
);
2、创建自定义表类型
create or replace type t_emp_table as table of t_emp;
3、创建自义定函数
CREATE OR REPLACE FUNCTION f_emp_table (n_id in number default null )
RETURN t_emp_table
IS
v_employee_id integer;
v_hire_date date;
v_employee_name varchar2(60);
v_objset t_emp_table := t_emp_table ();
i NUMBER := 0;
cur sys_refcursor;
BEGIN
open cur for select employee_id ,hire_date,employee_name
from dmhr.employee
where employee_id > n_id;--传参代入employee_id
LOOP
-- Fetch from cursor variable
FETCH cur
INTO v_employee_id, v_hire_date, v_employee_name;
EXIT WHEN cur%NOTFOUND;
-- exit when last row is fetched
-- append to collection
i := i + 1;
v_objset.EXTEND;
v_objset (i) := t_emp (v_employee_id, v_hire_date, v_employee_name);
END LOOP;
CLOSE cur;
RETURN v_objset;
END;
4、table函数调用
select count(*) from table(f_emp_table(11143));
执行结果:
select * from table(f_emp_table(11143));
执行结果:
select * from table(f_emp_table(11143)) where hire_date > '2014-07-01';
执行结果:
select t2.employee_id,
t2.employee_name,
t1.start_date,
t3.job_title
from dmhr.job_history t1 ,
table(f_emp_table(0)) t2 ,
dmhr.job t3
where t1.employee_id=t2.employee_id
and t1.job_id = t3.job_id;
执行结果: