使用ORACLE 10I 以上,來解 BOM 表與求需求量

TIPTOP ERP ORACLE BOM QTY

/*正解*/

select bmb01, bmb03, level, bmb06,
    LTRIM(sys_connect_by_path(bmb01, '->'), '->') fpath,
    LTRIM(sys_connect_by_path(bmb06, '->'), '->') ausage,
    str_mult(sys_connect_by_path(bmb06, '*')) quate
 from bmb_file
start with bmb01 like '01DS102200G%'
connect by prior bmb03=bmb01

/*或是使用 olap 內建的FUNCTION, 因為我的環境沒安裝這東西所以無法使用*/
select bmb01, bmb03, level, bmb06,
    LTRIM(sys_connect_by_path(bmb01, '->'), '->') fpath,
    LTRIM(sys_connect_by_path(bmb06, '->'), '->') ausage,
    dbms_aw.eval_number(ltrim(sys_connect_by_path(bmb06, '*'), '*')) quate
 from bmb_file
start with bmb01 like '01DS102200G%'
connect by prior bmb03=bmb01



CREATE OR REPLACE FUNCTION GWH.str_mult(p_str IN VARCHAR2) RETURN NUMBER
AS
    l_str VARCHAR2(1000):=p_str||'*';
    l_n   NUMBER;
    l_data NUMBER:=1;
BEGIN
    IF instr(l_str,'*')=1 THEN
       l_str:=SUBSTR(l_str,2);
    END IF;
    LOOP
          l_n:=instr(l_str,'*');
        EXIT WHEN (NVL(l_n,0)=0);
        l_data:=l_data*TO_NUMBER(SUBSTR(l_str,1,l_n-1));
        l_str:=SUBSTR(l_str,l_n+1);
    END LOOP;
    RETURN l_data;
END;