一、单值函数:
/* Formatted on 2012/05/11 18:02 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION demo.f_basic_get_orgid
RETURN NUMBER
IS
i_return NUMBER (11);
i_get_id NUMBER (11);
i_line NUMBER (11);
CURSOR cur_org
IS
SELECT organization_id, ROWNUM AS rn
FROM t_basic_organization
ORDER BY organization_id;
cur_org_val cur_org%ROWTYPE;
BEGIN
i_return := 0;
OPEN cur_org;
LOOP
FETCH cur_org
INTO cur_org_val;
EXIT WHEN cur_org%NOTFOUND;
i_get_id := cur_org_val.organization_id + 1;
i_line := cur_org_val.rn;
IF i_get_id != i_line
THEN
i_return := i_line - 1;
EXIT;
END IF;
END LOOP;
CLOSE cur_org;
IF i_return = 0
THEN
SELECT COUNT (1)
INTO i_return
FROM t_basic_organization;
END IF;
RETURN i_return;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END f_basic_get_orgid;
功能:获取t_basic_organization表主键的下一个值,用于向该表中插入新值。
二、记录集函数:
/* Formatted on 2012/05/11 18:03 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION demo.f_get_childorg (parent_org NUMBER)
RETURN childorg_tb PIPELINED
IS
obj_childorg childorg_ty;
sub_childorg childorg_ty;
org_level NUMBER;
BEGIN
org_level := 0;
FOR myrow IN (SELECT *
FROM t_basic_organization
WHERE parent_organization = parent_org)
LOOP
obj_childorg :=
childorg_ty (myrow.organization_id,
myrow.organization_name,
myrow.parent_organization,
org_level
);
PIPE ROW (obj_childorg);
FOR subrow IN
(SELECT *
FROM TABLE (f_get_childorg (obj_childorg.organization_id)))
LOOP
sub_childorg :=
childorg_ty (subrow.organization_id,
subrow.organization_name,
subrow.parent_organization,
subrow.organization_level + 1
);
PIPE ROW (sub_childorg);
END LOOP;
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END f_get_childorg;
功能:通过递归调用获取所有子行信息。
PS: childorg_ty 和 childorg_tb 的定义:
/* Formatted on 2012/05/14 12:02 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TYPE demo.childorg_ty AS OBJECT (
organization_id NUMBER,
organization_name VARCHAR2 (250),
parent_organization NUMBER,
organization_level NUMBER
)
CREATE OR REPLACE TYPE DEMO.CHILDORG_TB AS TABLE OF CHILDORG_TY;