一个遍历的例子

strSql = "SELECT tb.location,tb.levl,tb.whereuse,tb.partno,tb.dateto,tb.datefrom,iim.idesc " & _
"FROM (SELECT LEVEL as levl,GET_LOCATION_CHARLEY (bprod,bseq)AS location, " & _
"mbm.bprod AS whereuse,mbm.bchld AS partno,mbm.bddis as dateto, " & _
"mbm.bdeff as datefrom " & _
"FROM mbm " & _
"WHERE mbm.bdeff <= '" & strDate & "' " & _
"AND mbm.bddis >= '" & strDate & "' " & _
"AND bid='BM' " & strFacilCondit & _
"START WITH bprod = '" & strParentNo & "' " & _
"CONNECT BY prior bchld = bprod " & _
"ORDER BY bprod,bchld) tb,iim " & _
"WHERE tb.partno = iim.iprod " & _
"AND iim.iid = 'IM' order by whereuse,levl"

Oracle函数

FUNCTION GET_LOCATION_CHARLEY(
strProd IN VARCHAR2,
strSeq IN VARCHAR2
)
RETURN VARCHAR2 AS

strLocate VARCHAR2(100);
strLocateAll VARCHAR2(500);

CURSOR locat_together IS SELECT pndes FROM mpn WHERE pnprod = strProd AND pnopn = strSeq;
BEGIN
OPEN locat_together;
LOOP
FETCH locat_together into strLocate;
EXIT WHEN locat_together%NOTFOUND;
strLocateAll := strLocateAll || ',' || strLocate;
END LOOP;
IF RTRIM(strLocateAll) IS NOT NULL THEN
IF LTRIM(strLocateAll) IS NOT NULL THEN
strLocateAll := SUBSTR(strLocateAll,2,length(strLocateAll));
END IF;
END IF;
CLOSE locat_together;
RETURN strLocateAll;
END;