一个遍历的例子

最新推荐文章于 2022-05-08 19:44:58 发布

chenanlin1981

最新推荐文章于 2022-05-08 19:44:58 发布

阅读量855

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/chenanlin1981/article/details/840181

本文展示了一个复杂的Oracle SQL查询示例，该查询用于从多个表中获取并整合数据，并介绍了一个自定义Oracle函数GET_LOCATION_CHARLEY，该函数用于根据产品编号和序列号检索位置描述。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;