利用FOR LOOP代替DB2 UNF中不能使…

在DB2的UDF中无法定义CURSOR,我们可以使用FOR LOOP来代替它.

CREATE FUNCTIONDTOPT.GetNodeNameAddress 

( 

p_nodeId INTEGER

)

RETURNS VARCHAR(255)

LANGUAGE SQL

READS SQL DATA

NO EXTERNAL ACTION

DETERMINISTIC

F1: BEGIN ATOMIC

    DECLAREv_nodeNameAddress VARCHAR(255) DEFAULT '';

    DECLARE v_isFirstINTEGER DEFAULT 1; --If it is the first level node

    FOR rec AS

WITH ret (childnodeid, parentnodeid, nodelevel) AS

(SELECT childnodeid, parentnodeid, nodelevel

FROM dtopt.producthierarchytree WHERE childnodeid= p_nodeIdAND treekey=1

UNION ALL

SELECT t.childnodeid, t.parentnodeid, t.nodelevel

FROM dtopt.producthierarchytree t, ret r WHERE r.parentnodeid= t.childnodeid AND t.treekey=1)

SELECT n.Name FROM ret INNER JOIN dtopt.ProductHierarchyNode non ret.childnodeid=n.ProductHierarchyNodeID

ORDER BY nodelevel

DO 

IF v_isFirst = 1

THEN

SET v_isFirst = 0;

SET v_nodeNameAddress = rec.name; 

ELSE

SET v_nodeNameAddress = v_nodeNameAddress ||'>' || rec.name;

END IF;

END FOR; 

RETURN v_nodeNameAddress;

END