oracle函数例子
如下:
select GET_CHILD_DEPT(579) from dual;
定义如下:
CREATE OR REPLACE FUNCTION GET_CHILD_DEPT
(
v_id IN NUMBER
)
RETURN type_dept_table PIPELINED
as
v_type_dept type_dept;
begin
for x in (
select *
from t_dept
where dr = 0
start with pk_id = v_id
connect by prior pk_id = SUPER_PK) loop
v_type_dept := type_dept(x.pk_id,x.org_pk,x.jgbm,x.jgmc,x.jgjc,
x.ywjc,x.jgdm,x.fzr,x.jglb,x.jgxzjb,
x.jglscj,x.sjsfxzzgjg,x.sjsfxzzgbm,
x.lxdh,x.czhm,x.dzyx,x.yb,x.dz,x.layer,
x.create_pk,x.create_time,x.update_pk,
x.update_time,x.dr,x.vstatus,x.ywmc,
x.super_pk,x.layer_str,x.locate_count,
x.old_pk_id,x.dwrs);
pipe row(v_type_dept);
end loop;
return;
END;